seawinde commented on code in PR #45855:
URL: https://github.com/apache/doris/pull/45855#discussion_r1897057349
##########
regression-test/suites/nereids_p0/hint/test_use_mv.groovy:
##########
@@ -102,4 +128,147 @@ suite("test_use_mv") {
notContains("t1(k1_k2_sumk3)")
}
+ // create database and tables
+ def db = "test_cbo_use_mv"
+ sql "DROP DATABASE IF EXISTS ${db}"
+ sql "CREATE DATABASE IF NOT EXISTS ${db}"
+ sql "use ${db}"
+
+ sql """drop table if exists t1;"""
+ sql """drop table if exists t2;"""
+ sql """drop table if exists t3;"""
+
+ sql """create table t1 (c1 int, c11 int) distributed by hash(c1) buckets 3
properties('replication_num' = '1');"""
+ sql """create table t2 (c2 int, c22 int) distributed by hash(c2) buckets 3
properties('replication_num' = '1');"""
+ sql """create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3
properties('replication_num' = '1');"""
+
+ sql """insert into t1 values (101, 101);"""
+ sql """insert into t2 values (102, 102);"""
+ sql """insert into t3 values (103, 103);"""
+
+ def query1 = """select * from t1"""
+ def query2 = """select * from t2"""
+ def query3 = """select c1 from t1"""
+ def query4 = """select c3 from t3"""
+
+ async_create_mv(db, query1, "mv1")
+ async_create_mv(db, query2, "mv2")
+ async_create_mv(db, query3, "mv3")
+ async_create_mv(db, query4, "mv4")
+
+ sql """use ${db};"""
+ explain {
+ sql """select /*+ use_mv(mv1)*/ * from t1;"""
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ no_use_mv(mv1)*/ * from t1;"""
+ contains("Used: no_use_mv([mv1])")
+ notContains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ sql """use test_use_mv"""
+ explain {
+ sql """select /*+ use_mv(mv1)*/ * from ${db}.t1;"""
+ contains("UnUsed: use_mv([mv1])")
+ }
+ explain {
+ sql """select /*+ no_use_mv(mv1)*/ * from ${db}.t1;"""
+ contains("UnUsed: no_use_mv([mv1])")
+ }
+ sql """use ${db};"""
+ explain {
+ sql """select /*+ use_mv(internal.${db}.mv1)*/ * from t1;"""
+ contains("Used: use_mv([internal, test_cbo_use_mv, mv1])")
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ no_use_mv(internal.${db}.mv1)*/ * from t1;"""
+ contains("Used: no_use_mv([internal, test_cbo_use_mv, mv1])")
+ }
+ sql """use ${db};"""
+ explain {
+ sql """select /*+ use_mv(mv1) */ * from t1"""
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ contains("Used: use_mv([mv1])")
+ }
+ explain {
+ sql """select /*+ no_use_mv(mv1) */ * from t1"""
+ contains("Used: no_use_mv([mv1])")
+ notContains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ use_mv(mv4) */ * from t3"""
+ contains("UnUsed: use_mv([mv4])")
+ }
+ explain {
+ sql """select /*+ no_use_mv(mv4) */ * from t3"""
+ contains("Used: no_use_mv([mv4])")
+ }
+ explain {
+ sql """select /*+ use_mv(mv1, mv2) */ * from t1 union all select *
from t2"""
+ contains("Used: use_mv([mv1].[mv2] )")
+ contains("internal.test_cbo_use_mv.mv2 chose")
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ no_use_mv(mv1, mv2) */ * from t1 union all select *
from t2"""
+ contains("Used: no_use_mv([mv1].[mv2] )")
+ notContains("internal.test_cbo_use_mv.mv2 chose")
+ notContains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ use_mv(mv1) no_use_mv(mv2) */ * from t1 union all
select * from t2"""
+ contains("Used: use_mv([mv1])")
+ contains("Used: no_use_mv([mv2])")
+ notContains("internal.test_cbo_use_mv.mv2 chose")
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ }
+ explain {
+ sql """select /*+ use_mv(mv2) no_use_mv(mv1) */ * from t1 union all
select * from t2"""
+ contains("Used: use_mv([mv2])")
+ contains("Used: no_use_mv([mv1])")
+ notContains("internal.test_cbo_use_mv.mv1 chose")
+ contains("internal.test_cbo_use_mv.mv2 chose")
+ }
+ explain {
+ sql """select /*+ use_mv(mv1, mv3) */ c1 from t1"""
+ contains("Used: use_mv([mv1].[mv3] )")
+ contains("internal.test_cbo_use_mv.mv1 chose")
+ contains("internal.test_cbo_use_mv.mv3 not chose")
+ }
+ explain {
+ sql """select /*+ use_mv(mv3, mv1) */ c1 from t1"""
+ contains("Used: use_mv([mv3].[mv1] )")
Review Comment:
should add `mv_rewrite_success` in Suite.groovy to check if use mv or not
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
