planner: unreferenced Cartesian inputs prevent DISTINCT ORDER BY LIMIT early termination

## Bug Report

### 1. Minimal reproduce step (Required)

[tidb_distinct_ordered_cartesian_repro.sql](https://github.com/user-attachments/files/30152985/tidb_distinct_ordered_cartesian_repro.sql)

[tidb_distinct_ordered_cartesian_repro_result.txt](https://github.com/user-attachments/files/30152986/tidb_distinct_ordered_cartesian_repro_result.txt)

Run the attached reproduction script:

```bash
mysql -h 127.0.0.1 -P 4000 -u root --comments --table \
< tidb_distinct_ordered_cartesian_repro.sql \
> tidb_distinct_ordered_cartesian_repro_result.txt 2>&1
```

The script creates:

- `t0`: 100,000 rows, with 20,000 distinct `c0` values and an index on `c0`
- `t1`: 10 rows
- `t2`: 10 rows

The primary query is:

```sql
SELECT DISTINCT t0.c0
FROM t2, t0, t1
ORDER BY t0.c0 DESC
LIMIT 1;
```

Only `t0.c0` contributes to the result, the `DISTINCT` key, and the ordering key. `t1` and `t2` are unreferenced Cartesian inputs that only determine whether the result is empty.

The script compares four query forms.

#### Q0: ordered single-table baseline

```sql
SELECT DISTINCT t0.c0
FROM t0 FORCE INDEX (idx_c0)
ORDER BY t0.c0 DESC
LIMIT 1;
```

#### Q1: original query

```sql
SELECT DISTINCT t0.c0
FROM t2, t0, t1
ORDER BY t0.c0 DESC
LIMIT 1;
```

#### Q2: equivalent existence-check rewrite

```sql
SELECT DISTINCT t0.c0
FROM t0 FORCE INDEX (idx_c0)
WHERE EXISTS (SELECT 1 FROM t1)
AND EXISTS (SELECT 1 FROM t2)
ORDER BY t0.c0 DESC
LIMIT 1;
```

#### Q3: ordered Top-1 control

```sql
SELECT t0.c0
FROM t0 FORCE INDEX (idx_c0)
WHERE EXISTS (SELECT 1 FROM t1)
AND EXISTS (SELECT 1 FROM t2)
ORDER BY t0.c0 DESC
LIMIT 1;
```

The script also runs the original logical query with HashJoin disabled:

```sql
SELECT /*+
STRAIGHT_JOIN()
USE_INDEX(t0, idx_c0)
STREAM_AGG()
NO_HASH_JOIN(t0, t1, t2)
*/ DISTINCT t0.c0
FROM t0, t2, t1
ORDER BY t0.c0 DESC
LIMIT 1;
```

All non-empty query forms return the same value:

```text
q0_t0_only_distinct = 19999
q1_original = 19999
q2_exists_distinct = 19999
q3_ordered_top1 = 19999

nonempty_result_equivalence = PASS
```

The script additionally verifies empty-input semantics. If either `t1` or `t2` is empty, the original query and both control queries return zero rows.

Statistics are collected after the data is committed. The execution result confirms:

```text
t0 Row_count = 100000
t1 Row_count = 10
t2 Row_count = 10

Modify_count = 0
Healthy = 100
Analyze = finished
```

The plans do not use pseudo statistics.

### 2. What did you expect to see? (Required)

TiDB can already generate an ordered early-termination plan for the single-table form:

```text
Limit
└─StreamAgg
└─IndexReader
└─StreamAgg
└─IndexFullScan keep order:true, desc
```

The unreferenced Cartesian inputs do not contribute any projected columns, grouping keys, ordering keys, predicates, or other observable expressions. They only determine whether the complete result is empty.

I expected the optimizer to combine two optimizations:

1. Treat `t1` and `t2` as one-time existence checks.
2. Preserve the ordered `idx_c0` path for `DISTINCT ... ORDER BY ... LIMIT 1`.

An acceptable plan could be logically equivalent to:

```sql
SELECT DISTINCT t0.c0
FROM t0
WHERE EXISTS (SELECT 1 FROM t1)
AND EXISTS (SELECT 1 FROM t2)
ORDER BY t0.c0 DESC
LIMIT 1;
```

The optimizer does not need to produce exactly this rewritten SQL. A physical implementation that reads at most one row from each unreferenced input and preserves their empty/non-empty status would also be sufficient.

The final plan should avoid work proportional to:

```text
|t0| * |t1| * |t2|
```

and should preserve the ordered-index early-termination opportunity on `t0`.

### 3. What did you see instead? (Required)

#### Q0: TiDB supports ordered DISTINCT LIMIT when `t0` is isolated

The single-table baseline uses:

```text
Limit
└─StreamAgg
└─IndexReader
└─StreamAgg
└─IndexFullScan keep order:true, desc
```

The index scan reads only 6,144 of the 100,000 rows before the root `Limit` receives one distinct value.

Three runs:

```text
3.22 ms
3.09 ms
3.06 ms
```

Median runtime:

```text
3.09 ms
```

This shows that TiDB can use an ordered index and stop early for the `DISTINCT ... ORDER BY ... LIMIT` operation when the projected table is queried alone.

#### Q1: adding unreferenced Cartesian inputs removes the ordered path

The original query uses:

```text
TopN
└─HashAgg
└─HashJoin CARTESIAN
├─HashJoin CARTESIAN
│ ├─t1
│ └─t2
└─IndexFullScan(t0) keep order:false
```

The estimated and actual cardinalities are both accurate:

```text
HashJoin estRows = 10000000
HashJoin actRows = 10000000
```

TiDB scans all 100,000 rows from `t0`, generates all 10,000,000 Cartesian rows, reduces them to 20,000 distinct values with `HashAgg`, and only then applies `TopN LIMIT 1`.

Three runs:

```text
338.1 ms
367.4 ms
397.5 ms
```

Median runtime:

```text
367.4 ms
```

This is not caused by a cardinality-estimation error. The optimizer knows that the Cartesian product contains 10,000,000 rows and still selects a plan that materializes the complete product.

#### Q2: existence checks restore the ordered DISTINCT LIMIT plan

The equivalent `EXISTS` rewrite uses essentially the same ordered plan as Q0:

```text
Limit
└─StreamAgg
└─IndexReader
└─StreamAgg
└─IndexFullScan keep order:true, desc
```

It avoids the Cartesian expansion and again reads only a small prefix of the `t0.c0` index.

Three runs:

```text
3.26 ms
3.79 ms
1.64 ms
```

Median runtime:

```text
3.26 ms
```

The original query is approximately 112.7 times slower:

```text
367.4 ms / 3.26 ms = 112.7x
```

The Q0 and Q2 median runtimes are almost identical:

```text
Q0 = 3.09 ms
Q2 = 3.26 ms
```

This indicates that once the unreferenced inputs are represented as existence checks, TiDB can preserve the ordered `DISTINCT ... ORDER BY ... LIMIT` path.

#### Q3: the ideal ordered Top-1 path reads one index row

The ordered Top-1 control uses:

```text
Limit
└─IndexReader
└─Limit
└─IndexFullScan keep order:true, desc
```

The index scan processes exactly one row:

```text
IndexFullScan actRows = 1
total_process_keys = 1
```

Three runs:

```text
406.0 us
283.3 us
309.4 us
```

Median runtime:

```text
309.4 us
```

The original query is approximately 1,187 times slower than this control:

```text
367.4 ms / 0.3094 ms = 1187x
```

#### Disabling HashJoin does not expose the desired combined plan

When HashJoin is disabled for the same original logical query, TiDB produces:

```text
Limit
└─StreamAgg
└─Sort
└─MergeJoin
└─MergeJoin
```

This plan still:

- scans all 100,000 rows from `t0`;
- generates all 10,000,000 joined rows;
- sorts the materialized result;
- uses approximately 243.1 MB for the Sort operator;
- and takes approximately 2.03 seconds.

Therefore, this does not appear to be only a cost-model error that selects HashJoin over an already available ordered alternative.

The optimizer does not construct or preserve a combined plan that:

- evaluates the independent unreferenced inputs as one-time existence checks; and
- retains the ordered `DISTINCT ... ORDER BY ... LIMIT` path on `t0`.

#### Impact

The query returns one row, but the selected plan generates 10,000,000 intermediate rows.

The unnecessary work grows multiplicatively with the sizes of the unreferenced inputs. It also destroys an ordered-index early-termination opportunity that TiDB can use when the same `DISTINCT ... ORDER BY ... LIMIT` operation is evaluated without the Cartesian inputs.

For larger inputs, this can cause:

- severe CPU amplification;
- increased latency;
- unnecessary memory use;
- resource contention;
- and query timeouts.

The current workaround is to manually rewrite the unreferenced Cartesian inputs as `EXISTS` conditions. Applications and generated SQL cannot always perform this transformation manually.

#### Suggested fix

Consider adding a logical transformation before physical join selection.

When an independent Cartesian input:

- appears in no join condition or filter predicate;
- contributes no columns to the projection, `DISTINCT` key, grouping key, ordering key, or other observable expression;
- can only duplicate rows from the referenced input;
- and only determines whether the complete result is empty;

the optimizer could reduce it to a one-time existence check.

The transformation should preserve useful physical properties from the referenced input. In this case, reducing `t1` and `t2` to existence checks should allow TiDB to retain:

```text
idx_c0 descending scan
→ streaming duplicate elimination
→ LIMIT early termination
```

An alternative physical implementation could read at most one row from each unreferenced input and propagate its empty/non-empty status without materializing the Cartesian product.

The transformation must preserve empty-input semantics: if either `t1` or `t2` is empty, the final result must remain empty.

Regression tests could cover:

- one or more non-empty unreferenced Cartesian inputs;
- one or more empty Cartesian inputs;
- `DISTINCT` and equivalent `GROUP BY` forms;
- ordered and unordered variants;
- queries with and without `LIMIT`;
- preservation of ordered index paths;
- and behavior when HashJoin is disabled.

### 4. What is your TiDB version? (Required)

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-15 02:06:00
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
```

Run the attached tidb_distinct_ordered_cartesian_repro.sql against TiDB v8.5.7 and compare Q0–Q3 with the HashJoin-disabled plan. Trace the logical transformation and physical join-selection paths described in the report; done means preserving empty-input semantics, avoiding Cartesian materialization, retaining ordered DISTINCT/ORDER BY/LIMIT early termination, and adding regression coverage for the listed variants.

Written by the indexing model from the issue text.