| # name: test/sql/set/basic_union.test |
| # description: UNION DISTINCT table operator |
| # feature: E071-01 |
| # [group] Set operations |
| |
| 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 |