| -- 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); |
| CREATE TABLE dml_union_s_1_prt_2 PARTITION OF dml_union_s FOR VALUES FROM (1) TO (1001); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_3 PARTITION OF dml_union_s FOR VALUES FROM (1001) TO (1101); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_4 PARTITION OF dml_union_s FOR VALUES FROM (1101) TO (1201); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_5 PARTITION OF dml_union_s FOR VALUES FROM (1201) TO (1301); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_6 PARTITION OF dml_union_s FOR VALUES FROM (1301) TO (1401); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_7 PARTITION OF dml_union_s FOR VALUES FROM (1401) TO (1501); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_8 PARTITION OF dml_union_s FOR VALUES FROM (1501) TO (1601); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_9 PARTITION OF dml_union_s FOR VALUES FROM (1601) TO (1701); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_10 PARTITION OF dml_union_s FOR VALUES FROM (1701) TO (1801); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_11 PARTITION OF dml_union_s FOR VALUES FROM (1801) TO (1901); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE dml_union_s_1_prt_def PARTITION OF dml_union_s DEFAULT; |
| NOTICE: table has parent, setting distribution columns to match parent table |
| 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" of relation "dml_union_s_1_prt_def" violates not-null constraint |
| 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: one or more assertions failed (seg0 slice1 10.138.0.30:7002 pid=2878957) |
| DETAIL: Expected no more than one row to be returned by 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; |
| SELECT COUNT(DISTINCT(d)) FROM dml_union_s; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| 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; |
| SELECT COUNT(DISTINCT(d)) FROM dml_union_s; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| 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; |
| SELECT COUNT(DISTINCT(d)) FROM dml_union_s; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| 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; |
| SELECT COUNT(DISTINCT(d)) FROM dml_union_s; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| 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; |
| SELECT COUNT(DISTINCT(d)) FROM dml_union_s; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| 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 |
| 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 |
| ----- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
| 46 |
| 47 |
| 48 |
| 49 |
| 50 |
| 51 |
| 52 |
| 53 |
| 54 |
| 55 |
| 56 |
| 57 |
| 58 |
| 59 |
| 60 |
| 61 |
| 62 |
| 63 |
| 64 |
| 65 |
| 66 |
| 67 |
| 68 |
| 69 |
| 70 |
| 71 |
| 72 |
| 73 |
| 74 |
| 75 |
| 76 |
| 77 |
| 78 |
| 79 |
| 80 |
| 81 |
| 82 |
| 83 |
| 84 |
| 85 |
| 86 |
| 87 |
| 88 |
| 89 |
| 90 |
| 91 |
| 92 |
| 93 |
| 94 |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
| 100 |
| |
| (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 |
| ----- |
| |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
| 46 |
| 47 |
| 48 |
| 49 |
| 50 |
| 51 |
| 52 |
| 53 |
| 54 |
| 55 |
| 56 |
| 57 |
| 58 |
| 59 |
| 60 |
| 61 |
| 62 |
| 63 |
| 64 |
| 65 |
| 66 |
| 67 |
| 68 |
| 69 |
| 70 |
| 71 |
| 72 |
| 73 |
| 74 |
| 75 |
| 76 |
| 77 |
| 78 |
| 79 |
| 80 |
| 81 |
| 82 |
| 83 |
| 84 |
| 85 |
| 86 |
| 87 |
| 88 |
| 89 |
| 90 |
| 91 |
| 92 |
| 93 |
| 94 |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
| 100 |
| (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; |
| DROP TABLE dml_union_s_1_prt_def; |
| 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 of relation "dml_union_s" found for row |
| DETAIL: Partition key of the failing row contains (d) = (null). |
| --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; |
| DROP TABLE dml_union_s_1_prt_def; |
| 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 of relation "dml_union_s" found for row |
| DETAIL: Partition key of the failing row contains (d) = (null). |
| --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; |
| DROP TABLE dml_union_s_1_prt_def; |
| 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 of relation "dml_union_s" found for row |
| DETAIL: Partition key of the failing row contains (d) = (null). |
| --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" of relation "dml_union_s_1_prt_2" violates not-null constraint (seg0 127.0.1.1:9002 pid=577298) |
| DETAIL: Failing row contains (1, null, s, 1). |
| --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; |
| ANALYZE dml_union_r, dml_union_s; |
| 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 (seg2 slice1 10.138.0.30:7004 pid=3108610) |
| 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: one or more assertions failed (seg2 10.138.0.30:7004 pid=2878859) |
| DETAIL: Expected no more than one row to be returned by expression |
| -- |
| -- Test mixing a set-returning function, which can be evaluated anywhere, |
| -- (it has General locus) and a diststributed table, in an Append. |
| -- |
| explain (costs off) |
| select a from dml_union_r where a > 95 |
| union all |
| select g from generate_series(1,2) g; |
| QUERY PLAN |
| ------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on dml_union_r |
| Filter: (a > 95) |
| -> Result |
| One-Time Filter: (gp_execution_segment() = 0) |
| -> Function Scan on generate_series |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (8 rows) |
| |
| select a from dml_union_r where a > 95 |
| union all |
| select g from generate_series(1,2) g; |
| a |
| ----- |
| 98 |
| 1 |
| 2 |
| 96 |
| 100 |
| 97 |
| 99 |
| (7 rows) |
| |
| explain (costs off) |
| select sum(a) from ( |
| select a from dml_union_r where a > 95 |
| union all |
| select g from generate_series(1,2) g |
| ) t; |
| QUERY PLAN |
| ------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Append |
| -> Seq Scan on dml_union_r |
| Filter: (a > 95) |
| -> Result |
| One-Time Filter: (gp_execution_segment() = 2) |
| -> Function Scan on generate_series |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select sum(a) from ( |
| select a from dml_union_r where a > 95 |
| union all |
| select g from generate_series(1,2) g |
| ) t; |
| sum |
| ----- |
| 493 |
| (1 row) |
| |
| -- |
| -- Continue to test appending General to distributed table. |
| -- This time, the General is a dummy path, produced by pushing down condition. |
| -- (Only for planner, orca does not create dummy path here) |
| -- |
| create table t_test_append_hash(a int, b int, c int) distributed by (a); |
| insert into t_test_append_hash select i, i+1, i+2 from generate_series(1, 5)i; |
| explain (costs off) |
| with t(a, b, s) as ( |
| select a, b, sum(c) from t_test_append_hash where a > b group by a, b |
| union all |
| select a, b, sum(c) from t_test_append_hash where a < b group by a, b |
| ) select * from t where t.a < t.b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> GroupAggregate |
| Group Key: t_test_append_hash.a, t_test_append_hash.b |
| -> Sort |
| Sort Key: t_test_append_hash.a, t_test_append_hash.b |
| -> Seq Scan on t_test_append_hash |
| Filter: ((a > b) AND (a < b)) |
| -> GroupAggregate |
| Group Key: t_test_append_hash_1.a, t_test_append_hash_1.b |
| -> Sort |
| Sort Key: t_test_append_hash_1.a, t_test_append_hash_1.b |
| -> Seq Scan on t_test_append_hash t_test_append_hash_1 |
| Filter: ((a < b) AND (a < b)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (15 rows) |
| |
| with t(a, b, s) as ( |
| select a, b, sum(c) from t_test_append_hash where a > b group by a, b |
| union all |
| select a, b, sum(c) from t_test_append_hash where a < b group by a, b |
| ) select * from t where t.a < t.b; |
| a | b | s |
| ---+---+--- |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
| 4 | 5 | 6 |
| 5 | 6 | 7 |
| 1 | 2 | 3 |
| (5 rows) |
| |
| -- Test mixing a SegmentGeneral with distributed table. |
| create table t_test_append_rep(a int, b int, c int) distributed replicated; |
| insert into t_test_append_rep select i, i+1, i+2 from generate_series(5, 10)i; |
| explain (costs off) |
| select * from t_test_append_rep |
| union all |
| select * from t_test_append_hash; |
| QUERY PLAN |
| ------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Result |
| One-Time Filter: (gp_execution_segment() = 0) |
| -> Seq Scan on t_test_append_rep |
| -> Seq Scan on t_test_append_hash |
| Optimizer: GPORCA |
| (7 rows) |
| |
| select * from t_test_append_rep |
| union all |
| select * from t_test_append_hash; |
| a | b | c |
| ----+----+---- |
| 5 | 6 | 7 |
| 6 | 7 | 8 |
| 7 | 8 | 9 |
| 8 | 9 | 10 |
| 9 | 10 | 11 |
| 10 | 11 | 12 |
| 1 | 2 | 3 |
| 5 | 6 | 7 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
| 4 | 5 | 6 |
| (11 rows) |
| |
| -- Test value scan union all with a distributed table that direct dispatch |
| -- value scan's locus is general, so it will use Result plan node with |
| -- resconstantqual to be gp_execution_segment() = <some segid> to turn |
| -- general locus to partitioned locus to avoid gather partitioned locus |
| -- table to singleQE. When the subplan of partitioned table's scan can |
| -- use direct dispatch, previously, the result plan does not handle |
| -- direct dispatch correctly. This case cannot test plan, this is because |
| -- gp_execution_segment() = <some segid> the filter segid is randomly picked. |
| -- So the result plan's direct dispatch info is also random. We print the plan |
| -- and ignore it for better debugging info if error happens. |
| -- See github issue https://github.com/greenplum-db/gpdb/issues/9874 for details. |
| create table t_github_issue_9874 (a int) distributed by (a); |
| -- start_ignore |
| explain (costs off) |
| select 1 |
| union all |
| select * from t_github_issue_9874 where a = 1; |
| QUERY PLAN |
| ------------------------------------------------ |
| Append |
| -> Result |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_github_issue_9874 |
| Filter: (a = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 rows) |
| |
| -- end_ignore |
| select 1 |
| union all |
| select * from t_github_issue_9874 where a = 1; |
| ?column? |
| ---------- |
| 1 |
| (1 row) |
| |
| -- |
| -- Test mixing a SegmentGeneral with distributed table |
| -- when gp_enable_direct_dispatch is off. |
| -- |
| begin; |
| create table rt1(a int, b int) distributed replicated; |
| create table t1(a int, b int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t1 select i, i+1 from generate_series(6, 9) i; |
| insert into rt1 select i, i+1 from generate_series(1, 5) i; |
| set local gp_enable_direct_dispatch = on; |
| explain(costs off) select * from rt1 union all select * from t1; |
| QUERY PLAN |
| ------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Result |
| One-Time Filter: (gp_execution_segment() = 0) |
| -> Seq Scan on rt1 |
| -> Seq Scan on t1 |
| Optimizer: GPORCA |
| (7 rows) |
| |
| select * from rt1 union all select * from t1; |
| a | b |
| ---+---- |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 9 | 10 |
| 7 | 8 |
| 8 | 9 |
| (9 rows) |
| |
| set local gp_enable_direct_dispatch = off; |
| select * from rt1 union all select * from t1; |
| a | b |
| ---+---- |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 9 | 10 |
| 7 | 8 |
| 8 | 9 |
| (9 rows) |
| |
| reset gp_enable_direct_dispatch; |
| abort; |
| -- Test mixing a SegmentGeneral with General locus scan. |
| explain (costs off) |
| select a from t_test_append_rep |
| union all |
| select * from generate_series(100, 105); |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Append |
| -> Seq Scan on t_test_append_rep |
| -> Function Scan on generate_series |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select a from t_test_append_rep |
| union all |
| select * from generate_series(100, 105); |
| a |
| ----- |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| (12 rows) |
| |
| -- test INTERSECT/EXCEPT with General and partitioned locus, but none of the columns are hashable |
| CREATE TABLE p1(a int) distributed by (a); |
| INSERT INTO p1 select generate_series(1,10); |
| explain (costs off) |
| select from generate_series(1,5) intersect select from p1; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Nested Loop |
| Join Filter: true |
| -> Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on p1 |
| -> Aggregate |
| -> Function Scan on generate_series |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (8 rows) |
| |
| select from generate_series(1,5) intersect select from p1; |
| -- |
| (1 row) |
| |
| explain (costs off) |
| select from generate_series(1,5) except select from p1; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Nested Loop Anti Join |
| Join Filter: true |
| -> Function Scan on generate_series |
| -> Materialize |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on p1 |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 rows) |
| |
| select from generate_series(1,5) except select from p1; |
| -- |
| (0 rows) |
| |
| -- |
| -- 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 |
| set enable_hashagg=off; |
| 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 |
| ------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: r.a, r.b |
| -> Sort |
| Sort Key: r.a, r.b |
| -> Append |
| -> HashAggregate |
| Group Key: r.a, r.b |
| -> Seq Scan on mergeappend_test r |
| -> Result |
| -> Redistribute Motion 1:3 (slice2) |
| -> Aggregate |
| -> Gather Motion 3:1 (slice3; segments: 3) |
| -> Seq Scan on mergeappend_test r_1 |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 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 |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.06 rows=22 width=16) (actual time=2.289..2.291 rows=7 loops=1) |
| Merge Key: mergeappend_test.a, mergeappend_test.b |
| -> Sort (cost=0.00..862.06 rows=8 width=16) (actual time=1.633..1.637 rows=5 loops=1) |
| Sort Key: mergeappend_test.a, mergeappend_test.b |
| Sort Method: quicksort Memory: 150kB |
| -> Append (cost=0.00..862.06 rows=8 width=16) (actual time=0.482..1.625 rows=5 loops=1) |
| -> HashAggregate (cost=0.00..431.05 rows=7 width=16) (actual time=0.482..0.489 rows=4 loops=1) |
| Group Key: mergeappend_test.a, mergeappend_test.b |
| Peak Memory Usage: 0 kB |
| -> Seq Scan on mergeappend_test r (cost=0.00..431.00 rows=167 width=12) (actual time=0.251..0.289 rows=301 loops=1) |
| -> Result (cost=0.00..431.01 rows=1 width=16) (actual time=1.133..1.134 rows=1 loops=1) |
| -> Redistribute Motion 1:3 (slice2) (cost=0.00..431.01 rows=1 width=8) (actual time=1.129..1.130 rows=1 loops=1) |
| -> Aggregate (cost=0.00..431.01 rows=1 width=8) (actual time=1.193..1.194 rows=1 loops=1) |
| -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.01 rows=500 width=4) (actual time=0.311..1.098 rows=500 loops=1) |
| -> Seq Scan on mergeappend_test r_1 (cost=0.00..431.00 rows=167 width=4) (actual time=0.071..0.146 rows=301 loops=1) |
| Planning Time: 51.388 ms |
| (slice0) Executor memory: 56K bytes. |
| (slice1) Executor memory: 41K bytes avg x 3 workers, 42K bytes max (seg0). Work_mem: 26K bytes max. |
| (slice2) Executor memory: 58K bytes (entry db). |
| (slice3) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). |
| Memory used: 128000kB |
| Optimizer: Pivotal Optimizer (GPORCA) |
| Execution Time: 2.977 ms |
| (23 rows) |
| |
| CREATE TABLE t1(c1 int, c2 int, c3 int); |
| CREATE TABLE t2(c1 int, c2 int, c3 int); |
| INSERT INTO t1 SELECT i, i ,i + 1 FROM generate_series(1,10) i; |
| INSERT INTO t2 SELECT i, i ,i + 1 FROM generate_series(1,10) i; |
| SET enable_hashagg = off; |
| with tcte(c1, c2, c3) as ( |
| SELECT c1, sum(c2) as c2, c3 FROM t1 WHERE c3 > 0 GROUP BY c1, c3 |
| UNION ALL |
| SELECT c1, sum(c2) as c2, c3 FROM t2 WHERE c3 < 0 GROUP BY c1, c3 |
| ) |
| SELECT * FROM tcte WHERE c3 = 1; |
| c1 | c2 | c3 |
| ----+----+---- |
| (0 rows) |
| |
