You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2024/12/31 02:56:09 UTC
(cloudberry) 27/27: Fix icw test "Derive Combined Hashed Spec For Outer Joins"
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 <zh...@hashdata.cn>
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: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org