planner: A MergeJoin estimated 13.7x more expensive runs 20.8x faster for LEFT JOIN with LIMIT 1

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

Run the attached reproduction script:

[tidb_limit_left_outer_join_hashjoin_repro.sql](https://github.com/user-attachments/files/30292384/tidb_limit_left_outer_join_hashjoin_repro.sql)

[tidb_limit_left_outer_join_hashjoin_repro_result.txt](https://github.com/user-attachments/files/30292385/tidb_limit_left_outer_join_hashjoin_repro_result.txt)

```bash
mysql -h 127.0.0.1 -P 4000 -u root < tidb_limit_left_outer_join_hashjoin_repro.sql
```

The script creates and analyzes three tables with the following data shape:

```text
t0: 172 rows
t1: 126 rows
t3: 8,700 rows
```

`t3.c2` contains no actual `NULL` values. The only join key present in `t0` and `t1` but missing from `t3` is `c0 = 0`.

Therefore, the 352 qualifying rows are NULL-extended rows produced by the second `LEFT JOIN`.

The tested query is:

```sql
SELECT
t1.c2 AS ref0,
t3.c3
FROM t1
LEFT JOIN t0
ON t1.c0 = t0.c0
LEFT JOIN t3
ON t0.c0 = t3.c0
WHERE t3.c2 IS NULL
LIMIT 1;
```

TiDB chooses a HashJoin plan for this query.

The control query forces MergeJoin:

```sql
SELECT /*+ MERGE_JOIN(t1, t0, t3) */
t1.c2 AS ref0,
t3.c3
FROM t1
LEFT JOIN t0
ON t1.c0 = t0.c0
LEFT JOIN t3
ON t0.c0 = t3.c0
WHERE t3.c2 IS NULL
LIMIT 1;
```

Both queries return the same result.

The script runs both plans three times with `EXPLAIN ANALYZE`.

The observed execution times are:

| Plan | Run 1 | Run 2 | Run 3 | Median |
| ---------------- | -------: | -------: | -------: | -------: |
| Default HashJoin | 118.0 ms | 109.7 ms | 118.2 ms | 118.0 ms |
| Forced MergeJoin | 5.43 ms | 5.76 ms | 5.67 ms | 5.67 ms |

The forced MergeJoin is approximately 20.8 times faster.

However, the estimated costs have the opposite ordering:

| Plan | Estimated cost |
| ---------------- | -------------: |
| Default HashJoin | 573,670.27 |
| Forced MergeJoin | 7,839,751.82 |

The MergeJoin is estimated to be approximately 13.7 times more expensive, despite running approximately 20.8 times faster.

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

The optimizer should rank candidate plans according to the amount of work required to produce the rows requested by the `LIMIT`.

For this query, the MergeJoin plan can produce the first qualifying row substantially earlier than the HashJoin plan.

Its estimated cost under `LIMIT 1` should reflect this lower first-row execution cost.

At minimum, a plan that runs approximately 20.8 times faster should not be estimated as approximately 13.7 times more expensive than the selected plan.

TiDB should either select the MergeJoin plan or assign the HashJoin plan a cost that reflects the work it must complete before it can emit the first qualifying row.

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

TiDB selects the HashJoin plan with the lower estimated cost:

```text
Default HashJoin:
estimated cost: 573,670.27
median runtime: 118.0 ms

Forced MergeJoin:
estimated cost: 7,839,751.82
median runtime: 5.67 ms
```

The estimated ranking is therefore the opposite of the observed runtime ranking.

`EXPLAIN ANALYZE` shows a large difference in the amount of work performed before the first result is returned.

For the selected HashJoin plan:

```text
Top HashJoin:
estRows: 1
actRows: 1,759,752
```

For the forced MergeJoin plan:

```text
Top MergeJoin:
estRows: 1
actRows: 1,024
```

The selected HashJoin produces all 1,759,752 top-level joined rows before the `Selection` returns the first qualifying row.

The MergeJoin produces only one 1,024-row batch before `LIMIT 1` stops execution.

The HashJoin plan also applies an aggressive row-goal reduction to its build-side estimates:

```text
t1 LEFT JOIN t0:
estRows: approximately 0
actRows: 2,710

t0 IndexFullScan:
estRows: approximately 0
actRows: 172

t1 IndexLookUp:
estRows: approximately 0
actRows: 126
```

In reality, all rows from `t0`, `t1`, and `t3` are consumed.

The qualifying rows are NULL-extended rows for the join key `c0 = 0`, which is missing from `t3`.

The selected HashJoin uses the preserved side of the second `LEFT JOIN` as its build side. It appears unable to emit unmatched build-side rows until the complete `t3` probe input has been consumed.

The MergeJoin processes the inputs in join-key order. Because `c0 = 0` is the lowest key and is missing from `t3`, the MergeJoin can detect the unmatched key and emit a NULL-extended row early.

The same behavior is visible with `LIMIT 64`:

```text
Default HashJoin:
runtime: 110.9 ms
top-level actRows: 1,759,752

Forced MergeJoin:
runtime: 4.98 ms
top-level actRows: 1,024
```

Increasing the limit from 1 to 64 does not reduce the work performed by the HashJoin.

As a control, without `LIMIT`, the HashJoin is faster:

```text
No LIMIT, default HashJoin: 107.2 ms
No LIMIT, forced MergeJoin: 442.9 ms
```

Without `LIMIT`, both plans produce all 1,759,752 joined rows.

This indicates that MergeJoin is not generally faster than HashJoin for this query. The cost misranking occurs specifically under `LIMIT`, where the optimizer needs to estimate the work required to produce the first qualifying rows.

The `LIMIT`-aware cost model appears not to account for the different timing with which HashJoin and MergeJoin can emit unmatched rows for a `LEFT OUTER JOIN`.

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

Additional optimizer settings:

```text
tidb_cost_model_version = 2
tidb_opt_advanced_join_hint = ON
```

### Difference from related issues

This issue is related to several existing LIMIT cost-model issues, but the trigger and the physical-plan behavior are different.

- In https://github.com/pingcap/tidb/issues/69963, the query contains a Cartesian join with a cross-table residual predicate. The main problem is
the choice of the HashJoin build/probe side under LIMIT. Both alternatives remain HashJoin-based plans. The current issue instead compares HashJoin with MergeJoin on equality-based LEFT OUTER JOINs, and the key difference is when unmatched outer rows can be emitted.

- In https://github.com/pingcap/tidb/issues/69564, the query uses `ORDER BY ... LIMIT`, and the two alternatives are different HashJoin join
orders under TopN. The faster join order evaluates a highly selective predicate earlier. The current query has no `ORDER BY` or TopN, does not
compare two HashJoin join orders, and the qualifying rows are NULL-extended unmatched rows.

- In https://github.com/pingcap/tidb/issues/69556, TiDB chooses a MergeJoin under LIMIT, while a HashJoin is faster. The post-join predicate returns no rows, so the chosen MergeJoin cannot terminate early. The current issue has the opposite plan and runtime direction: TiDB chooses HashJoin, while MergeJoin is about 20.8x faster because it can emit the first unmatched row early.

In the current case, `t3.c2` contains no actual NULL values. All qualifying rows are NULL-extended rows produced for the lowest join key that is missing from `t3`. The selected LEFT OUTER HashJoin processes all 1,759,752 top-level join rows before returning the first result, whereas MergeJoin returns after producing only 1,024 top-level rows.

Therefore, although these issues are all broadly related to LIMIT-aware costing, this issue specifically concerns the cost model not accounting for
the operator-specific timing of unmatched-row emission in a LEFT OUTER JOIN.

Start with tidb_limit_left_outer_join_hashjoin_repro.sql and run the supplied EXPLAIN ANALYZE comparisons for HashJoin and MergeJoin under LIMIT. Trace the LIMIT-aware costing for LEFT OUTER JOINs and how unmatched rows are emitted; done means the estimated ranking reflects first-row work and matches the observed behavior without regressing the no-LIMIT case.

Written by the indexing model from the issue text.