From 05e3d0ee8666b74f11ffad16f46e372459d6e53e Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Thu, 5 Oct 2000 19:11:39 +0000
Subject: Reimplementation of UNION/INTERSECT/EXCEPT.  INTERSECT/EXCEPT now
 meet the SQL92 semantics, including support for ALL option.  All three can be
 used in subqueries and views.  DISTINCT and ORDER BY work now in views, too.
 This rewrite fixes many problems with cross-datatype UNIONs and INSERT/SELECT
 where the SELECT yields different datatypes than the INSERT needs.  I did
 that by making UNION subqueries and SELECT in INSERT be treated like
 subselects-in-FROM, thereby allowing an extra level of targetlist where the
 datatype conversions can be inserted safely. INITDB NEEDED!

---
 src/test/regress/expected/union.out | 85 ++++++++++++++++++++++++++++++++-----
 src/test/regress/sql/union.sql      | 35 ++++++++++++---
 2 files changed, 104 insertions(+), 16 deletions(-)

(limited to 'src/test')

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;
-- 
cgit v1.2.3