This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit ccbcf879b5876aa306d7da44aea8ccadcb685fbb Author: seawinde <[email protected]> AuthorDate: Sun Feb 4 14:39:17 2024 +0800 [test](mtmv) Add materialized view availability regression test (#30769) Add materialized view availability regression test when mv refresh_time is in the grace_period(unit is second), materialized view will be use to query rewrite regardless of the base table is update or not when mv refresh_time is out of the grace_period(unit is second), will check the base table is update or not if update the materialized view will not be used to query rewrite --- .../nereids/rules/exploration/mv/StructInfo.java | 5 +- .../org/apache/doris/regression/suite/Suite.groovy | 38 ++ .../agg_with_roll_up/aggregate_with_roll_up.groovy | 92 ++-- .../aggregate_without_roll_up.groovy | 90 ++-- .../mv/availability/grace_period.groovy | 489 +++++++++++++++++++++ .../availability/materialized_view_switch.groovy | 155 +++++++ .../mv/join/dphyp_inner/inner_join_dphyp.groovy | 78 +--- .../mv/join/dphyp_outer/outer_join_dphyp.groovy | 76 +--- .../mv/join/inner/inner_join.groovy | 78 +--- .../mv/join/left_outer/outer_join.groovy | 80 +--- .../nereids_rules_p0/mv/ssb/mv_ssb_test.groovy | 65 +-- 11 files changed, 831 insertions(+), 415 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java index 6a5c82691e7..af874d37b11 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java @@ -177,8 +177,9 @@ public class StructInfo { nodePlan.accept(RELATION_COLLECTOR, nodeRelations); relationBuilder.addAll(nodeRelations); // every node should only have one relation, this is for LogicalCompatibilityContext - relationIdStructInfoNodeMap.put(nodeRelations.get(0).getRelationId(), (StructInfoNode) node); - + if (!nodeRelations.isEmpty()) { + relationIdStructInfoNodeMap.put(nodeRelations.get(0).getRelationId(), (StructInfoNode) node); + } // record expressions in node if (structInfoNode.getExpressions() != null) { structInfoNode.getExpressions().forEach(expression -> { diff --git a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy index 97dcb879b04..b5dde914199 100644 --- a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy +++ b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy @@ -1032,4 +1032,42 @@ class Suite implements GroovyInterceptable { return result.values().toList() } + + def check_mv_rewrite_success = { db, mv_sql, query_sql, mv_name -> + + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" + sql""" + CREATE MATERIALIZED VIEW ${mv_name} + BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL + DISTRIBUTED BY RANDOM BUCKETS 2 + PROPERTIES ('replication_num' = '1') + AS ${mv_sql} + """ + + def job_name = getJobName(db, mv_name); + waitingMTMVTaskFinished(job_name) + explain { + sql("${query_sql}") + contains("${mv_name}(${mv_name})") + } + } + + def check_mv_rewrite_fail = { db, mv_sql, query_sql, mv_name -> + + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" + sql""" + CREATE MATERIALIZED VIEW ${mv_name} + BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL + DISTRIBUTED BY RANDOM BUCKETS 2 + PROPERTIES ('replication_num' = '1') + AS ${mv_sql} + """ + + def job_name = getJobName(db, mv_name); + waitingMTMVTaskFinished(job_name) + explain { + sql("${query_sql}") + notContains("${mv_name}(${mv_name})") + } + } } diff --git a/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy b/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy index eb4657f7863..bc2d9f13525 100644 --- a/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy @@ -132,25 +132,6 @@ suite("aggregate_with_roll_up") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - def check_rewrite_with_mv_partition = { mv_sql, query_sql, mv_name, partition_column -> sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" @@ -193,25 +174,6 @@ suite("aggregate_with_roll_up") { } } - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // multi table // filter inside + left + use roll up dimension def mv13_0 = "select l_shipdate, o_orderdate, l_partkey, l_suppkey, " + @@ -240,7 +202,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query13_0_before "${query13_0}" - check_rewrite(mv13_0, query13_0, "mv13_0") + check_mv_rewrite_success(db, mv13_0, query13_0, "mv13_0") order_qt_query13_0_after "${query13_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv13_0""" @@ -275,7 +237,7 @@ suite("aggregate_with_roll_up") { l_suppkey; """ order_qt_query13_1_before "${query13_1}" - check_not_match(mv13_1, query13_1, "mv13_1") + check_mv_rewrite_fail(db, mv13_1, query13_1, "mv13_1") order_qt_query13_1_after "${query13_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv13_1""" @@ -309,7 +271,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query14_0_before "${query14_0}" - check_rewrite(mv14_0, query14_0, "mv14_0") + check_mv_rewrite_success(db, mv14_0, query14_0, "mv14_0") order_qt_query14_0_after "${query14_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv14_0""" @@ -415,7 +377,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query16_0_before "${query16_0}" - check_rewrite(mv16_0, query16_0, "mv16_0") + check_mv_rewrite_success(db, mv16_0, query16_0, "mv16_0") order_qt_query16_0_after "${query16_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv16_0""" @@ -450,7 +412,7 @@ suite("aggregate_with_roll_up") { l_suppkey; """ order_qt_query17_0_before "${query17_0}" - check_rewrite(mv17_0, query17_0, "mv17_0") + check_mv_rewrite_success(db, mv17_0, query17_0, "mv17_0") order_qt_query17_0_after "${query17_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv17_0""" @@ -481,7 +443,7 @@ suite("aggregate_with_roll_up") { "l_shipdate, " + "l_suppkey" order_qt_query18_0_before "${query18_0}" - check_rewrite(mv18_0, query18_0, "mv18_0") + check_mv_rewrite_success(db, mv18_0, query18_0, "mv18_0") order_qt_query18_0_after "${query18_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv18_0""" @@ -503,7 +465,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query19_0_before "${query19_0}" - check_rewrite(mv19_0, query19_0, "mv19_0") + check_mv_rewrite_success(db, mv19_0, query19_0, "mv19_0") order_qt_query19_0_after "${query19_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_0""" @@ -534,7 +496,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query19_1_before "${query19_1}" - check_rewrite(mv19_1, query19_1, "mv19_1") + check_mv_rewrite_success(db, mv19_1, query19_1, "mv19_1") order_qt_query19_1_after "${query19_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_1""" @@ -568,7 +530,7 @@ suite("aggregate_with_roll_up") { "o_orderdate, " + "l_suppkey" order_qt_query20_0_before "${query20_0}" - check_rewrite(mv20_0, query20_0, "mv20_0") + check_mv_rewrite_success(db, mv20_0, query20_0, "mv20_0") order_qt_query20_0_after "${query20_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_0""" @@ -608,7 +570,7 @@ suite("aggregate_with_roll_up") { l_suppkey; """ order_qt_query20_1_before "${query20_1}" - check_rewrite(mv20_1, query20_1, "mv20_1") + check_mv_rewrite_success(db, mv20_1, query20_1, "mv20_1") order_qt_query20_1_after "${query20_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_1""" @@ -642,7 +604,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query21_0_before "${query21_0}" - check_rewrite(mv21_0, query21_0, "mv21_0") + check_mv_rewrite_success(db, mv21_0, query21_0, "mv21_0") order_qt_query21_0_after "${query21_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_0""" @@ -675,7 +637,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query22_0_before "${query22_0}" - check_rewrite(mv22_0, query22_0, "mv22_0") + check_mv_rewrite_success(db, mv22_0, query22_0, "mv22_0") order_qt_query22_0_after "${query22_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv22_0""" @@ -708,7 +670,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query22_1_before "${query22_1}" - check_rewrite(mv22_1, query22_1, "mv22_1") + check_mv_rewrite_success(db, mv22_1, query22_1, "mv22_1") order_qt_query22_1_after "${query22_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv22_1""" @@ -742,7 +704,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query23_0_before "${query23_0}" - check_rewrite(mv23_0, query23_0, "mv23_0") + check_mv_rewrite_success(db, mv23_0, query23_0, "mv23_0") order_qt_query23_0_after "${query23_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv23_0""" @@ -774,7 +736,7 @@ suite("aggregate_with_roll_up") { "l_shipdate, " + "l_suppkey" order_qt_query24_0_before "${query24_0}" - check_rewrite(mv24_0, query24_0, "mv24_0") + check_mv_rewrite_success(db, mv24_0, query24_0, "mv24_0") order_qt_query24_0_after "${query24_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv24_0""" @@ -805,7 +767,7 @@ suite("aggregate_with_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query25_0_before "${query25_0}" - check_rewrite(mv25_0, query25_0, "mv25_0") + check_mv_rewrite_success(db, mv25_0, query25_0, "mv25_0") order_qt_query25_0_after "${query25_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv25_0""" @@ -842,7 +804,7 @@ suite("aggregate_with_roll_up") { l_suppkey; """ order_qt_query25_1_before "${query25_1}" - check_rewrite(mv25_1, query25_1, "mv25_1") + check_mv_rewrite_success(db, mv25_1, query25_1, "mv25_1") order_qt_query25_1_after "${query25_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv25_1""" @@ -881,7 +843,7 @@ suite("aggregate_with_roll_up") { l_suppkey; """ order_qt_query25_2_before "${query25_2}" - check_rewrite(mv25_2, query25_2, "mv25_2") + check_mv_rewrite_success(db, mv25_2, query25_2, "mv25_2") order_qt_query25_2_after "${query25_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv25_2""" @@ -922,7 +884,7 @@ suite("aggregate_with_roll_up") { l_suppkey + l_partkey; """ order_qt_query25_3_before "${query25_3}" - check_rewrite(mv25_3, query25_3, "mv25_3") + check_mv_rewrite_success(db, mv25_3, query25_3, "mv25_3") order_qt_query25_3_after "${query25_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv25_3""" @@ -950,7 +912,7 @@ suite("aggregate_with_roll_up") { """ order_qt_query25_4_before "${query25_4}" - check_rewrite(mv25_4, query25_4, "mv25_4") + check_mv_rewrite_success(db, mv25_4, query25_4, "mv25_4") order_qt_query25_4_after "${query25_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv25_4""" @@ -1056,7 +1018,7 @@ suite("aggregate_with_roll_up") { o_shippriority; """ order_qt_query26_0_before "${query26_0}" - check_not_match(mv26_0, query26_0, "mv26_0") + check_mv_rewrite_fail(db, mv26_0, query26_0, "mv26_0") order_qt_query26_0_after "${query26_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv26_0""" @@ -1090,7 +1052,7 @@ suite("aggregate_with_roll_up") { o_shippriority; """ order_qt_query27_0_before "${query27_0}" - check_not_match(mv27_0, query27_0, "mv27_0") + check_mv_rewrite_fail(db, mv27_0, query27_0, "mv27_0") order_qt_query27_0_after "${query27_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv27_0""" @@ -1131,7 +1093,7 @@ suite("aggregate_with_roll_up") { limit 10; """ order_qt_query28_0_before "${query28_0}" - check_rewrite(mv28_0, query28_0, "mv28_0") + check_mv_rewrite_success(db, mv28_0, query28_0, "mv28_0") order_qt_query28_0_after "${query28_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv28_0""" @@ -1160,7 +1122,7 @@ suite("aggregate_with_roll_up") { ) as t; """ order_qt_query29_0_before "${query29_0}" - check_rewrite(mv29_0, query29_0, "mv29_0") + check_mv_rewrite_success(db, mv29_0, query29_0, "mv29_0") order_qt_query29_0_after "${query29_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv29_0""" @@ -1187,7 +1149,7 @@ suite("aggregate_with_roll_up") { """ order_qt_query29_1_before "${query29_1}" - check_rewrite(mv29_1, query29_1, "mv29_1") + check_mv_rewrite_success(db, mv29_1, query29_1, "mv29_1") order_qt_query29_1_after "${query29_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv29_1""" @@ -1213,7 +1175,7 @@ suite("aggregate_with_roll_up") { left join orders on l_orderkey = o_orderkey and l_shipdate = o_orderdate; """ order_qt_query29_2_before "${query29_2}" - check_rewrite(mv29_2, query29_2, "mv29_2") + check_mv_rewrite_success(db, mv29_2, query29_2, "mv29_2") order_qt_query29_2_after "${query29_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv29_2""" @@ -1260,7 +1222,7 @@ suite("aggregate_with_roll_up") { group by l_linenumber; """ order_qt_query30_0_before "${query30_0}" - check_not_match(mv30_0, query30_0, "mv30_0") + check_mv_rewrite_fail(db, mv30_0, query30_0, "mv30_0") order_qt_query30_0_after "${query30_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv30_0""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy index 07675365fcd..95ea99bc190 100644 --- a/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy @@ -134,44 +134,6 @@ suite("aggregate_without_roll_up") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // single table // with filter def mv1_0 = "select o_shippriority, o_comment, " + @@ -198,7 +160,7 @@ suite("aggregate_without_roll_up") { "o_shippriority, " + "o_comment " order_qt_query1_0_before "${query1_0}" - check_rewrite(mv1_0, query1_0, "mv1_0") + check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0") order_qt_query1_0_after "${query1_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0""" @@ -253,7 +215,7 @@ suite("aggregate_without_roll_up") { "O_SHIPPRIORITY, " + "O_COMMENT " order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -306,7 +268,7 @@ suite("aggregate_without_roll_up") { "O_SHIPPRIORITY, " + "O_COMMENT " order_qt_query1_2_before "${query1_2}" - check_rewrite(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_success(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -353,7 +315,7 @@ suite("aggregate_without_roll_up") { "O_SHIPPRIORITY, " + "O_COMMENT " order_qt_query2_0_before "${query2_0}" - check_rewrite(mv2_0, query2_0, "mv2_0") + check_mv_rewrite_success(db, mv2_0, query2_0, "mv2_0") order_qt_query2_0_after "${query2_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0""" @@ -400,7 +362,7 @@ suite("aggregate_without_roll_up") { "from orders " + "where O_ORDERDATE < '2023-12-30' and O_ORDERDATE > '2023-12-01'" order_qt_query3_0_before "${query3_0}" - check_rewrite(mv3_0, query3_0, "mv3_0") + check_mv_rewrite_success(db, mv3_0, query3_0, "mv3_0") order_qt_query3_0_after "${query3_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0""" @@ -432,7 +394,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query13_0_before "${query13_0}" - check_rewrite(mv13_0, query13_0, "mv13_0") + check_mv_rewrite_success(db, mv13_0, query13_0, "mv13_0") order_qt_query13_0_after "${query13_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv13_0""" @@ -465,7 +427,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query14_0_before "${query14_0}" - check_rewrite(mv14_0, query14_0, "mv14_0") + check_mv_rewrite_success(db, mv14_0, query14_0, "mv14_0") order_qt_query14_0_after "${query14_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv14_0""" @@ -498,7 +460,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query15_0_before "${query15_0}" - check_rewrite(mv15_0, query15_0, "mv15_0") + check_mv_rewrite_success(db, mv15_0, query15_0, "mv15_0") order_qt_query15_0_after "${query15_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv15_0""" @@ -535,7 +497,7 @@ suite("aggregate_without_roll_up") { l_suppkey; """ order_qt_query15_1_before "${query15_1}" - check_rewrite(mv15_1, query15_1, "mv15_1") + check_mv_rewrite_success(db, mv15_1, query15_1, "mv15_1") order_qt_query15_0_after "${query15_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv15_1""" @@ -566,7 +528,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query16_0_before "${query16_0}" - check_rewrite(mv16_0, query16_0, "mv16_0") + check_mv_rewrite_success(db, mv16_0, query16_0, "mv16_0") order_qt_query16_0_after "${query16_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv16_0""" @@ -600,7 +562,7 @@ suite("aggregate_without_roll_up") { l_suppkey; """ order_qt_query16_1_before "${query16_1}" - check_not_match(mv16_1, query16_1, "mv16_1") + check_mv_rewrite_fail(db, mv16_1, query16_1, "mv16_1") order_qt_query16_1_after "${query16_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv16_1""" @@ -631,7 +593,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query17_0_before "${query17_0}" - check_rewrite(mv17_0, query17_0, "mv17_0") + check_mv_rewrite_success(db, mv17_0, query17_0, "mv17_0") order_qt_query17_0_after "${query17_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv17_0""" @@ -690,7 +652,7 @@ suite("aggregate_without_roll_up") { "orders.O_SHIPPRIORITY, " + "orders.O_COMMENT " order_qt_query17_1_before "${query17_1}" - check_rewrite(mv17_1, query17_1, "mv17_1") + check_mv_rewrite_success(db, mv17_1, query17_1, "mv17_1") order_qt_query17_1_after "${query17_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv17_1""" @@ -719,7 +681,7 @@ suite("aggregate_without_roll_up") { "l_shipdate, " + "l_suppkey" order_qt_query18_0_before "${query18_0}" - check_rewrite(mv18_0, query18_0, "mv18_0") + check_mv_rewrite_success(db, mv18_0, query18_0, "mv18_0") order_qt_query18_0_after "${query18_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv18_0""" @@ -734,7 +696,7 @@ suite("aggregate_without_roll_up") { "where o_custkey = 2 and l_linenumber = 4 " + "group by l_linenumber, o_custkey " order_qt_query18_1_before "${query18_1}" - check_rewrite(mv18_1, query18_1, "mv18_1") + check_mv_rewrite_success(db, mv18_1, query18_1, "mv18_1") order_qt_query18_1_after "${query18_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv18_1""" @@ -749,7 +711,7 @@ suite("aggregate_without_roll_up") { "where o_custkey = 2 and l_suppkey= 3 " + "group by lineitem.l_linenumber, orders.o_custkey " order_qt_query18_2_before "${query18_2}" - check_not_match(mv18_2, query18_2, "mv18_2") + check_mv_rewrite_fail(db, mv18_2, query18_2, "mv18_2") order_qt_query18_2_after "${query18_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv18_2""" @@ -778,7 +740,7 @@ suite("aggregate_without_roll_up") { "l_partkey, " + "l_suppkey" order_qt_query19_0_before "${query19_0}" - check_rewrite(mv19_0, query19_0, "mv19_0") + check_mv_rewrite_success(db, mv19_0, query19_0, "mv19_0") order_qt_query19_0_after "${query19_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_0""" @@ -792,7 +754,7 @@ suite("aggregate_without_roll_up") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "group by lineitem.L_LINENUMBER, orders.O_CUSTKEY " order_qt_query19_1_before "${query19_1}" - check_rewrite(mv19_1, query19_1, "mv19_1") + check_mv_rewrite_success(db, mv19_1, query19_1, "mv19_1") order_qt_query19_1_after "${query19_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_1""" @@ -838,7 +800,7 @@ suite("aggregate_without_roll_up") { l_suppkey; """ order_qt_query19_2_before "${query19_2}" - check_rewrite(mv19_2, query19_2, "mv19_2") + check_mv_rewrite_success(db, mv19_2, query19_2, "mv19_2") order_qt_query19_2_after "${query19_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_2""" @@ -874,7 +836,7 @@ suite("aggregate_without_roll_up") { l_suppkey; """ order_qt_query19_3_before "${query19_3}" - check_rewrite(mv19_3, query19_3, "mv19_3") + check_mv_rewrite_success(db, mv19_3, query19_3, "mv19_3") order_qt_query19_3_after "${query19_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_0""" @@ -923,7 +885,7 @@ suite("aggregate_without_roll_up") { "on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERDATE < '2023-12-30' and orders.O_ORDERDATE > '2023-12-01' " order_qt_query20_0_before "${query20_0}" - check_rewrite(mv20_0, query20_0, "mv20_0") + check_mv_rewrite_success(db, mv20_0, query20_0, "mv20_0") order_qt_query20_0_after "${query20_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_0""" @@ -952,7 +914,7 @@ suite("aggregate_without_roll_up") { on lineitem.L_ORDERKEY = orders.O_ORDERKEY """ order_qt_query20_1_before "${query20_1}" - check_not_match(mv20_1, query20_1, "mv20_1") + check_mv_rewrite_fail(db, mv20_1, query20_1, "mv20_1") order_qt_query20_1_after "${query20_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_1""" @@ -980,7 +942,7 @@ suite("aggregate_without_roll_up") { l_shipinstruct; """ order_qt_query20_2_before "${query20_2}" - check_not_match(mv20_2, query20_2, "mv20_2") + check_mv_rewrite_fail(db, mv20_2, query20_2, "mv20_2") order_qt_query20_2_after "${query20_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_2""" @@ -1024,7 +986,7 @@ suite("aggregate_without_roll_up") { group by l_linenumber; """ order_qt_query21_0_before "${query21_0}" - check_rewrite(mv21_0, query21_0, "mv21_0") + check_mv_rewrite_success(db, mv21_0, query21_0, "mv21_0") order_qt_query21_0_after "${query21_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_0""" @@ -1068,7 +1030,7 @@ suite("aggregate_without_roll_up") { group by l_linenumber; """ order_qt_query21_1_before "${query21_1}" - check_not_match(mv21_1, query21_1, "mv21_1") + check_mv_rewrite_fail(db, mv21_1, query21_1, "mv21_1") order_qt_query21_1_after "${query21_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_1""" @@ -1113,7 +1075,7 @@ suite("aggregate_without_roll_up") { group by l_linenumber; """ order_qt_query21_2_before "${query21_2}" - check_not_match(mv21_2, query21_2, "mv21_2") + check_mv_rewrite_fail(db, mv21_2, query21_2, "mv21_2") order_qt_query21_2_after "${query21_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_2""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/availability/grace_period.groovy b/regression-test/suites/nereids_rules_p0/mv/availability/grace_period.groovy new file mode 100644 index 00000000000..e90a5e8bc7b --- /dev/null +++ b/regression-test/suites/nereids_rules_p0/mv/availability/grace_period.groovy @@ -0,0 +1,489 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("grace_period") { + + // when mv refresh_time is in the grace_period(unit is second), materialized view will be use to + // query rewrite regardless of the base table is update or not + // when mv refresh_time is out of the grace_period(unit is second), will check the base table is update or not + // if update will not be used to query rewrite + String db = context.config.getDbNameByFile(context.file) + sql "use ${db}" + sql "SET enable_nereids_planner=true" + sql "set runtime_filter_mode=OFF" + sql "SET enable_fallback_to_original_planner=false" + sql "SET enable_materialized_view_rewrite=true" + sql "SET enable_nereids_timeout = false" + + sql """ + drop table if exists orders + """ + sql """ + CREATE TABLE IF NOT EXISTS orders_partition ( + o_orderkey integer not null, + o_custkey integer not null, + o_orderstatus char(1) not null, + o_totalprice decimalv3(15,2) not null, + o_orderdate date not null, + o_orderpriority char(15) not null, + o_clerk char(15) not null, + o_shippriority integer not null, + o_comment varchar(79) not null + ) + DUPLICATE KEY(o_orderkey, o_custkey) + PARTITION BY RANGE(o_orderdate)( + FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY + ) + DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + + sql """ + drop table if exists lineitem + """ + + sql """ + CREATE TABLE IF NOT EXISTS lineitem_partition ( + l_orderkey integer not null, + l_partkey integer not null, + l_suppkey integer not null, + l_linenumber integer not null, + l_quantity decimalv3(15,2) not null, + l_extendedprice decimalv3(15,2) not null, + l_discount decimalv3(15,2) not null, + l_tax decimalv3(15,2) not null, + l_returnflag char(1) not null, + l_linestatus char(1) not null, + l_shipdate date not null, + l_commitdate date not null, + l_receiptdate date not null, + l_shipinstruct char(25) not null, + l_shipmode char(10) not null, + l_comment varchar(44) not null + ) + DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber) + PARTITION BY RANGE(l_shipdate) + (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY) + DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + + sql """ + insert into orders_partition values + (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'), + (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'), + (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy'); + """ + + sql """ + insert into lineitem_partition values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'), + (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'), + (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19', '2023-10-19', 'c', 'd', 'xxxxxxxxx'); + """ + + + def create_partition_mv = { mv_name, mv_sql, grace_period, partition_field -> + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" + sql""" + CREATE MATERIALIZED VIEW ${mv_name} + BUILD IMMEDIATE REFRESH AUTO ON MANUAL + partition by(${partition_field}) + DISTRIBUTED BY RANDOM BUCKETS 2 + PROPERTIES ('replication_num' = '1', 'grace_period'= '${grace_period}') + AS ${mv_sql} + """ + def job_name = getJobName(db, mv_name); + waitingMTMVTaskFinished(job_name) + } + + + def create_un_partition_mv = { mv_name, mv_sql, grace_period -> + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" + sql""" + CREATE MATERIALIZED VIEW ${mv_name} + BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL + DISTRIBUTED BY RANDOM BUCKETS 2 + PROPERTIES ('replication_num' = '1', 'grace_period'= '${grace_period}') + AS ${mv_sql} + """ + def job_name = getJobName(db, mv_name); + waitingMTMVTaskFinished(job_name) + } + + + def mv_partition_consistent_name = "mv_partition_consistent" + create_partition_mv(mv_partition_consistent_name, + """ + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey""", + 0, + "l_shipdate") + + sql """ + insert into lineitem_partition values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'); + """ + + // force consistency when partition table, and query use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_partition_consistent_name}(${mv_partition_consistent_name})") + } + // force consistency when partition table, and query doesn't use the partition changed, should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_partition_consistent_name}(${mv_partition_consistent_name})") + } + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_partition_consistent_name}""" + + + + + def mv_un_partition_consistent_name = "mv_un_partition_consistent" + create_un_partition_mv(mv_un_partition_consistent_name, + """ + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey""", + 0) + sql """ + insert into lineitem_partition values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'); + """ + // force consistency when un partition table, and query use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_consistent_name}(${mv_un_partition_consistent_name})") + } + + // force consistency when un partition table, and query use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_consistent_name}(${mv_un_partition_consistent_name})") + } + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_un_partition_consistent_name}""" + + + + + def mv_partition_allow_staleness_name = "mv_partition_allow_staleness" + create_partition_mv(mv_partition_allow_staleness_name, + """ + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey""", + 15, + "l_shipdate") + + sql """ + insert into lineitem_partition values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'); + """ + + // allow 10s staleness when partition table, and query use the partition changed, should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + // allow 10s staleness when partition table, and query doesn't use the partition changed, should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + sql "SET enable_materialized_view_rewrite=false" + // allow 10s staleness when partition table, and query use the partition changed, should success, + // but disable materialized view rewrite, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + // allow 10s staleness when partition table, and query doesn't use the partition changed, + // but disable materialized view rewrite, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + sql "SET enable_materialized_view_rewrite=true" + Thread.sleep(15000); + // after 10s when partition table, and query use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + // after 10s when partition table, and query doesn't use the partition changed, should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_partition_allow_staleness_name}(${mv_partition_allow_staleness_name})") + } + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_partition_allow_staleness_name}""" + + + + + def mv_un_partition_allow_staleness_name = "mv_un_partition_allow_staleness" + create_un_partition_mv(mv_un_partition_allow_staleness_name, + """ + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey""", + 15) + + sql """ + insert into lineitem_partition values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'); + """ + // allow 10s staleness when un partition table should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + // allow 10s staleness when un partition table, should success + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + contains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + sql "SET enable_materialized_view_rewrite=false" + // allow 10s staleness when un partition table, but disable materialized view rewrite, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + // allow 10s staleness when un partition table, but disable materialized view rewrite, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + sql "SET enable_materialized_view_rewrite=true" + Thread.sleep(15000); + // after 10s when un partition table, and query use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-17' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + // after 10s when un partition table, and query doesn't use the partition changed, should fail + explain { + sql(""" + select l_shipdate, o_orderdate, l_partkey, + l_suppkey, sum(o_totalprice) as sum_total + from lineitem_partition + left join orders_partition on l_orderkey = o_orderkey and l_shipdate = o_orderdate + where l_shipdate = '2023-10-18' + group by + l_shipdate, + o_orderdate, + l_partkey, + l_suppkey; + """) + notContains("${mv_un_partition_allow_staleness_name}(${mv_un_partition_allow_staleness_name})") + } + sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_un_partition_allow_staleness_name}""" +} diff --git a/regression-test/suites/nereids_rules_p0/mv/availability/materialized_view_switch.groovy b/regression-test/suites/nereids_rules_p0/mv/availability/materialized_view_switch.groovy new file mode 100644 index 00000000000..29bd112e76f --- /dev/null +++ b/regression-test/suites/nereids_rules_p0/mv/availability/materialized_view_switch.groovy @@ -0,0 +1,155 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("materialized_view_switch") { + + String db = context.config.getDbNameByFile(context.file) + sql "use ${db}" + sql "SET enable_nereids_planner=true" + sql "set runtime_filter_mode=OFF"; + sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'" + sql "SET enable_fallback_to_original_planner=false" + sql "SET enable_materialized_view_rewrite=true" + + sql """ + drop table if exists orders + """ + + sql """ + CREATE TABLE IF NOT EXISTS orders ( + o_orderkey INTEGER NOT NULL, + o_custkey INTEGER NOT NULL, + o_orderstatus CHAR(1) NOT NULL, + o_totalprice DECIMALV3(15,2) NOT NULL, + o_orderdate DATE NOT NULL, + o_orderpriority CHAR(15) NOT NULL, + o_clerk CHAR(15) NOT NULL, + o_shippriority INTEGER NOT NULL, + O_COMMENT VARCHAR(79) NOT NULL + ) + DUPLICATE KEY(o_orderkey, o_custkey) + PARTITION BY RANGE(o_orderdate) ( + PARTITION `day_2` VALUES LESS THAN ('2023-12-9'), + PARTITION `day_3` VALUES LESS THAN ("2023-12-11"), + PARTITION `day_4` VALUES LESS THAN ("2023-12-30") + ) + DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + + sql """ + drop table if exists lineitem + """ + + sql""" + CREATE TABLE IF NOT EXISTS lineitem ( + l_orderkey INTEGER NOT NULL, + l_partkey INTEGER NOT NULL, + l_suppkey INTEGER NOT NULL, + l_linenumber INTEGER NOT NULL, + l_quantity DECIMALV3(15,2) NOT NULL, + l_extendedprice DECIMALV3(15,2) NOT NULL, + l_discount DECIMALV3(15,2) NOT NULL, + l_tax DECIMALV3(15,2) NOT NULL, + l_returnflag CHAR(1) NOT NULL, + l_linestatus CHAR(1) NOT NULL, + l_shipdate DATE NOT NULL, + l_commitdate DATE NOT NULL, + l_receiptdate DATE NOT NULL, + l_shipinstruct CHAR(25) NOT NULL, + l_shipmode CHAR(10) NOT NULL, + l_comment VARCHAR(44) NOT NULL + ) + DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber) + PARTITION BY RANGE(l_shipdate) ( + PARTITION `day_1` VALUES LESS THAN ('2023-12-9'), + PARTITION `day_2` VALUES LESS THAN ("2023-12-11"), + PARTITION `day_3` VALUES LESS THAN ("2023-12-30")) + DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ) + """ + + sql """ + drop table if exists partsupp + """ + + sql """ + CREATE TABLE IF NOT EXISTS partsupp ( + ps_partkey INTEGER NOT NULL, + ps_suppkey INTEGER NOT NULL, + ps_availqty INTEGER NOT NULL, + ps_supplycost DECIMALV3(15,2) NOT NULL, + ps_comment VARCHAR(199) NOT NULL + ) + DUPLICATE KEY(ps_partkey, ps_suppkey) + DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ) + """ + + sql """ insert into lineitem values + (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-08', '2023-12-09', '2023-12-10', 'a', 'b', 'yyyyyyyyy'), + (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-09', '2023-12-09', '2023-12-10', 'a', 'b', 'yyyyyyyyy'), + (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-10', '2023-12-09', '2023-12-10', 'a', 'b', 'yyyyyyyyy'), + (4, 3, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-11', '2023-12-09', '2023-12-10', 'a', 'b', 'yyyyyyyyy'), + (5, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-12-12', '2023-12-12', '2023-12-13', 'c', 'd', 'xxxxxxxxx'); + """ + + sql """ + insert into orders values + (1, 1, 'o', 9.5, '2023-12-08', 'a', 'b', 1, 'yy'), + (1, 1, 'o', 10.5, '2023-12-08', 'a', 'b', 1, 'yy'), + (2, 1, 'o', 11.5, '2023-12-09', 'a', 'b', 1, 'yy'), + (3, 1, 'o', 12.5, '2023-12-10', 'a', 'b', 1, 'yy'), + (3, 1, 'o', 33.5, '2023-12-10', 'a', 'b', 1, 'yy'), + (4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'), + (5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'), + (5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi'); + """ + + sql """ + insert into partsupp values + (2, 3, 9, 10.01, 'supply1'), + (2, 3, 10, 11.01, 'supply2'); + """ + + def mv_name = """ + select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey + from lineitem + left join (select * from orders where o_orderdate = '2023-12-10' ) t2 + on lineitem.l_orderkey = t2.o_orderkey; + """ + def query = """ + select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey + from lineitem + left join orders + on lineitem.l_orderkey = orders.o_orderkey + where o_orderdate = '2023-12-10' order by 1, 2, 3, 4, 5; + """ + + check_mv_rewrite_success(db, mv_name, query, "mv_name") + sql "SET enable_materialized_view_rewrite=false" + check_mv_rewrite_fail(db, mv_name, query, "mv_name") + sql "SET enable_materialized_view_rewrite=true" + check_mv_rewrite_success(db, mv_name, query, "mv_name") + sql """ DROP MATERIALIZED VIEW IF EXISTS mv_name""" +} \ No newline at end of file diff --git a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_inner/inner_join_dphyp.groovy b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_inner/inner_join_dphyp.groovy index 5fd8db4bcdc..95e5dbed7a7 100644 --- a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_inner/inner_join_dphyp.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_inner/inner_join_dphyp.groovy @@ -125,44 +125,6 @@ suite("inner_join_dphyp") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // without filter def mv1_0 = "select lineitem.L_LINENUMBER, orders.O_CUSTKEY " + "from lineitem " + @@ -171,7 +133,7 @@ suite("inner_join_dphyp") { "from lineitem " + "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_0_before "${query1_0}" - check_rewrite(mv1_0, query1_0, "mv1_0") + check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0") order_qt_query1_0_after "${query1_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0""" @@ -187,7 +149,7 @@ suite("inner_join_dphyp") { "inner join partsupp on lineitem.L_PARTKEY = partsupp.PS_PARTKEY " + "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY" order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -199,7 +161,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_2_before "${query1_2}" // join direction is not same, should not match - check_rewrite(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_success(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -212,7 +174,7 @@ suite("inner_join_dphyp") { "from orders " + "inner join lineitem on orders.O_ORDERKEY = lineitem.L_ORDERKEY" order_qt_query1_3_before "${query1_3}" - check_rewrite(mv1_3, query1_3, "mv1_3") + check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3") order_qt_query1_3_after "${query1_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3""" @@ -231,7 +193,7 @@ suite("inner_join_dphyp") { and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY; """ order_qt_query1_4_before "${query1_4}" - check_rewrite(mv1_4, query1_4, "mv1_4") + check_mv_rewrite_success(db, mv1_4, query1_4, "mv1_4") order_qt_query1_4_after "${query1_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_4""" @@ -247,7 +209,7 @@ suite("inner_join_dphyp") { and o_shippriority = l_partkey; """ order_qt_query1_5_before "${query1_5}" - check_rewrite(mv1_5, query1_5, "mv1_5") + check_mv_rewrite_success(db, mv1_5, query1_5, "mv1_5") order_qt_query1_5_after "${query1_5}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_5""" @@ -260,7 +222,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 0" order_qt_query2_0_before "${query2_0}" - check_rewrite(mv2_0, query2_0, "mv2_0") + check_mv_rewrite_success(db, mv2_0, query2_0, "mv2_0") order_qt_query2_0_after "${query2_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0""" @@ -273,7 +235,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1" order_qt_query2_1_before "${query2_1}" - check_rewrite(mv2_1, query2_1, "mv2_1") + check_mv_rewrite_success(db, mv2_1, query2_1, "mv2_1") order_qt_query2_1_after "${query2_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1""" @@ -286,7 +248,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1 and l_suppkey = 3" order_qt_query2_2_before "${query2_2}" - check_rewrite(mv2_2, query2_2, "mv2_2") + check_mv_rewrite_success(db, mv2_2, query2_2, "mv2_2") order_qt_query2_2_after "${query2_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2""" @@ -303,7 +265,7 @@ suite("inner_join_dphyp") { "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY " + "where lineitem.L_LINENUMBER > 1 and l_suppkey = 3 " order_qt_query2_3_before "${query2_3}" - check_rewrite(mv2_3, query2_3, "mv2_3") + check_mv_rewrite_success(db, mv2_3, query2_3, "mv2_3") order_qt_query2_3_after "${query2_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3""" @@ -318,7 +280,7 @@ suite("inner_join_dphyp") { "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_0_before "${query3_0}" // use a filed not from mv, should not success - check_not_match(mv3_0, query3_0, "mv3_0") + check_mv_rewrite_fail(db, mv3_0, query3_0, "mv3_0") order_qt_query3_0_after "${query3_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0""" @@ -331,7 +293,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_1_before "${query3_1}" - check_rewrite(mv3_1, query3_1, "mv3_1") + check_mv_rewrite_success(db, mv3_1, query3_1, "mv3_1") order_qt_query3_1_after "${query3_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1""" @@ -346,7 +308,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_2_before "${query3_2}" - check_rewrite(mv3_2, query3_2, "mv3_2") + check_mv_rewrite_success(db, mv3_2, query3_2, "mv3_2") order_qt_query3_2_after "${query3_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2""" @@ -363,7 +325,7 @@ suite("inner_join_dphyp") { "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY " + "where o_custkey in (1, 2, 3, 4) " order_qt_query3_3_before "${query3_3}" - check_rewrite(mv3_3, query3_3, "mv3_3") + check_mv_rewrite_success(db, mv3_3, query3_3, "mv3_3") order_qt_query3_3_after "${query3_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_3""" @@ -383,7 +345,7 @@ suite("inner_join_dphyp") { where o_custkey = 1 and l_linenumber > 0; """ order_qt_query3_4_before "${query3_4}" - check_rewrite(mv3_4, query3_4, "mv3_4") + check_mv_rewrite_success(db, mv3_4, query3_4, "mv3_4") order_qt_query3_4_after "${query3_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_4""" @@ -397,7 +359,7 @@ suite("inner_join_dphyp") { "inner join orders on lineitem.l_orderkey = orders.o_orderkey " + "where o_orderstatus = 'o' AND l_linenumber in (1, 2, 3, 4, 5) " order_qt_query4_0_before "${query4_0}" - check_rewrite(mv4_0, query4_0, "mv4_0") + check_mv_rewrite_success(db, mv4_0, query4_0, "mv4_0") order_qt_query4_0_after "${query4_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0""" @@ -411,7 +373,7 @@ suite("inner_join_dphyp") { "from (select * from lineitem where l_linenumber > 1) t1 " + "inner join orders on t1.l_orderkey = orders.O_ORDERKEY " order_qt_query5_0_before "${query5_0}" - check_rewrite(mv5_0, query5_0, "mv5_0") + check_mv_rewrite_success(db, mv5_0, query5_0, "mv5_0") order_qt_query5_0_after "${query5_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_0""" @@ -426,7 +388,7 @@ suite("inner_join_dphyp") { "inner join (select * from orders where o_orderdate = '2023-12-08') t2 " + "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " order_qt_query6_0_before "${query6_0}" - check_rewrite(mv6_0, query6_0, "mv6_0") + check_mv_rewrite_success(db, mv6_0, query6_0, "mv6_0") order_qt_query6_0_after "${query6_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_0""" @@ -443,7 +405,7 @@ suite("inner_join_dphyp") { "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " + "where l_partkey = 2" order_qt_query7_0_before "${query7_0}" - check_rewrite(mv7_0, query7_0, "mv7_0") + check_mv_rewrite_success(db, mv7_0, query7_0, "mv7_0") order_qt_query7_0_after "${query7_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_0""" @@ -458,7 +420,7 @@ suite("inner_join_dphyp") { "WHERE lineitem.L_LINENUMBER > 0 AND orders.O_CUSTKEY = 1 AND " + "orders.O_SHIPPRIORITY = 2" order_qt_query10_0_before "${query10_0}" - check_not_match(mv10_0, query10_0, "mv10_0") + check_mv_rewrite_fail(db, mv10_0, query10_0, "mv10_0") order_qt_query10_0_after "${query10_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv10_0""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy index dc9e78e00c6..69ffd60485b 100644 --- a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy @@ -125,44 +125,6 @@ suite("outer_join_dphyp") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // without filter def mv1_0 = "select lineitem.L_LINENUMBER, orders.O_CUSTKEY " + "from lineitem " + @@ -171,7 +133,7 @@ suite("outer_join_dphyp") { "from lineitem " + "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_0_before "${query1_0}" - check_rewrite(mv1_0, query1_0, "mv1_0") + check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0") order_qt_query1_0_after "${query1_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0""" @@ -187,7 +149,7 @@ suite("outer_join_dphyp") { "left join partsupp on lineitem.L_PARTKEY = partsupp.PS_PARTKEY " + "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY" order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -199,7 +161,7 @@ suite("outer_join_dphyp") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_2_before "${query1_2}" // join direction is not same, should not match - check_not_match(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_fail(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -212,7 +174,7 @@ suite("outer_join_dphyp") { "from orders " + "left join lineitem on orders.O_ORDERKEY = lineitem.L_ORDERKEY" order_qt_query1_3_before "${query1_3}" - check_rewrite(mv1_3, query1_3, "mv1_3") + check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3") order_qt_query1_3_after "${query1_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3""" @@ -226,7 +188,7 @@ suite("outer_join_dphyp") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 0" order_qt_query2_0_before "${query2_0}" - check_not_match(mv2_0, query2_0, "mv2_0") + check_mv_rewrite_fail(db, mv2_0, query2_0, "mv2_0") order_qt_query2_0_after "${query2_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0""" @@ -239,7 +201,7 @@ suite("outer_join_dphyp") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1" order_qt_query2_1_before "${query2_1}" - check_rewrite(mv2_1, query2_1, "mv2_1") + check_mv_rewrite_success(db, mv2_1, query2_1, "mv2_1") order_qt_query2_1_after "${query2_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1""" @@ -256,7 +218,7 @@ suite("outer_join_dphyp") { where lineitem.L_LINENUMBER > 1 and l_suppkey = 3; """ order_qt_query2_2_before "${query2_2}" - check_not_match(mv2_2, query2_2, "mv2_2") + check_mv_rewrite_fail(db, mv2_2, query2_2, "mv2_2") order_qt_query2_2_after "${query2_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2""" @@ -273,7 +235,7 @@ suite("outer_join_dphyp") { where lineitem.L_LINENUMBER > 1 and l_suppkey = 3; """ order_qt_query2_3_before "${query2_3}" - check_rewrite(mv2_3, query2_3, "mv2_3") + check_mv_rewrite_success(db, mv2_3, query2_3, "mv2_3") order_qt_query2_3_after "${query2_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3""" @@ -292,7 +254,7 @@ suite("outer_join_dphyp") { """ order_qt_query3_0_before "${query3_0}" // use a filed not from mv, should not success - check_not_match(mv3_0, query3_0, "mv3_0") + check_mv_rewrite_fail(db, mv3_0, query3_0, "mv3_0") order_qt_query3_0_after "${query3_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0""" @@ -305,7 +267,7 @@ suite("outer_join_dphyp") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_1_before "${query3_1}" - check_rewrite(mv3_1, query3_1, "mv3_1") + check_mv_rewrite_success(db, mv3_1, query3_1, "mv3_1") order_qt_query3_1_after "${query3_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1""" @@ -321,7 +283,7 @@ suite("outer_join_dphyp") { "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_2_before "${query3_2}" // should not success, as mv filter is under left outer input - check_rewrite(mv3_2, query3_2, "mv3_2") + check_mv_rewrite_success(db, mv3_2, query3_2, "mv3_2") order_qt_query3_2_after "${query3_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2""" @@ -339,7 +301,7 @@ suite("outer_join_dphyp") { where o_orderstatus = 'o' AND o_orderkey = 1; """ order_qt_query4_0_before "${query4_0}" - check_rewrite(mv4_0, query4_0, "mv4_0") + check_mv_rewrite_success(db, mv4_0, query4_0, "mv4_0") order_qt_query4_0_after "${query4_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0""" @@ -353,7 +315,7 @@ suite("outer_join_dphyp") { "from (select * from lineitem where l_linenumber > 1) t1 " + "left join orders on t1.l_orderkey = orders.O_ORDERKEY " order_qt_query5_0_before "${query5_0}" - check_rewrite(mv5_0, query5_0, "mv5_0") + check_mv_rewrite_success(db, mv5_0, query5_0, "mv5_0") order_qt_query5_0_after "${query5_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_0""" @@ -372,7 +334,7 @@ suite("outer_join_dphyp") { where o_orderdate = '2023-12-08' """ order_qt_query5_1_before "${query5_1}" - check_not_match(mv5_1, query5_1, "mv5_1") + check_mv_rewrite_fail(db, mv5_1, query5_1, "mv5_1") order_qt_query5_1_after "${query5_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_1""" @@ -387,7 +349,7 @@ suite("outer_join_dphyp") { "left join (select * from orders where o_orderdate = '2023-12-08') t2 " + "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " order_qt_query6_0_before "${query6_0}" - check_rewrite(mv6_0, query6_0, "mv6_0") + check_mv_rewrite_success(db, mv6_0, query6_0, "mv6_0") order_qt_query6_0_after "${query6_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_0""" @@ -404,7 +366,7 @@ suite("outer_join_dphyp") { "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " + "where l_partkey = 3" order_qt_query7_0_before "${query7_0}" - check_rewrite(mv7_0, query7_0, "mv7_0") + check_mv_rewrite_success(db, mv7_0, query7_0, "mv7_0") order_qt_query7_0_after "${query7_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_0""" @@ -423,7 +385,7 @@ suite("outer_join_dphyp") { on t1.l_orderkey = orders.o_orderkey; """ order_qt_query7_1_before "${query7_1}" - check_not_match(mv7_1, query7_1, "mv7_1") + check_mv_rewrite_fail(db, mv7_1, query7_1, "mv7_1") order_qt_query7_1_after "${query7_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_1""" @@ -463,7 +425,7 @@ suite("outer_join_dphyp") { group by a.o_orderkey; """ order_qt_query8_0_before "${query8_0}" - check_rewrite(mv8_0, query8_0, "mv8_0") + check_mv_rewrite_success(db, mv8_0, query8_0, "mv8_0") order_qt_query8_0_after "${query8_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv8_0""" @@ -523,7 +485,7 @@ suite("outer_join_dphyp") { t1.col1 """ order_qt_query9_0_before "${query9_0}" - check_rewrite(mv9_0, query9_0, "mv9_0") + check_mv_rewrite_success(db, mv9_0, query9_0, "mv9_0") order_qt_query9_0_after "${query9_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv9_0""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join.groovy b/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join.groovy index ed50e19c605..e34c338a7f3 100644 --- a/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/join/inner/inner_join.groovy @@ -124,44 +124,6 @@ suite("inner_join") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // without filter def mv1_0 = "select lineitem.L_LINENUMBER, orders.O_CUSTKEY " + "from lineitem " + @@ -170,7 +132,7 @@ suite("inner_join") { "from lineitem " + "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_0_before "${query1_0}" - check_rewrite(mv1_0, query1_0, "mv1_0") + check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0") order_qt_query1_0_after "${query1_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0""" @@ -186,7 +148,7 @@ suite("inner_join") { "inner join partsupp on lineitem.L_PARTKEY = partsupp.PS_PARTKEY " + "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY" order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -198,7 +160,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_2_before "${query1_2}" // join direction is not same, should not match - check_rewrite(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_success(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -211,7 +173,7 @@ suite("inner_join") { "from orders " + "inner join lineitem on orders.O_ORDERKEY = lineitem.L_ORDERKEY" order_qt_query1_3_before "${query1_3}" - check_rewrite(mv1_3, query1_3, "mv1_3") + check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3") order_qt_query1_3_after "${query1_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3""" @@ -230,7 +192,7 @@ suite("inner_join") { and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY; """ order_qt_query1_4_before "${query1_4}" - check_rewrite(mv1_4, query1_4, "mv1_4") + check_mv_rewrite_success(db, mv1_4, query1_4, "mv1_4") order_qt_query1_4_after "${query1_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_4""" @@ -246,7 +208,7 @@ suite("inner_join") { and o_shippriority = l_partkey; """ order_qt_query1_5_before "${query1_5}" - check_rewrite(mv1_5, query1_5, "mv1_5") + check_mv_rewrite_success(db, mv1_5, query1_5, "mv1_5") order_qt_query1_5_after "${query1_5}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_5""" @@ -259,7 +221,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 0" order_qt_query2_0_before "${query2_0}" - check_rewrite(mv2_0, query2_0, "mv2_0") + check_mv_rewrite_success(db, mv2_0, query2_0, "mv2_0") order_qt_query2_0_after "${query2_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0""" @@ -272,7 +234,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1" order_qt_query2_1_before "${query2_1}" - check_rewrite(mv2_1, query2_1, "mv2_1") + check_mv_rewrite_success(db, mv2_1, query2_1, "mv2_1") order_qt_query2_1_after "${query2_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1""" @@ -285,7 +247,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1 and l_suppkey = 3" order_qt_query2_2_before "${query2_2}" - check_rewrite(mv2_2, query2_2, "mv2_2") + check_mv_rewrite_success(db, mv2_2, query2_2, "mv2_2") order_qt_query2_2_after "${query2_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2""" @@ -302,7 +264,7 @@ suite("inner_join") { "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY " + "where lineitem.L_LINENUMBER > 1 and l_suppkey = 3 " order_qt_query2_3_before "${query2_3}" - check_rewrite(mv2_3, query2_3, "mv2_3") + check_mv_rewrite_success(db, mv2_3, query2_3, "mv2_3") order_qt_query2_3_after "${query2_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3""" @@ -317,7 +279,7 @@ suite("inner_join") { "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_0_before "${query3_0}" // use a filed not from mv, should not success - check_not_match(mv3_0, query3_0, "mv3_0") + check_mv_rewrite_fail(db, mv3_0, query3_0, "mv3_0") order_qt_query3_0_after "${query3_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0""" @@ -330,7 +292,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_1_before "${query3_1}" - check_rewrite(mv3_1, query3_1, "mv3_1") + check_mv_rewrite_success(db, mv3_1, query3_1, "mv3_1") order_qt_query3_1_after "${query3_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1""" @@ -345,7 +307,7 @@ suite("inner_join") { "inner join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_2_before "${query3_2}" - check_rewrite(mv3_2, query3_2, "mv3_2") + check_mv_rewrite_success(db, mv3_2, query3_2, "mv3_2") order_qt_query3_2_after "${query3_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2""" @@ -362,7 +324,7 @@ suite("inner_join") { "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY " + "where o_custkey in (1, 2, 3, 4) " order_qt_query3_3_before "${query3_3}" - check_rewrite(mv3_3, query3_3, "mv3_3") + check_mv_rewrite_success(db, mv3_3, query3_3, "mv3_3") order_qt_query3_3_after "${query3_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_3""" @@ -382,7 +344,7 @@ suite("inner_join") { where o_custkey = 1 and l_linenumber > 0; """ order_qt_query3_4_before "${query3_4}" - check_rewrite(mv3_4, query3_4, "mv3_4") + check_mv_rewrite_success(db, mv3_4, query3_4, "mv3_4") order_qt_query3_4_after "${query3_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_4""" @@ -396,7 +358,7 @@ suite("inner_join") { "inner join orders on lineitem.l_orderkey = orders.o_orderkey " + "where o_orderstatus = 'o' AND l_linenumber in (1, 2, 3, 4, 5) " order_qt_query4_0_before "${query4_0}" - check_rewrite(mv4_0, query4_0, "mv4_0") + check_mv_rewrite_success(db, mv4_0, query4_0, "mv4_0") order_qt_query4_0_after "${query4_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0""" @@ -410,7 +372,7 @@ suite("inner_join") { "from (select * from lineitem where l_linenumber > 1) t1 " + "inner join orders on t1.l_orderkey = orders.O_ORDERKEY " order_qt_query5_0_before "${query5_0}" - check_rewrite(mv5_0, query5_0, "mv5_0") + check_mv_rewrite_success(db, mv5_0, query5_0, "mv5_0") order_qt_query5_0_after "${query5_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_0""" @@ -425,7 +387,7 @@ suite("inner_join") { "inner join (select * from orders where o_orderdate = '2023-12-08') t2 " + "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " order_qt_query6_0_before "${query6_0}" - check_rewrite(mv6_0, query6_0, "mv6_0") + check_mv_rewrite_success(db, mv6_0, query6_0, "mv6_0") order_qt_query6_0_after "${query6_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_0""" @@ -442,7 +404,7 @@ suite("inner_join") { "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " + "where l_partkey = 2" order_qt_query7_0_before "${query7_0}" - check_rewrite(mv7_0, query7_0, "mv7_0") + check_mv_rewrite_success(db, mv7_0, query7_0, "mv7_0") order_qt_query7_0_after "${query7_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_0""" @@ -457,7 +419,7 @@ suite("inner_join") { "WHERE lineitem.L_LINENUMBER > 0 AND orders.O_CUSTKEY = 1 AND " + "orders.O_SHIPPRIORITY = 2" order_qt_query10_0_before "${query10_0}" - check_not_match(mv10_0, query10_0, "mv10_0") + check_mv_rewrite_fail(db, mv10_0, query10_0, "mv10_0") order_qt_query10_0_after "${query10_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv10_0""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy b/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy index 6a5bda9d22b..e2b9a8297ee 100644 --- a/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy @@ -202,44 +202,6 @@ suite("outer_join") { (2, 3, 10, 11.01, 'supply2'); """ - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - // without filter def mv1_0 = "select lineitem.L_LINENUMBER, orders.O_CUSTKEY " + "from lineitem " + @@ -248,7 +210,7 @@ suite("outer_join") { "from lineitem " + "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_0_before "${query1_0}" - check_rewrite(mv1_0, query1_0, "mv1_0") + check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0") order_qt_query1_0_after "${query1_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0""" @@ -264,7 +226,7 @@ suite("outer_join") { "left join partsupp on lineitem.L_PARTKEY = partsupp.PS_PARTKEY " + "and lineitem.L_SUPPKEY = partsupp.PS_SUPPKEY" order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -276,7 +238,7 @@ suite("outer_join") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " order_qt_query1_2_before "${query1_2}" // join direction is not same, should not match - check_not_match(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_fail(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -289,7 +251,7 @@ suite("outer_join") { "from orders " + "left join lineitem on orders.O_ORDERKEY = lineitem.L_ORDERKEY" order_qt_query1_3_before "${query1_3}" - check_rewrite(mv1_3, query1_3, "mv1_3") + check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3") order_qt_query1_3_after "${query1_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3""" @@ -303,7 +265,7 @@ suite("outer_join") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 0" order_qt_query2_0_before "${query2_0}" - check_not_match(mv2_0, query2_0, "mv2_0") + check_mv_rewrite_fail(db, mv2_0, query2_0, "mv2_0") order_qt_query2_0_after "${query2_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0""" @@ -316,7 +278,7 @@ suite("outer_join") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where lineitem.L_LINENUMBER > 1" order_qt_query2_1_before "${query2_1}" - check_rewrite(mv2_1, query2_1, "mv2_1") + check_mv_rewrite_success(db, mv2_1, query2_1, "mv2_1") order_qt_query2_1_after "${query2_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1""" @@ -333,7 +295,7 @@ suite("outer_join") { where lineitem.L_LINENUMBER > 1 and l_suppkey = 3; """ order_qt_query2_2_before "${query2_2}" - check_not_match(mv2_2, query2_2, "mv2_2") + check_mv_rewrite_fail(db, mv2_2, query2_2, "mv2_2") order_qt_query2_2_after "${query2_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2""" @@ -350,7 +312,7 @@ suite("outer_join") { where lineitem.L_LINENUMBER > 1 and l_suppkey = 3; """ order_qt_query2_3_before "${query2_3}" - check_rewrite(mv2_3, query2_3, "mv2_3") + check_mv_rewrite_success(db, mv2_3, query2_3, "mv2_3") order_qt_query2_3_after "${query2_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3""" @@ -369,7 +331,7 @@ suite("outer_join") { """ order_qt_query3_0_before "${query3_0}" // use a filed not from mv, should not success - check_not_match(mv3_0, query3_0, "mv3_0") + check_mv_rewrite_fail(db, mv3_0, query3_0, "mv3_0") order_qt_query3_0_after "${query3_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0""" @@ -382,7 +344,7 @@ suite("outer_join") { "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " + "where orders.O_ORDERSTATUS = 'o'" order_qt_query3_1_before "${query3_1}" - check_rewrite(mv3_1, query3_1, "mv3_1") + check_mv_rewrite_success(db, mv3_1, query3_1, "mv3_1") order_qt_query3_1_after "${query3_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1""" @@ -402,7 +364,7 @@ suite("outer_join") { """ order_qt_query3_2_before "${query3_2}" // should not success, as mv filter is under left outer input - check_rewrite(mv3_2, query3_2, "mv3_2") + check_mv_rewrite_success(db, mv3_2, query3_2, "mv3_2") order_qt_query3_2_after "${query3_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2""" @@ -420,7 +382,7 @@ suite("outer_join") { where o_orderstatus = 'o' AND o_orderkey = 1; """ order_qt_query4_0_before "${query4_0}" - check_rewrite(mv4_0, query4_0, "mv4_0") + check_mv_rewrite_success(db, mv4_0, query4_0, "mv4_0") order_qt_query4_0_after "${query4_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0""" @@ -434,7 +396,7 @@ suite("outer_join") { "from (select * from lineitem where l_linenumber > 1) t1 " + "left join orders on t1.l_orderkey = orders.O_ORDERKEY " order_qt_query5_0_before "${query5_0}" - check_rewrite(mv5_0, query5_0, "mv5_0") + check_mv_rewrite_success(db, mv5_0, query5_0, "mv5_0") order_qt_query5_0_after "${query5_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_0""" @@ -453,7 +415,7 @@ suite("outer_join") { where o_orderdate = '2023-12-08' """ order_qt_query5_1_before "${query5_1}" - check_not_match(mv5_1, query5_1, "mv5_1") + check_mv_rewrite_fail(db, mv5_1, query5_1, "mv5_1") order_qt_query5_1_after "${query5_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_1""" @@ -468,7 +430,7 @@ suite("outer_join") { "left join (select * from orders where o_orderdate = '2023-12-08') t2 " + "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " order_qt_query6_0_before "${query6_0}" - check_rewrite(mv6_0, query6_0, "mv6_0") + check_mv_rewrite_success(db, mv6_0, query6_0, "mv6_0") order_qt_query6_0_after "${query6_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_0""" @@ -487,7 +449,7 @@ suite("outer_join") { where l_shipdate = '2023-12-10' and o_orderdate = '2023-12-10'; """ order_qt_query6_1_before "${query6_1}" - check_rewrite(mv6_1, query6_1, "mv6_1") + check_mv_rewrite_success(db, mv6_1, query6_1, "mv6_1") order_qt_query6_1_after "${query6_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_1""" @@ -507,7 +469,7 @@ suite("outer_join") { where o_orderdate = '2023-12-10' order by 1, 2, 3, 4, 5; """ order_qt_query6_2_before "${query6_2}" - check_rewrite(mv6_2, query6_2, "mv6_2") + check_mv_rewrite_success(db, mv6_2, query6_2, "mv6_2") order_qt_query6_2_after "${query6_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_2""" @@ -524,7 +486,7 @@ suite("outer_join") { "on t1.l_orderkey = o_orderkey and t1.l_shipdate = o_orderdate " + "where l_partkey = 3" order_qt_query7_0_before "${query7_0}" - check_rewrite(mv7_0, query7_0, "mv7_0") + check_mv_rewrite_success(db, mv7_0, query7_0, "mv7_0") order_qt_query7_0_after "${query7_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_0""" @@ -543,7 +505,7 @@ suite("outer_join") { on t1.l_orderkey = orders.o_orderkey; """ order_qt_query7_1_before "${query7_1}" - check_not_match(mv7_1, query7_1, "mv7_1") + check_mv_rewrite_fail(db, mv7_1, query7_1, "mv7_1") order_qt_query7_1_after "${query7_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv7_1""" @@ -583,7 +545,7 @@ suite("outer_join") { group by a.o_orderkey; """ order_qt_query8_0_before "${query8_0}" - check_rewrite(mv8_0, query8_0, "mv8_0") + check_mv_rewrite_success(db, mv8_0, query8_0, "mv8_0") order_qt_query8_0_after "${query8_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv8_0""" @@ -644,7 +606,7 @@ suite("outer_join") { t1.col1 """ order_qt_query9_0_before "${query9_0}" - check_rewrite(mv9_0, query9_0, "mv9_0") + check_mv_rewrite_success(db, mv9_0, query9_0, "mv9_0") order_qt_query9_0_after "${query9_0}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv9_0""" } diff --git a/regression-test/suites/nereids_rules_p0/mv/ssb/mv_ssb_test.groovy b/regression-test/suites/nereids_rules_p0/mv/ssb/mv_ssb_test.groovy index 887907c651f..7bbb8f19da9 100644 --- a/regression-test/suites/nereids_rules_p0/mv/ssb/mv_ssb_test.groovy +++ b/regression-test/suites/nereids_rules_p0/mv/ssb/mv_ssb_test.groovy @@ -92,45 +92,6 @@ suite("mv_ssb_test") { sql "SET enable_materialized_view_rewrite=true" sql "SET enable_nereids_timeout = false" - def check_rewrite = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - contains("${mv_name}(${mv_name})") - } - } - - def check_not_match = { mv_sql, query_sql, mv_name -> - - sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}""" - sql""" - CREATE MATERIALIZED VIEW ${mv_name} - BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL - DISTRIBUTED BY RANDOM BUCKETS 2 - PROPERTIES ('replication_num' = '1') - AS ${mv_sql} - """ - - def job_name = getJobName(db, mv_name); - waitingMTMVTaskFinished(job_name) - explain { - sql("${query_sql}") - notContains("${mv_name}(${mv_name})") - } - } - - def mv1_1 = """ SELECT SUM(lo_extendedprice*lo_discount) AS REVENUE @@ -150,7 +111,7 @@ suite("mv_ssb_test") { AND lo_quantity < 25; """ order_qt_query1_1_before "${query1_1}" - check_rewrite(mv1_1, query1_1, "mv1_1") + check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1") order_qt_query1_1_after "${query1_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1""" @@ -174,7 +135,7 @@ suite("mv_ssb_test") { AND lo_quantity BETWEEN 26 AND 35; """ order_qt_query1_2_before "${query1_2}" - check_rewrite(mv1_2, query1_2, "mv1_2") + check_mv_rewrite_success(db, mv1_2, query1_2, "mv1_2") order_qt_query1_2_after "${query1_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2""" @@ -199,7 +160,7 @@ suite("mv_ssb_test") { AND lo_quantity BETWEEN 26 AND 35; """ order_qt_query1_3before "${query1_3}" - check_rewrite(mv1_3, query1_3, "mv1_3") + check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3") order_qt_query1_3_after "${query1_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3""" @@ -227,7 +188,7 @@ suite("mv_ssb_test") { ORDER BY d_year, p_brand; """ order_qt_query2_1before "${query2_1}" - check_rewrite(mv2_1, query2_1, "mv2_1") + check_mv_rewrite_success(db, mv2_1, query2_1, "mv2_1") order_qt_query2_1_after "${query2_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1""" @@ -257,7 +218,7 @@ suite("mv_ssb_test") { ORDER BY d_year, p_brand; """ order_qt_query2_2before "${query2_2}" - check_rewrite(mv2_2, query2_2, "mv2_2") + check_mv_rewrite_success(db, mv2_2, query2_2, "mv2_2") order_qt_query2_2_after "${query2_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2""" @@ -284,7 +245,7 @@ suite("mv_ssb_test") { ORDER BY d_year, p_brand; """ order_qt_query2_3before "${query2_3}" - check_rewrite(mv2_3, query2_3, "mv2_3") + check_mv_rewrite_success(db, mv2_3, query2_3, "mv2_3") order_qt_query2_3_after "${query2_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3""" @@ -315,7 +276,7 @@ suite("mv_ssb_test") { ORDER BY d_year ASC, REVENUE DESC; """ order_qt_query3_1before "${query3_1}" - check_rewrite(mv3_1, query3_1, "mv3_1") + check_mv_rewrite_success(db, mv3_1, query3_1, "mv3_1") order_qt_query3_1_after "${query3_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1""" @@ -347,7 +308,7 @@ suite("mv_ssb_test") { ORDER BY d_year ASC, REVENUE DESC; """ order_qt_query3_2before "${query3_2}" - check_rewrite(mv3_2, query3_2, "mv3_2") + check_mv_rewrite_success(db, mv3_2, query3_2, "mv3_2") order_qt_query3_2_after "${query3_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2""" @@ -383,7 +344,7 @@ suite("mv_ssb_test") { ORDER BY d_year ASC, REVENUE DESC; """ order_qt_query3_3before "${query3_3}" - check_rewrite(mv3_3, query3_3, "mv3_3") + check_mv_rewrite_success(db, mv3_3, query3_3, "mv3_3") order_qt_query3_3_after "${query3_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_3""" @@ -419,7 +380,7 @@ suite("mv_ssb_test") { ORDER BY d_year ASC, REVENUE DESC; """ order_qt_query3_4before "${query3_4}" - check_rewrite(mv3_4, query3_4, "mv3_4") + check_mv_rewrite_success(db, mv3_4, query3_4, "mv3_4") order_qt_query3_4_after "${query3_4}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_4""" @@ -455,7 +416,7 @@ suite("mv_ssb_test") { ORDER BY d_year, c_nation; """ order_qt_query4_1before "${query4_1}" - check_rewrite(mv4_1, query4_1, "mv4_1") + check_mv_rewrite_success(db, mv4_1, query4_1, "mv4_1") order_qt_query4_1_after "${query4_1}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_1""" @@ -492,7 +453,7 @@ suite("mv_ssb_test") { ORDER BY d_year, s_nation, p_category; """ order_qt_query4_2before "${query4_2}" - check_rewrite(mv4_2, query4_2, "mv4_2") + check_mv_rewrite_success(db, mv4_2, query4_2, "mv4_2") order_qt_query4_2_after "${query4_2}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_2""" @@ -526,7 +487,7 @@ suite("mv_ssb_test") { ORDER BY d_year, s_city, p_brand; """ order_qt_query4_3before "${query4_3}" - check_rewrite(mv4_3, query4_3, "mv4_3") + check_mv_rewrite_success(db, mv4_3, query4_3, "mv4_3") order_qt_query4_3_after "${query4_3}" sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_3""" } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
