Skip to content

Commit e3d0754

Browse files
viiryaIsaac
andcommitted
bench: add a hot-scope multi-column NOT IN query
Co-authored-by: Isaac <[email protected]>
1 parent 0fa56a1 commit e3d0754

3 files changed

Lines changed: 52 additions & 4 deletions

File tree

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-Q13), 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-Q13). 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"

0 commit comments

Comments
 (0)