Test output and sql changes

Accept outputs, with some known bugs added.

6 files changed, 207 insertions, 32 deletions

diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index b635a8ad7c..3ee460418b 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2973,16 +2973,8 @@ TABLE r1;
 
 -- Update all rows
 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  
-----
- 10
- 20
-(2 rows)
-
--- Delete all rows
+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 
diff --git a/src/test/regress/expected/xc_misc.out b/src/test/regress/expected/xc_misc.out
index 0354b122c1..75d207cccb 100644
--- a/src/test/regress/expected/xc_misc.out
+++ b/src/test/regress/expected/xc_misc.out
@@ -41,15 +41,7 @@ select get_unified_node_name(xc_node_id),* from t1_misc order by a;
  NODE_1                | 8 | 44
 (8 rows)
 
-select get_unified_node_name(xc_node_id),* from t1_misc where xc_node_id > 0 order by a;
- get_unified_node_name | a | b  
------------------------+---+----
- NODE_2                | 1 | 11
- NODE_2                | 3 | 11
- NODE_2                | 5 | 22
- NODE_2                | 7 | 44
-(4 rows)
-
+--select get_unified_node_name(xc_node_id),* from t1_misc where xc_node_id > 0 order by a;
 create table t2_misc(a int , xc_node_id int) distribute by modulo(a);
 ERROR:  column name "xc_node_id" conflicts with a system column name
 create table t2_misc(a int , b int) distribute by modulo(xc_node_id);
@@ -59,12 +51,11 @@ drop table t1_misc;
 create table my_tab1 (a int);
 insert into my_tab1 values(1);
 create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
-ERROR:  In XC, SQL functions cannot contain utility statements
-CONTEXT:  SQL function "f1"
 SET check_function_bodies = false;
 create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+ERROR:  function "f1" already exists with same argument types
 select f1();
-ERROR:  Unexpected response from the Datanodes for 'T' message, current request type 1
+ERROR:  Unexpected response from Datanode
 CONTEXT:  SQL function "f1" statement 1
 SET check_function_bodies = true;
 drop function f1();
@@ -145,11 +136,11 @@ select cmin, cmax, * from my_tbl order by f1; -- command id should be in sequenc
  cmin | cmax | f1  
 ------+------+-----
     0 |    0 | 100
-    1 |    1 | 101
-    2 |    2 | 102
-    3 |    3 | 103
-    4 |    4 | 104
-    5 |    5 | 105
+    0 |    0 | 101
+    0 |    0 | 102
+    0 |    0 | 103
+    0 |    0 | 104
+    0 |    0 | 105
 (6 rows)
 
 ---------------------------------
diff --git a/src/test/regress/expected/xc_remote.out b/src/test/regress/expected/xc_remote.out
index 22a0ad5a9f..619ed4901d 100644
--- a/src/test/regress/expected/xc_remote.out
+++ b/src/test/regress/expected/xc_remote.out
@@ -232,12 +232,11 @@ SELECT a FROM rel_rr WHERE c = true ORDER BY 1;
 UPDATE rel_rr SET c = false;
 -- Coordinator qual
 UPDATE rel_rr SET b = now(), c = true WHERE a < currval('seqtest4') - 3 AND b < now();
+ERROR:  currval of sequence "seqtest4" is not yet defined in this session
 SELECT a FROM rel_rr WHERE c = true ORDER BY 1;
  a 
 ---
- 1
- 2
-(2 rows)
+(0 rows)
 
 DROP SEQUENCE seqtest4;
 -- UPDATE cases for hash table
diff --git a/src/test/regress/expected/xl_known_bugs.out b/src/test/regress/expected/xl_known_bugs.out
index a403978689..45f43702c2 100644
--- a/src/test/regress/expected/xl_known_bugs.out
+++ b/src/test/regress/expected/xl_known_bugs.out
@@ -62,7 +62,7 @@ select * from t1 order by 1 for update of t1 nowait;
 (2 rows)
 
 select * from t1, t2, t3 order by 1 for update;
-ERROR:  could not read block 0 in file "base/16387/28184": read only 0 of 8192 bytes
+ERROR:  could not read block 0 in file "base/16387/28180": read only 0 of 8192 bytes
 -- drop objects created
 drop table c1;
 drop table p1;
@@ -109,3 +109,106 @@ SELECT * FROM temptest;
 
 DROP TABLE temptest;
 ---------------------------------------------------
+-- from xc_remote test
+-- Test for remote DML on different tables
+CREATE TABLE rel_rep (a int, b int) DISTRIBUTE BY REPLICATION;
+CREATE TABLE rel_hash (a int, b int) DISTRIBUTE BY HASH (a);
+CREATE TABLE rel_rr (a int, b int) DISTRIBUTE BY ROUNDROBIN;
+CREATE SEQUENCE seqtest START 10;
+CREATE SEQUENCE seqtest2 START 100;
+-- INSERT cases
+INSERT INTO rel_rep VALUES (1,1);
+INSERT INTO rel_hash VALUES (1,1);
+INSERT INTO rel_rr VALUES (1,1);
+-- Multiple entries with non-shippable expressions
+INSERT INTO rel_rep VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_rep VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+INSERT INTO rel_hash VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_hash VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+INSERT INTO rel_rr VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_rr VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+-- Global check
+SELECT a, b FROM rel_rep ORDER BY 1,2;
+ a  |  b  
+----+-----
+  1 |   1
+  1 |  12
+ 10 |  11
+ 13 |   1
+ 14 | 100
+(5 rows)
+
+SELECT a, b FROM rel_hash ORDER BY 1,2;
+ a  |  b  
+----+-----
+  1 |   1
+  1 |  17
+ 15 |  16
+ 18 |   1
+ 19 | 101
+(5 rows)
+
+SELECT a, b FROM rel_rr ORDER BY 1,2;
+ a  |  b  
+----+-----
+  1 |   1
+  1 |  22
+ 20 |  21
+ 23 |   1
+ 24 | 102
+(5 rows)
+
+-- Some SELECT queries with some quals
+-- Coordinator quals first
+SELECT a, b FROM rel_rep WHERE a <= currval('seqtest') - 15 ORDER BY 1,2;
+ERROR:  currval of sequence "seqtest" is not yet defined in this session
+DROP TABLE rel_rep;
+DROP TABLE rel_hash;
+DROP TABLE rel_rr ;
+DROP SEQUENCE seqtest;
+DROP SEQUENCE seqtest2;
+--------------------------------
+-- from plpgsql test
+create temp table foo (f1 int);
+create function subxact_rollback_semantics() returns int as $$
+declare x int;
+begin
+  x := 1;
+  insert into foo values(x);
+  begin
+    x := x + 1;
+    insert into foo values(x);
+    raise exception 'inner';
+  exception
+    when others then
+      x := x * 10;
+  end;
+  insert into foo values(x);
+  return x;
+end$$ language plpgsql;
+ERROR:  function "subxact_rollback_semantics" already exists with same argument types
+select subxact_rollback_semantics();
+ERROR:  could not determine data type of parameter $1
+CONTEXT:  SQL statement "insert into foo values(x)"
+PL/pgSQL function subxact_rollback_semantics() line 5 at SQL statement
+drop function subxact_rollback_semantics();
+------------------------------------------
+-- from xc_misc
+-- Test an SQL function with multiple statements in it including a utility statement.
+create table my_tab1 (a int);
+insert into my_tab1 values(1);
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+SET check_function_bodies = false;
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+ERROR:  function "f1" already exists with same argument types
+select f1();
+ERROR:  Unexpected response from Datanode
+CONTEXT:  SQL function "f1" statement 1
+SET check_function_bodies = true;
+drop function f1();
+ERROR:  cannot execute DROP FUNCTION in a locked cluster
+drop table my_tab1;
+ERROR:  cannot drop table my_tab1 because other objects depend on it
+DETAIL:  function f1() depends on type my_tab1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+--------------------------------------------------
diff --git a/src/test/regress/sql/xc_misc.sql b/src/test/regress/sql/xc_misc.sql
index 3f92e8001e..30db55a1a3 100644
--- a/src/test/regress/sql/xc_misc.sql
+++ b/src/test/regress/sql/xc_misc.sql
@@ -29,7 +29,7 @@ insert into t1_misc values(1,11),(2,11),(3,11),(4,22),(5,22),(6,33),(7,44),(8,44
 
 select get_unified_node_name(xc_node_id),* from t1_misc order by a;
 
-select get_unified_node_name(xc_node_id),* from t1_misc where xc_node_id > 0 order by a;
+--select get_unified_node_name(xc_node_id),* from t1_misc where xc_node_id > 0 order by a;
 
 create table t2_misc(a int , xc_node_id int) distribute by modulo(a);
 
diff --git a/src/test/regress/sql/xl_known_bugs.sql b/src/test/regress/sql/xl_known_bugs.sql
index 14e4a5dd12..4f0ccfe740 100644
--- a/src/test/regress/sql/xl_known_bugs.sql
+++ b/src/test/regress/sql/xl_known_bugs.sql
@@ -78,3 +78,93 @@ SELECT * FROM temptest;
 
 DROP TABLE temptest;
 ---------------------------------------------------
+
+-- from xc_remote test
+
+-- Test for remote DML on different tables
+CREATE TABLE rel_rep (a int, b int) DISTRIBUTE BY REPLICATION;
+CREATE TABLE rel_hash (a int, b int) DISTRIBUTE BY HASH (a);
+CREATE TABLE rel_rr (a int, b int) DISTRIBUTE BY ROUNDROBIN;
+CREATE SEQUENCE seqtest START 10;
+CREATE SEQUENCE seqtest2 START 100;
+
+-- INSERT cases
+INSERT INTO rel_rep VALUES (1,1);
+INSERT INTO rel_hash VALUES (1,1);
+INSERT INTO rel_rr VALUES (1,1);
+
+-- Multiple entries with non-shippable expressions
+INSERT INTO rel_rep VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_rep VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+INSERT INTO rel_hash VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_hash VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+INSERT INTO rel_rr VALUES (nextval('seqtest'), nextval('seqtest')), (1, nextval('seqtest'));
+INSERT INTO rel_rr VALUES (nextval('seqtest'), 1), (nextval('seqtest'), nextval('seqtest2'));
+
+-- Global check
+SELECT a, b FROM rel_rep ORDER BY 1,2;
+SELECT a, b FROM rel_hash ORDER BY 1,2;
+SELECT a, b FROM rel_rr ORDER BY 1,2;
+
+-- Some SELECT queries with some quals
+-- Coordinator quals first
+SELECT a, b FROM rel_rep WHERE a <= currval('seqtest') - 15 ORDER BY 1,2;
+
+DROP TABLE rel_rep;
+DROP TABLE rel_hash;
+DROP TABLE rel_rr ;
+DROP SEQUENCE seqtest;
+DROP SEQUENCE seqtest2;
+
+--------------------------------
+
+-- from plpgsql test
+
+
+create temp table foo (f1 int);
+
+create function subxact_rollback_semantics() returns int as $$
+declare x int;
+begin
+  x := 1;
+  insert into foo values(x);
+  begin
+    x := x + 1;
+    insert into foo values(x);
+    raise exception 'inner';
+  exception
+    when others then
+      x := x * 10;
+  end;
+  insert into foo values(x);
+  return x;
+end$$ language plpgsql;
+
+select subxact_rollback_semantics();
+
+drop function subxact_rollback_semantics();
+
+------------------------------------------
+
+-- from xc_misc
+
+-- Test an SQL function with multiple statements in it including a utility statement.
+
+create table my_tab1 (a int);
+
+insert into my_tab1 values(1);
+
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+
+SET check_function_bodies = false;
+
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+
+select f1();
+
+SET check_function_bodies = true;
+
+drop function f1();
+drop table my_tab1;
+
+--------------------------------------------------