[Compatibility]: non-equality correlated scalar aggregates reject common query shapes

### Is there an existing issue for the same bug?

- [x] I have checked the existing issues.

### Branch Name

main

### Commit ID

74668fc075c965c462b30f10ae24a1c19c289cfd

### Other Environment Information

```Markdown
- MatrixOne was built from the commit above and started locally with etc/launch/launch.toml.
- MySQL comparison version: 8.0.45.
- Every query was repeated three times with the same result.
```

### Actual Behavior

MatrixOne supports a simple non-equality correlated scalar aggregate such as
`SELECT MAX(i.val) ... WHERE i.grp <= o.grp`, but rejects several ordinary
extensions of the same query that MySQL 8.0 accepts:

1. Wrapping the aggregate result in an expression, including `MAX(i.val) + 1`,
`COALESCE(MAX(i.val), -1)`, and `CASE ... MAX(i.val) ... END`.
2. Adding `HAVING`, `DISTINCT`, or `GROUP BY ... ORDER BY ... LIMIT 1` inside
the scalar aggregate subquery.
3. Reading the outer row from a derived table.
4. Referencing columns from two outer join inputs.
5. Applying `COUNT(*)` to a derived `UNION ALL` inner input.

The first group is rejected with:

```text
aggregation with non equal predicate in scalar subquery will be supported in future version is not yet implemented
```

The remaining groups are rejected by related NYI guards for derived outer
tables, multiple outer tables, correlated LIMIT, or an inner shape without a
reachable Row_ID.

The planner's non-equality scalar-aggregate rewrite only accepts a narrow
`PROJECT -> AGG -> inner` form whose first projection is a direct aggregate
column. It also requires exactly one base-table outer binding with a hidden
Row_ID and a reachable inner Row_ID for `COUNT(*)`. These structural
restrictions reject semantically valid scalar queries rather than producing a
result.

### Expected Behavior

These queries should produce the same scalar result as MySQL 8.0.45. For the
sample data below, representative expected results include:

```text
MAX(i.val) + 1: (1,6), (2,6), (3,11), (4,11)
derived outer table + MAX: (1,5), (2,5), (3,10), (4,10)
UNION ALL inner + COUNT(*): (1,4), (2,4), (3,8), (4,8)
```

### Steps to Reproduce

```sql
CREATE DATABASE codex_correlated_noneq_aggregate_0907;
USE codex_correlated_noneq_aggregate_0907;

CREATE TABLE outer_t (
id INT PRIMARY KEY,
grp INT NOT NULL,
val INT NULL
);
CREATE TABLE inner_t (
id INT PRIMARY KEY,
grp INT NOT NULL,
val INT NULL
);

INSERT INTO outer_t VALUES
(1,1,5),(2,1,NULL),(3,2,10),(4,3,7);
INSERT INTO inner_t VALUES
(10,1,5),(11,1,NULL),(12,2,8),(13,2,10),(14,4,NULL);

-- Control: supported by both MatrixOne and MySQL.
SELECT o.id,
(SELECT MAX(i.val) FROM inner_t i WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;

-- Aggregate-result expressions.
SELECT o.id,
(SELECT MAX(i.val) + 1 FROM inner_t i WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;

SELECT o.id,
(SELECT COALESCE(MAX(i.val), -1)
FROM inner_t i WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;

SELECT o.id,
(SELECT CASE WHEN MAX(i.val) IS NULL THEN -1 ELSE MAX(i.val) END
FROM inner_t i WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;

-- HAVING / DISTINCT / grouped Top-1.
SELECT o.id,
(SELECT MAX(i.val) FROM inner_t i
WHERE i.grp <= o.grp HAVING MAX(i.val) >= 5) AS v
FROM outer_t o ORDER BY o.id;

SELECT o.id,
(SELECT DISTINCT MAX(i.val) FROM inner_t i WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;

SELECT o.id,
(SELECT MAX(i.val) FROM inner_t i WHERE i.grp <= o.grp
GROUP BY i.grp ORDER BY i.grp DESC LIMIT 1) AS v
FROM outer_t o ORDER BY o.id;

-- Derived outer input.
SELECT d.id,
(SELECT MAX(i.val) FROM inner_t i WHERE i.grp <= d.grp) AS v
FROM (SELECT id, grp FROM outer_t) d ORDER BY d.id;

-- Two outer inputs.
SELECT o.id, x.id,
(SELECT MAX(i.val) FROM inner_t i
WHERE i.grp <= o.grp AND i.id <= x.id) AS v
FROM outer_t o JOIN outer_t x ON x.id = o.id
ORDER BY o.id;

-- Derived UNION ALL inner input with COUNT(*).
SELECT o.id,
(SELECT COUNT(*)
FROM (SELECT id,grp FROM inner_t
UNION ALL
SELECT id+100,grp FROM inner_t) i
WHERE i.grp <= o.grp) AS v
FROM outer_t o ORDER BY o.id;
```

### Additional information

This is a remaining coverage gap after the simple form fixed for #23942 / PR
#24053. The original #23942 shape now works; the cases above exercise planner
shapes that are still explicitly rejected in `pkg/sql/plan/flatten_subquery.go`.