Teach the system how to use hashing for UNION. (INTERSECT/EXCEPT will follow,

but seem like a separate patch since most of the remaining work is on the
executor side.)  I took the opportunity to push selection of the grouping
operators for set operations into the parser where it belongs.  Otherwise this
is just a small exercise in making prepunion.c consider both alternatives.

As with the recent DISTINCT patch, this means we can UNION on datatypes that
can hash but not sort, and it means that UNION without ORDER BY is no longer
certain to produce sorted output.

4 files changed, 26 insertions, 16 deletions

diff --git a/src/test/regress/expected/copyselect.out b/src/test/regress/expected/copyselect.out
index c42bad143e4..8a42b0e3d80 100644
--- a/src/test/regress/expected/copyselect.out
+++ b/src/test/regress/expected/copyselect.out
@@ -73,7 +73,7 @@ copy (select * from test1 join test2 using (id)) to stdout;
 --
 -- Test UNION SELECT
 --
-copy (select t from test1 where id = 1 UNION select * from v_test1) to stdout;
+copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout;
 a
 v_a
 v_b
@@ -83,7 +83,7 @@ v_e
 --
 -- Test subselect
 --
-copy (select * from (select t from test1 where id = 1 UNION select * from v_test1) t1) to stdout;
+copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout;
 a
 v_a
 v_b
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index abcbc9503a8..722f9651be1 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -102,7 +102,7 @@ SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
      3
 (3 rows)
 
-SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8;
+SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
  two 
 -----
  1.1
@@ -129,7 +129,8 @@ SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
 --
 SELECT f1 AS five FROM FLOAT8_TBL
 UNION
-SELECT f1 FROM FLOAT8_TBL;
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
          five          
 -----------------------
  -1.2345678901234e+200
@@ -158,7 +159,8 @@ SELECT f1 FROM FLOAT8_TBL;
 
 SELECT f1 AS nine FROM FLOAT8_TBL
 UNION
-SELECT f1 FROM INT4_TBL;
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
          nine          
 -----------------------
  -1.2345678901234e+200
@@ -205,7 +207,8 @@ SELECT f1 FROM INT4_TBL
 
 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
 UNION
-SELECT f1 FROM CHAR_TBL;
+SELECT f1 FROM CHAR_TBL
+ORDER BY 1;
  three 
 -------
  a   
@@ -215,7 +218,8 @@ SELECT f1 FROM CHAR_TBL;
 
 SELECT f1 AS three FROM VARCHAR_TBL
 UNION
-SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
+SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+ORDER BY 1;
  three 
 -------
  a
@@ -242,7 +246,8 @@ SELECT f1 AS five FROM TEXT_TBL
 UNION
 SELECT f1 FROM VARCHAR_TBL
 UNION
-SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
+SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+ORDER BY 1;
        five        
 -------------------
  a
diff --git a/src/test/regress/sql/copyselect.sql b/src/test/regress/sql/copyselect.sql
index c2526487c8c..beca507ae71 100644
--- a/src/test/regress/sql/copyselect.sql
+++ b/src/test/regress/sql/copyselect.sql
@@ -53,11 +53,11 @@ copy (select * from test1 join test2 using (id)) to stdout;
 --
 -- Test UNION SELECT
 --
-copy (select t from test1 where id = 1 UNION select * from v_test1) to stdout;
+copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout;
 --
 -- Test subselect
 --
-copy (select * from (select t from test1 where id = 1 UNION select * from v_test1) t1) to stdout;
+copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout;
 --
 -- Test headers, CSV and quotes
 --
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 0f846091cd1..0b83d6b185b 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -34,7 +34,7 @@ SELECT 1.0::float8 AS two UNION ALL SELECT 1;
 
 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
 
-SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8;
+SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
 
 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2;
 
@@ -46,7 +46,8 @@ SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
 
 SELECT f1 AS five FROM FLOAT8_TBL
 UNION
-SELECT f1 FROM FLOAT8_TBL;
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
 
 SELECT f1 AS ten FROM FLOAT8_TBL
 UNION ALL
@@ -54,7 +55,8 @@ SELECT f1 FROM FLOAT8_TBL;
 
 SELECT f1 AS nine FROM FLOAT8_TBL
 UNION
-SELECT f1 FROM INT4_TBL;
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
 
 SELECT f1 AS ten FROM FLOAT8_TBL
 UNION ALL
@@ -68,11 +70,13 @@ SELECT f1 FROM INT4_TBL
 
 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
 UNION
-SELECT f1 FROM CHAR_TBL;
+SELECT f1 FROM CHAR_TBL
+ORDER BY 1;
 
 SELECT f1 AS three FROM VARCHAR_TBL
 UNION
-SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
+SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+ORDER BY 1;
 
 SELECT f1 AS eight FROM VARCHAR_TBL
 UNION ALL
@@ -82,7 +86,8 @@ SELECT f1 AS five FROM TEXT_TBL
 UNION
 SELECT f1 FROM VARCHAR_TBL
 UNION
-SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
+SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+ORDER BY 1;
 
 --
 -- INTERSECT and EXCEPT