@@ -42,3 +42,150 @@ query IR
4242SELECT int_col, double_col FROM alltypes_plain where int_col > 0 INTERSECT SELECT int_col, double_col FROM alltypes_plain
4343----
44441 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;
0 commit comments