Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9bbca2c

Browse files
committed
Add some more union/intersect/except test cases, per suggestions
from Kevin O'Gorman.
1 parenta1d1339 commit9bbca2c

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 q2FROM int8_tbl EXCEPT ALLSELECT DISTINCT q1FROM int8_tbl;
100100

101+
SELECT q1FROM int8_tbl EXCEPTSELECT q2FROM int8_tbl;
102+
103+
SELECT q1FROM int8_tbl EXCEPT ALLSELECT q2FROM int8_tbl;
104+
105+
SELECT q1FROM int8_tbl EXCEPT ALLSELECT DISTINCT q2FROM int8_tbl;
106+
101107
--
102108
-- Mixed types
103109
--
104110

105111
SELECT f1FROM float8_tbl INTERSECTSELECT f1FROM int4_tbl;
106112

107113
SELECT f1FROM float8_tbl EXCEPTSELECT f1FROM int4_tbl;
114+
115+
--
116+
-- Operator precedence and (((((extra))))) parentheses
117+
--
118+
119+
SELECT q1FROM int8_tbl INTERSECTSELECT q2FROM int8_tblUNION ALLSELECT q2FROM int8_tbl;
120+
121+
SELECT q1FROM int8_tbl INTERSECT (((SELECT q2FROM int8_tblUNION ALLSELECT q2FROM int8_tbl)));
122+
123+
(((SELECT q1FROM int8_tbl INTERSECTSELECT q2FROM int8_tbl)))UNION ALLSELECT q2FROM int8_tbl;
124+
125+
SELECT q1FROM int8_tblUNION ALLSELECT q2FROM int8_tbl EXCEPTSELECT q1FROM int8_tbl;
126+
127+
SELECT q1FROM int8_tblUNION ALL (((SELECT q2FROM int8_tbl EXCEPTSELECT q1FROM int8_tbl)));
128+
129+
(((SELECT q1FROM int8_tblUNION ALLSELECT q2FROM int8_tbl))) EXCEPTSELECT q1FROM 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,q2FROM int8_tbl EXCEPTSELECT q2,q1FROM 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 q1FROM int8_tbl EXCEPTSELECT q2FROM int8_tblORDER BY q2LIMIT1;
141+
142+
-- But this should work:
143+
SELECT q1FROM int8_tbl EXCEPT (((SELECT q2FROM int8_tblORDER BY q2LIMIT1)));
144+
145+
--
146+
-- New syntaxes (7.1) permit new tests
147+
--
148+
149+
(((((select*from int8_tbl)))));
150+
151+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp