Skip to content

Commit 04f3d7d

Browse files
adriangbclaude
andcommitted
test: cover mark-join plan shapes and NULL semantics for projected IN subqueries
Add plan guards for the two paths of a projected IN subquery: one null-aware LeftMark join per subquery when the join filter is hashable only, and the three-join materialization when a non-equality correlation stays as a residual join filter. Add result tests for IN, NOT IN, EXISTS, an aggregate probe, a CASE wrapper and a COALESCE wrapper over tables that hold NULL keys. The expected values agree with DuckDB 1.5.2 and PostgreSQL 17. Co-Authored-By: Claude Fable 5.1 <[email protected]>
1 parent a20cc17 commit 04f3d7d

1 file changed

Lines changed: 154 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/subquery_projection.slt‎

Lines changed: 154 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,3 +97,157 @@ FROM outer_values o;
9797
3 NULL
9898
4 true
9999
5 NULL
100+
101+
# Plan shapes and NULL semantics of a projected IN subquery.
102+
#
103+
# `n1.id` holds a NULL, `n2.id` holds a NULL, and `n3.id` holds none. The mark
104+
# column of a LeftMark join carries the three-valued result on its own when the
105+
# join filter is hashable only, so one join per subquery is enough.
106+
107+
statement ok
108+
CREATE TABLE n1(id INT, z INT) AS VALUES (1, 10), (2, 20), (NULL, 30), (4, 40);
109+
110+
statement ok
111+
CREATE TABLE n2(id INT, z INT) AS VALUES (1, 5), (NULL, 50);
112+
113+
statement ok
114+
CREATE TABLE n3(id INT) AS VALUES (1), (2);
115+
116+
# One hash mark join per subquery. There is no materialization join, so no
117+
# nested loop join over outer x inner rows.
118+
query TT
119+
EXPLAIN SELECT id, id IN (SELECT id FROM n3) AS m3, id IN (SELECT id FROM n2) AS m2 FROM n1;
120+
----
121+
logical_plan
122+
01)Projection: n1.id, __correlated_sq_1.mark AS m3, __correlated_sq_2.mark AS m2
123+
02)--LeftMark Join: n1.id = __correlated_sq_2.id null_aware
124+
03)----LeftMark Join: n1.id = __correlated_sq_1.id null_aware
125+
04)------TableScan: n1 projection=[id]
126+
05)------SubqueryAlias: __correlated_sq_1
127+
06)--------TableScan: n3 projection=[id]
128+
07)----SubqueryAlias: __correlated_sq_2
129+
08)------TableScan: n2 projection=[id]
130+
physical_plan
131+
01)ProjectionExec: expr=[id@0 as id, mark@1 as m3, mark@2 as m2]
132+
02)--HashJoinExec: mode=CollectLeft, join_type=LeftMark, on=[(id@0, id@0)], null_aware
133+
03)----HashJoinExec: mode=CollectLeft, join_type=LeftMark, on=[(id@0, id@0)], null_aware
134+
04)------DataSourceExec: partitions=1, partition_sizes=[1]
135+
05)------DataSourceExec: partitions=1, partition_sizes=[1]
136+
06)----DataSourceExec: partitions=1, partition_sizes=[1]
137+
138+
# A non-equality correlation stays a residual join filter, so this query keeps
139+
# the three-join materialization.
140+
query TT
141+
EXPLAIN SELECT id, id IN (SELECT n2.id FROM n2 WHERE n2.z < n1.z) AS m FROM n1;
142+
----
143+
logical_plan
144+
01)Projection: n1.id, __correlated_sq_1.mark IS NOT DISTINCT FROM Boolean(true) OR (__correlated_sq_2.mark OR n1.id IS NULL AND __correlated_sq_3.mark) IS NOT DISTINCT FROM Boolean(true) AND __correlated_sq_1.mark IS DISTINCT FROM Boolean(true) AND Boolean(NULL) AS m
145+
02)--LeftMark Join: Filter: __correlated_sq_3.z < n1.z
146+
03)----LeftMark Join: Filter: __correlated_sq_2.z < n1.z
147+
04)------LeftMark Join: n1.id = __correlated_sq_1.id Filter: __correlated_sq_1.z < n1.z
148+
05)--------TableScan: n1 projection=[id, z]
149+
06)--------SubqueryAlias: __correlated_sq_1
150+
07)----------TableScan: n2 projection=[id, z]
151+
08)------SubqueryAlias: __correlated_sq_2
152+
09)--------Projection: n2.z
153+
10)----------Filter: n2.id IS NULL
154+
11)------------TableScan: n2 projection=[id, z]
155+
12)----SubqueryAlias: __correlated_sq_3
156+
13)------TableScan: n2 projection=[z]
157+
physical_plan
158+
01)ProjectionExec: expr=[id@0 as id, mark@1 IS NOT DISTINCT FROM true OR (mark@2 OR id@0 IS NULL AND mark@3) IS NOT DISTINCT FROM true AND mark@1 IS DISTINCT FROM true AND NULL as m]
159+
02)--NestedLoopJoinExec: join_type=RightMark, filter=z@1 < z@0, projection=[id@0, mark@2, mark@3, mark@4]
160+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
161+
04)----NestedLoopJoinExec: join_type=RightMark, filter=z@1 < z@0
162+
05)------FilterExec: id@0 IS NULL, projection=[z@1]
163+
06)--------DataSourceExec: partitions=1, partition_sizes=[1]
164+
07)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
165+
08)--------HashJoinExec: mode=CollectLeft, join_type=LeftMark, on=[(id@0, id@0)], filter=z@1 < z@0
166+
09)----------DataSourceExec: partitions=1, partition_sizes=[1]
167+
10)----------DataSourceExec: partitions=1, partition_sizes=[1]
168+
169+
query IB rowsort
170+
SELECT id, id IN (SELECT n2.id FROM n2 WHERE n2.z < n1.z) AS m FROM n1;
171+
----
172+
1 true
173+
2 false
174+
4 false
175+
NULL NULL
176+
177+
query IBBB rowsort
178+
SELECT
179+
id,
180+
id IN (SELECT id FROM n3) AS m3,
181+
EXISTS (SELECT 1 FROM n2 WHERE n2.id = n1.id) AS e2,
182+
id NOT IN (SELECT id FROM n3 WHERE n3.id > 1) AS nn3
183+
FROM n1;
184+
----
185+
1 true true true
186+
2 true false false
187+
4 false false true
188+
NULL NULL false NULL
189+
190+
query IB rowsort
191+
SELECT id, id IN (SELECT id FROM n2) AS m FROM n1;
192+
----
193+
1 true
194+
2 NULL
195+
4 NULL
196+
NULL NULL
197+
198+
query IB rowsort
199+
SELECT id, id NOT IN (SELECT id FROM n2) AS m FROM n1;
200+
----
201+
1 false
202+
2 NULL
203+
4 NULL
204+
NULL NULL
205+
206+
query IB rowsort
207+
SELECT id, id IN (SELECT id FROM n3) AS m FROM n1;
208+
----
209+
1 true
210+
2 true
211+
4 false
212+
NULL NULL
213+
214+
query IT rowsort
215+
SELECT id, CASE WHEN NOT (id IN (SELECT id FROM n2)) THEN 'a' ELSE 'b' END AS c FROM n1;
216+
----
217+
1 b
218+
2 b
219+
4 b
220+
NULL b
221+
222+
query IB rowsort
223+
SELECT z, sum(id) IN (SELECT id FROM n3) AS m FROM n1 GROUP BY z;
224+
----
225+
10 true
226+
20 true
227+
30 NULL
228+
40 false
229+
230+
query IB rowsort
231+
SELECT id, COALESCE((id IN (SELECT id FROM n3))::boolean, false) AS matched FROM n1;
232+
----
233+
1 true
234+
2 true
235+
4 false
236+
NULL false
237+
238+
query IT rowsort
239+
SELECT id, CASE WHEN id NOT IN (SELECT n2.id FROM n2 WHERE n2.z < n1.z) THEN 'a' ELSE 'b' END AS c FROM n1;
240+
----
241+
1 b
242+
2 a
243+
4 a
244+
NULL b
245+
246+
statement ok
247+
DROP TABLE n1;
248+
249+
statement ok
250+
DROP TABLE n2;
251+
252+
statement ok
253+
DROP TABLE n3;

0 commit comments

Comments
 (0)