planner: non-inner joins miss safe predicate derivation from DNF and equality conditions

## Enhancement

Affects: planner / predicate pushdown.

This issue tracks safe predicate-derivation gaps in `LogicalJoin.PredicatePushDown` for non-inner join shapes. It is independent of partial indexes and IndexJoin path selection. Those access-path choices can benefit from better predicate derivation, but they should not be tracked here.

All file:line references below are against master at commit `daa3eca390` and later.

### Summary

There are two main gaps, plus one DNF-specific subcase that should be tracked explicitly.

First, for `LeftOuterJoin` / `RightOuterJoin`, TiDB already handles plain top-level `EqualConditions`: the matching side can get `not(isnull(...))` and some equality-derived predicates through `outerJoinPropConst` / `PropConstForOuterJoin.propagateColumnEQ`. The matching side can also get some relaxed filters from ON DNF expressions through `DeriveOtherConditions`. However, the join's own `OtherConditions` are not passed through `ExtractFiltersFromDNFs`.

That means a common conjunct hidden inside an ON-condition DNF is not extracted before outer-join predicate derivation. If that common conjunct is a join key, it stays hidden in `OtherConditions` instead of becoming an `EqualCondition`, so later join-key-based derivations such as matching-side `IS NOT NULL` and equality-based propagation are missed.

Second, `AntiSemiJoin`, `LeftOuterSemiJoin`, and `AntiLeftOuterSemiJoin` set `nullSensitive=true` in `outerJoinPropConst`, so `PropConstForOuterJoin.propagateColumnEQ` returns early and skips all column-equality-based derivation. Some of that conservatism is required for `IN` / `NOT IN` / null-aware semantics, but it is too broad for plain, non-IN-derived `EQ` join keys.

Third, `AntiSemiJoin` has its own ON/subquery DNF blind spot: unlike `InnerJoin` / `SemiJoin`, it does not run `ExtractFiltersFromDNFs` over combined join conditions; unlike regular outer joins, it does not call `DeriveOtherConditions`. Therefore, a plain equality that is only present as a common DNF conjunct can remain hidden even if a later fix handles top-level plain `EQ`.

A related, finer gap inside the second item: `DeriveOtherConditions` skips right-side `not(isnull(...))` for `LeftOuterSemiJoin` / `AntiLeftOuterSemiJoin` with a blanket `continue` (see `pkg/planner/core/operator/logicalop/logical_join.go:2307`). The reason recorded in the adjacent comment is that `OtherConditions` may hold `EQ` conditions converted from `IN (subq)`. Those `IsEQCondFromIn` EQs are already routed to `OtherConditions` by `ExtractOnCondition` at `logical_join.go:1462`, so the blanket skip also discards derivation from non-IN `OtherConditions`. The fix for these two outer-semi types should be source-aware (per expression), not an on/off switch on that `continue`.

### 1. Minimal reproduce step (Required)

#### Case A: outer join `OtherConditions` DNF is not extracted

For a regular `LeftOuterJoin`, top-level equality is already handled:

```sql
SELECT *
FROM t t1
LEFT JOIN t t2
ON t1.e = t2.e;
```

But if the same equality is hidden as a common conjunct of a DNF in the join's own ON condition, it is not extracted by the outer-join branch:

```sql
SELECT *
FROM t t1
LEFT JOIN t t2
ON (t1.e = t2.e AND t2.a = 1)
OR (t1.e = t2.e AND t2.a = 2);
```

Logically, the ON condition can expose the common equality:

```sql
t1.e = t2.e AND (t2.a = 1 OR t2.a = 2)
```

For predicate derivation, the planner should be able to:

- keep `t1.e = t2.e` as a join equality;
- push the right-side ON predicate `t2.a = 1 OR t2.a = 2` to the right child;
- derive `not(isnull(t2.e))` for the matching/right side.

Currently, `LeftOuterJoin` / `RightOuterJoin` only call `ExtractFiltersFromDNFs` for incoming `predicates` from WHERE (`logical_join.go:206` and `:226`). They do not do the same for the join's own `OtherConditions`.

#### Case B: null-sensitive semi/anti joins skip equality propagation

The existing planner unit-test fixture already records conservative behavior for `AntiSemiJoin`:

```sql
SELECT *
FROM t t1
WHERE NOT EXISTS (
SELECT *
FROM t t2
WHERE t2.e = t1.e
);
```

In `pkg/planner/core/testdata/plan_suite_unexported_in.json`, this is the last case in `TestDeriveNotNullConds`. The corresponding expected output in `pkg/planner/core/testdata/plan_suite_unexported_out.json` currently records:

```text
Plan: Join{DataScan(t1)->DataScan(t2)}(test.t.e,test.t.e)->Projection
Left: []
Right: []
```

The right child does not receive `not(isnull(test.t.e))` even though right-side rows with `t2.e IS NULL` cannot make the `NOT EXISTS` subquery match.

The same skipped equality-propagation path can also miss broader matching-side filters:

```sql
SELECT *
FROM t t1
WHERE t1.e > 10
AND NOT EXISTS (
SELECT *
FROM t t2
WHERE t2.e = t1.e
);
```

For `t1.e > 10` rows, matching right-side rows must satisfy `t2.e > 10` and `t2.e IS NOT NULL`.

Note: column-equality propagation for null-sensitive join types currently has only one possible home. The standalone `LogicalJoin.ConstantPropagation` rule (`logical_join.go:519-547`) returns early for every type except `LeftOuterJoin` / `RightOuterJoin` / `InnerJoin` (its `default` branch), so it does not run for `AntiSemiJoin` / `LeftOuterSemiJoin` / `AntiLeftOuterSemiJoin` today. The only path these types reach is `outerJoinPropConst` → `PropConstForOuterJoin.propagateColumnEQ`, which is gated by `nullSensitive` (`pkg/expression/constant_propagation.go:887-889`). The fix should state which of these two locations it extends; see Notes.

#### Case C: anti semi join `OtherConditions` DNF is also not extracted

A plain `NOT EXISTS` subquery can also hide the correlation equality inside every DNF branch:

```sql
SELECT *
FROM t t1
WHERE NOT EXISTS (
SELECT *
FROM t t2
WHERE (t2.e = t1.e AND t2.a = 1)
OR (t2.e = t1.e AND t2.a = 2)
);
```

Logically, the subquery predicate can expose:

```sql
t2.e = t1.e AND (t2.a = 1 OR t2.a = 2)
```

For predicate derivation, the planner should be able to consider the plain equality separately from the branch filter, while still preserving `NOT EXISTS` semantics and excluding `IN` / `NOT IN` / null-aware cases.

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

For non-inner joins, TiDB should apply safe predicate derivation more precisely:

- For `LeftOuterJoin` / `RightOuterJoin`, run DNF common-filter extraction on the join's own `OtherConditions` where doing so preserves ON semantics. This should expose common join keys that are currently hidden inside DNF expressions, then let existing classification decide which matching-side filters can be pushed and which preserved-side filters must remain join conditions.
- For `AntiSemiJoin`, `LeftOuterSemiJoin`, and `AntiLeftOuterSemiJoin`, derive matching-side predicates from plain, non-IN-derived `EQ` conditions where it is semantically safe.

This includes matching-side `not(isnull())` and, where safe, other matching-side filters derived through the same plain equality key.

For `LeftOuterSemiJoin` / `AntiLeftOuterSemiJoin`, the right-side `not(isnull(...))` derivation from non-IN `OtherConditions` is currently blocked by the blanket `continue` at `logical_join.go:2307`; the fix should make that skip per-expression rather than per-join-type.

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

`InnerJoin` and `SemiJoin` combine join conditions and incoming predicates into `tempCond`, then call `ExtractFiltersFromDNFs(tempCond)`.

`LeftOuterJoin` and `RightOuterJoin` only call `ExtractFiltersFromDNFs` on incoming WHERE predicates. Their own ON `OtherConditions` are handled by `outerJoinPropConst` and `DeriveOtherConditions`, but neither step extracts common conjuncts from DNF into separate join conditions. `DeriveOtherConditions` can derive matching-side relaxed DNF filters, but it does not expose common join keys as `EqualConditions`.

`AntiSemiJoin`, `LeftOuterSemiJoin`, and `AntiLeftOuterSemiJoin` are treated as null-sensitive as a whole in `outerJoinPropConst`, so `PropConstForOuterJoin.propagateColumnEQ` is skipped entirely for these join types.

For `AntiSemiJoin`, the join's own `OtherConditions` also do not go through `ExtractFiltersFromDNFs` or `DeriveOtherConditions`, so DNF-hidden plain equality conditions need explicit coverage.

### 4. Scope: current behavior by join type

Verified against:

- `pkg/planner/core/operator/logicalop/logical_join.go`
- `pkg/expression/util.go`
- `pkg/expression/constant_propagation.go`
- `pkg/planner/core/testdata/plan_suite_unexported_in.json`
- `pkg/planner/core/testdata/plan_suite_unexported_out.json`

The planner `JoinType` enum has exactly seven values (`pkg/planner/core/base/plan_base.go:306-323`, pinned by an `init()` assertion at `:327-334`). There is no `RightOuterSemiJoin`, `AntiRightOuterSemiJoin`, or `FullJoin` to track. `LogicalApply` embeds `LogicalJoin` and reuses the `LeftOuterJoin` arm of `PredicatePushDown`, so it is covered transitively and is not a separate case.

| Join type | Current behavior | Gap to track in this issue |
| --- | --- | --- |
| `InnerJoin` | `EqualConditions`, `OtherConditions`, and incoming predicates go through `ExtractFiltersFromDNFs` together. | No for this issue. |
| `SemiJoin` | Shares the `InnerJoin` predicate-pushdown branch. | No for this issue. |
| `LeftOuterJoin` | Top-level `EqualConditions` are handled by `outerJoinPropConst`; WHERE predicates go through `ExtractFiltersFromDNFs`. | Yes: the join's own ON `OtherConditions` are not DNF-extracted before outer-join derivation. |
| `RightOuterJoin` | Symmetric with `LeftOuterJoin`. | Yes: same ON `OtherConditions` DNF extraction gap. |
| `AntiSemiJoin` | `outerJoinPropConst` marks it `nullSensitive`, so column-equality propagation is skipped. It also does not run `ExtractFiltersFromDNFs` or `DeriveOtherConditions` for its join `OtherConditions`. | Yes: derive right-side predicates for plain non-IN-derived `EQ`; never derive outer-side `IS NOT NULL`. Also cover DNF extraction for plain ON/subquery conditions. |
| `LeftOuterSemiJoin` | Also `nullSensitive`; many cases come from scalar `IN` and require three-valued NULL semantics. It follows the left-outer branch, so matching-side relaxed DNF filters may be derived, but matching-side `not(isnull(...))` from `OtherConditions` is skipped by the blanket `continue` at `logical_join.go:2307`. | Limited: derive only from plain non-IN-derived `EQ` / safely extracted DNF pieces, not from `IsEQCondFromIn` / `InOperand` conditions. |
| `AntiLeftOuterSemiJoin` | Also `nullSensitive`; `NOT IN` and null-aware anti join cases are unsafe. It follows the left-outer branch but can also use `NAEQConditions` for null-aware anti join. | Limited: derive only from plain non-IN-derived `EQ` / safely extracted DNF pieces, not from `IsEQCondFromIn`, `NAEQConditions`, or null-aware conditions. |

### Safety boundaries

- Do not derive from `NullEQ` (`<=>`). `NULL <=> NULL` can match.
- Do not derive from `NAEQConditions`.
- Do not derive from `IsEQCondFromIn` / `InOperand` conditions. `IN` / `NOT IN` conditions can be empty-aware or null-aware, and filtering inner-side `NULL` values can change `NULL` vs `FALSE` results.
- Do not derive preserved/outer-side `IS NOT NULL` for outer or anti joins. For example, `NOT EXISTS (... WHERE t2.e = t1.e)` still returns the outer row when `t1.e IS NULL`.
- Do not push preserved-side ON filters below an outer join as scan filters. For `LeftOuterJoin`, left-side ON filters must remain join conditions; for `RightOuterJoin`, right-side ON filters must remain join conditions.
- Do not extract a join key from DNF unless the condition is present in every branch after the supported normalization. For example, `(t1.e = t2.e AND t2.a = 1) OR t2.a = 2` must not become an `EqualCondition`.
- Do not let the new DNF-extraction path change the evaluation count of mutable / side-effecting predicates. `ExtractFiltersFromDNFs` (`pkg/expression/util.go:1201-1218`) does not itself check `IsMutableEffectsExpr`; it identifies common conjuncts by `HashCode()`. A common conjunct such as `rand() > 0.5` extracted out of `(rand() > 0.5 AND t2.a = 1) OR (rand() > 0.5 AND t2.a = 2)` would be evaluated once instead of once per branch. The new outer-join `OtherConditions` extraction must skip common conjuncts that contain mutable / side-effecting expressions (the existing `DeriveOtherConditions` and `ExtractOnCondition` already skip mutable expressions via `IsMutableEffectsExpr` at `logical_join.go:2184`, `:1506`, and `:1513`).
- Do not remove the existing null-sensitive guard wholesale. The fix should be source-aware and only add derivations that are known to be plain equality semantics.

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

Master (commit `daa3eca390` and later).

### Notes / proposed direction

Handle the gaps separately:

1. For regular outer joins, consider applying `ExtractFiltersFromDNFs` to the join's own `OtherConditions` before `outerJoinPropConst`, then reclassify extracted conditions with the existing `AttachOnConds` / `extractOnCondition` logic. Keep preserved-side ON predicates as join conditions; only matching-side ON predicates can be pushed to the matching child.
2. For null-sensitive semi/anti joins, keep the current conservative behavior for `IN` / `NOT IN` / null-aware paths, but add a narrow derivation path for ordinary equality conditions.
3. For `AntiSemiJoin`, explicitly handle safe DNF extraction from join/subquery `OtherConditions` so DNF-hidden plain equality keys are not missed after the top-level plain `EQ` path is fixed.
4. For `LeftOuterSemiJoin` / `AntiLeftOuterSemiJoin`, replace the blanket `continue` at `logical_join.go:2307` with a per-expression check: skip `not(isnull(...))` derivation only when the `OtherConditions` entry is `IsEQCondFromIn` or contains an `InOperand` column, and derive for ordinary non-IN expressions.

For the null-sensitive semi/anti path:

- Source conditions: plain `EQ` in `EqualConditions`, and possibly plain `EQ` extracted from safe DNF pieces.
- Exclude `NullEQ`, `NAEQConditions`, and any expression for which `expression.IsEQCondFromIn(expr)` is true or either side carries `InOperand`.
- Destination side: matching/right child only for `AntiSemiJoin`, `LeftOuterSemiJoin`, and `AntiLeftOuterSemiJoin`.
- Reuse existing null-rejection checks where possible for `not(isnull(...))`.
- Choose the derivation home explicitly. Two options exist today:
- Extend `LogicalJoin.ConstantPropagation` (`logical_join.go:519-547`) to handle null-sensitive types instead of `default: return`. This rule runs before `PPDSolver` in `pkg/planner/core/optimizer.go`, so derivations would be visible to later pushdown.
- Split `PropConstForOuterJoin.propagateColumnEQ` (`pkg/expression/constant_propagation.go:886-941`) so the safe subset runs while the `nullSensitive` early return still blocks the unsafe subset.
- Pick one and record the choice in the implementing PR; mixing both risks double derivation.
- `pkg/planner/cascades/old/transformation_rules.go:903` also computes `nullSensitive` but omits `AntiSemiJoin`, which is inconsistent with the main path at `logical_join.go:1969`. That directory is currently dead code (no production import of `pkg/planner/cascades/old`), so it is out of scope for this issue; do not use it as a reference when implementing, and do not copy its `nullSensitive` set.

Add regression tests for:

- positive `LeftOuterJoin` / `RightOuterJoin` ON-DNF extraction where a common join key is hidden inside all DNF branches;
- positive matching-side filter pushdown from an extracted outer-join ON DNF, while confirming preserved-side ON filters are not pushed below the outer join;
- positive `AntiSemiJoin` / `NOT EXISTS` right-side `not(isnull(...))`;
- positive derived right-side filter through a plain equality key, for example `t1.e > 10` plus `t2.e = t1.e`;
- positive `AntiSemiJoin` / `NOT EXISTS` DNF extraction where a plain equality is hidden inside all DNF branches;
- positive `LeftOuterSemiJoin` / `AntiLeftOuterSemiJoin` right-side `not(isnull(...))` from a non-IN `OtherCondition`;
- negative scalar `IN` / `NOT IN` cases with nullable operands;
- negative `NullEQ` (`<=>`) and `NAEQConditions` cases;
- negative DNF cases where the join key is not common to every branch;
- negative preserved-side ON filter pushdown for outer joins;
- negative mutable-predicate cases, including the DNF-extraction form `(rand() > 0.5 AND t2.a = 1) OR (rand() > 0.5 AND t2.a = 2)` to ensure evaluation count is not reduced.

Start in pkg/planner/core/operator/logicalop/logical_join.go, especially PredicatePushDown, outerJoinPropConst, and the continue near line 2307. Read ExtractFiltersFromDNFs in pkg/expression/util.go and propagation in pkg/expression/constant_propagation.go, then run TestDeriveNotNullConds using the cited plan-suite fixtures. Done means coverage for the outer, semi, anti, and DNF cases while preserving the listed NULL and IN safety boundaries.

Written by the indexing model from the issue text.