HAWQ-1122. Guard against ScalarArrayOpExpr qual under Index Scan [#128092771]
Also updated gp_optimizer expected output and ignore line number difference for functions.c This is cherry-picked from greenplum-db/gpdb@684b429457f820f13db730ff46601b299582cb3c Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/7c69aacf Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/7c69aacf Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/7c69aacf Branch: refs/heads/master Commit: 7c69aacf341514a206501a65478f599a0c106fe0 Parents: 14524b4 Author: Haisheng Yuan <[email protected]> Authored: Fri Aug 12 11:57:53 2016 -0700 Committer: rlei <[email protected]> Committed: Fri Oct 28 11:52:20 2016 +0800 ---------------------------------------------------------------------- .../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 5 ++ src/test/regress/expected/gp_optimizer.out | 89 ++++++++------------ src/test/regress/init_file | 38 +++++++++ src/test/regress/sql/gp_optimizer.sql | 4 - 4 files changed, 79 insertions(+), 57 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/7c69aacf/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp ---------------------------------------------------------------------- diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp index 5cbae05..75b6ed5 100644 --- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp +++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp @@ -1527,6 +1527,11 @@ CTranslatorDXLToPlStmt::TranslateIndexConditions GPOS_ASSERT((IsA(pexprIndexCond, OpExpr) || IsA(pexprIndexCond, ScalarArrayOpExpr)) && "expected OpExpr or ScalarArrayOpExpr in index qual"); + if (IsA(pexprIndexCond, ScalarArrayOpExpr) && IMDIndex::EmdindBitmap != pmdindex->Emdindt()) + { + GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiDXL2PlStmtConversion, GPOS_WSZ_LIT("ScalarArrayOpExpr condition on index scan is not supported")); + } + // for indexonlyscan, we already have the attno referring to the index if (!fIndexOnlyScan) { http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/7c69aacf/src/test/regress/expected/gp_optimizer.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/gp_optimizer.out b/src/test/regress/expected/gp_optimizer.out index 8444eb3..424f3b0 100644 --- a/src/test/regress/expected/gp_optimizer.out +++ b/src/test/regress/expected/gp_optimizer.out @@ -8753,82 +8753,65 @@ drop table canSetTag_input_data; -- Test B-Tree index scan with in list CREATE TABLE btree_test as SELECT * FROM generate_series(1,100) as a distributed randomly; CREATE INDEX btree_test_index ON btree_test(a); -EXPLAIN SELECT * FROM btree_test WHERE a in (select 1); - QUERY PLAN ------------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2.00 rows=1 width=4) - -> Nested Loop (cost=0.00..2.00 rows=1 width=4) - Join Filter: true - -> Result (cost=0.00..0.00 rows=1 width=4) - -> Result (cost=0.00..0.00 rows=1 width=1) - -> Index Scan using btree_test_index on btree_test (cost=0.00..2.00 rows=1 width=4) - Index Cond: btree_test.a = (1) - Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: PQO version 1.647 -(9 rows) - EXPLAIN SELECT * FROM btree_test WHERE a in (1, 47); - QUERY PLAN ------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=3 width=4) - -> Index Scan using btree_test_index on btree_test (cost=0.00..6.00 rows=1 width=4) - Index Cond: a = ANY ('{1,47}'::integer[]) + QUERY PLAN +---------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..4.25 rows=2 width=4) + -> Seq Scan on btree_test (cost=0.00..4.25 rows=1 width=4) + Filter: a = ANY ('{1,47}'::integer[]) Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: PQO version 1.647 + Optimizer status: legacy query optimizer (5 rows) EXPLAIN SELECT * FROM btree_test WHERE a in ('2', 47); - QUERY PLAN ------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=3 width=4) - -> Index Scan using btree_test_index on btree_test (cost=0.00..6.00 rows=1 width=4) - Index Cond: a = ANY ('{2,47}'::integer[]) + QUERY PLAN +---------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..4.25 rows=2 width=4) + -> Seq Scan on btree_test (cost=0.00..4.25 rows=1 width=4) + Filter: a = ANY ('{2,47}'::integer[]) Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: PQO version 1.647 + Optimizer status: legacy query optimizer (5 rows) EXPLAIN SELECT * FROM btree_test WHERE a in ('1', '2'); - QUERY PLAN ------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=3 width=4) - -> Index Scan using btree_test_index on btree_test (cost=0.00..6.00 rows=1 width=4) - Index Cond: a = ANY ('{1,2}'::integer[]) + QUERY PLAN +---------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..4.25 rows=2 width=4) + -> Seq Scan on btree_test (cost=0.00..4.25 rows=1 width=4) + Filter: a = ANY ('{1,2}'::integer[]) Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: PQO version 1.647 + Optimizer status: legacy query optimizer (5 rows) EXPLAIN SELECT * FROM btree_test WHERE a in ('1', '2', 47); - QUERY PLAN ------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..8.00 rows=4 width=4) - -> Index Scan using btree_test_index on btree_test (cost=0.00..8.00 rows=2 width=4) - Index Cond: a = ANY ('{1,2,47}'::integer[]) + QUERY PLAN +---------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..4.38 rows=3 width=4) + -> Seq Scan on btree_test (cost=0.00..4.38 rows=1 width=4) + Filter: a = ANY ('{1,2,47}'::integer[]) Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: PQO version 1.647 + Optimizer status: legacy query optimizer (5 rows) -- Test Bitmap index scan with in list CREATE TABLE bitmap_test as SELECT * FROM generate_series(1,100) as a distributed randomly; CREATE INDEX bitmap_index ON bitmap_test USING BITMAP(a); --- The following query should fall back to planner. --- Update the result file when this has been fixed in ORCA. EXPLAIN SELECT * FROM bitmap_test WHERE a in (select 1); - QUERY PLAN -------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice2; segments: 3) (cost=0.07..4.38 rows=7 width=4) - -> Hash Join (cost=0.07..4.38 rows=3 width=4) - Hash Cond: bitmap_test.a = (1) - -> Seq Scan on bitmap_test (cost=0.00..4.00 rows=34 width=4) - -> Hash (cost=0.05..0.05 rows=1 width=4) - -> Broadcast Motion 1:3 (slice1; segments: 1) (cost=0.02..0.05 rows=1 width=4) - -> HashAggregate (cost=0.02..0.03 rows=1 width=4) - Group By: 1 - -> Result (cost=0.00..0.01 rows=1 width=0) + QUERY PLAN +--------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.05 rows=100 width=4) + -> Nested Loop (cost=0.00..0.04 rows=34 width=4) + Join Filter: true + -> Result (cost=0.00..0.00 rows=1 width=4) + -> Result (cost=0.00..0.00 rows=1 width=1) + -> Bitmap Table Scan on bitmap_test (cost=0.00..0.04 rows=1 width=4) + Recheck Cond: bitmap_test.a = (1) + -> Bitmap Index Scan on bitmap_index (cost=0.00..0.00 rows=0 width=0) + Index Cond: bitmap_test.a = (1) Settings: optimizer=on; optimizer_metadata_caching=on - Optimizer status: legacy query optimizer + Optimizer status: PQO version 1.658 (11 rows) --- The following queries should work without falling back to planner. EXPLAIN SELECT * FROM bitmap_test WHERE a in (1, 47); QUERY PLAN --------------------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/7c69aacf/src/test/regress/init_file ---------------------------------------------------------------------- diff --git a/src/test/regress/init_file b/src/test/regress/init_file index 75a62dd..8790e3b 100644 --- a/src/test/regress/init_file +++ b/src/test/regress/init_file @@ -10,3 +10,41 @@ m/^ Optimizer status:.*/ s/^ Optimizer status:.*// -- end_matchignore + +-- start_matchsubs +#entry db matches +m/\s+\(entry db(.*)+\spid=\d+\)/ +s/\s+\(entry db(.*)+\spid=\d+\)// + +#remove unecessary diffs due to SOMEFILE:SOMEFUNC +m/\(SOMEFILE:SOMEFUNC\)/ +s/\(SOMEFILE:SOMEFUNC\)// + +m/\(COptTasks\.cpp:\d+\)/ +s/\(COptTasks\.cpp:\d+\)// + +m/\(cdbdisp\.c:\d+\)/ +s/\(cdbdisp\.c:\d+\)// + +m/\(plpython\.c:\d+\)/ +s/\(plpython\.c:\d+\)// + +m/\(functions\.c:\d+\)/ +s/\(functions\.c:\d+\)// + +#In alter_table_distribution_policy tests, some test cases emit NOTICE message +#like the following +#drop cascades to function pg_atsdb_211851_2_4_out. +#The name contains OID embedded in it and it can change with test runs. We want +#to remove these and diff the rest. +m/^NOTICE:.*drop cascades to.*/ +s/^NOTICE:.*drop cascades to.*/^NOTICE: drop cascades to / + +# Mask out some numbers (parts of partition names) that vary from run to run. +m/overlaps existing partition "r\d+"/ +s/overlaps existing partition "r\d+"/partition "r##########"/ + +# Mask out some numbers (part of temp table schema) that vary from run to run. +m/Table "pg_temp_\d+.temp/ +s/Table "pg_temp_\d+.temp/Table "pg_temp_#####/ +-- end_matchsubs http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/7c69aacf/src/test/regress/sql/gp_optimizer.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/gp_optimizer.sql b/src/test/regress/sql/gp_optimizer.sql index d873e2a..d0c43c7 100644 --- a/src/test/regress/sql/gp_optimizer.sql +++ b/src/test/regress/sql/gp_optimizer.sql @@ -844,7 +844,6 @@ drop table orca_exc_handle; -- Test B-Tree index scan with in list CREATE TABLE btree_test as SELECT * FROM generate_series(1,100) as a distributed randomly; CREATE INDEX btree_test_index ON btree_test(a); -EXPLAIN SELECT * FROM btree_test WHERE a in (select 1); EXPLAIN SELECT * FROM btree_test WHERE a in (1, 47); EXPLAIN SELECT * FROM btree_test WHERE a in ('2', 47); EXPLAIN SELECT * FROM btree_test WHERE a in ('1', '2'); @@ -853,10 +852,7 @@ EXPLAIN SELECT * FROM btree_test WHERE a in ('1', '2', 47); -- Test Bitmap index scan with in list CREATE TABLE bitmap_test as SELECT * FROM generate_series(1,100) as a distributed randomly; CREATE INDEX bitmap_index ON bitmap_test USING BITMAP(a); --- The following query should fall back to planner. --- Update the result file when this has been fixed in ORCA. EXPLAIN SELECT * FROM bitmap_test WHERE a in (select 1); --- The following queries should work without falling back to planner. EXPLAIN SELECT * FROM bitmap_test WHERE a in (1, 47); EXPLAIN SELECT * FROM bitmap_test WHERE a in ('2', 47); EXPLAIN SELECT * FROM bitmap_test WHERE a in ('1', '2');
