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!

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;