planner: plain DISTINCT materializes unreferenced Cartesian inputs instead of using existence checks

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

[tidb_distinct_cartesian_repro.sql](https://github.com/user-attachments/files/30153289/tidb_distinct_cartesian_repro.sql)

[tidb_distinct_cartesian_repro_result.txt](https://github.com/user-attachments/files/30153293/tidb_distinct_cartesian_repro_result.txt)

Run the attached reproduction script:

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

The script creates the following tables:

- `t0`: 85 rows
- `t1`: 81 rows
- `t2`: 155 rows

The original query is:

```sql
SELECT DISTINCTROW t1.c4 AS ref0
FROM t2, t0, t1;
```

The query has no `ORDER BY`, `LIMIT`, join condition, or filter predicate.

Only `t1.c4` contributes to the output and the `DISTINCT` key. `t0` and `t2` are independent, unreferenced Cartesian inputs. They only determine whether the complete result is empty.

The script compares the original query with the following equivalent rewrite:

```sql
SELECT DISTINCTROW t1.c4 AS ref0
FROM t1
WHERE EXISTS (SELECT 1 FROM t0)
AND EXISTS (SELECT 1 FROM t2);
```

It also tests an equivalent physical form that explicitly reads at most one row from each unreferenced input:

```sql
SELECT DISTINCTROW t1.c4 AS ref0
FROM
(SELECT 1 AS marker FROM t2 LIMIT 1) AS t2_one
CROSS JOIN
(SELECT 1 AS marker FROM t0 LIMIT 1) AS t0_one
CROSS JOIN t1;
```

The script:

- collects table statistics;
- verifies the exact result sets;
- verifies empty-input semantics;
- runs `EXPLAIN FORMAT = 'verbose'`;
- and runs `EXPLAIN ANALYZE` three times for the baseline, original query, and equivalent rewrites.

The complete SQL script and execution result are attached.

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

`t0` and `t2` do not contribute any output columns, grouping keys, ordering keys, join conditions, filter predicates, or other observable expressions.

For this `DISTINCT` query, rows from `t0` and `t2` can only duplicate rows produced from `t1`. Once either table is known to be non-empty, reading additional rows from that table cannot change the final distinct values of `t1.c4`.

I expected the optimizer to avoid materializing the complete Cartesian product.

For example, the optimizer could reduce `t0` and `t2` to one-time existence checks and execute a plan logically equivalent to:

```sql
SELECT DISTINCTROW t1.c4 AS ref0
FROM t1
WHERE EXISTS (SELECT 1 FROM t0)
AND EXISTS (SELECT 1 FROM t2);
```

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 its empty/non-empty status would also be sufficient.

The execution should avoid work proportional to:

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

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

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

All tested query forms return the same 45-row result:

```text
q0_t1_only = 45
q1_original = 45
q2_exists = 45
q3_limit_one_inputs = 45
```

The exact result comparison reports:

```text
original_vs_exists_mismatch = 0
original_vs_limit_one_mismatch = 0
result_equivalence = PASS
```

The empty-input tests also pass:

```text
t0_empty:
original_rows = 0
exists_rows = 0
limit_one_rows = 0

t2_empty:
original_rows = 0
exists_rows = 0
limit_one_rows = 0
```

Statistics were successfully collected:

```text
t0 Row_count = 85
t1 Row_count = 81
t2 Row_count = 155

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

The expected Cartesian product contains:

```text
85 * 81 * 155 = 1,067,175 rows
```

The original query uses the following plan shape:

```text
HashAgg
└─HashJoin CARTESIAN
├─t2
└─HashJoin CARTESIAN
├─t0
└─t1
```

The inner Cartesian join produces:

```text
estRows = 6,885
actRows = 6,885
```

The outer Cartesian join then produces the complete product:

```text
estRows = 1,067,175
actRows = 1,067,175
```

`HashAgg` finally reduces these 1,067,175 rows to only 45 distinct result rows.

The cardinality estimate is accurate. This is therefore not caused by missing statistics or a severe cardinality-estimation error. TiDB knows the full Cartesian-product size but still materializes every row before duplicate elimination.

Three runs of the original query:

```text
52.5 ms
48.4 ms
54.2 ms
```

Median runtime:

```text
52.5 ms
```

The equivalent `EXISTS` rewrite does not contain the Cartesian joins. Its plan only scans `t1` and performs duplicate elimination:

```text
HashAgg
└─TableReader
└─HashAgg
└─TableFullScan(t1)
```

Three runs of the `EXISTS` rewrite:

```text
645.4 us
578.6 us
556.3 us
```

Median runtime:

```text
578.6 us
```

The original query is approximately 90.7 times slower:

```text
52.5 ms / 0.5786 ms = 90.7x
```

The single-table baseline has a median runtime of approximately:

```text
598.4 us
```

The baseline and the `EXISTS` rewrite have nearly identical runtimes:

```text
single-table baseline = 598.4 us
EXISTS rewrite = 578.6 us
```

This indicates that once `t0` and `t2` are represented as existence-only inputs, the query cost is essentially reduced to scanning and deduplicating `t1`.

The explicit one-row-input rewrite confirms that TiDB can execute the desired physical strategy:

```text
t0 actRows = 1
t2 actRows = 1
final join rows = 81
distinct results = 45
```

However, TiDB does not derive this strategy from the original query.

Disabling HashJoin does not expose an existence-check plan. TiDB changes the physical join algorithm, but still materializes the complete 1,067,175-row Cartesian product before duplicate elimination.

This suggests that the missing optimization occurs before physical join-algorithm selection: the optimizer does not reduce independent, unreferenced Cartesian inputs to existence checks under a duplicate-insensitive operator.

#### Impact

This is not only a constant-factor plan difference.

The query returns 45 rows, but TiDB first generates 1,067,175 intermediate rows. The intermediate result is approximately 23,715 times larger than the final result:

```text
1,067,175 / 45 = 23,715
```

The unnecessary work grows multiplicatively with the sizes of the unreferenced inputs:

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

For larger tables, this behavior can cause:

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

The current workaround is to manually rewrite the unreferenced inputs as `EXISTS` conditions or explicitly limit each input to one row. Applications and generated SQL cannot always perform this transformation manually.

#### Suggested fix

Consider adding a logical optimization rule for duplicate-insensitive operators such as `DISTINCT` and equivalent `GROUP BY` plans.

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 produced by the referenced input;
- and only determines whether the complete result is empty;

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

For this query, the logical transformation is equivalent to:

```sql
SELECT DISTINCTROW t1.c4 AS ref0
FROM t1
WHERE EXISTS (SELECT 1 FROM t0)
AND EXISTS (SELECT 1 FROM t2);
```

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

The transformation must preserve empty-input semantics: if any required Cartesian input is empty, the final result must remain empty.

Regression tests could cover:

- one independent unreferenced Cartesian input;
- multiple independent unreferenced Cartesian inputs;
- one or more empty inputs;
- `DISTINCT` and equivalent `GROUP BY` forms;
- nullable output columns;
- and behavior with different physical join algorithms enabled or disabled.

#### Relation to #69915

This issue is related to #69915 because both involve unreferenced Cartesian inputs under `DISTINCT`.

However, #69915 contains `ORDER BY` and `LIMIT` and focuses on the failure to combine existence-only input handling with an ordered `DISTINCT ... ORDER BY ... LIMIT` early-termination path.

This reproducer has neither `ORDER BY` nor `LIMIT`. It demonstrates the more fundamental case: TiDB does not perform the Cartesian-input-to-existence reduction even for a plain `DISTINCT` query where no ordering property or TopN early termination needs to be preserved.

### 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
```

Start by running the attached tidb_distinct_cartesian_repro.sql script and comparing EXPLAIN ANALYZE for the original query and the EXISTS rewrite. Then trace the planner's handling of plain DISTINCT with independent Cartesian inputs. Done means a plan avoids materializing the full product, preserves empty-input semantics, and includes regression coverage for the described cases.

Written by the indexing model from the issue text.