# Copyright 1994, Regents of the University of California. # Copyright 1996-2022 PostgreSQL Global Development Group. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the regression test suite in PostgreSQL. # The original file was retrieved on February 2, 2022 from: # # https://github.com/postgres/postgres/blob/d33a81203e95d31e62157c4ae0e00e2198841208/src/test/regress/expected/union.out # # The original source code is subject to the terms of the PostgreSQL # license, a copy of which can be found in the LICENSE file at the # root of this repository. # UNION (also INTERSECT, EXCEPT) mode cockroach statement ok CREATE TABLE tenk1 (unique1 int, unique2 int, fivethous int) statement ok INSERT INTO tenk1 SELECT * FROM ROWS FROM (generate_series(0, 4999), generate_series(4999, 0, -1), generate_series(0, 4999)) statement ok INSERT INTO tenk1 SELECT * FROM ROWS FROM (generate_series(5000, 9999), generate_series(9999, 5000, -1), generate_series(0, 4999)) statement ok CREATE TABLE char_tbl (f1 char(4)) statement ok INSERT INTO char_tbl VALUES ('a'), ('ab'), ('abcd'), ('abcd ') statement ok CREATE TABLE varchar_tbl (f1 varchar(4)) statement ok INSERT INTO varchar_tbl VALUES ('a'), ('ab'), ('abcd'), ('abcd ') statement ok CREATE TABLE TEXT_TBL (f1 text) statement ok INSERT INTO TEXT_TBL VALUES ('doh!'), ('hi de ho neighbor') statement ok CREATE TABLE int4_tbl (f1 int4) statement ok INSERT INTO int4_tbl VALUES ('0'), ('123456'), ('-123456'), ('2147483647'), ('-2147483647') statement ok CREATE TABLE int8_tbl (q1 int8, q2 int8) statement ok INSERT INTO int8_tbl VALUES ('123', '456'), ('123', '4567890123456789'), ('4567890123456789', '123'), ('4567890123456789', '4567890123456789'), ('4567890123456789','-4567890123456789') statement ok CREATE TABLE float8_tbl (f1 float8) statement ok INSERT INTO float8_tbl VALUES ('0.0'), ('-34.84'), ('-1004.30'), ('-123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'), ('-0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234') query I colnames SELECT 1 AS two UNION SELECT 2 ORDER BY 1 ---- two 1 2 query I colnames SELECT 1 AS one UNION SELECT 1 ORDER BY 1 ---- one 1 query I colnames SELECT 1 AS two UNION ALL SELECT 2 ---- two 1 2 query I colnames SELECT 1 AS two UNION ALL SELECT 1 ---- two 1 1 query I colnames SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1 ---- three 1 2 3 query I colnames SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1 ---- two 1 2 query I colnames SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1 ---- three 1 2 2 query R colnames SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1 ---- two 1.1 2.2 # Mixed types query R colnames SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1 ---- two 1.1 2 query R colnames SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1 ---- two 1 2.2 query R colnames SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1 ---- one 1 query R colnames SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1 ---- two 1.1 2 query R colnames SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1 ---- two 1 1 query R colnames SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1 ---- three 1.1 2 3 query R colnames SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1 ---- two 1.1 2 query R colnames SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1 ---- three 1.1 2 2 query R colnames SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1 ---- two 1.1 2 # Try testing from tables... query R colnames SELECT f1 AS five FROM FLOAT8_TBL UNION SELECT f1 FROM FLOAT8_TBL ORDER BY 1 ---- five -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -1004.3 -34.84 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 0 query R colnames SELECT f1 AS ten FROM FLOAT8_TBL UNION ALL SELECT f1 FROM FLOAT8_TBL ORDER BY 1 ---- ten -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -1004.3 -1004.3 -34.84 -34.84 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 0 0 query R colnames SELECT f1 AS nine FROM FLOAT8_TBL UNION SELECT f1 FROM INT4_TBL ORDER BY 1 ---- nine -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -2147483647 -123456 -1004.3 -34.84 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 0 123456 2147483647 query R colnames SELECT f1 AS ten FROM FLOAT8_TBL UNION ALL SELECT f1 FROM INT4_TBL ORDER BY 1 ---- ten -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -2147483647 -123456 -1004.3 -34.84 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 0 0 123456 2147483647 query R colnames 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 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 0 123456 query T colnames SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL ORDER BY 1 ---- three a␠␠␠ ab␠␠ abcd query T colnames SELECT f1 AS three FROM VARCHAR_TBL UNION SELECT CAST(f1 AS varchar) FROM CHAR_TBL ORDER BY 1 ---- three a ab abcd query T colnames SELECT f1 AS eight FROM VARCHAR_TBL UNION ALL SELECT f1 FROM CHAR_TBL ORDER BY 1 ---- eight a a ab ab abcd abcd abcd abcd query T colnames 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 # INTERSECT and EXCEPT query T colnames SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1 ---- q2 123 4567890123456789 query T colnames SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1 ---- q2 123 4567890123456789 4567890123456789 query T colnames SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1 ---- q2 -4567890123456789 456 query T colnames SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1 ---- q2 -4567890123456789 456 query T colnames SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1 ---- q2 -4567890123456789 456 4567890123456789 query T colnames SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1 ---- q1 query T colnames SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1 ---- q1 123 4567890123456789 query T colnames SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1 ---- q1 123 4567890123456789 4567890123456789 # nested cases query TTT colnames (SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6 ---- ?column? ?column? ?column? 4 5 6 query TTT colnames (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6 ---- ?column? ?column? ?column? 4 5 6 query TTT colnames (SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6 ---- ?column? ?column? ?column? 1 2 3 query TTT colnames (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6 ---- ?column? ?column? ?column? 1 2 3 query I colnames select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss ---- count 10000 query I colnames select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss ---- count 5000 query I colnames select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10 ---- unique1 10 query I colnames select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss ---- count 10000 query I colnames select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss ---- count 5000 query I colnames select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10 ---- unique1 10 query T colnames,rowsort 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} query T colnames 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} query T colnames 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} query T colnames 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} query T colnames 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} query T colnames 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} query T colnames 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) query T colnames 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) query T colnames 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) query T colnames 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) query T colnames 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) query T colnames 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) # Mixed types query R colnames SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1 ---- f1 0 query R colnames,rowsort SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1 ---- f1 -123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -1004.3 -34.84 -0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234 # Operator precedence and (((((extra))))) parentheses query I colnames,rowsort 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 query I colnames,rowsort SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1 ---- q1 123 4567890123456789 query I colnames,rowsort (((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 query I colnames SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1 ---- q1 -4567890123456789 456 query I colnames,rowsort SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1))) ---- q1 123 123 456 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 query I colnames (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1 ---- q1 -4567890123456789 456 # Subqueries with ORDER BY & LIMIT clauses # # In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT query II colnames SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl ORDER BY q2,q1 ---- q1 q2 4567890123456789 -4567890123456789 123 456 # This should fail, because q2 isn't a name of an EXCEPT output column query error column "q2" does not exist SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1 # But this should work: query T colnames SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1 ---- q1 123 4567890123456789 # New syntaxes (7.1) permit new tests query II colnames,rowsort (((((select * from int8_tbl))))) ---- q1 q2 123 456 123 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 # Check behavior with empty select list (allowed since 9.4) query select union select ---- query select intersect select ---- query select except select ---- query select from generate_series(1,5) union select from generate_series(1,3) ---- query select from generate_series(1,5) union all select from generate_series(1,3) ---- query select from generate_series(1,5) intersect select from generate_series(1,3) ---- query select from generate_series(1,5) intersect all select from generate_series(1,3) ---- query select from generate_series(1,5) except select from generate_series(1,3) ---- query select from generate_series(1,5) except all select from generate_series(1,3) ---- query select from generate_series(1,5) union select from generate_series(1,3) ---- query select from generate_series(1,5) union all select from generate_series(1,3) ---- query select from generate_series(1,5) intersect select from generate_series(1,3) ---- query select from generate_series(1,5) intersect all select from generate_series(1,3) ---- query select from generate_series(1,5) except select from generate_series(1,3) ---- query select from generate_series(1,5) except all select from generate_series(1,3) ---- # 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. query T colnames 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 # TODO(benesch): this should have the following error instead. # query error invalid input syntax for type numeric: "foo" query error UNION types numeric and text cannot be matched SELECT '3.4'::numeric UNION SELECT 'foo' query TT colnames 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 query TT colnames 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 query I colnames select distinct q1 from (select distinct * from int8_tbl i81 union all select distinct * from int8_tbl i82) ss where q2 = q2 order by 1 ---- q1 123 4567890123456789 query I colnames select distinct q1 from (select distinct * from int8_tbl i81 union all select distinct * from int8_tbl i82) ss where -q1 = q2 ---- q1 4567890123456789 query III colnames 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) order by 1, 2, 3 ---- q1 q2 x 123 456 0 123 4567890123456789 0 4567890123456789 -4567890123456789 0 4567890123456789 123 0 4567890123456789 4567890123456789 0 4567890123456789 4567890123456789 1