You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 6a3a84a
Browse filesBrowse the repository at this point in the historyBrowse files
`x NOT IN (<subquery>)` is UNKNOWN when x is absent from the subquery result and
that result holds a NULL, so a WHERE clause must drop the row. Correlated
subqueries returned wrong results instead, and two shapes failed to plan.
A null-aware hash join implements the three-valued semantics by scanning its
equi-join keys for NULLs. That only works when the whole predicate becomes those
keys. A correlation contributes a second key, and a non-equality correlation
leaves a residual filter the NULL scan cannot see:
* `DecorrelatePredicateSubquery` set `null_aware` on a `LeftAnti` join without
checking for a residual filter, so the join answered with two-valued logic
while ignoring the correlation, or failed to plan when the split left it with
two keys or none.
* The `LeftMark` path did check, but on failure it silently emitted a two-valued
mark join, where a subquery NULL marks FALSE rather than UNKNOWN.
`in_subquery_value_mark_join` already expresses `IN` as a three-valued CASE over
three plain mark joins, which needs no null-aware join and places no shape
restriction on the correlation. It was reachable only from a projection; this
commit routes the predicates a null-aware join cannot carry to it as well.
`build_join` now takes `strict_three_valued`, set for a `NOT IN` whose UNKNOWN is
observable. When it cannot produce a join with correct three-valued semantics it
returns `Ok(None)`, and the two callers fall back to the general rewrite. The
single-key, filter-free shape still takes the null-aware fast path, so the plans
that were already correct are unchanged.
This fixes all 18 query shapes in the report, including the six it proposed to
turn into planning errors. The `joins.slt` and `subquery.slt` plans this commit
updates are the correlated shape: both dropped every row because the subquery
held a NULL, even for outer rows whose correlated subquery is empty, where
`NOT IN` is TRUE.
The fallback plan is three mark joins where the null-aware path uses one. A
correlated equality could instead use a null-aware `LeftMark` join, which already
accepts a correlation key, and keep the single join; that is an optimization of
the shapes fixed here, not a correctness gap.
Closesapache#25347.
Co-Authored-By: Claude Opus 5 <[email protected]>
Claude-Session: https://claude.ai/code/session_01VCeMPyJNgAiFpGXaz5CxF3
Filter: NOT CASE WHEN __correlated_sq_2.mark THEN Boolean(true) WHEN __correlated_sq_3.mark OR Int32(3) IS NULL AND __correlated_sq_4.mark THEN Boolean(NULL) ELSE Boolean(false) END [id:Int32;N, grp:Int32;N, mark:Boolean;N, mark:Boolean;N, mark:Boolean;N]
0 commit comments