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 78ebf55f1287b22ca31555617abce29b77f9115c Author: xuejing zhao <[email protected]> AuthorDate: Mon Dec 5 10:08:07 2022 +0800 Add test case for fix wrong results caused by over-eager constraint exclusion (#14592) Add test case and fix compile warning for commit:f25190de2b3b3e063af2274629673a9c48ebff50 --- src/backend/optimizer/util/predtest.c | 3 +-- src/test/regress/expected/partition_pruning.out | 31 ++++++++++++++++++++++ .../expected/partition_pruning_optimizer.out | 29 ++++++++++++++++++++ src/test/regress/sql/partition_pruning.sql | 15 +++++++++++ 4 files changed, 76 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/util/predtest.c b/src/backend/optimizer/util/predtest.c index 9b772e5985..16e213faba 100644 --- a/src/backend/optimizer/util/predtest.c +++ b/src/backend/optimizer/util/predtest.c @@ -250,8 +250,7 @@ predicate_refuted_by(List *predicate_list, List *clause_list, c = (Node *) clause_list; /* And away we go ... */ - if ( predicate_refuted_by_recurse(c, p, weak)) - return true; + return predicate_refuted_by_recurse(c, p, weak); } /*---------- diff --git a/src/test/regress/expected/partition_pruning.out b/src/test/regress/expected/partition_pruning.out index 2bd61af921..e59c30db31 100644 --- a/src/test/regress/expected/partition_pruning.out +++ b/src/test/regress/expected/partition_pruning.out @@ -14,6 +14,37 @@ set enable_incremental_sort=on; -- GPDB_12_MERGE_FIXME: Many of these queries are no longer able to constraint -- exclusion, like we used to on GPDB 6. Not sure what we should do about it. -- See https://github.com/greenplum-db/gpdb/issues/10287. +-- In GPDB6 this case is partition pruned, but the result is wrong, +-- in MAIN branch it is not partition pruned, but has right result. +-- Create test table with two partitions, for values equal to '1' and values equal to '2'. +create table parttab (n numeric, t text) + partition by list (n)(partition one values ('1'), partition two values('2')); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' 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 three rows. They're all equal to '1', but different number of zeros after decimal point. +insert into parttab values + ('1', 'one'), + ('1.0', 'one point zero'), + ('1.00', 'one point zero zero'); +-- select rows whose text representation is three characters long. This should return the '1.0' row. +select * from parttab where length(n::text) = 3; + n | t +-----+---------------- + 1.0 | one point zero +(1 row) + +explain select * from parttab where length(n::text) = 3; + QUERY PLAN +--------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..533.04 rows=70 width=64) + -> Append (cost=0.00..532.12 rows=23 width=64) + -> Seq Scan on parttab_1_prt_one (cost=0.00..266.00 rows=12 width=64) + Filter: (length((n)::text) = 3) + -> Seq Scan on parttab_1_prt_two (cost=0.00..266.00 rows=12 width=64) + Filter: (length((n)::text) = 3) + Optimizer: Postgres query optimizer +(7 rows) + -- Use index scans when possible. That exercises more code, and allows us to -- spot the cases where the planner cannot use even when it exists. set enable_seqscan=off; diff --git a/src/test/regress/expected/partition_pruning_optimizer.out b/src/test/regress/expected/partition_pruning_optimizer.out index f414e70e6c..e4b8643363 100644 --- a/src/test/regress/expected/partition_pruning_optimizer.out +++ b/src/test/regress/expected/partition_pruning_optimizer.out @@ -14,6 +14,35 @@ set enable_incremental_sort=on; -- GPDB_12_MERGE_FIXME: Many of these queries are no longer able to constraint -- exclusion, like we used to on GPDB 6. Not sure what we should do about it. -- See https://github.com/greenplum-db/gpdb/issues/10287. +-- In GPDB6 this case is partition pruned, but the result is wrong, +-- in MAIN branch it is not partition pruned, but has right result. +-- Create test table with two partitions, for values equal to '1' and values equal to '2'. +create table parttab (n numeric, t text) + partition by list (n)(partition one values ('1'), partition two values('2')); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' 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 three rows. They're all equal to '1', but different number of zeros after decimal point. +insert into parttab values + ('1', 'one'), + ('1.0', 'one point zero'), + ('1.00', 'one point zero zero'); +-- select rows whose text representation is three characters long. This should return the '1.0' row. +select * from parttab where length(n::text) = 3; + n | t +-----+---------------- + 1.0 | one point zero +(1 row) + +explain select * from parttab where length(n::text) = 3; + QUERY PLAN +------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) + -> Dynamic Seq Scan on parttab (cost=0.00..431.00 rows=1 width=16) + Number of partitions to scan: 2 (out of 2) + Filter: (length((n)::text) = 3) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + -- Use index scans when possible. That exercises more code, and allows us to -- spot the cases where the planner cannot use even when it exists. set enable_seqscan=off; diff --git a/src/test/regress/sql/partition_pruning.sql b/src/test/regress/sql/partition_pruning.sql index 4b6a96e581..40147850fa 100644 --- a/src/test/regress/sql/partition_pruning.sql +++ b/src/test/regress/sql/partition_pruning.sql @@ -16,6 +16,21 @@ set enable_incremental_sort=on; -- GPDB_12_MERGE_FIXME: Many of these queries are no longer able to constraint -- exclusion, like we used to on GPDB 6. Not sure what we should do about it. -- See https://github.com/greenplum-db/gpdb/issues/10287. +-- In GPDB6 this case is partition pruned, but the result is wrong, +-- in MAIN branch it is not partition pruned, but has right result. +-- Create test table with two partitions, for values equal to '1' and values equal to '2'. +create table parttab (n numeric, t text) + partition by list (n)(partition one values ('1'), partition two values('2')); + +-- Insert three rows. They're all equal to '1', but different number of zeros after decimal point. +insert into parttab values + ('1', 'one'), + ('1.0', 'one point zero'), + ('1.00', 'one point zero zero'); + +-- select rows whose text representation is three characters long. This should return the '1.0' row. +select * from parttab where length(n::text) = 3; +explain select * from parttab where length(n::text) = 3; -- Use index scans when possible. That exercises more code, and allows us to -- spot the cases where the planner cannot use even when it exists. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
