You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/02/04 14:22:03 UTC
(doris) 02/08: [test](mtmv) Add materialized view availability regression test (#30769)
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 <14...@users.noreply.github.com>
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: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org