diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/union.out | 85 | ||||
| -rw-r--r-- | src/test/regress/sql/union.sql | 35 |
2 files changed, 104 insertions, 16 deletions
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index bc342f5c435..72d6b77fa18 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1,5 +1,5 @@ -- --- UNION +-- UNION (also INTERSECT, EXCEPT) -- -- Simple UNION constructs SELECT 1 AS two UNION SELECT 2; @@ -71,10 +71,10 @@ SELECT 1 AS two UNION SELECT 2.2; two ----- 1 - 2 + 2.2 (2 rows) -SELECT 1 AS one UNION SELECT 1.1; +SELECT 1 AS one UNION SELECT 1.0; one ----- 1 @@ -87,36 +87,43 @@ SELECT 1.1 AS two UNION ALL SELECT 2; 2 (2 rows) -SELECT 1 AS two UNION ALL SELECT 1; +SELECT 1.0 AS two UNION ALL SELECT 1; two ----- 1 1 (2 rows) -SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; +SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3; three ------- - 1 + 1.1 2 3 (3 rows) -SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; +SELECT 1.1 AS two UNION SELECT 2 UNION SELECT 2.0; two ----- - 1 + 1.1 2 (2 rows) -SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; +SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2; three ------- - 1 + 1.1 2 2 (3 rows) +SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2); + two +----- + 1.1 + 2 +(2 rows) + -- -- Try testing from tables... -- @@ -247,3 +254,61 @@ SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; hi de ho neighbor (5 rows) +-- +-- INTERSECT and EXCEPT +-- +SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; + q2 +------------------ + 123 + 4567890123456789 +(2 rows) + +SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; + q2 +------------------ + 123 + 4567890123456789 + 4567890123456789 +(3 rows) + +SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl; + q2 +------------------- + -4567890123456789 + 456 +(2 rows) + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl; + q2 +------------------- + -4567890123456789 + 456 +(2 rows) + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl; + q2 +------------------- + -4567890123456789 + 456 + 4567890123456789 +(3 rows) + +-- +-- Mixed types +-- +SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl; + f1 +---- + 0 +(1 row) + +SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl; + f1 +----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 +(4 rows) + diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 22ea190f2ee..d232c00a237 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -1,5 +1,5 @@ -- --- UNION +-- UNION (also INTERSECT, EXCEPT) -- -- Simple UNION constructs @@ -26,17 +26,19 @@ SELECT 1.1 AS two UNION SELECT 2; SELECT 1 AS two UNION SELECT 2.2; -SELECT 1 AS one UNION SELECT 1.1; +SELECT 1 AS one UNION SELECT 1.0; SELECT 1.1 AS two UNION ALL SELECT 2; -SELECT 1 AS two UNION ALL SELECT 1; +SELECT 1.0 AS two UNION ALL SELECT 1; -SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; +SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3; -SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; +SELECT 1.1 AS two UNION SELECT 2 UNION SELECT 2.0; -SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; +SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2; + +SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2); -- -- Try testing from tables... @@ -82,3 +84,24 @@ SELECT f1 FROM VARCHAR_TBL UNION SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; +-- +-- INTERSECT and EXCEPT +-- + +SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; + +SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; + +SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl; + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl; + +SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl; + +-- +-- Mixed types +-- + +SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl; + +SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl; |