planner: HashJoin plan is estimated 325x more costly but runs 3x faster than the chosen MergeJoin under LIMIT

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

I found a query where TiDB chooses a much slower MergeJoin plan under `LIMIT 8`.

The surprising part is that a hinted HashJoin plan is estimated to be about **325x more costly** than the chosen MergeJoin plan, but it actually runs about **3x faster**. This looks like a cost-model / cardinality-estimation mis-ranking under `LIMIT`.

The issue was reproduced on TiDB v8.5.0 with plan cache disabled and fresh statistics collected.

I will attach:

- `tidb_limit_hashjoin_cost_misrank_repro.sql`
- `tidb_limit_hashjoin_cost_misrank_result.txt`

Session variables:

```sql
SET SESSION tidb_enable_prepared_plan_cache = OFF;
SET SESSION tidb_enable_non_prepared_plan_cache = OFF;
SET SESSION tidb_analyze_version = 2;
````

The relevant session variables are:

```text
tidb_analyze_version = 2
tidb_enable_pseudo_for_outdated_stats = 0
tidb_opt_prefer_range_scan = 1
tidb_cost_model_version = 2
tidb_enable_prepared_plan_cache = 0
tidb_enable_non_prepared_plan_cache = 0
tidb_executor_concurrency = 5
tidb_merge_join_concurrency = 1
```

After loading the attached reproduction SQL, I ran:

```sql
ANALYZE TABLE t0 ALL COLUMNS;
ANALYZE TABLE t1 ALL COLUMNS;
ANALYZE TABLE t2 ALL COLUMNS;

SHOW STATS_META WHERE db_name = DATABASE();
SHOW STATS_HEALTHY WHERE db_name = DATABASE();
```

The statistics are healthy:

```text
t0 row_count = 123, modify_count = 0, healthy = 100
t1 row_count = 170, modify_count = 0, healthy = 100
t2 row_count = 219, modify_count = 0, healthy = 100
```

The problematic query is:

```sql
EXPLAIN FORMAT = 'verbose'
SELECT t0.c0 AS ref0, t0.c2
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0)
LIMIT 8;
```

TiDB chooses the following MergeJoin plan:

```text
Limit_16 estRows=8.00 estCost=430.49
└─Selection_17 estRows=8.00 estCost=430.49
└─MergeJoin_18 estRows=8.00 estCost=31.29
├─MergeJoin_26(Build) estRows=0.24 estCost=30.18
│ ├─IndexLookUp_47 on t0 using i0(c0) estRows=0.01
│ └─IndexLookUp_43 on t2 using i4(c0) estRows=0.01
└─IndexReader_23 on t1 using i2(c0) estRows=0.01
```

Then I ran:

```sql
EXPLAIN ANALYZE
SELECT t0.c0 AS ref0, t0.c2
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0)
LIMIT 8;
```

The chosen MergeJoin plan was consistently slower:

```text
Default MergeJoin plan:
- estimated cost: 430.49
- run 1: 64.4 ms, RU: 3.436781
- run 2: 65.0 ms, RU: 3.471537
- run 3: 63.8 ms, RU: 3.407590
- median runtime: about 64.4 ms
- top MergeJoin: estRows = 8, actRows = 183192
- inner MergeJoin: estRows = 0.24, actRows = 5388
```

The corresponding hinted HashJoin query is:

```sql
EXPLAIN FORMAT = 'verbose'
SELECT /*+ HASH_JOIN(t1, t2, t0) */ t0.c0 AS ref0, t0.c2
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0)
LIMIT 8;
```

The hinted HashJoin plan is estimated to be much more expensive:

```text
Limit_16 estRows=8.00 estCost=139870.06
└─Selection_17 estRows=8.00 estCost=139870.06
└─HashJoin_21 estRows=8.00 estCost=139470.86
├─IndexReader_25(Build) estRows=0.01 estCost=0.11
└─HashJoin_43(Probe) estRows=5387.40 estCost=30338.24
```

However, the hinted HashJoin plan is actually much faster:

```sql
EXPLAIN ANALYZE
SELECT /*+ HASH_JOIN(t1, t2, t0) */ t0.c0 AS ref0, t0.c2
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0)
LIMIT 8;
```

```text
Hinted HashJoin plan:
- estimated cost: 139870.06
- estimated cost ratio over chosen plan: 139870.06 / 430.49 = 324.9x
- run 1: 21.4 ms, RU: 2.187345
- run 2: 20.3 ms, RU: 2.141043
- run 3: 24.2 ms, RU: 2.184568
- median runtime: about 21.4 ms
- top HashJoin: estRows = 8, actRows = 183192
- inner HashJoin: estRows = 5387.40, actRows = 5388
```

So the plan estimated to be about **325x more costly** actually runs about **3x faster** than the plan chosen by TiDB.

I also checked the true cardinalities:

```sql
SELECT COUNT(*) AS join_t1_t2_t0_count
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0;

SELECT COUNT(*) AS original_filter_count
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0);
```

Result:

```text
join_t1_t2_t0_count = 183192
original_filter_count = 0
```

Therefore, `LIMIT 8` cannot stop early in this case, because the post-join `Selection` filters out all joined rows.

I also tested the query without `LIMIT`:

```sql
EXPLAIN ANALYZE
SELECT t0.c0 AS ref0, t0.c2
FROM t1
LEFT JOIN t2 ON t1.c0 = t2.c0
LEFT JOIN t0 ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0);
```

Without `LIMIT`, TiDB chooses HashJoin by default and estimates the join cardinality accurately:

```text
HashJoin estRows = 183171.60
HashJoin actRows = 183192
runtime = about 24.5 ms
```

This suggests that the bad plan choice is related to `LIMIT` row-goal costing. With `LIMIT 8`, the optimizer seems to over-favor the ordered-index MergeJoin plan, but the post-join filter prevents early termination.

In addition, if I ignore the join-key indexes, TiDB chooses a HashJoin/TableFullScan plan and runs much faster:

```sql
EXPLAIN ANALYZE
SELECT t0.c0 AS ref0, t0.c2
FROM t1 IGNORE INDEX(i2, i3)
LEFT JOIN t2 IGNORE INDEX(i4, i5) ON t1.c0 = t2.c0
LEFT JOIN t0 IGNORE INDEX(i0, i1) ON t2.c0 = t0.c0
WHERE ((t0.c2 < 0) OR (t2.c3 = '')) OR (t2.c2 = 0)
LIMIT 8;
```

This plan also uses HashJoin and runs in about 22 ms.

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

I expected TiDB to choose the faster HashJoin/TableFullScan plan, or at least not rank it as much more expensive than the chosen MergeJoin plan.

In this query, the `LIMIT 8` does not reduce the actual work, because the post-join `Selection` filters out all joined rows. The optimizer should not assume that the ordered-index MergeJoin plan can stop early after producing 8 qualifying rows.

A better plan is the HashJoin plan:

```text
Chosen MergeJoin:
- estimated cost: 430.49
- median runtime: about 64.4 ms
- top MergeJoin: estRows = 8, actRows = 183192

Hinted HashJoin:
- estimated cost: 139870.06
- median runtime: about 21.4 ms
- inner HashJoin estimate is accurate: estRows = 5387.40, actRows = 5388
```

The cost model should not estimate the faster HashJoin plan as about 325x more costly than the chosen MergeJoin plan.

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

[tidb_limit_hashjoin_cost_misrank_repro.sql](https://github.com/user-attachments/files/29532265/tidb_limit_hashjoin_cost_misrank_repro.sql)
[tidb_limit_hashjoin_cost_misrank_repro.txt](https://github.com/user-attachments/files/29532266/tidb_limit_hashjoin_cost_misrank_repro.txt)

TiDB chooses the MergeJoin plan under `LIMIT 8`.

The chosen plan severely underestimates the actual number of rows:

```text
Top MergeJoin:
- estRows = 8
- actRows = 183192

Inner MergeJoin:
- estRows = 0.24
- actRows = 5388
```

The hinted HashJoin plan is estimated to be about 325x more expensive, but it is consistently much faster:

```text
Chosen MergeJoin:
- estimated cost: 430.49
- runtime: 64.4 ms, 65.0 ms, 63.8 ms

Hinted HashJoin:
- estimated cost: 139870.06
- runtime: 21.4 ms, 20.3 ms, 24.2 ms
```

This looks like a cost-model / cardinality-estimation mis-ranking: `LIMIT` row-goal costing over-favors the ordered-index MergeJoin plan, while the post-join filter prevents early termination.

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

Release Version: v8.5.0
Edition: Community
Git Commit Hash: d13e52ed6e22cc5789bed7c64c861578cd2ed55b
Git Branch: HEAD
UTC Build Time: 2024-12-18 02:26:06
GoVersion: go1.23.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv

Start with the attached tidb_limit_hashjoin_cost_misrank_repro.sql and compare EXPLAIN ANALYZE results for the LIMIT and no-LIMIT queries. Read the optimizer cost-model and cardinality-estimation entry points related to LIMIT, MergeJoin, HashJoin, and post-join Selection. Done means the reproduced query no longer severely misranks the faster plan under LIMIT 8, with regression coverage for the reported behavior.

Written by the indexing model from the issue text.