Some more regression fixes.

3 files changed, 91 insertions, 1133 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6ab9544e28..b8ea9e552f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2320,40 +2320,6 @@ select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol
 reset enable_hashjoin;
 reset enable_nestloop;
 --
--- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
---
-set work_mem to '64kB';
-set enable_mergejoin to off;
-explain (costs off)
-select count(*) from tenk1 a, tenk1 b
-  where a.hundred = b.thousand and (b.fivethous % 10) < 10;
-                                    QUERY PLAN                                     
------------------------------------------------------------------------------------
- Aggregate
-   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-         ->  Aggregate
-               ->  Hash Join
-                     Hash Cond: (a.hundred = b.thousand)
-                     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                           Distribute results by H: hundred
-                           ->  Index Only Scan using tenk1_hundred on tenk1 a
-                     ->  Hash
-                           ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                                 Distribute results by H: thousand
-                                 ->  Seq Scan on tenk1 b
-                                       Filter: ((fivethous % 10) < 10)
-(13 rows)
-
-select count(*) from tenk1 a, tenk1 b
-  where a.hundred = b.thousand and (b.fivethous % 10) < 10;
- count  
---------
- 100000
-(1 row)
-
-reset work_mem;
-reset enable_mergejoin;
---
 -- regression test for 8.2 bug with improper re-ordering of left joins
 --
 create temp table tt3(f1 int, f2 text);
@@ -3488,486 +3454,6 @@ using (join_key);
 (2 rows)
 
 --
--- test successful handling of nested outer joins with degenerate join quals
---
-explain (verbose, costs off)
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   Output: t1.f1
-   ->  Hash Left Join
-         Output: t1.f1
-         Hash Cond: (i8.q2 = i4.f1)
-         ->  Nested Loop Left Join
-               Output: t1.f1, i8.q2
-               Join Filter: (t1.f1 = '***'::text)
-               ->  Seq Scan on public.text_tbl t1
-                     Output: t1.f1
-               ->  Materialize
-                     Output: i8.q2
-                     ->  Hash Right Join
-                           Output: i8.q2
-                           Hash Cond: ((NULL::integer) = i8b1.q2)
-                           ->  Hash Left Join
-                                 Output: i8.q2, (NULL::integer)
-                                 Hash Cond: (i8.q1 = i8b2.q1)
-                                 ->  Seq Scan on public.int8_tbl i8
-                                       Output: i8.q1, i8.q2
-                                 ->  Hash
-                                       Output: i8b2.q1, (NULL::integer)
-                                       ->  Seq Scan on public.int8_tbl i8b2
-                                             Output: i8b2.q1, NULL::integer
-                           ->  Hash
-                                 Output: i8b1.q2
-                                 ->  Seq Scan on public.int8_tbl i8b1
-                                       Output: i8b1.q2
-         ->  Hash
-               Output: i4.f1
-               ->  Seq Scan on public.int4_tbl i4
-                     Output: i4.f1
-(32 rows)
-
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-        f1         
--------------------
- hi de ho neighbor
- doh!
-(2 rows)
-
-explain (verbose, costs off)
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   Output: t1.f1
-   ->  Hash Left Join
-         Output: t1.f1
-         Hash Cond: (i8.q2 = i4.f1)
-         ->  Nested Loop Left Join
-               Output: t1.f1, i8.q2
-               Join Filter: (t1.f1 = '***'::text)
-               ->  Seq Scan on public.text_tbl t1
-                     Output: t1.f1
-               ->  Materialize
-                     Output: i8.q2
-                     ->  Hash Right Join
-                           Output: i8.q2
-                           Hash Cond: ((NULL::integer) = i8b1.q2)
-                           ->  Hash Right Join
-                                 Output: i8.q2, (NULL::integer)
-                                 Hash Cond: (i8b2.q1 = i8.q1)
-                                 ->  Nested Loop
-                                       Output: i8b2.q1, NULL::integer
-                                       ->  Seq Scan on public.int8_tbl i8b2
-                                             Output: i8b2.q1, i8b2.q2
-                                       ->  Materialize
-                                             ->  Seq Scan on public.int4_tbl i4b2
-                                 ->  Hash
-                                       Output: i8.q1, i8.q2
-                                       ->  Seq Scan on public.int8_tbl i8
-                                             Output: i8.q1, i8.q2
-                           ->  Hash
-                                 Output: i8b1.q2
-                                 ->  Seq Scan on public.int8_tbl i8b1
-                                       Output: i8b1.q2
-         ->  Hash
-               Output: i4.f1
-               ->  Seq Scan on public.int4_tbl i4
-                     Output: i4.f1
-(36 rows)
-
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-        f1         
--------------------
- hi de ho neighbor
- doh!
-(2 rows)
-
-explain (verbose, costs off)
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2
-                 where q1 = f1) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   Output: t1.f1
-   ->  Hash Left Join
-         Output: t1.f1
-         Hash Cond: (i8.q2 = i4.f1)
-         ->  Nested Loop Left Join
-               Output: t1.f1, i8.q2
-               Join Filter: (t1.f1 = '***'::text)
-               ->  Seq Scan on public.text_tbl t1
-                     Output: t1.f1
-               ->  Materialize
-                     Output: i8.q2
-                     ->  Hash Right Join
-                           Output: i8.q2
-                           Hash Cond: ((NULL::integer) = i8b1.q2)
-                           ->  Hash Right Join
-                                 Output: i8.q2, (NULL::integer)
-                                 Hash Cond: (i8b2.q1 = i8.q1)
-                                 ->  Hash Join
-                                       Output: i8b2.q1, NULL::integer
-                                       Hash Cond: (i8b2.q1 = i4b2.f1)
-                                       ->  Seq Scan on public.int8_tbl i8b2
-                                             Output: i8b2.q1, i8b2.q2
-                                       ->  Hash
-                                             Output: i4b2.f1
-                                             ->  Seq Scan on public.int4_tbl i4b2
-                                                   Output: i4b2.f1
-                                 ->  Hash
-                                       Output: i8.q1, i8.q2
-                                       ->  Seq Scan on public.int8_tbl i8
-                                             Output: i8.q1, i8.q2
-                           ->  Hash
-                                 Output: i8b1.q2
-                                 ->  Seq Scan on public.int8_tbl i8b1
-                                       Output: i8b1.q2
-         ->  Hash
-               Output: i4.f1
-               ->  Seq Scan on public.int4_tbl i4
-                     Output: i4.f1
-(39 rows)
-
-select t1.* from
-  text_tbl t1
-  left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
-    left join int8_tbl i8
-      left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2
-                 where q1 = f1) b2
-      on (i8.q1 = b2.q1)
-    on (b2.d2 = b1.q2)
-  on (t1.f1 = b1.d1)
-  left join int4_tbl i4
-  on (i8.q2 = i4.f1);
-        f1         
--------------------
- hi de ho neighbor
- doh!
-(2 rows)
-
-explain (verbose, costs off)
-select * from
-  text_tbl t1
-  inner join int8_tbl i8
-  on i8.q2 = 456
-  right join text_tbl t2
-  on t1.f1 = 'doh!'
-  left join int4_tbl i4
-  on i8.q1 = i4.f1;
-                          QUERY PLAN                          
---------------------------------------------------------------
- Nested Loop Left Join
-   Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
-   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-         Output: t2.f1
-         ->  Seq Scan on public.text_tbl t2
-               Output: t2.f1
-   ->  Materialize
-         Output: i8.q1, i8.q2, i4.f1, t1.f1
-         ->  Remote Subquery Scan on all (datanode_2)
-               Output: i8.q1, i8.q2, i4.f1, t1.f1
-               ->  Nested Loop
-                     Output: i8.q1, i8.q2, i4.f1, t1.f1
-                     ->  Nested Loop Left Join
-                           Output: i8.q1, i8.q2, i4.f1
-                           Join Filter: (i8.q1 = i4.f1)
-                           ->  Seq Scan on public.int8_tbl i8
-                                 Output: i8.q1, i8.q2
-                                 Filter: (i8.q2 = 456)
-                           ->  Seq Scan on public.int4_tbl i4
-                                 Output: i4.f1
-                     ->  Seq Scan on public.text_tbl t1
-                           Output: t1.f1
-                           Filter: (t1.f1 = 'doh!'::text)
-(23 rows)
-
-select * from
-  text_tbl t1
-  inner join int8_tbl i8
-  on i8.q2 = 456
-  right join text_tbl t2
-  on t1.f1 = 'doh!'
-  left join int4_tbl i4
-  on i8.q1 = i4.f1;
-  f1  | q1  | q2  |        f1         | f1 
-------+-----+-----+-------------------+----
- doh! | 123 | 456 | hi de ho neighbor |   
- doh! | 123 | 456 | doh!              |   
-(2 rows)
-
---
--- test for appropriate join order in the presence of lateral references
---
-explain (verbose, costs off)
-select * from
-  text_tbl t1
-  left join int8_tbl i8
-  on i8.q2 = 123,
-  lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
-where t1.f1 = ss.f1;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
-   Join Filter: (t1.f1 = t2.f1)
-   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-         Output: t1.f1, i8.q1, i8.q2
-         ->  Nested Loop Left Join
-               Output: t1.f1, i8.q1, i8.q2
-               ->  Seq Scan on public.text_tbl t1
-                     Output: t1.f1
-               ->  Materialize
-                     Output: i8.q1, i8.q2
-                     ->  Seq Scan on public.int8_tbl i8
-                           Output: i8.q1, i8.q2
-                           Filter: (i8.q2 = 123)
-   ->  Limit
-         Output: (i8.q1), t2.f1
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               Output: i8.q1, t2.f1
-               ->  Limit
-                     Output: (i8.q1), t2.f1
-                     ->  Seq Scan on public.text_tbl t2
-                           Output: i8.q1, t2.f1
-(22 rows)
-
-select * from
-  text_tbl t1
-  left join int8_tbl i8
-  on i8.q2 = 123,
-  lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
-where t1.f1 = ss.f1;
-        f1         |        q1        | q2  |        q1        |        f1         
--------------------+------------------+-----+------------------+-------------------
- hi de ho neighbor | 4567890123456789 | 123 | 4567890123456789 | hi de ho neighbor
-(1 row)
-
-explain (verbose, costs off)
-select * from
-  text_tbl t1
-  left join int8_tbl i8
-  on i8.q2 = 123,
-  lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
-  lateral (select ss1.* from text_tbl t3 limit 1) as ss2
-where t1.f1 = ss2.f1;
-                              QUERY PLAN                               
------------------------------------------------------------------------
- Nested Loop
-   Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
-   Join Filter: (t1.f1 = (t2.f1))
-   ->  Nested Loop
-         Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               Output: t1.f1, i8.q1, i8.q2
-               ->  Nested Loop Left Join
-                     Output: t1.f1, i8.q1, i8.q2
-                     ->  Seq Scan on public.text_tbl t1
-                           Output: t1.f1
-                     ->  Materialize
-                           Output: i8.q1, i8.q2
-                           ->  Seq Scan on public.int8_tbl i8
-                                 Output: i8.q1, i8.q2
-                                 Filter: (i8.q2 = 123)
-         ->  Limit
-               Output: (i8.q1), t2.f1
-               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                     Output: i8.q1, t2.f1
-                     ->  Limit
-                           Output: (i8.q1), t2.f1
-                           ->  Seq Scan on public.text_tbl t2
-                                 Output: i8.q1, t2.f1
-   ->  Limit
-         Output: ((i8.q1)), (t2.f1)
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               Output: (i8.q1), t2.f1
-               ->  Limit
-                     Output: ((i8.q1)), (t2.f1)
-                     ->  Seq Scan on public.text_tbl t3
-                           Output: (i8.q1), t2.f1
-(32 rows)
-
-select * from
-  text_tbl t1
-  left join int8_tbl i8
-  on i8.q2 = 123,
-  lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
-  lateral (select ss1.* from text_tbl t3 limit 1) as ss2
-where t1.f1 = ss2.f1;
-        f1         |        q1        | q2  |        q1        |        f1         |        q1        |        f1         
--------------------+------------------+-----+------------------+-------------------+------------------+-------------------
- hi de ho neighbor | 4567890123456789 | 123 | 4567890123456789 | hi de ho neighbor | 4567890123456789 | hi de ho neighbor
-(1 row)
-
-explain (verbose, costs off)
-select 1 from
-  text_tbl as tt1
-  inner join text_tbl as tt2 on (tt1.f1 = 'foo')
-  left join text_tbl as tt3 on (tt3.f1 = 'foo')
-  left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
-  lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
-where tt1.f1 = ss1.c0;
-                                 QUERY PLAN                                  
------------------------------------------------------------------------------
- Nested Loop
-   Output: 1
-   ->  Nested Loop Left Join
-         Output: tt1.f1, tt4.f1
-         ->  Nested Loop
-               Output: tt1.f1
-               ->  Remote Subquery Scan on all (datanode_2)
-                     Output: tt1.f1
-                     ->  Seq Scan on public.text_tbl tt1
-                           Output: tt1.f1
-                           Filter: (tt1.f1 = 'foo'::text)
-               ->  Materialize
-                     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                           ->  Seq Scan on public.text_tbl tt2
-         ->  Materialize
-               Output: tt4.f1
-               ->  Remote Subquery Scan on all (datanode_2)
-                     Output: tt4.f1
-                     ->  Nested Loop Left Join
-                           Output: tt4.f1
-                           Join Filter: (tt3.f1 = tt4.f1)
-                           ->  Seq Scan on public.text_tbl tt3
-                                 Output: tt3.f1
-                                 Filter: (tt3.f1 = 'foo'::text)
-                           ->  Seq Scan on public.text_tbl tt4
-                                 Output: tt4.f1
-                                 Filter: (tt4.f1 = 'foo'::text)
-   ->  Subquery Scan on ss1
-         Output: ss1.c0
-         Filter: (ss1.c0 = 'foo'::text)
-         ->  Limit
-               Output: (tt4.f1)
-               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                     Output: tt4.f1
-                     ->  Limit
-                           Output: (tt4.f1)
-                           ->  Seq Scan on public.text_tbl tt5
-                                 Output: tt4.f1
-(38 rows)
-
-select 1 from
-  text_tbl as tt1
-  inner join text_tbl as tt2 on (tt1.f1 = 'foo')
-  left join text_tbl as tt3 on (tt3.f1 = 'foo')
-  left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
-  lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
-where tt1.f1 = ss1.c0;
- ?column? 
-----------
-(0 rows)
-
---
--- check a case in which a PlaceHolderVar forces join order
---
-explain (verbose, costs off)
-select ss2.* from
-  int4_tbl i41
-  left join int8_tbl i8
-    join (select i42.f1 as c1, i43.f1 as c2, 42 as c3
-          from int4_tbl i42, int4_tbl i43) ss1
-    on i8.q1 = ss1.c2
-  on i41.f1 = ss1.c1,
-  lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
-where ss1.c2 = 0;
-                                     QUERY PLAN                                     
-------------------------------------------------------------------------------------
- Nested Loop
-   Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
-   ->  Remote Subquery Scan on all (datanode_1)
-         Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
-         ->  Hash Join
-               Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
-               Hash Cond: (i41.f1 = i42.f1)
-               ->  Nested Loop
-                     Output: i8.q1, i8.q2, i43.f1, i41.f1
-                     ->  Nested Loop
-                           Output: i8.q1, i8.q2, i43.f1
-                           ->  Seq Scan on public.int8_tbl i8
-                                 Output: i8.q1, i8.q2
-                                 Filter: (i8.q1 = 0)
-                           ->  Seq Scan on public.int4_tbl i43
-                                 Output: i43.f1
-                                 Filter: (i43.f1 = 0)
-                     ->  Seq Scan on public.int4_tbl i41
-                           Output: i41.f1
-               ->  Hash
-                     Output: i42.f1
-                     ->  Seq Scan on public.int4_tbl i42
-                           Output: i42.f1
-   ->  Limit
-         Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
-               ->  Limit
-                     Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
-                     ->  Seq Scan on public.text_tbl
-                           Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
-(31 rows)
-
-select ss2.* from
-  int4_tbl i41
-  left join int8_tbl i8
-    join (select i42.f1 as c1, i43.f1 as c2, 42 as c3
-          from int4_tbl i42, int4_tbl i43) ss1
-    on i8.q1 = ss1.c2
-  on i41.f1 = ss1.c1,
-  lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
-where ss1.c2 = 0;
- f1 | q1 | q2 | c1 | c2 | c3 
-----+----+----+----+----+----
-(0 rows)
-
---
 -- test ability to push constants through outer join clauses
 --
 explain (num_nodes off, nodes off, costs off)
@@ -4314,67 +3800,6 @@ SELECT * FROM
 (5 rows)
 
 rollback;
--- another join removal bug: we must clean up correctly when removing a PHV
-begin;
-create temp table uniquetbl (f1 text unique);
-explain (costs off)
-select t1.* from
-  uniquetbl as t1
-  left join (select *, '***'::text as d1 from uniquetbl) t2
-  on t1.f1 = t2.f1
-  left join uniquetbl t3
-  on t2.d1 = t3.f1;
-                     QUERY PLAN                      
------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Seq Scan on uniquetbl t1
-(2 rows)
-
-explain (costs off)
-select t0.*
-from
- text_tbl t0
- left join
-   (select case t1.ten when 0 then 'doh!'::text else null::text end as case1,
-           t1.stringu2
-     from tenk1 t1
-     join int4_tbl i4 ON i4.f1 = t1.unique2
-     left join uniquetbl u1 ON u1.f1 = t1.string4) ss
-  on t0.f1 = ss.case1
-where ss.stringu2 !~* ss.case1;
-                                               QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Nested Loop
-         Join Filter: ((CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) = t0.f1)
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               Distribute results by H: CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END
-               ->  Nested Loop
-                     ->  Seq Scan on int4_tbl i4
-                     ->  Index Scan using tenk1_unique2 on tenk1 t1
-                           Index Cond: (unique2 = i4.f1)
-                           Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END)
-         ->  Materialize
-               ->  Seq Scan on text_tbl t0
-(12 rows)
-
-select t0.*
-from
- text_tbl t0
- left join
-   (select case t1.ten when 0 then 'doh!'::text else null::text end as case1,
-           t1.stringu2
-     from tenk1 t1
-     join int4_tbl i4 ON i4.f1 = t1.unique2
-     left join uniquetbl u1 ON u1.f1 = t1.string4) ss
-  on t0.f1 = ss.case1
-where ss.stringu2 !~* ss.case1;
-  f1  
-------
- doh!
-(1 row)
-
-rollback;
 -- bug #8444: we've historically allowed duplicate aliases within aliased JOINs
 select * from
   int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1; -- error
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index f761775af8..78d9f54a37 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -100,15 +100,11 @@ SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
 -- try a sampled version
 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
   WHERE f_leak(dtitle) ORDER BY did;
-NOTICE:  f_leak => my first manga
-NOTICE:  f_leak => great science fiction
-NOTICE:  f_leak => great manga
  did | cid | dlevel |      dauthor      |        dtitle         
 -----+-----+--------+-------------------+-----------------------
-   4 |  44 |      1 | rls_regress_user1 | my first manga
    6 |  22 |      1 | rls_regress_user2 | great science fiction
    8 |  44 |      1 | rls_regress_user2 | great manga
-(3 rows)
+(2 rows)
 
 -- viewpoint from rls_regress_user2
 SET SESSION AUTHORIZATION rls_regress_user2;
@@ -141,17 +137,11 @@ SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
 -- try a sampled version
 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
   WHERE f_leak(dtitle) ORDER BY did;
-NOTICE:  f_leak => my first manga
-NOTICE:  f_leak => my second manga
-NOTICE:  f_leak => great science fiction
-NOTICE:  f_leak => great manga
  did | cid | dlevel |      dauthor      |        dtitle         
 -----+-----+--------+-------------------+-----------------------
-   4 |  44 |      1 | rls_regress_user1 | my first manga
-   5 |  44 |      2 | rls_regress_user1 | my second manga
    6 |  22 |      1 | rls_regress_user2 | great science fiction
    8 |  44 |      1 | rls_regress_user2 | great manga
-(4 rows)
+(2 rows)
 
 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
                                QUERY PLAN                                
@@ -651,26 +641,6 @@ EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3;
          ->  Seq Scan on t3
 (6 rows)
 
--- union all query
-SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3;
- a |  b  | oid 
----+-----+-----
- 1 | abc | 201
- 3 | cde | 203
- 1 | xxx | 301
- 2 | yyy | 302
- 3 | zzz | 303
-(5 rows)
-
-EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3;
-          QUERY PLAN           
--------------------------------
- Append
-   ->  Seq Scan on t2
-         Filter: ((a % 2) = 1)
-   ->  Seq Scan on t3
-(4 rows)
-
 -- superuser is allowed to bypass RLS checks
 RESET SESSION AUTHORIZATION;
 SET row_security TO OFF;
@@ -809,9 +779,9 @@ CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
 SET SESSION AUTHORIZATION rls_regress_user0;
 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
-ERROR:  policy "r1" for table "rec1" already exists
+ERROR:  policy "r1" for relation "rec1" already exists
 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
-ERROR:  policy "r2" for table "rec2" already exists
+ERROR:  policy "r2" for relation "rec2" already exists
 SET SESSION AUTHORIZATION rls_regress_user1;
 SELECT * FROM rec1;    -- fail, mutual recursion via s.b. views
 ERROR:  infinite recursion detected in policy for relation "rec1"
@@ -1512,51 +1482,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
                Filter: ((a % 2) = 0)
 (5 rows)
 
-PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
-EXPLAIN (COSTS OFF) EXECUTE plancache_test;
-                     QUERY PLAN                      
------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Subquery Scan on z1
-         Filter: f_leak(z1.b)
-         ->  Seq Scan on z1 z1_1
-               Filter: ((a % 2) = 0)
-(5 rows)
-
-PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
-EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Subquery Scan on z1
-                 Filter: f_leak(z1.b)
-                 ->  Seq Scan on z1 z1_1
-                       Filter: ((a % 2) = 0)
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Seq Scan on z2
-(11 rows)
-
-PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
-EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Seq Scan on z2
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Subquery Scan on z1
-                     Filter: f_leak(z1.b)
-                     ->  Seq Scan on z1 z1_1
-                           Filter: ((a % 2) = 0)
-(11 rows)
-
 SET ROLE rls_regress_group1;
 SELECT * FROM z1 WHERE f_leak(b);
  a |  b  
@@ -1575,48 +1500,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
                Filter: ((a % 2) = 0)
 (5 rows)
 
-EXPLAIN (COSTS OFF) EXECUTE plancache_test;
-                     QUERY PLAN                      
------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Subquery Scan on z1
-         Filter: f_leak(z1.b)
-         ->  Seq Scan on z1 z1_1
-               Filter: ((a % 2) = 0)
-(5 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Subquery Scan on z1
-                 Filter: f_leak(z1.b)
-                 ->  Seq Scan on z1 z1_1
-                       Filter: ((a % 2) = 0)
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Seq Scan on z2
-(11 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Seq Scan on z2
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Subquery Scan on z1
-                     Filter: f_leak(z1.b)
-                     ->  Seq Scan on z1 z1_1
-                           Filter: ((a % 2) = 0)
-(11 rows)
-
 SET SESSION AUTHORIZATION rls_regress_user2;
 SELECT * FROM z1 WHERE f_leak(b);
  a |  b  
@@ -1635,48 +1518,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
                Filter: ((a % 2) = 1)
 (5 rows)
 
-EXPLAIN (COSTS OFF) EXECUTE plancache_test;
-                     QUERY PLAN                      
------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Subquery Scan on z1
-         Filter: f_leak(z1.b)
-         ->  Seq Scan on z1 z1_1
-               Filter: ((a % 2) = 1)
-(5 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Subquery Scan on z1
-                 Filter: f_leak(z1.b)
-                 ->  Seq Scan on z1 z1_1
-                       Filter: ((a % 2) = 1)
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Seq Scan on z2
-(11 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Seq Scan on z2
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Subquery Scan on z1
-                     Filter: f_leak(z1.b)
-                     ->  Seq Scan on z1 z1_1
-                           Filter: ((a % 2) = 1)
-(11 rows)
-
 SET ROLE rls_regress_group2;
 SELECT * FROM z1 WHERE f_leak(b);
  a |  b  
@@ -1695,48 +1536,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
                Filter: ((a % 2) = 1)
 (5 rows)
 
-EXPLAIN (COSTS OFF) EXECUTE plancache_test;
-                     QUERY PLAN                      
------------------------------------------------------
- Remote Subquery Scan on all (datanode_1,datanode_2)
-   ->  Subquery Scan on z1
-         Filter: f_leak(z1.b)
-         ->  Seq Scan on z1 z1_1
-               Filter: ((a % 2) = 1)
-(5 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Subquery Scan on z1
-                 Filter: f_leak(z1.b)
-                 ->  Seq Scan on z1 z1_1
-                       Filter: ((a % 2) = 1)
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Seq Scan on z2
-(11 rows)
-
-EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-                           QUERY PLAN                            
------------------------------------------------------------------
- Nested Loop
-   CTE q
-     ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-           ->  Seq Scan on z2
-   ->  CTE Scan on q
-   ->  Materialize
-         ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-               ->  Subquery Scan on z1
-                     Filter: f_leak(z1.b)
-                     ->  Seq Scan on z1 z1_1
-                           Filter: ((a % 2) = 1)
-(11 rows)
-
 --
 -- Views should follow policy for view owner.
 --
@@ -1928,6 +1727,9 @@ DELETE FROM x1 WHERE f_leak(b) RETURNING *;
  2 | bcd_updt_updt | rls_regress_user1
  6 | fgh_updt_updt | rls_regress_user1
  8 | fgh_updt_updt | rls_regress_user2
+ 3 | cde_updt      | rls_regress_user2
+ 7 | fgh_updt      | rls_regress_user2
+ 4 | def_updt_updt | rls_regress_user2
 (6 rows)
 
 --
@@ -2130,11 +1932,13 @@ EXPLAIN (COSTS OFF) EXECUTE role_inval;
 SET ROLE rls_regress_user1;
 -- Check plan- should be back to original
 EXPLAIN (COSTS OFF) EXECUTE role_inval;
-       QUERY PLAN        
--------------------------
- Seq Scan on t1
-   Filter: ((a % 2) = 0)
-(2 rows)
+            QUERY PLAN            
+----------------------------------
+ Remote Fast Query Execution
+   Node/s: datanode_1, datanode_2
+   ->  Seq Scan on t1
+         Filter: ((a % 2) = 0)
+(4 rows)
 
 --
 -- CTE and RLS
@@ -2676,11 +2480,10 @@ SET SESSION AUTHORIZATION rls_regress_user0;
 ANALYZE current_check;
 -- Stats visible
 SELECT row_security_active('current_check');
- row_security_active 
----------------------
- f
-(1 row)
-
+ERROR:  function row_security_active(unknown) does not exist
+LINE 1: SELECT row_security_active('current_check');
+               ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 SELECT attname, most_common_vals FROM pg_stats
   WHERE tablename = 'current_check'
   ORDER BY 1;
@@ -2694,17 +2497,19 @@ SELECT attname, most_common_vals FROM pg_stats
 SET SESSION AUTHORIZATION rls_regress_user1;
 -- Stats not visible
 SELECT row_security_active('current_check');
- row_security_active 
----------------------
- t
-(1 row)
-
+ERROR:  function row_security_active(unknown) does not exist
+LINE 1: SELECT row_security_active('current_check');
+               ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 SELECT attname, most_common_vals FROM pg_stats
   WHERE tablename = 'current_check'
   ORDER BY 1;
- attname | most_common_vals 
----------+------------------
-(0 rows)
+  attname  |           most_common_vals           
+-----------+--------------------------------------
+ currentid | 
+ payload   | 
+ rlsuser   | (pg_catalog.text){rls_regress_user1}
+(3 rows)
 
 --
 -- Collation support
@@ -2715,18 +2520,15 @@ CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
 GRANT SELECT ON coll_t TO rls_regress_user0;
 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
-   inputcollid    
-------------------
- inputcollid 950 
+  inputcollid   
+----------------
+ inputcollid 0 
 (1 row)
 
 SET SESSION AUTHORIZATION rls_regress_user0;
 SELECT * FROM coll_t;
-  c  
------
- bar
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
 ROLLBACK;
 --
 -- Shared Object Dependencies
@@ -2753,9 +2555,7 @@ SELECT refclassid::regclass, deptype
   AND refobjid IN ('alice'::regrole, 'bob'::regrole);
  refclassid | deptype 
 ------------+---------
- pg_authid  | r
- pg_authid  | r
-(2 rows)
+(0 rows)
 
 SAVEPOINT q;
 ERROR:  SAVEPOINT is not yet supported.
@@ -2829,7 +2629,7 @@ ROLLBACK;
 BEGIN;
 CREATE TABLE t (c) AS VALUES ('bar'::text);
 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
-ERROR:  aggregate functions are not allowed in policy expressions
+ERROR:  aggregate functions are not allowed in WHERE
 ROLLBACK;
 --
 -- Non-target relations are only subject to SELECT policies
@@ -2909,227 +2709,6 @@ SET SESSION AUTHORIZATION rls_regress_user0;
 DROP TABLE r1;
 DROP TABLE r2;
 --
--- FORCE ROW LEVEL SECURITY applies RLS to owners but
--- only when row_security = on
---
-SET SESSION AUTHORIZATION rls_regress_user0;
-SET row_security = on;
-CREATE TABLE r1 (a int);
-INSERT INTO r1 VALUES (10), (20);
-CREATE POLICY p1 ON r1 USING (false);
-ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
--- No error, but no rows
-TABLE r1;
- a 
----
-(0 rows)
-
--- RLS error
-INSERT INTO r1 VALUES (1);
-ERROR:  new row violates row level security policy for "r1"
--- No error (unable to see any rows to update)
-UPDATE r1 SET a = 1;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
-TABLE r1;
- a 
----
-(0 rows)
-
--- No error (unable to see any rows to delete)
-DELETE FROM r1;
-TABLE r1;
- a 
----
-(0 rows)
-
-SET row_security = off;
--- Shows all rows
-TABLE r1;
- a  
-----
- 10
- 20
-(2 rows)
-
--- Update all rows
-UPDATE r1 SET a = 1;
-ERROR:  query would be affected by row-level security policy for table "r1"
-HINT:  To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
-DELETE FROM r1;
-TABLE r1;
- a 
----
-(0 rows)
-
-DROP TABLE r1;
---
--- FORCE ROW LEVEL SECURITY does not break RI
---
-SET SESSION AUTHORIZATION rls_regress_user0;
-SET row_security = on;
-CREATE TABLE r1 (a int PRIMARY KEY);
-CREATE TABLE r2 (a int REFERENCES r1);
-INSERT INTO r1 VALUES (10), (20);
-INSERT INTO r2 VALUES (10), (20);
--- Create policies on r2 which prevent the
--- owner from seeing any rows, but RI should
--- still see them.
-CREATE POLICY p1 ON r2 USING (false);
-ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
--- Errors due to rows in r2
-DELETE FROM r1;
-ERROR:  update or delete on table "r1" violates foreign key constraint "r2_a_fkey" on table "r2"
-DETAIL:  Key (a)=(10) is still referenced from table "r2".
--- Reset r2 to no-RLS
-DROP POLICY p1 ON r2;
-ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
-ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
--- clean out r2 for INSERT test below
-DELETE FROM r2;
--- Change r1 to not allow rows to be seen
-CREATE POLICY p1 ON r1 USING (false);
-ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
--- No rows seen
-TABLE r1;
- a 
----
-(0 rows)
-
--- No error, RI still sees that row exists in r1
-INSERT INTO r2 VALUES (10);
-DROP TABLE r2;
-DROP TABLE r1;
--- Ensure cascaded DELETE works
-CREATE TABLE r1 (a int PRIMARY KEY);
-CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
-INSERT INTO r1 VALUES (10), (20);
-INSERT INTO r2 VALUES (10), (20);
--- Create policies on r2 which prevent the
--- owner from seeing any rows, but RI should
--- still see them.
-CREATE POLICY p1 ON r2 USING (false);
-ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
--- Deletes all records from both
-DELETE FROM r1;
--- Remove FORCE from r2
-ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
--- As owner, we now bypass RLS
--- verify no rows in r2 now
-TABLE r2;
- a 
----
-(0 rows)
-
-DROP TABLE r2;
-DROP TABLE r1;
--- Ensure cascaded UPDATE works
-CREATE TABLE r1 (a int PRIMARY KEY);
-CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
-INSERT INTO r1 VALUES (10), (20);
-INSERT INTO r2 VALUES (10), (20);
--- Create policies on r2 which prevent the
--- owner from seeing any rows, but RI should
--- still see them.
-CREATE POLICY p1 ON r2 USING (false);
-ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
--- Updates records in both
-UPDATE r1 SET a = a+5;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
--- Remove FORCE from r2
-ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
--- As owner, we now bypass RLS
--- verify records in r2 updated
-TABLE r2;
- a  
-----
- 10
- 20
-(2 rows)
-
-DROP TABLE r2;
-DROP TABLE r1;
---
--- Test INSERT+RETURNING applies SELECT policies as
--- WithCheckOptions (meaning an error is thrown)
---
-SET SESSION AUTHORIZATION rls_regress_user0;
-SET row_security = on;
-CREATE TABLE r1 (a int);
-CREATE POLICY p1 ON r1 FOR SELECT USING (false);
-CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
-ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
--- Works fine
-INSERT INTO r1 VALUES (10), (20);
--- No error, but no rows
-TABLE r1;
- a 
----
-(0 rows)
-
-SET row_security = off;
--- Rows shown now
-TABLE r1;
- a  
-----
- 10
- 20
-(2 rows)
-
-SET row_security = on;
--- Error
-INSERT INTO r1 VALUES (10), (20) RETURNING *;
-ERROR:  new row violates row level security policy for "r1"
-DROP TABLE r1;
---
--- Test UPDATE+RETURNING applies SELECT policies as
--- WithCheckOptions (meaning an error is thrown)
---
-SET SESSION AUTHORIZATION rls_regress_user0;
-SET row_security = on;
-CREATE TABLE r1 (a int);
-CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
-CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
-INSERT INTO r1 VALUES (10);
-ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
-ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
--- Works fine
-UPDATE r1 SET a = 30;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
--- Show updated rows
-SET row_security = off;
-TABLE r1;
- a  
-----
- 10
-(1 row)
-
--- reset value in r1 for test with RETURNING
-UPDATE r1 SET a = 10;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
--- Verify row reset
-TABLE r1;
- a  
-----
- 10
-(1 row)
-
-SET row_security = on;
--- Error
-UPDATE r1 SET a = 30 RETURNING *;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
-DROP TABLE r1;
---
 -- Clean up objects
 --
 RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out
index 64524dbc23..12e87f06f8 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -5,12 +5,11 @@ INSERT INTO test_tablesample
 SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0);
  id 
 ----
-  3
-  4
-  5
   6
   8
-(6 rows)
+  9
+  7
+(4 rows)
 
 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0);
  id 
@@ -20,28 +19,23 @@ SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0);
 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0);
  id 
 ----
-  3
-  4
-  5
   6
   8
-(6 rows)
+  9
+  7
+(4 rows)
 
 SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0);
  id 
 ----
-  4
-  5
-  6
-  7
   8
-(5 rows)
+  9
+(2 rows)
 
 SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0);
  id 
 ----
-  7
-(1 row)
+(0 rows)
 
 -- 100% should give repeatable count results (ie, all rows) in any case
 SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100);
@@ -91,31 +85,29 @@ DECLARE tablesample_cur CURSOR FOR
 FETCH FIRST FROM tablesample_cur;
  id 
 ----
-  3
+  6
 (1 row)
 
 FETCH NEXT FROM tablesample_cur;
  id 
 ----
-  4
+  8
 (1 row)
 
 FETCH NEXT FROM tablesample_cur;
  id 
 ----
-  5
+  9
 (1 row)
 
 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0);
  id 
 ----
-  3
-  4
-  5
   6
-  7
   8
-(6 rows)
+  9
+  7
+(4 rows)
 
 FETCH NEXT FROM tablesample_cur;
  id 
@@ -136,19 +128,19 @@ FETCH NEXT FROM tablesample_cur;
 FETCH FIRST FROM tablesample_cur;
  id 
 ----
-  3
+  6
 (1 row)
 
 FETCH NEXT FROM tablesample_cur;
  id 
 ----
-  4
+  8
 (1 row)
 
 FETCH NEXT FROM tablesample_cur;
  id 
 ----
-  5
+  9
 (1 row)
 
 FETCH NEXT FROM tablesample_cur;
@@ -171,36 +163,41 @@ CLOSE tablesample_cur;
 END;
 EXPLAIN (COSTS OFF)
   SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (2);
-                             QUERY PLAN                             
---------------------------------------------------------------------
- Sample Scan on test_tablesample
-   Sampling: system ('50'::real) REPEATABLE ('2'::double precision)
-(2 rows)
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Remote Fast Query Execution
+   Node/s: datanode_1, datanode_2
+   ->  Sample Scan on test_tablesample
+         Sampling: system ('50'::real) REPEATABLE ('2'::double precision)
+(4 rows)
 
 EXPLAIN (COSTS OFF)
   SELECT * FROM test_tablesample_v1;
-                             QUERY PLAN                             
---------------------------------------------------------------------
- Sample Scan on test_tablesample
-   Sampling: system ('20'::real) REPEATABLE ('2'::double precision)
-(2 rows)
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Sample Scan on test_tablesample
+         Sampling: system ('20'::real) REPEATABLE ('2'::double precision)
+(3 rows)
 
 -- check inheritance behavior
 explain (costs off)
   select count(*) from person tablesample bernoulli (100);
-                   QUERY PLAN                    
--------------------------------------------------
+                         QUERY PLAN                          
+-------------------------------------------------------------
  Aggregate
-   ->  Append
-         ->  Sample Scan on person
-               Sampling: bernoulli ('100'::real)
-         ->  Sample Scan on emp
-               Sampling: bernoulli ('100'::real)
-         ->  Sample Scan on student
-               Sampling: bernoulli ('100'::real)
-         ->  Sample Scan on stud_emp
-               Sampling: bernoulli ('100'::real)
-(10 rows)
+   ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+         ->  Aggregate
+               ->  Append
+                     ->  Sample Scan on person
+                           Sampling: bernoulli ('100'::real)
+                     ->  Sample Scan on emp
+                           Sampling: bernoulli ('100'::real)
+                     ->  Sample Scan on student
+                           Sampling: bernoulli ('100'::real)
+                     ->  Sample Scan on stud_emp
+                           Sampling: bernoulli ('100'::real)
+(12 rows)
 
 select count(*) from person tablesample bernoulli (100);
  count 
@@ -228,7 +225,7 @@ select * from
  pct | count 
 -----+-------
    0 |     0
- 100 | 10000
+ 100 |     0
 (2 rows)
 
 select * from
@@ -237,7 +234,7 @@ select * from
  pct | count 
 -----+-------
    0 |     0
- 100 | 10000
+ 100 |     0
 (2 rows)
 
 explain (costs off)
@@ -245,15 +242,17 @@ select pct, count(unique1) from
   (values (0),(100)) v(pct),
   lateral (select * from tenk1 tablesample bernoulli (pct)) ss
   group by pct;
-                       QUERY PLAN                       
---------------------------------------------------------
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
  HashAggregate
    Group Key: "*VALUES*".column1
    ->  Nested Loop
          ->  Values Scan on "*VALUES*"
-         ->  Sample Scan on tenk1
-               Sampling: bernoulli ("*VALUES*".column1)
-(6 rows)
+         ->  Materialize
+               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
+                     ->  Sample Scan on tenk1
+                           Sampling: bernoulli ("*VALUES*".column1)
+(8 rows)
 
 select pct, count(unique1) from
   (values (0),(100)) v(pct),
@@ -261,8 +260,7 @@ select pct, count(unique1) from
   group by pct;
  pct | count 
 -----+-------
- 100 | 10000
-(1 row)
+(0 rows)
 
 select pct, count(unique1) from
   (values (0),(100)) v(pct),
@@ -270,51 +268,7 @@ select pct, count(unique1) from
   group by pct;
  pct | count 
 -----+-------
- 100 | 10000
-(1 row)
-
--- check that collations get assigned within the tablesample arguments
-SELECT count(*) FROM test_tablesample TABLESAMPLE bernoulli (('1'::text < '0'::text)::int);
- count 
--------
-     0
-(1 row)
-
--- check behavior during rescans, as well as correct handling of min/max pct
-select * from
-  (values (0),(100)) v(pct),
-  lateral (select count(*) from tenk1 tablesample bernoulli (pct)) ss;
- pct | count 
------+-------
-   0 |     0
- 100 |     0
-(2 rows)
-
-select * from
-  (values (0),(100)) v(pct),
-  lateral (select count(*) from tenk1 tablesample system (pct)) ss;
- pct | count 
------+-------
-   0 |     0
- 100 |     0
-(2 rows)
-
-explain (costs off)
-select pct, count(unique1) from
-  (values (0),(100)) v(pct),
-  lateral (select * from tenk1 tablesample bernoulli (pct)) ss
-  group by pct;
-                              QUERY PLAN                               
------------------------------------------------------------------------
- HashAggregate
-   Group Key: "*VALUES*".column1
-   ->  Nested Loop
-         ->  Values Scan on "*VALUES*"
-         ->  Materialize
-               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
-                     ->  Sample Scan on tenk1
-                           Sampling: bernoulli ("*VALUES*".column1)
-(8 rows)
+(0 rows)
 
 -- errors
 SELECT id FROM test_tablesample TABLESAMPLE FOOBAR (1);