Skip to content

Commit fa66d51

Browse files
1fanwangkosiew
authored andcommitted
test: cover INTERSECT ALL and EXCEPT ALL duplicate counts
Add sqllogictest cases for min and max duplicate counts with NULLs, rows that are NULL in only some columns, INTERSECT ALL binding tighter than EXCEPT ALL, and the two qualifier regressions. Record the +0.0 / -0.0 case as a known issue: window partitioning puts the two values in separate partitions, so the counts are wrong until apache#25766 is fixed. Signed-off-by: Stefan Wang <[email protected]>
1 parent b316e92 commit fa66d51

2 files changed

Lines changed: 164 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/intersection.slt‎

Lines changed: 147 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,3 +42,150 @@ query IR
4242
SELECT int_col, double_col FROM alltypes_plain where int_col > 0 INTERSECT SELECT int_col, double_col FROM alltypes_plain
4343
----
4444
1 10.1
45+
46+
# INTERSECT ALL keeps min(m, n) copies of each row and EXCEPT ALL keeps
47+
# max(m - n, 0), with NULLs compared as equal.
48+
statement ok
49+
CREATE TABLE set_all_left(v INT) AS VALUES (NULL), (NULL), (NULL), (1), (1), (2);
50+
51+
statement ok
52+
CREATE TABLE set_all_right(v INT) AS VALUES (NULL), (NULL), (1), (1), (1), (3);
53+
54+
query I rowsort
55+
SELECT v FROM set_all_left INTERSECT ALL SELECT v FROM set_all_right
56+
----
57+
1
58+
1
59+
NULL
60+
NULL
61+
62+
query I rowsort
63+
SELECT v FROM set_all_left EXCEPT ALL SELECT v FROM set_all_right
64+
----
65+
2
66+
NULL
67+
68+
query I rowsort
69+
SELECT v FROM set_all_right EXCEPT ALL SELECT v FROM set_all_left
70+
----
71+
1
72+
3
73+
74+
# Rows that are NULL in only some columns
75+
statement ok
76+
CREATE TABLE set_all_pairs_left(a INT, b INT) AS VALUES
77+
(1, NULL), (1, NULL), (NULL, 1), (1, 1), (NULL, NULL);
78+
79+
statement ok
80+
CREATE TABLE set_all_pairs_right(a INT, b INT) AS VALUES
81+
(1, NULL), (NULL, 1), (NULL, 1), (NULL, NULL), (NULL, NULL);
82+
83+
query II rowsort
84+
SELECT a, b FROM set_all_pairs_left INTERSECT ALL SELECT a, b FROM set_all_pairs_right
85+
----
86+
1 NULL
87+
NULL 1
88+
NULL NULL
89+
90+
query II rowsort
91+
SELECT a, b FROM set_all_pairs_left EXCEPT ALL SELECT a, b FROM set_all_pairs_right
92+
----
93+
1 1
94+
1 NULL
95+
96+
# INTERSECT binds tighter than EXCEPT
97+
statement ok
98+
CREATE TABLE set_all_a(v INT) AS VALUES (1), (1), (1), (2), (2), (3);
99+
100+
statement ok
101+
CREATE TABLE set_all_b(v INT) AS VALUES (1), (1), (2), (2), (2);
102+
103+
statement ok
104+
CREATE TABLE set_all_c(v INT) AS VALUES (1), (2), (2), (2), (4);
105+
106+
query I rowsort
107+
SELECT v FROM set_all_a EXCEPT ALL SELECT v FROM set_all_b INTERSECT ALL SELECT v FROM set_all_c
108+
----
109+
1
110+
1
111+
3
112+
113+
query I rowsort
114+
(SELECT v FROM set_all_a EXCEPT ALL SELECT v FROM set_all_b) INTERSECT ALL SELECT v FROM set_all_c
115+
----
116+
1
117+
118+
# The output keeps the left input's qualifiers
119+
statement ok
120+
CREATE TABLE set_all_t(x INT) AS VALUES (1), (1), (2);
121+
122+
statement ok
123+
CREATE TABLE set_all_u(x INT) AS VALUES (1), (3);
124+
125+
query I
126+
SELECT set_all_t.x FROM set_all_t INTERSECT ALL SELECT set_all_u.x FROM set_all_u ORDER BY set_all_t.x
127+
----
128+
1
129+
130+
query I
131+
SELECT set_all_t.x FROM set_all_t EXCEPT ALL SELECT set_all_u.x FROM set_all_u ORDER BY set_all_t.x
132+
----
133+
1
134+
2
135+
136+
# Both sides read the same table, so the planner has to requalify them internally
137+
query I
138+
SELECT set_all_t.x FROM set_all_t INTERSECT ALL SELECT set_all_t.x FROM set_all_t ORDER BY set_all_t.x
139+
----
140+
1
141+
1
142+
2
143+
144+
# Columns with the same name from different relations on one side
145+
query II
146+
SELECT t1.x, t2.x FROM (VALUES (1)) t1(x) CROSS JOIN (VALUES (2)) t2(x)
147+
INTERSECT ALL
148+
SELECT t1.x, t2.x FROM (VALUES (1)) t1(x) CROSS JOIN (VALUES (2)) t2(x)
149+
----
150+
1 2
151+
152+
query II
153+
SELECT t1.x, t2.x FROM (VALUES (1), (1)) t1(x) CROSS JOIN (VALUES (2)) t2(x)
154+
EXCEPT ALL
155+
SELECT t1.x, t2.x FROM (VALUES (1)) t1(x) CROSS JOIN (VALUES (2)) t2(x)
156+
----
157+
1 2
158+
159+
query II
160+
SELECT t1.x, t2.x FROM (VALUES (1)) t1(x) CROSS JOIN (VALUES (2)) t2(x)
161+
INTERSECT ALL
162+
SELECT t3.x, t4.x FROM (VALUES (1)) t3(x) CROSS JOIN (VALUES (2)) t4(x)
163+
----
164+
1 2
165+
166+
statement ok
167+
DROP TABLE set_all_left;
168+
169+
statement ok
170+
DROP TABLE set_all_right;
171+
172+
statement ok
173+
DROP TABLE set_all_pairs_left;
174+
175+
statement ok
176+
DROP TABLE set_all_pairs_right;
177+
178+
statement ok
179+
DROP TABLE set_all_a;
180+
181+
statement ok
182+
DROP TABLE set_all_b;
183+
184+
statement ok
185+
DROP TABLE set_all_c;
186+
187+
statement ok
188+
DROP TABLE set_all_t;
189+
190+
statement ok
191+
DROP TABLE set_all_u;

‎datafusion/sqllogictest/test_files/negative_zero.slt‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,16 @@ SELECT 0.0 AS a INTERSECT ALL SELECT -0.0;
159159
----
160160
0
161161

162+
# INTERSECT ALL with multiplicities min(2,1) should return 1 row.
163+
# Known issue: it returns 2, because `row_number() OVER (PARTITION BY a)`
164+
# puts +0.0 and -0.0 in separate partitions.
165+
# https://github.com/apache/datafusion/issues/25766
166+
query R rowsort
167+
SELECT a FROM (VALUES (0.0), (-0.0)) t(a) INTERSECT ALL SELECT a FROM (VALUES (0.0)) s(a);
168+
----
169+
0
170+
0
171+
162172
# INTERSECT for Float32: same matching behavior.
163173
query R rowsort
164174
SELECT arrow_cast(0.0, 'Float32') AS a
@@ -193,6 +203,13 @@ query R rowsort
193203
SELECT 0.0 AS a EXCEPT ALL SELECT -0.0;
194204
----
195205

206+
# EXCEPT ALL with multiplicities 2 - 1 should return 1 row.
207+
# Known issue: it returns none, for the same reason as INTERSECT ALL above.
208+
# https://github.com/apache/datafusion/issues/25766
209+
query R rowsort
210+
SELECT a FROM (VALUES (0.0), (-0.0)) t(a) EXCEPT ALL SELECT a FROM (VALUES (0.0)) s(a);
211+
----
212+
196213
#####
197214
## INNER JOIN ON equality with +0.0 / -0.0
198215
#####

0 commit comments

Comments
 (0)