Skip to content

Commit 9bbca2c

Browse files
tglsfdctglsfdc
committed
Add some more union/intersect/except test cases, per suggestions
from Kevin O'Gorman.
1 parent a1d1339 commit 9bbca2c

File tree

2 files changed

+160
-0
lines changed

2 files changed

+160
-0
lines changed

‎src/test/regress/expected/union.out

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -294,6 +294,26 @@ SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
294294
4567890123456789
295295
(3 rows)
296296

297+
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
298+
q1
299+
----
300+
(0 rows)
301+
302+
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
303+
q1
304+
------------------
305+
123
306+
4567890123456789
307+
(2 rows)
308+
309+
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
310+
q1
311+
------------------
312+
123
313+
4567890123456789
314+
4567890123456789
315+
(3 rows)
316+
297317
--
298318
-- Mixed types
299319
--
@@ -312,3 +332,99 @@ SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
312332
-1.2345678901234e-200
313333
(4 rows)
314334

335+
--
336+
-- Operator precedence and (((((extra))))) parentheses
337+
--
338+
SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
339+
q1
340+
-------------------
341+
123
342+
4567890123456789
343+
456
344+
4567890123456789
345+
123
346+
4567890123456789
347+
-4567890123456789
348+
(7 rows)
349+
350+
SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
351+
q1
352+
------------------
353+
123
354+
4567890123456789
355+
(2 rows)
356+
357+
(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
358+
q1
359+
-------------------
360+
123
361+
4567890123456789
362+
456
363+
4567890123456789
364+
123
365+
4567890123456789
366+
-4567890123456789
367+
(7 rows)
368+
369+
SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
370+
q1
371+
-------------------
372+
-4567890123456789
373+
456
374+
(2 rows)
375+
376+
SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
377+
q1
378+
-------------------
379+
123
380+
123
381+
4567890123456789
382+
4567890123456789
383+
4567890123456789
384+
-4567890123456789
385+
456
386+
(7 rows)
387+
388+
(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
389+
q1
390+
-------------------
391+
-4567890123456789
392+
456
393+
(2 rows)
394+
395+
--
396+
-- Subqueries with ORDER BY & LIMIT clauses
397+
--
398+
-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
399+
SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
400+
ORDER BY q2,q1;
401+
q1 | q2
402+
------------------+-------------------
403+
4567890123456789 | -4567890123456789
404+
123 | 456
405+
(2 rows)
406+
407+
-- This should fail, because q2 isn't a name of an EXCEPT output column
408+
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
409+
ERROR: Attribute 'q2' not found
410+
-- But this should work:
411+
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
412+
q1
413+
------------------
414+
123
415+
4567890123456789
416+
(2 rows)
417+
418+
--
419+
-- New syntaxes (7.1) permit new tests
420+
--
421+
(((((select * from int8_tbl)))));
422+
q1 | q2
423+
------------------+-------------------
424+
123 | 456
425+
123 | 4567890123456789
426+
4567890123456789 | 123
427+
4567890123456789 | 4567890123456789
428+
4567890123456789 | -4567890123456789
429+
(5 rows)
430+

‎src/test/regress/sql/union.sql

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,10 +98,54 @@ SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl;
9898

9999
SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
100100

101+
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
102+
103+
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
104+
105+
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
106+
101107
--
102108
-- Mixed types
103109
--
104110

105111
SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
106112

107113
SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
114+
115+
--
116+
-- Operator precedence and (((((extra))))) parentheses
117+
--
118+
119+
SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
120+
121+
SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
122+
123+
(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
124+
125+
SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
126+
127+
SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
128+
129+
(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
130+
131+
--
132+
-- Subqueries with ORDER BY & LIMIT clauses
133+
--
134+
135+
-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
136+
SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
137+
ORDER BY q2,q1;
138+
139+
-- This should fail, because q2 isn't a name of an EXCEPT output column
140+
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
141+
142+
-- But this should work:
143+
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
144+
145+
--
146+
-- New syntaxes (7.1) permit new tests
147+
--
148+
149+
(((((select * from int8_tbl)))));
150+
151+

0 commit comments

Comments
 (0)