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 2bfac78e2c9f5991c6e007ff5f0e3771c6907e7c Author: DevChattopadhyay <[email protected]> AuthorDate: Tue May 9 12:55:23 2023 +0530 Marking the "PexprConvert2In" preprocessing step as "unsupported for now" This PR marks the FIXME as unsupported for now because enabling the "PexprConvert2In" preprocessing step is leading to multiple issues like IN operator with Hash Indexed Table is falling back to planner,wrong filters are generated while converting a "OR" between a date and timestamp to IN operator etc. --- .../src/operators/CExpressionPreprocessor.cpp | 6 ++--- src/backend/gporca/server/CMakeLists.txt | 4 +-- src/test/regress/expected/gporca.out | 27 +++++++++++++++++++ src/test/regress/expected/gporca_optimizer.out | 31 ++++++++++++++++++++++ src/test/regress/sql/gporca.sql | 12 +++++++++ 5 files changed, 75 insertions(+), 5 deletions(-) diff --git a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp index c94454c875..040bc30fe9 100644 --- a/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp +++ b/src/backend/gporca/libgpopt/src/operators/CExpressionPreprocessor.cpp @@ -3367,9 +3367,9 @@ CExpressionPreprocessor::PexprPreprocess( CExpression *pexprConvert2In = pexprUnnested; - // GPDB_12_MERGE_FIXME: Although we've enabled EopttraceArrayConstraints, - // the following conversion is causing problems; and might be very - // inefficient! Disable for noe. + // ORCA_FEATURE_NOT_SUPPORTED: Unsupported for now as on enabling it,in some cases + // providing promising results but its also inefficient in some cases and + // generating wrong outputs if (GPOS_FTRACE(EopttraceArrayConstraints) && false) { // (10.5) ensure predicates are array IN or NOT IN where applicable diff --git a/src/backend/gporca/server/CMakeLists.txt b/src/backend/gporca/server/CMakeLists.txt index b9d82a61d1..a149b3acfd 100644 --- a/src/backend/gporca/server/CMakeLists.txt +++ b/src/backend/gporca/server/CMakeLists.txt @@ -40,8 +40,8 @@ endfunction() # GPDB_12_MERGE_FIXME: casts on part column no longer supported! # CPartTableLossyCastTest -# GPDB_12_MERGE_FIXME: re-add the following tests to CArrayCmpTest once the -# duplicate predicate issue is fixed +# ORCA_FEATURE_NOT_SUPPORTED: re-add the following tests to CArrayCmpTest once the +# PexprConvert2In preprocessing step is enabled # IN-ArrayCmp # NOT-IN-ArrayCmp diff --git a/src/test/regress/expected/gporca.out b/src/test/regress/expected/gporca.out index a0e59bfe48..66a7be1b10 100644 --- a/src/test/regress/expected/gporca.out +++ b/src/test/regress/expected/gporca.out @@ -14610,3 +14610,30 @@ explain insert into array_coerce_foo select * from array_coerce_bar; insert into array_coerce_foo select * from array_coerce_bar; ERROR: value too long for type character varying(2) (seg1 127.0.0.1:7003 pid=55908) +-- These testcases will fallback to postgres when "PexprConvert2In" is enabled if +-- underlying issues are not fixed +create table baz (a int,b int); +explain select baz.* from baz where +baz.a=1 OR +baz.b = 1 OR baz.b = 2 OR baz.b = 3 OR baz.b = 4 OR baz.b = 5 OR baz.b = 6 OR baz.b = 7 OR baz.b = 8 OR baz.b = 9 OR baz.b = 10 OR +baz.b = 11 OR baz.b = 12 OR baz.b = 13 OR baz.b = 14 OR baz.b = 15 OR baz.b = 16 OR baz.b = 17 OR baz.b = 18 OR baz.b = 19 OR baz.b = 20; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1851.62 rows=1790 width=8) + -> Seq Scan on baz (cost=0.00..1827.75 rows=597 width=8) + Filter: ((a = 1) OR (b = 1) OR (b = 2) OR (b = 3) OR (b = 4) OR (b = 5) OR (b = 6) OR (b = 7) OR (b = 8) OR (b = 9) OR (b = 10) OR (b = 11) OR (b = 12) OR (b = 13) OR (b = 14) OR (b = 15) OR (b = 16) OR (b = 17) OR (b = 18) OR (b = 19) OR (b = 20)) + Optimizer: Postgres query optimizer +(4 rows) + +drop table baz; +create table baz ( a varchar); +explain select * from baz where baz.a::bpchar='b' or baz.a='c'; + QUERY PLAN +--------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..299.41 rows=106 width=32) + -> Seq Scan on baz (cost=0.00..298.00 rows=35 width=32) + Filter: (((a)::bpchar = 'b'::bpchar) OR ((a)::text = 'c'::text)) + Optimizer: Postgres query optimizer +(4 rows) + +drop table baz; diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index e68157ffb0..78ec799376 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -14726,3 +14726,34 @@ explain insert into array_coerce_foo select * from array_coerce_bar; insert into array_coerce_foo select * from array_coerce_bar; ERROR: value too long for type character varying(2) (seg1 127.0.0.1:7003 pid=51460) +-- These testcases will fallback to postgres when "PexprConvert2In" is enabled if +-- underlying issues are not fixed +create table baz (a int,b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +explain select baz.* from baz where +baz.a=1 OR +baz.b = 1 OR baz.b = 2 OR baz.b = 3 OR baz.b = 4 OR baz.b = 5 OR baz.b = 6 OR baz.b = 7 OR baz.b = 8 OR baz.b = 9 OR baz.b = 10 OR +baz.b = 11 OR baz.b = 12 OR baz.b = 13 OR baz.b = 14 OR baz.b = 15 OR baz.b = 16 OR baz.b = 17 OR baz.b = 18 OR baz.b = 19 OR baz.b = 20; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) + -> Seq Scan on baz (cost=0.00..431.00 rows=1 width=8) + Filter: ((a = 1) OR (b = 1) OR (b = 2) OR (b = 3) OR (b = 4) OR (b = 5) OR (b = 6) OR (b = 7) OR (b = 8) OR (b = 9) OR (b = 10) OR (b = 11) OR (b = 12) OR (b = 13) OR (b = 14) OR (b = 15) OR (b = 16) OR (b = 17) OR (b = 18) OR (b = 19) OR (b = 20)) + Optimizer: Pivotal Optimizer (GPORCA) +(4 rows) + +drop table baz; +create table baz ( a varchar); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +explain select * from baz where baz.a::bpchar='b' or baz.a='c'; + QUERY PLAN +------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) + -> Seq Scan on baz (cost=0.00..431.00 rows=1 width=8) + Filter: (((a)::bpchar = 'b'::bpchar) OR ((a)::text = 'c'::text)) + Optimizer: Pivotal Optimizer (GPORCA) +(4 rows) + +drop table baz; diff --git a/src/test/regress/sql/gporca.sql b/src/test/regress/sql/gporca.sql index a314ab51d6..e02448a605 100644 --- a/src/test/regress/sql/gporca.sql +++ b/src/test/regress/sql/gporca.sql @@ -3592,6 +3592,18 @@ WITH conf AS ( select * from conf; reset optimizer_trace_fallback; +-- These testcases will fallback to postgres when "PexprConvert2In" is enabled if +-- underlying issues are not fixed +create table baz (a int,b int); +explain select baz.* from baz where +baz.a=1 OR +baz.b = 1 OR baz.b = 2 OR baz.b = 3 OR baz.b = 4 OR baz.b = 5 OR baz.b = 6 OR baz.b = 7 OR baz.b = 8 OR baz.b = 9 OR baz.b = 10 OR +baz.b = 11 OR baz.b = 12 OR baz.b = 13 OR baz.b = 14 OR baz.b = 15 OR baz.b = 16 OR baz.b = 17 OR baz.b = 18 OR baz.b = 19 OR baz.b = 20; +drop table baz; +create table baz ( a varchar); +explain select * from baz where baz.a::bpchar='b' or baz.a='c'; +drop table baz; + -- start_ignore DROP SCHEMA orca CASCADE; -- end_ignore --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
