| blob | 0456d48c93a53f6bcf99d6a6786edd69979042d6 |
1 --
2 -- UNION (also INTERSECT, EXCEPT)
3 --
4 -- Simple UNION constructs
5 SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
6 two
7 -----
8 1
9 2
10 (2 rows)
12 SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
13 one
14 -----
15 1
16 (1 row)
18 SELECT 1 AS two UNION ALL SELECT 2;
19 two
20 -----
21 1
22 2
23 (2 rows)
25 SELECT 1 AS two UNION ALL SELECT 1;
26 two
27 -----
28 1
29 1
30 (2 rows)
32 SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
33 three
34 -------
35 1
36 2
37 3
38 (3 rows)
40 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
41 two
42 -----
43 1
44 2
45 (2 rows)
47 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
48 three
49 -------
50 1
51 2
52 2
53 (3 rows)
55 SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
56 two
57 -----
58 1.1
59 2.2
60 (2 rows)
62 -- Mixed types
63 SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
64 two
65 -----
66 1.1
67 2
68 (2 rows)
70 SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
71 two
72 -----
73 1
74 2.2
75 (2 rows)
77 SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1;
78 one
79 -----
80 1
81 (1 row)
83 SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
84 two
85 -----
86 1.1
87 2
88 (2 rows)
90 SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1;
91 two
92 -----
93 1
94 1
95 (2 rows)
97 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
98 three
99 -------
100 1.1
101 2
102 3
103 (3 rows)
105 SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
106 two
107 -----
108 1.1
109 2
110 (2 rows)
112 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
113 three
114 -------
115 1.1
116 2
117 2
118 (3 rows)
120 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
121 two
122 -----
123 1.1
124 2
125 (2 rows)
127 --
128 -- Try testing from tables...
129 --
130 SELECT f1 AS five FROM FLOAT8_TBL
131 UNION
132 SELECT f1 FROM FLOAT8_TBL
133 ORDER BY 1;
134 five
135 -----------------------
136 -1.2345678901234e+200
137 -1004.3
138 -34.84
139 -1.2345678901234e-200
140 0
141 (5 rows)
143 SELECT f1 AS ten FROM FLOAT8_TBL
144 UNION ALL
145 SELECT f1 FROM FLOAT8_TBL;
146 ten
147 -----------------------
148 0
149 -34.84
150 -1004.3
151 -1.2345678901234e+200
152 -1.2345678901234e-200
153 0
154 -34.84
155 -1004.3
156 -1.2345678901234e+200
157 -1.2345678901234e-200
158 (10 rows)
160 SELECT f1 AS nine FROM FLOAT8_TBL
161 UNION
162 SELECT f1 FROM INT4_TBL
163 ORDER BY 1;
164 nine
165 -----------------------
166 -1.2345678901234e+200
167 -2147483647
168 -123456
169 -1004.3
170 -34.84
171 -1.2345678901234e-200
172 0
173 123456
174 2147483647
175 (9 rows)
177 SELECT f1 AS ten FROM FLOAT8_TBL
178 UNION ALL
179 SELECT f1 FROM INT4_TBL;
180 ten
181 -----------------------
182 0
183 -34.84
184 -1004.3
185 -1.2345678901234e+200
186 -1.2345678901234e-200
187 0
188 123456
189 -123456
190 2147483647
191 -2147483647
192 (10 rows)
194 SELECT f1 AS five FROM FLOAT8_TBL
195 WHERE f1 BETWEEN -1e6 AND 1e6
196 UNION
197 SELECT f1 FROM INT4_TBL
198 WHERE f1 BETWEEN 0 AND 1000000
199 ORDER BY 1;
200 five
201 -----------------------
202 -1004.3
203 -34.84
204 -1.2345678901234e-200
205 0
206 123456
207 (5 rows)
209 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
210 UNION
211 SELECT f1 FROM CHAR_TBL
212 ORDER BY 1;
213 three
214 -------
215 a
216 ab
217 abcd
218 (3 rows)
220 SELECT f1 AS three FROM VARCHAR_TBL
221 UNION
222 SELECT CAST(f1 AS varchar) FROM CHAR_TBL
223 ORDER BY 1;
224 three
225 -------
226 a
227 ab
228 abcd
229 (3 rows)
231 SELECT f1 AS eight FROM VARCHAR_TBL
232 UNION ALL
233 SELECT f1 FROM CHAR_TBL;
234 eight
235 -------
236 a
237 ab
238 abcd
239 abcd
240 a
241 ab
242 abcd
243 abcd
244 (8 rows)
246 SELECT f1 AS five FROM TEXT_TBL
247 UNION
248 SELECT f1 FROM VARCHAR_TBL
249 UNION
250 SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
251 ORDER BY 1;
252 five
253 -------------------
254 a
255 ab
256 abcd
257 doh!
258 hi de ho neighbor
259 (5 rows)
261 --
262 -- INTERSECT and EXCEPT
263 --
264 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
265 q2
266 ------------------
267 123
268 4567890123456789
269 (2 rows)
271 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
272 q2
273 ------------------
274 123
275 4567890123456789
276 4567890123456789
277 (3 rows)
279 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
280 q2
281 -------------------
282 -4567890123456789
283 456
284 (2 rows)
286 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
287 q2
288 -------------------
289 -4567890123456789
290 456
291 (2 rows)
293 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
294 q2
295 -------------------
296 -4567890123456789
297 456
298 4567890123456789
299 (3 rows)
301 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
302 q1
303 ----
304 (0 rows)
306 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
307 q1
308 ------------------
309 123
310 4567890123456789
311 (2 rows)
313 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
314 q1
315 ------------------
316 123
317 4567890123456789
318 4567890123456789
319 (3 rows)
321 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
322 ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
323 -- nested cases
324 (SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
325 ?column? | ?column? | ?column?
326 ----------+----------+----------
327 4 | 5 | 6
328 (1 row)
330 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
331 ?column? | ?column? | ?column?
332 ----------+----------+----------
333 4 | 5 | 6
334 (1 row)
336 (SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
337 ?column? | ?column? | ?column?
338 ----------+----------+----------
339 1 | 2 | 3
340 (1 row)
342 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
343 ?column? | ?column? | ?column?
344 ----------+----------+----------
345 1 | 2 | 3
346 (1 row)
348 -- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
349 set enable_hashagg to on;
350 explain (costs off)
351 select count(*) from
352 ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
353 QUERY PLAN
354 ----------------------------------------------------------------
355 Aggregate
356 -> HashAggregate
357 Group Key: tenk1.unique1
358 -> Append
359 -> Index Only Scan using tenk1_unique1 on tenk1
360 -> Seq Scan on tenk1 tenk1_1
361 (6 rows)
363 select count(*) from
364 ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
365 count
366 -------
367 10000
368 (1 row)
370 explain (costs off)
371 select count(*) from
372 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
373 QUERY PLAN
374 ------------------------------------------------------------------------------------
375 Aggregate
376 -> Subquery Scan on ss
377 -> HashSetOp Intersect
378 -> Append
379 -> Subquery Scan on "*SELECT* 2"
380 -> Seq Scan on tenk1
381 -> Subquery Scan on "*SELECT* 1"
382 -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
383 (8 rows)
385 select count(*) from
386 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
387 count
388 -------
389 5000
390 (1 row)
392 explain (costs off)
393 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
394 QUERY PLAN
395 ------------------------------------------------------------------------
396 HashSetOp Except
397 -> Append
398 -> Subquery Scan on "*SELECT* 1"
399 -> Index Only Scan using tenk1_unique1 on tenk1
400 -> Subquery Scan on "*SELECT* 2"
401 -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
402 Filter: (unique2 <> 10)
403 (7 rows)
405 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
406 unique1
407 ---------
408 10
409 (1 row)
411 set enable_hashagg to off;
412 explain (costs off)
413 select count(*) from
414 ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
415 QUERY PLAN
416 ----------------------------------------------------------------
417 Aggregate
418 -> Unique
419 -> Merge Append
420 Sort Key: tenk1.unique1
421 -> Index Only Scan using tenk1_unique1 on tenk1
422 -> Sort
423 Sort Key: tenk1_1.fivethous
424 -> Seq Scan on tenk1 tenk1_1
425 (8 rows)
427 select count(*) from
428 ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
429 count
430 -------
431 10000
432 (1 row)
434 explain (costs off)
435 select count(*) from
436 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
437 QUERY PLAN
438 ------------------------------------------------------------------------------------------
439 Aggregate
440 -> Subquery Scan on ss
441 -> SetOp Intersect
442 -> Sort
443 Sort Key: "*SELECT* 2".fivethous
444 -> Append
445 -> Subquery Scan on "*SELECT* 2"
446 -> Seq Scan on tenk1
447 -> Subquery Scan on "*SELECT* 1"
448 -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
449 (10 rows)
451 select count(*) from
452 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
453 count
454 -------
455 5000
456 (1 row)
458 explain (costs off)
459 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
460 QUERY PLAN
461 ------------------------------------------------------------------------------
462 SetOp Except
463 -> Sort
464 Sort Key: "*SELECT* 1".unique1
465 -> Append
466 -> Subquery Scan on "*SELECT* 1"
467 -> Index Only Scan using tenk1_unique1 on tenk1
468 -> Subquery Scan on "*SELECT* 2"
469 -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
470 Filter: (unique2 <> 10)
471 (9 rows)
473 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
474 unique1
475 ---------
476 10
477 (1 row)
479 reset enable_hashagg;
480 -- non-hashable type
481 set enable_hashagg to on;
482 explain (costs off)
483 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
484 QUERY PLAN
485 -----------------------------------------------
486 Unique
487 -> Sort
488 Sort Key: "*VALUES*".column1
489 -> Append
490 -> Values Scan on "*VALUES*"
491 -> Values Scan on "*VALUES*_1"
492 (6 rows)
494 set enable_hashagg to off;
495 explain (costs off)
496 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
497 QUERY PLAN
498 -----------------------------------------------
499 Unique
500 -> Sort
501 Sort Key: "*VALUES*".column1
502 -> Append
503 -> Values Scan on "*VALUES*"
504 -> Values Scan on "*VALUES*_1"
505 (6 rows)
507 reset enable_hashagg;
508 -- arrays
509 set enable_hashagg to on;
510 explain (costs off)
511 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
512 QUERY PLAN
513 -----------------------------------------
514 HashAggregate
515 Group Key: "*VALUES*".column1
516 -> Append
517 -> Values Scan on "*VALUES*"
518 -> Values Scan on "*VALUES*_1"
519 (5 rows)
521 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
522 x
523 -------
524 {1,4}
525 {1,2}
526 {1,3}
527 (3 rows)
529 explain (costs off)
530 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
531 QUERY PLAN
532 -----------------------------------------------
533 HashSetOp Intersect
534 -> Append
535 -> Subquery Scan on "*SELECT* 1"
536 -> Values Scan on "*VALUES*"
537 -> Subquery Scan on "*SELECT* 2"
538 -> Values Scan on "*VALUES*_1"
539 (6 rows)
541 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
542 x
543 -------
544 {1,2}
545 (1 row)
547 explain (costs off)
548 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
549 QUERY PLAN
550 -----------------------------------------------
551 HashSetOp Except
552 -> Append
553 -> Subquery Scan on "*SELECT* 1"
554 -> Values Scan on "*VALUES*"
555 -> Subquery Scan on "*SELECT* 2"
556 -> Values Scan on "*VALUES*_1"
557 (6 rows)
559 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
560 x
561 -------
562 {1,3}
563 (1 row)
565 -- non-hashable type
566 explain (costs off)
567 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
568 QUERY PLAN
569 -----------------------------------------------
570 Unique
571 -> Sort
572 Sort Key: "*VALUES*".column1
573 -> Append
574 -> Values Scan on "*VALUES*"
575 -> Values Scan on "*VALUES*_1"
576 (6 rows)
578 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
579 x
580 ------
581 {01}
582 {10}
583 {11}
584 (3 rows)
586 set enable_hashagg to off;
587 explain (costs off)
588 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
589 QUERY PLAN
590 -----------------------------------------------
591 Unique
592 -> Sort
593 Sort Key: "*VALUES*".column1
594 -> Append
595 -> Values Scan on "*VALUES*"
596 -> Values Scan on "*VALUES*_1"
597 (6 rows)
599 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
600 x
601 -------
602 {1,2}
603 {1,3}
604 {1,4}
605 (3 rows)
607 explain (costs off)
608 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
609 QUERY PLAN
610 -----------------------------------------------------
611 SetOp Intersect
612 -> Sort
613 Sort Key: "*SELECT* 1".x
614 -> Append
615 -> Subquery Scan on "*SELECT* 1"
616 -> Values Scan on "*VALUES*"
617 -> Subquery Scan on "*SELECT* 2"
618 -> Values Scan on "*VALUES*_1"
619 (8 rows)
621 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
622 x
623 -------
624 {1,2}
625 (1 row)
627 explain (costs off)
628 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
629 QUERY PLAN
630 -----------------------------------------------------
631 SetOp Except
632 -> Sort
633 Sort Key: "*SELECT* 1".x
634 -> Append
635 -> Subquery Scan on "*SELECT* 1"
636 -> Values Scan on "*VALUES*"
637 -> Subquery Scan on "*SELECT* 2"
638 -> Values Scan on "*VALUES*_1"
639 (8 rows)
641 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
642 x
643 -------
644 {1,3}
645 (1 row)
647 reset enable_hashagg;
648 -- records
649 set enable_hashagg to on;
650 explain (costs off)
651 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
652 QUERY PLAN
653 -----------------------------------------------
654 Unique
655 -> Sort
656 Sort Key: "*VALUES*".column1
657 -> Append
658 -> Values Scan on "*VALUES*"
659 -> Values Scan on "*VALUES*_1"
660 (6 rows)
662 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
663 x
664 -------
665 (1,2)
666 (1,3)
667 (1,4)
668 (3 rows)
670 explain (costs off)
671 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
672 QUERY PLAN
673 -----------------------------------------------------
674 SetOp Intersect
675 -> Sort
676 Sort Key: "*SELECT* 1".x
677 -> Append
678 -> Subquery Scan on "*SELECT* 1"
679 -> Values Scan on "*VALUES*"
680 -> Subquery Scan on "*SELECT* 2"
681 -> Values Scan on "*VALUES*_1"
682 (8 rows)
684 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
685 x
686 -------
687 (1,2)
688 (1 row)
690 explain (costs off)
691 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
692 QUERY PLAN
693 -----------------------------------------------------
694 SetOp Except
695 -> Sort
696 Sort Key: "*SELECT* 1".x
697 -> Append
698 -> Subquery Scan on "*SELECT* 1"
699 -> Values Scan on "*VALUES*"
700 -> Subquery Scan on "*SELECT* 2"
701 -> Values Scan on "*VALUES*_1"
702 (8 rows)
704 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
705 x
706 -------
707 (1,3)
708 (1 row)
710 -- non-hashable type
711 -- With an anonymous row type, the typcache does not report that the
712 -- type is hashable. (Otherwise, this would fail at execution time.)
713 explain (costs off)
714 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
715 QUERY PLAN
716 -----------------------------------------------
717 Unique
718 -> Sort
719 Sort Key: "*VALUES*".column1
720 -> Append
721 -> Values Scan on "*VALUES*"
722 -> Values Scan on "*VALUES*_1"
723 (6 rows)
725 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
726 x
727 ------
728 (01)
729 (10)
730 (11)
731 (3 rows)
733 -- With a defined row type, the typcache can inspect the type's fields
734 -- for hashability.
735 create type ct1 as (f1 varbit);
736 explain (costs off)
737 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
738 QUERY PLAN
739 -----------------------------------------------
740 Unique
741 -> Sort
742 Sort Key: "*VALUES*".column1
743 -> Append
744 -> Values Scan on "*VALUES*"
745 -> Values Scan on "*VALUES*_1"
746 (6 rows)
748 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
749 x
750 ------
751 (01)
752 (10)
753 (11)
754 (3 rows)
756 drop type ct1;
757 set enable_hashagg to off;
758 explain (costs off)
759 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
760 QUERY PLAN
761 -----------------------------------------------
762 Unique
763 -> Sort
764 Sort Key: "*VALUES*".column1
765 -> Append
766 -> Values Scan on "*VALUES*"
767 -> Values Scan on "*VALUES*_1"
768 (6 rows)
770 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
771 x
772 -------
773 (1,2)
774 (1,3)
775 (1,4)
776 (3 rows)
778 explain (costs off)
779 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
780 QUERY PLAN
781 -----------------------------------------------------
782 SetOp Intersect
783 -> Sort
784 Sort Key: "*SELECT* 1".x
785 -> Append
786 -> Subquery Scan on "*SELECT* 1"
787 -> Values Scan on "*VALUES*"
788 -> Subquery Scan on "*SELECT* 2"
789 -> Values Scan on "*VALUES*_1"
790 (8 rows)
792 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
793 x
794 -------
795 (1,2)
796 (1 row)
798 explain (costs off)
799 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
800 QUERY PLAN
801 -----------------------------------------------------
802 SetOp Except
803 -> Sort
804 Sort Key: "*SELECT* 1".x
805 -> Append
806 -> Subquery Scan on "*SELECT* 1"
807 -> Values Scan on "*VALUES*"
808 -> Subquery Scan on "*SELECT* 2"
809 -> Values Scan on "*VALUES*_1"
810 (8 rows)
812 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
813 x
814 -------
815 (1,3)
816 (1 row)
818 -- non-sortable type
819 -- Ensure we get a HashAggregate plan. Keep enable_hashagg=off to ensure
820 -- there's no chance of a sort.
821 explain (costs off) select '123'::xid union select '123'::xid;
822 QUERY PLAN
823 ---------------------------
824 HashAggregate
825 Disabled Nodes: 1
826 Group Key: ('123'::xid)
827 -> Append
828 -> Result
829 -> Result
830 (6 rows)
832 reset enable_hashagg;
833 --
834 -- Mixed types
835 --
836 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
837 f1
838 ----
839 0
840 (1 row)
842 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
843 f1
844 -----------------------
845 -1.2345678901234e+200
846 -1004.3
847 -34.84
848 -1.2345678901234e-200
849 (4 rows)
851 --
852 -- Operator precedence and (((((extra))))) parentheses
853 --
854 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1;
855 q1
856 -------------------
857 -4567890123456789
858 123
859 123
860 456
861 4567890123456789
862 4567890123456789
863 4567890123456789
864 (7 rows)
866 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1;
867 q1
868 ------------------
869 123
870 4567890123456789
871 (2 rows)
873 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl;
874 q1
875 -------------------
876 123
877 4567890123456789
878 456
879 4567890123456789
880 123
881 4567890123456789
882 -4567890123456789
883 (7 rows)
885 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
886 q1
887 -------------------
888 -4567890123456789
889 456
890 (2 rows)
892 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
893 q1
894 -------------------
895 123
896 123
897 4567890123456789
898 4567890123456789
899 4567890123456789
900 -4567890123456789
901 456
902 (7 rows)
904 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
905 q1
906 -------------------
907 -4567890123456789
908 456
909 (2 rows)
911 --
912 -- Subqueries with ORDER BY & LIMIT clauses
913 --
914 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
915 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
916 ORDER BY q2,q1;
917 q1 | q2
918 ------------------+-------------------
919 4567890123456789 | -4567890123456789
920 123 | 456
921 (2 rows)
923 -- This should fail, because q2 isn't a name of an EXCEPT output column
924 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
925 ERROR: column "q2" does not exist
926 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
927 ^
928 DETAIL: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
929 -- But this should work:
930 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
931 q1
932 ------------------
933 123
934 4567890123456789
935 (2 rows)
937 --
938 -- New syntaxes (7.1) permit new tests
939 --
940 (((((select * from int8_tbl)))));
941 q1 | q2
942 ------------------+-------------------
943 123 | 456
944 123 | 4567890123456789
945 4567890123456789 | 123
946 4567890123456789 | 4567890123456789
947 4567890123456789 | -4567890123456789
948 (5 rows)
950 --
951 -- Check behavior with empty select list (allowed since 9.4)
952 --
953 select union select;
954 --
955 (1 row)
957 select intersect select;
958 --
959 (1 row)
961 select except select;
962 --
963 (0 rows)
965 -- check hashed implementation
966 set enable_hashagg = true;
967 set enable_sort = false;
968 -- We've no way to check hashed UNION as the empty pathkeys in the Append are
969 -- fine to make use of Unique, which is cheaper than HashAggregate and we've
970 -- no means to disable Unique.
971 explain (costs off)
972 select from generate_series(1,5) intersect select from generate_series(1,3);
973 QUERY PLAN
974 ----------------------------------------------------------------------
975 HashSetOp Intersect
976 -> Append
977 -> Subquery Scan on "*SELECT* 1"
978 -> Function Scan on generate_series
979 -> Subquery Scan on "*SELECT* 2"
980 -> Function Scan on generate_series generate_series_1
981 (6 rows)
983 select from generate_series(1,5) union all select from generate_series(1,3);
984 --
985 (8 rows)
987 select from generate_series(1,5) intersect select from generate_series(1,3);
988 --
989 (1 row)
991 select from generate_series(1,5) intersect all select from generate_series(1,3);
992 --
993 (3 rows)
995 select from generate_series(1,5) except select from generate_series(1,3);
996 --
997 (0 rows)
999 select from generate_series(1,5) except all select from generate_series(1,3);
1000 --
1001 (2 rows)
1003 -- check sorted implementation
1004 set enable_hashagg = false;
1005 set enable_sort = true;
1006 explain (costs off)
1007 select from generate_series(1,5) union select from generate_series(1,3);
1008 QUERY PLAN
1009 ----------------------------------------------------------------
1010 Unique
1011 -> Append
1012 -> Function Scan on generate_series
1013 -> Function Scan on generate_series generate_series_1
1014 (4 rows)
1016 explain (costs off)
1017 select from generate_series(1,5) intersect select from generate_series(1,3);
1018 QUERY PLAN
1019 ----------------------------------------------------------------------
1020 SetOp Intersect
1021 -> Append
1022 -> Subquery Scan on "*SELECT* 1"
1023 -> Function Scan on generate_series
1024 -> Subquery Scan on "*SELECT* 2"
1025 -> Function Scan on generate_series generate_series_1
1026 (6 rows)
1028 select from generate_series(1,5) union select from generate_series(1,3);
1029 --
1030 (1 row)
1032 select from generate_series(1,5) union all select from generate_series(1,3);
1033 --
1034 (8 rows)
1036 select from generate_series(1,5) intersect select from generate_series(1,3);
1037 --
1038 (1 row)
1040 select from generate_series(1,5) intersect all select from generate_series(1,3);
1041 --
1042 (3 rows)
1044 select from generate_series(1,5) except select from generate_series(1,3);
1045 --
1046 (0 rows)
1048 select from generate_series(1,5) except all select from generate_series(1,3);
1049 --
1050 (2 rows)
1052 -- Try a variation of the above but with a CTE which contains a column, again
1053 -- with an empty final select list.
1054 -- Ensure we get the expected 1 row with 0 columns
1055 with cte as materialized (select s from generate_series(1,5) s)
1056 select from cte union select from cte;
1057 --
1058 (1 row)
1060 -- Ensure we get the same result as the above.
1061 with cte as not materialized (select s from generate_series(1,5) s)
1062 select from cte union select from cte;
1063 --
1064 (1 row)
1066 reset enable_hashagg;
1067 reset enable_sort;
1068 --
1069 -- Check handling of a case with unknown constants. We don't guarantee
1070 -- an undecorated constant will work in all cases, but historically this
1071 -- usage has worked, so test we don't break it.
1072 --
1073 SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
1074 UNION
1075 SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
1076 ORDER BY 1;
1077 f1
1078 ------
1079 a
1080 ab
1081 abcd
1082 test
1083 (4 rows)
1085 -- This should fail, but it should produce an error cursor
1086 SELECT '3.4'::numeric UNION SELECT 'foo';
1087 ERROR: invalid input syntax for type numeric: "foo"
1088 LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
1089 ^
1090 --
1091 -- Test that expression-index constraints can be pushed down through
1092 -- UNION or UNION ALL
1093 --
1094 CREATE TEMP TABLE t1 (a text, b text);
1095 CREATE INDEX t1_ab_idx on t1 ((a || b));
1096 CREATE TEMP TABLE t2 (ab text primary key);
1097 INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
1098 INSERT INTO t2 VALUES ('ab'), ('xy');
1099 set enable_seqscan = off;
1100 set enable_indexscan = on;
1101 set enable_bitmapscan = off;
1102 set enable_sort = off;
1103 explain (costs off)
1104 SELECT * FROM
1105 (SELECT a || b AS ab FROM t1
1106 UNION ALL
1107 SELECT * FROM t2) t
1108 WHERE ab = 'ab';
1109 QUERY PLAN
1110 ---------------------------------------------
1111 Append
1112 -> Index Scan using t1_ab_idx on t1
1113 Index Cond: ((a || b) = 'ab'::text)
1114 -> Index Only Scan using t2_pkey on t2
1115 Index Cond: (ab = 'ab'::text)
1116 (5 rows)
1118 explain (costs off)
1119 SELECT * FROM
1120 (SELECT a || b AS ab FROM t1
1121 UNION
1122 SELECT * FROM t2) t
1123 WHERE ab = 'ab';
1124 QUERY PLAN
1125 ---------------------------------------------------
1126 HashAggregate
1127 Group Key: ((t1.a || t1.b))
1128 -> Append
1129 -> Index Scan using t1_ab_idx on t1
1130 Index Cond: ((a || b) = 'ab'::text)
1131 -> Index Only Scan using t2_pkey on t2
1132 Index Cond: (ab = 'ab'::text)
1133 (7 rows)
1135 --
1136 -- Test that ORDER BY for UNION ALL can be pushed down to inheritance
1137 -- children.
1138 --
1139 CREATE TEMP TABLE t1c (b text, a text);
1140 ALTER TABLE t1c INHERIT t1;
1141 CREATE TEMP TABLE t2c (primary key (ab)) INHERITS (t2);
1142 INSERT INTO t1c VALUES ('v', 'w'), ('c', 'd'), ('m', 'n'), ('e', 'f');
1143 INSERT INTO t2c VALUES ('vw'), ('cd'), ('mn'), ('ef');
1144 CREATE INDEX t1c_ab_idx on t1c ((a || b));
1145 set enable_seqscan = on;
1146 set enable_indexonlyscan = off;
1147 explain (costs off)
1148 SELECT * FROM
1149 (SELECT a || b AS ab FROM t1
1150 UNION ALL
1151 SELECT ab FROM t2) t
1152 ORDER BY 1 LIMIT 8;
1153 QUERY PLAN
1154 -----------------------------------------------------
1155 Limit
1156 -> Merge Append
1157 Sort Key: ((t1.a || t1.b))
1158 -> Index Scan using t1_ab_idx on t1
1159 -> Index Scan using t1c_ab_idx on t1c t1_1
1160 -> Index Scan using t2_pkey on t2
1161 -> Index Scan using t2c_pkey on t2c t2_1
1162 (7 rows)
1164 SELECT * FROM
1165 (SELECT a || b AS ab FROM t1
1166 UNION ALL
1167 SELECT ab FROM t2) t
1168 ORDER BY 1 LIMIT 8;
1169 ab
1170 ----
1171 ab
1172 ab
1173 cd
1174 dc
1175 ef
1176 fe
1177 mn
1178 nm
1179 (8 rows)
1181 reset enable_seqscan;
1182 reset enable_indexscan;
1183 reset enable_bitmapscan;
1184 reset enable_sort;
1185 -- This simpler variant of the above test has been observed to fail differently
1186 create table events (event_id int primary key);
1187 create table other_events (event_id int primary key);
1188 create table events_child () inherits (events);
1189 explain (costs off)
1190 select event_id
1191 from (select event_id from events
1192 union all
1193 select event_id from other_events) ss
1194 order by event_id;
1195 QUERY PLAN
1196 ----------------------------------------------------------
1197 Merge Append
1198 Sort Key: events.event_id
1199 -> Index Scan using events_pkey on events
1200 -> Sort
1201 Sort Key: events_1.event_id
1202 -> Seq Scan on events_child events_1
1203 -> Index Scan using other_events_pkey on other_events
1204 (7 rows)
1206 drop table events_child, events, other_events;
1207 reset enable_indexonlyscan;
1208 -- Test constraint exclusion of UNION ALL subqueries
1209 explain (costs off)
1210 SELECT * FROM
1211 (SELECT 1 AS t, * FROM tenk1 a
1212 UNION ALL
1213 SELECT 2 AS t, * FROM tenk1 b) c
1214 WHERE t = 2;
1215 QUERY PLAN
1216 ---------------------
1217 Seq Scan on tenk1 b
1218 (1 row)
1220 -- Test that we push quals into UNION sub-selects only when it's safe
1221 explain (costs off)
1222 SELECT * FROM
1223 (SELECT 1 AS t, 2 AS x
1224 UNION
1225 SELECT 2 AS t, 4 AS x) ss
1226 WHERE x < 4
1227 ORDER BY x;
1228 QUERY PLAN
1229 --------------------------------------------------
1230 Sort
1231 Sort Key: (2)
1232 -> Unique
1233 -> Sort
1234 Sort Key: (1), (2)
1235 -> Append
1236 -> Result
1237 -> Result
1238 One-Time Filter: false
1239 (9 rows)
1241 SELECT * FROM
1242 (SELECT 1 AS t, 2 AS x
1243 UNION
1244 SELECT 2 AS t, 4 AS x) ss
1245 WHERE x < 4
1246 ORDER BY x;
1247 t | x
1248 ---+---
1249 1 | 2
1250 (1 row)
1252 explain (costs off)
1253 SELECT * FROM
1254 (SELECT 1 AS t, generate_series(1,10) AS x
1255 UNION
1256 SELECT 2 AS t, 4 AS x) ss
1257 WHERE x < 4
1258 ORDER BY x;
1259 QUERY PLAN
1260 --------------------------------------------------------
1261 Sort
1262 Sort Key: ss.x
1263 -> Subquery Scan on ss
1264 Filter: (ss.x < 4)
1265 -> HashAggregate
1266 Group Key: (1), (generate_series(1, 10))
1267 -> Append
1268 -> ProjectSet
1269 -> Result
1270 -> Result
1271 (10 rows)
1273 SELECT * FROM
1274 (SELECT 1 AS t, generate_series(1,10) AS x
1275 UNION
1276 SELECT 2 AS t, 4 AS x) ss
1277 WHERE x < 4
1278 ORDER BY x;
1279 t | x
1280 ---+---
1281 1 | 1
1282 1 | 2
1283 1 | 3
1284 (3 rows)
1286 explain (costs off)
1287 SELECT * FROM
1288 (SELECT 1 AS t, (random()*3)::int AS x
1289 UNION
1290 SELECT 2 AS t, 4 AS x) ss
1291 WHERE x > 3
1292 ORDER BY x;
1293 QUERY PLAN
1294 ------------------------------------------------------------------------------------
1295 Sort
1296 Sort Key: ss.x
1297 -> Subquery Scan on ss
1298 Filter: (ss.x > 3)
1299 -> Unique
1300 -> Sort
1301 Sort Key: (1), (((random() * '3'::double precision))::integer)
1302 -> Append
1303 -> Result
1304 -> Result
1305 (10 rows)
1307 SELECT * FROM
1308 (SELECT 1 AS t, (random()*3)::int AS x
1309 UNION
1310 SELECT 2 AS t, 4 AS x) ss
1311 WHERE x > 3
1312 ORDER BY x;
1313 t | x
1314 ---+---
1315 2 | 4
1316 (1 row)
1318 -- Test cases where the native ordering of a sub-select has more pathkeys
1319 -- than the outer query cares about
1320 explain (costs off)
1321 select distinct q1 from
1322 (select distinct * from int8_tbl i81
1323 union all
1324 select distinct * from int8_tbl i82) ss
1325 where q2 = q2;
1326 QUERY PLAN
1327 ----------------------------------------------------------
1328 Unique
1329 -> Merge Append
1330 Sort Key: "*SELECT* 1".q1
1331 -> Subquery Scan on "*SELECT* 1"
1332 -> Unique
1333 -> Sort
1334 Sort Key: i81.q1, i81.q2
1335 -> Seq Scan on int8_tbl i81
1336 Filter: (q2 IS NOT NULL)
1337 -> Subquery Scan on "*SELECT* 2"
1338 -> Unique
1339 -> Sort
1340 Sort Key: i82.q1, i82.q2
1341 -> Seq Scan on int8_tbl i82
1342 Filter: (q2 IS NOT NULL)
1343 (15 rows)
1345 select distinct q1 from
1346 (select distinct * from int8_tbl i81
1347 union all
1348 select distinct * from int8_tbl i82) ss
1349 where q2 = q2;
1350 q1
1351 ------------------
1352 123
1353 4567890123456789
1354 (2 rows)
1356 explain (costs off)
1357 select distinct q1 from
1358 (select distinct * from int8_tbl i81
1359 union all
1360 select distinct * from int8_tbl i82) ss
1361 where -q1 = q2;
1362 QUERY PLAN
1363 --------------------------------------------------------
1364 Unique
1365 -> Merge Append
1366 Sort Key: "*SELECT* 1".q1
1367 -> Subquery Scan on "*SELECT* 1"
1368 -> Unique
1369 -> Sort
1370 Sort Key: i81.q1, i81.q2
1371 -> Seq Scan on int8_tbl i81
1372 Filter: ((- q1) = q2)
1373 -> Subquery Scan on "*SELECT* 2"
1374 -> Unique
1375 -> Sort
1376 Sort Key: i82.q1, i82.q2
1377 -> Seq Scan on int8_tbl i82
1378 Filter: ((- q1) = q2)
1379 (15 rows)
1381 select distinct q1 from
1382 (select distinct * from int8_tbl i81
1383 union all
1384 select distinct * from int8_tbl i82) ss
1385 where -q1 = q2;
1386 q1
1387 ------------------
1388 4567890123456789
1389 (1 row)
1391 -- Test proper handling of parameterized appendrel paths when the
1392 -- potential join qual is expensive
1393 create function expensivefunc(int) returns int
1394 language plpgsql immutable strict cost 10000
1395 as $$begin return $1; end$$;
1396 create temp table t3 as select generate_series(-1000,1000) as x;
1397 create index t3i on t3 (expensivefunc(x));
1398 analyze t3;
1399 explain (costs off)
1400 select * from
1401 (select * from t3 a union all select * from t3 b) ss
1402 join int4_tbl on f1 = expensivefunc(x);
1403 QUERY PLAN
1404 ------------------------------------------------------------
1405 Nested Loop
1406 -> Seq Scan on int4_tbl
1407 -> Append
1408 -> Index Scan using t3i on t3 a
1409 Index Cond: (expensivefunc(x) = int4_tbl.f1)
1410 -> Index Scan using t3i on t3 b
1411 Index Cond: (expensivefunc(x) = int4_tbl.f1)
1412 (7 rows)
1414 select * from
1415 (select * from t3 a union all select * from t3 b) ss
1416 join int4_tbl on f1 = expensivefunc(x);
1417 x | f1
1418 ---+----
1419 0 | 0
1420 0 | 0
1421 (2 rows)
1423 drop table t3;
1424 drop function expensivefunc(int);
1425 -- Test handling of appendrel quals that const-simplify into an AND
1426 explain (costs off)
1427 select * from
1428 (select *, 0 as x from int8_tbl a
1429 union all
1430 select *, 1 as x from int8_tbl b) ss
1431 where (x = 0) or (q1 >= q2 and q1 <= q2);
1432 QUERY PLAN
1433 ---------------------------------------------
1434 Append
1435 -> Seq Scan on int8_tbl a
1436 -> Seq Scan on int8_tbl b
1437 Filter: ((q1 >= q2) AND (q1 <= q2))
1438 (4 rows)
1440 select * from
1441 (select *, 0 as x from int8_tbl a
1442 union all
1443 select *, 1 as x from int8_tbl b) ss
1444 where (x = 0) or (q1 >= q2 and q1 <= q2);
1445 q1 | q2 | x
1446 ------------------+-------------------+---
1447 123 | 456 | 0
1448 123 | 4567890123456789 | 0
1449 4567890123456789 | 123 | 0
1450 4567890123456789 | 4567890123456789 | 0
1451 4567890123456789 | -4567890123456789 | 0
1452 4567890123456789 | 4567890123456789 | 1
1453 (6 rows)
1455 --
1456 -- Test the planner's ability to produce cheap startup plans with Append nodes
1457 --
1458 -- Ensure we get a Nested Loop join between tenk1 and tenk2
1459 explain (costs off)
1460 select t1.unique1 from tenk1 t1
1461 inner join tenk2 t2 on t1.tenthous = t2.tenthous and t2.thousand = 0
1462 union all
1463 (values(1)) limit 1;
1464 QUERY PLAN
1465 --------------------------------------------------------
1466 Limit
1467 -> Append
1468 -> Nested Loop
1469 Join Filter: (t1.tenthous = t2.tenthous)
1470 -> Seq Scan on tenk1 t1
1471 -> Materialize
1472 -> Seq Scan on tenk2 t2
1473 Filter: (thousand = 0)
1474 -> Result
1475 (9 rows)
1477 -- Ensure there is no problem if cheapest_startup_path is NULL
1478 explain (costs off)
1479 select * from tenk1 t1
1480 left join lateral
1481 (select t1.tenthous from tenk2 t2 union all (values(1)))
1482 on true limit 1;
1483 QUERY PLAN
1484 -------------------------------------------------------------------
1485 Limit
1486 -> Nested Loop Left Join
1487 -> Seq Scan on tenk1 t1
1488 -> Append
1489 -> Index Only Scan using tenk2_hundred on tenk2 t2
1490 -> Result
1491 (6 rows)