Skip to content

Commit 132234d

Browse files
viiryaIsaac
andauthored
perf: read NOT NULL tuple elements of a multi-column NOT IN as scope keys (apache#26214)
## Which issue does this PR close? - Closes apache#26088. ## Rationale for this change A multi-column `(a, b) NOT IN (SELECT x, y ...)` plans as a null-aware hash join that decides UNKNOWN per build row. Without correlation keys, every row with a NULL in any tuple element is paired with every row on the other side, so the NULL path costs O(rows x NULL-valued rows). A tuple element that cannot be NULL in scope on either side never makes the tuple comparison UNKNOWN: against a definite value it is either equal, which leaves the other elements to decide, or a definite mismatch, which makes the comparison FALSE. It only selects which rows a NULL in another element is compared with, which is exactly what a correlation scope key does. For example, with `b` and `y` NOT NULL, a NULL `a` only needs to be compared with the subquery rows whose `y` equals its `b`. ## What changes are included in this PR? - **Decorrelation** (`build_join`): for a null-aware tuple, the elements are partitioned by `may_be_null_in_scope`, keeping their order within each group. The elements that can be NULL come first and are the `NOT IN` value keys (`null_aware_value_keys` counts only them); the others follow as scope keys, before any correlation keys. The "value keys first" layout is unchanged, so the hash join already narrows the NULL candidate pairs through its scope hash map. - All elements NOT NULL: the join is not null-aware, as before. - Exactly one nullable element: `V = 1`, a scalar `NOT IN` on that element correlated by the others. - `LeftMark` (projection / `OR`) is reordered the same way and stays three-valued exact. - A NOT NULL element that is not a hashable equi-join key now stays in the join filter like any correlation instead of being rejected. A nullable one is still rejected. - An element of the `Null`, dictionary, run-end encoded or union type, or one that reads a column of such a type (e.g. under a coercion cast), always stays a value key: an array of these types can hold a NULL without a null bit, so its field's nullability does not rule out a NULL. A run-end encoded element is therefore still rejected, as before. - **Hash join** (`not_in.rs`): the hash table over the build rows' scope keys is now built on the first probe row with a NULL value key, instead of with the build side. Only such rows look it up, so a probe side without NULLs no longer pays for a second hash table over the build side. Since this PR sends more multi-column `NOT IN`s through the scope-key path, this keeps the no-NULL case (Q10) at the cost of `main`. - A cross product with marks on the build rows fills chunks of a batch with unmarked build rows. It keeps the first batch of probe rows for all chunks and produces the rest again, from the first of them, only for a chunk that those leave with unmarked rows, rather than collecting every build row index or every probe index first. - If the memory pool declines either scope map (the lazy one over all build rows, or the one over the NULL-valued build rows), the join pairs those rows with every row on the other side and checks scope equality per pair, as the all-value-key layout does, instead of failing the query. A declined lazy scope map is not cached: the next probe batch with a NULL value key tries to reserve it again. - When the sampled estimate rejects pairing by hash groups, the per-row lookups now stop once they waste as many matches again; the remaining rows are then sorted by hash and paired by group. This covers a hot scope value among rare ones, which the estimate alone misses. - A volatile element is never made a scope key by a filter proof: `key_may_be_null_in_scope` returns true for a volatile key. This also fixes the scalar `NOT IN` with a volatile expression, which uses the same check. - Docs: `Join::null_aware_value_keys`, `HashJoinExec::null_aware_value_keys` and the 56.0.0 upgrade guide describe the new key layout. - The Q11 / Q12 comments of the `null_aware_join` benchmark now describe the scope-key path: their `z` column (`value % 1000`) is NOT NULL. A new Q13 covers a low-cardinality scope key: `(o.id_n50, o.k) NOT IN (...)` with a NOT NULL `k` of 16 distinct values. A new Q14 covers a hot scope value: `hk` holds one value for 3/4 of the rows and rare values for the rest. ## What is the testing strategy for this PR? - `null_aware_anti_join.slt`: EXPLAIN plans for the reordering, with a correlation, with two nullable elements and one NOT NULL element, a constant element, NOT NULL on the outer side only (not reordered), a mark join, a nullable element made a scope key by a correlated filter that rejects NULL, and a dictionary under a coercion cast on either side (stays a value key). - `null_aware_multi_column.slt` (batch size matrix): results with NOT NULL elements for `LeftAnti` and `LeftMark`, correlated subqueries, two nullable elements and a scope key, a constant element, a run-end encoded element (rejected), and a nullable element made a scope key by a correlated filter that rejects NULL (NULL scope keys reach the join). - `null_aware_join_fuzz.rs`: NOT NULL columns on both sides and seven mixed shapes, compared with the `NOT EXISTS ... IS NOT FALSE` reference. Placing the NOT NULL elements first fails this fuzz on its first seed. - `hash_join/exec.rs`: a unit test that the scope map is only built (and charged to `build_mem_used`) when the probe side has a NULL value key and that the join falls back when the memory pool declines it, a test of the same fallback for the scope map over the NULL-valued build rows, and a test with more NULL-valued probe rows in one batch than the batch size, whose rows past the first batch decide build rows. - `not_in.rs`: `hot_scope_among_rare_ones_is_grouped` bounds the matches walked for a hot scope value among rare ones, for marks on both sides, and `declined_scope_map_is_built_once_memory_is_free` checks that a declined scope map is built once the pool has room. - `decorrelate_predicate_subquery.rs`: a volatile key with a NULL-rejecting correlation stays null-aware. - `core/tests/sql/joins.rs`: dictionary and `Null` elements under inferred non-nullable fields keep the tuple result UNKNOWN. - `not_in.rs`: `cross_product_chunks_the_build_rows` bounds the build rows held at once and counts the walks of the probe rows and the rows they read, including when exactly a batch of probe rows leaves a chunk unmarked, and `cross_product_fills_chunks_with_unmarked_build_rows` bounds the pairs when few build rows are left unmarked. - `roundtrip_logical_plan.rs`: the existing multi-column case had only NOT NULL `VALUES` columns for `b`/`y`, so its `b` now holds a NULL and the case keeps covering two value keys. Benchmark `null_aware_join` against `main` (criterion mean, average of alternating runs): | NAJ_ROWS | Q10 (no NULL) | Q11 (1% NULL in subquery) | Q12 (1% NULL in outer) | Q13 (16-value scope key) | |---|---|---|---|---| | 10000 | 0.32 ms -> 0.34 ms | 3.31 ms -> 0.35 ms | 0.48 ms -> 0.36 ms | 78.8 ms -> 0.80 ms | | 20000 | 0.38 ms -> 0.38 ms | 6.10 ms -> 0.43 ms | 0.56 ms -> 0.44 ms | 162 ms -> 0.99 ms | Q01 to Q09 are unchanged. Q14 (10k rows, `ci` profile) takes 23 ms, against 899 ms with the grouping rule before the hot-scope fix and 709 ms with both elements as value keys. Q13 depends on apache#25679: before it, the same shape took 20.5 / 41.3 ms on this branch. ## Are there any user-facing changes? Faster multi-column `NOT IN` when some tuple elements are NOT NULL. Plans show fewer `NOT IN` value keys (`null_aware(value_keys=N)`) for such tuples. No API change. --------- Co-authored-by: Isaac <[email protected]>
1 parent 2f4d087 commit 132234d

17 files changed

Lines changed: 1505 additions & 186 deletions

File tree

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q11.benchmark‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,9 +25,10 @@ expect_plan HashJoinExec
2525

2626
run
2727
-- Q11: uncorrelated multi-column NOT IN with 1% NULL in one subquery element.
28-
-- Each NULL-valued subquery row is paired with every outer row and the pair is
29-
-- kept as UNKNOWN only when the other element matches, so the cost grows with
30-
-- (outer rows) x (NULL-valued subquery rows). Compare with Q10 and Q12.
28+
-- `z` is NOT NULL on both sides, so the join reads it as a scope key: each
29+
-- NULL-valued subquery row is paired only with the outer rows with the same
30+
-- `z`, through a hash lookup, so the cost grows with (NULL-valued subquery
31+
-- rows) x (outer rows per `z` value). Compare with Q10 and Q12.
3132
SELECT count(*)
3233
FROM small_outer o
3334
WHERE (o.id_n0, o.z) NOT IN (SELECT i.id_n1, i.z FROM small_inner i);

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q12.benchmark‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -26,10 +26,10 @@ expect_plan HashJoinExec
2626

2727
run
2828
-- Q12: uncorrelated multi-column NOT IN with 1% NULL in one outer element,
29-
-- the mirror of Q11. Each NULL-valued outer row is paired with every subquery
30-
-- row and is UNKNOWN as soon as the other element of one of them matches: the
31-
-- cost grows with (NULL-valued outer rows) x (subquery rows). Compare with Q10
32-
-- and Q11.
29+
-- the mirror of Q11. `z` is NOT NULL on both sides, so the join reads it as a
30+
-- scope key: each NULL-valued outer row is paired only with the subquery rows
31+
-- with the same `z`, so the cost grows with (NULL-valued outer rows) x
32+
-- (subquery rows per `z` value). Compare with Q10 and Q11.
3333
SELECT count(*)
3434
FROM small_outer o
3535
WHERE (o.id_n1, o.z) NOT IN (SELECT i.id_n0, i.z FROM small_inner i);
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
name Q13
2+
group null_aware_join
3+
4+
load sql_benchmarks/null_aware_join/init/load.sql
5+
6+
# Correctness canary: the NOT IN result must match a reference count that does
7+
# not use NOT IN. It holds for every NAJ_ROWS. `k` is NOT NULL on both sides, so
8+
# a subquery row makes an outer tuple UNKNOWN or TRUE exactly when `k` matches
9+
# and `id_n50` is NULL on either side or equal; the row is kept only when no
10+
# such subquery row exists.
11+
assert I
12+
SELECT count(*) = (
13+
SELECT count(*)
14+
FROM small_outer o
15+
WHERE NOT EXISTS (
16+
SELECT 1 FROM small_inner i
17+
WHERE i.k = o.k
18+
AND (o.id_n50 IS NULL OR i.id_n50 IS NULL OR i.id_n50 = o.id_n50)
19+
)
20+
)
21+
FROM small_outer o
22+
WHERE (o.id_n50, o.k) NOT IN (SELECT i.id_n50, i.k FROM small_inner i);
23+
----
24+
true
25+
26+
expect_plan HashJoinExec
27+
28+
run
29+
-- Q13: uncorrelated multi-column NOT IN whose NOT NULL element `k` has only
30+
-- 16 distinct values, with 50% NULL in the other element on both sides. `k` is
31+
-- a scope key, so each scope holds 1/16 of each side and most of its rows are
32+
-- NULL-valued: the join must not keep pairing rows whose `NOT IN` is already
33+
-- known to be UNKNOWN. Compare with Q11 and Q12, whose scope key `z` has 1000
34+
-- distinct values.
35+
SELECT count(*)
36+
FROM small_outer o
37+
WHERE (o.id_n50, o.k) NOT IN (SELECT i.id_n50, i.k FROM small_inner i);
38+
39+
cleanup sql_benchmarks/null_aware_join/init/cleanup.sql
Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
name Q14
2+
group null_aware_join
3+
4+
load sql_benchmarks/null_aware_join/init/load.sql
5+
6+
# Correctness canary: the NOT IN result must match a reference count that does
7+
# not use NOT IN. It holds for every NAJ_ROWS. `hk` is NOT NULL on both sides, so
8+
# a subquery row makes an outer tuple UNKNOWN or TRUE exactly when `hk` matches
9+
# and `id_n50` is NULL on either side or equal; the row is kept only when no
10+
# such subquery row exists.
11+
assert I
12+
SELECT count(*) = (
13+
SELECT count(*)
14+
FROM small_outer o
15+
WHERE NOT EXISTS (
16+
SELECT 1 FROM small_inner i
17+
WHERE i.z < 500
18+
AND i.hk = o.hk
19+
AND (o.id_n50 IS NULL OR i.id_n50 IS NULL OR i.id_n50 = o.id_n50)
20+
)
21+
)
22+
FROM small_outer o
23+
WHERE (o.id_n50, o.hk) NOT IN (
24+
SELECT i.id_n50, i.hk FROM small_inner i WHERE i.z < 500
25+
);
26+
----
27+
true
28+
29+
expect_plan HashJoinExec
30+
31+
run
32+
-- Q14: uncorrelated multi-column NOT IN whose NOT NULL element `hk` is a scope
33+
-- key with one hot value among many rare ones, with 50% NULL in the other
34+
-- element on both sides. The NULL-valued outer rows of the hot scope each pair
35+
-- with its many subquery rows, while the rare ones pair with one row each: the
36+
-- join must not walk the hot scope once per outer row. The filter halves the
37+
-- subquery side, so the outer side is probed. Compare with Q13, whose scopes
38+
-- are all equally large.
39+
SELECT count(*)
40+
FROM small_outer o
41+
WHERE (o.id_n50, o.hk) NOT IN (
42+
SELECT i.id_n50, i.hk FROM small_inner i WHERE i.z < 500
43+
);
44+
45+
cleanup sql_benchmarks/null_aware_join/init/cleanup.sql

‎benchmarks/sql_benchmarks/null_aware_join/init/load.sql‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,14 @@
88
-- id_n50 Int64 NULL, 50% NULL
99
-- z correlation value for a non-equality correlation (`i.z < o.z`)
1010
-- k correlation key for an equality correlation (`i.k = o.k`), 16 groups
11+
-- hk a hot value (-1) for 3/4 of the rows, and a rare one for the rest
1112
--
1213
-- `id_n0` is nullable so the planner still builds a null-aware join, but holds no
1314
-- NULL, which is what makes it the zero-NULL baseline for the correlated queries.
1415
-- `id` is only ~half covered by the subquery side, so the anti join returns rows
1516
-- rather than degenerating to an empty or full result.
1617

17-
-- Small tables: used by the correlated and multi-column queries (Q04-Q12), whose cost grows with
18+
-- Small tables: used by the correlated and multi-column queries (Q04-Q14), whose cost grows with
1819
-- the product of the two table sizes.
1920
CREATE TABLE small_outer AS
2021
SELECT
@@ -23,7 +24,8 @@ SELECT
2324
CASE WHEN value % 100 = 0 THEN NULL ELSE value END AS id_n1,
2425
CASE WHEN value % 2 = 0 THEN NULL ELSE value END AS id_n50,
2526
value % 1000 AS z,
26-
value % 16 AS k
27+
value % 16 AS k,
28+
CASE WHEN value % 4 = 0 THEN value ELSE -1 END AS hk
2729
FROM range(0, ${NAJ_ROWS:-10000});
2830

2931
CREATE TABLE small_inner AS
@@ -33,7 +35,8 @@ SELECT
3335
CASE WHEN value % 100 = 0 THEN NULL ELSE value * 2 END AS id_n1,
3436
CASE WHEN value % 2 = 0 THEN NULL ELSE value * 2 END AS id_n50,
3537
value % 1000 AS z,
36-
value % 16 AS k
38+
value % 16 AS k,
39+
CASE WHEN value % 4 = 0 THEN value ELSE -1 END AS hk
3740
FROM range(0, ${NAJ_ROWS:-10000});
3841

3942
-- Large tables: used by the uncorrelated queries (Q01-Q03), whose cost is linear

‎benchmarks/sql_benchmarks/null_aware_join/null_aware_join.suite‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ short = "r"
88
env = "NAJ_ROWS"
99
default = "10000"
1010
values = ["10000", "..."]
11-
help = "Rows per table for the correlated and multi-column queries (Q04-Q12). These evaluate the join filter over candidate build x probe pairs, so their cost grows with the square of this value."
11+
help = "Rows per table for the correlated and multi-column queries (Q04-Q14). These evaluate the join filter over candidate build x probe pairs, so their cost grows with the square of this value."
1212

1313
[[options]]
1414
name = "large-rows"

‎datafusion/core/tests/fuzz_cases/null_aware_join_fuzz.rs‎

Lines changed: 38 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,8 @@
88
//! null-aware hash joins, are compared with `NOT EXISTS` / `EXISTS`
99
//! formulations of the same three-valued logic, which do not. Random tables
1010
//! with NULLs on both sides are swept across batch sizes and partition counts.
11+
//! Some columns are NOT NULL, so that tuples mix elements that can be NULL with
12+
//! elements that cannot, which the null-aware join reads as scope keys.
1113
//!
1214
//! The subquery table is sometimes much smaller than the outer table, so
1315
//! `JoinSelection` also swaps `NOT IN` into a null-aware `RightAnti` that
@@ -31,11 +33,12 @@ async fn null_aware_join_matches_reference() {
3133
let mut plans = NullAwarePlans::default();
3234
for seed in 0..20 {
3335
let mut rng = StdRng::seed_from_u64(seed);
34-
let outer = random_table(&mut rng, &["id", "k", "a", "b", "c"], 6);
36+
let outer = random_table(&mut rng, &["id", "k", "a", "b", "c"], &["n", "m"], 6);
3537
// Alternate between a subquery table of similar size and a much
3638
// smaller one that `JoinSelection` moves to the build side.
3739
let inner_rows = if seed % 2 == 0 { 6 } else { 2 };
38-
let inner = random_table(&mut rng, &["k", "x", "y", "z"], inner_rows);
40+
let inner =
41+
random_table(&mut rng, &["k", "x", "y", "z"], &["v", "w"], inner_rows);
3942
for target_partitions in [1, 4] {
4043
for batch_size in [1, 3, 8192] {
4144
let config = SessionConfig::new()
@@ -129,6 +132,22 @@ fn cases() -> Vec<(String, String)> {
129132
"o.a = i.x AND o.b = i.y AND o.c = i.z",
130133
),
131134
("(o.a, 1)", "i.x, i.y", "o.a = i.x AND 1 = i.y"),
135+
// `o.n`, `o.m`, `i.v` and `i.w` are NOT NULL.
136+
("(o.a, o.n)", "i.x, i.v", "o.a = i.x AND o.n = i.v"),
137+
("(o.n, o.a)", "i.v, i.x", "o.n = i.v AND o.a = i.x"),
138+
(
139+
"(o.n, o.a, o.b)",
140+
"i.v, i.x, i.y",
141+
"o.n = i.v AND o.a = i.x AND o.b = i.y",
142+
),
143+
(
144+
"(o.n, o.a, o.m)",
145+
"i.v, i.x, i.w",
146+
"o.n = i.v AND o.a = i.x AND o.m = i.w",
147+
),
148+
("(o.n, o.a)", "i.y, i.x", "o.n = i.y AND o.a = i.x"),
149+
("(1, o.a)", "i.v, i.x", "1 = i.v AND o.a = i.x"),
150+
("(o.n, o.m)", "i.v, i.w", "o.n = i.v AND o.m = i.w"),
132151
] {
133152
let subquery = format!("SELECT {output} FROM i{correlation}");
134153
let matched = format!(
@@ -166,14 +185,24 @@ fn cases() -> Vec<(String, String)> {
166185
cases
167186
}
168187

169-
/// A table of nullable `Int32` columns with values in `0..3`, split into up
170-
/// to three partitions of fewer than `max_rows` rows each. A column named `id`
171-
/// holds unique row ids instead.
172-
fn random_table(rng: &mut StdRng, columns: &[&str], max_rows: i32) -> Arc<MemTable> {
188+
/// A table of `Int32` columns with values in `0..3`, split into up to three
189+
/// partitions of fewer than `max_rows` rows each: the `nullable` columns, then
190+
/// the `non_nullable` ones. A column named `id` holds unique row ids instead.
191+
fn random_table(
192+
rng: &mut StdRng,
193+
nullable: &[&str],
194+
non_nullable: &[&str],
195+
max_rows: i32,
196+
) -> Arc<MemTable> {
197+
let columns = nullable
198+
.iter()
199+
.map(|name| (*name, true))
200+
.chain(non_nullable.iter().map(|name| (*name, false)))
201+
.collect::<Vec<_>>();
173202
let schema = Arc::new(Schema::new(
174203
columns
175204
.iter()
176-
.map(|name| Field::new(*name, DataType::Int32, true))
205+
.map(|(name, nullable)| Field::new(*name, DataType::Int32, *nullable))
177206
.collect::<Vec<_>>(),
178207
));
179208
let null_fraction = [0.0, 0.1, 0.3][rng.random_range(0..3)];
@@ -183,13 +212,13 @@ fn random_table(rng: &mut StdRng, columns: &[&str], max_rows: i32) -> Arc<MemTab
183212
let rows = rng.random_range(0..max_rows);
184213
let arrays = columns
185214
.iter()
186-
.map(|name| {
215+
.map(|(name, nullable)| {
187216
let values: Int32Array = if *name == "id" {
188217
(next_id..next_id + rows).map(Some).collect()
189218
} else {
190219
(0..rows)
191220
.map(|_| {
192-
(!rng.random_bool(null_fraction))
221+
(!(*nullable && rng.random_bool(null_fraction)))
193222
.then(|| rng.random_range(0..3))
194223
})
195224
.collect()

‎datafusion/core/tests/sql/joins.rs‎

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -688,3 +688,89 @@ async fn asof_join_unparser_preserves_right_preselection() -> Result<()> {
688688
}
689689
Ok(())
690690
}
691+
692+
/// Registers `o(a, b)` and `i(x, y)` with the fields that
693+
/// `RecordBatch::try_from_iter` infers, which count only the null bits.
694+
fn hidden_null_context(b: ArrayRef, y: ArrayRef) -> Result<SessionContext> {
695+
let outer = RecordBatch::try_from_iter(vec![
696+
(
697+
"a",
698+
Arc::new(Int32Array::from(vec![Some(1), None])) as ArrayRef,
699+
),
700+
("b", b),
701+
])?;
702+
let inner = RecordBatch::try_from_iter(vec![
703+
("x", Arc::new(Int32Array::from(vec![1])) as ArrayRef),
704+
("y", y),
705+
])?;
706+
assert!(!outer.schema().field_with_name("b")?.is_nullable());
707+
assert!(!inner.schema().field_with_name("y")?.is_nullable());
708+
let ctx = SessionContext::new();
709+
ctx.register_batch("o", outer)?;
710+
ctx.register_batch("i", inner)?;
711+
Ok(ctx)
712+
}
713+
714+
/// A dictionary with keys `keys` into `values`.
715+
fn dictionary(keys: Vec<i32>, values: ArrayRef) -> Result<ArrayRef> {
716+
Ok(Arc::new(DictionaryArray::<Int32Type>::try_new(
717+
Int32Array::from(keys),
718+
values,
719+
)?))
720+
}
721+
722+
const HIDDEN_NULL_PROJECTED: &str = "SELECT a, (a, b) NOT IN (SELECT x, y FROM i) AS r \
723+
FROM o ORDER BY a NULLS LAST";
724+
const HIDDEN_NULL_FILTERED: &str =
725+
"SELECT a FROM o WHERE (a, b) NOT IN (SELECT x, y FROM i)";
726+
727+
/// A dictionary key can point to a NULL value without a null bit, so the
728+
/// field inferred for `b` is non-nullable although `b` holds a NULL. The
729+
/// multi-column `NOT IN` must still see that NULL: `(1, NULL) NOT IN ((1, 2))`
730+
/// is NULL, so a `WHERE` drops the row.
731+
#[tokio::test]
732+
async fn multi_column_not_in_sees_null_dictionary_values() -> Result<()> {
733+
let ctx = hidden_null_context(
734+
dictionary(vec![0, 1], Arc::new(Int32Array::from(vec![None, Some(3)])))?,
735+
dictionary(vec![0], Arc::new(Int32Array::from(vec![2])))?,
736+
)?;
737+
let projected = ctx.sql(HIDDEN_NULL_PROJECTED).await?.collect().await?;
738+
assert_batches_eq!(
739+
[
740+
"+---+------+",
741+
"| a | r |",
742+
"+---+------+",
743+
"| 1 | |",
744+
"| | true |",
745+
"+---+------+",
746+
],
747+
&projected
748+
);
749+
let filtered = ctx.sql(HIDDEN_NULL_FILTERED).await?.collect().await?;
750+
assert_batches_eq!(["+---+", "| a |", "+---+", "| |", "+---+"], &filtered);
751+
Ok(())
752+
}
753+
754+
/// Every slot of a `Null` array is NULL without a null bit, so the inferred
755+
/// fields of `b` and `y` are non-nullable. Each row compares a NULL `b`, so
756+
/// `NOT IN` is NULL for both and a `WHERE` drops both.
757+
#[tokio::test]
758+
async fn multi_column_not_in_sees_null_type_values() -> Result<()> {
759+
let ctx =
760+
hidden_null_context(Arc::new(NullArray::new(2)), Arc::new(NullArray::new(1)))?;
761+
let projected = ctx.sql(HIDDEN_NULL_PROJECTED).await?.collect().await?;
762+
assert_batches_eq!(
763+
[
764+
"+---+---+",
765+
"| a | r |",
766+
"+---+---+",
767+
"| 1 | |",
768+
"| | |",
769+
"+---+---+",
770+
],
771+
&projected
772+
);
773+
let filtered = ctx.sql(HIDDEN_NULL_FILTERED).await?.collect().await?;
774+
assert_batches_eq!(["++", "++"], &filtered);
775+
Ok(())
776+
}

‎datafusion/expr/src/logical_plan/plan.rs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4644,7 +4644,9 @@ pub struct Join {
46444644
/// [`Self::null_aware`] is set.
46454645
///
46464646
/// A scalar `x NOT IN (SELECT y ...)` has one value key, a multi-column
4647-
/// `(a, b) NOT IN (SELECT x, y ...)` one per tuple element. The value keys
4647+
/// `(a, b) NOT IN (SELECT x, y ...)` one per tuple element that can be
4648+
/// NULL in scope. A tuple element that cannot be NULL on either side never
4649+
/// makes the comparison UNKNOWN, so it is a scope key instead. The value keys
46484650
/// are the leading equi-join keys (`on[..n]`, or the leading equality
46494651
/// conjuncts of `filter` before they are extracted into `on`); any keys
46504652
/// after them are correlation scope keys of a correlated subquery. A NULL

0 commit comments

Comments
 (0)