This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 4ebe18c08f1 [test](mtmv) Add mtmv basic one and two dimensional test
cases (#30651)
4ebe18c08f1 is described below
commit 4ebe18c08f130e99c3478cdbbda664c835d08dda
Author: zfr95 <[email protected]>
AuthorDate: Sun Feb 4 11:17:37 2024 +0800
[test](mtmv) Add mtmv basic one and two dimensional test cases (#30651)
---
.../mv/dimension/dimension_1.groovy | 689 ++++++++++++++++++++
.../mv/dimension/dimension_2_1.groovy | 433 +++++++++++++
.../mv/dimension/dimension_2_2.groovy | 432 +++++++++++++
.../mv/dimension/dimension_2_3.groovy | 373 +++++++++++
.../mv/dimension/dimension_2_4.groovy | 715 +++++++++++++++++++++
.../mv/dimension/dimension_2_5.groovy | 424 ++++++++++++
.../mv/dimension/dimension_2_6.groovy | 409 ++++++++++++
7 files changed, 3475 insertions(+)
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_1.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_1.groovy
new file mode 100644
index 00000000000..988bbdbd803
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_1.groovy
@@ -0,0 +1,689 @@
+// 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.
+
+/*
+This suite is a one dimensional test case file.
+ */
+suite("partition_mv_rewrite_dimension_1") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_1
+ """
+
+ sql """CREATE TABLE `orders_1` (
+ `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_1
+ """
+
+ sql """CREATE TABLE `lineitem_1` (
+ `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_1 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_1 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_1 with sync;"""
+ sql """analyze table lineitem_1 with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+
+ // join direction
+ def mv_name_1 = "mv_join_1"
+ def join_direction_mv_1 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ """
+
+ create_mv_lineitem(mv_name_1, join_direction_mv_1)
+ def job_name_1 = getJobName(db, mv_name_1)
+ waitingMTMVTaskFinished(job_name_1)
+
+ def join_direction_sql_1 = """
+ select l_shipdate
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ """
+ def join_direction_sql_2 = """
+ select l_shipdate
+ from orders_1
+ left join lineitem_1
+ on orders_1.o_orderkey = lineitem_1.l_orderkey
+ """
+ explain {
+ sql("${join_direction_sql_1}")
+ contains "${mv_name_1}(${mv_name_1})"
+ }
+ compare_res(join_direction_sql_1 + " order by 1")
+ explain {
+ sql("${join_direction_sql_2}")
+ notContains "${mv_name_1}(${mv_name_1})"
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_1};"""
+
+
+ def mv_name_2 = "mv_join_2"
+ def join_direction_mv_2 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ inner join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ """
+
+ create_mv_lineitem(mv_name_2, join_direction_mv_2)
+ def job_name_2 = getJobName(db, mv_name_2)
+ waitingMTMVTaskFinished(job_name_2)
+
+ def join_direction_sql_3 = """
+ select l_shipdate
+ from lineitem_1
+ inner join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ """
+ def join_direction_sql_4 = """
+ select l_shipdate
+ from orders_1
+ inner join lineitem_1
+ on orders_1.o_orderkey = lineitem_1.l_orderkey
+ """
+ explain {
+ sql("${join_direction_sql_3}")
+ contains "${mv_name_2}(${mv_name_2})"
+ }
+ compare_res(join_direction_sql_3 + " order by 1")
+ explain {
+ sql("${join_direction_sql_4}")
+ contains "${mv_name_2}(${mv_name_2})"
+ }
+ compare_res(join_direction_sql_4 + " order by 1")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_2};"""
+
+ // join filter position
+ def join_filter_stmt_1 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+ def join_filter_stmt_2 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from (select * from lineitem_1 where l_shipdate = '2023-10-17' ) t1
+ left join orders_1
+ on t1.l_orderkey = orders_1.o_orderkey"""
+ def join_filter_stmt_3 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join (select * from orders_1 where o_orderdate = '2023-10-17' )
t2
+ on lineitem_1.l_orderkey = t2.o_orderkey"""
+ def join_filter_stmt_4 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'"""
+ def join_filter_stmt_5 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate = '2023-10-17'"""
+ def join_filter_stmt_6 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where o_orderdate = '2023-10-17'"""
+ def join_filter_stmt_7 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where orders_1.o_orderkey=1"""
+
+ def mv_list = [
+ join_filter_stmt_1, join_filter_stmt_2, join_filter_stmt_3,
join_filter_stmt_4,
+ join_filter_stmt_5, join_filter_stmt_6, join_filter_stmt_7]
+
+ for (int i = 0; i < mv_list.size(); i++) {
+ logger.info("i:" + i)
+ def join_filter_mv = """join_filter_mv_${i}"""
+ create_mv_lineitem(join_filter_mv, mv_list[i])
+ def job_name = getJobName(db, join_filter_mv)
+ waitingMTMVTaskFinished(job_name)
+ def res_1 = sql """show partitions from ${join_filter_mv};"""
+ logger.info("res_1:" + res_1)
+ if (i == 0) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ logger.info("j:" + j)
+ if (j == 2) {
+ continue
+ }
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ }
+ } else if (i == 1) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ logger.info("j:" + j)
+ if (j == 1 || j == 4 || j == 3) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+ }
+ } else if (i == 2) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ logger.info("j:" + j)
+ if (j == 2 || j == 3 || j == 5) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+
+ }
+ } else if (i == 3) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ logger.info("j:" + j)
+ if (j == 3) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+
+ }
+ } else if (i == 4) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ logger.info("j:" + j)
+ if (j == 4 || j == 1 || j == 3) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+ }
+ } else if (i == 5) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ if (j == 5 || j == 3) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+
+ }
+ } else if (i == 6) {
+ for (int j = 0; j < mv_list.size(); j++) {
+ if (j == 6) {
+ explain {
+ sql("${mv_list[j]}")
+ contains "${join_filter_mv}(${join_filter_mv})"
+ }
+ compare_res(mv_list[j] + " order by 1, 2, 3, 4, 5")
+ } else {
+ explain {
+ sql("${mv_list[j]}")
+ notContains "${join_filter_mv}(${join_filter_mv})"
+ }
+ }
+
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${join_filter_mv};"""
+ }
+
+ // join type
+ def join_type_stmt_1 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+ def join_type_stmt_2 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ inner join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+
+ // Todo: right/cross/full/semi/anti join
+ // Currently, only left join and inner join are supported.
+// def join_type_stmt_3 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_1
+// right join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+// def join_type_stmt_4 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_1
+// cross join orders_1"""
+// def join_type_stmt_5 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_1
+// full join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+// def join_type_stmt_6 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_1
+// semi join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+// def join_type_stmt_7 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_1
+// anti join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+ def join_type_stmt_list = [join_type_stmt_1, join_type_stmt_2]
+ for (int i = 0; i < join_type_stmt_list.size(); i++) {
+ logger.info("i:" + i)
+ String join_type_mv = """join_type_mv_${i}"""
+ if (i == 2) {
+ create_mv_orders(join_type_mv, join_type_stmt_list[i])
+ } else {
+ create_mv_lineitem(join_type_mv, join_type_stmt_list[i])
+ }
+ def job_name = getJobName(db, join_type_mv)
+ waitingMTMVTaskFinished(job_name)
+ for (int j = 0; j < join_type_stmt_list.size(); j++) {
+ logger.info("j:" + j)
+ if (i == j) {
+ explain {
+ sql("${join_type_stmt_list[j]}")
+ contains "${join_type_mv}(${join_type_mv})"
+ }
+ compare_res(join_type_stmt_list[j] + " order by 1,2,3,4")
+ } else {
+ explain {
+ sql("${join_type_stmt_list[j]}")
+ notContains "${join_type_mv}(${join_type_mv})"
+ }
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${join_type_mv};"""
+ }
+
+ // agg
+ // agg + without group by + with agg function
+ def agg_mv_name_1 = "agg_mv_name_1"
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${agg_mv_name_1};"""
+ sql """DROP TABLE IF EXISTS ${agg_mv_name_1}"""
+ sql """
+ CREATE MATERIALIZED VIEW ${agg_mv_name_1}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ select
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_1
+ """
+ def agg_job_name_1 = getJobName(db, agg_mv_name_1)
+ waitingMTMVTaskFinished(agg_job_name_1)
+
+ def agg_sql_1 = """select
+ count(distinct case when o_shippriority > 1 and o_orderkey IN (1, 3)
then o_custkey else null end) as cnt_1,
+ count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN (2) then
o_custkey else null end) as cnt_2,
+ sum(o_totalprice),
+ max(o_totalprice),
+ min(o_totalprice),
+ count(*)
+ from orders_1
+ """
+ explain {
+ sql("${agg_sql_1}")
+ contains "${agg_mv_name_1}(${agg_mv_name_1})"
+ }
+ compare_res(agg_sql_1 + " order by 1,2,3,4,5,6")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${agg_mv_name_1};"""
+
+ // agg + with group by + without agg function
+ def agg_mv_name_2 = "agg_mv_name_2"
+ def agg_mv_stmt_2 = """
+ select o_orderdate, o_shippriority, o_comment
+ from orders_1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment
+ """
+ create_mv_orders(agg_mv_name_2, agg_mv_stmt_2)
+ def agg_job_name_2 = getJobName(db, agg_mv_name_2)
+ waitingMTMVTaskFinished(agg_job_name_2)
+ sql """analyze table ${agg_mv_name_2} with sync;"""
+
+ def agg_sql_2 = """select o_shippriority, o_comment
+ from orders_1
+ group by
+ o_shippriority,
+ o_comment
+ """
+ def agg_sql_explain_2 = sql """explain ${agg_sql_2};"""
+ def mv_index_1 =
agg_sql_explain_2.toString().indexOf("MaterializedViewRewriteSuccessButNotChose:")
+ assert(mv_index_1 != -1)
+
assert(agg_sql_explain_2.toString().substring(mv_index_1).indexOf(agg_mv_name_2)
!= -1)
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${agg_mv_name_2};"""
+
+ // agg + with group by + with agg function
+ def agg_mv_name_3 = "agg_mv_name_3"
+ def agg_mv_stmt_3 = """
+ select o_orderdate, o_shippriority, o_comment,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment
+ """
+ create_mv_orders(agg_mv_name_3, agg_mv_stmt_3)
+ def agg_job_name_3 = getJobName(db, agg_mv_name_3)
+ waitingMTMVTaskFinished(agg_job_name_3)
+ sql """analyze table ${agg_mv_name_3} with sync;"""
+
+ def agg_sql_3 = """select o_shippriority, o_comment,
+ count(distinct case when o_shippriority > 1 and o_orderkey IN (1,
3) then o_custkey else null end) as cnt_1,
+ count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN (2)
then o_custkey else null end) as cnt_2,
+ sum(o_totalprice),
+ max(o_totalprice),
+ min(o_totalprice),
+ count(*)
+ from orders_1
+ group by
+ o_shippriority,
+ o_comment
+ """
+ def agg_sql_explain_3 = sql """explain ${agg_sql_3};"""
+ def mv_index_2 =
agg_sql_explain_3.toString().indexOf("MaterializedViewRewriteSuccessButNotChose:")
+ assert(mv_index_2 != -1)
+
assert(agg_sql_explain_3.toString().substring(mv_index_2).indexOf(agg_mv_name_3)
!= -1)
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${agg_mv_name_3};"""
+
+
+ // Todo: query partittial rewriting
+// def query_partition_mv_name_1 = "query_partition_mv_name_1"
+// def query_partition_mv_stmt_1 = """
+// select l_shipdate, o_orderdate, l_partkey, l_suppkey, count(*)
+// from lineitem_1
+// left join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey
+// """
+// create_mv_orders(query_partition_mv_name_1, query_partition_mv_stmt_1)
+// def query_partition_job_name_1 = getJobName(db,
query_partition_mv_name_1)
+// waitingMTMVTaskFinished(query_partition_job_name_1)
+//
+// def query_partition_sql_1 = """select l_shipdate, l_partkey, count(*)
from lineitem_1;"""
+// def query_partition_sql_2 = """select o_orderdate, count(*) from
orders_1;"""
+// explain {
+// sql("${query_partition_sql_1}")
+// contains "${query_partition_mv_name_1}(${query_partition_mv_name_1})"
+// }
+// compare_res(query_partition_sql_1)
+// explain {
+// sql("${query_partition_sql_2}")
+// contains "${query_partition_mv_name_1}(${query_partition_mv_name_1})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${query_partition_mv_name_1};"""
+
+ // view partital rewriting
+ def view_partition_mv_name_1 = "view_partition_mv_name_1"
+ def view_partition_mv_stmt_1 = """
+ select l_shipdate, l_partkey, l_orderkey from lineitem_1 group by
l_shipdate, l_partkey, l_orderkey"""
+ create_mv_lineitem(view_partition_mv_name_1, view_partition_mv_stmt_1)
+ def view_partition_job_name_1 = getJobName(db, view_partition_mv_name_1)
+ waitingMTMVTaskFinished(view_partition_job_name_1)
+
+ def view_partition_sql_1 = """select t.l_shipdate, o_orderdate,
t.l_partkey
+ from (select l_shipdate, l_partkey, l_orderkey from lineitem_1 group
by l_shipdate, l_partkey, l_orderkey) t
+ left join orders_1
+ on t.l_orderkey = orders_1.o_orderkey group by t.l_shipdate,
o_orderdate, t.l_partkey
+ """
+ explain {
+ sql("${view_partition_sql_1}")
+ contains "${view_partition_mv_name_1}(${view_partition_mv_name_1})"
+ }
+ compare_res(view_partition_sql_1 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${view_partition_mv_name_1};"""
+
+ // Todo: union rewrte
+// def union_mv_name_1 = "union_mv_name_1"
+// def union_mv_stmt_1 = """
+// select l_shipdate, o_orderdate, l_partkey, count(*)
+// from lineitem_1
+// left join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey
+// where l_shipdate >= "2023-12-04"
+// """
+// create_mv_orders(union_mv_name_1, union_mv_stmt_1)
+// def union_job_name_1 = getJobName(db, union_mv_name_1)
+// waitingMTMVTaskFinished(union_job_name_1)
+//
+// def union_sql_1 = """select l_shipdate, o_orderdate, l_partkey, count(*)
+// from lineitem_1
+// left join orders_1
+// on lineitem_1.l_orderkey = orders_1.o_orderkey
+// where l_shipdate >= "2023-12-01"
+// """
+// explain {
+// sql("${union_sql_1}")
+// contains "${union_mv_name_1}(${union_mv_name_1})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${union_mv_name_1};"""
+
+ // predicate compensate
+ def predicate_mv_name_1 = "predicate_mv_name_1"
+ def predicate_mv_stmt_1 = """
+ select l_shipdate, o_orderdate, l_partkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate >= "2023-10-17"
+ """
+ create_mv_lineitem(predicate_mv_name_1, predicate_mv_stmt_1)
+ def predicate_job_name_1 = getJobName(db, predicate_mv_name_1)
+ waitingMTMVTaskFinished(predicate_job_name_1)
+
+ def predicate_sql_1 = """
+ select l_shipdate, o_orderdate, l_partkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate >= "2023-10-17" and l_partkey = 1
+ """
+ explain {
+ sql("${predicate_sql_1}")
+ contains "${predicate_mv_name_1}(${predicate_mv_name_1})"
+ }
+ compare_res(predicate_sql_1 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${predicate_mv_name_1};"""
+
+ // Todo: project rewriting
+// def rewriting_mv_name_1 = "rewriting_mv_name_1"
+// def rewriting_mv_stmt_1 = """
+// select o_orderdate, o_shippriority, o_comment, o_orderkey,
o_shippriority + o_custkey,
+// case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey
else null end cnt_1,
+// case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+// from orders_1
+// where o_orderkey > 1 + 1;
+// """
+// create_mv_orders(rewriting_mv_name_1, rewriting_mv_stmt_1)
+// def rewriting_job_name_1 = getJobName(db, rewriting_mv_name_1)
+// waitingMTMVTaskFinished(rewriting_job_name_1)
+//
+// def rewriting_sql_1 = """select o_shippriority, o_comment,
o_shippriority + o_custkey + o_orderkey,
+// case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end cnt_1,
+// case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+// from orders_1
+// where o_orderkey > (-3) + 5;
+// """
+// explain {
+// sql("${rewriting_sql_1}")
+// contains "${rewriting_mv_name_1}(${rewriting_mv_name_1})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${rewriting_mv_name_1};"""
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_1.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_1.groovy
new file mode 100644
index 00000000000..328bf7925d1
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_1.groovy
@@ -0,0 +1,433 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the left join and filter positions.
+ */
+suite("partition_mv_rewrite_dimension_2_1") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_1
+ """
+
+ sql """CREATE TABLE `orders_2_1` (
+ `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_2_1
+ """
+
+ sql """CREATE TABLE `lineitem_2_1` (
+ `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_2_1 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'o', 99.5, 'a', 'b', null, 'yy', '2023-10-19'),
+ (2, 1, 'k', 109.2, 'c','d',2, null, '2023-10-18'),
+ (3, 2, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (4, 5, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');
+ """
+
+ sql """
+ insert into lineitem_2_1 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_2_1 with sync;"""
+ sql """analyze table lineitem_2_1 with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // left join + filter on different position
+ def mv_stmt_0 = """select t.l_shipdate, o_orderdate, t.l_partkey,
t.l_suppkey, orders_2_1.o_orderkey
+ from (select l_shipdate, l_partkey, l_suppkey, l_orderkey from
lineitem_2_1 where l_shipdate = '2023-10-17') t
+ left join orders_2_1
+ on t.l_orderkey = orders_2_1.o_orderkey"""
+
+ def mv_stmt_1 = """select l_shipdate, t.o_orderdate, l_partkey, l_suppkey,
t.o_orderkey
+ from lineitem_2_1
+ left join (select o_orderdate,o_orderkey from orders_2_1 where
o_orderdate = '2023-10-17' ) t
+ on lineitem_2_1.l_orderkey = t.o_orderkey"""
+
+ def mv_stmt_2 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from lineitem_2_1
+ left join orders_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17'"""
+
+ def mv_stmt_3 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from lineitem_2_1
+ left join orders_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where o_orderdate = '2023-10-17'"""
+
+ def mv_stmt_4 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from lineitem_2_1
+ left join orders_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'"""
+
+ def mv_stmt_5 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from lineitem_2_1
+ left join orders_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'
+ and o_orderkey = 1"""
+
+ def mv_stmt_6 = """select t.l_shipdate, o_orderdate, t.l_partkey,
t.l_suppkey, orders_2_1.o_orderkey
+ from orders_2_1
+ left join (select l_shipdate, l_orderkey, l_partkey, l_suppkey from
lineitem_2_1 where l_shipdate = '2023-10-17') t
+ on t.l_orderkey = orders_2_1.o_orderkey"""
+
+ def mv_stmt_7 = """select l_shipdate, t.o_orderdate, l_partkey, l_suppkey,
t.o_orderkey
+ from (select o_orderdate, o_orderkey from orders_2_1 where o_orderdate
= '2023-10-17' ) t
+ left join lineitem_2_1
+ on lineitem_2_1.l_orderkey = t.o_orderkey"""
+
+ def mv_stmt_8 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from orders_2_1
+ left join lineitem_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17' """
+
+ def mv_stmt_9 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from orders_2_1
+ left join lineitem_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where o_orderdate = '2023-10-17' """
+
+ def mv_stmt_10 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from orders_2_1
+ left join lineitem_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17' """
+
+ def mv_stmt_11 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_1.o_orderkey
+ from orders_2_1
+ left join lineitem_2_1
+ on lineitem_2_1.l_orderkey = orders_2_1.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'
+ and o_orderkey = 1"""
+ def mv_list_1 = [mv_stmt_0, mv_stmt_1, mv_stmt_2, mv_stmt_3, mv_stmt_4,
mv_stmt_5, mv_stmt_6,
+ mv_stmt_7, mv_stmt_8, mv_stmt_9, mv_stmt_10, mv_stmt_11]
+ for (int i = 0; i < mv_list_1.size(); i++) {
+ logger.info("i:" + i)
+ def mv_name = """mv_name_2_1_${i}"""
+ if (i < 6) {
+ create_mv_lineitem(mv_name, mv_list_1[i])
+ } else {
+ create_mv_orders(mv_name, mv_list_1[i])
+ }
+ def job_name = getJobName(db, mv_name)
+ waitingMTMVTaskFinished(job_name)
+ if (i == 0) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [ 0, 2, 4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 1) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [1, 3, 4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 2) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [0, 2, 4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 3) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [3, 4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 4) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 5) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [5, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 6) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ // 5, 11 should be success but not now, should support in the
future by equivalence class
+ if (j in [4, 6, 8, 10]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 7) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 8) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 8, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 9) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 10) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 11) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [5, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ }
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_2.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_2.groovy
new file mode 100644
index 00000000000..74046d9b3e4
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_2.groovy
@@ -0,0 +1,432 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the inner join and filter positions.
+ */
+suite("partition_mv_rewrite_dimension_2_2") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_2
+ """
+
+ sql """CREATE TABLE `orders_2_2` (
+ `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_2_2
+ """
+
+ sql """CREATE TABLE `lineitem_2_2` (
+ `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_2_2 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'k', 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, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');
+ """
+
+ sql """
+ insert into lineitem_2_2 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_2_2 with sync;"""
+ sql """analyze table lineitem_2_2 with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // inner join + filter on different position
+ def mv_stmt_0 = """select t.l_shipdate, o_orderdate, t.l_partkey,
t.l_suppkey, orders_2_2.o_orderkey
+ from (select l_shipdate, l_partkey, l_suppkey, l_orderkey from
lineitem_2_2 where l_shipdate = '2023-10-17') t
+ inner join orders_2_2
+ on t.l_orderkey = orders_2_2.o_orderkey"""
+
+ def mv_stmt_1 = """select l_shipdate, t.o_orderdate, l_partkey, l_suppkey,
t.o_orderkey
+ from lineitem_2_2
+ inner join (select o_orderdate,o_orderkey from orders_2_2 where
o_orderdate = '2023-10-17' ) t
+ on lineitem_2_2.l_orderkey = t.o_orderkey"""
+
+ def mv_stmt_2 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from lineitem_2_2
+ inner join orders_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17'"""
+
+ def mv_stmt_3 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from lineitem_2_2
+ inner join orders_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where o_orderdate = '2023-10-17'"""
+
+ def mv_stmt_4 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from lineitem_2_2
+ inner join orders_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'"""
+
+ def mv_stmt_5 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from lineitem_2_2
+ inner join orders_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'
+ and o_orderkey = 1"""
+
+ def mv_stmt_6 = """select t.l_shipdate, o_orderdate, t.l_partkey,
t.l_suppkey, orders_2_2.o_orderkey
+ from orders_2_2
+ inner join (select l_shipdate, l_orderkey, l_partkey, l_suppkey from
lineitem_2_2 where l_shipdate = '2023-10-17') t
+ on t.l_orderkey = orders_2_2.o_orderkey"""
+
+ def mv_stmt_7 = """select l_shipdate, t.o_orderdate, l_partkey, l_suppkey,
t.o_orderkey
+ from (select o_orderdate, o_orderkey from orders_2_2 where o_orderdate
= '2023-10-17' ) t
+ inner join lineitem_2_2
+ on lineitem_2_2.l_orderkey = t.o_orderkey"""
+
+ def mv_stmt_8 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from orders_2_2
+ inner join lineitem_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17' """
+
+ def mv_stmt_9 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from orders_2_2
+ inner join lineitem_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where o_orderdate = '2023-10-17' """
+
+ def mv_stmt_10 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from orders_2_2
+ inner join lineitem_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17' """
+
+ def mv_stmt_11 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
orders_2_2.o_orderkey
+ from orders_2_2
+ inner join lineitem_2_2
+ on lineitem_2_2.l_orderkey = orders_2_2.o_orderkey
+ where l_shipdate = '2023-10-17' and o_orderdate = '2023-10-17'
+ and o_orderkey = 1"""
+ def mv_list_1 = [mv_stmt_0, mv_stmt_1, mv_stmt_2, mv_stmt_3, mv_stmt_4,
mv_stmt_5, mv_stmt_6,
+ mv_stmt_7, mv_stmt_8, mv_stmt_9, mv_stmt_10, mv_stmt_11]
+ for (int i = 0; i < mv_list_1.size(); i++) {
+ logger.info("i:" + i)
+ def mv_name = """mv_name_2_2_${i}"""
+ if (i < 6) {
+ create_mv_lineitem(mv_name, mv_list_1[i])
+ } else {
+ create_mv_orders(mv_name, mv_list_1[i])
+ }
+ def job_name = getJobName(db, mv_name)
+ waitingMTMVTaskFinished(job_name)
+ if (i == 0) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("current index j:" + j)
+ if (j in [0, 2, 4, 5, 6, 8, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 1) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [1, 3, 4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 2) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [0, 2, 4, 5, 6, 8, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 3) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [1, 3, 4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 4) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 5) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [5, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 6) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [0, 2, 4, 5, 6, 8, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 7) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [1, 3, 4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 8) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [0, 2, 4, 5, 6, 8, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 9) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [1, 3, 4, 5, 7, 9, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 10) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [4, 5, 10, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ } else if (i == 11) {
+ for (int j = 0; j < mv_list_1.size(); j++) {
+ logger.info("j:" + j)
+ if (j in [5, 11]) {
+ explain {
+ sql("${mv_list_1[j]}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(mv_list_1[j] + " order by 1,2,3,4,5")
+ } else {
+ explain {
+ sql("${mv_list_1[j]}")
+ notContains "${mv_name}(${mv_name})"
+ }
+ }
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ }
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_3.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_3.groovy
new file mode 100644
index 00000000000..3a9d25b3f40
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_3.groovy
@@ -0,0 +1,373 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the agg function, etc
+ */
+suite("partition_mv_rewrite_dimension_2_3") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_3
+ """
+
+ sql """CREATE TABLE `orders_2_3` (
+ `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_2_3
+ """
+
+ sql """CREATE TABLE `lineitem_2_3` (
+ `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_2_3 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'k', 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, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');
+ """
+
+ sql """
+ insert into lineitem_2_3 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_2_3 with sync;"""
+ sql """analyze table lineitem_2_3 with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_all_mv = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // join + agg function
+ def mv_name_1 = "mv_name_2_3_1"
+ def mv_stmt_1 = """select
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey"""
+ create_all_mv(mv_name_1, mv_stmt_1)
+ def job_name_1 = getJobName(db, mv_name_1)
+ waitingMTMVTaskFinished(job_name_1)
+
+ def sql_stmt_1 = """select
+ count(distinct case when o_shippriority > 1 and o_orderkey IN (1,
3) then o_custkey else null end) as cnt_1,
+ count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN (2)
then o_custkey else null end) as cnt_2,
+ sum(o_totalprice),
+ max(o_totalprice),
+ min(o_totalprice),
+ count(*)
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey"""
+ explain {
+ sql("${sql_stmt_1}")
+ contains "${mv_name_1}(${mv_name_1})"
+ }
+ compare_res(sql_stmt_1 + " order by 1,2,3,4,5,6")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_1};"""
+
+ // join + group by
+ def mv_name_2 = "mv_name_2_3_2"
+ def mv_stmt_2 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_mv_orders(mv_name_2, mv_stmt_2)
+ def job_name_2 = getJobName(db, mv_name_2)
+ waitingMTMVTaskFinished(job_name_2)
+
+ def sql_stmt_2 = """select o_shippriority, o_comment
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey
+ group by
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_2}")
+ contains "${mv_name_2}(${mv_name_2})"
+ }
+ compare_res(sql_stmt_2 + " order by 1,2")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_2};"""
+
+ // join + group by + agg function
+ def mv_name_3 = "mv_name_2_3_3"
+ def mv_stmt_3 = """select o_orderdate, o_shippriority, o_comment,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_mv_orders(mv_name_3, mv_stmt_3)
+ def job_name_3 = getJobName(db, mv_name_3)
+ waitingMTMVTaskFinished(job_name_3)
+
+ def sql_stmt_3 = """select o_shippriority, o_comment,
+ count(distinct case when o_shippriority > 1 and o_orderkey IN (1,
3) then o_custkey else null end) as cnt_1,
+ count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN (2)
then o_custkey else null end) as cnt_2,
+ sum(o_totalprice),
+ max(o_totalprice),
+ min(o_totalprice),
+ count(*)
+ from orders_2_3
+ left join lineitem_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey
+ group by
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_3}")
+ contains "${mv_name_3}(${mv_name_3})"
+ }
+ compare_res(sql_stmt_3 + " order by 1,2,3,4,5,6,7,8")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_3};"""
+
+
+ // Todo: query partial
+// def mv_name_4 = "mv_name_2_3_4"
+// def mv_stmt_4 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey
+// from lineitem_2_3
+// left join orders_2_3
+// on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey """
+// create_mv_orders(mv_name_4, mv_stmt_4)
+// def job_name_4 = getJobName(db, mv_name_4)
+// waitingMTMVTaskFinished(job_name_4)
+//
+// def sql_stmt_4 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey
from lineitem_2_3 """
+// explain {
+// sql("${sql_stmt_4}")
+// contains "${mv_name_4}(${mv_name_4})"
+// }
+// sql_stmt_4 = """select o_orderdate from orders_2_3 """
+// explain {
+// sql("${sql_stmt_4}")
+// contains "${mv_name_4}(${mv_name_4})"
+// }
+
+ // view partial
+ def mv_name_5 = "mv_name_2_3_5"
+ def mv_stmt_5 = """select l_shipdate, l_partkey, l_orderkey, o_orderdate
+ from lineitem_2_3
+ left join orders_2_3
+ on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey"""
+ create_mv_lineitem(mv_name_5, mv_stmt_5)
+ def job_name_5 = getJobName(db, mv_name_5)
+ waitingMTMVTaskFinished(job_name_5)
+
+ def sql_stmt_5 = """select l_shipdate, o_orderdate, l_partkey
+ from lineitem_2_3
+ left join orders_2_3 on lineitem_2_3.l_orderkey =
orders_2_3.o_orderkey"""
+ explain {
+ sql("${sql_stmt_5}")
+ contains "${mv_name_5}(${mv_name_5})"
+ }
+ compare_res(sql_stmt_5 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_5};"""
+
+
+ // Todo: union rewriting
+// def mv_name_6 = "mv_name_2_3_6"
+// def mv_stmt_6 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_3
+// left join orders_2_3
+// on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+// where l_shipdate >= '2023-10-17'"""
+// create_mv_lineitem(mv_name_6, mv_stmt_6)
+// def job_name_6 = getJobName(db, mv_name_6)
+// waitingMTMVTaskFinished(job_name_6)
+//
+// def sql_stmt_6 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_3
+// left join orders_2_3
+// on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+// where l_shipdate >= '2023-10-15'"""
+// explain {
+// sql("${sql_stmt_6}")
+// contains "${mv_name_6}(${mv_name_6})"
+// }
+
+ // predicate compensate
+ def mv_name_7 = "mv_name_2_3_7"
+ def mv_stmt_7 = """select l_shipdate, o_orderdate, l_partkey
+ from lineitem_2_3
+ left join orders_2_3
+ on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+ where l_shipdate >= '2023-10-17'"""
+ create_mv_lineitem(mv_name_7, mv_stmt_7)
+ def job_name_7 = getJobName(db, mv_name_7)
+ waitingMTMVTaskFinished(job_name_7)
+
+ def sql_stmt_7 = """select l_shipdate, o_orderdate, l_partkey
+ from lineitem_2_3
+ left join orders_2_3
+ on lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+ where l_shipdate >= "2023-10-17" and l_partkey = 3"""
+ explain {
+ sql("${sql_stmt_7}")
+ contains "${mv_name_7}(${mv_name_7})"
+ }
+ compare_res(sql_stmt_7 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_7};"""
+
+
+ // project rewriting
+ def mv_name_8 = "mv_name_2_3_8"
+ def mv_stmt_8 = """select o_orderdate, o_shippriority, o_comment,
l_suppkey, o_shippriority + o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end cnt_1,
+ case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_3 left join lineitem_2_3 on
lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+ where o_orderkey > 1 + 1 """
+ create_mv_orders(mv_name_8, mv_stmt_8)
+ def job_name_8 = getJobName(db, mv_name_8)
+ waitingMTMVTaskFinished(job_name_8)
+
+ def sql_stmt_8 = """select o_shippriority, o_comment, o_shippriority +
o_custkey + l_suppkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end as cnt_1,
+ case when O_SHIPPRIORITY > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_3 left join lineitem_2_3 on
lineitem_2_3.l_orderkey = orders_2_3.o_orderkey
+ where o_orderkey > (-3) + 5 """
+ explain {
+ sql("${sql_stmt_8}")
+ contains "${mv_name_8}(${mv_name_8})"
+ }
+ compare_res(sql_stmt_8 + " order by 1,2,3,4,5")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_8};"""
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_4.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_4.groovy
new file mode 100644
index 00000000000..09b414588d7
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_4.groovy
@@ -0,0 +1,715 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the query partial, view partial, union rewriting, predicate
compensate, project rewriting.
+ */
+suite("partition_mv_rewrite_dimension_2_4") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_4
+ """
+
+ sql """CREATE TABLE `orders_2_4` (
+ `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_2_4
+ """
+
+ sql """CREATE TABLE `lineitem_2_4` (
+ `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 """
+ drop table if exists partsupp
+ """
+
+ sql """CREATE TABLE `partsupp` (
+ `ps_partkey` INT NULL,
+ `ps_suppkey` INT NULL,
+ `ps_availqty` INT NULL,
+ `ps_supplycost` DECIMAL(15, 2) NULL,
+ `ps_comment` VARCHAR(199) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`, `ps_suppkey`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ insert into orders_2_4 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'k', 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_2_4 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"""
+ insert into partsupp values
+ (1, 1, 1, 99.5, 'yy'),
+ (null, 2, 2, 109.2, 'mm'),
+ (3, null, 1, 99.5, 'yy');
+ """
+
+ sql """analyze table orders_2_4 with sync;"""
+ sql """analyze table lineitem_2_4 with sync;"""
+ sql """analyze table partsupp with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_all_mv = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // Todo: query partial
+ // agg function + query partial
+// def mv_name_1 = "mv_name_2_4_1"
+// def mv_stmt_1 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey"""
+// create_all_mv(mv_name_1, mv_stmt_1)
+// def job_name_1 = getJobName(db, mv_name_1)
+// waitingMTMVTaskFinished(job_name_1)
+//
+// def sql_stmt_1 = """select
+// count(distinct case when o_shippriority > 1 and o_orderkey IN
(1, 3) then o_custkey else null end) as cnt_1,
+// count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN
(2) then o_custkey else null end) as cnt_2,
+// sum(o_totalprice),
+// max(o_totalprice),
+// min(o_totalprice),
+// count(*)
+// from orders_2_4 """
+// explain {
+// sql("${sql_stmt_1}")
+// contains "${mv_name_1}(${mv_name_1})"
+// }
+
+
+ // group by + query partial
+// def mv_name_2 = "mv_name_2_4_2"
+// def mv_stmt_2 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_2, mv_stmt_2)
+// def job_name_2 = getJobName(db, mv_name_2)
+// waitingMTMVTaskFinished(job_name_2)
+//
+// def sql_stmt_2 = """select o_shippriority, o_comment
+// from orders_2_4
+// group by
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_2}")
+// contains "${mv_name_2}(${mv_name_2})"
+// }
+
+ // agg function + group by + query partial
+// def mv_name_3 = "mv_name_2_4_3"
+// def mv_stmt_3 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_3, mv_stmt_3)
+// def job_name_3 = getJobName(db, mv_name_3)
+// waitingMTMVTaskFinished(job_name_3)
+//
+// def sql_stmt_3 = """select o_shippriority, o_comment,
+// count(distinct case when o_shippriority > 1 and o_orderkey IN
(1, 3) then o_custkey else null end) as cnt_1,
+// count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN
(2) then o_custkey else null end) as cnt_2,
+// sum(o_totalprice),
+// max(o_totalprice),
+// min(o_totalprice),
+// count(*)
+// from orders_2_4
+// group by
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_3}")
+// contains "${mv_name_3}(${mv_name_3})"
+// }
+
+ // view partial
+ // agg function + view partial
+ def mv_name_4 = "mv_name_2_4_4"
+ def mv_stmt_4 = """select
+ o_totalprice,
+ o_shippriority,
+ o_orderkey,
+ l_orderkey,
+ o_custkey
+ from orders_2_4
+ left join lineitem_2_4
+ on lineitem_2_4.l_orderkey = orders_2_4.o_orderkey """
+ create_all_mv(mv_name_4, mv_stmt_4)
+ def job_name_4 = getJobName(db, mv_name_4)
+ waitingMTMVTaskFinished(job_name_4)
+
+ def sql_stmt_4 = """select
+ o_totalprice,
+ o_shippriority,
+ o_orderkey,
+ l_orderkey,
+ o_custkey
+ from orders_2_4
+ left join lineitem_2_4
+ on lineitem_2_4.l_orderkey = orders_2_4.o_orderkey
+ left join partsupp on partsupp.ps_partkey =
lineitem_2_4.l_orderkey"""
+ explain {
+ sql("${sql_stmt_4}")
+ contains "${mv_name_4}(${mv_name_4})"
+ }
+ compare_res(sql_stmt_4 + " order by 1,2,3,4,5")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_4};"""
+
+
+ // group by + query partial
+ def mv_name_5 = "mv_name_2_4_5"
+ def mv_stmt_5 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_mv_orders(mv_name_5, mv_stmt_5)
+ def job_name_5 = getJobName(db, mv_name_5)
+ waitingMTMVTaskFinished(job_name_5)
+
+ def sql_stmt_5 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+ left join partsupp on partsupp.ps_partkey = lineitem_2_4.l_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_5}")
+ notContains "${mv_name_5}(${mv_name_5})"
+ }
+ compare_res(sql_stmt_5 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_5};"""
+
+
+ // agg function + group by + view partial
+ def mv_name_6 = "mv_name_2_4_6"
+ def mv_stmt_6 = """select o_orderdate, o_shippriority, o_comment,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4
+ left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_mv_orders(mv_name_6, mv_stmt_6)
+ def job_name_6 = getJobName(db, mv_name_6)
+ waitingMTMVTaskFinished(job_name_6)
+
+ def sql_stmt_6 = """select o_orderdate, o_shippriority, o_comment,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4
+ left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+ left join partsupp on partsupp.ps_partkey =
lineitem_2_4.l_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_6}")
+ notContains "${mv_name_6}(${mv_name_6})"
+ }
+ compare_res(sql_stmt_6 + " order by 1,2,3,4,5,6,7")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_6};"""
+
+
+
+ // Todo: union rewriting
+ // agg function + union rewriting
+// def mv_name_7 = "mv_name_2_4_7"
+// def mv_stmt_7 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// where o_orderdate >= '2023-10-17'"""
+// create_mv_orders(mv_name_7, mv_stmt_7)
+// def job_name_7 = getJobName(db, mv_name_7)
+// waitingMTMVTaskFinished(job_name_7)
+//
+// def sql_stmt_7 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// where o_orderdate >= "2023-10-15" """
+// explain {
+// sql("${sql_stmt_7}")
+// contains "${mv_name_7}(${mv_name_7})"
+// }
+//
+// // group by + union rewriting
+// def mv_name_8 = "mv_name_2_4_8"
+// def mv_stmt_8 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_8, mv_stmt_8)
+// def job_name_8 = getJobName(db, mv_name_8)
+// waitingMTMVTaskFinished(job_name_8)
+//
+// def sql_stmt_8 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// where l_shipdate >= "2023-10-15"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_8}")
+// contains "${mv_name_8}(${mv_name_8})"
+// }
+//
+// // agg function + group by + union rewriting
+// def mv_name_9 = "mv_name_2_4_9"
+// def mv_stmt_9 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_9, mv_stmt_9)
+// def job_name_9 = getJobName(db, mv_name_9)
+// waitingMTMVTaskFinished(job_name_9)
+//
+// def sql_stmt_9 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_4
+// left join lineitem_2_4 on lineitem_2_4.l_orderkey =
orders_2_4.o_orderkey
+// left join partsupp on partsupp.ps_partkey =
lineitem_2_4.l_orderkey
+// where l_shipdate >= "2023-10-15"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_9}")
+// contains "${mv_name_9}(${mv_name_9})"
+// }
+
+ // predicate compensate
+ // agg function + predicate compensate
+ def mv_name_10 = "mv_name_2_4_10"
+ def mv_stmt_10 = """select
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4
+ where o_orderdate >= '2023-10-17'"""
+ create_all_mv(mv_name_10, mv_stmt_10)
+ def job_name_10 = getJobName(db, mv_name_10)
+ waitingMTMVTaskFinished(job_name_10)
+
+ def sql_stmt_10 = """select t.sum_total from (select
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4 where o_orderdate >= "2023-10-17" ) as t
+ where t.count_all = 3"""
+ explain {
+ sql("${sql_stmt_10}")
+ contains "${mv_name_10}(${mv_name_10})"
+ }
+ compare_res(sql_stmt_10 + " order by 1")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_10};"""
+
+
+ // group by + predicate compensate
+
+ def mv_name_11 = "mv_name_2_4_11"
+ def mv_stmt_11 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ where o_orderdate >= "2023-10-17"
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_all_mv(mv_name_11, mv_stmt_11)
+ def job_name_11 = getJobName(db, mv_name_11)
+ waitingMTMVTaskFinished(job_name_11)
+
+ def sql_stmt_11 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ where o_orderdate >= "2023-10-17" and o_totalprice = 1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_11}")
+ notContains "${mv_name_11}(${mv_name_11})"
+ }
+ compare_res(sql_stmt_11 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_11};"""
+
+ def mv_name_16 = "mv_name_2_4_16"
+ def mv_stmt_16 = """select o_orderdate, o_shippriority, o_comment,
o_totalprice
+ from orders_2_4
+ where o_orderdate >= "2023-10-17"
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ o_totalprice """
+ create_all_mv(mv_name_16, mv_stmt_16)
+ def job_name_16 = getJobName(db, mv_name_16)
+ waitingMTMVTaskFinished(job_name_16)
+
+ def sql_stmt_16 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ where o_orderdate >= "2023-10-17" and o_totalprice = 1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+
+ def agg_sql_explain_1 = sql """explain ${sql_stmt_16};"""
+ def mv_index_1 =
agg_sql_explain_1.toString().indexOf("MaterializedViewRewriteSuccessButNotChose:")
+ assert(mv_index_1 != -1)
+
assert(agg_sql_explain_1.toString().substring(mv_index_1).indexOf(mv_name_16)
!= -1)
+
+ compare_res(sql_stmt_16 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_16};"""
+
+ // agg function + group by + predicate compensate
+ def mv_name_12 = "mv_name_2_4_12"
+ def mv_stmt_12 = """select o_orderdate, o_shippriority, o_comment ,
o_totalprice,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4
+ where o_orderdate >= "2023-10-17"
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ o_totalprice """
+ create_all_mv(mv_name_12, mv_stmt_12)
+ def job_name_12 = getJobName(db, mv_name_12)
+ waitingMTMVTaskFinished(job_name_12)
+
+ def sql_stmt_12 = """select t.o_orderdate, t.o_shippriority, t.o_comment,
+ t.sum_total, t.max_total, t.min_total, t.count_all
+ from (
+ select o_orderdate, o_shippriority, o_comment , o_totalprice,
+ sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4 where o_orderdate >= "2023-10-17"
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ o_totalprice
+ ) as t
+ where t.o_totalprice = 1
+ """
+ explain {
+ sql("${sql_stmt_12}")
+ contains "${mv_name_12}(${mv_name_12})"
+ }
+ compare_res(sql_stmt_12 + " order by 1,2,3,4,5,6,7")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_12};"""
+
+
+ // project rewriting
+ // agg function + group by + project rewriting
+ def mv_name_13 = "mv_name_2_4_13"
+ def mv_stmt_13 = """select sum(o_totalprice) as sum_total,
+ max(o_totalprice) as max_total,
+ min(o_totalprice) as min_total,
+ count(*) as count_all,
+ bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey
IN (1, 3) then o_custkey else null end)) cnt_1,
+ bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey
IN (2) then o_custkey else null end)) as cnt_2
+ from orders_2_4
+ where o_orderkey > 1 + 1 """
+ create_all_mv(mv_name_13, mv_stmt_13)
+ def job_name_13 = getJobName(db, mv_name_13)
+ waitingMTMVTaskFinished(job_name_13)
+
+ def sql_stmt_13 = """select sum(o_totalprice) + count(*) ,
+ count(distinct case when o_shippriority > 1 and o_orderkey IN (1,
3) then o_custkey else null end) as cnt_1,
+ count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN (2)
then o_custkey else null end) as cnt_2
+ from orders_2_4
+ where o_orderkey > (-3) + 5 """
+ explain {
+ sql("${sql_stmt_13}")
+ contains "${mv_name_13}(${mv_name_13})"
+ }
+ compare_res(sql_stmt_13 + " order by 1")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_13};"""
+
+
+ // group by + project rewriting
+ def mv_name_14 = "mv_name_2_4_14"
+ def mv_stmt_14 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_4
+ where o_orderkey > 1 + 1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ create_all_mv(mv_name_14, mv_stmt_14)
+ def job_name_14 = getJobName(db, mv_name_14)
+ waitingMTMVTaskFinished(job_name_14)
+
+ def sql_stmt_14 = """select o_orderdate + o_shippriority, o_comment
+ from orders_2_4
+ where o_orderkey > (-3) + 5
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_14}")
+ contains "${mv_name_14}(${mv_name_14})"
+ }
+ compare_res(sql_stmt_14 + " order by 1,2")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_14};"""
+
+
+ // agg function + group by + project rewriting
+ def mv_name_15 = "mv_name_2_4_15"
+ def mv_stmt_15 = """select o_orderdate, o_shippriority, o_comment,
o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end as cnt_1,
+ case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_4
+ where o_orderkey > 1 + 1
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ o_shippriority,
+ o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end,
+ case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end """
+ create_all_mv(mv_name_15, mv_stmt_15)
+ def job_name_15 = getJobName(db, mv_name_15)
+ waitingMTMVTaskFinished(job_name_15)
+
+ def sql_stmt_15 = """select o_shippriority, o_comment, o_shippriority +
o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end as cnt_1,
+ case when O_SHIPPRIORITY > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_4
+ where o_orderkey > (-3) + 5
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end,
+ case when O_SHIPPRIORITY > 2 and o_orderkey IN (2) then o_custkey
else null end """
+ explain {
+ sql("${sql_stmt_15}")
+ contains "${mv_name_15}(${mv_name_15})"
+ }
+ compare_res(sql_stmt_15 + " order by 1,2,3,4,5")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_15};"""
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_5.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_5.groovy
new file mode 100644
index 00000000000..c42e27e1da0
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_5.groovy
@@ -0,0 +1,424 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the query partial, view partial, union rewriting, predicate
compensate, project rewriting.
+ */
+suite("partition_mv_rewrite_dimension_2_5") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_5
+ """
+
+ sql """CREATE TABLE `orders_2_5` (
+ `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_2_5
+ """
+
+ sql """CREATE TABLE `lineitem_2_5` (
+ `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 """
+ drop table if exists partsupp
+ """
+
+ sql """CREATE TABLE `partsupp` (
+ `ps_partkey` INT NULL,
+ `ps_suppkey` INT NULL,
+ `ps_availqty` INT NULL,
+ `ps_supplycost` DECIMAL(15, 2) NULL,
+ `ps_comment` VARCHAR(199) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`, `ps_suppkey`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ insert into orders_2_5 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'k', 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_2_5 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"""
+ insert into partsupp values
+ (1, 1, 1, 99.5, 'yy'),
+ (null, 2, 2, 109.2, 'mm'),
+ (3, null, 1, 99.5, 'yy');
+ """
+
+ sql """analyze table orders_2_5 with sync;"""
+ sql """analyze table lineitem_2_5 with sync;"""
+ sql """analyze table partsupp with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_all_mv = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // Todo: query partial
+ // agg function + query partial
+// def mv_name_1 = "mv_name_2_5_1"
+// def mv_stmt_1 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey"""
+// create_all_mv(mv_name_1, mv_stmt_1)
+// def job_name_1 = getJobName(db, mv_name_1)
+// waitingMTMVTaskFinished(job_name_1)
+//
+// def sql_stmt_1 = """select
+// count(distinct case when o_shippriority > 1 and o_orderkey IN
(1, 3) then o_custkey else null end) as cnt_1,
+// count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN
(2) then o_custkey else null end) as cnt_2,
+// sum(o_totalprice),
+// max(o_totalprice),
+// min(o_totalprice),
+// count(*)
+// from orders_2_5 """
+// explain {
+// sql("${sql_stmt_1}")
+// contains "${mv_name_1}(${mv_name_1})"
+// }
+
+ // group by + query partial
+// def mv_name_2 = "mv_name_2_5_2"
+// def mv_stmt_2 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_2, mv_stmt_2)
+// def job_name_2 = getJobName(db, mv_name_2)
+// waitingMTMVTaskFinished(job_name_2)
+//
+// def sql_stmt_2 = """select o_shippriority, o_comment
+// from orders_2_5
+// group by
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_2}")
+// contains "${mv_name_2}(${mv_name_2})"
+// }
+
+ // agg function + group by + query partial
+// def mv_name_3 = "mv_name_2_5_3"
+// def mv_stmt_3 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_3, mv_stmt_3)
+// def job_name_3 = getJobName(db, mv_name_3)
+// waitingMTMVTaskFinished(job_name_3)
+//
+// def sql_stmt_3 = """select o_shippriority, o_comment,
+// count(distinct case when o_shippriority > 1 and o_orderkey IN
(1, 3) then o_custkey else null end) as cnt_1,
+// count(distinct case when O_SHIPPRIORITY > 2 and o_orderkey IN
(2) then o_custkey else null end) as cnt_2,
+// sum(o_totalprice),
+// max(o_totalprice),
+// min(o_totalprice),
+// count(*)
+// from orders_2_5
+// group by
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_3}")
+// contains "${mv_name_3}(${mv_name_3})"
+// }
+
+ // view partial
+ // group by + query partial
+ def mv_name_5 = "mv_name_2_5_5"
+ def mv_stmt_5 = """select o_orderdate, o_shippriority, o_comment,
l_orderkey, o_orderkey
+ from orders_2_5
+ left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ l_orderkey,
+ o_orderkey """
+ create_mv_orders(mv_name_5, mv_stmt_5)
+ def job_name_5 = getJobName(db, mv_name_5)
+ waitingMTMVTaskFinished(job_name_5)
+
+ def sql_stmt_5 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_5
+ left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+ left join partsupp on partsupp.ps_partkey = lineitem_2_5.l_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_5}")
+ contains "${mv_name_5}(${mv_name_5})"
+ }
+ compare_res(sql_stmt_5 + " order by 1,2,3")
+
+ def sql_stmt_5_2 = """select o_orderdate, o_shippriority, o_comment
+ from orders_2_5
+ left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+ left join partsupp on partsupp.ps_partkey = orders_2_5.o_orderkey
+ group by
+ o_orderdate,
+ o_shippriority,
+ o_comment """
+ explain {
+ sql("${sql_stmt_5_2}")
+ contains "${mv_name_5}(${mv_name_5})"
+ }
+ compare_res(sql_stmt_5_2 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_5};"""
+
+
+ // Todo: union rewriting
+ // agg function + union rewriting
+// def mv_name_7 = "mv_name_2_5_7"
+// def mv_stmt_7 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// where o_orderdate >= '2023-10-17'"""
+// create_mv_orders(mv_name_7, mv_stmt_7)
+// def job_name_7 = getJobName(db, mv_name_7)
+// waitingMTMVTaskFinished(job_name_7)
+//
+// def sql_stmt_7 = """select
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// where o_orderdate >= "2023-10-15" """
+// explain {
+// sql("${sql_stmt_7}")
+// contains "${mv_name_7}(${mv_name_7})"
+// }
+//
+// // group by + union rewriting
+// def mv_name_8 = "mv_name_2_5_8"
+// def mv_stmt_8 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_8, mv_stmt_8)
+// def job_name_8 = getJobName(db, mv_name_8)
+// waitingMTMVTaskFinished(job_name_8)
+//
+// def sql_stmt_8 = """select o_orderdate, o_shippriority, o_comment
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// where l_shipdate >= "2023-10-15"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_8}")
+// contains "${mv_name_8}(${mv_name_8})"
+// }
+//
+// // agg function + group by + union rewriting
+// def mv_name_9 = "mv_name_2_5_9"
+// def mv_stmt_9 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// create_mv_orders(mv_name_9, mv_stmt_9)
+// def job_name_9 = getJobName(db, mv_name_9)
+// waitingMTMVTaskFinished(job_name_9)
+//
+// def sql_stmt_9 = """select o_orderdate, o_shippriority, o_comment,
+// sum(o_totalprice) as sum_total,
+// max(o_totalprice) as max_total,
+// min(o_totalprice) as min_total,
+// count(*) as count_all,
+// bitmap_union(to_bitmap(case when o_shippriority > 1 and
o_orderkey IN (1, 3) then o_custkey else null end)) cnt_1,
+// bitmap_union(to_bitmap(case when o_shippriority > 2 and
o_orderkey IN (2) then o_custkey else null end)) as cnt_2
+// from orders_2_5
+// left join lineitem_2_5 on lineitem_2_5.l_orderkey =
orders_2_5.o_orderkey
+// left join partsupp on partsupp.ps_partkey =
lineitem_2_5.l_orderkey
+// where l_shipdate >= "2023-10-15"
+// group by
+// o_orderdate,
+// o_shippriority,
+// o_comment """
+// explain {
+// sql("${sql_stmt_9}")
+// contains "${mv_name_9}(${mv_name_9})"
+// }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_6.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_6.groovy
new file mode 100644
index 00000000000..f3bce205cda
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_2_6.groovy
@@ -0,0 +1,409 @@
+// 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.
+
+/*
+This suite is a two dimensional test case file.
+It mainly tests the query partial, view partial, union rewriting, predicate
compensate, project rewriting.
+ */
+suite("partition_mv_rewrite_dimension_2_6") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ 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_2_6
+ """
+
+ sql """CREATE TABLE `orders_2_6` (
+ `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_2_6
+ """
+
+ sql """CREATE TABLE `lineitem_2_6` (
+ `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 """
+ drop table if exists partsupp
+ """
+
+ sql """CREATE TABLE `partsupp` (
+ `ps_partkey` INT NULL,
+ `ps_suppkey` INT NULL,
+ `ps_availqty` INT NULL,
+ `ps_supplycost` DECIMAL(15, 2) NULL,
+ `ps_comment` VARCHAR(199) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`, `ps_suppkey`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ insert into orders_2_6 values
+ (null, 1, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (1, null, 'k', 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, 'o', 99.5, 'a', null, 1, 'yy', '2023-10-22'),
+ (1, 3, 'k', 99.5, 'a', 'b', null, 'yy', '2023-10-19'),
+ (2, 1, 'k', 109.2, 'c','d',2, null, '2023-10-18'),
+ (3, 2, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (4, 5, 'o', 99.5, 'a', 'b', 1, 'yy', '2023-10-19');
+ """
+
+ sql """
+ insert into lineitem_2_6 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"""
+ insert into partsupp values
+ (1, 1, 1, 99.5, 'yy'),
+ (null, 2, 2, 109.2, 'mm'),
+ (3, null, 1, 99.5, 'yy');
+ """
+
+ sql """analyze table orders_2_6 with sync;"""
+ sql """analyze table lineitem_2_6 with sync;"""
+ sql """analyze table partsupp with sync;"""
+
+ def create_mv_lineitem = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_mv_orders = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ partition by(o_orderdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ def create_all_mv = { mv_name, mv_sql ->
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+ sql """DROP TABLE IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ ${mv_sql}
+ """
+ }
+
+ 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])
+ }
+ }
+ }
+
+ // Todo: query partial rewriting
+ // union rewriting
+// def mv_name_1 = "mv_name_2_6_1"
+// def mv_stmt_1 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
count(*)
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by l_shipdate, o_orderdate, l_partkey, l_suppkey"""
+// create_mv_lineitem(mv_name_1, mv_stmt_1)
+// def job_name_1 = getJobName(db, mv_name_1)
+// waitingMTMVTaskFinished(job_name_1)
+//
+// def sql_stmt_1 = """select l_shipdate, l_partkey, l_suppkey, count(*)
+// from lineitem_2_6
+// where l_shipdate >= "2023-10-10"
+// group by l_shipdate, l_partkey, l_suppkey """
+// explain {
+// sql("${sql_stmt_1}")
+// contains "${mv_name_1}(${mv_name_1})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_1};"""
+
+ // predicate compensate
+// def mv_name_2 = "mv_name_2_6_2"
+// def mv_stmt_2 = """select l_shipdate, o_orderdate, l_partkey, l_suppkey,
count(*)
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-17"
+// group by l_shipdate, o_orderdate, l_partkey, l_suppkey"""
+// create_mv_lineitem(mv_name_2, mv_stmt_2)
+// def job_name_2 = getJobName(db, mv_name_2)
+// waitingMTMVTaskFinished(job_name_2)
+//
+// def sql_stmt_2 = """select l_shipdate, l_partkey, l_suppkey, count(*)
+// from lineitem_2_6
+// where l_shipdate >= "2023-10-10" and l_partkey > 1 + 1
+// group by l_shipdate, l_partkey, l_suppkey"""
+// explain {
+// sql("${sql_stmt_2}")
+// contains "${mv_name_2}(${mv_name_2})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_2};"""
+
+
+ // project rewriting
+// def mv_name_3 = "mv_name_2_6_3"
+// def mv_stmt_3 = """select o_orderdate, o_shippriority, o_comment,
l_suppkey, o_shippriority + o_custkey,
+// case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey
else null end cnt_1,
+// case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+// from orders_2_6
+// left join lineitem_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where o_custkey > 1 + 1"""
+// create_mv_lineitem(mv_name_3, mv_stmt_3)
+// def job_name_3 = getJobName(db, mv_name_3)
+// waitingMTMVTaskFinished(job_name_3)
+//
+// def sql_stmt_3 = """select o_orderdate, o_shippriority, o_comment,
o_shippriority + o_custkey,
+// case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey
else null end cnt_1,
+// case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+// from orders_2_6
+// where o_custkey > (-3) + 5"""
+// explain {
+// sql("${sql_stmt_3}")
+// contains "${mv_name_3}(${mv_name_3})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_3};"""
+//
+ // Todo: view partial rewriting
+ // union rewriting
+// def mv_name_4 = "mv_name_2_6_4"
+// def mv_stmt_4 = """select l_shipdate, l_partkey, l_orderkey
+// from lineitem_2_6
+// where l_shipdate >= "2023-10-17"
+// group by l_shipdate, l_partkey, l_orderkey"""
+// create_mv_lineitem(mv_name_4, mv_stmt_4)
+// def job_name_4 = getJobName(db, mv_name_4)
+// waitingMTMVTaskFinished(job_name_4)
+//
+// def sql_stmt_4 = """select t.l_shipdate, o_orderdate, t.l_partkey
+// from (select l_shipdate, l_partkey, l_orderkey from lineitem_2_6
group by l_shipdate, l_partkey, l_orderkey) t
+// left join orders_2_6
+// on t.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-10"
+// group by t.l_shipdate, o_orderdate, t.l_partkey"""
+// explain {
+// sql("${sql_stmt_4}")
+// contains "${mv_name_4}(${mv_name_4})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_4};"""
+
+ // predicate compensate
+ def mv_name_5 = "mv_name_2_6_5"
+ def mv_stmt_5 = """select l_shipdate, l_partkey, l_orderkey
+ from lineitem_2_6
+ where l_shipdate >= "2023-10-17"
+ group by l_shipdate, l_partkey, l_orderkey"""
+ create_mv_lineitem(mv_name_5, mv_stmt_5)
+ def job_name_5 = getJobName(db, mv_name_5)
+ waitingMTMVTaskFinished(job_name_5)
+
+ def sql_stmt_5 = """select t.l_shipdate, o_orderdate, t.l_partkey
+ from (select l_shipdate, l_partkey, l_orderkey from lineitem_2_6 group
by l_shipdate, l_partkey, l_orderkey) t
+ left join orders_2_6
+ on t.l_orderkey = orders_2_6.o_orderkey
+ where l_shipdate >= "2023-10-17" and l_partkey > 1 + 1
+ group by t.l_shipdate, o_orderdate, t.l_partkey"""
+ explain {
+ sql("${sql_stmt_5}")
+ contains "${mv_name_5}(${mv_name_5})"
+ }
+ compare_res(sql_stmt_5 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_5};"""
+
+ // project rewriting
+ def mv_name_6 = "mv_name_2_6_6"
+ def mv_stmt_6 = """select l_shipdate, l_partkey, l_orderkey
+ from lineitem_2_6
+ where l_partkey > 1 + 1
+ group by l_shipdate, l_partkey, l_orderkey"""
+ create_mv_lineitem(mv_name_6, mv_stmt_6)
+ def job_name_6 = getJobName(db, mv_name_6)
+ waitingMTMVTaskFinished(job_name_6)
+
+ def sql_stmt_6 = """select t.l_shipdate, o_orderdate, t.l_partkey * 2
+ from (select l_shipdate, l_partkey, l_orderkey from lineitem_2_6 group
by l_shipdate, l_partkey, l_orderkey) t
+ left join orders_2_6
+ on t.l_orderkey = orders_2_6.o_orderkey
+ where l_partkey > (-3) + 5
+ group by t.l_shipdate, o_orderdate, t.l_partkey"""
+ explain {
+ sql("${sql_stmt_6}")
+ contains "${mv_name_6}(${mv_name_6})"
+ }
+ compare_res(sql_stmt_6 + " order by 1,2,3")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_6};"""
+
+
+ // Todo: union rewriting
+ // predicate compensate
+// def mv_name_7 = "mv_name_2_6_7"
+// def mv_stmt_7 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= '2023-10-17'"""
+// create_mv_lineitem(mv_name_7, mv_stmt_7)
+// def job_name_7 = getJobName(db, mv_name_7)
+// waitingMTMVTaskFinished(job_name_7)
+//
+// def sql_stmt_7 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-10" and o_custkey > 1 + 1 """
+// explain {
+// sql("${sql_stmt_7}")
+// contains "${mv_name_7}(${mv_name_7})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_7};"""
+//
+//
+// // project rewriting
+// def mv_name_8 = "mv_name_2_6_8"
+// def mv_stmt_8 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-17" and o_custkey > 1 + 1"""
+// create_mv_lineitem(mv_name_8, mv_stmt_8)
+// def job_name_8 = getJobName(db, mv_name_8)
+// waitingMTMVTaskFinished(job_name_8)
+//
+// def sql_stmt_8 = """select l_shipdate, o_orderdate, l_partkey
+// from lineitem_2_6
+// left join orders_2_6
+// on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+// where l_shipdate >= "2023-10-10" and o_custkey > (-3) + 5 """
+// explain {
+// sql("${sql_stmt_8}")
+// contains "${mv_name_8}(${mv_name_8})"
+// }
+// sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_8};"""
+
+ // predicate compensate
+ // project rewriting
+ def mv_name_9 = "mv_name_2_6_9"
+ def mv_stmt_9 = """ select o_orderdate, o_shippriority, o_comment,
o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end cnt_1,
+ case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_6
+ left join lineitem_2_6
+ on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+ where o_custkey > 1 + 1"""
+ create_mv_orders(mv_name_9, mv_stmt_9)
+ def job_name_9 = getJobName(db, mv_name_9)
+ waitingMTMVTaskFinished(job_name_9)
+
+ def sql_stmt_9 = """select o_orderdate, o_shippriority, o_comment,
o_shippriority + o_custkey,
+ case when o_shippriority > 1 and o_orderkey IN (1, 3) then
o_custkey else null end cnt_1,
+ case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey
else null end as cnt_2
+ from orders_2_6
+ left join lineitem_2_6
+ on lineitem_2_6.l_orderkey = orders_2_6.o_orderkey
+ where o_custkey > (-3) + 5 and o_orderdate >= '2023-10-17' """
+ explain {
+ sql("${sql_stmt_9}")
+ contains "${mv_name_9}(${mv_name_9})"
+ }
+ compare_res(sql_stmt_9 + " order by 1,2,3,4,5,6")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name_9};"""
+
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
