Fix expected output diffs from test case union

2 files changed, 62 insertions, 575 deletions

diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index ac6de6d83d..5e5d742cf1 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -420,11 +420,11 @@ LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
                                                              ^
 HINT:  There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
 -- But this should work:
-SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
+SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY q1;
         q1        
 ------------------
- 4567890123456789
               123
+ 4567890123456789
 (2 rows)
 
 --
@@ -474,37 +474,41 @@ INSERT INTO t2 VALUES ('ab'), ('xy');
 set enable_seqscan = off;
 set enable_indexscan = on;
 set enable_bitmapscan = off;
-explain (costs off)
+explain (num_nodes off, nodes off, costs off)
  SELECT * FROM
  (SELECT a || b AS ab FROM t1
   UNION ALL
   SELECT * FROM t2) t
  WHERE ab = 'ab';
-                 QUERY PLAN                  
----------------------------------------------
+                    QUERY PLAN                     
+---------------------------------------------------
  Append
-   ->  Index Scan using t1_ab_idx on t1
-         Index Cond: ((a || b) = 'ab'::text)
-   ->  Index Only Scan using t2_pkey on t2
-         Index Cond: (ab = 'ab'::text)
-(5 rows)
+   ->  Remote Subquery Scan on all
+         ->  Index Scan using t1_ab_idx on t1
+               Index Cond: ((a || b) = 'ab'::text)
+   ->  Remote Subquery Scan on all
+         ->  Index Only Scan using t2_pkey on t2
+               Index Cond: (ab = 'ab'::text)
+(7 rows)
 
-explain (costs off)
+explain (num_nodes off, nodes off, costs off)
  SELECT * FROM
  (SELECT a || b AS ab FROM t1
   UNION
   SELECT * FROM t2) t
  WHERE ab = 'ab';
-                    QUERY PLAN                     
----------------------------------------------------
+                       QUERY PLAN                        
+---------------------------------------------------------
  HashAggregate
    Group Key: ((t1.a || t1.b))
    ->  Append
-         ->  Index Scan using t1_ab_idx on t1
-               Index Cond: ((a || b) = 'ab'::text)
-         ->  Index Only Scan using t2_pkey on t2
-               Index Cond: (ab = 'ab'::text)
-(7 rows)
+         ->  Remote Subquery Scan on all
+               ->  Index Scan using t1_ab_idx on t1
+                     Index Cond: ((a || b) = 'ab'::text)
+         ->  Remote Subquery Scan on all
+               ->  Index Only Scan using t2_pkey on t2
+                     Index Cond: (ab = 'ab'::text)
+(9 rows)
 
 --
 -- Test that ORDER BY for UNION ALL can be pushed down to inheritance
@@ -524,16 +528,20 @@ explain (costs off)
    UNION ALL
    SELECT ab FROM t2) t
   ORDER BY 1 LIMIT 8;
-                   QUERY PLAN                   
-------------------------------------------------
+                           QUERY PLAN                            
+-----------------------------------------------------------------
  Limit
    ->  Merge Append
          Sort Key: ((t1.a || t1.b))
-         ->  Index Scan using t1_ab_idx on t1
-         ->  Index Scan using t1c_ab_idx on t1c
-         ->  Index Scan using t2_pkey on t2
-         ->  Index Scan using t2c_pkey on t2c
-(7 rows)
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t1_ab_idx on t1
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t1c_ab_idx on t1c
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t2_pkey on t2
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t2c_pkey on t2c
+(11 rows)
 
   SELECT * FROM
   (SELECT a || b AS ab FROM t1
@@ -565,31 +573,35 @@ select event_id
        union all
        select event_id from other_events) ss
  order by event_id;
-                        QUERY PLAN                        
-----------------------------------------------------------
+                           QUERY PLAN                           
+----------------------------------------------------------------
  Merge Append
    Sort Key: events.event_id
-   ->  Index Scan using events_pkey on events
-   ->  Sort
-         Sort Key: events_child.event_id
-         ->  Seq Scan on events_child
-   ->  Index Scan using other_events_pkey on other_events
-(7 rows)
+   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+         ->  Index Scan using events_pkey on events
+   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+         ->  Sort
+               Sort Key: events_child.event_id
+               ->  Seq Scan on events_child
+   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+         ->  Index Scan using other_events_pkey on other_events
+(10 rows)
 
 drop table events_child, events, other_events;
 reset enable_indexonlyscan;
 -- Test constraint exclusion of UNION ALL subqueries
-explain (costs off)
+explain (num_nodes off, nodes off, costs off)
  SELECT * FROM
   (SELECT 1 AS t, * FROM tenk1 a
    UNION ALL
    SELECT 2 AS t, * FROM tenk1 b) c
  WHERE t = 2;
-        QUERY PLAN         
----------------------------
- Append
-   ->  Seq Scan on tenk1 b
-(2 rows)
+           QUERY PLAN            
+---------------------------------
+ Remote Subquery Scan on all
+   ->  Append
+         ->  Seq Scan on tenk1 b
+(3 rows)
 
 -- Test that we push quals into UNION sub-selects only when it's safe
 explain (costs off)
@@ -692,16 +704,19 @@ explain (costs off)
 select * from
   (select * from t3 a union all select * from t3 b) ss
   join int4_tbl on f1 = expensivefunc(x);
-                         QUERY PLAN                         
-------------------------------------------------------------
+                            QUERY PLAN                            
+------------------------------------------------------------------
  Nested Loop
-   ->  Seq Scan on int4_tbl
+   ->  Remote Subquery Scan on all (datanode_2)
+         ->  Seq Scan on int4_tbl
    ->  Append
-         ->  Index Scan using t3i on t3 a
-               Index Cond: (expensivefunc(x) = int4_tbl.f1)
-         ->  Index Scan using t3i on t3 b
-               Index Cond: (expensivefunc(x) = int4_tbl.f1)
-(7 rows)
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t3i on t3 a
+                     Index Cond: (expensivefunc(x) = int4_tbl.f1)
+         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+               ->  Index Scan using t3i on t3 b
+                     Index Cond: (expensivefunc(x) = int4_tbl.f1)
+(10 rows)
 
 select * from
   (select * from t3 a union all select * from t3 b) ss
diff --git a/src/test/regress/expected/union_1.out b/src/test/regress/expected/union_1.out
deleted file mode 100644
index 86033c10d3..0000000000
--- a/src/test/regress/expected/union_1.out
+++ /dev/null
@@ -1,528 +0,0 @@
---
--- UNION (also INTERSECT, EXCEPT)
---
--- Simple UNION constructs
-SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
- two 
------
-   1
-   2
-(2 rows)
-
-SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
- one 
------
-   1
-(1 row)
-
-SELECT 1 AS two UNION ALL SELECT 2 ORDER BY 1;
- two 
------
-   1
-   2
-(2 rows)
-
-SELECT 1 AS two UNION ALL SELECT 1 ORDER BY 1;
- two 
------
-   1
-   1
-(2 rows)
-
-SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
- three 
--------
-     1
-     2
-     3
-(3 rows)
-
-SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
- two 
------
-   1
-   2
-(2 rows)
-
-SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
- three 
--------
-     1
-     2
-     2
-(3 rows)
-
-SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
- two 
------
- 1.1
- 2.2
-(2 rows)
-
--- Mixed types
-SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
- two 
------
- 1.1
-   2
-(2 rows)
-
-SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
- two 
------
-   1
- 2.2
-(2 rows)
-
-SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1;
- one 
------
-   1
-(1 row)
-
-SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
- two 
------
- 1.1
-   2
-(2 rows)
-
-SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1;
- two 
------
-   1
-   1
-(2 rows)
-
-SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
- three 
--------
-   1.1
-     2
-     3
-(3 rows)
-
-SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
- two 
------
- 1.1
-   2
-(2 rows)
-
-SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
- three 
--------
-   1.1
-     2
-     2
-(3 rows)
-
-SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
- two 
------
- 1.1
-   2
-(2 rows)
-
---
--- Try testing from tables...
---
-SELECT f1 AS five FROM FLOAT8_TBL
-UNION
-SELECT f1 FROM FLOAT8_TBL
-ORDER BY 1;
-         five          
------------------------
- -1.2345678901234e+200
-               -1004.3
-                -34.84
- -1.2345678901234e-200
-                     0
-(5 rows)
-
-SELECT f1 AS ten FROM FLOAT8_TBL
-UNION ALL
-SELECT f1 FROM FLOAT8_TBL 
-ORDER BY 1;
-          ten          
------------------------
- -1.2345678901234e+200
- -1.2345678901234e+200
-               -1004.3
-               -1004.3
-                -34.84
-                -34.84
- -1.2345678901234e-200
- -1.2345678901234e-200
-                     0
-                     0
-(10 rows)
-
-SELECT f1 AS nine FROM FLOAT8_TBL
-UNION
-SELECT f1 FROM INT4_TBL
-ORDER BY 1;
-         nine          
------------------------
- -1.2345678901234e+200
-           -2147483647
-               -123456
-               -1004.3
-                -34.84
- -1.2345678901234e-200
-                     0
-                123456
-            2147483647
-(9 rows)
-
-SELECT f1 AS ten FROM FLOAT8_TBL
-UNION ALL
-SELECT f1 FROM INT4_TBL 
-ORDER BY 1;
-          ten          
------------------------
- -1.2345678901234e+200
-           -2147483647
-               -123456
-               -1004.3
-                -34.84
- -1.2345678901234e-200
-                     0
-                     0
-                123456
-            2147483647
-(10 rows)
-
-SELECT f1 AS five FROM FLOAT8_TBL
-  WHERE f1 BETWEEN -1e6 AND 1e6
-UNION
-SELECT f1 FROM INT4_TBL
-  WHERE f1 BETWEEN 0 AND 1000000 
-  ORDER BY 1;
-         five          
------------------------
-               -1004.3
-                -34.84
- -1.2345678901234e-200
-                     0
-                123456
-(5 rows)
-
-SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
-UNION
-SELECT f1 FROM CHAR_TBL
-ORDER BY 1;
- three 
--------
- a   
- ab  
- abcd
-(3 rows)
-
-SELECT f1 AS three FROM VARCHAR_TBL
-UNION
-SELECT CAST(f1 AS varchar) FROM CHAR_TBL
-ORDER BY 1;
- three 
--------
- a
- ab
- abcd
-(3 rows)
-
-SELECT f1 AS eight FROM VARCHAR_TBL
-UNION ALL
-SELECT f1 FROM CHAR_TBL 
-ORDER BY 1;
- eight 
--------
- a
- a
- ab
- ab
- abcd
- abcd
- abcd
- abcd
-(8 rows)
-
-SELECT f1 AS five FROM TEXT_TBL
-UNION
-SELECT f1 FROM VARCHAR_TBL
-UNION
-SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
-ORDER BY 1;
-       five        
--------------------
- a
- ab
- abcd
- doh!
- hi de ho neighbor
-(5 rows)
-
---
--- INTERSECT and EXCEPT
---
-SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
-        q2        
-------------------
-              123
- 4567890123456789
-(2 rows)
-
-SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
-        q2        
-------------------
-              123
- 4567890123456789
- 4567890123456789
-(3 rows)
-
-SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
-        q2         
--------------------
- -4567890123456789
-               456
-(2 rows)
-
-SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
-        q2         
--------------------
- -4567890123456789
-               456
-(2 rows)
-
-SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
-        q2         
--------------------
- -4567890123456789
-               456
-  4567890123456789
-(3 rows)
-
-SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
- q1 
-----
-(0 rows)
-
-SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
-        q1        
-------------------
-              123
- 4567890123456789
-(2 rows)
-
-SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
-        q1        
-------------------
-              123
- 4567890123456789
- 4567890123456789
-(3 rows)
-
---
--- Mixed types
---
-SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
- f1 
-----
-  0
-(1 row)
-
-SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
-          f1           
------------------------
- -1.2345678901234e+200
-               -1004.3
-                -34.84
- -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 ORDER BY 1;
-        q1         
--------------------
- -4567890123456789
-               123
-               123
-               456
-  4567890123456789
-  4567890123456789
-  4567890123456789
-(7 rows)
-
-SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1;
-        q1        
-------------------
-              123
- 4567890123456789
-(2 rows)
-
-(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1;
-        q1         
--------------------
- -4567890123456789
-               123
-               123
-               456
-  4567890123456789
-  4567890123456789
-  4567890123456789
-(7 rows)
-
-SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
-        q1         
--------------------
- -4567890123456789
-               456
-(2 rows)
-
-SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1))) ORDER BY 1;
-        q1         
--------------------
- -4567890123456789
-               123
-               123
-               456
-  4567890123456789
-  4567890123456789
-  4567890123456789
-(7 rows)
-
-(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
-        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:  column "q2" does not exist
-LINE 1: ... 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))) ORDER BY q1;
-        q1        
-------------------
-              123
- 4567890123456789
-(2 rows)
-
---
--- New syntaxes (7.1) permit new tests
---
-(((((select * from int8_tbl  ORDER BY q1, q2)))));
-        q1        |        q2         
-------------------+-------------------
-              123 |               456
-              123 |  4567890123456789
- 4567890123456789 | -4567890123456789
- 4567890123456789 |               123
- 4567890123456789 |  4567890123456789
-(5 rows)
-
---
--- Check handling of a case with unknown constants.  We don't guarantee
--- an undecorated constant will work in all cases, but historically this
--- usage has worked, so test we don't break it.
---
-SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
-UNION
-SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
-ORDER BY 1;
-  f1  
-------
- a
- ab
- abcd
- test
-(4 rows)
-
--- This should fail, but it should produce an error cursor
-SELECT '3.4'::numeric UNION SELECT 'foo';
-ERROR:  invalid input syntax for type numeric: "foo"
-LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
-                                           ^
---
--- Test that expression-index constraints can be pushed down through
--- UNION or UNION ALL
---
-CREATE TEMP TABLE t1 (a text, b text);
-CREATE INDEX t1_ab_idx on t1 ((a || b));
-CREATE TEMP TABLE t2 (ab text primary key);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
-INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
-INSERT INTO t2 VALUES ('ab'), ('xy');
-set enable_seqscan = off;
-set enable_indexscan = on;
-set enable_bitmapscan = off;
-explain (num_nodes off, nodes off, costs off)
- SELECT * FROM
- (SELECT a || b AS ab FROM t1
-  UNION ALL
-  SELECT * FROM t2) t
- WHERE ab = 'ab';
-                       QUERY PLAN                        
----------------------------------------------------------
- Result
-   ->  Append
-         ->  Remote Subquery Scan on all
-               ->  Index Scan using t1_ab_idx on t1
-                     Index Cond: ((a || b) = 'ab'::text)
-         ->  Remote Subquery Scan on all
-               ->  Index Only Scan using t2_pkey on t2
-                     Index Cond: (ab = 'ab'::text)
-(8 rows)
-
-explain (num_nodes off, nodes off, costs off)
- SELECT * FROM
- (SELECT a || b AS ab FROM t1
-  UNION
-  SELECT * FROM t2) t
- WHERE ab = 'ab';
-                       QUERY PLAN                        
----------------------------------------------------------
- HashAggregate
-   ->  Append
-         ->  Remote Subquery Scan on all
-               ->  Index Scan using t1_ab_idx on t1
-                     Index Cond: ((a || b) = 'ab'::text)
-         ->  Remote Subquery Scan on all
-               ->  Index Only Scan using t2_pkey on t2
-                     Index Cond: (ab = 'ab'::text)
-(8 rows)
-
-reset enable_seqscan;
-reset enable_indexscan;
-reset enable_bitmapscan;
--- Test constraint exclusion of UNION ALL subqueries
-explain (num_nodes off, nodes off, costs off)
- SELECT * FROM
-  (SELECT 1 AS t, * FROM tenk1 a
-   UNION ALL
-   SELECT 2 AS t, * FROM tenk1 b) c
- WHERE t = 2;
-              QUERY PLAN               
----------------------------------------
- Remote Subquery Scan on all
-   ->  Result
-         ->  Append
-               ->  Seq Scan on tenk1 b
-(4 rows)
-