This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 5b0f9186e16 branch-3.1: [fix](hint)should pull up subquery alias with
hint for all kind of root nodes #58767 (#58859)
5b0f9186e16 is described below
commit 5b0f9186e166a4e65ec718156be202acf67804e5
Author: starocean999 <[email protected]>
AuthorDate: Wed Dec 10 12:10:15 2025 +0800
branch-3.1: [fix](hint)should pull up subquery alias with hint for all kind
of root nodes #58767 (#58859)
picked from #58767
---
.../processor/pre/PullUpSubqueryAliasToCTE.java | 69 ++++++---
.../suites/nereids_p0/hint/test_hint.groovy | 158 +++++++++++++++++++++
2 files changed, 205 insertions(+), 22 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java
index 8e8889f5e62..3d1679ed3c9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java
@@ -20,11 +20,15 @@ package org.apache.doris.nereids.processor.pre;
import org.apache.doris.nereids.StatementContext;
import org.apache.doris.nereids.analyzer.UnboundRelation;
import org.apache.doris.nereids.analyzer.UnboundResultSink;
+import org.apache.doris.nereids.analyzer.UnboundTableSink;
import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalCTE;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFileSink;
+import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.trees.plans.logical.LogicalSelectHint;
+import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
import org.apache.doris.nereids.trees.plans.logical.LogicalSubQueryAlias;
import java.util.ArrayList;
@@ -39,27 +43,25 @@ public class PullUpSubqueryAliasToCTE extends
PlanPreprocessor {
@Override
public Plan visitUnboundResultSink(UnboundResultSink<? extends Plan>
unboundResultSink,
StatementContext context) {
- Plan topPlan = visitChildren(this, unboundResultSink, context);
- if (!aliasQueries.isEmpty()) {
- if (((UnboundResultSink) topPlan).child() instanceof LogicalCTE) {
- LogicalCTE logicalCTE = (LogicalCTE) ((UnboundResultSink)
topPlan).child();
- List<LogicalSubQueryAlias<Plan>> subQueryAliases = new
ArrayList<>();
- subQueryAliases.addAll(logicalCTE.getAliasQueries());
- subQueryAliases.addAll(aliasQueries);
- return topPlan.withChildren(
- new LogicalCTE<>(subQueryAliases, (LogicalPlan)
((UnboundResultSink) topPlan).child()));
- }
- return topPlan.withChildren(
- new LogicalCTE<>(aliasQueries, (LogicalPlan)
((UnboundResultSink) topPlan).child()));
- }
- return topPlan;
+ return createCteForRootNode(unboundResultSink, context);
+ }
+
+ @Override
+ public Plan visitUnboundTableSink(UnboundTableSink<? extends Plan>
unboundTableSink,
+ StatementContext context) {
+ return createCteForRootNode(unboundTableSink, context);
+ }
+
+ @Override
+ public Plan visitLogicalFileSink(LogicalFileSink<? extends Plan>
logicalFileSink,
+ StatementContext context) {
+ return createCteForRootNode(logicalFileSink, context);
}
@Override
public Plan visitLogicalSubQueryAlias(LogicalSubQueryAlias<? extends Plan>
alias,
StatementContext context) {
- if (alias.child() instanceof LogicalSelectHint
- && ((LogicalSelectHint) alias.child()).isIncludeLeading()) {
+ if (findLeadingHintIgnoreSortAndLimit(alias.child())) {
aliasQueries.add((LogicalSubQueryAlias<Plan>) alias);
List<String> tableName = new ArrayList<>();
tableName.add(alias.getAlias());
@@ -72,12 +74,7 @@ public class PullUpSubqueryAliasToCTE extends
PlanPreprocessor {
public Plan visitLogicalCTE(LogicalCTE<? extends Plan> logicalCTE,
StatementContext context) {
List<LogicalSubQueryAlias<Plan>> subQueryAliases =
logicalCTE.getAliasQueries();
for (LogicalSubQueryAlias<Plan> subQueryAlias : subQueryAliases) {
- Plan newSubQueryAlias = subQueryAlias.accept(new
PullUpSubqueryAliasToCTE(), context);
- if (newSubQueryAlias instanceof LogicalSubQueryAlias) {
- subQueryAlias = (LogicalSubQueryAlias<Plan>) newSubQueryAlias;
- } else {
- subQueryAlias = new
LogicalSubQueryAlias<>(subQueryAlias.getAlias(), newSubQueryAlias);
- }
+ subQueryAlias.accept(new PullUpSubqueryAliasToCTE(), context);
}
Plan cte = visitChildren(this, logicalCTE, context);
if (!aliasQueries.isEmpty()) {
@@ -90,4 +87,32 @@ public class PullUpSubqueryAliasToCTE extends
PlanPreprocessor {
}
return cte;
}
+
+ private Plan createCteForRootNode(Plan plan, StatementContext context) {
+ Plan topPlan = visitChildren(this, plan, context);
+ if (!aliasQueries.isEmpty()) {
+ if (topPlan.child(0) instanceof LogicalCTE) {
+ LogicalCTE logicalCTE = (LogicalCTE) topPlan.child(0);
+ List<LogicalSubQueryAlias<Plan>> subQueryAliases = new
ArrayList<>();
+ subQueryAliases.addAll(logicalCTE.getAliasQueries());
+ subQueryAliases.addAll(aliasQueries);
+ return topPlan.withChildren(
+ new LogicalCTE<>(subQueryAliases, (LogicalPlan)
topPlan.child(0)));
+ }
+ return topPlan.withChildren(
+ new LogicalCTE<>(aliasQueries, (LogicalPlan)
topPlan.child(0)));
+ }
+ return topPlan;
+ }
+
+ private boolean findLeadingHintIgnoreSortAndLimit(Plan plan) {
+ if (plan instanceof LogicalSelectHint
+ && ((LogicalSelectHint) plan).isIncludeLeading()) {
+ return true;
+ } else if (plan instanceof LogicalLimit || plan instanceof
LogicalSort) {
+ return findLeadingHintIgnoreSortAndLimit(plan.child(0));
+ } else {
+ return false;
+ }
+ }
}
diff --git a/regression-test/suites/nereids_p0/hint/test_hint.groovy
b/regression-test/suites/nereids_p0/hint/test_hint.groovy
index 81033e014f1..fab109afc18 100644
--- a/regression-test/suites/nereids_p0/hint/test_hint.groovy
+++ b/regression-test/suites/nereids_p0/hint/test_hint.groovy
@@ -58,4 +58,162 @@ suite("test_hint") {
// qt_select1_8 """explain shape plan /*+DBP: ROUTE={GROUP_ID(zjaq)}*/
select /*+DBP: ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
+ multi_sql '''
+ drop table if exists
table_50_undef_partitions2_keys3_properties4_distributed_by5;
+ create table table_50_undef_partitions2_keys3_properties4_distributed_by5 (
+ col_int_undef_signed2 int ,
+ col_int_undef_signed int ,
+ col_int_undef_signed3 int ,
+ col_int_undef_signed4 int ,
+ pk int
+ ) engine=olap
+ DUPLICATE KEY(col_int_undef_signed2)
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_50_undef_partitions2_keys3_properties4_distributed_by5(pk,col_int_undef_signed,col_int_undef_signed2,col_int_undef_signed3,col_int_undef_signed4)
values
(0,8,0,3,7),(1,6,227612,4,8),(2,-590975,9,-4411568,6),(3,-7241036,null,3,5),(4,1,7,null,8),(5,2509741,5,5,1),(6,2,9,null,4817793),(7,6,8,3,0),(8,null,1,4,null),(9,711269,null,-613109,null),(10,null,7,0,7),(11,null,-5534845,0,4),(12,5,2,9,6850777),(13,-5789051,8,6,2463068),(14,2,5,953451,1),(15,-6229147,-6738861,4,0)
[...]
+ drop table if exists
table_50_undef_partitions2_keys3_properties4_distributed_by52;
+ create table table_50_undef_partitions2_keys3_properties4_distributed_by52
(
+ col_int_undef_signed int ,
+ col_int_undef_signed2 int ,
+ col_int_undef_signed3 int ,
+ col_int_undef_signed4 int ,
+ pk int
+ ) engine=olap
+ DUPLICATE KEY(col_int_undef_signed, col_int_undef_signed2)
+ PARTITION BY RANGE(col_int_undef_signed) (
+ PARTITION p0 VALUES LESS THAN ('4'),
+ PARTITION p1 VALUES LESS THAN ('6'),
+ PARTITION p2 VALUES LESS THAN ('7'),
+ PARTITION p3 VALUES LESS THAN ('8'),
+ PARTITION p4 VALUES LESS THAN ('10'),
+ PARTITION p5 VALUES LESS THAN ('83647'),
+ PARTITION p100 VALUES LESS THAN ('2147483647')
+ )
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_50_undef_partitions2_keys3_properties4_distributed_by52(pk,col_int_undef_signed,col_int_undef_signed2,col_int_undef_signed3,col_int_undef_signed4)
values
(0,-7314662,null,0,4373927),(1,0,9,2,null),(2,5,2151343,-1467194,null),(3,null,null,-6124108,null),(4,5795207,4306466,4,7),(5,6,8,3,9),(6,null,8,-7232808,9),(7,9,6,9,6),(8,4637962,-1241311,2,8),(9,1,2,3,null),(10,0,-1652390,1,3),(11,0,9,6,2),(12,-8342795,0,5539034,-4960208),(13,2768087,7,-6242297,4996873),(14,1,2,1
[...]
+ drop table if exists
table_100_undef_partitions2_keys3_properties4_distributed_by52;
+ create table
table_100_undef_partitions2_keys3_properties4_distributed_by52 (
+ col_int_undef_signed int ,
+ col_int_undef_signed2 int ,
+ col_int_undef_signed3 int ,
+ col_int_undef_signed4 int ,
+ pk int
+ ) engine=olap
+ DUPLICATE KEY(col_int_undef_signed, col_int_undef_signed2)
+ PARTITION BY RANGE(col_int_undef_signed) (
+ PARTITION p0 VALUES LESS THAN ('4'),
+ PARTITION p1 VALUES LESS THAN ('6'),
+ PARTITION p2 VALUES LESS THAN ('7'),
+ PARTITION p3 VALUES LESS THAN ('8'),
+ PARTITION p4 VALUES LESS THAN ('10'),
+ PARTITION p5 VALUES LESS THAN ('83647'),
+ PARTITION p100 VALUES LESS THAN ('2147483647')
+ )
+
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_100_undef_partitions2_keys3_properties4_distributed_by52(pk,col_int_undef_signed,col_int_undef_signed2,col_int_undef_signed3,col_int_undef_signed4)
values
(0,7865838,-348902,null,8),(1,-9434,9,8,0),(2,1845860,6675073,-7931956,-66007),(3,-7523286,210291,3,4),(4,null,-1341350,-5318642,1),(5,-6634226,2179558,2,7),(6,2,7,2,3),(7,9,2,3,-7773846),(8,0,8,6,2407384),(9,0,1,7,7),(10,5,5,null,8),(11,9,null,8283010,6),(12,7359987,5145929,2,5),(13,0,5225949,0,6770846),(14,1,454
[...]
+ drop table if exists
table_200_undef_partitions2_keys3_properties4_distributed_by52;
+ create table
table_200_undef_partitions2_keys3_properties4_distributed_by52 (
+ col_int_undef_signed int ,
+ col_int_undef_signed2 int ,
+ col_int_undef_signed3 int ,
+ col_int_undef_signed4 int ,
+ pk int
+ ) engine=olap
+ DUPLICATE KEY(col_int_undef_signed, col_int_undef_signed2)
+ PARTITION BY RANGE(col_int_undef_signed) (
+ PARTITION p0 VALUES LESS THAN ('4'),
+ PARTITION p1 VALUES LESS THAN ('6'),
+ PARTITION p2 VALUES LESS THAN ('7'),
+ PARTITION p3 VALUES LESS THAN ('8'),
+ PARTITION p4 VALUES LESS THAN ('10'),
+ PARTITION p5 VALUES LESS THAN ('83647'),
+ PARTITION p100 VALUES LESS THAN ('2147483647')
+ )
+
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_200_undef_partitions2_keys3_properties4_distributed_by52(pk,col_int_undef_signed,col_int_undef_signed2,col_int_undef_signed3,col_int_undef_signed4)
values
(0,null,6178782,4,-1498997),(1,null,null,2,4),(2,8,6,6114625,6840353),(3,6,-3487226,4,-18364),(4,6647558,0,7,4),(5,5,1,3,3991803),(6,null,3,3,6),(7,-1597140,3,3,2),(8,6415967,null,9,null),(9,0,2,-1569216,8263281),(10,2546741,4,-4334118,8),(11,2375117,5,null,-3767162),(12,4,290235,null,6),(13,5569849,8,6,null),(14,
[...]
+ drop table if exists tmp_t006;
+ CREATE TABLE tmp_t006 properties ("replication_num" = "1") as select 1,
original_sql.* from ( SELECT /*+ leading( tbl4 ) */ tbl4 .
col_int_undef_signed AS col_int_undef_signed , 8 AS col_int_undef_signed2 , 4
AS col_int_undef_signed3 , 6 AS col_int_undef_signed4 FROM
table_50_undef_partitions2_keys3_properties4_distributed_by52 AS tbl1 INNER
JOIN table_50_undef_partitions2_keys3_properties4_distributed_by52 AS tbl2 ON
tbl1 . col_int_undef_signed = tbl1 . col_int_undef_signed IN [...]
+ '''
+
+ explain {
+ sql('''
+ select
+ 1,
+ original_sql.*
+ from
+ (
+ SELECT
+ /*+ leading( tbl4 ) */
+ tbl4.col_int_undef_signed AS col_int_undef_signed,
+ 8 AS col_int_undef_signed2,
+ 4 AS col_int_undef_signed3,
+ 6 AS col_int_undef_signed4
+ FROM
+
table_50_undef_partitions2_keys3_properties4_distributed_by52 AS tbl1
+ INNER JOIN
table_50_undef_partitions2_keys3_properties4_distributed_by52 AS tbl2 ON
tbl1.col_int_undef_signed = tbl1.col_int_undef_signed
+ INNER JOIN
table_50_undef_partitions2_keys3_properties4_distributed_by5 AS tbl3 ON
tbl3.col_int_undef_signed4 != tbl3.col_int_undef_signed
+ INNER JOIN (
+ SELECT
+ /*+ leading( tbl1 ) */
+ 0 AS col_int_undef_signed,
+ tbl1.col_int_undef_signed2 AS
col_int_undef_signed2,
+ tbl1.col_int_undef_signed2 AS
col_int_undef_signed3,
+ tbl1.col_int_undef_signed2 AS col_int_undef_signed4
+ FROM
+
table_50_undef_partitions2_keys3_properties4_distributed_by52 AS tbl1
+ WHERE
+ (
+ tbl1.col_int_undef_signed3 IN (
+ SELECT
+ /*+ leading( tbl2 tbl1 ) */
+ 3 AS col_int_undef_signed
+ FROM
+
table_200_undef_partitions2_keys3_properties4_distributed_by52 AS tbl1
+ INNER JOIN
table_100_undef_partitions2_keys3_properties4_distributed_by52 AS tbl2 ON
tbl2.col_int_undef_signed2 = tbl1.col_int_undef_signed3
+ WHERE
+ (
+ tbl1.col_int_undef_signed2 =
tbl2.col_int_undef_signed2
+ AND (
+ tbl1.col_int_undef_signed4 IN
(2, NULL, 6, 0, 1, NULL)
+ )
+ )
+ ORDER BY
+ 1 ASC
+ LIMIT
+ 5
+ )
+ AND (
+ tbl1.col_int_undef_signed2 IN (0 + 5, 9, 9
* 7, 8 + 5, 9 - 7, 7)
+ )
+ )
+ ORDER BY
+ 1,
+ 2,
+ 3,
+ 4 DESC
+ LIMIT
+ 6666 OFFSET 50
+ ) AS tbl4 ON tbl1.col_int_undef_signed3 =
tbl1.col_int_undef_signed
+ WHERE
+ NOT (
+ tbl1.col_int_undef_signed3 IN (NULL, 4, 1, 0, 6, 8 * 6)
+ )
+ ORDER BY
+ 1,
+ 2,
+ 3,
+ 4 ASC
+ LIMIT
+ 6666 OFFSET 500
+ ) as original_sql INTO OUTFILE "file:///tmp/tt.csv" FORMAT AS
CSV_WITH_NAMES PROPERTIES(
+ "column_separator" = ",",
+ "success_file_name" = "_SUCCESS"
+ );
+ ''')
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
