planner: x BETWEEN y AND y and x >= y AND x <= y cause large performance regression due to missing equi-join extraction

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

Run the attached reproduction file:

[tidb_range_join_predicate_repro.sql](https://github.com/user-attachments/files/30164089/tidb_range_join_predicate_repro.sql)

[tidb_range_join_predicate_repro_result.txt](https://github.com/user-attachments/files/30164091/tidb_range_join_predicate_repro_result.txt)

The script creates and analyzes three tables containing 6,000 rows each.

The data is constructed so that:

* `t3.c0 = t1.c1` produces 6,000 matching rows.
* `t3.c1 = t2.c1` produces 10 matching rows.
* The final three-table join produces 10 rows.

The script compares the following three logically equivalent queries.

Equality reference:

```sql
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 = t2.c1
WHERE t2.c1 <= 1596112929;
```

`BETWEEN` form:

```sql
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 BETWEEN t2.c1 AND t2.c1
WHERE t2.c1 <= 1596112929;
```

Paired range form:

```sql
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2
ON t3.c1 >= t2.c1
AND t3.c1 <= t2.c1
WHERE t2.c1 <= 1596112929;
```

All three queries return the same result:

```text
+--------------------+-------------+----------+
| variant | result_rows | checksum |
+--------------------+-------------+----------+
| EQUALITY_REFERENCE | 10 | 40000110 |
| BETWEEN_TEST | 10 | 40000110 |
| GE_LE_TEST | 10 | 40000110 |
+--------------------+-------------+----------+
```

The tables were analyzed immediately before the queries were executed:

```text
+------------+--------------+-----------+
| Table_name | Modify_count | Row_count |
+------------+--------------+-----------+
| t1 | 0 | 6000 |
| t2 | 0 | 6000 |
| t3 | 0 | 6000 |
+------------+--------------+-----------+
```

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

TiDB should recognize the following equality-equivalent predicates:

```sql
x BETWEEN y AND y
```

and:

```sql
x >= y AND x <= y
```

as equivalent to:

```sql
x = y
```

The predicates should be normalized before join-condition extraction, allowing the optimizer to construct an equi-join key such as:

```text
equal:[eq(t3.c1, t2.c1)]
```

The resulting physical plan does not have to be identical to the equality reference, but it should avoid a Cartesian join and have comparable performance.

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

For the equality reference, TiDB correctly extracts both equality predicates as equi-join keys:

```text
HashJoin
inner join,
equal:[eq(t3.c0, t1.c1)]

HashJoin
inner join,
equal:[eq(t2.c1, t3.c1)]
```

The equality reference returned 10 rows with the following execution times:

```text
run 1: 13.4 ms
run 2: 7.92 ms
run 3: 5.65 ms
median: 7.92 ms
```

For the `BETWEEN` form, TiDB expands the predicate into `GE` and `LE` expressions but does not recognize that the lower and upper bounds are identical.

The condition is kept as a residual predicate on a Cartesian Hash Join:

```text
HashJoin
CARTESIAN inner join,
other cond:
ge(t3.c1, t2.c1),
le(t3.c1, t2.c1)
```

The plan estimates that the Cartesian join returns 11,964,000 rows, while the actual output contains only 10 rows:

```text
estRows: 11964000.00
actRows: 10
```

The `BETWEEN` query required:

```text
run 1: 2.26 s
run 2: 1.94 s
run 3: 2.08 s
median: 2.08 s
```

The median runtime is approximately:

```text
2.08 s / 7.92 ms = 262.6x
```

slower than the equality reference.

The explicit paired range form:

```sql
t3.c1 >= t2.c1
AND t3.c1 <= t2.c1
```

produces the same plan shape:

```text
HashJoin
CARTESIAN inner join,
other cond:
ge(t3.c1, t2.c1),
le(t3.c1, t2.c1)
```

This shows that the issue is not specific to the surface syntax of `BETWEEN`. Both forms reach the same internal pair of range conditions, but TiDB does not collapse them into an equality predicate before equi-join extraction.

The other join condition remains a normal non-Cartesian join:

```text
MergeJoin
inner join,
left key:t3.c0,
right key:t1.c1
```

Therefore, the Cartesian join is isolated to the failure to normalize the equality-equivalent range predicate.

#### Suggested fix direction

Before extracting join keys, normalize predicates with identical lower and upper bounds.

For example:

```sql
x BETWEEN y AND y
```

should be normalized to:

```sql
x = y
```

Similarly:

```sql
x >= y AND x <= y
```

should be normalized to:

```sql
x = y
```

The resulting equality expression should participate in equi-join condition extraction so that Hash Join, Merge Join, or Index Join can use it as a join key instead of evaluating it as a residual condition on a Cartesian join.

Regression tests should cover:

* `x BETWEEN y AND y`
* `x >= y AND x <= y`
* NULL values, to verify that SQL three-valued semantics are preserved
* Operand-order variants such as `y <= x AND y >= x`

#### Relation to #69933

This issue is related to #69933 because both cases fail to extract an equality-equivalent predicate as an equi-join key.

However, the missing normalization rules are different.

Issue #69933 concerns negation elimination:

```text
NOT (x <> y) -> x = y
```

This issue concerns collapsing identical lower and upper range bounds:

```text
x BETWEEN y AND y -> x = y

x >= y AND x <= y -> x = y
```

Although both issues have the same downstream symptom—a Cartesian join with a residual condition—they involve distinct predicate rewrite paths and should be tracked separately.

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

Relevant optimizer settings:

```text
tidb_opt_enable_hash_join = ON
tidb_hash_join_version = legacy
tidb_cost_model_version = 2
```

Run the attached tidb_range_join_predicate_repro.sql reproduction and compare its equality, BETWEEN, and paired-range plans. Trace predicate normalization and equi-join extraction, then add regression coverage for both forms, operand order, and NULL semantics; done means the equivalent predicates produce usable join keys without a Cartesian join.

Written by the indexing model from the issue text.