This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new a12d20fffae branch-4.0: [test](nereids)fix sql cache of mtmv test case
#57177 (#60733)
a12d20fffae is described below
commit a12d20fffae27a7bb9bdf9062a8ed7490a13ba01
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Sat Feb 14 10:57:56 2026 +0800
branch-4.0: [test](nereids)fix sql cache of mtmv test case #57177 (#60733)
Cherry-picked from #57177
Co-authored-by: zfr95 <[email protected]>
---
.../nereids_p0/cache/mtmv_with_sql_cache.groovy | 1178 ++++++++++++++------
1 file changed, 853 insertions(+), 325 deletions(-)
diff --git a/regression-test/suites/nereids_p0/cache/mtmv_with_sql_cache.groovy
b/regression-test/suites/nereids_p0/cache/mtmv_with_sql_cache.groovy
index bd31e46373e..d10b015a553 100644
--- a/regression-test/suites/nereids_p0/cache/mtmv_with_sql_cache.groovy
+++ b/regression-test/suites/nereids_p0/cache/mtmv_with_sql_cache.groovy
@@ -16,9 +16,7 @@
// under the License.
import com.google.common.util.concurrent.Uninterruptibles
-
import java.util.concurrent.TimeUnit
-import java.util.stream.Collectors
/*
When to use a direct query and when to use a query rewrite?
@@ -49,7 +47,6 @@ class CanRetryException extends IllegalStateException {
}
}
-
suite("mtmv_with_sql_cache") {
withGlobalLock("cache_last_version_interval_second") {
@@ -95,11 +92,11 @@ suite("mtmv_with_sql_cache") {
sql """DROP MATERIALIZED VIEW IF EXISTS ${db}.${mv_name}"""
sql"""
- CREATE MATERIALIZED VIEW ${db}.${mv_name}
- BUILD IMMEDIATE REFRESH auto ON MANUAL
- PARTITION BY ${partition_col}
- DISTRIBUTED BY RANDOM BUCKETS 2
- PROPERTIES ('replication_num' = '1')
+ CREATE MATERIALIZED VIEW ${db}.${mv_name}
+ BUILD IMMEDIATE REFRESH auto ON MANUAL
+ PARTITION BY ${partition_col}
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
AS ${mv_sql}
"""
def job_name = getJobName(db, mv_name);
@@ -130,327 +127,858 @@ suite("mtmv_with_sql_cache") {
}
String dbName = context.config.getDbNameByFile(context.file)
- def prefix_str = "mtmv_with_sql_cache_"
- def tb_name1 = prefix_str + "table1"
- def tb_name2 = prefix_str + "table2"
-
- def mv_name1 = prefix_str + "mtmv1"
- def mv_name2 = prefix_str + "mtmv2"
- def mv_name3 = prefix_str + "mtmv3"
- def mv_name4 = prefix_str + "mtmv4"
- def nested_mv_name1 = prefix_str + "nested_mtmv1"
- def mtmv_sql1 = """
- select t1.id as id, t2.value as value
- from ${tb_name1} as t1
- left join ${tb_name2} as t2
- on t1.id = t2.id
- """
- def mtmv_sql2 = """
- select t2.id as id, t2.value as value
- from ${tb_name1} as t1
- right join ${tb_name2} as t2
- on t1.id = t2.id
- """
- def mtmv_sql3 = """
- select t2.id as id, t1.value as value1
- from ${tb_name1} as t1
- right join ${tb_name2} as t2
- on t1.id = t2.id
- """
- def mtmv_sql4 = """
- select t1.id as id, t1.value as value1
- from ${tb_name1} as t1
- left join ${tb_name2} as t2
- on t1.id = t2.id
- """
- def nested_mtmv_sql1 = """
- select t1.id as id, t2.value as value
- from ${mv_name1} as t1
- left join ${mv_name2} as t2
- on t1.id = t2.id
- """
- def nested_mtmv_sql3 = """
- select t1.id as id, t2.value as value
- from ${mv_name3} as t1
- left join ${mv_name2} as t2
- on t1.id = t2.id
- """
for (def __ in 0..3) {
- retryTestSqlCache(3, 1000) {
- createTestTable tb_name1
- createTestTable tb_name2
-
- sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name1};"""
- sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name2};"""
- sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name3};"""
- sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name4};"""
- sql """DROP MATERIALIZED VIEW IF EXISTS ${nested_mv_name1};"""
- cur_create_async_partition_mv(dbName, mv_name1, mtmv_sql1,
"(id)")
- cur_create_async_partition_mv(dbName, mv_name2, mtmv_sql2,
"(id)")
- cur_create_async_partition_mv(dbName, mv_name4, mtmv_sql4,
"(id)")
- cur_create_async_partition_mv(dbName, nested_mv_name1,
nested_mtmv_sql1, "(id)")
-
- sleep(10000)
- sql "set enable_nereids_planner=true"
- sql "set enable_fallback_to_original_planner=false"
- sql "set enable_sql_cache=true"
-
- // Direct Query
- assertNoCache "select * from ${mv_name1}"
- assertNoCache "select * from ${mv_name2}"
- assertNoCache "select * from ${mv_name4}"
- assertNoCache "select * from ${nested_mv_name1}"
- // mtmv rewrite
- assertNoCache mtmv_sql1
- assertNoCache mtmv_sql2
- assertNoCache mtmv_sql4
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache "select * from ${mv_name1}"
- retryUntilHasSqlCache "select * from ${mv_name2}"
- retryUntilHasSqlCache "select * from ${mv_name4}"
- retryUntilHasSqlCache "select * from ${nested_mv_name1}"
- retryUntilHasSqlCache mtmv_sql1
- retryUntilHasSqlCache mtmv_sql2
- retryUntilHasSqlCache mtmv_sql4
- retryUntilHasSqlCache nested_mtmv_sql1
-
- assertHasCache "select * from ${mv_name1}"
- assertHasCache "select * from ${mv_name2}"
- assertHasCache "select * from ${mv_name4}"
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache mtmv_sql1
- assertHasCache mtmv_sql2
- assertHasCache mtmv_sql4
- assertHasCache nested_mtmv_sql1
-
- // rename mtmv
- sql """ALTER MATERIALIZED VIEW ${mv_name1} rename
${mv_name3};"""
- assertNoCache "select * from ${mv_name3}"
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql3
-
- sql """ALTER MATERIALIZED VIEW ${mv_name3} rename
${mv_name1};"""
- assertHasCache "select * from ${mv_name1}" // Since this SQL
query hasn't been executed before, so it's still valid now.
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}" // nested
mtmv don't change
- assertHasCache nested_mtmv_sql1 // Since this SQL query hasn't
been executed before, so it's still valid now.
-
- retryUntilHasSqlCache mtmv_sql1
- assertHasCache mtmv_sql1
-
- // replace mtmv
- sql """ALTER MATERIALIZED VIEW ${mv_name1} REPLACE WITH
MATERIALIZED VIEW ${mv_name2};"""
- assertNoCache "select * from ${mv_name1}"
- assertNoCache "select * from ${mv_name2}"
- assertNoCache mtmv_sql1
- assertNoCache mtmv_sql2// --> "select * from mv1/mv2" -->
version change --> nocache
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- sql """ALTER MATERIALIZED VIEW ${mv_name2} REPLACE WITH
MATERIALIZED VIEW ${mv_name1};"""
- assertNoCache "select * from ${mv_name1}"
- assertNoCache "select * from ${mv_name2}"
- assertNoCache mtmv_sql1
- assertNoCache mtmv_sql2 // --> "select * from mv1/mv2" -->
version change --> nocache
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache "select * from ${mv_name1}"
- retryUntilHasSqlCache "select * from ${mv_name2}"
- retryUntilHasSqlCache mtmv_sql1
- retryUntilHasSqlCache mtmv_sql2
- retryUntilHasSqlCache "select * from ${nested_mv_name1}"
- retryUntilHasSqlCache nested_mtmv_sql1
-
- assertHasCache "select * from ${mv_name1}"
- assertHasCache "select * from ${mv_name2}"
- assertHasCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- // pause/resume mtmv don't affect the SQL cache's operation.
- sql """PAUSE MATERIALIZED VIEW JOB ON ${mv_name1};"""
- assertHasCache "select * from ${mv_name1}"
- assertHasCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- sql """PAUSE MATERIALIZED VIEW JOB ON ${mv_name4};"""
- assertHasCache "select * from ${mv_name4}"
- assertHasCache mtmv_sql4
-
- sql """RESUME MATERIALIZED VIEW JOB ON ${mv_name1};"""
- assertHasCache "select * from ${mv_name1}"
- assertHasCache mtmv_sql1
-
- sql """RESUME MATERIALIZED VIEW JOB ON ${mv_name4};"""
- assertHasCache "select * from ${mv_name4}"
- assertHasCache mtmv_sql4
-
- // To refresh the materialized view to ensure its initial
performance is normal.
- sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name1)
-
- sleep(10000)
- assertHasCache "select * from ${mv_name1}"
- assertHasCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- // refresh mtmv complete
- sql "REFRESH MATERIALIZED VIEW ${mv_name1} complete;"
- sleep(10000)
- assertNoCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql1
- retryUntilHasSqlCache "select * from ${mv_name1}"
- retryUntilHasSqlCache nested_mtmv_sql1
-
- assertHasCache mtmv_sql1
- assertHasCache "select * from ${mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- // base table insert overwrite
- sql "INSERT OVERWRITE table ${tb_name1} PARTITION(p5) VALUES
(5, 6);"
- sleep(10000)
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql1
- assertHasCache mtmv_sql1
-
- sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name1)
- sleep(10000)
- assertNoCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache "select * from ${mv_name1}"
- retryUntilHasSqlCache nested_mtmv_sql1
-
- assertHasCache "select * from ${mv_name1}"
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
-
- // add partition
- sql "alter table ${tb_name1} add partition p6
values[('6'),('7'))"
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql1
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql1
- assertHasCache mtmv_sql1
-
- // base table insert data
- sql "insert into ${tb_name1} values(6, 1)"
- sleep(10000)
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql1 // mtmv no work -> directly base
table -> no cache
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1 // nested mtmv no work ->
mtmv cache work -> has cache
-
- retryUntilHasSqlCache mtmv_sql1
-
- // recreate mtmv to add column
- cur_create_async_partition_mv(dbName, mv_name1, mtmv_sql3,
"(id)")
- sleep(10000)
- assertNoCache "select * from ${mv_name1}"
- assertHasCache "select * from ${mv_name2}"
- assertNoCache mtmv_sql1
- assertNoCache mtmv_sql4 // base table change, not hit
mtmv1/mtmv4
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache "select * from ${mv_name1}"
- assertHasCache "select * from ${mv_name1}"
-
- sql "REFRESH MATERIALIZED VIEW ${mv_name2} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name2)
- sleep(10000)
- assertNoCache "select * from ${mv_name2}"
- retryUntilHasSqlCache "select * from ${mv_name2}"
- assertHasCache "select * from ${mv_name2}"
-
- retryUntilHasSqlCache mtmv_sql1
- assertHasCache mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql4
- retryUntilHasSqlCache nested_mtmv_sql1
- assertHasCache mtmv_sql4
- assertHasCache nested_mtmv_sql1
-
- // insert overwrite
- sql "INSERT OVERWRITE table ${tb_name1} PARTITION(p4) VALUES
(4, 6);"
- sleep(10000)
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql4
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql4
- assertHasCache mtmv_sql4
-
- sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name1)
- sleep(10000)
- assertNoCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql3 // base table change -> mtmv no work
-> directly base table -> no cache
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
-
- retryUntilHasSqlCache "select * from ${mv_name1}"
- retryUntilHasSqlCache nested_mtmv_sql1
-
- sql "REFRESH MATERIALIZED VIEW ${mv_name4} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name4)
- sleep(10000)
- assertNoCache mtmv_sql4
-
- assertHasCache "select * from ${mv_name1}"
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- // add partition
- sql "alter table ${tb_name1} add partition p7
values[('7'),('8'))"
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql4
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- retryUntilHasSqlCache mtmv_sql4
- assertHasCache mtmv_sql4
-
- // insert data
- sql "insert into ${tb_name1} values(7, 1)"
- sleep(10000)
- assertHasCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql3
- assertHasCache "select * from ${nested_mv_name1}"
- assertHasCache nested_mtmv_sql1
-
- sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
- waitingMTMVTaskFinishedByMvName(mv_name1)
- sleep(10000)
- assertNoCache "select * from ${mv_name1}"
- assertNoCache mtmv_sql3
- assertHasCache "select * from ${nested_mv_name1}"
- assertNoCache nested_mtmv_sql1
- }
+ combineFutures(
+ extraThread("testRenameMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_rename_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql3 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name3} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // rename mtmv
+ sql """ALTER MATERIALIZED VIEW ${mv_name1} rename
${mv_name3};"""
+ assertNoCache "select * from ${mv_name3}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql3
+
+ sql """ALTER MATERIALIZED VIEW ${mv_name3} rename
${mv_name1};"""
+ assertHasCache "select * from ${mv_name1}" //
Since this SQL query hasn't been executed before, so it's still valid now.
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
// nested mtmv don't change
+ assertHasCache nested_mtmv_sql1 // Since this SQL
query hasn't been executed before, so it's still valid now.
+
+ retryUntilHasSqlCache mtmv_sql1
+ assertHasCache mtmv_sql1
+ }
+ }),
+
+ extraThread("testReplaceMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_replace_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // replace mtmv
+ sql """ALTER MATERIALIZED VIEW ${mv_name1} REPLACE
WITH MATERIALIZED VIEW ${mv_name2};"""
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2// --> "select * from
mv1/mv2" --> version change --> nocache
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql1
+
+ sql """ALTER MATERIALIZED VIEW ${mv_name2} REPLACE
WITH MATERIALIZED VIEW ${mv_name1};"""
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2 // --> "select * from
mv1/mv2" --> version change --> nocache
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+ }
+ }),
+
+ extraThread("testPauseResumeMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_pause_resume_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // pause/resume mtmv don't affect the SQL cache's
operation.
+ sql """PAUSE MATERIALIZED VIEW JOB ON
${mv_name1};"""
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+
+ sql """PAUSE MATERIALIZED VIEW JOB ON
${mv_name4};"""
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache mtmv_sql4
+
+ sql """RESUME MATERIALIZED VIEW JOB ON
${mv_name1};"""
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache mtmv_sql1
+
+ sql """RESUME MATERIALIZED VIEW JOB ON
${mv_name4};"""
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache mtmv_sql4
+ }
+ }),
+ extraThread("testRefreshAutoMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_refresh_auto_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // To refresh the materialized view to ensure its
initial performance is normal.
+ sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
+ waitingMTMVTaskFinishedByMvName(mv_name1)
+
+ sleep(10000)
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+ }
+ }),
+
+ extraThread("testRefreshCompleteMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_refresh_complete_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // refresh mtmv complete
+ sql "REFRESH MATERIALIZED VIEW ${mv_name1}
complete;"
+ sleep(10000)
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache mtmv_sql1
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache nested_mtmv_sql1
+ }
+ }),
+ extraThread("testInsertOverwriteMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_insert_overwrite_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // base table insert overwrite
+ sql "INSERT OVERWRITE table ${tb_name1}
PARTITION(p5) VALUES (5, 6);"
+ sleep(10000)
+ assertHasCache "select * from ${mv_name1}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache mtmv_sql1
+ assertHasCache mtmv_sql1
+
+ sql "REFRESH MATERIALIZED VIEW ${mv_name1} AUTO;"
+ waitingMTMVTaskFinishedByMvName(mv_name1)
+ sleep(30 * 1000)
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+ }
+ }),
+ extraThread("testInsertIntoMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_insert_into_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // add partition
+ sql "alter table ${tb_name1} add partition p6
values[('6'),('7'))"
+ assertHasCache "select * from ${mv_name1}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache mtmv_sql1
+ assertHasCache mtmv_sql1
+
+ // base table insert data
+ sql "insert into ${tb_name1} values(6, 1)"
+ sleep(10000)
+ assertHasCache "select * from ${mv_name1}"
+ assertNoCache mtmv_sql1 // mtmv no work ->
directly base table -> no cache
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache nested_mtmv_sql1 // nested mtmv no
work -> mtmv cache work -> has cache
+
+ retryUntilHasSqlCache mtmv_sql1
+ }
+ }),
+ extraThread("testAddColumnMtmv", {
+ retryTestSqlCache(3, 1000) {
+ def prefix_str = "test_add_column_mtmv_"
+ def tb_name1 = prefix_str + "table1"
+ def tb_name2 = prefix_str + "table2"
+
+ def mv_name1 = prefix_str + "mtmv1"
+ def mv_name2 = prefix_str + "mtmv2"
+ def mv_name3 = prefix_str + "mtmv3"
+ def mv_name4 = prefix_str + "mtmv4"
+ def nested_mv_name1 = prefix_str + "nested_mtmv1"
+ def mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql2 = """
+ select t2.id as id, t2.value as value
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql3 = """
+ select t2.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ right join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def mtmv_sql4 = """
+ select t1.id as id, t1.value as value1
+ from ${tb_name1} as t1
+ left join ${tb_name2} as t2
+ on t1.id = t2.id
+ """
+ def nested_mtmv_sql1 = """
+ select t1.id as id, t2.value as value
+ from ${mv_name1} as t1
+ left join ${mv_name2} as t2
+ on t1.id = t2.id
+ """
+
+ createTestTable tb_name1
+ createTestTable tb_name2
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name1};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name2};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name3};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${mv_name4};"""
+ sql """DROP MATERIALIZED VIEW IF EXISTS
${nested_mv_name1};"""
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql1, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name2,
mtmv_sql2, "(id)")
+ cur_create_async_partition_mv(dbName, mv_name4,
mtmv_sql4, "(id)")
+ cur_create_async_partition_mv(dbName,
nested_mv_name1, nested_mtmv_sql1, "(id)")
+
+ sleep(10000)
+ sql "set enable_nereids_planner=true"
+ sql "set enable_fallback_to_original_planner=false"
+ sql "set enable_sql_cache=true"
+
+ // Direct Query
+ assertNoCache "select * from ${mv_name1}"
+ assertNoCache "select * from ${mv_name2}"
+ assertNoCache "select * from ${mv_name4}"
+ assertNoCache "select * from ${nested_mv_name1}"
+ // mtmv rewrite
+ assertNoCache mtmv_sql1
+ assertNoCache mtmv_sql2
+ assertNoCache mtmv_sql4
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ retryUntilHasSqlCache "select * from ${mv_name2}"
+ retryUntilHasSqlCache "select * from ${mv_name4}"
+ retryUntilHasSqlCache "select * from
${nested_mv_name1}"
+ retryUntilHasSqlCache mtmv_sql1
+ retryUntilHasSqlCache mtmv_sql2
+ retryUntilHasSqlCache mtmv_sql4
+ retryUntilHasSqlCache nested_mtmv_sql1
+
+ assertHasCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertHasCache "select * from ${mv_name4}"
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertHasCache mtmv_sql1
+ assertHasCache mtmv_sql2
+ assertHasCache mtmv_sql4
+ assertHasCache nested_mtmv_sql1
+
+ // recreate mtmv to add column
+ cur_create_async_partition_mv(dbName, mv_name1,
mtmv_sql3, "(id)")
+ sleep(15 * 1000)
+ assertNoCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name2}"
+ assertNoCache mtmv_sql1
+ assertHasCache "select * from ${nested_mv_name1}"
+ assertNoCache nested_mtmv_sql1
+
+ retryUntilHasSqlCache "select * from ${mv_name1}"
+ assertHasCache "select * from ${mv_name1}"
+
+ sql "REFRESH MATERIALIZED VIEW ${mv_name2} AUTO;"
+ waitingMTMVTaskFinishedByMvName(mv_name2)
+ sleep(10000)
+ assertHasCache "select * from ${mv_name2}"
+
+ retryUntilHasSqlCache mtmv_sql1
+ assertHasCache mtmv_sql1
+ retryUntilHasSqlCache nested_mtmv_sql1
+ assertHasCache nested_mtmv_sql1
+ }
+ })
+ ).get()
}
-
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
