From 9bbca2c0f09868bd627293b7a1dfc266315b8277 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 9 Nov 2000 02:47:49 +0000
Subject: [PATCH] Add some more union/intersect/except test cases, per
 suggestions from Kevin O'Gorman.

---
 src/test/regress/expected/union.out | 116 ++++++++++++++++++++++++++++
 src/test/regress/sql/union.sql      |  44 +++++++++++
 2 files changed, 160 insertions(+)

diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 72d6b77fa1..fb24220156 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -294,6 +294,26 @@ SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
   4567890123456789
 (3 rows)
 
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
+ q1 
+----
+(0 rows)
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
+        q1        
+------------------
+              123
+ 4567890123456789
+(2 rows)
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
+        q1        
+------------------
+              123
+ 4567890123456789
+ 4567890123456789
+(3 rows)
+
 --
 -- Mixed types
 --
@@ -312,3 +332,99 @@ SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
  -1.2345678901234e-200
 (4 rows)
 
+--
+-- Operator precedence and (((((extra))))) parentheses
+--
+SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
+        q1         
+-------------------
+               123
+  4567890123456789
+               456
+  4567890123456789
+               123
+  4567890123456789
+ -4567890123456789
+(7 rows)
+
+SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
+        q1        
+------------------
+              123
+ 4567890123456789
+(2 rows)
+
+(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
+        q1         
+-------------------
+               123
+  4567890123456789
+               456
+  4567890123456789
+               123
+  4567890123456789
+ -4567890123456789
+(7 rows)
+
+SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
+        q1         
+-------------------
+ -4567890123456789
+               456
+(2 rows)
+
+SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
+        q1         
+-------------------
+               123
+               123
+  4567890123456789
+  4567890123456789
+  4567890123456789
+ -4567890123456789
+               456
+(7 rows)
+
+(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
+        q1         
+-------------------
+ -4567890123456789
+               456
+(2 rows)
+
+--
+-- Subqueries with ORDER BY & LIMIT clauses
+--
+-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
+ORDER BY q2,q1;
+        q1        |        q2         
+------------------+-------------------
+ 4567890123456789 | -4567890123456789
+              123 |               456
+(2 rows)
+
+-- This should fail, because q2 isn't a name of an EXCEPT output column
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
+ERROR:  Attribute 'q2' not found
+-- But this should work:
+SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
+        q1        
+------------------
+              123
+ 4567890123456789
+(2 rows)
+
+--
+-- New syntaxes (7.1) permit new tests
+--
+(((((select * from int8_tbl)))));
+        q1        |        q2         
+------------------+-------------------
+              123 |               456
+              123 |  4567890123456789
+ 4567890123456789 |               123
+ 4567890123456789 |  4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index d232c00a23..747d281948 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -98,6 +98,12 @@ 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;
 
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
+
 --
 -- Mixed types
 --
@@ -105,3 +111,41 @@ SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
 
 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
+
+--
+-- Operator precedence and (((((extra))))) parentheses
+--
+
+SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
+
+SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
+
+(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
+
+SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
+
+SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
+
+(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
+
+--
+-- Subqueries with ORDER BY & LIMIT clauses
+--
+
+-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
+ORDER BY q2,q1;
+
+-- This should fail, because q2 isn't a name of an EXCEPT output column
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
+
+-- But this should work:
+SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
+
+--
+-- New syntaxes (7.1) permit new tests
+--
+
+(((((select * from int8_tbl)))));
+
+
-- 
2.40.0