diff options
Diffstat (limited to 'src/test/regress/expected/union.out')
| -rw-r--r-- | src/test/regress/expected/union.out | 1434 |
1 files changed, 1434 insertions, 0 deletions
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out new file mode 100644 index 0000000..e2613d6 --- /dev/null +++ b/src/test/regress/expected/union.out @@ -0,0 +1,1434 @@ +-- +-- UNION (also INTERSECT, EXCEPT) +-- +-- Simple UNION constructs +SELECT 1 AS two UNION SELECT 2 ORDER BY 1; + two +----- + 1 + 2 +(2 rows) + +SELECT 1 AS one UNION SELECT 1 ORDER BY 1; + one +----- + 1 +(1 row) + +SELECT 1 AS two UNION ALL SELECT 2; + two +----- + 1 + 2 +(2 rows) + +SELECT 1 AS two UNION ALL SELECT 1; + two +----- + 1 + 1 +(2 rows) + +SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1; + three +------- + 1 + 2 + 3 +(3 rows) + +SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1; + two +----- + 1 + 2 +(2 rows) + +SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1; + three +------- + 1 + 2 + 2 +(3 rows) + +SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1; + two +----- + 1.1 + 2.2 +(2 rows) + +-- Mixed types +SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1; + two +----- + 1.1 + 2 +(2 rows) + +SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1; + two +----- + 1 + 2.2 +(2 rows) + +SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1; + one +----- + 1 +(1 row) + +SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1; + two +----- + 1.1 + 2 +(2 rows) + +SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1; + two +----- + 1 + 1 +(2 rows) + +SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1; + three +------- + 1.1 + 2 + 3 +(3 rows) + +SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1; + two +----- + 1.1 + 2 +(2 rows) + +SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1; + three +------- + 1.1 + 2 + 2 +(3 rows) + +SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1; + two +----- + 1.1 + 2 +(2 rows) + +-- +-- Try testing from tables... +-- +SELECT f1 AS five FROM FLOAT8_TBL +UNION +SELECT f1 FROM FLOAT8_TBL +ORDER BY 1; + five +----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 +(5 rows) + +SELECT f1 AS ten FROM FLOAT8_TBL +UNION ALL +SELECT f1 FROM FLOAT8_TBL; + ten +----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 +(10 rows) + +SELECT f1 AS nine FROM FLOAT8_TBL +UNION +SELECT f1 FROM INT4_TBL +ORDER BY 1; + nine +----------------------- + -1.2345678901234e+200 + -2147483647 + -123456 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 + 2147483647 +(9 rows) + +SELECT f1 AS ten FROM FLOAT8_TBL +UNION ALL +SELECT f1 FROM INT4_TBL; + ten +----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + 123456 + -123456 + 2147483647 + -2147483647 +(10 rows) + +SELECT f1 AS five FROM FLOAT8_TBL + WHERE f1 BETWEEN -1e6 AND 1e6 +UNION +SELECT f1 FROM INT4_TBL + WHERE f1 BETWEEN 0 AND 1000000 +ORDER BY 1; + five +----------------------- + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 +(5 rows) + +SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL +UNION +SELECT f1 FROM CHAR_TBL +ORDER BY 1; + three +------- + a + ab + abcd +(3 rows) + +SELECT f1 AS three FROM VARCHAR_TBL +UNION +SELECT CAST(f1 AS varchar) FROM CHAR_TBL +ORDER BY 1; + three +------- + a + ab + abcd +(3 rows) + +SELECT f1 AS eight FROM VARCHAR_TBL +UNION ALL +SELECT f1 FROM CHAR_TBL; + eight +------- + a + ab + abcd + abcd + a + ab + abcd + abcd +(8 rows) + +SELECT f1 AS five FROM TEXT_TBL +UNION +SELECT f1 FROM VARCHAR_TBL +UNION +SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL +ORDER BY 1; + five +------------------- + a + ab + abcd + doh! + hi de ho neighbor +(5 rows) + +-- +-- INTERSECT and EXCEPT +-- +SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1; + q2 +------------------ + 123 + 4567890123456789 +(2 rows) + +SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1; + q2 +------------------ + 123 + 4567890123456789 + 4567890123456789 +(3 rows) + +SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; + q2 +------------------- + -4567890123456789 + 456 +(2 rows) + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1; + q2 +------------------- + -4567890123456789 + 456 +(2 rows) + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1; + q2 +------------------- + -4567890123456789 + 456 + 4567890123456789 +(3 rows) + +SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1; + q1 +---- +(0 rows) + +SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1; + q1 +------------------ + 123 + 4567890123456789 +(2 rows) + +SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1; + q1 +------------------ + 123 + 4567890123456789 + 4567890123456789 +(3 rows) + +SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE; +ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT +-- nested cases +(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6; + ?column? | ?column? | ?column? +----------+----------+---------- + 4 | 5 | 6 +(1 row) + +(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6; + ?column? | ?column? | ?column? +----------+----------+---------- + 4 | 5 | 6 +(1 row) + +(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6; + ?column? | ?column? | ?column? +----------+----------+---------- + 1 | 2 | 3 +(1 row) + +(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6; + ?column? | ?column? | ?column? +----------+----------+---------- + 1 | 2 | 3 +(1 row) + +-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT +set enable_hashagg to on; +explain (costs off) +select count(*) from + ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> HashAggregate + Group Key: tenk1.unique1 + -> Append + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 tenk1_1 +(6 rows) + +select count(*) from + ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; + count +------- + 10000 +(1 row) + +explain (costs off) +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + QUERY PLAN +------------------------------------------------------------------------------------ + Aggregate + -> Subquery Scan on ss + -> HashSetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 2" + -> Seq Scan on tenk1 + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 +(8 rows) + +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + count +------- + 5000 +(1 row) + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + QUERY PLAN +------------------------------------------------------------------------ + HashSetOp Except + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Subquery Scan on "*SELECT* 2" + -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + Filter: (unique2 <> 10) +(7 rows) + +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + unique1 +--------- + 10 +(1 row) + +set enable_hashagg to off; +explain (costs off) +select count(*) from + ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Unique + -> Sort + Sort Key: tenk1.unique1 + -> Append + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 tenk1_1 +(7 rows) + +select count(*) from + ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; + count +------- + 10000 +(1 row) + +explain (costs off) +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + QUERY PLAN +------------------------------------------------------------------------------------------ + Aggregate + -> Subquery Scan on ss + -> SetOp Intersect + -> Sort + Sort Key: "*SELECT* 2".fivethous + -> Append + -> Subquery Scan on "*SELECT* 2" + -> Seq Scan on tenk1 + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 +(10 rows) + +select count(*) from + ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; + count +------- + 5000 +(1 row) + +explain (costs off) +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + QUERY PLAN +------------------------------------------------------------------------------ + SetOp Except + -> Sort + Sort Key: "*SELECT* 1".unique1 + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Only Scan using tenk1_unique1 on tenk1 + -> Subquery Scan on "*SELECT* 2" + -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + Filter: (unique2 <> 10) +(9 rows) + +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + unique1 +--------- + 10 +(1 row) + +reset enable_hashagg; +-- non-hashable type +set enable_hashagg to on; +explain (costs off) +select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +set enable_hashagg to off; +explain (costs off) +select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +reset enable_hashagg; +-- arrays +set enable_hashagg to on; +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------- + HashAggregate + Group Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(5 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,4} + {1,2} + {1,3} +(3 rows) + +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------------- + HashSetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,2} +(1 row) + +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------------- + HashSetOp Except + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,3} +(1 row) + +-- non-hashable type +explain (costs off) +select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x); + x +----------- + {$100.00} + {$200.00} + {$300.00} +(3 rows) + +set enable_hashagg to off; +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,2} + {1,3} + {1,4} +(3 rows) + +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Intersect + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,2} +(1 row) + +explain (costs off) +select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Except + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x); + x +------- + {1,3} +(1 row) + +reset enable_hashagg; +-- records +set enable_hashagg to on; +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,2) + (1,3) + (1,4) +(3 rows) + +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Intersect + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,2) +(1 row) + +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Except + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,3) +(1 row) + +-- non-hashable type +-- With an anonymous row type, the typcache does not report that the +-- type is hashable. (Otherwise, this would fail at execution time.) +explain (costs off) +select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x); + x +----------- + ($100.00) + ($200.00) + ($300.00) +(3 rows) + +-- With a defined row type, the typcache can inspect the type's fields +-- for hashability. +create type ct1 as (f1 money); +explain (costs off) +select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x) union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x) union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)) _(x); + x +----------- + ($100.00) + ($200.00) + ($300.00) +(3 rows) + +drop type ct1; +set enable_hashagg to off; +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------- + Unique + -> Sort + Sort Key: "*VALUES*".column1 + -> Append + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*_1" +(6 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,2) + (1,3) + (1,4) +(3 rows) + +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Intersect + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,2) +(1 row) + +explain (costs off) +select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); + QUERY PLAN +----------------------------------------------------- + SetOp Except + -> Sort + Sort Key: "*SELECT* 1".x + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Values Scan on "*VALUES*" + -> Subquery Scan on "*SELECT* 2" + -> Values Scan on "*VALUES*_1" +(8 rows) + +select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); + x +------- + (1,3) +(1 row) + +reset enable_hashagg; +-- +-- Mixed types +-- +SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1; + f1 +---- + 0 +(1 row) + +SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1; + f1 +----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 +(4 rows) + +-- +-- Operator precedence and (((((extra))))) parentheses +-- +SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1; + q1 +------------------- + -4567890123456789 + 123 + 123 + 456 + 4567890123456789 + 4567890123456789 + 4567890123456789 +(7 rows) + +SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1; + q1 +------------------ + 123 + 4567890123456789 +(2 rows) + +(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl; + q1 +------------------- + 123 + 4567890123456789 + 456 + 4567890123456789 + 123 + 4567890123456789 + -4567890123456789 +(7 rows) + +SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; + q1 +------------------- + -4567890123456789 + 456 +(2 rows) + +SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1))); + q1 +------------------- + 123 + 123 + 4567890123456789 + 4567890123456789 + 4567890123456789 + -4567890123456789 + 456 +(7 rows) + +(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; + q1 +------------------- + -4567890123456789 + 456 +(2 rows) + +-- +-- Subqueries with ORDER BY & LIMIT clauses +-- +-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT +SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl +ORDER BY q2,q1; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 + 123 | 456 +(2 rows) + +-- This should fail, because q2 isn't a name of an EXCEPT output column +SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1; +ERROR: column "q2" does not exist +LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1... + ^ +DETAIL: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query. +-- But this should work: +SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1; + q1 +------------------ + 123 + 4567890123456789 +(2 rows) + +-- +-- New syntaxes (7.1) permit new tests +-- +(((((select * from int8_tbl))))); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +-- +-- Check behavior with empty select list (allowed since 9.4) +-- +select union select; +-- +(1 row) + +select intersect select; +-- +(1 row) + +select except select; +-- +(0 rows) + +-- check hashed implementation +set enable_hashagg = true; +set enable_sort = false; +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------- + HashAggregate + -> Append + -> Function Scan on generate_series + -> Function Scan on generate_series generate_series_1 +(4 rows) + +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------------- + HashSetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Function Scan on generate_series + -> Subquery Scan on "*SELECT* 2" + -> Function Scan on generate_series generate_series_1 +(6 rows) + +select from generate_series(1,5) union select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) union all select from generate_series(1,3); +-- +(8 rows) + +select from generate_series(1,5) intersect select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) intersect all select from generate_series(1,3); +-- +(3 rows) + +select from generate_series(1,5) except select from generate_series(1,3); +-- +(0 rows) + +select from generate_series(1,5) except all select from generate_series(1,3); +-- +(2 rows) + +-- check sorted implementation +set enable_hashagg = false; +set enable_sort = true; +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------- + Unique + -> Append + -> Function Scan on generate_series + -> Function Scan on generate_series generate_series_1 +(4 rows) + +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------------- + SetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Function Scan on generate_series + -> Subquery Scan on "*SELECT* 2" + -> Function Scan on generate_series generate_series_1 +(6 rows) + +select from generate_series(1,5) union select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) union all select from generate_series(1,3); +-- +(8 rows) + +select from generate_series(1,5) intersect select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) intersect all select from generate_series(1,3); +-- +(3 rows) + +select from generate_series(1,5) except select from generate_series(1,3); +-- +(0 rows) + +select from generate_series(1,5) except all select from generate_series(1,3); +-- +(2 rows) + +reset enable_hashagg; +reset enable_sort; +-- +-- Check handling of a case with unknown constants. We don't guarantee +-- an undecorated constant will work in all cases, but historically this +-- usage has worked, so test we don't break it. +-- +SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a +UNION +SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b +ORDER BY 1; + f1 +------ + a + ab + abcd + test +(4 rows) + +-- This should fail, but it should produce an error cursor +SELECT '3.4'::numeric UNION SELECT 'foo'; +ERROR: invalid input syntax for type numeric: "foo" +LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo'; + ^ +-- +-- Test that expression-index constraints can be pushed down through +-- UNION or UNION ALL +-- +CREATE TEMP TABLE t1 (a text, b text); +CREATE INDEX t1_ab_idx on t1 ((a || b)); +CREATE TEMP TABLE t2 (ab text primary key); +INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y'); +INSERT INTO t2 VALUES ('ab'), ('xy'); +set enable_seqscan = off; +set enable_indexscan = on; +set enable_bitmapscan = off; +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION ALL + SELECT * FROM t2) t + WHERE ab = 'ab'; + QUERY PLAN +--------------------------------------------- + Append + -> Index Scan using t1_ab_idx on t1 + Index Cond: ((a || b) = 'ab'::text) + -> Index Only Scan using t2_pkey on t2 + Index Cond: (ab = 'ab'::text) +(5 rows) + +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION + SELECT * FROM t2) t + WHERE ab = 'ab'; + QUERY PLAN +--------------------------------------------------- + HashAggregate + Group Key: ((t1.a || t1.b)) + -> Append + -> Index Scan using t1_ab_idx on t1 + Index Cond: ((a || b) = 'ab'::text) + -> Index Only Scan using t2_pkey on t2 + Index Cond: (ab = 'ab'::text) +(7 rows) + +-- +-- Test that ORDER BY for UNION ALL can be pushed down to inheritance +-- children. +-- +CREATE TEMP TABLE t1c (b text, a text); +ALTER TABLE t1c INHERIT t1; +CREATE TEMP TABLE t2c (primary key (ab)) INHERITS (t2); +INSERT INTO t1c VALUES ('v', 'w'), ('c', 'd'), ('m', 'n'), ('e', 'f'); +INSERT INTO t2c VALUES ('vw'), ('cd'), ('mn'), ('ef'); +CREATE INDEX t1c_ab_idx on t1c ((a || b)); +set enable_seqscan = on; +set enable_indexonlyscan = off; +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION ALL + SELECT ab FROM t2) t + ORDER BY 1 LIMIT 8; + QUERY PLAN +----------------------------------------------------- + Limit + -> Merge Append + Sort Key: ((t1.a || t1.b)) + -> Index Scan using t1_ab_idx on t1 + -> Index Scan using t1c_ab_idx on t1c t1_1 + -> Index Scan using t2_pkey on t2 + -> Index Scan using t2c_pkey on t2c t2_1 +(7 rows) + + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION ALL + SELECT ab FROM t2) t + ORDER BY 1 LIMIT 8; + ab +---- + ab + ab + cd + dc + ef + fe + mn + nm +(8 rows) + +reset enable_seqscan; +reset enable_indexscan; +reset enable_bitmapscan; +-- This simpler variant of the above test has been observed to fail differently +create table events (event_id int primary key); +create table other_events (event_id int primary key); +create table events_child () inherits (events); +explain (costs off) +select event_id + from (select event_id from events + union all + select event_id from other_events) ss + order by event_id; + QUERY PLAN +---------------------------------------------------------- + Merge Append + Sort Key: events.event_id + -> Index Scan using events_pkey on events + -> Sort + Sort Key: events_1.event_id + -> Seq Scan on events_child events_1 + -> Index Scan using other_events_pkey on other_events +(7 rows) + +drop table events_child, events, other_events; +reset enable_indexonlyscan; +-- Test constraint exclusion of UNION ALL subqueries +explain (costs off) + SELECT * FROM + (SELECT 1 AS t, * FROM tenk1 a + UNION ALL + SELECT 2 AS t, * FROM tenk1 b) c + WHERE t = 2; + QUERY PLAN +--------------------- + Seq Scan on tenk1 b +(1 row) + +-- Test that we push quals into UNION sub-selects only when it's safe +explain (costs off) +SELECT * FROM + (SELECT 1 AS t, 2 AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x < 4 +ORDER BY x; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: (2) + -> Unique + -> Sort + Sort Key: (1), (2) + -> Append + -> Result + -> Result + One-Time Filter: false +(9 rows) + +SELECT * FROM + (SELECT 1 AS t, 2 AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x < 4 +ORDER BY x; + t | x +---+--- + 1 | 2 +(1 row) + +explain (costs off) +SELECT * FROM + (SELECT 1 AS t, generate_series(1,10) AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x < 4 +ORDER BY x; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: ss.x + -> Subquery Scan on ss + Filter: (ss.x < 4) + -> HashAggregate + Group Key: (1), (generate_series(1, 10)) + -> Append + -> ProjectSet + -> Result + -> Result +(10 rows) + +SELECT * FROM + (SELECT 1 AS t, generate_series(1,10) AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x < 4 +ORDER BY x; + t | x +---+--- + 1 | 1 + 1 | 2 + 1 | 3 +(3 rows) + +explain (costs off) +SELECT * FROM + (SELECT 1 AS t, (random()*3)::int AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x > 3 +ORDER BY x; + QUERY PLAN +------------------------------------------------------------------------------------ + Sort + Sort Key: ss.x + -> Subquery Scan on ss + Filter: (ss.x > 3) + -> Unique + -> Sort + Sort Key: (1), (((random() * '3'::double precision))::integer) + -> Append + -> Result + -> Result +(10 rows) + +SELECT * FROM + (SELECT 1 AS t, (random()*3)::int AS x + UNION + SELECT 2 AS t, 4 AS x) ss +WHERE x > 3 +ORDER BY x; + t | x +---+--- + 2 | 4 +(1 row) + +-- Test cases where the native ordering of a sub-select has more pathkeys +-- than the outer query cares about +explain (costs off) +select distinct q1 from + (select distinct * from int8_tbl i81 + union all + select distinct * from int8_tbl i82) ss +where q2 = q2; + QUERY PLAN +---------------------------------------------------------- + Unique + -> Merge Append + Sort Key: "*SELECT* 1".q1 + -> Subquery Scan on "*SELECT* 1" + -> Unique + -> Sort + Sort Key: i81.q1, i81.q2 + -> Seq Scan on int8_tbl i81 + Filter: (q2 IS NOT NULL) + -> Subquery Scan on "*SELECT* 2" + -> Unique + -> Sort + Sort Key: i82.q1, i82.q2 + -> Seq Scan on int8_tbl i82 + Filter: (q2 IS NOT NULL) +(15 rows) + +select distinct q1 from + (select distinct * from int8_tbl i81 + union all + select distinct * from int8_tbl i82) ss +where q2 = q2; + q1 +------------------ + 123 + 4567890123456789 +(2 rows) + +explain (costs off) +select distinct q1 from + (select distinct * from int8_tbl i81 + union all + select distinct * from int8_tbl i82) ss +where -q1 = q2; + QUERY PLAN +-------------------------------------------------------- + Unique + -> Merge Append + Sort Key: "*SELECT* 1".q1 + -> Subquery Scan on "*SELECT* 1" + -> Unique + -> Sort + Sort Key: i81.q1, i81.q2 + -> Seq Scan on int8_tbl i81 + Filter: ((- q1) = q2) + -> Subquery Scan on "*SELECT* 2" + -> Unique + -> Sort + Sort Key: i82.q1, i82.q2 + -> Seq Scan on int8_tbl i82 + Filter: ((- q1) = q2) +(15 rows) + +select distinct q1 from + (select distinct * from int8_tbl i81 + union all + select distinct * from int8_tbl i82) ss +where -q1 = q2; + q1 +------------------ + 4567890123456789 +(1 row) + +-- Test proper handling of parameterized appendrel paths when the +-- potential join qual is expensive +create function expensivefunc(int) returns int +language plpgsql immutable strict cost 10000 +as $$begin return $1; end$$; +create temp table t3 as select generate_series(-1000,1000) as x; +create index t3i on t3 (expensivefunc(x)); +analyze t3; +explain (costs off) +select * from + (select * from t3 a union all select * from t3 b) ss + join int4_tbl on f1 = expensivefunc(x); + QUERY PLAN +------------------------------------------------------------ + Nested Loop + -> Seq Scan on int4_tbl + -> Append + -> Index Scan using t3i on t3 a + Index Cond: (expensivefunc(x) = int4_tbl.f1) + -> Index Scan using t3i on t3 b + Index Cond: (expensivefunc(x) = int4_tbl.f1) +(7 rows) + +select * from + (select * from t3 a union all select * from t3 b) ss + join int4_tbl on f1 = expensivefunc(x); + x | f1 +---+---- + 0 | 0 + 0 | 0 +(2 rows) + +drop table t3; +drop function expensivefunc(int); +-- Test handling of appendrel quals that const-simplify into an AND +explain (costs off) +select * from + (select *, 0 as x from int8_tbl a + union all + select *, 1 as x from int8_tbl b) ss +where (x = 0) or (q1 >= q2 and q1 <= q2); + QUERY PLAN +--------------------------------------------- + Append + -> Seq Scan on int8_tbl a + -> Seq Scan on int8_tbl b + Filter: ((q1 >= q2) AND (q1 <= q2)) +(4 rows) + +select * from + (select *, 0 as x from int8_tbl a + union all + select *, 1 as x from int8_tbl b) ss +where (x = 0) or (q1 >= q2 and q1 <= q2); + q1 | q2 | x +------------------+-------------------+--- + 123 | 456 | 0 + 123 | 4567890123456789 | 0 + 4567890123456789 | 123 | 0 + 4567890123456789 | 4567890123456789 | 0 + 4567890123456789 | -4567890123456789 | 0 + 4567890123456789 | 4567890123456789 | 1 +(6 rows) + |