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.

1 files changed, 11 insertions, 6 deletions

diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 0f846091cd..0b83d6b185 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