planner: enhance join elimination for CASE WHEN constant-false branches referencing LEFT JOIN tables

## Enhancement

### Background

In some real production queries, we observed that TiDB sometimes can eliminate a `LEFT JOIN` whose columns only appear in a **constant-false** branch of a `CASE WHEN` expression, but sometimes it cannot. This leads to multiple plans for similar SQLs, and the plan that fails to eliminate the unnecessary table becomes significantly slower.

After reducing the case, we found a specific pattern where:

- The `CASE WHEN` expression uses **constant conditions**, e.g. `'1'='2'` (always false), `'2'='2'` (always true);
- One branch (constant-false) references columns from table **T2**;
- Another branch (constant-true) references columns from table **T3**;
- From SQL semantics, T2 is “logically unreachable” and its `LEFT JOIN` can be eliminated;
- However, the current TiDB optimizer does **not** eliminate the `LEFT JOIN` on T2 in this pattern.

At the same time, in the “mirror” pattern (where the constant-false branch references T3), TiDB **can** eliminate the `LEFT JOIN` on T3. So TiDB is already capable of doing such optimization in some shapes, but still has limitations in others.

---

### Affected Versions

- **Observed in**: v6.5.11
- **Reproduced in**: v8.5.4

(Other versions have not been systematically tested yet.)

---

### Minimal Reproduction

#### 1. Test schema

```sql
-- Main table
CREATE TABLE base_tbl (
id INT PRIMARY KEY,
k INT
);

-- T2 table
CREATE TABLE t2 (
k INT PRIMARY KEY,
data_dt DATE
);

-- T3 table
CREATE TABLE t3 (
k INT PRIMARY KEY,
data_dt DATE
);

INSERT INTO base_tbl VALUES
(1, 10),
(2, 20),
(3, 30);

INSERT INTO t2 VALUES
(10, '2024-01-01'),
(20, '2024-02-01');

INSERT INTO t3 VALUES
(10, '2023-01-01'),
(20, '2023-02-01'),
(30, '2023-03-01');
```

#### 2. Scenario A – T3 can be eliminated (works well)

```sql
EXPLAIN
SELECT DISTINCT
CASE
WHEN '1' = '1' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '1' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END AS ym
FROM base_tbl b
LEFT JOIN t2 ON b.k = t2.k
LEFT JOIN t3 ON b.k = t3.k
WHERE
CASE
WHEN '1' = '1' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '1' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END <> '';
```

- Here `'1'='1'` is always **true**, `'1'='2'` is always **false**;
- The branch referencing **T3** is in the constant-false branch;
- TiDB successfully **eliminates the `LEFT JOIN` on T3**, and generates a good plan.

#### 3. Scenario B – T2 cannot be eliminated (current limitation)

```sql
EXPLAIN
SELECT DISTINCT
CASE
WHEN '1' = '2' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '2' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END AS ym
FROM base_tbl b
LEFT JOIN t2 ON b.k = t2.k
LEFT JOIN t3 ON b.k = t3.k
WHERE
CASE
WHEN '1' = '2' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '2' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END <> '';
```

- Here `'1'='2'` is always **false**, `'2'='2'` is always **true**;
- The branch referencing **T2** is in the constant-false branch;
- From SQL semantics, T2 is “unreachable”, so the `LEFT JOIN t2` is **logically redundant**;
- However, current TiDB **does not** eliminate the `LEFT JOIN` on T2 in this shape.

---

### Current Behavior & Analysis

Based on reading `planner/core/rule_join_elimination.go` and internal discussion:

- In Scenario B, before join reorder, the physical join order can be seen as:

```text
t3 JOIN (b LEFT JOIN t2)
```

- `t3` acts as the parent operator joining with `(b LEFT JOIN t2)`, and T3’s related columns are attached to the child as `parentCols`;
- During **outer join elimination**, TiDB uses checks like `IsColsAllFromOuterTable`:
- Only when all referenced columns (seen from the parent’s point of view) come from the **outer (left) table** can we safely eliminate the `LEFT JOIN`;
- In this specific join shape and with the current rule implementation, the condition is not satisfied, so the optimizer **conservatively keeps the `LEFT JOIN` on T2**.

In other words:

- From pure SQL semantics, the branch on T2 is constant-false, so **T2 is removable** in Scenario B;
- From the current implementation’s perspective, given the join shape and existing `IsColsAllFromOuterTable` rule, the optimizer **cannot safely prove** that removing `(b LEFT JOIN t2)` is always correct, and therefore chooses not to eliminate it.

We also did a rough comparison in Oracle:

- In similar queries, Oracle does **not** eliminate either side in both patterns (like Scenario A and B);
- TiDB is already more aggressive than some databases in Scenario A, but in Scenario B still behaves conservatively.

---

### Why This Is an Enhancement (Not a Bug)

- The query result is always **correct**; there is no wrong result or incorrect semantics;
- The limitation is in the **aggressiveness and coverage of join elimination**:
- Some logically safe cases (like Scenario B) are not yet optimized;
- TiDB chooses a conservative plan to avoid mis-optimization in more complex queries.

So this is more like:

> “**Optimizer is not yet smart enough** to remove a logically unreachable `LEFT JOIN` under this specific join shape and `CASE WHEN` structure”.

---

### Expected Behavior

Ideally, for queries like Scenario B:

- TiDB should be able to use constant folding and semantic analysis on `CASE WHEN` to detect that the branch referencing T2 is **constant-false**;
- Then, in the join elimination phase, it should be able to **safely remove** the `LEFT JOIN` on T2 (when it is proven that T2 only appears in such unreachable branches and does not affect other predicates);
- The resulting plan would be logically equivalent to:

```sql
SELECT DISTINCT
DATE_FORMAT(t3.data_dt, '%Ym') AS ym
FROM base_tbl b
LEFT JOIN t3 ON b.k = t3.k
WHERE
DATE_FORMAT(t3.data_dt, '%Ym') <> '';
```

---

### Proposed Enhancement

We’d like to propose an enhancement to TiDB’s optimizer:

1. **Strengthen constant folding / expression analysis for `CASE WHEN`**
- Detect when a `CASE WHEN` branch is **provably constant-false** using constant expressions like `'1'='2'`, and isolate the relevant column references.

2. **Enhance join elimination rules** (`rule_join_elimination`) to:
- Recognize that some table columns are only used in **unreachable branches** of expressions (e.g. `CASE WHEN` constant-false branches);
- When it is safe and all usages of a table are in such unreachable branches, allow eliminating that table’s `LEFT JOIN` even under more complex join shapes such as `t3 JOIN (b LEFT JOIN t2)`.

3. **Keep correctness as the first priority**
- This enhancement should be guarded by strict conditions to avoid affecting correctness in more complicated queries (especially with non-constant conditions, correlated subqueries, etc.);
- If needed, the enhancement can be introduced gradually and guarded by a session variable / experimental flag.

---

### Workaround for Users

Until the optimizer is enhanced, a practical workaround for users is to **rewrite SQL manually** when the constant-false condition is a true business constant. For Scenario B:

```sql
-- Original (not fully optimized by TiDB yet)
SELECT DISTINCT
CASE
WHEN '1' = '2' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '2' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END AS ym
FROM base_tbl b
LEFT JOIN t2 ON b.k = t2.k
LEFT JOIN t3 ON b.k = t3.k
WHERE
CASE
WHEN '1' = '2' THEN DATE_FORMAT(t2.data_dt, '%Ym')
WHEN '2' = '2' THEN DATE_FORMAT(t3.data_dt, '%Ym')
END <> '';

-- Manually simplified (logically equivalent if '1'='2' is truly constant-false)
SELECT DISTINCT
DATE_FORMAT(t3.data_dt, '%Ym') AS ym
FROM base_tbl b
LEFT JOIN t3 ON b.k = t3.k
WHERE
DATE_FORMAT(t3.data_dt, '%Ym') <> '';
```

This effectively “helps” the optimizer and avoids the unnecessary `LEFT JOIN t2`.

---

### Additional Info

- Cluster version where this was originally observed: **v6.5.11**
- Reproduced in: **v8.5.4**
- Join elimination implementation reference:
- `planner/core/rule_join_elimination.go` (e.g. `IsColsAllFromOuterTable` and related logic)