Skip to content

Commit e6e7989

Browse files
adriangbclaude
andcommitted
bench: add correctness asserts to null_aware_join Q01-Q04
Each assert compares the NOT IN count with a reference count that does not use NOT IN, so it holds at every NAJ_ROWS / NAJ_LARGE_ROWS value. Q05-Q08 give wrong results on main (apache#25336), so their asserts go in apache#25339 together with the fix. Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent a4cea4b commit e6e7989

4 files changed

Lines changed: 45 additions & 0 deletions

File tree

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q01.benchmark‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,16 @@ group null_aware_join
33

44
load sql_benchmarks/null_aware_join/init/load.sql
55

6+
# Correctness canary: the NOT IN result must match a reference count
7+
# that does not use NOT IN. It holds for every NAJ_ROWS / NAJ_LARGE_ROWS.
8+
# Odd ids are never in the subquery, which holds only even ids.
9+
assert I
10+
SELECT count(*) = (SELECT count(*) FROM large_outer WHERE id % 2 = 1)
11+
FROM large_outer o
12+
WHERE o.id NOT IN (SELECT i.id FROM large_inner i);
13+
----
14+
true
15+
616
expect_plan HashJoinExec
717

818
run

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q02.benchmark‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,16 @@ group null_aware_join
33

44
load sql_benchmarks/null_aware_join/init/load.sql
55

6+
# Correctness canary: the NOT IN result must match a reference count
7+
# that does not use NOT IN. It holds for every NAJ_ROWS / NAJ_LARGE_ROWS.
8+
# The subquery always holds a NULL, so no outer row is TRUE.
9+
assert I
10+
SELECT count(*) = 0
11+
FROM large_outer o
12+
WHERE o.id NOT IN (SELECT i.id_n1 FROM large_inner i);
13+
----
14+
true
15+
616
expect_plan HashJoinExec
717
expect_plan null_aware: true
818

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q03.benchmark‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,16 @@ group null_aware_join
33

44
load sql_benchmarks/null_aware_join/init/load.sql
55

6+
# Correctness canary: the NOT IN result must match a reference count
7+
# that does not use NOT IN. It holds for every NAJ_ROWS / NAJ_LARGE_ROWS.
8+
# The non-NULL outer ids are odd, and the subquery holds only even ids.
9+
assert I
10+
SELECT count(*) = (SELECT count(*) FROM large_outer WHERE id % 2 = 1)
11+
FROM large_outer o
12+
WHERE o.id_n50 NOT IN (SELECT i.id FROM large_inner i);
13+
----
14+
true
15+
616
expect_plan HashJoinExec
717
expect_plan null_aware: true
818

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q04.benchmark‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,21 @@ group null_aware_join
33

44
load sql_benchmarks/null_aware_join/init/load.sql
55

6+
# Correctness canary: the NOT IN result must match a reference count
7+
# that does not use NOT IN. It holds for every NAJ_ROWS / NAJ_LARGE_ROWS.
8+
# A row is TRUE when the subquery is empty, or when its key is not NULL and
9+
# not in the subquery. `id` = 2 * v is in the subquery when v % 1000 < o.z.
10+
assert I
11+
SELECT count(*) = (
12+
SELECT count(*) FROM small_outer o
13+
WHERE o.z <= (SELECT min(z) FROM small_inner)
14+
OR (o.id_n0 IS NOT NULL AND NOT (o.id % 2 = 0 AND (o.id / 2) % 1000 < o.z))
15+
)
16+
FROM small_outer o
17+
WHERE o.id_n0 NOT IN (SELECT i.id_n0 FROM small_inner i WHERE i.z < o.z);
18+
----
19+
true
20+
621
expect_plan HashJoinExec
722
expect_plan null_aware: true
823

0 commit comments

Comments
 (0)