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
NOT IN subquery returns rows that must be filtered (wrong results) #16
Filed here after being triaged in a downstream repo — the wrong results are introduced by liquid cache itself, not by the consumer.
A NOT IN subquery returns rows that standard SQL semantics require to be filtered out, when the session is built with LiquidCacheLocalBuilder (liquid_cache_datafusion_local).
SELECT'a'AS c0 FROM t0
WHEREt0.a NOT IN (SELECTt1.aFROM t1 WHEREt1.sLIKE'a%')
Both tables are Parquet-backed; t1.a is nullable and the subquery's result set contains at least one NULL.
Configuration
Rows
Liquid cache enabled (LiquidCacheLocalBuilder)
109
Vanilla DataFusion, same pinned version, same Arrow batches in a MemTable
0
Same downstream engine with liquid cache disabled, same data/query/seed
0
0 rows is the correct answer: x NOT IN (set containing NULL) is never true under SQL tri-state semantics, so the whole result set is eliminated. Returning 109 rows is consistent with the NULLs being dropped or mishandled on the anti-join build side under liquid cache.
Notes from triage:
Reproduced by a differential fuzz suite (liquid-cache arm vs. vanilla DataFusion reference) on its first Linux run. Liquid cache is Linux-only, so this configuration had no differential coverage before.
The cold run and the warm run return the same wrong 109 rows. This is not a cache-population race — the liquid cache session state (its optimizer rules / scan path) is in play from the first execution.
The subquery side goes through the liquid cache scan, so the suspect surface is the same one recently touched by the reader fixes for row-filter/limit ordering and cache-sized batching, rather than the join operator itself.
Severity: silent wrong results in the default local configuration, with no error and no warning.
A regression test should cover a nullable column feeding a NOT IN / anti-join build side over a liquid-cache scan, and assert equality against the vanilla DataFusion plan on the same batches.
The premise in the issue body — that the wrong results are introduced by liquid cache — is wrong, and the "vanilla DataFusion" reference that supported it was misleading. That reference used a MemTable, which accepts no filter pushdown. Against the same parquet files, vanilla DataFusion is equally wrong:
Configuration
Rows
Liquid cache
109
Vanilla DataFusion over the same parquet files
109
Vanilla DataFusion over a MemTable
0
Either, with enable_join_dynamic_filter_pushdown = false
0
Liquid cache tracks vanilla parquet exactly in both settings.
A null-aware LeftAnti join's answer depends on what its probe side contains, not just on which probe rows match: a NULL probe key annihilates the output, and an empty probe side passes every build row through. HashJoinExec publishes a filter built from its build-side keys (key >= min AND key <= max AND key IN (..)) and pushes it into the probe scan. NULL satisfies no comparison, so probe NULLs are pruned before the join sees them, and a probe side with no matching keys prunes to nothing and reads as empty. EXPLAIN ANALYZE shows the whole probe file pruned: output_rows=0, files_ranges_pruned_statistics=1 total → 0 matched.
Upstream main has since added a null_aware guard, but it fires only when the build key is nullable, which misses the pruned-probe-NULL case entirely.
NullAwareJoinFilterGuard clears the dynamic filter from any hash join that is null-aware or whose probe subtree holds one. Per-node rather than plan-wide, so a join holding the anti join on its build side keeps its pruning — worth 400k rows scanned versus one in a measured case.
NullAwareValueFilterGuard suppresses TopK and aggregate dynamic filters plan-wide when the plan holds a null-aware join. These cannot be gated per node: a UNION ALL sibling drains before the anti join's probe side is read and tightens the shared filter already pushed into it.
What is NOT fixed — read before closing
This fixes bare NOT IN over a NULL-bearing subquery. It does not fix the wider class, and two holes remain that no physical rule can reach:
NOT IN plus any predicate on the join key is still wrong. Logical infer_join_predicates copies that predicate onto the subquery side, deleting the deciding NULL. 24/24 shapes tried are wrong, including AND t0.a IS NOT NULL (109 rows, expected 0), AND t0.a <> 1050, AND abs(t0.a) > 1050. Wrong in vanilla DataFusion over a MemTable with every dynamic filter disabled. Recorded as the #[ignore]d test not_in_with_any_predicate_on_the_join_key.
prefer_hash_join = false makes the planner emit a SortMergeJoinExec that drops null_aware outright, so both guards go inert.
Both belong upstream. A differential fuzz suite should expect failures in shape 1 in particular — it is a common query shape, so re-enabling a liquid-cache fuzz arm on the strength of this fix alone will produce findings.
Activity
zfarrell commentedon Aug 27, 2026
Root cause: DataFusion, not the cache
The premise in the issue body — that the wrong results are introduced by liquid cache — is wrong, and the "vanilla DataFusion" reference that supported it was misleading. That reference used a
MemTable, which accepts no filter pushdown. Against the same parquet files, vanilla DataFusion is equally wrong:MemTableenable_join_dynamic_filter_pushdown = falseLiquid cache tracks vanilla parquet exactly in both settings.
A null-aware
LeftAntijoin's answer depends on what its probe side contains, not just on which probe rows match: a NULL probe key annihilates the output, and an empty probe side passes every build row through.HashJoinExecpublishes a filter built from its build-side keys (key >= min AND key <= max AND key IN (..)) and pushes it into the probe scan. NULL satisfies no comparison, so probe NULLs are pruned before the join sees them, and a probe side with no matching keys prunes to nothing and reads as empty.EXPLAIN ANALYZEshows the whole probe file pruned:output_rows=0, files_ranges_pruned_statistics=1 total → 0 matched.Upstream
mainhas since added anull_awareguard, but it fires only when the build key is nullable, which misses the pruned-probe-NULL case entirely.What is fixed here
Two physical optimizer rules, added in #17:
NullAwareJoinFilterGuardclears the dynamic filter from any hash join that is null-aware or whose probe subtree holds one. Per-node rather than plan-wide, so a join holding the anti join on its build side keeps its pruning — worth 400k rows scanned versus one in a measured case.NullAwareValueFilterGuardsuppresses TopK and aggregate dynamic filters plan-wide when the plan holds a null-aware join. These cannot be gated per node: aUNION ALLsibling drains before the anti join's probe side is read and tightens the shared filter already pushed into it.What is NOT fixed — read before closing
This fixes bare
NOT INover a NULL-bearing subquery. It does not fix the wider class, and two holes remain that no physical rule can reach:NOT INplus any predicate on the join key is still wrong. Logicalinfer_join_predicatescopies that predicate onto the subquery side, deleting the deciding NULL. 24/24 shapes tried are wrong, includingAND t0.a IS NOT NULL(109 rows, expected 0),AND t0.a <> 1050,AND abs(t0.a) > 1050. Wrong in vanilla DataFusion over aMemTablewith every dynamic filter disabled. Recorded as the#[ignore]d testnot_in_with_any_predicate_on_the_join_key.prefer_hash_join = falsemakes the planner emit aSortMergeJoinExecthat dropsnull_awareoutright, so both guards go inert.Both belong upstream. A differential fuzz suite should expect failures in shape 1 in particular — it is a common query shape, so re-enabling a liquid-cache fuzz arm on the strength of this fix alone will produce findings.