This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-2.0-alpha
in repository https://gitbox.apache.org/repos/asf/doris.git
commit ed2dd67fd9abee625e98b811d861d33a62798b69
Author: starocean999 <40539150+starocean...@users.noreply.github.com>
AuthorDate: Thu Apr 20 17:02:36 2023 +0800
[fix](nereids)disable SelectMaterializedIndexWithAggregate rule (#18380)
* [fix](nereids)disable SelectMaterializedIndexWithAggregate rule
* rebase code
* disable related test cases
* remove failed test cases for now
---
.../doris/nereids/jobs/batch/NereidsRewriter.java | 4 +-
.../doris/nereids/rules/mv/SelectMvIndexTest.java | 35 ++++++++++
.../nereids/rules/mv/SelectRollupIndexTest.java | 8 +++
.../join/colocate_join_with_rollup.groovy | 21 +++---
.../suites/nereids_syntax_p0/rollup.groovy | 37 +++++------
.../nereids_tpch_shape_sf1000_p0/shape/q21.groovy | 75 ++++++++++++++++++++++
6 files changed, 150 insertions(+), 30 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/NereidsRewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/NereidsRewriter.java
index fb1769f7c0..d8be8cb357 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/NereidsRewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/NereidsRewriter.java
@@ -30,7 +30,6 @@ import
org.apache.doris.nereids.rules.analysis.LogicalSubQueryAliasToLogicalProj
import org.apache.doris.nereids.rules.expression.ExpressionNormalization;
import org.apache.doris.nereids.rules.expression.ExpressionOptimization;
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
-import org.apache.doris.nereids.rules.mv.SelectMaterializedIndexWithAggregate;
import
org.apache.doris.nereids.rules.mv.SelectMaterializedIndexWithoutAggregate;
import org.apache.doris.nereids.rules.rewrite.logical.AdjustNullable;
import
org.apache.doris.nereids.rules.rewrite.logical.AggScalarSubQueryToWindowFunction;
@@ -250,7 +249,8 @@ public class NereidsRewriter extends BatchRewriteJob {
topic("MV optimization",
topDown(
- new SelectMaterializedIndexWithAggregate(),
+ // TODO: enable this rule after
https://github.com/apache/doris/issues/18263 is fixed
+ // new SelectMaterializedIndexWithAggregate(),
new SelectMaterializedIndexWithoutAggregate(),
new PushdownFilterThroughProject(),
new MergeProjects(),
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
index 48306242dd..2e9b53b4b1 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectMvIndexTest.java
@@ -152,6 +152,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMvWithTwoTable(union, EMPS_MV_NAME, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggQueryOnAggMV1() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, sum(salary), "
@@ -161,6 +162,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggQueryOnAggMV2() throws Exception {
String agg = "select deptno, sum(salary) from " + EMPS_TABLE_NAME + "
group by deptno";
@@ -171,6 +173,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggQueryOnAggMV3() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, commission, sum(salary)"
@@ -197,6 +200,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* There will be a compensating Project added after matching of the
Aggregate.
*/
+ @Disabled //ISSUE #18263
@Test
public void testAggQuqeryOnAggMV5() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, commission, sum(salary)"
@@ -210,6 +214,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* There will be a compensating Project + Filter added after matching of
the Aggregate.
*/
+ @Disabled //ISSUE #18263
@Test
public void testAggQuqeryOnAggMV6() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, commission, sum(salary)"
@@ -224,6 +229,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
* Aggregation query with groupSets at coarser level of aggregation than
* aggregation materialized view.
*/
+ @Disabled //ISSUE #18263
@Test
public void testGroupingSetQueryOnAggMV() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select empid, deptno, sum(salary) "
@@ -236,6 +242,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* Aggregation query at coarser level of aggregation than aggregation
materialized view.
*/
+ @Disabled //ISSUE #18263
@Test
public void testAggQuqeryOnAggMV7() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, commission, sum(salary) "
@@ -245,6 +252,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggQueryOnAggMV8() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, sum(salary) "
@@ -271,6 +279,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* Query with rollup and arithmetic expr
*/
+ @Disabled //ISSUE #18263
@Test
public void testAggQueryOnAggMV10() throws Exception {
String createMVSql = "create materialized view " + EMPS_MV_NAME + " as
select deptno, commission, sum(salary) "
@@ -306,6 +315,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMvWithTwoTable(query, EMPS_TABLE_NAME, EMPS_TABLE_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testJoinOnLeftProjectToJoin() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME
@@ -320,6 +330,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, ImmutableMap.of(EMPS_TABLE_NAME, EMPS_MV_NAME,
DEPTS_TABLE_NAME, DEPTS_MV_NAME));
}
+ @Disabled //ISSUE #18263
@Test
public void testJoinOnRightProjectToJoin() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, sum(salary), sum"
@@ -334,6 +345,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, ImmutableMap.of(EMPS_TABLE_NAME, EMPS_MV_NAME,
DEPTS_TABLE_NAME, DEPTS_MV_NAME));
}
+ @Disabled //ISSUE #18263
@Test
public void testJoinOnProjectsToJoin() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, sum(salary), sum"
@@ -444,6 +456,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVAggregateFuncs1() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select empid, deptno, sum(salary) "
@@ -453,6 +466,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVAggregateFuncs2() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select empid, deptno, sum(salary) "
@@ -462,6 +476,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVAggregateFuncs3() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select empid, deptno, sum(salary) "
@@ -471,6 +486,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVAggregateFuncs4() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select empid, deptno, sum(salary) "
@@ -480,6 +496,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVAggregateFuncs5() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, empid, sum(salary) "
@@ -489,6 +506,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVCalcGroupByQuery1() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, empid, sum(salary) "
@@ -499,6 +517,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVCalcGroupByQuery2() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, empid, sum(salary) "
@@ -509,6 +528,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVCalcGroupByQuery3() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select deptno, empid, sum(salary) "
@@ -567,6 +587,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, ImmutableMap.of(EMPS_TABLE_NAME, EMPS_MV_NAME));
}
+ @Disabled //ISSUE #18263
@Test
public void testMultiMVMultiUsage() throws Exception {
String createEmpsMVSql01 = "create materialized view emp_mv_01 as
select deptno, empid, salary "
@@ -591,6 +612,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, ImmutableMap.of(EMPS_TABLE_NAME, EMPS_MV_NAME,
DEPTS_TABLE_NAME, DEPTS_TABLE_NAME));
}
+ @Disabled //ISSUE #18263
@Test
public void testAggregateMVOnCountDistinctQuery1() throws Exception {
String createEmpsMVsql = "create materialized view " + EMPS_MV_NAME +
" as select empid, deptno, sum(salary) "
@@ -728,6 +750,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* bitmap_union_count(to_bitmap()) -> bitmap_union_count without having
*/
+ @Disabled //ISSUE #18263
@Test
public void testBitmapUnionRewrite() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME
@@ -742,6 +765,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* bitmap_union_count(to_bitmap()) -> bitmap_union_count with having
*/
+ @Disabled //ISSUE #18263
@Test
public void testBitmapUnionInQuery() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME
@@ -753,6 +777,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
singleTableTest(query, USER_TAG_MV_NAME, true);
}
+ @Disabled //ISSUE #18263
@Test
public void testBitmapUnionInSubquery() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -792,6 +817,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, ImmutableMap.of("user_tags", "user_tags"));
}
+ @Disabled //ISSUE #18263
@Test
public void testTwoTupleInQuery() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -863,6 +889,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
/**
* count distinct to bitmap_union_count in mv
*/
+ @Disabled //ISSUE #18263
@Test
public void testCountDistinctToBitmap() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -895,6 +922,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, USER_TAG_TABLE_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testNDVToHll() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -918,6 +946,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
// dorisAssert.query(query).explainContains(USER_TAG_MV_NAME,
"hll_union_agg");
}
+ @Disabled //ISSUE #18263
@Test
public void testHLLUnionFamilyRewrite() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -966,6 +995,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, EMPS_TABLE_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testCountFieldInQuery() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -982,6 +1012,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, USER_TAG_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void testCreateMVBaseBitmapAggTable() throws Exception {
String createTableSQL = "create table " + HR_DB_NAME + ".agg_table "
@@ -1000,6 +1031,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
dropTable("agg_table", true);
}
+ @Disabled //ISSUE #18263
@Test
public void testSelectMVWithTableAlias() throws Exception {
String createUserTagMVSql = "create materialized view " +
USER_TAG_MV_NAME + " as select user_id, "
@@ -1016,6 +1048,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
testMv(query, USER_TAG_MV_NAME);
}
+ @Disabled //ISSUE #18263
@Test
public void selectBitmapMvWithProjectTest1() throws Exception {
createTable("create table t(\n"
@@ -1037,6 +1070,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
dropTable("t", true);
}
+ @Disabled //ISSUE #18263
@Test
public void selectBitmapMvWithProjectTest2() throws Exception {
createTable("create table t(\n"
@@ -1058,6 +1092,7 @@ class SelectMvIndexTest extends
BaseMaterializedIndexSelectTest implements MemoP
dropTable("t", true);
}
+ @Disabled //ISSUE #18263
@Test
public void selectBitmapMvWithProjectMultiMv() throws Exception {
createTable("create table t(\n"
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
index 947a30e449..db0cdfc966 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/SelectRollupIndexTest.java
@@ -98,6 +98,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
+ ");");
}
+ @Disabled //ISSUE #18263
@Test
public void testAggMatching() {
singleTableTest("select k2, sum(v1) from t group by k2", "r1", true);
@@ -132,6 +133,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
PlanChecker.from(connectContext).checkPlannerResult("select k2,
sum(v1) from t group by k2");
}
+ @Disabled //ISSUE #18263
@Test
public void testTranslateWhenPreAggIsOff() {
singleTableTest("select k2, min(v1) from t group by k2", scan -> {
@@ -313,6 +315,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
}));
}
+ @Disabled //ISSUE #18263
@Test
public void testKeysOnlyQuery() throws Exception {
singleTableTest("select k1 from t1", "r3", false);
@@ -326,6 +329,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
/**
* Rollup with all the keys should be used.
*/
+ @Disabled //ISSUE #18263
@Test
public void testRollupWithAllTheKeys() throws Exception {
createTable(" CREATE TABLE `t4` (\n"
@@ -351,16 +355,19 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
singleTableTest("select k1, sum(v1) from t4 group by k1", "r1", true);
}
+ @Disabled //ISSUE #18263
@Test
public void testComplexGroupingExpr() throws Exception {
singleTableTest("select k2 + 1, sum(v1) from t group by k2 + 1", "r1",
true);
}
+ @Disabled //ISSUE #18263
@Test
public void testCountDistinctKeyColumn() {
singleTableTest("select k2, count(distinct k3) from t group by k2",
"r4", true);
}
+ @Disabled //ISSUE #18263
@Test
public void testCountDistinctValueColumn() {
singleTableTest("select k1, count(distinct v1) from t group by k1",
scan -> {
@@ -371,6 +378,7 @@ class SelectRollupIndexTest extends
BaseMaterializedIndexSelectTest implements M
});
}
+ @Disabled //ISSUE #18263
@Test
public void testOnlyValueColumn1() throws Exception {
singleTableTest("select sum(v1) from t", "r1", true);
diff --git
a/regression-test/suites/nereids_p0/join/colocate_join_with_rollup.groovy
b/regression-test/suites/nereids_p0/join/colocate_join_with_rollup.groovy
index 73ffd09b4f..235b86b013 100644
--- a/regression-test/suites/nereids_p0/join/colocate_join_with_rollup.groovy
+++ b/regression-test/suites/nereids_p0/join/colocate_join_with_rollup.groovy
@@ -174,16 +174,17 @@ suite("colocate_join_with_rollup", "nereids_p0") {
contains "COLOCATE"
}
- explain {
- // hit diffrent rollup, no colocate
- sql("""select sum_col1,sum_col2
- from
- (select datekey,sum(sum_col1) as sum_col1 from
test_query_no_colocate group by datekey) t1
- left join
- (select datekey,sum(sum_col2) as sum_col2 from
test_query_no_colocate group by datekey) t2
- on t1.datekey = t2.datekey""")
- notContains "COLOCATE"
- }
+ // ISSUE #18263
+ // explain {
+ // // hit diffrent rollup, no colocate
+ // sql("""select sum_col1,sum_col2
+ // from
+ // (select datekey,sum(sum_col1) as sum_col1 from
test_query_no_colocate group by datekey) t1
+ // left join
+ // (select datekey,sum(sum_col2) as sum_col2 from
test_query_no_colocate group by datekey) t2
+ // on t1.datekey = t2.datekey""")
+ // notContains "COLOCATE"
+ // }
sql """ DROP TABLE IF EXISTS test_query_colocate1 """
sql """ DROP TABLE IF EXISTS test_query_colocate2 """
diff --git a/regression-test/suites/nereids_syntax_p0/rollup.groovy
b/regression-test/suites/nereids_syntax_p0/rollup.groovy
index 0b293b3fa8..f8dba84ee1 100644
--- a/regression-test/suites/nereids_syntax_p0/rollup.groovy
+++ b/regression-test/suites/nereids_syntax_p0/rollup.groovy
@@ -71,26 +71,27 @@ suite("rollup") {
sql "SET enable_fallback_to_original_planner=false"
- explain {
- sql("select k2, sum(v1) from rollup_t1 group by k2")
- contains("rollup_t1(r1)")
- contains("PREAGGREGATION: ON")
- }
+ // ISSUE #18263
+ // explain {
+ // sql("select k2, sum(v1) from rollup_t1 group by k2")
+ // contains("rollup_t1(r1)")
+ // contains("PREAGGREGATION: ON")
+ // }
- explain {
- sql("select k1, sum(v1) from rollup_t1 group by k1")
- contains("rollup_t1(rollup_t1)")
- contains("PREAGGREGATION: ON")
- }
+ // explain {
+ // sql("select k1, sum(v1) from rollup_t1 group by k1")
+ // contains("rollup_t1(rollup_t1)")
+ // contains("PREAGGREGATION: ON")
+ // }
- order_qt_rollup1 "select k2, sum(v1) from rollup_t1 group by k2"
+ // order_qt_rollup1 "select k2, sum(v1) from rollup_t1 group by k2"
- order_qt_rollup2 "select k1, sum(v1) from rollup_t1 group by k1"
+ // order_qt_rollup2 "select k1, sum(v1) from rollup_t1 group by k1"
- explain {
- sql("select k1, max(v1) from rollup_t1 group by k1")
- contains("rollup_t1(rollup_t1)")
- contains("PREAGGREGATION: OFF")
- contains("Aggregate operator don't match, aggregate function: max(v1),
column aggregate type: SUM")
- }
+ // explain {
+ // sql("select k1, max(v1) from rollup_t1 group by k1")
+ // contains("rollup_t1(rollup_t1)")
+ // contains("PREAGGREGATION: OFF")
+ // contains("Aggregate operator don't match, aggregate function:
max(v1), column aggregate type: SUM")
+ // }
}
diff --git
a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
new file mode 100644
index 0000000000..131f2d5a7b
--- /dev/null
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
@@ -0,0 +1,75 @@
+/*
+ * 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.
+ */
+
+suite("q21") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+ sql 'set enable_new_cost_model=false'
+ sql 'set global exec_mem_limit = 21G'
+ sql 'set global broadcast_row_count_limit = 30000000'
+
+ // qt_select """
+ // explain shape plan
+ // select
+ // s_name,
+ // count(*) as numwait
+ // from
+ // supplier,
+ // lineitem l1,
+ // orders,
+ // nation
+ // where
+ // s_suppkey = l1.l_suppkey
+ // and o_orderkey = l1.l_orderkey
+ // and o_orderstatus = 'F'
+ // and l1.l_receiptdate > l1.l_commitdate
+ // and exists (
+ // select
+ // *
+ // from
+ // lineitem l2
+ // where
+ // l2.l_orderkey = l1.l_orderkey
+ // and l2.l_suppkey <> l1.l_suppkey
+ // )
+ // and not exists (
+ // select
+ // *
+ // from
+ // lineitem l3
+ // where
+ // l3.l_orderkey = l1.l_orderkey
+ // and l3.l_suppkey <> l1.l_suppkey
+ // and l3.l_receiptdate > l3.l_commitdate
+ // )
+ // and s_nationkey = n_nationkey
+ // and n_name = 'SAUDI ARABIA'
+ // group by
+ // s_name
+ // order by
+ // numwait desc,
+ // s_name
+ // limit 100;
+ // """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
