-- Create common test tables
--
-- All the test statements use these same test tables. To avoid having to
-- re-create them for every test, all the actual tests are wrapped in
-- begin-rollback blocks (except a few that throw an ERROR, and will
-- therefore roll back implicitly anyway). The purpose of these tests is to
-- exercise planner, so it doesn't matter that the changes are rolled back
-- afterwards.
-- start_matchsubs
-- m/DETAIL:  Failing row contains \(.*\)/
-- s/DETAIL:  Failing row contains \(.*\)/DETAIL:  Failing row contains (#####)/
-- end_matchsubs
begin;
CREATE TABLE dml_union_r (
        a int CONSTRAINT r_check_a CHECK(a <> -1),
        b int,
        c text,
        d numeric)
DISTRIBUTED BY (a);
CREATE TABLE dml_union_s (
        a int ,
        b int not NULL,
        c text ,
        d numeric default 10.00)
DISTRIBUTED BY (b)
PARTITION BY range(d) (
        start(1) end(1001) every(100),
        default partition def
);
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_def" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_2" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_3" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_4" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_5" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_6" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_7" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_8" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_9" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_10" for table "dml_union_s"
NOTICE:  CREATE TABLE will create partition "dml_union_s_1_prt_11" for table "dml_union_s"
INSERT INTO dml_union_r SELECT generate_series(1,100), generate_series(1,100) * 3,'r', generate_series(1,100) % 6;
INSERT INTO dml_union_r VALUES(NULL,NULL,'text',NULL),(NULL,NULL,'text',NULL),(NULL,NULL,'text',NULL),(NULL,NULL,'text',NULL),(NULL,NULL,'text',NULL);
INSERT INTO dml_union_r VALUES(1,1,'text',1),(2,2,'text',2),(3,3,'text',3),(4,4,'text',4),(5,5,'text',5);
INSERT INTO dml_union_r VALUES(1,1,'text',1),(2,2,'text',2),(3,3,'text',3),(4,4,'text',4),(5,5,'text',5);
INSERT INTO dml_union_r VALUES(1,2,'text',3),(2,3,'text',4),(3,4,'text',5),(4,5,'text',6),(5,6,'text',7);
INSERT INTO dml_union_s SELECT generate_series(1,100), generate_series(1,100) * 3,'s', generate_series(1,100) ;
INSERT INTO dml_union_s VALUES(1,1,'text',1),(2,2,'text',2),(3,3,'text',3),(4,4,'text',4),(5,5,'text',5);
INSERT INTO dml_union_s VALUES(1,1,'text',1),(2,2,'text',2),(3,3,'text',3),(4,4,'text',4),(5,5,'text',5);
INSERT INTO dml_union_s VALUES(1,2,'text',3),(2,3,'text',4),(3,4,'text',5),(4,5,'text',6),(5,6,'text',7);
commit;
-- @description union_test1: INSERT and INTERSECT operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r INTERSECT SELECT dml_union_s.* FROM dml_union_s)foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r INTERSECT SELECT dml_union_s.* FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test2: INSERT and INTERSECT operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.* FROM dml_union_r INTERSECT ALL SELECT dml_union_s.a, dml_union_s.b, dml_union_s.c, dml_union_s.d FROM dml_union_s)foo;
 count 
-------
    15
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.* FROM dml_union_r INTERSECT ALL SELECT dml_union_s.a, dml_union_s.b, dml_union_s.c, dml_union_s.d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   135
(1 row)

rollback;
-- @description union_test3: INTERSECT with generate_series
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT generate_series(1,10) INTERSECT SELECT generate_series(1,100))foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT generate_series(1,10) INTERSECT SELECT generate_series(1,100);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test4: INTERSECT with generate_series
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT generate_series(1,10) INTERSECT ALL  SELECT generate_series(1,100))foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT generate_series(1,10) INTERSECT ALL  SELECT generate_series(1,100);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test5: INTERSECT with constants
begin;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   115
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b,'A' as c, 0 as d FROM dml_union_r INTERSECT SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s)foo;
 count 
-------
   110
(1 row)

INSERT INTO dml_union_s (SELECT dml_union_r.a, dml_union_r.b,'A' as c, 0 as d FROM dml_union_r INTERSECT SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   225
(1 row)

rollback;
-- @description union_test6: INTERSECT with constants
begin;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   115
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b,'A' as c ,0 as d FROM dml_union_r INTERSECT ALL SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s)foo;
 count 
-------
   115
(1 row)

INSERT INTO dml_union_s (SELECT dml_union_r.a, dml_union_r.b,'A' as c ,0 as d FROM dml_union_r INTERSECT ALL SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   230
(1 row)

rollback;
-- @description union_test7: INTERSECT with DISTINCT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT distinct a,b,c,d FROM dml_union_r INTERSECT SELECT distinct a,b,c,d FROM dml_union_s)foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT distinct a,b,c,d FROM dml_union_r INTERSECT SELECT distinct a,b,c,d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test8: INTERSECT with DISTINCT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT distinct a,b,c,d FROM dml_union_r INTERSECT ALL SELECT distinct a,b,c,d FROM dml_union_s)foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT distinct a,b,c,d FROM dml_union_r INTERSECT ALL SELECT distinct a,b,c,d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test9:  INSERT and EXCEPT operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r EXCEPT SELECT * FROM dml_union_s)foo;
 count 
-------
   101
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r EXCEPT SELECT * FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   221
(1 row)

rollback;
-- @description union_test10:  INSERT and EXCEPT operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM dml_union_r EXCEPT ALL SELECT dml_union_s.* FROM dml_union_s)foo;
 count 
-------
   105
(1 row)

INSERT INTO dml_union_r SELECT * FROM dml_union_r EXCEPT ALL SELECT dml_union_s.* FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   225
(1 row)

rollback;
-- @description union_test12: EXCEPT with generate_series
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT generate_series(1,10) EXCEPT ALL SELECT generate_series(1,10))foo;
 count 
-------
     0
(1 row)

INSERT INTO dml_union_r SELECT generate_series(1,10) EXCEPT ALL SELECT generate_series(1,10);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_test13: EXCEPT with predicate
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT * FROM dml_union_r EXCEPT ALL SELECT * FROM dml_union_s) foo WHERE c='text')bar;
 count 
-------
     5
(1 row)

INSERT INTO dml_union_r SELECT * FROM (SELECT * FROM dml_union_r EXCEPT ALL SELECT * FROM dml_union_s) foo WHERE c='text';
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   125
(1 row)

rollback;
-- @description union_test14: EXCEPT with predicate (returns 0 rows)
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT * FROM dml_union_r EXCEPT SELECT * FROM dml_union_s) foo WHERE c='s')bar;
 count 
-------
     0
(1 row)

INSERT INTO dml_union_r SELECT * FROM (SELECT * FROM dml_union_r EXCEPT SELECT * FROM dml_union_s) foo WHERE c='s';
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_test15: EXCEPT with constants
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b,'A' as c ,0 as d FROM dml_union_r EXCEPT ALL SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s)foo;
 count 
-------
     5
(1 row)

INSERT INTO dml_union_r (SELECT dml_union_r.a, dml_union_r.b,'A' as c ,0 as d FROM dml_union_r EXCEPT ALL SELECT dml_union_s.a, dml_union_s.b,'A' as C,0 as d FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   125
(1 row)

rollback;
-- @description union_test16: EXCEPT with distinct
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT distinct a,b,c,d FROM dml_union_r EXCEPT SELECT distinct a,b,c,d FROM dml_union_s)foo;
 count 
-------
   101
(1 row)

INSERT INTO dml_union_r SELECT distinct a,b,c,d FROM dml_union_r EXCEPT SELECT distinct a,b,c,d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   221
(1 row)

rollback;
-- @description union_test17: EXCEPT with distinct
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT distinct a,b,c,d FROM dml_union_r EXCEPT ALL SELECT distinct a,b,c,d FROM dml_union_s)foo;
 count 
-------
   101
(1 row)

INSERT INTO dml_union_r SELECT distinct a,b,c,d FROM dml_union_r EXCEPT ALL SELECT distinct a,b,c,d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   221
(1 row)

rollback;
-- @description union_test18: INSERT and UNION operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r UNION SELECT dml_union_s.* FROM dml_union_s)foo;
 count 
-------
   211
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.a, dml_union_r.b, dml_union_r.c, dml_union_r.d FROM dml_union_r UNION SELECT dml_union_s.* FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   331
(1 row)

rollback;
-- @description union_test19: INSERT and UNION operation
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.* FROM dml_union_r UNION All SELECT * FROM dml_union_s)foo;
 count 
-------
   235
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.* FROM dml_union_r UNION All SELECT * FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   355
(1 row)

rollback;
-- @description union_test20: UNION with generate_series
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT generate_series(1,10) UNION SELECT generate_series(1,10))foo;
 count 
-------
    10
(1 row)

INSERT INTO dml_union_r SELECT generate_series(1,10) UNION SELECT generate_series(1,10);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test21: UNION with generate_series
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT generate_series(1,10) UNION ALL SELECT generate_series(1,10))foo;
 count 
-------
    20
(1 row)

INSERT INTO dml_union_r SELECT generate_series(1,10) UNION ALL SELECT generate_series(1,10);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   140
(1 row)

rollback;
-- @description union_test22: UNION with limit
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM dml_union_r UNION ALL SELECT * FROM dml_union_s ORDER BY 1,2,3,4) foo LIMIT 10;
 count 
-------
   235
(1 row)

SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

INSERT INTO dml_union_r SELECT * FROM (SELECT * FROM dml_union_r UNION ALL SELECT * FROM dml_union_s ORDER BY 1,2,3,4) foo LIMIT 10;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   130
(1 row)

rollback;
-- @description union_test23: UNION with dml_union_sub-query in SELECT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT NULL,(SELECT NULL f1 FROM dml_union_r UNION SELECT NULL f1 FROM dml_union_s)::int, 'nullval',NULL)foo;
 count 
-------
     1
(1 row)

INSERT INTO dml_union_r SELECT NULL,(SELECT NULL f1 FROM dml_union_r UNION SELECT NULL f1 FROM dml_union_s)::int, 'nullval',NULL;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   121
(1 row)

rollback;
-- @description union_test24: UNION with exists
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT AVG(a),10,'avg',10 FROM dml_union_r WHERE exists (SELECT a FROM dml_union_r UNION ALL SELECT b FROM dml_union_s))foo;
 count 
-------
     1
(1 row)

INSERT INTO dml_union_r SELECT AVG(a),10,'avg',10 FROM dml_union_r WHERE exists (SELECT a FROM dml_union_r UNION ALL SELECT b FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   121
(1 row)

rollback;
-- @description union_test25: UNION with DISTINCT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT distinct a,b,c,d FROM dml_union_r UNION SELECT distinct a,b,c,d FROM dml_union_s)foo;
 count 
-------
   211
(1 row)

INSERT INTO dml_union_r SELECT distinct a,b,c,d FROM dml_union_r UNION SELECT distinct a,b,c,d FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   331
(1 row)

rollback;
-- @description union_test26: UNION with AGGREGATE
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT AVG(a) as a FROM dml_union_r UNION SELECT AVG(b) as a FROM dml_union_s) foo)bar;
 count 
-------
     2
(1 row)

INSERT INTO dml_union_r SELECT * FROM (SELECT AVG(a) as a FROM dml_union_r UNION SELECT AVG(b) as a FROM dml_union_s) foo;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   122
(1 row)

rollback;
-- @description union_test27: Negative tests VIOLATES NULL VALUE CONSTRAINT
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM ( SELECT * FROM (SELECT * FROM dml_union_r EXCEPT SELECT * FROM dml_union_s ) foo WHERE c='text')bar;
 count 
-------
     1
(1 row)

INSERT INTO dml_union_s SELECT * FROM (SELECT * FROM dml_union_r EXCEPT SELECT * FROM dml_union_s) foo WHERE c='text';
ERROR:  null value in column "b" violates not-null constraint  (seg1 172.17.0.2:25433 pid=278765)
DETAIL:  Failing row contains (null, null, text, null).
--SELECT COUNT(*) FROM dml_union_r;
-- @description union_test28: Negative tests MORE THAN ONE ROW RETURNED
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

INSERT INTO dml_union_r SELECT (SELECT dml_union_r.d::int FROM dml_union_r INTERSECT SELECT dml_union_s.d FROM dml_union_s ORDER BY 1),1,'newval',1.000;
ERROR:  more than one row returned by a subquery used as an expression
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

-- @description union_test29: INSERT NON ATOMICS with union/intersect/except
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT dml_union_r.* FROM dml_union_r INTERSECT (SELECT dml_union_r.* FROM dml_union_r UNION ALL SELECT dml_union_s.* FROM dml_union_s) EXCEPT SELECT dml_union_s.* FROM dml_union_s)foo;
 count 
-------
   101
(1 row)

INSERT INTO dml_union_r SELECT dml_union_r.* FROM dml_union_r INTERSECT (SELECT dml_union_r.* FROM dml_union_r UNION ALL SELECT dml_union_s.* FROM dml_union_s) EXCEPT SELECT dml_union_s.* FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   221
(1 row)

rollback;
-- @description union_test30: INSERT NON ATOMICS with union/intersect/except
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT c,c+1,case when c = 1 then 'r' else 's' end,c+1 FROM (    SELECT COUNT(distinct c) c FROM (SELECT f1, f2 , COUNT(*) c FROM (SELECT 10 f1, 'r' f2 FROM dml_union_r UNION SELECT 40 f1, 's' f2 FROM dml_union_r UNION SELECT a, c FROM dml_union_r INTERSECT SELECT a, c FROM dml_union_s ORDER BY 1) foo group by f1,f2) foo)foo)bar;
 count 
-------
     1
(1 row)

INSERT INTO dml_union_r SELECT c,c+1,case when c = 1 then 'r' else 's' end,c+1 FROM (SELECT COUNT(distinct c) c FROM (SELECT f1, f2 , COUNT(*) c FROM (SELECT 10 f1, 'r' f2 FROM dml_union_r UNION SELECT 40 f1, 's' f2 FROM dml_union_r UNION SELECT a, c FROM dml_union_r INTERSECT SELECT a, c FROM dml_union_s ORDER BY 1) foo group by f1,f2) foo)foo;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   121
(1 row)

rollback;
-- @description union_test31: INSERT NON ATOMICS with union/intersect/except
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM dml_union_r WHERE a in (SELECT dml_union_r.d::int FROM dml_union_r INTERSECT SELECT dml_union_s.d FROM dml_union_s ORDER BY 1) UNION SELECT * FROM dml_union_s)bar;
 count 
-------
   117
(1 row)

INSERT INTO dml_union_r SELECT * FROM dml_union_r WHERE a in (SELECT dml_union_r.d::int FROM dml_union_r INTERSECT SELECT dml_union_s.d FROM dml_union_s ORDER BY 1) UNION SELECT * FROM dml_union_s;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   237
(1 row)

rollback;
-- @description union_delete_test1:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test2:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test3:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r INTERSECT SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test4:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r INTERSECT ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test5:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r EXCEPT SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test6:  With UNION/INTERSECT/EXCEPT in dml_union_subquery
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a in (SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test7:  With UNION/INTERSECT/EXCEPT in the predicate condition ( 0 rows)
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT NULL UNION SELECT NULL)::int;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test8:  With UNION/INTERSECT/EXCEPT in the predicate condition ( 0 rows )
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT NULL INTERSECT SELECT NULL)::int;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test9:  With UNION/INTERSECT/EXCEPT in the predicate condition( 0 rows )
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT NULL EXCEPT SELECT NULL)::int;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test10:  With UNION/INTERSECT/EXCEPT in the predicate condition
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s ORDER BY a LIMIT 1);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   116
(1 row)

rollback;
-- @description union_delete_test11:  With UNION/INTERSECT/EXCEPT in the predicate condition
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT a FROM dml_union_r INTERSECT SELECT a FROM dml_union_s ORDER BY a LIMIT 1);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   116
(1 row)

rollback;
-- @description union_delete_test12:  With UNION/INTERSECT/EXCEPT in the predicate condition
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r WHERE a = (SELECT a FROM dml_union_r EXCEPT SELECT a FROM dml_union_s ORDER BY a LIMIT 1);
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test13:  With UNION/INTERSECT/EXCEPT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r USING (SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s) foo WHERE foo.a = dml_union_r.a;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test14:  With UNION/INTERSECT/EXCEPT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r USING (SELECT a FROM dml_union_r INTERSECT SELECT a FROM dml_union_s) foo WHERE foo.a = dml_union_r.a;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
     5
(1 row)

rollback;
-- @description union_delete_test15:  With UNION/INTERSECT/EXCEPT
begin;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

DELETE FROM dml_union_r USING (SELECT a FROM dml_union_r EXCEPT SELECT a FROM dml_union_s) foo WHERE foo.a = dml_union_r.a;
SELECT COUNT(*) FROM dml_union_r;
 count 
-------
   120
(1 row)

rollback;
-- @description union_delete_test16:  Using Partition table
begin;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   115
(1 row)

DELETE FROM dml_union_s USING (SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s_1_prt_def) foo;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
     0
(1 row)

rollback;
-- @description union_delete_test17:  Using Partition table
begin;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
   115
(1 row)

DELETE FROM dml_union_s USING (SELECT * FROM dml_union_r UNION SELECT * FROM dml_union_s_1_prt_def) foo WHERE foo.d = dml_union_s.d;
SELECT COUNT(*) FROM dml_union_s;
 count 
-------
    93
(1 row)

rollback;
-- @description union_update_test1: Update distribution column with UNION
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s ORDER BY 1 LIMIT 1;
 a 
---
 1
(1 row)

UPDATE dml_union_r SET a = (SELECT a FROM dml_union_r UNION SELECT a FROM dml_union_s ORDER BY 1 LIMIT 1);
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
     1
(1 row)

rollback;
-- @description union_update_test2: Update distribution column with UNION
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = (SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s ORDER BY 1 LIMIT 1);
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
     1
(1 row)

SELECT DISTINCT(a) FROM dml_union_r;
 a 
---
 1
(1 row)

rollback;
-- @description union_update_test3: Update distribution column with INTERSECT
begin;
SELECT COUNT(*) FROM dml_union_r WHERE a = 1;
 count 
-------
     4
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT a FROM dml_union_r order by a limit 1) foo INTERSECT SELECT a FROM dml_union_s)bar;
 count 
-------
     1
(1 row)

UPDATE dml_union_r SET a = ( SELECT * FROM (SELECT a FROM dml_union_r order by a limit 1) foo INTERSECT SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE a = 1;
 count 
-------
   120
(1 row)

rollback;
-- @description union_update_test4: Update distribution column with INTERSECT
begin;
SELECT COUNT(*) FROM dml_union_r WHERE a = 1;
 count 
-------
     4
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT a FROM dml_union_r ORDER BY 1 limit 1) foo INTERSECT ALL SELECT a FROM dml_union_s)bar; 
 count 
-------
     1
(1 row)

UPDATE dml_union_r SET a = ( SELECT * FROM (SELECT a FROM dml_union_r ORDER BY 1 limit 1) foo INTERSECT ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE a = 1;
 count 
-------
   120
(1 row)

rollback;
-- @description union_update_test5: Update distribution column with EXCEPT
begin;
SELECT SUM(a) FROM dml_union_r;
 sum  
------
 5095
(1 row)

SELECT COUNT(*) FROM (SELECT * FROM (SELECT a FROM dml_union_r limit 1) foo EXCEPT SELECT a FROM dml_union_s)bar;
 count 
-------
     0
(1 row)

UPDATE dml_union_r SET a = ( SELECT * FROM (SELECT a FROM dml_union_r limit 1) foo EXCEPT SELECT a FROM dml_union_s);
SELECT SUM(a) FROM dml_union_r;
 sum 
-----
    
(1 row)

rollback;
-- @description union_update_test6: Update distribution column with EXCEPT
begin;
UPDATE dml_union_r SET a = ( SELECT * FROM (SELECT a FROM dml_union_r limit 1) foo EXCEPT ALL SELECT a FROM dml_union_s);
SELECT DISTINCT(a) FROM dml_union_r;
 a 
---
  
(1 row)

rollback;
-- @description union_update_test7: NULL values to distribution key
begin;
UPDATE dml_union_r SET a = (SELECT NULL UNION SELECT NULL)::int;
SELECT DISTINCT(a) FROM dml_union_r;
 a 
---
  
(1 row)

rollback;
-- @description union_update_test8: NULL values to distribution key
begin;
UPDATE dml_union_r SET a = (SELECT NULL INTERSECT SELECT NULL)::int;
SELECT DISTINCT(a) FROM dml_union_r;
 a 
---
  
(1 row)

rollback;
-- @description union_update_test9: NULL values to distribution key
begin;
UPDATE dml_union_r SET a = (SELECT NULL INTERSECT ALL SELECT NULL)::int;
SELECT DISTINCT(a) FROM dml_union_r;
 a 
---
  
(1 row)

rollback;
-- @description union_update_test10: NULL values to distribution key
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r; 
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = (SELECT NULL EXCEPT SELECT NULL)::int;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r; 
 count 
-------
     0
(1 row)

rollback;
-- @description union_update_test11: NULL values to text
begin;
SELECT COUNT(DISTINCT(c)) FROM dml_union_r; 
 count 
-------
     2
(1 row)

UPDATE dml_union_r SET c = (SELECT NULL EXCEPT ALL SELECT NULL);
SELECT COUNT(DISTINCT(c)) FROM dml_union_r; 
 count 
-------
     0
(1 row)

rollback;
-- @description union_update_test12: Update partition key to NULL values when default partition present
begin;
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL UNION SELECT NULL)::numeric;
ERROR:  moving tuple from partition "dml_union_s_1_prt_2" to partition "dml_union_s_1_prt_def" not supported
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test13: Update partition key to NULL values when default partition present
begin;
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL INTERSECT SELECT NULL)::numeric;
ERROR:  moving tuple from partition "dml_union_s_1_prt_2" to partition "dml_union_s_1_prt_def" not supported
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test14: Update partition key to NULL values when default partition present
begin;
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL INTERSECT ALL SELECT NULL)::numeric;
ERROR:  moving tuple from partition "dml_union_s_1_prt_2" to partition "dml_union_s_1_prt_def" not supported
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test15: Update partition key to NULL values when default partition present
begin;
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL EXCEPT SELECT NULL)::numeric;
ERROR:  moving tuple from partition "dml_union_s_1_prt_2" to partition "dml_union_s_1_prt_def" not supported
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test16: Update partition key to NULL values when default partition present
begin;
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL EXCEPT ALL SELECT NULL)::numeric;
ERROR:  moving tuple from partition "dml_union_s_1_prt_2" to partition "dml_union_s_1_prt_def" not supported
SELECT COUNT(DISTINCT(d)) FROM dml_union_s; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test17: Update partition key to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM (SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s)foo;
 count 
-------
   235
(1 row)

UPDATE dml_union_r SET d = 20000 WHERE a in (SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
   115
(1 row)

rollback;
-- @description union_update_test18: Update partition key to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM (SELECT a FROM dml_union_r INTERSECT ALL SELECT a FROM dml_union_s)foo;
 count 
-------
   115
(1 row)

UPDATE dml_union_r SET d = 20000 WHERE a in (SELECT a FROM dml_union_r INTERSECT ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
   115
(1 row)

rollback;
-- @description union_update_test19: Update partition key to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM (SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s)foo;
 count 
-------
     5
(1 row)

UPDATE dml_union_r SET d = 20000 WHERE a in (SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE d = 20000; 
 count 
-------
     0
(1 row)

rollback;
-- @description union_update_test20:  UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = dml_union_s.a FROM dml_union_s WHERE dml_union_r.b in (SELECT b FROM dml_union_r UNION SELECT b FROM dml_union_s);
ERROR:  multiple updates to a row by the same query is not allowed  (seg2 10.152.10.75:25434 pid=18097)
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
rollback;
-- @description union_update_test21:  UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = dml_union_r.a WHERE b in (SELECT b FROM dml_union_r INTERSECT SELECT b FROM dml_union_s);
SELECT DISTINCT(a) FROM dml_union_r;
  a  
-----
  49
  35
  30
  13
  37
  33
  72
  17
   1
  48
  51
  31
  86
  34
  70
   2
  71
  15
  69
  36
  29
  28
  63
  16
  83
  52
  85
  68
  50
  14
  84
  20
  40
   5
  91
  56
  42
  19
  18
  57
  54
  22
  89
  75
  53
   6
  39
   7
  77
  76
  41
  38
  96
  90
  21
   4
  73
  97
    
   3
  88
  55
  87
  74
  23
  78
  24
  67
  66
  11
  10
  60
  45
  27
 100
  12
  80
  79
  46
  44
  82
  59
  62
  47
  65
  92
  61
   8
  43
  95
  93
  64
  94
  26
  58
  25
  99
  98
   9
  81
  32
(101 rows)

SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

rollback;
-- @description union_update_test22:  UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = dml_union_r.a WHERE b in (SELECT b FROM dml_union_r EXCEPT SELECT b FROM dml_union_s);
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT DISTINCT(a) FROM dml_union_r;
  a  
-----
  23
  78
  24
  67
  66
  11
  10
  60
  45
  27
 100
  12
  80
  79
  46
  44
  82
  59
  62
  47
  65
  92
  61
   8
  43
  95
  93
  64
  94
  26
  58
  25
  99
  98
   9
  81
  32
  49
  35
  30
  13
  37
  33
  72
  17
   1
  48
  51
  31
  86
  34
  70
   2
  71
  15
  69
  36
  29
  28
  63
  16
  83
  52
  85
  68
  50
  14
  84
  20
  40
   5
  91
  56
  42
  19
  18
  57
  54
  22
  89
  75
  53
   6
  39
   7
  77
  76
  41
  38
  96
  90
  21
   4
  73
  97
    
   3
  88
  55
  87
  74
(101 rows)

rollback;
-- @description union_update_test23: Update distribution column to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
     0
(1 row)

UPDATE dml_union_r SET a = 0 WHERE a in (SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s);
SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
   115
(1 row)

SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
     1
(1 row)

rollback;
-- @description union_update_test24: Update distribution column to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
     0
(1 row)

UPDATE dml_union_r SET a = 0 WHERE a in (SELECT a FROM dml_union_r INTERSECT ALL SELECT a FROM dml_union_s);
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
     1
(1 row)

SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
   115
(1 row)

rollback;
-- @description union_update_test25: Update distribution column to constant value with UNION/INTERSECT/EXCEPT within dml_union_sub-query
begin;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
     0
(1 row)

UPDATE dml_union_r SET a = 0 WHERE a in (SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

SELECT COUNT(*) FROM dml_union_r WHERE a = 0;
 count 
-------
     0
(1 row)

rollback;
-- @description union_update_test26: Negative Tests Update the partition key to an out of dml_union_range value with no default partition
begin;
ALTER TABLE dml_union_s drop default partition;
NOTICE:  dropped partition "def" for relation "dml_union_s"
SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL UNION SELECT NULL)::numeric;
ERROR:  no partition for partitioning key  (seg0 sdw1:35511 pid=6757)
--SELECT DISTINCT(d) FROM dml_union_s;
--SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
rollback;
-- @description union_update_test27: Negative Tests Update the partition key to an out of range value with no default partition
begin;
ALTER TABLE dml_union_s drop default partition;
NOTICE:  dropped partition "def" for relation "dml_union_s"
SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL INTERSECT SELECT NULL)::numeric; 
ERROR:  no partition for partitioning key  (seg1 sdw1:35512 pid=6983)
--SELECT DISTINCT(d) FROM dml_union_s;
--SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
rollback;
-- @description union_update_test28: Negative Tests Update the partition key to an out of dml_union_range value with no default partition
begin;
ALTER TABLE dml_union_s drop default partition;
NOTICE:  dropped partition "def" for relation "dml_union_s"
SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
 count 
-------
   100
(1 row)

UPDATE dml_union_s SET d = (SELECT NULL EXCEPT SELECT NULL)::numeric; 
ERROR:  no partition for partitioning key  (seg0 sdw1:35511 pid=7201)
--SELECT DISTINCT(d) FROM dml_union_s;
--SELECT COUNT(DISTINCT(d)) FROM dml_union_s;
rollback;
-- @description union_update_test29: Negative Tests  UPDATE violates the CHECK constraint on the column
SELECT COUNT(DISTINCT(b)) FROM dml_union_s;
 count 
-------
   104
(1 row)

UPDATE dml_union_s SET b = (SELECT NULL UNION SELECT NULL)::numeric;
ERROR:  null value in column "b" violates not-null constraint  (seg1 10.152.10.75:25433 pid=19531)
DETAIL:  Failing row contains (5, null, s, 5).
--SELECT COUNT(DISTINCT(b)) FROM dml_union_s;
--SELECT DISTINCT(b) FROM dml_union_s;
-- @description union_update_test30: Negative Tests  more than one row returned by a sub-query used as an expression
--
-- The access plan of this UPDATE is sensitive to optimizer_segments. With
-- ORCA, the error message varies depending accesss plan; you either get:
--
--   ERROR:  more than one row returned by a subquery used as an expression
--
-- like with the Postgres planner, or you get:
--
--   ERROR:  one or more assertions failed
--   DETAIL:  Expected no more than one row to be returned by expression
--
-- To make the output stable, arbitrarily fix optimizer_segments to 2, to get the latter.
set optimizer_segments=2;
SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
 count 
-------
   100
(1 row)

UPDATE dml_union_r SET a = ( SELECT a FROM dml_union_r UNION ALL SELECT a FROM dml_union_s);
ERROR:  more than one row returned by a subquery used as an expression
reset optimizer_segments;
--SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
-- @description union_update_test31: Negative Tests  more than one row returned by a sub-query used as an expression
UPDATE dml_union_r SET b = ( SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
ERROR:  more than one row returned by a subquery used as an expression
--
-- Test for creation of MergeAppend paths.
--
-- We used to have a bug in creation of MergeAppend paths, so that this failed
-- with "could not find pathkey item to sort" error.  See
-- https://github.com/greenplum-db/gpdb/issues/5695
--
create table mergeappend_test ( a int, b int, x int ) distributed by (a,b);
insert into mergeappend_test select g/100, g/100, g from generate_series(1, 500) g;
analyze mergeappend_test;
select a, b, array_dims(array_agg(x)) from mergeappend_test r group by a, b
union all
select null, null, array_dims(array_agg(x)) from mergeappend_test r
order by 1,2;
 a | b | array_dims 
---+---+------------
 0 | 0 | [1:99]
 1 | 1 | [1:100]
 2 | 2 | [1:100]
 3 | 3 | [1:100]
 4 | 4 | [1:100]
 5 | 5 | [1:1]
   |   | [1:500]
(7 rows)

-- Check that it's using a MergeAppend
explain (costs off)
select a, b, array_dims(array_agg(x)) from mergeappend_test r group by a, b
union all
select null, null, array_dims(array_agg(x)) from mergeappend_test r
order by 1,2;
                         QUERY PLAN                         
------------------------------------------------------------
 Merge Append
   Sort Key: r.a, r.b
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Merge Key: r.a, r.b
         ->  GroupAggregate
               Group Key: r.a, r.b
               ->  Sort
                     Sort Key: r.a, r.b
                     ->  Seq Scan on mergeappend_test r
   ->  Sort
         Sort Key: (NULL::integer), (NULL::integer)
         ->  Aggregate
               ->  Gather Motion 3:1  (slice2; segments: 3)
                     ->  Seq Scan on mergeappend_test r_1
 Optimizer: Postgres query optimizer
(15 rows)

-- This used to trip an assertion in MotionStateFinderWalker(), when we were
-- missing support for MergeAppend in planstate_walk_kids().
-- (https://github.com/greenplum-db/gpdb/issues/6668)
select a, b, array_dims(array_agg(x)) from mergeappend_test r group by a, b
union all
select null, null, array_dims(array_agg(x)) FROM mergeappend_test r, pg_sleep(0)
order by 1,2;
 a | b | array_dims 
---+---+------------
 0 | 0 | [1:99]
 1 | 1 | [1:100]
 2 | 2 | [1:100]
 3 | 3 | [1:100]
 4 | 4 | [1:100]
 5 | 5 | [1:1]
   |   | [1:500]
(7 rows)

-- check that EXPLAIN ANALYZE works on MergeAppend, too.
explain analyze select a, b, array_dims(array_agg(x)) from mergeappend_test r group by a, b
union all
select null, null, array_dims(array_agg(x)) FROM mergeappend_test r
order by 1,2;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Merge Append  (cost=59.71..66.08 rows=37 width=40) (actual time=0.958..0.965 rows=4 loops=2)
   Sort Key: r.a, r.b
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=30.41..37.03 rows=36 width=40) (actual time=0.550..0.554 rows=3 loops=2)
         Merge Key: r.a, r.b
         ->  GroupAggregate  (cost=30.41..35.95 rows=12 width=40) (actual time=0.234..0.317 rows=2 loops=2)
               Group Key: r.a, r.b
               ->  Sort  (cost=30.41..31.66 rows=167 width=12) (actual time=0.181..0.205 rows=150 loops=2)
                     Sort Key: r.a, r.b
                     Sort Method:  quicksort  Memory: 115kB
                     ->  Seq Scan on mergeappend_test r  (cost=0.00..8.00 rows=167 width=12) (actual time=0.005..0.032 rows=150 loops=2)
   ->  Sort  (cost=29.29..29.29 rows=1 width=40) (actual time=0.407..0.409 rows=0 loops=2)
         Sort Key: (NULL::integer), (NULL::integer)
         Sort Method:  quicksort  Memory: 33kB
         ->  Aggregate  (cost=29.25..29.27 rows=1 width=40) (actual time=0.395..0.395 rows=0 loops=2)
               ->  Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..28.00 rows=500 width=4) (actual time=0.034..0.319 rows=250 loops=2)
                     ->  Seq Scan on mergeappend_test r_1  (cost=0.00..8.00 rows=167 width=4) (actual time=0.008..0.041 rows=150 loops=2)
 Planning time: 0.793 ms
   (slice0)    Executor memory: 380K bytes.  Work_mem: 33K bytes max.
   (slice1)    Executor memory: 121K bytes avg x 3 workers, 140K bytes max (seg0).  Work_mem: 49K bytes max.
   (slice2)    Executor memory: 42K bytes avg x 3 workers, 42K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution time: 2.496 ms
(23 rows)