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
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new e3c740df4b4 branch-2.1: [test](mtmv)Check whether the constants in SQL
will hit mtmv #40442 (#46671)
e3c740df4b4 is described below
commit e3c740df4b41856a5122c4c446ab028afa226fef
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Jan 9 22:19:55 2025 +0800
branch-2.1: [test](mtmv)Check whether the constants in SQL will hit mtmv
#40442 (#46671)
Cherry-picked from #40442
Co-authored-by: zfr95 <[email protected]>
---
.../constant_genaration_random_test_1.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_10.groovy | 186 ++++++++++++++++++++
.../constant_genaration_random_test_11.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_12.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_13.groovy | 186 ++++++++++++++++++++
.../constant_genaration_random_test_2.groovy | 188 +++++++++++++++++++++
.../constant_genaration_random_test_3.groovy | 186 ++++++++++++++++++++
.../constant_genaration_random_test_4.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_5.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_6.groovy | 186 ++++++++++++++++++++
.../constant_genaration_random_test_7.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_8.groovy | 187 ++++++++++++++++++++
.../constant_genaration_random_test_9.groovy | 187 ++++++++++++++++++++
13 files changed, 2428 insertions(+)
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_1.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_1.groovy
new file mode 100644
index 00000000000..ba2fb61ce82
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_1.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where a constant appears in the select list in basic SQL.
+// eg: select plac_1 from tb
+suite ("constant_genaration_random_mtmv_1", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_1"
+ String table2 = "orders_constant_genaration_1"
+ String mtmv_name = "constant_genaration_mtmv_1"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql1 = "select plac_1 from ${table1}"
+
+ def sql_lists = [sql1]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_10.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_10.groovy
new file mode 100644
index 00000000000..9ea7a32cf09
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_10.groovy
@@ -0,0 +1,186 @@
+// 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.
+
+// Test the case where multiple different constants appear in an SQL statement
containing subqueries and the JOIN operator.
+// eg: select plac_1 from tb1 left join (select plac_2 as col1 from tb2) as t1
on plac_1 = t1.col1
+suite ("constant_genaration_random_mtmv_10",
"constant_genaration_random_mtmv") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_10"
+ String table2 = "orders_constant_genaration_10"
+ String mtmv_name = "constant_genaration_mtmv_10"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql12 = "select plac_1 from ${table1} left join (select plac_2 as col1
from ${table2}) as t1 on plac_1 = t1.col1 "
+ def sql_lists = [sql12]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_11.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_11.groovy
new file mode 100644
index 00000000000..83cad8259b9
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_11.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where multiple different constants appear in an SQL statement
containing subqueries, the JOIN operator, and the aggregation operator.
+// eg: select plac_1 from tb1 left join (select plac_2 as col1 from tb2) as t1
on plac_1 = t1.col1 group by plac_1
+suite ("constant_genaration_random_mtmv_11",
"constant_genaration_random_mtmv") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_11"
+ String table2 = "orders_constant_genaration_11"
+ String mtmv_name = "constant_genaration_mtmv_11"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql13 = "select plac_1 from ${table1} left join (select plac_2 as col1
from ${table2}) as t1 on plac_1 = t1.col1 group by plac_1"
+
+ def sql_lists = [sql13]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_12.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_12.groovy
new file mode 100644
index 00000000000..ce8c5c81736
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_12.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where multiple different constants appear in an SQL statement
containing subqueries, the JOIN operator, and the aggregation operator.
+// eg: select t1.col1 from (select plac_2 as col1 from tb1 where plac_2 =
plac_2) as t1 left join (select plac_1 as col2 from tb2) as t2 on t1.col1 =
t2.col2 group by t1.col1
+suite ("constant_genaration_random_mtmv_12",
"constant_genaration_random_mtmv") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_12"
+ String table2 = "orders_constant_genaration_12"
+ String mtmv_name = "constant_genaration_mtmv_12"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql14 = "select t1.col1 from (select plac_2 as col1 from ${table2}
where plac_2 = plac_2) as t1 left join (select plac_1 as col2 from ${table1})
as t2 on t1.col1 = t2.col2 group by t1.col1"
+
+ def sql_lists = [sql14]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_13.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_13.groovy
new file mode 100644
index 00000000000..9d4ba87fb9a
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_13.groovy
@@ -0,0 +1,186 @@
+// 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.
+
+// Test the case where multiple different constants appear in an SQL statement
with subqueries, JOIN operator, FILTER operator, and aggregation operator.
+// eg: select t1.col1 from (select plac_2 as col1 from tb2 where plac_2 =
plac_2 group by col1) as t1 left join (select plac_1 as col2 from tb1) as t2 on
t1.col1 = t2.col2 group by t1.col1
+suite ("constant_genaration_random_mtmv_13",
"constant_genaration_random_mtmv") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_13"
+ String table2 = "orders_constant_genaration_13"
+ String mtmv_name = "constant_genaration_mtmv_13"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql15 = "select t1.col1 from (select plac_2 as col1 from ${table2}
where plac_2 = plac_2 group by col1) as t1 left join (select plac_1 as col2
from ${table1}) as t2 on t1.col1 = t2.col2 group by t1.col1"
+ def sql_lists = [sql15]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_2.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_2.groovy
new file mode 100644
index 00000000000..8ddae0f7b23
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_2.groovy
@@ -0,0 +1,188 @@
+// 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.
+
+// Test the case where a constant appears in the select list position of an
SQL statement containing subqueries.
+// eg: select col1 from (select plac_1 as col1 from tb) as t1
+suite ("constant_genaration_random_mtmv_2", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_2"
+ String table2 = "orders_constant_genaration_2"
+ String mtmv_name = "constant_genaration_mtmv_2"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+
+ def sql3 = "select col1 from (select plac_1 as col1 from ${table1}) as t1"
+
+ def sql_lists = [sql3]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_3.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_3.groovy
new file mode 100644
index 00000000000..3d5b521843b
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_3.groovy
@@ -0,0 +1,186 @@
+// 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.
+
+// Test the case where a constant appears in the select list position of an
SQL statement containing the GROUP BY operator.
+// eg: select plac_1 from tb group by plac_1
+suite ("constant_genaration_random_mtmv_3", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_3"
+ String table2 = "orders_constant_genaration_3"
+ String mtmv_name = "constant_genaration_mtmv_3"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql4 = "select plac_1 from ${table1} group by plac_1"
+ def sql_lists = [sql4]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_4.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_4.groovy
new file mode 100644
index 00000000000..e79c2d767af
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_4.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where a constant appears in the select list position of an
SQL statement containing the GROUP BY operator and the FILTER operator.
+// eg: select plac_1 from tb where plac_1 >= 1 group by plac_1
+suite ("constant_genaration_random_mtmv_4", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_4"
+ String table2 = "orders_constant_genaration_4"
+ String mtmv_name = "constant_genaration_mtmv_4"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql5 = "select plac_1 from ${table1} where plac_1 >= 1 group by plac_1"
+
+ def sql_lists = [sql5]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_5.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_5.groovy
new file mode 100644
index 00000000000..45d518af26d
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_5.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where a constant appears in the select list position of a
subquery in an SQL statement that contains the GROUP BY operator and the FILTER
operator.
+// eg: select col1 from (select plac_1 as col1 from tb where plac_1 >= 1) as t1
+suite ("constant_genaration_random_mtmv_5", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_5"
+ String table2 = "orders_constant_genaration_5"
+ String mtmv_name = "constant_genaration_mtmv_5"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql7 = "select col1 from (select plac_1 as col1 from ${table1} where
plac_1 >= 1) as t1"
+
+ def sql_lists = [sql7]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_6.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_6.groovy
new file mode 100644
index 00000000000..48a6015d40c
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_6.groovy
@@ -0,0 +1,186 @@
+// 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.
+
+// Test the case where a constant appears in the select list position of an
SQL statement containing the JOIN operator.
+// eg: select plac_1 from tb left join (select plac_1 as col1 from tb) as t1
on plac_1 = t1.col1
+suite ("constant_genaration_random_mtmv_6", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_6"
+ String table2 = "orders_constant_genaration_6"
+ String mtmv_name = "constant_genaration_mtmv_6"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql8 = "select plac_1 from ${table1} left join (select plac_1 as col1
from ${table1}) as t1 on plac_1 = t1.col1 "
+ def sql_lists = [sql8]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_7.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_7.groovy
new file mode 100644
index 00000000000..81ea82eae4e
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_7.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where constants appear in an SQL statement containing the
JOIN operator and subqueries.
+// eg: select plac_1 from tb1 left join (select plac_1 as col1 from tb2) as t1
on plac_1 = t1.col1 group by plac_1
+suite ("constant_genaration_random_mtmv_7", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_7"
+ String table2 = "orders_constant_genaration_7"
+ String mtmv_name = "constant_genaration_mtmv_7"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+
+ def sql9 = "select plac_1 from ${table1} left join (select plac_1 as col1
from ${table1}) as t1 on plac_1 = t1.col1 group by plac_1"
+ def sql_lists = [sql9]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_8.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_8.groovy
new file mode 100644
index 00000000000..042c3c4b334
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_8.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where constants appear in an SQL statement for joining two
subqueries.
+// eg: select t1.col1 from (select plac_1 as col1 from tb1 where plac_1 =
plac_1) as t1 left join (select plac_1 as col2 from tb2) as t2 on t1.col1 =
t2.col2 group by t1.col1
+suite ("constant_genaration_random_mtmv_8", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_8"
+ String table2 = "orders_constant_genaration_8"
+ String mtmv_name = "constant_genaration_mtmv_8"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql10 = "select t1.col1 from (select plac_1 as col1 from ${table1}
where plac_1 = plac_1) as t1 left join (select plac_1 as col2 from ${table1})
as t2 on t1.col1 = t2.col2 group by t1.col1"
+
+ def sql_lists = [sql10]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_9.groovy
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_9.groovy
new file mode 100644
index 00000000000..e6be7895afa
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/genera_constant_sql/constant_genaration_random_test_9.groovy
@@ -0,0 +1,187 @@
+// 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.
+
+// Test the case where constants appear in an SQL statement that joins two
subqueries with a GROUP BY clause.
+// eg: select t1.col1 from (select plac_1 as col1 from tb1 where plac_1 =
plac_1 group by col1) as t1 left join (select plac_1 as col2 from tb2) as t2 on
t1.col1 = t2.col2 group by t1.col1
+suite ("constant_genaration_random_mtmv_9", "constant_genaration_random_mtmv")
{
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ String table1 = "lineitem_constant_genaration_9"
+ String table2 = "orders_constant_genaration_9"
+ String mtmv_name = "constant_genaration_mtmv_9"
+
+ sql """
+ drop table if exists ${table2}
+ """
+
+ sql """CREATE TABLE `${table2}` (
+ `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 ${table1}
+ """
+
+ sql """CREATE TABLE `${table1}` (
+ `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 ${table2} values
+ (null, 1, 'k', 99.5, 'a', 'b', 1, 'yy', '2023-10-17'),
+ (0, null, 'o', 109.2, 'c','d',2, 'mm', '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 ${table1} 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'),
+ (0, 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'),
+ (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 ${table2} with sync;"""
+ sql """analyze table ${table1} with sync;"""
+
+ def create_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])
+ }
+ }
+ }
+
+ def param1 = "2"
+ def param2 = "l_orderkey"
+ def param3 = "abs(l_orderkey)"
+ def param4 = "2+l_orderkey"
+ def param5 = "2+abs(l_orderkey)"
+ def param6 = "l_orderkey + abs(l_orderkey)"
+ def param_lists1 = [param1, param2, param3, param4, param5, param6]
+
+ def param7 = "2"
+ def param8 = "o_orderkey"
+ def param9 = "abs(o_orderkey)"
+ def param10 = "2+o_orderkey"
+ def param11 = "2+abs(o_orderkey)"
+ def param12 = "o_orderkey + abs(o_orderkey)"
+ def param_lists2 = [param7, param8, param9, param10, param11, param12]
+
+ def sql11 = "select t1.col1 from (select plac_1 as col1 from ${table1}
where plac_1 = plac_1 group by col1) as t1 left join (select plac_1 as col2
from ${table1}) as t2 on t1.col1 = t2.col2 group by t1.col1"
+
+ def sql_lists = [sql11]
+
+ def check_not_chose = { def str, def mv_name ->
+ def sql_explain = sql """explain ${str};"""
+ def mv_index_1 =
sql_explain.toString().indexOf("MaterializedViewRewriteSuccessAndChose:")
+ def mv_index_2 =
sql_explain.toString().indexOf("MaterializedViewRewriteFail:")
+ assert(mv_index_1 != -1)
+ assert(mv_index_2 != -1)
+ if (sql_explain.toString().substring(mv_index_1,
mv_index_2).indexOf(mv_name) != -1) {
+ return true
+ }
+ return false
+ }
+
+ for (int i = 0; i < sql_lists.size(); i++) {
+ for (int j = 0; j < param_lists1.size(); j++) {
+ for (int k = 0; k < param_lists2.size(); k++) {
+ logger.info("i: " + i + ", j: " + j + ", k: " + k)
+ def str = sql_lists[i].replaceAll("plac_1",
param_lists1[j]).replaceAll("plac_2", param_lists2[k]).replaceAll(" on 2 = ", "
on l_orderkey = ")
+
+ create_mv(mtmv_name, str)
+ waitingMTMVTaskFinishedByMvName(mtmv_name)
+
+ check_not_chose(str, mtmv_name)
+ compare_res(str + " order by 1")
+ }
+ }
+ }
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
