`ORDER BY` Triggers Two‑Phase Aggregation Push‑Down, Exposing TiKV/TiDB Mismatch in `NO_ZERO_DATE` Handling for `MAX(CAST(... AS DATE))`

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal Reproduce Step (Required)

```sql
DROP DATABASE IF EXISTS repro_tidb615_db1_min;
CREATE DATABASE repro_tidb615_db1_min;
USE repro_tidb615_db1_min;

SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET time_zone='+00:00';

CREATE TABLE src(
id BIGINT NOT NULL PRIMARY KEY,
c2 TEXT NULL,
c4 BIGINT NULL
);

INSERT INTO src VALUES
(5, '', 0),
(6, '-541333355', 9223372036854775807);

CREATE TABLE l(
id BIGINT NOT NULL PRIMARY KEY,
c4 BIGINT NULL
);

CREATE TABLE r(
id BIGINT NOT NULL PRIMARY KEY,
c2 TEXT NULL
);

INSERT INTO l SELECT id, c4 FROM src;
INSERT INTO r SELECT id, c2 FROM src;

-- Single‑table with ORDER BY: returns '0000-00-00'
SELECT MAX(
CAST(
CASE c2
WHEN id THEN id | c4
ELSE CAST(c4 AS DATETIME)
END AS DATE
)
) AS single_max
FROM src
ORDER BY id;

-- Split/scalar subquery: returns NULL
SELECT MAX(
CAST(
CASE v.c2
WHEN v.id THEN v.id | v.c4
ELSE CAST(v.c4 AS DATETIME)
END AS DATE
)
) AS split_max
FROM (
SELECT
l.id,
(SELECT r.c2 FROM r WHERE r.id = l.id) AS c2,
l.c4
FROM l
) v
ORDER BY v.id;
```

### 2. What Did You Expect to See? (Required)

In `NO_ZERO_DATE` mode, `CAST(c4 AS DATETIME)` for both `c4 = 0` and the out‑of‑range value `9223372036854775807` should produce invalid datetime values (zero dates). The outer `CAST(... AS DATE)` should therefore return `NULL`. `MAX()` over all `NULL`s should return `NULL`.

Thus both the single‑table and the scalar‑subquery rewrite should return `NULL`.

### 3. What Did You See Instead? (Required)

- Single‑table with `ORDER BY id`: returns `'0000-00-00'` (wrong)
- Split/scalar subquery: returns `NULL` (correct)
- Single‑table without `ORDER BY id`: also returns `NULL` — the bug disappears when the `ORDER BY` clause is removed.

### 4. What Is Your TiDB Version? (Required)

Version: TiDB‑v9.0.0

### 5. Execution Plan Differences

**Single‑table with `ORDER BY id` (wrong)** — two‑phase aggregation pushes `MAX(CAST(... AS DATE))` down to TiKV:

```
Sort_8 [root]
└─HashAgg_16 [root] funcs:max(Column#7)->Column#5, firstrow(Column#8)->src.id
└─TableReader_17
└─HashAgg_10 [cop[tikv]] funcs:max(cast(case(...), date BINARY))->Column#7, firstrow(src.id)->Column#8
└─TableFullScan_15 [cop[tikv]]
```

**Single‑table without `ORDER BY id` (correct)** — aggregation stays at root:

(No forced push‑down; the `MAX` is evaluated entirely at the TiDB root layer, yielding `NULL`.)

**Split/scalar subquery (correct)** — expression evaluated at root before aggregation:

```
Sort_15 [root]
└─HashAgg_19 [root] funcs:max(Column#12)->Column#10, firstrow(Column#13)->l.id
└─Projection_50 [root] cast(case(...), date BINARY)->Column#12
└─MergeJoin_22 [root]
```

### 6. Root Cause

The presence of `ORDER BY id` in an aggregate query without `GROUP BY` forces the optimizer to retain the non‑aggregated column `id` for sorting. This triggers a two‑phase aggregation strategy:

**Phase 1 (TiKV coprocessor):** partial `MAX(CAST(... AS DATE))` and `firstrow(id)` are pushed down to TiKV. TiKV's expression evaluator, under `NO_ZERO_DATE` mode, incorrectly treats `CAST(0 AS DATETIME)` and the out‑of‑range cast as valid zero dates (`'0000-00-00'`) instead of returning `NULL`.

**Phase 2 (TiDB root):** the partial results are merged; the root `MAX()` simply picks up the non‑`NULL` value from TiKV, returning `'0000-00-00'`.

When `ORDER BY id` is removed, the optimizer does not need to preserve `id` and can perform the entire aggregation at the TiDB root layer, where the expression evaluator correctly applies `NO_ZERO_DATE` and returns `NULL` for all rows.

The scalar‑subquery rewrite introduces a join, preventing the aggregate expression from being pushed down to TiKV. The expression is evaluated at the root layer, yielding `NULL`.

This is a plan‑sensitive bug: the `ORDER BY` clause alters the optimizer's push‑down decision, accidentally exposing the mismatch between TiKV and TiDB in handling illegal date conversions.