Skip to content

Commit 28f8113

Browse files
authored
test: JoinScan NOT IN null-safety regress (requires patched datafusion-proto) (#5006)
## Summary Adds `joinscan_notin_null_safety.sql` -- a multi-table regress test that exercises the JoinScan path's `NOT IN` handling against a NULL-bearing inner column. Asserts the result is 0 rows (matching SQL `NOT IN` three-valued logic -- any NULL in the inner key column poisons NOT IN under WHERE) and that the EXPLAIN does not show a swapped `RightAnti` (which would lose null-aware semantics). This PR is a regress test only. The pg_search-side null_aware code that makes the test return the correct answer is supplied by sister PR #5005 -- that PR adds the `null_aware` field on `JoinNode`, lifts `NOT IN` SubPlans to `JoinType::Anti` with `null_aware=true` in `wrap_with_semi_anti`, and routes through `LogicalPlanBuilder::join_detailed_with_options(.., null_aware=true)` in the translator. ## Why this regress test needs a patched `datafusion-proto` DataFusion fully implements null-aware NOT IN -- `LogicalPlan::Join` has a `null_aware: bool` field, the optimizer routes NOT IN with nullable join keys through it, the physical planner threads it down to `HashJoinExec`, and `HashJoinExec` tracks `probe_side_has_null` to emit zero rows when needed. All of this was added in [apache/datafusion#19635](apache/datafusion#19635) and ships in DataFusion 53.1.0. But that PR missed one symmetric proto change: `null_aware` was added to the **physical** `HashJoinExecNode` proto in `datafusion-proto` but **not** to the **logical** `JoinNode` proto. The encoder destructures `LogicalPlan::Join` with `..` and silently drops `null_aware` during serialization. ```protobuf // datafusion-proto-53.1.0/proto/datafusion.proto:244 (line is the JoinNode for the LogicalPlan) message JoinNode { LogicalPlanNode left = 1; LogicalPlanNode right = 2; // ... 6 more fields ... // null_aware MISSING -- should be `bool null_aware = 9;` } ``` ```rust // datafusion-proto-53.1.0/src/logical_plan/mod.rs:1316 LogicalPlan::Join(Join { left, right, on, filter, join_type, join_constraint, null_equality, .. // <- `null_aware` falls into "the rest" and is silently dropped }) => { /* serialize without null_aware */ } ``` **pg_search's JoinScan path serializes its `LogicalPlan` to bytes** for Postgres' parallel-execution leader/worker IPC (see `pg_search/src/scan/codec.rs::serialize_logical_plan`), so the round-trip drops the flag and reverts to the silent-wrong-answer behavior. The agg-on-join path (covered by #5005) does NOT serialize the LogicalPlan -- it executes in-process -- and is unaffected. Empirically demonstrated: | Configuration | Rows returned | |---|---| | Pure PG (correct ground truth) | 0 | | pg_search JoinScan ON, no proto patch | 89 (wrong -- `LeftAnti` semantics, not `NOT IN`) | | pg_search JoinScan ON, patched datafusion-proto | 0 (correct) | ## What the test covers The query is intentionally minimal -- single outer table, single inner list table with a NULL-bombed exclude set, single-column equi-key -- chosen to exercise: 1. JoinScan accepts the lifted `NOT IN` SubPlan (via #5005's `wrap_with_semi_anti` change). 2. JoinScan's translator routes `null_aware=true` through `LogicalPlanBuilder::join_detailed_with_options(.., null_aware=true)`. 3. JoinScan serializes the LogicalPlan via `datafusion_proto::bytes::logical_plan_to_bytes_with_extension_codec` for IPC. 4. The patched `datafusion-proto` preserves `null_aware` through round-trip. 5. DataFusion's physical `HashJoinExec` runs with `null_aware=true`, refuses the `LeftAnti`->`RightAnti` swap (guarded in `join_selection`), and returns 0 rows when the probe side has NULL. ## Dependencies (must land before this PR's CI passes) 1. **#5005** -- adds the pg_search-side `null_aware` plumbing and `wrap_with_semi_anti` lift. 2. **A published patched `datafusion-proto`** -- for example a `paradedb/datafusion-proto` fork containing the missing `null_aware` field on `LogicalPlan::JoinNode` plus the symmetric `to_proto` / `from_proto` code. Pinned via `[patch.crates-io]` in the workspace `Cargo.toml`. 3. **An upstream issue / PR at apache/datafusion** as a follow-up to #19635 -- to add the missing field to the logical Join proto. Filed as [apache/datafusion#22065](apache/datafusion#22065). Once merged and released, the `[patch.crates-io]` entry can be dropped. The proto patch itself is small (~10 lines): add `bool null_aware = 9` to `message JoinNode`, propagate `null_aware` through `to_proto`'s destructure, restore via `Join::try_new(.., null_aware: true)` in `from_proto` when set. Local fork at `/tmp/datafusion-fork` works empirically end-to-end; needs to be hosted somewhere reachable for CI. ## What CI will look like | State | CI result | |---|---| | #5005 not merged, no proto patch | Test returns `89` (wrong); CI fails. | | #5005 merged, no proto patch | Test returns `89` (wrong); CI fails. | | #5005 merged, proto patch in `Cargo.toml` `[patch.crates-io]` | Test returns `0` (correct); CI passes. | This PR is in the third state on the author's machine. To merge to main, the `[patch.crates-io]` `Cargo.toml` change needs to ship as a follow-up commit on this branch once the fork is published. Refs #4911 -- JoinScan NOT IN portion. The agg-on-join NOT IN portion is in #5005.
1 parent fb90b2b commit 28f8113

2 files changed

Lines changed: 240 additions & 0 deletions

File tree

Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,121 @@
1+
-- =====================================================================
2+
-- Regression: JoinScan lifts `NOT IN (SELECT col FROM t)` SubPlans into
3+
-- a DataFusion `LeftAnti` join with the `null_aware=true` flag set, so
4+
-- SQL three-valued NULL semantics are preserved.
5+
--
6+
-- SQL semantics: `x NOT IN (... NULL ...)` evaluates to UNKNOWN for any
7+
-- x that doesn't equal a non-NULL element. UNKNOWN in WHERE excludes
8+
-- the row. So a NOT IN against any list containing NULL excludes ALL
9+
-- outer rows.
10+
--
11+
-- Plain DataFusion `LeftAnti`: emit each left row where no
12+
-- `left.x = right.col` evaluates to TRUE. NULL right.col never
13+
-- matches, so it's silently ignored — equivalent to NOT EXISTS, which
14+
-- is *different* from NOT IN.
15+
--
16+
-- Pre-fix behavior: `wrap_with_semi_anti` lifted `is_anti = true`
17+
-- SubPlans into `JoinType::Anti` and the translator lowered them to
18+
-- plain `LeftAnti`. Result: silent wrong row counts (a query that
19+
-- should return 0 rows returned all the non-matching outer rows
20+
-- instead).
21+
--
22+
-- Post-fix behavior: `wrap_with_semi_anti` lifts `is_anti = true`
23+
-- SubPlans to `JoinType::Anti` with `null_aware=true`. The translator
24+
-- routes through `LogicalPlanBuilder::join_detailed_with_options(...,
25+
-- null_aware=true)`. DataFusion's HashJoinExec then tracks
26+
-- `probe_side_has_null` and emits zero rows when any inner key column
27+
-- is NULL, matching SQL three-valued logic.
28+
--
29+
-- DataFusion's null-aware mode is restricted to single-column
30+
-- equi-keys; multi-column `NOT IN`s are still skipped at lift time
31+
-- (Postgres' executor handles those correctly via three-valued logic).
32+
-- =====================================================================
33+
CREATE EXTENSION IF NOT EXISTS pg_search;
34+
SET work_mem = '1GB';
35+
DROP TABLE IF EXISTS jnns_items CASCADE;
36+
DROP TABLE IF EXISTS jnns_include_set CASCADE;
37+
DROP TABLE IF EXISTS jnns_exclude_set CASCADE;
38+
-- Outer: 100 rows, all with txt='match'.
39+
CREATE TABLE jnns_items (
40+
id bigint PRIMARY KEY,
41+
txt text NOT NULL
42+
);
43+
INSERT INTO jnns_items SELECT s, 'match' FROM generate_series(1, 100) s;
44+
-- Drives JoinScan engagement (single-table queries don't trigger it).
45+
CREATE TABLE jnns_include_set (
46+
id serial PRIMARY KEY,
47+
val bigint NOT NULL
48+
);
49+
INSERT INTO jnns_include_set (val) SELECT s FROM generate_series(1, 100) s;
50+
-- The NULL bomb: 11 vals (50..60) plus a single NULL row.
51+
-- Under SQL NOT IN, this NULL poisons every outer row's check.
52+
CREATE TABLE jnns_exclude_set (
53+
id serial PRIMARY KEY,
54+
val bigint -- NULLABLE
55+
);
56+
INSERT INTO jnns_exclude_set (val) SELECT s FROM generate_series(50, 60) s;
57+
INSERT INTO jnns_exclude_set (val) VALUES (NULL);
58+
CREATE INDEX jnns_items_idx ON jnns_items USING bm25 (id, txt)
59+
WITH (key_field=id, text_fields='{"txt":{"fast":true}}');
60+
CREATE INDEX jnns_include_set_idx ON jnns_include_set USING bm25 (id, val)
61+
WITH (key_field=id, numeric_fields='{"val":{"fast":true}}');
62+
CREATE INDEX jnns_exclude_set_idx ON jnns_exclude_set USING bm25 (id, val)
63+
WITH (key_field=id, numeric_fields='{"val":{"fast":true}}');
64+
ANALYZE jnns_items; ANALYZE jnns_include_set; ANALYZE jnns_exclude_set;
65+
-- =====================================================================
66+
-- Test 1 — Postgres ground truth (all custom scans off).
67+
-- Expected: 0 rows (NULL in jnns_exclude_set poisons NOT IN).
68+
-- =====================================================================
69+
SET paradedb.enable_custom_scan TO off;
70+
SET paradedb.enable_join_custom_scan TO off;
71+
SET paradedb.enable_aggregate_custom_scan TO off;
72+
SELECT COUNT(*) AS expected_zero
73+
FROM jnns_items
74+
WHERE id IN (SELECT val FROM jnns_include_set)
75+
AND id NOT IN (SELECT val FROM jnns_exclude_set)
76+
AND txt @@@ 'match';
77+
expected_zero
78+
---------------
79+
0
80+
(1 row)
81+
82+
-- =====================================================================
83+
-- Test 2 — JoinScan enabled. After the fix, should also return 0.
84+
-- Pre-fix this returned 89 (the LeftAnti-as-NOT-EXISTS answer) — wrong.
85+
-- Post-fix the NOT IN SubPlan is lifted to `JoinType::Anti` with
86+
-- `null_aware=true`, so DataFusion correctly returns 0 when the inner
87+
-- (jnns_exclude_set.val) contains a NULL.
88+
-- =====================================================================
89+
SET paradedb.enable_custom_scan TO on;
90+
SET paradedb.enable_join_custom_scan TO on;
91+
SELECT COUNT(*) AS joinscan_result FROM (
92+
SELECT jnns_items.id FROM jnns_items
93+
WHERE id IN (SELECT val FROM jnns_include_set)
94+
AND id NOT IN (SELECT val FROM jnns_exclude_set)
95+
AND txt @@@ 'match'
96+
ORDER BY jnns_items.id LIMIT 1000
97+
) sub;
98+
joinscan_result
99+
-----------------
100+
0
101+
(1 row)
102+
103+
-- =====================================================================
104+
-- Test 3 — `NOT EXISTS` rewrite is the safe acceleration path.
105+
-- Different SQL semantics by design (NOT EXISTS doesn't propagate
106+
-- UNKNOWN), so this can return non-zero rows even when NOT IN's answer
107+
-- is zero. Documenting the difference.
108+
-- =====================================================================
109+
SELECT COUNT(*) AS notexists_result FROM (
110+
SELECT jnns_items.id FROM jnns_items
111+
WHERE EXISTS (SELECT 1 FROM jnns_include_set i WHERE i.val = jnns_items.id)
112+
AND NOT EXISTS (SELECT 1 FROM jnns_exclude_set e WHERE e.val = jnns_items.id)
113+
AND txt @@@ 'match'
114+
ORDER BY jnns_items.id LIMIT 1000
115+
) sub;
116+
notexists_result
117+
------------------
118+
89
119+
(1 row)
120+
121+
DROP TABLE jnns_items, jnns_include_set, jnns_exclude_set CASCADE;
Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,119 @@
1+
-- =====================================================================
2+
-- Regression: JoinScan lifts `NOT IN (SELECT col FROM t)` SubPlans into
3+
-- a DataFusion `LeftAnti` join with the `null_aware=true` flag set, so
4+
-- SQL three-valued NULL semantics are preserved.
5+
--
6+
-- SQL semantics: `x NOT IN (... NULL ...)` evaluates to UNKNOWN for any
7+
-- x that doesn't equal a non-NULL element. UNKNOWN in WHERE excludes
8+
-- the row. So a NOT IN against any list containing NULL excludes ALL
9+
-- outer rows.
10+
--
11+
-- Plain DataFusion `LeftAnti`: emit each left row where no
12+
-- `left.x = right.col` evaluates to TRUE. NULL right.col never
13+
-- matches, so it's silently ignored — equivalent to NOT EXISTS, which
14+
-- is *different* from NOT IN.
15+
--
16+
-- Pre-fix behavior: `wrap_with_semi_anti` lifted `is_anti = true`
17+
-- SubPlans into `JoinType::Anti` and the translator lowered them to
18+
-- plain `LeftAnti`. Result: silent wrong row counts (a query that
19+
-- should return 0 rows returned all the non-matching outer rows
20+
-- instead).
21+
--
22+
-- Post-fix behavior: `wrap_with_semi_anti` lifts `is_anti = true`
23+
-- SubPlans to `JoinType::Anti` with `null_aware=true`. The translator
24+
-- routes through `LogicalPlanBuilder::join_detailed_with_options(...,
25+
-- null_aware=true)`. DataFusion's HashJoinExec then tracks
26+
-- `probe_side_has_null` and emits zero rows when any inner key column
27+
-- is NULL, matching SQL three-valued logic.
28+
--
29+
-- DataFusion's null-aware mode is restricted to single-column
30+
-- equi-keys; multi-column `NOT IN`s are still skipped at lift time
31+
-- (Postgres' executor handles those correctly via three-valued logic).
32+
-- =====================================================================
33+
34+
CREATE EXTENSION IF NOT EXISTS pg_search;
35+
SET work_mem = '1GB';
36+
37+
DROP TABLE IF EXISTS jnns_items CASCADE;
38+
DROP TABLE IF EXISTS jnns_include_set CASCADE;
39+
DROP TABLE IF EXISTS jnns_exclude_set CASCADE;
40+
41+
-- Outer: 100 rows, all with txt='match'.
42+
CREATE TABLE jnns_items (
43+
id bigint PRIMARY KEY,
44+
txt text NOT NULL
45+
);
46+
INSERT INTO jnns_items SELECT s, 'match' FROM generate_series(1, 100) s;
47+
48+
-- Drives JoinScan engagement (single-table queries don't trigger it).
49+
CREATE TABLE jnns_include_set (
50+
id serial PRIMARY KEY,
51+
val bigint NOT NULL
52+
);
53+
INSERT INTO jnns_include_set (val) SELECT s FROM generate_series(1, 100) s;
54+
55+
-- The NULL bomb: 11 vals (50..60) plus a single NULL row.
56+
-- Under SQL NOT IN, this NULL poisons every outer row's check.
57+
CREATE TABLE jnns_exclude_set (
58+
id serial PRIMARY KEY,
59+
val bigint -- NULLABLE
60+
);
61+
INSERT INTO jnns_exclude_set (val) SELECT s FROM generate_series(50, 60) s;
62+
INSERT INTO jnns_exclude_set (val) VALUES (NULL);
63+
64+
CREATE INDEX jnns_items_idx ON jnns_items USING bm25 (id, txt)
65+
WITH (key_field=id, text_fields='{"txt":{"fast":true}}');
66+
CREATE INDEX jnns_include_set_idx ON jnns_include_set USING bm25 (id, val)
67+
WITH (key_field=id, numeric_fields='{"val":{"fast":true}}');
68+
CREATE INDEX jnns_exclude_set_idx ON jnns_exclude_set USING bm25 (id, val)
69+
WITH (key_field=id, numeric_fields='{"val":{"fast":true}}');
70+
71+
ANALYZE jnns_items; ANALYZE jnns_include_set; ANALYZE jnns_exclude_set;
72+
73+
-- =====================================================================
74+
-- Test 1 — Postgres ground truth (all custom scans off).
75+
-- Expected: 0 rows (NULL in jnns_exclude_set poisons NOT IN).
76+
-- =====================================================================
77+
SET paradedb.enable_custom_scan TO off;
78+
SET paradedb.enable_join_custom_scan TO off;
79+
SET paradedb.enable_aggregate_custom_scan TO off;
80+
81+
SELECT COUNT(*) AS expected_zero
82+
FROM jnns_items
83+
WHERE id IN (SELECT val FROM jnns_include_set)
84+
AND id NOT IN (SELECT val FROM jnns_exclude_set)
85+
AND txt @@@ 'match';
86+
87+
-- =====================================================================
88+
-- Test 2 — JoinScan enabled. After the fix, should also return 0.
89+
-- Pre-fix this returned 89 (the LeftAnti-as-NOT-EXISTS answer) — wrong.
90+
-- Post-fix the NOT IN SubPlan is lifted to `JoinType::Anti` with
91+
-- `null_aware=true`, so DataFusion correctly returns 0 when the inner
92+
-- (jnns_exclude_set.val) contains a NULL.
93+
-- =====================================================================
94+
SET paradedb.enable_custom_scan TO on;
95+
SET paradedb.enable_join_custom_scan TO on;
96+
97+
SELECT COUNT(*) AS joinscan_result FROM (
98+
SELECT jnns_items.id FROM jnns_items
99+
WHERE id IN (SELECT val FROM jnns_include_set)
100+
AND id NOT IN (SELECT val FROM jnns_exclude_set)
101+
AND txt @@@ 'match'
102+
ORDER BY jnns_items.id LIMIT 1000
103+
) sub;
104+
105+
-- =====================================================================
106+
-- Test 3 — `NOT EXISTS` rewrite is the safe acceleration path.
107+
-- Different SQL semantics by design (NOT EXISTS doesn't propagate
108+
-- UNKNOWN), so this can return non-zero rows even when NOT IN's answer
109+
-- is zero. Documenting the difference.
110+
-- =====================================================================
111+
SELECT COUNT(*) AS notexists_result FROM (
112+
SELECT jnns_items.id FROM jnns_items
113+
WHERE EXISTS (SELECT 1 FROM jnns_include_set i WHERE i.val = jnns_items.id)
114+
AND NOT EXISTS (SELECT 1 FROM jnns_exclude_set e WHERE e.val = jnns_items.id)
115+
AND txt @@@ 'match'
116+
ORDER BY jnns_items.id LIMIT 1000
117+
) sub;
118+
119+
DROP TABLE jnns_items, jnns_include_set, jnns_exclude_set CASCADE;

0 commit comments

Comments
 (0)