This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1562d0e644a [test](mtmv)Add column name case sensitive test (#33538)
1562d0e644a is described below
commit 1562d0e644a13e50caae11b3d2f29cb169792da8
Author: zfr95 <87513668+zfr9...@users.noreply.github.com>
AuthorDate: Sat Apr 13 19:08:20 2024 +0800
[test](mtmv)Add column name case sensitive test (#33538)
---
.../mv/dimension/dimension_1.groovy | 222 +++++++++++----------
1 file changed, 112 insertions(+), 110 deletions(-)
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
index 50689b2733d..192f991647c 100644
--- a/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_1.groovy
+++ b/regression-test/suites/nereids_rules_p0/mv/dimension/dimension_1.groovy
@@ -135,7 +135,7 @@ suite("partition_mv_rewrite_dimension_1") {
"""
}
- def create_mv_all = { mv_name, mv_sql ->
+ def create_mv = { mv_name, mv_sql ->
sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
sql """DROP TABLE IF EXISTS ${mv_name}"""
sql"""
@@ -168,9 +168,9 @@ suite("partition_mv_rewrite_dimension_1") {
// 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
+ 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
"""
@@ -179,16 +179,16 @@ suite("partition_mv_rewrite_dimension_1") {
waitingMTMVTaskFinished(job_name_1)
def join_direction_sql_1 = """
- select l_shipdate
- from lineitem_1
- left join orders_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
+ 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}")
@@ -204,9 +204,9 @@ suite("partition_mv_rewrite_dimension_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
+ 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
"""
@@ -215,15 +215,15 @@ suite("partition_mv_rewrite_dimension_1") {
waitingMTMVTaskFinished(job_name_2)
def join_direction_sql_3 = """
- select l_shipdate
- from lineitem_1
- inner join orders_1
+ 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
+ select L_shipdate
+ from orders_1
+ inner join lineitem_1
on orders_1.o_orderkey = lineitem_1.l_orderkey
"""
explain {
@@ -240,44 +240,44 @@ suite("partition_mv_rewrite_dimension_1") {
// 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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"""
+ 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,
@@ -351,7 +351,6 @@ suite("partition_mv_rewrite_dimension_1") {
notContains "${join_filter_mv}(${join_filter_mv})"
}
}
-
}
} else if (i == 4) {
for (int j = 0; j < mv_list.size(); j++) {
@@ -410,14 +409,17 @@ suite("partition_mv_rewrite_dimension_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"""
+ 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
+ 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"""
@@ -426,39 +428,39 @@ suite("partition_mv_rewrite_dimension_1") {
// from lineitem_1
// cross join orders_1"""
def join_type_stmt_5 = """
- select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ 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, l_partkey, l_suppkey, l_linenumber
+ select l_shipdate, l_partkey, l_suppkey, l_Shipmode, l_orderkey
from lineitem_1
left semi join orders_1
- on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+ on lineitem_1.L_ORDERKEY = orders_1.o_orderkey"""
def join_type_stmt_7 = """
- select o_orderdate, o_orderkey, o_custkey, o_orderstatus
+ select o_orderkey, o_custkey, o_Orderdate, o_clerk, o_totalprice
from lineitem_1
right semi join orders_1
on lineitem_1.l_orderkey = orders_1.o_orderkey"""
def join_type_stmt_8 = """
- select l_shipdate, l_partkey, l_suppkey, l_linenumber
+ select l_shipdate, l_partkey, l_suppkeY, l_shipmode, l_orderkey
from lineitem_1
left anti join orders_1
- on lineitem_1.l_orderkey = orders_1.o_orderkey"""
+ on lineitem_1.l_orderkey = orders_1.o_orderkeY"""
def join_type_stmt_9 = """
- select o_orderdate, o_orderkey, o_custkey, o_orderstatus
+ select o_orderkey, o_custkeY, o_orderdate, o_clerk, o_totalprice
from lineitem_1
right 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,
join_type_stmt_3, join_type_stmt_5,
- join_type_stmt_6, join_type_stmt_7,
join_type_stmt_8, join_type_stmt_9]
+ on lineitem_1.L_ORDERKEY = orders_1.o_orderkey"""
+ def join_type_stmt_list = [join_type_stmt_1, join_type_stmt_2,
join_type_stmt_3,
+ join_type_stmt_5, join_type_stmt_6,
join_type_stmt_7, join_type_stmt_8, join_type_stmt_9]
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 || i == 5 || i == 7) {
+ if (i in [2, 5, 7]) {
create_mv_orders(join_type_mv, join_type_stmt_list[i])
} else if (i == 3) {
- create_mv_all(join_type_mv, join_type_stmt_list[i])
+ create_mv(join_type_mv, join_type_stmt_list[i])
} else {
create_mv_lineitem(join_type_mv, join_type_stmt_list[i])
}
@@ -494,24 +496,24 @@ suite("partition_mv_rewrite_dimension_1") {
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
+ 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(*)
+ 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 {
@@ -524,23 +526,23 @@ suite("partition_mv_rewrite_dimension_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
+ 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_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("MaterializedViewRewriteFail:")
@@ -551,35 +553,35 @@ suite("partition_mv_rewrite_dimension_1") {
// 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
+ 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,
+ 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
+ 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("MaterializedViewRewriteFail:")
@@ -616,7 +618,7 @@ suite("partition_mv_rewrite_dimension_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"""
+ 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)
@@ -661,7 +663,7 @@ suite("partition_mv_rewrite_dimension_1") {
// predicate compensate
def predicate_mv_name_1 = "predicate_mv_name_1"
def predicate_mv_stmt_1 = """
- select l_shipdate, o_orderdate, l_partkey
+ select l_shipdatE, o_orderdate, l_partkey
from lineitem_1
left join orders_1
on lineitem_1.l_orderkey = orders_1.o_orderkey
@@ -672,7 +674,7 @@ suite("partition_mv_rewrite_dimension_1") {
waitingMTMVTaskFinished(predicate_job_name_1)
def predicate_sql_1 = """
- select l_shipdate, o_orderdate, l_partkey
+ select l_shipdate, o_orderdate, l_partkeY
from lineitem_1
left join orders_1
on lineitem_1.l_orderkey = orders_1.o_orderkey
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
