This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 121d52dd37b17f266d873ae43ce80011140813cb 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]
