This is an automated email from the ASF dual-hosted git repository.
hellostephen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new c1dda530539 [test](mtmv)Add the cases of upgrade and downgrade of mtmv
(#38868)
c1dda530539 is described below
commit c1dda5305390253fbb70902b40fb6e176cd9574f
Author: zfr95 <[email protected]>
AuthorDate: Mon Aug 12 11:15:02 2024 +0800
[test](mtmv)Add the cases of upgrade and downgrade of mtmv (#38868)
## Proposed changes
[test](mtmv)Add the cases of upgrade and downgrade of mtmv
---
.../suites/nereids_rules_p0/mv_up_down/load.groovy | 128 +++++++++++++++++++
.../mv_up_down/test_mtmv_job_and_hit.groovy | 141 +++++++++++++++++++++
2 files changed, 269 insertions(+)
diff --git a/regression-test/suites/nereids_rules_p0/mv_up_down/load.groovy
b/regression-test/suites/nereids_rules_p0/mv_up_down/load.groovy
new file mode 100644
index 00000000000..9a514c65291
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv_up_down/load.groovy
@@ -0,0 +1,128 @@
+// 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("test_upgrade_downgrade_prepare_mtmv","p0,mtmv,restart_fe") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ String orders_tb = "up_down_mtmv_orders"
+ String lineitem_tb = "up_down_mtmv_lineitem"
+ String mtmv_name = "up_down_mtmv_test_mv"
+ sql "use ${db}"
+
+
+ sql """
+ drop table if exists ${orders_tb}
+ """
+
+ sql """CREATE TABLE `${orders_tb}` (
+ `o_orderkey` BIGINT NULL,
+ `o_custkey` INT NULL,
+ `o_orderstatus` VARCHAR(1) NULL,
+ `o_totalprice` DECIMAL(15, 2) NULL,
+ `o_orderpriority` VARCHAR(15) NULL,
+ `o_clerk` VARCHAR(15) NULL,
+ `o_shippriority` INT NULL,
+ `o_comment` VARCHAR(79) NULL,
+ `o_orderdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`o_orderkey`, `o_custkey`)
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`o_orderdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ drop table if exists ${lineitem_tb}
+ """
+
+ sql """CREATE TABLE `${lineitem_tb}` (
+ `l_orderkey` BIGINT NULL,
+ `l_linenumber` INT NULL,
+ `l_partkey` INT NULL,
+ `l_suppkey` INT NULL,
+ `l_quantity` DECIMAL(15, 2) NULL,
+ `l_extendedprice` DECIMAL(15, 2) NULL,
+ `l_discount` DECIMAL(15, 2) NULL,
+ `l_tax` DECIMAL(15, 2) NULL,
+ `l_returnflag` VARCHAR(1) NULL,
+ `l_linestatus` VARCHAR(1) NULL,
+ `l_commitdate` DATE NULL,
+ `l_receiptdate` DATE NULL,
+ `l_shipinstruct` VARCHAR(25) NULL,
+ `l_shipmode` VARCHAR(10) NULL,
+ `l_comment` VARCHAR(44) NULL,
+ `l_shipdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(l_orderkey, l_linenumber, l_partkey, l_suppkey )
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`l_shipdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ insert into ${orders_tb} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'o', 109.2, 'c','d',2, 'mm', '2023-10-17'),
+ (3, 3, null, 99.5, 'a', 'b', 1, 'yy', '2023-10-19'),
+ (1, 2, 'o', null, 'a', 'b', 1, 'yy', '2023-10-20'),
+ (2, 3, 'k', 109.2, null,'d',2, 'mm', '2023-10-21'),
+ (3, 1, 'k', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'o', 99.5, 'a', 'b', null, 'yy', '2023-10-19'),
+ (2, 1, 'o', 109.2, 'c','d',2, null, '2023-10-18'),
+ (3, 2, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (4, 5, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');
+ """
+
+ sql """
+ insert into ${lineitem_tb} values
+ (null, 1, 2, 3, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (1, null, 3, 1, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (3, 3, null, 2, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19',
'c', 'd', 'xxxxxxxxx', '2023-10-19'),
+ (1, 2, 3, null, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (2, 3, 2, 1, 5.5, 6.5, 7.5, 8.5, 'o', 'k', null, '2023-10-18', 'a', 'b',
'yyyyyyyyy', '2023-10-18'),
+ (3, 1, 1, 2, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', null, 'c', 'd',
'xxxxxxxxx', '2023-10-19'),
+ (1, 3, 2, 2, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17');
+ """
+
+ sql """analyze table ${orders_tb} with sync;"""
+ sql """analyze table ${lineitem_tb} with sync;"""
+
+ String mtmv_sql = """select l_Shipdate, o_Orderdate, l_partkey, l_suppkey
+ from ${lineitem_tb}
+ left join ${orders_tb}
+ on ${lineitem_tb}.l_orderkey = ${orders_tb}.o_orderkey"""
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mtmv_name};"""
+ sql """DROP TABLE IF EXISTS ${mtmv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mtmv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mtmv_sql}
+ """
+
+ def job_name = getJobName(db, mtmv_name)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv_up_down/test_mtmv_job_and_hit.groovy
b/regression-test/suites/nereids_rules_p0/mv_up_down/test_mtmv_job_and_hit.groovy
new file mode 100644
index 00000000000..31869fa2141
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv_up_down/test_mtmv_job_and_hit.groovy
@@ -0,0 +1,141 @@
+// 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("test_upgrade_downgrade_compatibility_mtmv","p0,mtmv,restart_fe") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ String orders_tb = "up_down_mtmv_orders"
+ String lineitem_tb = "up_down_mtmv_lineitem"
+ String mtmv_name = "up_down_mtmv_test_mv"
+
+ def compare_res = { def stmt ->
+ sql "SET enable_materialized_view_rewrite=false"
+ def origin_res = sql stmt
+ logger.info("origin_res: " + origin_res)
+ sql "SET enable_materialized_view_rewrite=true"
+ def mv_origin_res = sql stmt
+ logger.info("mv_origin_res: " + mv_origin_res)
+ assertTrue((mv_origin_res == [] && origin_res == []) ||
(mv_origin_res.size() == origin_res.size()))
+ for (int row = 0; row < mv_origin_res.size(); row++) {
+ assertTrue(mv_origin_res[row].size() == origin_res[row].size())
+ for (int col = 0; col < mv_origin_res[row].size(); col++) {
+ assertTrue(mv_origin_res[row][col] == origin_res[row][col])
+ }
+ }
+ }
+
+ String mtmv_sql = """select l_Shipdate, o_Orderdate, l_partkey, l_suppkey
+ from ${lineitem_tb}
+ left join ${orders_tb}
+ on ${lineitem_tb}.l_orderkey = ${orders_tb}.o_orderkey"""
+
+ def select_count1 = sql """select count(*) from ${mtmv_name}"""
+ logger.info("select_count1: " + select_count1)
+
+ explain {
+ sql("${mtmv_sql}")
+ contains "${mtmv_name}(${mtmv_name})"
+ }
+ compare_res(mtmv_sql + " order by 1,2,3,4")
+
+ sql """
+ insert into ${orders_tb} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (6, null, 'o', 109.2, 'c','d',2, 'mm', '2023-10-17'),
+ (6, 3, null, 99.5, 'a', 'b', 1, 'yy', '2023-10-19'),
+ (6, 2, 'o', null, 'a', 'b', 1, 'yy', '2023-10-20');
+ """
+
+ sql """
+ insert into ${lineitem_tb} values
+ (null, 1, 2, 3, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (6, null, 3, 1, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18',
'a', 'b', 'yyyyyyyyy', '2023-10-17'),
+ (6, 3, null, 2, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19',
'c', 'd', 'xxxxxxxxx', '2023-10-19'),
+ (6, 2, 3, null, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'a', 'b', 'yyyyyyyyy', '2023-10-17');
+ """
+
+ sql """refresh MATERIALIZED VIEW ${mtmv_name} auto;"""
+
+ // insert and refresh mtmv
+ def job_name = getJobName(db, mtmv_name)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+ def select_count2 = sql """select count(*) from ${mtmv_name}"""
+ logger.info("select_count2: " + select_count2)
+ assertTrue(select_count2[0][0] != select_count1[0][0])
+
+ explain {
+ sql("${mtmv_sql}")
+ contains "${mtmv_name}(${mtmv_name})"
+ }
+ compare_res(mtmv_sql + " order by 1,2,3,4")
+
+ // pause
+ def job_status = sql """select * from jobs("type"="mv") where
Name="${job_name}";"""
+ assertTrue(job_status[0][8] == "RUNNING")
+ sql """PAUSE MATERIALIZED VIEW JOB ON ${mtmv_name};"""
+ job_status = sql """select * from jobs("type"="mv") where
Name="${job_name}";"""
+ assertTrue(job_status[0][8] == "PAUSED")
+
+ explain {
+ sql("${mtmv_sql}")
+ contains "${mtmv_name}(${mtmv_name})"
+ }
+ compare_res(mtmv_sql + " order by 1,2,3,4")
+
+ // resume
+ sql """RESUME MATERIALIZED VIEW JOB ON ${mtmv_name};"""
+ job_status = sql """select * from jobs("type"="mv") where
Name="${job_name}";"""
+ assertTrue(job_status[0][8] == "RUNNING")
+
+ explain {
+ sql("${mtmv_sql}")
+ contains "${mtmv_name}(${mtmv_name})"
+ }
+ compare_res(mtmv_sql + " order by 1,2,3,4")
+
+ // drop
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mtmv_name};"""
+ sql """DROP TABLE IF EXISTS ${mtmv_name}"""
+ test {
+ sql """select count(*) from ${mtmv_name}"""
+ exception "does not exist"
+ }
+
+ // create
+ sql"""
+ CREATE MATERIALIZED VIEW ${mtmv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mtmv_sql}
+ """
+
+ job_name = getJobName(db, mtmv_name)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ def select_count3 = sql """select count(*) from ${mtmv_name}"""
+ logger.info("select_count3: " + select_count3)
+ assertTrue(select_count3[0][0] == select_count2[0][0])
+
+ explain {
+ sql("${mtmv_sql}")
+ contains "${mtmv_name}(${mtmv_name})"
+ }
+ compare_res(mtmv_sql + " order by 1,2,3,4")
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
