Fix identity test case

Accept some differences as the identity column is also used distribution
column and hence updates are disallowed in XL. So accept those differences (we
should later add XL-specific test cases by having identity in non-distribution
column). Also add ORDER BY in some select queries to ensure consistent ordering
of the result

2 files changed, 28 insertions, 22 deletions

diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 88b56dad93..7844395da2 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -77,7 +77,7 @@ SELECT * FROM itest2;
  2 | 
 (2 rows)
 
-SELECT * FROM itest3;
+SELECT * FROM itest3 ORDER BY a;
  a  | b 
 ----+---
   7 | 
@@ -94,13 +94,13 @@ SELECT * FROM itest4;
 -- OVERRIDING tests
 INSERT INTO itest1 VALUES (10, 'xyz');
 INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
-SELECT * FROM itest1;
+SELECT * FROM itest1 ORDER BY a;
  a  |  b  
 ----+-----
   1 | 
   2 | 
- 10 | xyz
   3 | xyz
+ 10 | xyz
 (4 rows)
 
 INSERT INTO itest2 VALUES (10, 'xyz');
@@ -108,7 +108,7 @@ ERROR:  cannot insert into column "a"
 DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
 HINT:  Use OVERRIDING SYSTEM VALUE to override.
 INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
-SELECT * FROM itest2;
+SELECT * FROM itest2 ORDER BY a;
  a  |  b  
 ----+-----
   1 | 
@@ -118,26 +118,32 @@ SELECT * FROM itest2;
 
 -- UPDATE tests
 UPDATE itest1 SET a = 101 WHERE a = 1;
+ERROR:  could not plan this distributed update
+DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
 UPDATE itest1 SET a = DEFAULT WHERE a = 2;
-SELECT * FROM itest1;
-  a  |  b  
------+-----
-  10 | xyz
-   3 | xyz
- 101 | 
-   4 | 
+ERROR:  could not plan this distributed update
+DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
+SELECT * FROM itest1 ORDER BY a;
+ a  |  b  
+----+-----
+  1 | 
+  2 | 
+  3 | xyz
+ 10 | xyz
 (4 rows)
 
 UPDATE itest2 SET a = 101 WHERE a = 1;
 ERROR:  column "a" can only be updated to DEFAULT
 DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
 UPDATE itest2 SET a = DEFAULT WHERE a = 2;
-SELECT * FROM itest2;
+ERROR:  could not plan this distributed update
+DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
+SELECT * FROM itest2 ORDER BY a;
  a  |  b  
 ----+-----
   1 | 
+  2 | 
  10 | xyz
-  3 | 
 (3 rows)
 
 -- DROP IDENTITY tests
@@ -151,7 +157,7 @@ ERROR:  null value in column "a" violates not-null constraint
 DETAIL:  Failing row contains (null, ).
 ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
 INSERT INTO itest4 DEFAULT VALUES;
-SELECT * FROM itest4;
+SELECT * FROM itest4 ORDER BY a;
  a | b 
 ---+---
  1 | 
@@ -241,7 +247,7 @@ INSERT INTO itest6 DEFAULT VALUES;
 ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
 INSERT INTO itest6 DEFAULT VALUES;
 INSERT INTO itest6 DEFAULT VALUES;
-SELECT * FROM itest6;
+SELECT * FROM itest6 ORDER BY a;
   a  | b 
 -----+---
    1 | 
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index a7e7b15737..a14f9c9cc4 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -46,7 +46,7 @@ INSERT INTO itest4 DEFAULT VALUES;
 
 SELECT * FROM itest1;
 SELECT * FROM itest2;
-SELECT * FROM itest3;
+SELECT * FROM itest3 ORDER BY a;
 SELECT * FROM itest4;
 
 
@@ -55,23 +55,23 @@ SELECT * FROM itest4;
 INSERT INTO itest1 VALUES (10, 'xyz');
 INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
 
-SELECT * FROM itest1;
+SELECT * FROM itest1 ORDER BY a;
 
 INSERT INTO itest2 VALUES (10, 'xyz');
 INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
 
-SELECT * FROM itest2;
+SELECT * FROM itest2 ORDER BY a;
 
 
 -- UPDATE tests
 
 UPDATE itest1 SET a = 101 WHERE a = 1;
 UPDATE itest1 SET a = DEFAULT WHERE a = 2;
-SELECT * FROM itest1;
+SELECT * FROM itest1 ORDER BY a;
 
 UPDATE itest2 SET a = 101 WHERE a = 1;
 UPDATE itest2 SET a = DEFAULT WHERE a = 2;
-SELECT * FROM itest2;
+SELECT * FROM itest2 ORDER BY a;
 
 
 -- DROP IDENTITY tests
@@ -83,7 +83,7 @@ ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS;  -- noop
 INSERT INTO itest4 DEFAULT VALUES;  -- fails because NOT NULL is not dropped
 ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
 INSERT INTO itest4 DEFAULT VALUES;
-SELECT * FROM itest4;
+SELECT * FROM itest4 ORDER BY a;
 
 -- check that sequence is removed
 SELECT sequence_name FROM itest4_a_seq;
@@ -141,7 +141,7 @@ INSERT INTO itest6 DEFAULT VALUES;
 ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
 INSERT INTO itest6 DEFAULT VALUES;
 INSERT INTO itest6 DEFAULT VALUES;
-SELECT * FROM itest6;
+SELECT * FROM itest6 ORDER BY a;
 
 SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';