modules/sql-engine/src/integrationTest/sql/group1/set/test_union.test

# name: test/sql/set/test_union.test
# description: Basic union tests
# feature: E071-01
# group: [set]

statement ok
CREATE TABLE a(i INTEGER);

statement ok
INSERT INTO a(i) VALUES (100), (NULL);

query I
SELECT 123 UNION DISTINCT SELECT 123 UNION ALL SELECT 123;
----
123
123

query I
SELECT 123 UNION ALL SELECT 123 UNION ALL SELECT 123;
----
123
123
123

query I
SELECT x FROM table(system_range(123, 123)) UNION DISTINCT SELECT x FROM table(system_range(123, 123)) UNION ALL SELECT 123;
----
123
123

query I
SELECT x FROM table(system_range(123, 123)) UNION ALL SELECT x FROM table(system_range(123, 123)) UNION ALL SELECT 123;
----
123
123
123

query I
SELECT NULL UNION DISTINCT SELECT NULL
----
NULL

query I
SELECT NULL UNION ALL SELECT NULL
----
NULL
NULL

query I
SELECT 'a' UNION DISTINCT SELECT NULL
----
a
NULL

query II
SELECT NULL, NULL UNION DISTINCT SELECT NULL, NULL FROM a
----
NULL	NULL

query II
SELECT NULL, NULL UNION ALL SELECT NULL, NULL FROM a
----
NULL	NULL
NULL	NULL
NULL	NULL

query II rowsort
SELECT 1, 2 UNION DISTINCT SELECT i, i FROM a
----
NULL	NULL
100	100
1	2

query II rowsort
SELECT 1, 2 UNION ALL SELECT i, i FROM a
----
NULL	NULL
100	100
1	2

#Mixed types

query I
SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
----
1.1
2

query I
SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
----
1
2.2

query I
SELECT 1 AS one UNION SELECT 1.0::FLOAT ORDER BY 1;
----
1

query I
SELECT 1.1::FLOAT AS two UNION SELECT 2 UNION SELECT 2.0::FLOAT ORDER BY 1;
----
1.1
2

query I
SELECT 1.1::FLOAT AS two UNION SELECT 2 UNION ALL SELECT 2.0::FLOAT ORDER BY 1;
----
1.1
2
2