planner: LIMIT cost model chooses a 42x slower HashJoin build side for a Cartesian join with a cross-table residual predicate

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

Run the attached reproduction file with optimizer comments preserved:

[tidb_limit_cartesian_hashjoin_build_side_repro.sql](https://github.com/user-attachments/files/30214789/tidb_limit_cartesian_hashjoin_build_side_repro.sql)

[tidb_limit_cartesian_hashjoin_build_side_repro_result.txt](https://github.com/user-attachments/files/30214791/tidb_limit_cartesian_hashjoin_build_side_repro_result.txt)

```bash
mysql --comments --table \
< tidb_limit_cartesian_hashjoin_build_side_repro.sql \
> tidb_limit_cartesian_hashjoin_build_side_repro_result.txt 2>&1
````

The reproduction creates two analyzed tables with the following data shape:

```text
t0_data rows = 3380
t1_data rows = 3361
t0_data.c3 = '' rows = 0
t1_data.c1 IS NULL rows = 1
full query result rows = 3380
```

Statistics are current and healthy:

```text
t0_data: Row_count = 3380, Modify_count = 0, Healthy = 100
t1_data: Row_count = 3361, Modify_count = 0, Healthy = 100
```

The target query is:

```sql
SELECT a.c1 AS ref0, b.c3
FROM t1_data AS a, t0_data AS b
WHERE a.c1 IS NULL OR b.c3 = ''
LIMIT 2;
```

TiDB chooses the following Cartesian HashJoin direction by default:

```text
HashJoin
├─IndexReader on t1_data (Build)
└─TableReader on t0_data (Probe)
```

Compare it with the same HashJoin using the opposite Build side:

```sql
SELECT /*+ HASH_JOIN_BUILD(b) */
a.c1 AS ref0, b.c3
FROM t1_data AS a, t0_data AS b
WHERE a.c1 IS NULL OR b.c3 = ''
LIMIT 2;
```

This produces:

```text
HashJoin
├─TableReader on t0_data (Build)
└─IndexReader on t1_data (Probe)
```

A MergeJoin control can also be tested:

```sql
SELECT /*+ MERGE_JOIN(a, b) */
a.c1 AS ref0, b.c3
FROM t1_data AS a, t0_data AS b
WHERE a.c1 IS NULL OR b.c3 = ''
LIMIT 2;
```

Each plan is warmed up once and then measured three times with `EXPLAIN ANALYZE`.

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

TiDB should account for the cross-table residual predicate and the small parent `LIMIT` when comparing the two possible HashJoin Build/Probe orientations.

In this data distribution, all qualifying rows come from the single `t1_data` row where `c1 IS NULL`:

```text
one qualifying t1_data row × 3380 t0_data rows
```

Using `t0_data` as the Build side allows that qualifying `t1_data` Probe row to produce enough results immediately to satisfy `LIMIT 2`.

The optimizer should therefore choose the reversed HashJoin Build side, or another plan such as MergeJoin that can satisfy the small LIMIT without evaluating a large number of Cartesian candidates.

At minimum, the estimated cost ordering should reflect the large runtime difference between the two HashJoin orientations.

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

TiDB chooses `t1_data` as the HashJoin Build side by default.

The estimated costs are:

```text
Default HashJoin, Build=t1_data: 230653.26
HashJoin, Build=t0_data: 269707.73
MergeJoin: 469769.82
```

Therefore, the faster reversed HashJoin is estimated to be about 16.9% more expensive, and MergeJoin is estimated to be about 2.04x more expensive than the default plan.

The measured runtimes are:

```text
Default HashJoin:
run 1: 219.6 ms
run 2: 240.2 ms
run 3: 218.8 ms
median: 219.6 ms

HashJoin Build=t0_data:
run 1: 7.51 ms
run 2: 5.24 ms
run 3: 3.18 ms
median: 5.24 ms

MergeJoin:
run 1: 4.02 ms
run 2: 3.79 ms
run 3: 3.73 ms
median: 3.79 ms
```

The reversed HashJoin Build side is therefore about:

```text
219.6 / 5.24 = 41.9x faster
```

despite having a higher estimated cost.

The MergeJoin control is about:

```text
219.6 / 3.79 = 57.9x faster
```

despite having an estimated cost about 2.04x higher than the default plan.

Even the slowest reversed-build execution, 7.51 ms, is about 29x faster than the fastest default execution, 218.8 ms. Therefore, the result cannot be explained by normal runtime noise or cache warming.

`EXPLAIN ANALYZE` for the default plan shows:

```text
Limit actRows: 2
HashJoin actRows: 308
t1_data Build actRows: 3361
t0_data Probe actRows: 3380
```

The default plan reads both inputs completely and spends most of its time in the HashJoin Probe phase while evaluating the cross-table residual condition.

The estimated Probe cardinality is displayed as approximately zero:

```text
TableReader on t0_data:
estRows = 0.00
actRows = 3380
```

This suggests that the LIMIT row-goal discount is derived from the Cartesian fanout before correctly accounting for the residual predicate.

For the default orientation, each `t0_data` Probe row has to be compared with the Build rows, but only the single `t1_data.c1 IS NULL` row produces a qualifying result.

For the reversed orientation, the qualifying NULL row is on the Probe side and can match the entire `t0_data` Build side at once, quickly producing enough rows for `LIMIT 2`.

The issue is specific to the combination of:

```text
Cartesian HashJoin
+ cross-table residual OR predicate
+ skewed qualifying rows
+ small LIMIT
+ Build/Probe orientation
```

Two controls support this conclusion:

1. When the predicate is changed to the pushable condition `a.c1 IS NULL`, TiDB uses an index range scan for the single NULL row and the query takes about 2.17 ms.

2. When the predicate is changed to `WHERE TRUE`, the pure Cartesian HashJoin takes about 2.77 ms.

Therefore, the slowdown is not caused by scanning these tables or by Cartesian HashJoin alone. It is caused by the interaction between the residual predicate, LIMIT costing, and the selected HashJoin orientation.

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

### Suggested fix

Consider making HashJoin orientation costing under a small LIMIT aware of residual-condition selectivity and of how qualifying matches are distributed across Probe rows.

The current cost appears to apply a LIMIT row-goal discount using the pre-residual Cartesian fanout, causing the Probe input to be estimated as approximately zero rows even though thousands of rows are read and a large number of Cartesian candidates are evaluated.

When comparing the two HashJoin orientations, the optimizer could estimate the expected number of qualifying output rows per Probe row after applying the residual predicate, rather than relying on the raw Cartesian fanout. The model should also include a lower bound for chunk-granularity and concurrent HashJoin work before a parent LIMIT can stop execution.

For this case, evaluating both Build-side alternatives with residual-aware LIMIT costing should favor `t0_data` as the Build side, or select the MergeJoin plan, instead of choosing the much slower default orientation.

Start by running tidb_limit_cartesian_hashjoin_build_side_repro.sql with optimizer comments preserved, then compare the EXPLAIN ANALYZE results for both HashJoin orientations and the MergeJoin control. Read the optimizer's LIMIT costing and Cartesian HashJoin build-side selection paths. Done means the cost ordering accounts for residual predicates and small LIMITs instead of selecting the much slower orientation.

Written by the indexing model from the issue text.