Skip to content

Commit e7a3e78

Browse files
authored
[fix](nereids)prune assert_true slots in subquery unnesting (apache#50256)
when unnest scalar subquery, we may add assert_true function with subquery output slot in projects list. The assert_true function should be pruned in final output. In order to make the plan unchanged, we add a new project node to prune the extra assert_true slot.
1 parent e0a83f7 commit e7a3e78

14 files changed

Lines changed: 109 additions & 125 deletions

File tree

‎fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -77,7 +77,6 @@
7777
import org.apache.doris.nereids.trees.expressions.functions.scalar.Ascii;
7878
import org.apache.doris.nereids.trees.expressions.functions.scalar.Asin;
7979
import org.apache.doris.nereids.trees.expressions.functions.scalar.Asinh;
80-
import org.apache.doris.nereids.trees.expressions.functions.scalar.AssertTrue;
8180
import org.apache.doris.nereids.trees.expressions.functions.scalar.Atan;
8281
import org.apache.doris.nereids.trees.expressions.functions.scalar.Atan2;
8382
import org.apache.doris.nereids.trees.expressions.functions.scalar.Atanh;
@@ -568,7 +567,6 @@ public class BuiltinScalarFunctions implements FunctionHelper {
568567
scalar(Ascii.class, "ascii"),
569568
scalar(Asin.class, "asin"),
570569
scalar(Asinh.class, "asinh"),
571-
scalar(AssertTrue.class, "assert_true"),
572570
scalar(Atan.class, "atan"),
573571
scalar(Atanh.class, "atanh"),
574572
scalar(Atan2.class, "atan2"),

‎fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java‎

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,6 @@
4747
import org.apache.doris.nereids.rules.analysis.ReplaceExpressionByChildOutput;
4848
import org.apache.doris.nereids.rules.analysis.SubqueryToApply;
4949
import org.apache.doris.nereids.rules.analysis.VariableToLiteral;
50-
import org.apache.doris.nereids.rules.rewrite.MergeProjects;
5150
import org.apache.doris.nereids.rules.rewrite.SemiJoinCommute;
5251
import org.apache.doris.nereids.rules.rewrite.SimplifyAggGroupBy;
5352
import org.apache.doris.nereids.trees.plans.logical.LogicalCTEAnchor;
@@ -154,8 +153,15 @@ private static List<RewriteJob> buildAnalyzerJobs() {
154153
),
155154
topDown(new LeadingJoin()),
156155
bottomUp(new NormalizeGenerate()),
157-
bottomUp(new SubqueryToApply()),
158-
topDown(new MergeProjects())
156+
bottomUp(new SubqueryToApply())
157+
/*
158+
* Notice, MergeProjects rule should NOT be placed after SubqueryToApply in analyze phase.
159+
* because in SubqueryToApply, we may add assert_true function with subquery output slot in projects list.
160+
* on the other hand, the assert_true function should be not be in final output.
161+
* in order to keep the plan unchanged, we add a new project node to prune the extra assert_true slot.
162+
* but MergeProjects rule will merge the two projects and keep assert_true anyway.
163+
* so we move MergeProjects from analyze to rewrite phase.
164+
*/
159165
);
160166
}
161167
}

‎fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,10 @@ public class Rewriter extends AbstractBatchJobExecutor {
173173
ImmutableSet.of(LogicalCTEAnchor.class),
174174
() -> jobs(
175175
topic("Plan Normalization",
176+
// move MergeProjects rule from analyze phase
177+
// because SubqueryToApply and BindSink rule may create extra project node
178+
// we need merge them at the beginning of rewrite phase to let later rules happy
179+
topDown(new MergeProjects()),
176180
topDown(
177181
new EliminateOrderByConstant(),
178182
new EliminateSortUnderSubqueryOrView(),

‎fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -123,13 +123,19 @@ public Expression visitScalarSubquery(ScalarSubquery scalar, T context) {
123123
boolean isCorrelated = analyzedResult.isCorrelated();
124124
LogicalPlan analyzedSubqueryPlan = analyzedResult.logicalPlan;
125125
checkOutputColumn(analyzedSubqueryPlan);
126+
// use limitOneIsEliminated to indicate if subquery has limit 1 clause
127+
// because limit 1 clause will ensure subquery output at most 1 row
128+
// we eliminate limit 1 clause and pass this info to later SubqueryToApply rule
129+
// so when creating LogicalApply node, we don't need to add AssertTrue function
130+
boolean limitOneIsEliminated = false;
126131
if (isCorrelated) {
127132
if (analyzedSubqueryPlan instanceof LogicalLimit) {
128133
LogicalLimit limit = (LogicalLimit) analyzedSubqueryPlan;
129134
if (limit.getOffset() == 0 && limit.getLimit() == 1) {
130135
// skip useless limit node
131136
analyzedResult = new AnalyzedResult((LogicalPlan) analyzedSubqueryPlan.child(0),
132137
analyzedResult.correlatedSlots);
138+
limitOneIsEliminated = true;
133139
} else {
134140
throw new AnalysisException("limit is not supported in correlated subquery "
135141
+ analyzedResult.getLogicalPlan());
@@ -169,7 +175,8 @@ public Expression visitScalarSubquery(ScalarSubquery scalar, T context) {
169175
}
170176
}
171177

172-
return new ScalarSubquery(analyzedResult.getLogicalPlan(), analyzedResult.getCorrelatedSlots());
178+
return new ScalarSubquery(analyzedResult.getLogicalPlan(), analyzedResult.getCorrelatedSlots(),
179+
limitOneIsEliminated);
173180
}
174181

175182
private void checkOutputColumn(LogicalPlan plan) {

‎fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java‎

Lines changed: 35 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,7 @@
6666
import com.google.common.collect.ImmutableList;
6767
import com.google.common.collect.ImmutableSet;
6868

69+
import java.util.ArrayList;
6970
import java.util.Collection;
7071
import java.util.HashMap;
7172
import java.util.LinkedHashMap;
@@ -400,7 +401,11 @@ private Pair<LogicalPlan, Optional<Expression>> addApply(SubqueryExpr subquery,
400401
Optional<MarkJoinSlotReference> markJoinSlot = subqueryToMarkJoinSlot.get(subquery);
401402
boolean needAddScalarSubqueryOutputToProjects = isConjunctContainsScalarSubqueryOutput(
402403
subquery, conjunct, isProject, singleSubquery);
403-
boolean needRuntimeAssertCount = false;
404+
// for scalar subquery, we need ensure it output at most 1 row
405+
// by doing that, we add an aggregate function any_value() to the project list
406+
// we use needRuntimeAnyValue to indicate if any_value() is needed
407+
// if needRuntimeAnyValue is true, we will add it to the project list
408+
boolean needRuntimeAnyValue = false;
404409
NamedExpression oldSubqueryOutput = subquery.getQueryPlan().getOutput().get(0);
405410
Slot countSlot = null;
406411
Slot anyValueSlot = null;
@@ -450,19 +455,25 @@ private Pair<LogicalPlan, Optional<Expression>> addApply(SubqueryExpr subquery,
450455
// select (select t2.c1 from t2 where t2.c2 = t1.c2) from t1;
451456
// the original output of the correlate subquery is t2.c1, after adding a scalar agg, it will be
452457
// select (select count(*), any_value(t2.c1) from t2 where t2.c2 = t1.c2) from t1;
453-
Alias countAlias = new Alias(new Count());
454458
Alias anyValueAlias = new Alias(new AnyValue(oldSubqueryOutput));
455-
LogicalAggregate<Plan> aggregate = new LogicalAggregate<>(ImmutableList.of(),
456-
ImmutableList.of(countAlias, anyValueAlias), subquery.getQueryPlan());
457-
countSlot = countAlias.toSlot();
459+
LogicalAggregate<Plan> aggregate;
460+
if (((ScalarSubquery) subquery).limitOneIsEliminated()) {
461+
aggregate = new LogicalAggregate<>(ImmutableList.of(),
462+
ImmutableList.of(anyValueAlias), subquery.getQueryPlan());
463+
} else {
464+
Alias countAlias = new Alias(new Count());
465+
countSlot = countAlias.toSlot();
466+
aggregate = new LogicalAggregate<>(ImmutableList.of(),
467+
ImmutableList.of(countAlias, anyValueAlias), subquery.getQueryPlan());
468+
}
458469
anyValueSlot = anyValueAlias.toSlot();
459470
subquery = subquery.withSubquery(aggregate);
460471
if (conjunct.isPresent()) {
461472
Map<Expression, Expression> replaceMap = new HashMap<>();
462473
replaceMap.put(oldSubqueryOutput, anyValueSlot);
463474
newConjunct = Optional.of(ExpressionUtils.replace(conjunct.get(), replaceMap));
464475
}
465-
needRuntimeAssertCount = true;
476+
needRuntimeAnyValue = true;
466477
}
467478
}
468479
LogicalApply.SubQueryType subQueryType;
@@ -493,21 +504,33 @@ private Pair<LogicalPlan, Optional<Expression>> addApply(SubqueryExpr subquery,
493504
projects.addAll(childPlan.getOutput());
494505
// markJoinSlotReference
495506
markJoinSlot.map(projects::add);
507+
LogicalProject logicalProject;
496508
if (needAddScalarSubqueryOutputToProjects) {
497-
if (needRuntimeAssertCount) {
509+
if (needRuntimeAnyValue) {
498510
// if we create a new subquery in previous step, we need add the any_value() and assert_true()
499511
// into the project list. So BE will use assert_true to check if the subquery return only 1 row
500512
projects.add(anyValueSlot);
501-
projects.add(new Alias(new AssertTrue(
502-
ExpressionUtils.or(new IsNull(countSlot),
503-
new LessThanEqual(countSlot, new IntegerLiteral(1))),
504-
new VarcharLiteral("correlate scalar subquery must return only 1 row"))));
513+
if (countSlot != null) {
514+
List<NamedExpression> upperProjects = new ArrayList<>();
515+
upperProjects.addAll(projects.build());
516+
projects.add(new Alias(new AssertTrue(
517+
ExpressionUtils.or(new IsNull(countSlot),
518+
new LessThanEqual(countSlot, new IntegerLiteral(1))),
519+
new VarcharLiteral("correlate scalar subquery must return only 1 row"))));
520+
logicalProject = new LogicalProject(projects.build(), newApply);
521+
logicalProject = new LogicalProject(upperProjects, logicalProject);
522+
} else {
523+
logicalProject = new LogicalProject(projects.build(), newApply);
524+
}
505525
} else {
506526
projects.add(oldSubqueryOutput);
527+
logicalProject = new LogicalProject(projects.build(), newApply);
507528
}
529+
} else {
530+
logicalProject = new LogicalProject(projects.build(), newApply);
508531
}
509532

510-
return Pair.of(new LogicalProject(projects.build(), newApply), newConjunct);
533+
return Pair.of(logicalProject, newConjunct);
511534
}
512535

513536
private boolean isConjunctContainsScalarSubqueryOutput(

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/ExpressionDeepCopier.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -150,6 +150,6 @@ public Expression visitScalarSubquery(ScalarSubquery scalar, DeepCopierContext c
150150
.collect(Collectors.toList());
151151
Optional<Expression> typeCoercionExpr = scalar.getTypeCoercionExpr()
152152
.map(c -> c.accept(this, context));
153-
return new ScalarSubquery(logicalPlan, correlateSlots, typeCoercionExpr);
153+
return new ScalarSubquery(logicalPlan, correlateSlots, typeCoercionExpr, scalar.limitOneIsEliminated());
154154
}
155155
}

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java‎

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -41,27 +41,37 @@
4141
*/
4242
public class ScalarSubquery extends SubqueryExpr implements LeafExpression {
4343

44+
// indicate if the subquery's root plan node is LogicalAggregate
4445
private final boolean hasTopLevelScalarAgg;
4546

47+
// indicate if the subquery has limit 1 clause but it's been eliminated in previous process step
48+
private final boolean limitOneIsEliminated;
49+
4650
public ScalarSubquery(LogicalPlan subquery) {
47-
this(subquery, ImmutableList.of());
51+
this(subquery, ImmutableList.of(), false);
4852
}
4953

50-
public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots) {
51-
this(subquery, correlateSlots, Optional.empty());
54+
public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots, boolean limitOneIsEliminated) {
55+
this(subquery, correlateSlots, Optional.empty(), limitOneIsEliminated);
5256
}
5357

54-
public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots, Optional<Expression> typeCoercionExpr) {
58+
public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots, Optional<Expression> typeCoercionExpr,
59+
boolean limitOneIsEliminated) {
5560
super(Objects.requireNonNull(subquery, "subquery can not be null"),
5661
Objects.requireNonNull(correlateSlots, "correlateSlots can not be null"),
5762
typeCoercionExpr);
5863
hasTopLevelScalarAgg = findTopLevelScalarAgg(subquery, ImmutableSet.copyOf(correlateSlots)) != null;
64+
this.limitOneIsEliminated = limitOneIsEliminated;
5965
}
6066

6167
public boolean hasTopLevelScalarAgg() {
6268
return hasTopLevelScalarAgg;
6369
}
6470

71+
public boolean limitOneIsEliminated() {
72+
return limitOneIsEliminated;
73+
}
74+
6575
/**
6676
* getTopLevelScalarAggFunction
6777
*/
@@ -103,12 +113,12 @@ public Expression withTypeCoercion(DataType dataType) {
103113
return new ScalarSubquery(queryPlan, correlateSlots,
104114
dataType == queryPlan.getOutput().get(0).getDataType()
105115
? Optional.of(queryPlan.getOutput().get(0))
106-
: Optional.of(new Cast(queryPlan.getOutput().get(0), dataType)));
116+
: Optional.of(new Cast(queryPlan.getOutput().get(0), dataType)), limitOneIsEliminated);
107117
}
108118

109119
@Override
110120
public ScalarSubquery withSubquery(LogicalPlan subquery) {
111-
return new ScalarSubquery(subquery, correlateSlots, typeCoercionExpr);
121+
return new ScalarSubquery(subquery, correlateSlots, typeCoercionExpr, limitOneIsEliminated);
112122
}
113123

114124
/**

‎fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeWhereSubqueryTest.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -379,6 +379,7 @@ public void testSql10AfterAnalyze() {
379379
// select * from t6 where t6.k1 < (select max(aa) from (select v1 as aa from t7 where t6.k2=t7.v2) t2 )
380380
PlanChecker.from(connectContext)
381381
.analyze(sql10)
382+
.applyTopDown(new MergeProjects())
382383
.matchesFromRoot(
383384
logicalResultSink(
384385
logicalProject(

‎fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlotsTest.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
import org.apache.doris.nereids.exceptions.AnalysisException;
2323
import org.apache.doris.nereids.properties.OrderKey;
2424
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
25+
import org.apache.doris.nereids.rules.rewrite.MergeProjects;
2526
import org.apache.doris.nereids.trees.expressions.Add;
2627
import org.apache.doris.nereids.trees.expressions.Alias;
2728
import org.apache.doris.nereids.trees.expressions.Cast;
@@ -138,6 +139,7 @@ public void testHavingGroupBySlot() {
138139
);
139140
Alias sumA2 = new Alias(new ExprId(3), new Sum(a2), "sum(a2)");
140141
PlanChecker.from(connectContext).analyze(sql)
142+
.applyTopDown(new MergeProjects())
141143
.applyBottomUp(new ExpressionRewrite(ExpressionAnalyzer.FUNCTION_ANALYZER_RULE))
142144
.matches(
143145
logicalProject(
@@ -162,6 +164,7 @@ public void testHavingAggregateFunction() {
162164
);
163165
Alias sumA2 = new Alias(new ExprId(3), new Sum(a2), "sum(a2)");
164166
PlanChecker.from(connectContext).analyze(sql)
167+
.applyTopDown(new MergeProjects())
165168
.matches(
166169
logicalProject(
167170
logicalFilter(
@@ -230,6 +233,7 @@ public void testHavingAggregateFunction() {
230233
);
231234
Alias minPK = new Alias(new ExprId(4), new Min(pk), "min(pk)");
232235
PlanChecker.from(connectContext).analyze(sql)
236+
.applyTopDown(new MergeProjects())
233237
.matches(
234238
logicalProject(
235239
logicalFilter(
@@ -254,6 +258,7 @@ public void testHavingAggregateFunction() {
254258
Alias sumA1A23 = new Alias(new ExprId(4), new Sum(new Add(new Add(a1, a2), new TinyIntLiteral((byte) 3))),
255259
"sum(((a1 + a2) + 3))");
256260
PlanChecker.from(connectContext).analyze(sql)
261+
.applyTopDown(new MergeProjects())
257262
.matches(
258263
logicalProject(
259264
logicalFilter(
@@ -266,6 +271,7 @@ public void testHavingAggregateFunction() {
266271
sql = "SELECT a1 FROM t1 GROUP BY a1 HAVING count(*) > 0";
267272
Alias countStar = new Alias(new ExprId(3), new Count(), "count(*)");
268273
PlanChecker.from(connectContext).analyze(sql)
274+
.applyTopDown(new MergeProjects())
269275
.matches(
270276
logicalProject(
271277
logicalFilter(
@@ -294,6 +300,7 @@ void testJoinWithHaving() {
294300
Alias sumA2 = new Alias(new ExprId(6), new Sum(a2), "sum(a2)");
295301
Alias sumB1 = new Alias(new ExprId(7), new Sum(b1), "sum(b1)");
296302
PlanChecker.from(connectContext).analyze(sql)
303+
.applyTopDown(new MergeProjects())
297304
.matches(
298305
logicalProject(
299306
logicalFilter(

‎fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OperativeColumnDeriveTest.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ public void testProject() {
5858
public void testUnionAll() {
5959
PlanChecker.from(connectContext)
6060
.analyze("select a, b from (select sid as a, cid as b from score union all select id as a, age as b from student) T")
61+
.applyTopDown(new MergeProjects())
6162
.customRewrite(new OperativeColumnDerive())
6263
.matches(logicalUnion(
6364
logicalProject(
@@ -73,6 +74,7 @@ public void testUnionAll() {
7374
public void testUnionAllBackPropagate() {
7475
PlanChecker.from(connectContext)
7576
.analyze("select a, b from (select sid as a, cid as b from score where cid > 0 union all select id as a, age as b from student) T")
77+
.applyTopDown(new MergeProjects())
7678
.customRewrite(new OperativeColumnDerive())
7779
.matches(logicalUnion(
7880
logicalProject(

0 commit comments

Comments
 (0)