This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 7f919d894cda3cca712f9fd1868aff7303eeb84a Author: zhoujiaqi <[email protected]> AuthorDate: Wed Dec 25 15:43:07 2024 +0800 Fix icw test "Derive Combined Hashed Spec For Outer Joins" - Fixed GPDB incorrect results in bfv_join.sql - Fixed some plan diff --- src/test/regress/expected/bfv_joins.out | 2 + src/test/regress/expected/bfv_joins_optimizer.out | 54 +- src/test/regress/expected/generated_optimizer.out | 541 ++++++++++++++++++--- .../regress/expected/partition_prune_optimizer.out | 4 +- src/test/regress/expected/rpt.out | 7 +- src/test/regress/expected/rpt_optimizer.out | 7 +- src/test/regress/expected/tidscan_optimizer.out | 38 +- src/test/regress/expected/tsrf_optimizer.out | 4 +- src/test/regress/expected/union_optimizer.out | 28 ++ src/test/regress/expected/update_gp.out | 20 +- src/test/regress/expected/update_gp_optimizer.out | 78 ++- .../regress/expected/with_clause_optimizer.out | 11 +- src/test/regress/sql/bfv_joins.sql | 2 + src/test/regress/sql/rpt.sql | 3 + 14 files changed, 632 insertions(+), 167 deletions(-) diff --git a/src/test/regress/expected/bfv_joins.out b/src/test/regress/expected/bfv_joins.out index 622d2091ec..03b897177a 100644 --- a/src/test/regress/expected/bfv_joins.out +++ b/src/test/regress/expected/bfv_joins.out @@ -3686,6 +3686,8 @@ ERROR: table "o2" does not exist drop table o3; ERROR: table "o3" does not exist --end_ignore +-- Current case add in Derive Combined Hashed Spec For Outer Joins (#14045), f8264ad +-- GPDB will got wrong result in ORCA plan, util merged [FIXME] Wrong results on main branch for INDF query,ce25faf create table o1 (a1 int, b1 int) distributed by (a1); create table o2 (a2 int, b2 int) distributed by (a2); create table o3 (a3 int, b3 int) distributed by (a3); diff --git a/src/test/regress/expected/bfv_joins_optimizer.out b/src/test/regress/expected/bfv_joins_optimizer.out index 1f0210399e..f631eff520 100644 --- a/src/test/regress/expected/bfv_joins_optimizer.out +++ b/src/test/regress/expected/bfv_joins_optimizer.out @@ -3700,6 +3700,8 @@ ERROR: table "o2" does not exist drop table o3; ERROR: table "o3" does not exist --end_ignore +-- Current case add in Derive Combined Hashed Spec For Outer Joins (#14045), f8264ad +-- GPDB will got wrong result in ORCA plan, util merged [FIXME] Wrong results on main branch for INDF query,ce25faf create table o1 (a1 int, b1 int) distributed by (a1); create table o2 (a2 int, b2 int) distributed by (a2); create table o3 (a3 int, b3 int) distributed by (a3); @@ -3709,22 +3711,22 @@ insert into o3 values (NULL, 20); select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3; a1 | b1 | a2 | b2 | a3 | b3 ----+----+----+----+----+---- - 2 | 2 | | | | - 3 | 3 | | | | - 4 | 4 | | | | - 7 | 7 | | | | - 8 | 8 | | | | - 16 | 16 | 16 | | | - 18 | 18 | 18 | | | - 19 | 19 | 19 | | | - 1 | 1 | | | | + 1 | 1 | | | | 20 12 | 12 | 12 | | | 15 | 15 | 15 | | | 20 | 20 | 20 | | | - 5 | 5 | | | | - 6 | 6 | | | | - 9 | 9 | | | | - 10 | 10 | | | | + 2 | 2 | | | | 20 + 3 | 3 | | | | 20 + 4 | 4 | | | | 20 + 7 | 7 | | | | 20 + 8 | 8 | | | | 20 + 16 | 16 | 16 | | | + 18 | 18 | 18 | | | + 19 | 19 | 19 | | | + 5 | 5 | | | | 20 + 6 | 6 | | | | 20 + 9 | 9 | | | | 20 + 10 | 10 | | | | 20 11 | 11 | 11 | | | 13 | 13 | 13 | | | 14 | 14 | 14 | | | @@ -3734,26 +3736,26 @@ select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 is distinct from b3; a1 | b1 | a2 | b2 | a3 | b3 ----+----+----+----+----+---- - 2 | 2 | | | | - 3 | 3 | | | | - 4 | 4 | | | | - 7 | 7 | | | | - 8 | 8 | | | | - 16 | 16 | 16 | | | - 18 | 18 | 18 | | | - 19 | 19 | 19 | | | - 1 | 1 | | | | + 1 | 1 | | | | 20 12 | 12 | 12 | | | 15 | 15 | 15 | | | 20 | 20 | 20 | | | - 5 | 5 | | | | - 6 | 6 | | | | - 9 | 9 | | | | - 10 | 10 | | | | + 5 | 5 | | | | 20 + 6 | 6 | | | | 20 + 9 | 9 | | | | 20 + 10 | 10 | | | | 20 11 | 11 | 11 | | | 13 | 13 | 13 | | | 14 | 14 | 14 | | | 17 | 17 | 17 | | | + 2 | 2 | | | | 20 + 3 | 3 | | | | 20 + 4 | 4 | | | | 20 + 7 | 7 | | | | 20 + 8 | 8 | | | | 20 + 16 | 16 | 16 | | | + 18 | 18 | 18 | | | + 19 | 19 | 19 | | | (20 rows) select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 = b3; diff --git a/src/test/regress/expected/generated_optimizer.out b/src/test/regress/expected/generated_optimizer.out index c7a5f5f5a5..851a8fff74 100644 --- a/src/test/regress/expected/generated_optimizer.out +++ b/src/test/regress/expected/generated_optimizer.out @@ -42,9 +42,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -75,6 +73,13 @@ ERROR: cannot use generated column "b" in column generation expression LINE 1: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO... ^ DETAIL: A generated column cannot reference another generated column. +-- a whole-row var is a self-reference on steroids, so disallow that too +CREATE TABLE gtest_err_2c (a int PRIMARY KEY, + b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED); +ERROR: cannot use whole-row variable in column generation expression +LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STOR... + ^ +DETAIL: This would cause the generated column to depend on its own value. -- invalid reference CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED); ERROR: column "c" does not exist @@ -93,6 +98,7 @@ ERROR: both identity and generation expression specified for column "b" of tabl LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ... ^ -- reference to system column not allowed in generated column +-- (except tableoid, which we test below) CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED); ERROR: cannot use system column "xmin" in column generation expression LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37... @@ -126,20 +132,31 @@ LINE 1: CREATE TABLE gtest_err_9a (a int, b int GENERATED ALWAYS AS ... ^ DETAIL: Column "b" is a generated column. CREATE TABLE gtest_err_9b (a int GENERATED ALWAYS AS (b * 2) STORED, b int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' 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 gtest1 VALUES (1); -INSERT INTO gtest1 VALUES (2, DEFAULT); +INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok INSERT INTO gtest1 VALUES (3, 33); -- error -ERROR: cannot insert into column "b" +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok SELECT * FROM gtest1 ORDER BY a; a | b ---+--- 1 | 2 2 | 4 -(2 rows) + 3 | 6 + 4 | 8 +(4 rows) +DELETE FROM gtest1 WHERE a >= 3; UPDATE gtest1 SET b = DEFAULT WHERE a = 1; UPDATE gtest1 SET b = 11 WHERE a = 1; -- error ERROR: column "b" can only be updated to DEFAULT @@ -217,9 +234,37 @@ SELECT * FROM gtest1v; 3 | 6 (1 row) -INSERT INTO gtest1v VALUES (4, 8); -- fails -ERROR: cannot insert into column "b" +INSERT INTO gtest1v VALUES (4, 8); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok +INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok +ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; +INSERT INTO gtest1v VALUES (8, DEFAULT); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +SELECT * FROM gtest1v; + a | b +---+---- + 3 | 6 + 5 | 10 + 6 | 12 + 7 | 14 +(4 rows) + +DELETE FROM gtest1v WHERE a >= 5; DROP VIEW gtest1v; -- CTEs WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo; @@ -247,9 +292,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -279,12 +322,99 @@ DETAIL: Feature not supported: Inherited tables 4 | 8 (2 rows) --- test inheritance mismatch +CREATE TABLE gtest_normal (a int, b int); +CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); +NOTICE: merging column "a" with inherited definition +NOTICE: merging column "b" with inherited definition +\d gtest_normal_child +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest_normal_child" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Inherits: gtest_normal + +INSERT INTO gtest_normal (a) VALUES (1); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables +INSERT INTO gtest_normal_child (a) VALUES (2); +SELECT * FROM gtest_normal; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables + a | b +---+--- + 1 | + 2 | 4 +(2 rows) + +CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); +ALTER TABLE gtest_normal_child2 INHERIT gtest_normal; +INSERT INTO gtest_normal_child2 (a) VALUES (3); +SELECT * FROM gtest_normal; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables + a | b +---+--- + 1 | + 2 | 4 + 3 | 9 +(3 rows) + +-- test inheritance mismatches between parent and child +CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error +NOTICE: merging column "b" with inherited definition +ERROR: child column "b" specifies generation expression +HINT: Omit the generation expression in the definition of the child table column to inherit the generation expression from the parent table. +CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error +NOTICE: merging column "b" with inherited definition +ERROR: column "b" inherits from generated column but specifies default +CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error +NOTICE: merging column "b" with inherited definition +ERROR: column "b" inherits from generated column but specifies identity +CREATE TABLE gtestxx_1 (a int NOT NULL, b int); +ALTER TABLE gtestxx_1 INHERIT gtest1; -- error +ERROR: column "b" in child table must be a generated column +CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED); +ALTER TABLE gtestxx_2 INHERIT gtest1; -- error +ERROR: column "b" in child table has a conflicting generation expression +CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED); +ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok +CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL); +ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok +-- test multiple inheritance mismatches CREATE TABLE gtesty (x int, b int); CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error NOTICE: merging multiple inherited definitions of column "b" ERROR: inherited column "b" has a generation conflict DROP TABLE gtesty; +CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED); +CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error +NOTICE: merging multiple inherited definitions of column "b" +ERROR: column "b" inherits conflicting generation expressions +DROP TABLE gtesty; +CREATE TABLE gtesty (x int, b int DEFAULT 55); +CREATE TABLE gtest1_2 () INHERITS (gtest0, gtesty); -- error +NOTICE: merging multiple inherited definitions of column "b" +ERROR: inherited column "b" has a generation conflict +DROP TABLE gtesty; -- test stored update CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL); @@ -384,6 +514,18 @@ SELECT * FROM gtest2; 1 | (1 row) +-- simple column reference for varlena types +CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED); +INSERT INTO gtest_varlena (a) VALUES('01234567890123456789'); +INSERT INTO gtest_varlena (a) VALUES(NULL); +SELECT * FROM gtest_varlena ORDER BY a; + a | b +----------------------+---------------------- + 01234567890123456789 | 01234567890123456789 + | +(2 rows) + +DROP TABLE gtest_varlena; -- composite types CREATE TYPE double_int as (a int, b int); CREATE TABLE gtest4 ( @@ -403,14 +545,16 @@ DROP TYPE double_int; -- using tableoid is allowed CREATE TABLE gtest_tableoid ( a int PRIMARY KEY, - b bool GENERATED ALWAYS AS (tableoid <> 0) STORED + b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED ); INSERT INTO gtest_tableoid VALUES (1), (2); +ALTER TABLE gtest_tableoid ADD COLUMN + c regclass GENERATED ALWAYS AS (tableoid) STORED; SELECT * FROM gtest_tableoid; - a | b ----+--- - 1 | t - 2 | t + a | b | c +---+---+---------------- + 1 | t | gtest_tableoid + 2 | t | gtest_tableoid (2 rows) -- drop column behavior @@ -430,9 +574,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -492,24 +634,24 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row -ERROR: check constraint "gtest20a_b_check" is violated by some row +ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest20b (a) VALUES (10); INSERT INTO gtest20b (a) VALUES (30); ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID; ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row -ERROR: check constraint "chk" is violated by some row +ERROR: check constraint "chk" of relation "gtest20b" is violated by some row -- not-null constraints CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL); INSERT INTO gtest21a (a) VALUES (1); -- ok INSERT INTO gtest21a (a) VALUES (0); -- violates constraint -ERROR: null value in column "b" violates not-null constraint +ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint DETAIL: Failing row contains (0, null). CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED); ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; INSERT INTO gtest21b (a) VALUES (1); -- ok INSERT INTO gtest21b (a) VALUES (0); -- violates constraint -ERROR: null value in column "b" violates not-null constraint +ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint DETAIL: Failing row contains (0, null). ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; INSERT INTO gtest21b (a) VALUES (0); -- ok now @@ -544,9 +686,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -572,7 +712,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using gtest22c_b_idx on gtest22c Index Cond: (b = 4) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (4 rows) SELECT * FROM gtest22c WHERE b = 4; @@ -634,15 +774,13 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -734,17 +872,63 @@ ERROR: cannot use generated column "b" in column generation expression DETAIL: A generated column cannot reference another generated column. ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error ERROR: column "z" does not exist +ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42, + ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED; +ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101; +ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8, + ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED; +SELECT * FROM gtest25 ORDER BY a; + a | b | c | x | d | y +---+----+----+-----+-----+----- + 3 | 9 | 42 | 168 | 101 | 404 + 4 | 12 | 42 | 168 | 101 | 404 +(2 rows) + +\d gtest25 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest25" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+------------------------------------------------------ + a | integer | | not null | + b | integer | | | generated always as (a * 3) stored + c | integer | | | 42 + x | integer | | | generated always as (c * 4) stored + d | double precision | | | 101 + y | double precision | | | generated always as (d * 4::double precision) stored +Indexes: + "gtest25_pkey" PRIMARY KEY, btree (a) + -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( a int, - b int GENERATED ALWAYS AS (a * 2) STORED + b int, + x int GENERATED ALWAYS AS ((a + b) * 2) STORED ); ALTER TABLE gtest27 SET DISTRIBUTED RANDOMLY; -INSERT INTO gtest27 (a) VALUES (3), (4); +INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11); ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error ERROR: cannot alter type of a column used by a generated column -DETAIL: Column "a" is used by generated column "b". -ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric; +DETAIL: Column "a" is used by generated column "x". +ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric; \d gtest27 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation @@ -757,59 +941,298 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation + Table "public.gtest27" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-------------------------------------------- + a | integer | | | + b | integer | | | + x | numeric | | | generated always as (((a + b) * 2)) stored + +SELECT * FROM gtest27; + a | b | x +---+----+---- + 3 | 7 | 20 + 4 | 11 | 30 +(2 rows) + +ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error +ERROR: generation expression for column "x" cannot be cast automatically to type boolean +ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error +ERROR: column "x" of relation "gtest27" is a generated column +HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead. +-- It's possible to alter the column types this way: +ALTER TABLE gtest27 + DROP COLUMN x, + ALTER COLUMN a TYPE bigint, + ALTER COLUMN b TYPE bigint, + ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED; +\d gtest27 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation - Table "public.gtest27" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+-------------------------------------- - a | integer | | | - b | numeric | | | generated always as ((a * 2)) stored +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest27" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+------------------------------------------ + a | bigint | | | + b | bigint | | | + x | bigint | | | generated always as ((a + b) * 2) stored + +-- Ideally you could just do this, but not today (and should x change type?): +ALTER TABLE gtest27 + ALTER COLUMN a TYPE float8, + ALTER COLUMN b TYPE float8; -- error +ERROR: cannot alter type of a column used by a generated column +DETAIL: Column "a" is used by generated column "x". +\d gtest27 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest27" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+------------------------------------------ + a | bigint | | | + b | bigint | | | + x | bigint | | | generated always as ((a + b) * 2) stored SELECT * FROM gtest27; - a | b ----+--- - 3 | 6 - 4 | 8 + a | b | x +---+----+---- + 3 | 7 | 20 + 4 | 11 | 30 (2 rows) -ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean USING b <> 0; -- error -ERROR: generation expression for column "b" cannot be cast automatically to type boolean -ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error -ERROR: column "b" of relation "gtest27" is a generated column -\d gtest27 +-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION +CREATE TABLE gtest29 ( + a int, + b int GENERATED ALWAYS AS (a * 2) STORED +); +INSERT INTO gtest29 (a) VALUES (3), (4); +ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error +ERROR: column "a" of relation "gtest29" is not a stored generated column +ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice +NOTICE: column "a" of relation "gtest29" is not a stored generated column, skipping +ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION; +INSERT INTO gtest29 (a) VALUES (5); +INSERT INTO gtest29 (a, b) VALUES (6, 66); +SELECT * FROM gtest29; + a | b +---+---- + 3 | 6 + 4 | 8 + 5 | + 6 | 66 +(4 rows) + +\d gtest29 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest29" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | + +-- check that dependencies between columns have also been removed +ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b +\d gtest29 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation + Table "public.gtest29" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + b | integer | | | + +-- with inheritance +CREATE TABLE gtest30 ( + a int, + b int GENERATED ALWAYS AS (a * 2) STORED +); +CREATE TABLE gtest30_1 () INHERITS (gtest30); +ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; +\d gtest30 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation - Table "public.gtest27" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+-------------------------------------- + Table "public.gtest30" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- a | integer | | | - b | numeric | | | generated always as ((a * 2)) stored + b | integer | | | +Number of child tables: 1 (Use \d+ to list them.) +\d gtest30_1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest30_1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Inherits: gtest30 + +DROP TABLE gtest30 CASCADE; +NOTICE: drop cascades to table gtest30_1 +CREATE TABLE gtest30 ( + a int, + b int GENERATED ALWAYS AS (a * 2) STORED +); +CREATE TABLE gtest30_1 () INHERITS (gtest30); +ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error +ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too +\d gtest30 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest30" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Number of child tables: 1 (Use \d+ to list them.) + +\d gtest30_1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest30_1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Inherits: gtest30 + +ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error +ERROR: cannot drop generation expression from inherited column -- triggers CREATE TABLE gtest26 ( a int PRIMARY KEY, @@ -998,9 +1421,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner @@ -1024,9 +1445,7 @@ DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation -INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Non-default collation +DETAIL: Feature not supported: Queries on master-only tables INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Non-default collation INFO: GPORCA failed to produce a plan, falling back to planner diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index 69572e75df..7c9c1bcdd0 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -3322,7 +3322,7 @@ select * from boolp where a = (select value from boolvalues where value); -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value Rows Removed by Filter: 1 - -> Hash (never executed) + -> Hash (actual rows=0 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) @@ -3346,7 +3346,7 @@ select * from boolp where a = (select value from boolvalues where not value); -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) Rows Removed by Filter: 1 - -> Hash (never executed) + -> Hash (actual rows=0 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) diff --git a/src/test/regress/expected/rpt.out b/src/test/regress/expected/rpt.out index 1c1ea7b09e..367ebd5b40 100644 --- a/src/test/regress/expected/rpt.out +++ b/src/test/regress/expected/rpt.out @@ -9,6 +9,8 @@ -- create schema rpt; set search_path to rpt; +-- start_ignore +-- GPDB_12_MERGE_FIXME: different plan when enable_parallel to on; -- If the producer is replicated, request a non-singleton spec -- that is not allowed to be enforced, to avoid potential CTE hang issue drop table if exists with_test1 cascade; @@ -66,9 +68,9 @@ SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT --------------------------------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=390.63..390.63 rows=1 width=104) -> Limit (cost=332.63..390.63 rows=1 width=104) - -> Hash Join (cost=332.63..739.49 rows=7 width=104) + -> Hash Join (cost=332.63..739.50 rows=7 width=104) Hash Cond: ((with_test2.iscd)::text = (r1.rc)::text) - -> Hash Join (cost=166.32..572.78 rows=129 width=104) + -> Hash Join (cost=166.32..572.79 rows=129 width=104) Hash Cond: ((with_test2.isc)::text = (r.rc)::text) -> Seq Scan on with_test2 (cost=0.00..344.00 rows=24400 width=104) -> Hash (cost=166.25..166.25 rows=5 width=516) @@ -91,6 +93,7 @@ SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT 1 | CMN_BIN_YES | CMN_BIN_YES (1 row) +-- end_ignore --------- -- INSERT --------- diff --git a/src/test/regress/expected/rpt_optimizer.out b/src/test/regress/expected/rpt_optimizer.out index fdd9c51844..62a9fe489a 100644 --- a/src/test/regress/expected/rpt_optimizer.out +++ b/src/test/regress/expected/rpt_optimizer.out @@ -9,6 +9,8 @@ -- create schema rpt; set search_path to rpt; +-- start_ignore +-- GPDB_12_MERGE_FIXME: different plan when enable_parallel to on; -- If the producer is replicated, request a non-singleton spec -- that is not allowed to be enforced, to avoid potential CTE hang issue drop table if exists with_test1 cascade; @@ -65,9 +67,9 @@ SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT --------------------------------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=390.63..390.63 rows=1 width=104) -> Limit (cost=332.63..390.63 rows=1 width=104) - -> Hash Join (cost=332.63..739.49 rows=7 width=104) + -> Hash Join (cost=332.63..739.50 rows=7 width=104) Hash Cond: ((with_test2.iscd)::text = (r1.rc)::text) - -> Hash Join (cost=166.32..572.78 rows=129 width=104) + -> Hash Join (cost=166.32..572.79 rows=129 width=104) Hash Cond: ((with_test2.isc)::text = (r.rc)::text) -> Seq Scan on with_test2 (cost=0.00..344.00 rows=24400 width=104) -> Hash (cost=166.25..166.25 rows=5 width=516) @@ -90,6 +92,7 @@ SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT 1 | CMN_BIN_YES | CMN_BIN_YES (1 row) +-- end_ignore --------- -- INSERT --------- diff --git a/src/test/regress/expected/tidscan_optimizer.out b/src/test/regress/expected/tidscan_optimizer.out index 79b4abdb56..ed4ae3e528 100644 --- a/src/test/regress/expected/tidscan_optimizer.out +++ b/src/test/regress/expected/tidscan_optimizer.out @@ -145,18 +145,21 @@ SET enable_hashjoin TO off; -- otherwise hash join might win EXPLAIN (COSTS OFF) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Hash Join Hash Cond: (tidscan.ctid = tidscan_1.ctid) - -> Seq Scan on tidscan + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: tidscan.ctid + -> Seq Scan on tidscan + Filter: (id = 1) -> Hash - -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Redistribute Motion 3:3 (slice3; segments: 3) + Hash Key: tidscan_1.ctid -> Seq Scan on tidscan tidscan_1 - Filter: (id = 1) Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) +(12 rows) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; @@ -168,18 +171,21 @@ FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; EXPLAIN (COSTS OFF) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; - QUERY PLAN ------------------------------------------------------- - Hash Right Join - Hash Cond: (tidscan.ctid = tidscan_1.ctid) - -> Gather Motion 3:1 (slice1; segments: 3) - -> Seq Scan on tidscan - -> Hash - -> Gather Motion 3:1 (slice2; segments: 3) - -> Seq Scan on tidscan tidscan_1 + QUERY PLAN +------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) + -> Hash Left Join + Hash Cond: (tidscan.ctid = tidscan_1.ctid) + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: tidscan.ctid + -> Seq Scan on tidscan Filter: (id = 1) + -> Hash + -> Redistribute Motion 3:3 (slice3; segments: 3) + Hash Key: tidscan_1.ctid + -> Seq Scan on tidscan tidscan_1 Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) +(12 rows) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; diff --git a/src/test/regress/expected/tsrf_optimizer.out b/src/test/regress/expected/tsrf_optimizer.out index 117522c09e..11d5b31af5 100644 --- a/src/test/regress/expected/tsrf_optimizer.out +++ b/src/test/regress/expected/tsrf_optimizer.out @@ -92,7 +92,7 @@ SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; ProjectSet Output: unnest('{1,2}'::integer[]) -> Result - Output: NULL::integer, NULL::tid, NULL::xid, NULL::cid, NULL::xid, NULL::cid, NULL::oid, NULL::integer, NULL::integer + Output: NULL::integer, NULL::tid, NULL::xid, NULL::cid, NULL::xid, NULL::cid, NULL::oid, NULL::integer, NULL::oid One-Time Filter: false Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -120,7 +120,7 @@ SELECT * FROM few f1, -> ProjectSet Output: unnest('{1,2}'::integer[]) -> Result - Output: NULL::integer, NULL::tid, NULL::xid, NULL::cid, NULL::xid, NULL::cid, NULL::oid, NULL::integer, NULL::integer + Output: NULL::integer, NULL::tid, NULL::xid, NULL::cid, NULL::xid, NULL::cid, NULL::oid, NULL::integer, NULL::oid One-Time Filter: false Optimizer: Pivotal Optimizer (GPORCA) (15 rows) diff --git a/src/test/regress/expected/union_optimizer.out b/src/test/regress/expected/union_optimizer.out index 42de63f9b7..083da7d7e4 100644 --- a/src/test/regress/expected/union_optimizer.out +++ b/src/test/regress/expected/union_optimizer.out @@ -705,6 +705,8 @@ reset enable_hashagg; set enable_hashagg to on; explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------- Unique @@ -718,6 +720,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (8 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,2) @@ -727,6 +731,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------------- SetOp Intersect @@ -741,6 +747,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va (9 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,2) @@ -748,6 +756,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------------- SetOp Except @@ -762,6 +772,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value (9 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,3) @@ -772,6 +784,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value -- type is hashable. (Otherwise, this would fail at execution time.) explain (costs off) select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------- Unique @@ -785,6 +799,8 @@ select x from (values (row(100::money)), (row(200::money))) _(x) union select x (8 rows) select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ----------- ($100.00) @@ -821,6 +837,8 @@ drop type ct1; set enable_hashagg to off; explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------- Unique @@ -834,6 +852,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (8 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,2) @@ -843,6 +863,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------------- SetOp Intersect @@ -857,6 +879,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va (9 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,2) @@ -864,6 +888,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va explain (costs off) select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION QUERY PLAN ----------------------------------------------------- SetOp Except @@ -878,6 +904,8 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value (9 rows) select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ROW EXPRESSION x ------- (1,3) diff --git a/src/test/regress/expected/update_gp.out b/src/test/regress/expected/update_gp.out index f8d47b7775..7535120a8e 100644 --- a/src/test/regress/expected/update_gp.out +++ b/src/test/regress/expected/update_gp.out @@ -107,34 +107,34 @@ WHERE t1.user_vie_project_code_pk = keo1.user_vie_project_code_pk; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Update on keo1 - InitPlan 3 (returns $2) (slice1) + InitPlan 3 (returns $2) (slice5) -> Aggregate - InitPlan 2 (returns $1) (slice3) - -> Gather Motion 3:1 (slice4; segments: 3) - InitPlan 1 (returns $0) (slice5) + InitPlan 2 (returns $1) (slice7) + -> Gather Motion 3:1 (slice8; segments: 3) + InitPlan 1 (returns $0) (slice9) -> Aggregate - -> Gather Motion 3:1 (slice6; segments: 3) + -> Gather Motion 3:1 (slice10; segments: 3) -> Seq Scan on keo4 -> Seq Scan on keo4 keo4_1 Filter: ((keo_para_budget_date)::text = $0) - -> Gather Motion 3:1 (slice2; segments: 3) + -> Gather Motion 3:1 (slice6; segments: 3) -> Seq Scan on keo3 Filter: ((bky_per)::text = ($1)::text) - -> Explicit Redistribute Motion 3:3 (slice7; segments: 3) + -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) -> Hash Join Hash Cond: ((a.user_vie_project_code_pk)::text = (b.projects_pk)::text) -> Hash Join Hash Cond: ((a.user_vie_project_code_pk)::text = (keo1.user_vie_project_code_pk)::text) - -> Redistribute Motion 3:3 (slice8; segments: 3) + -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: a.user_vie_project_code_pk -> Seq Scan on keo1 a Filter: ((user_vie_fiscal_year_period_sk)::text = $2) -> Hash - -> Redistribute Motion 3:3 (slice9; segments: 3) + -> Redistribute Motion 3:3 (slice3; segments: 3) Hash Key: keo1.user_vie_project_code_pk -> Seq Scan on keo1 -> Hash - -> Redistribute Motion 3:3 (slice10; segments: 3) + -> Redistribute Motion 3:3 (slice4; segments: 3) Hash Key: b.projects_pk -> Seq Scan on keo2 b Optimizer: Postgres query optimizer diff --git a/src/test/regress/expected/update_gp_optimizer.out b/src/test/regress/expected/update_gp_optimizer.out index 9bb4909651..1e59baaa29 100644 --- a/src/test/regress/expected/update_gp_optimizer.out +++ b/src/test/regress/expected/update_gp_optimizer.out @@ -122,48 +122,46 @@ EXPLAIN (COSTS OFF) UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM (SELECT min (keo4.keo_para_budget_date) FROM keo4))) ) t1 WHERE t1.user_vie_project_code_pk = keo1.user_vie_project_code_pk; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on keo1 - -> Result - -> Split - -> Hash Join - Hash Cond: ((keo1_1.user_vie_project_code_pk)::text = (keo1_2.user_vie_project_code_pk)::text) - -> Seq Scan on keo1 keo1_1 - -> Hash - -> Broadcast Motion 3:3 (slice1; segments: 3) - -> Hash Join - Hash Cond: ((keo1_2.user_vie_project_code_pk)::text = (keo2.projects_pk)::text) - -> Hash Join - Hash Cond: ((max((keo3.sky_per)::text)) = (keo1_2.user_vie_fiscal_year_period_sk)::text) - -> Redistribute Motion 1:3 (slice2; segments: 1) - -> Aggregate - -> Hash Join - Hash Cond: ((keo3.bky_per)::text = (keo4_1.keo_para_required_period)::text) - -> Gather Motion 3:1 (slice3; segments: 3) - -> Seq Scan on keo3 - -> Hash - -> Assert - Assert Cond: ((row_number() OVER (?)) = 1) - -> WindowAgg - -> Gather Motion 3:1 (slice4; segments: 3) - -> Hash Join - Hash Cond: ((min((keo4.keo_para_budget_date)::text)) = (keo4_1.keo_para_budget_date)::text) - -> Redistribute Motion 1:3 (slice5; segments: 1) - -> Aggregate - -> Gather Motion 3:1 (slice6; segments: 3) - -> Seq Scan on keo4 - -> Hash - -> Broadcast Motion 3:3 (slice7; segments: 3) - -> Seq Scan on keo4 keo4_1 - -> Hash - -> Broadcast Motion 3:3 (slice8; segments: 3) - -> Seq Scan on keo1 keo1_2 - -> Hash - -> Broadcast Motion 3:3 (slice9; segments: 3) - -> Seq Scan on keo2 + -> Hash Join + Hash Cond: ((keo1_1.user_vie_project_code_pk)::text = (keo1_2.user_vie_project_code_pk)::text) + -> Seq Scan on keo1 keo1_1 + -> Hash + -> Broadcast Motion 3:3 (slice1; segments: 3) + -> Hash Join + Hash Cond: ((keo1_2.user_vie_project_code_pk)::text = (keo2.projects_pk)::text) + -> Hash Join + Hash Cond: ((max((keo3.sky_per)::text)) = (keo1_2.user_vie_fiscal_year_period_sk)::text) + -> Redistribute Motion 1:3 (slice2; segments: 1) + -> Aggregate + -> Hash Join + Hash Cond: ((keo3.bky_per)::text = (keo4_1.keo_para_required_period)::text) + -> Gather Motion 3:1 (slice3; segments: 3) + -> Seq Scan on keo3 + -> Hash + -> Assert + Assert Cond: ((row_number() OVER (?)) = 1) + -> WindowAgg + -> Gather Motion 3:1 (slice4; segments: 3) + -> Hash Join + Hash Cond: ((min((keo4.keo_para_budget_date)::text)) = (keo4_1.keo_para_budget_date)::text) + -> Redistribute Motion 1:3 (slice5; segments: 1) + -> Aggregate + -> Gather Motion 3:1 (slice6; segments: 3) + -> Seq Scan on keo4 + -> Hash + -> Broadcast Motion 3:3 (slice7; segments: 3) + -> Seq Scan on keo4 keo4_1 + -> Hash + -> Broadcast Motion 3:3 (slice8; segments: 3) + -> Seq Scan on keo1 keo1_2 + -> Hash + -> Broadcast Motion 3:3 (slice9; segments: 3) + -> Seq Scan on keo2 Optimizer: Pivotal Optimizer (GPORCA) -(39 rows) +(37 rows) UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM ( SELECT a.user_vie_project_code_pk FROM keo1 a INNER JOIN keo2 b diff --git a/src/test/regress/expected/with_clause_optimizer.out b/src/test/regress/expected/with_clause_optimizer.out index d7da0a393e..2e50a1f283 100644 --- a/src/test/regress/expected/with_clause_optimizer.out +++ b/src/test/regress/expected/with_clause_optimizer.out @@ -2284,12 +2284,11 @@ SELECT count(a1.i) -> Redistribute Motion 1:3 (slice2) -> Aggregate -> Gather Motion 3:1 (slice3; segments: 3) - -> Partial Aggregate - -> Hash Join - Hash Cond: (share0_ref3.i = share1_ref3.i) - -> Shared Scan (share slice:id 3:0) - -> Hash - -> Shared Scan (share slice:id 3:1) + -> Hash Join + Hash Cond: (share0_ref3.i = share1_ref3.i) + -> Shared Scan (share slice:id 3:0) + -> Hash + -> Shared Scan (share slice:id 3:1) Optimizer: Pivotal Optimizer (GPORCA) (22 rows) diff --git a/src/test/regress/sql/bfv_joins.sql b/src/test/regress/sql/bfv_joins.sql index 011226d5f0..d15f03b4f0 100644 --- a/src/test/regress/sql/bfv_joins.sql +++ b/src/test/regress/sql/bfv_joins.sql @@ -416,6 +416,8 @@ drop table o2; drop table o3; --end_ignore +-- Current case add in Derive Combined Hashed Spec For Outer Joins (#14045), f8264ad +-- GPDB will got wrong result in ORCA plan, util merged [FIXME] Wrong results on main branch for INDF query,ce25faf create table o1 (a1 int, b1 int) distributed by (a1); create table o2 (a2 int, b2 int) distributed by (a2); create table o3 (a3 int, b3 int) distributed by (a3); diff --git a/src/test/regress/sql/rpt.sql b/src/test/regress/sql/rpt.sql index 21a3bbf31b..f412bb1b01 100644 --- a/src/test/regress/sql/rpt.sql +++ b/src/test/regress/sql/rpt.sql @@ -10,6 +10,8 @@ create schema rpt; set search_path to rpt; +-- start_ignore +-- GPDB_12_MERGE_FIXME: different plan when enable_parallel to on; -- If the producer is replicated, request a non-singleton spec -- that is not allowed to be enforced, to avoid potential CTE hang issue drop table if exists with_test1 cascade; @@ -41,6 +43,7 @@ WITH t1 AS (SELECT * FROM with_test2), t2 AS (SELECT id, rc FROM with_test3 WHERE ri = 101991) SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT 1; +-- end_ignore --------- -- INSERT --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
