You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by hu...@apache.org on 2022/09/02 09:34:27 UTC
[doris] branch master updated: [feature](nereids) Convert subqueries into algebraic expressions and … (#11454)
This is an automated email from the ASF dual-hosted git repository.
huajianlan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7f7a3a7524 [feature](nereids) Convert subqueries into algebraic expressions and … (#11454)
7f7a3a7524 is described below
commit 7f7a3a75242d00f41e7243e5f4105b27d954a29e
Author: zhengshiJ <32...@users.noreply.github.com>
AuthorDate: Fri Sep 2 17:34:19 2022 +0800
[feature](nereids) Convert subqueries into algebraic expressions and … (#11454)
1.Convert subqueries to Apply nodes.
2.Convert ApplyNode to ordinary join.
### Detailed design:
There are three types of current subexpressions, scalarSubquery, inSubquery, and Exists. The scalarSubquery refers to the returned data as 1 row and 1 column.
**Subquery replacement**
```
before:
scalarSubquery: filter(t1.a = scalarSubquery(output b));
inSubquery: filter(inSubquery); inSubquery = (t1.a in select ***);
exists: filter(exists); exists = (select ***);
end:
scalarSubquery: filter(t1.a = b);
inSubquery: filter(True);
exists: filter(True);
```
**Subquery Transformation Rules**
```
PushApplyUnderFilter
* before:
* Apply
* / \
* Input(output:b) Filter(Correlated predicate/UnCorrelated predicate)
*
* after:
* Filter(Correlated predicate)
* |
* Apply
* / \
* Input(output:b) Filter(UnCorrelated predicate)
```
```
PushApplyUnderProject
* before:
* Apply
* / \
* Input(output:b) Project(output:a)
*
* after:
* Project(b,(if the Subquery is Scalar add 'a' as the output column))
* / \
* Input(output:b) Apply
```
```
ApplyPullFilterOnAgg
* before:
* Apply
* / \
* Input(output:b) agg(output:fn,c; group by:null)
* |
* Filter(Correlated predicate(Input.e = this.f)/UnCorrelated predicate)
*
* end:
* Apply(Correlated predicate(Input.e = this.f))
* / \
* Input(output:b) agg(output:fn,this.f; group by:this.f)
* |
* Filter(UnCorrelated predicate)
```
```
ApplyPullFilterOnProjectUnderAgg
* before:
* apply
* / \
* Input(output:b) agg
* |
* Project(output:a)
* |
* Filter(correlated predicate(Input.e = this.f)/Unapply predicate)
* |
* child
* apply
* / \
* Input(output:b) agg
* |
* Filter(correlated predicate(Input.e = this.f)/Unapply predicate)
* |
* Project(output:a,this.f, Unapply predicate(slots))
* |
* child
```
```
ScalarToJoin
* UnCorrelated -> CROSS_JOIN
* Correlated -> LEFT_OUTER_JOIN
```
```
InToJoin
* Not In -> LEFT_ANTI_JOIN
* In -> LEFT_SEMI_JOIN
```
```
existsToJoin
* Exists
* Correlated -> LEFT_SEMI_JOIN
* correlated LEFT_SEMI_JOIN(Correlated Predicate)
* / \ --> / \
* input queryPlan input queryPlan
*
* UnCorrelated -> CROSS_JOIN(limit(1))
* uncorrelated CROSS_JOIN
* / \ --> / \
* input queryPlan input limit(1)
* |
* queryPlan
*
* Not Exists
* Correlated -> LEFT_ANTI_JOIN
* correlated LEFT_ANTI_JOIN(Correlated Predicate)
* / \ --> / \
* input queryPlan input queryPlan
*
* UnCorrelated -> CROSS_JOIN(Count(*))
* Filter(count(*) = 0)
* |
* apply Cross_Join
* / \ --> / \
* input queryPlan input agg(output:count(*))
* |
* limit(1)
* |
* queryPlan
```
---
.../org/apache/doris/nereids/CascadesContext.java | 18 +
.../doris/nereids/analyzer/NereidsAnalyzer.java | 2 +
.../glue/translator/ExpressionTranslator.java | 35 +-
.../glue/translator/PhysicalPlanTranslator.java | 36 +-
.../AdjustApplyFromCorrelatToUnCorrelatJob.java | 50 +++
.../jobs/batch/AnalyzeSubqueryRulesJob.java | 34 ++
.../nereids/jobs/batch/ConvertApplyToJoinJob.java | 43 ++
.../doris/nereids/jobs/batch/RewriteJob.java | 8 +
.../doris/nereids/parser/LogicalPlanBuilder.java | 5 +-
.../org/apache/doris/nereids/rules/RuleSet.java | 2 +
.../org/apache/doris/nereids/rules/RuleType.java | 12 +
.../nereids/rules/analysis/AnalyzeSubquery.java | 132 ++++++
.../nereids/rules/analysis/BindSlotReference.java | 186 ++++++++-
.../apache/doris/nereids/rules/analysis/Scope.java | 41 +-
...ogicalAssertNumRowsToPhysicalAssertNumRows.java | 36 ++
.../rewrite/logical/ApplyPullFilterOnAgg.java | 91 +++++
.../logical/ApplyPullFilterOnProjectUnderAgg.java | 84 ++++
.../rules/rewrite/logical/ExistsApplyToJoin.java | 120 ++++++
.../rules/rewrite/logical/InApplyToJoin.java | 68 ++++
.../rewrite/logical/PushApplyUnderFilter.java | 79 ++++
.../rewrite/logical/PushApplyUnderProject.java | 64 +++
.../rules/rewrite/logical/ScalarApplyToJoin.java | 74 ++++
.../apache/doris/nereids/stats/JoinEstimation.java | 12 +-
.../doris/nereids/stats/StatsCalculator.java | 13 +
.../doris/nereids/trees/expressions/Alias.java | 2 +-
.../trees/expressions/AssertNumRowsElement.java | 109 +++++
.../doris/nereids/trees/expressions/Exists.java | 41 +-
.../nereids/trees/expressions/InSubquery.java | 35 +-
.../doris/nereids/trees/expressions/ListQuery.java | 11 +-
.../nereids/trees/expressions/ScalarSubquery.java | 6 +
.../nereids/trees/expressions/SlotReference.java | 11 +-
.../nereids/trees/expressions/SubqueryExpr.java | 39 +-
.../visitor/DefaultSubExprRewriter.java | 76 ----
.../expressions/visitor/ExpressionVisitor.java | 5 +
.../apache/doris/nereids/trees/plans/PlanType.java | 6 +-
.../nereids/trees/plans/logical/LogicalApply.java | 101 +++--
...rceSingleRow.java => LogicalAssertNumRows.java} | 69 ++--
.../trees/plans/logical/LogicalCorrelatedJoin.java | 136 -------
.../PhysicalAssertNumRows.java} | 60 +--
.../nereids/trees/plans/visitor/PlanVisitor.java | 16 +-
.../java/org/apache/doris/nereids/util/Utils.java | 59 +++
.../nereids/trees/expressions/SubqueryTest.java | 24 +-
.../nereids/util/AnalyzeWhereSubqueryTest.java | 451 +++++++++++++++++++++
.../nereids_syntax_p0/sub_query_correlated.groovy | 243 +++++++++++
44 files changed, 2372 insertions(+), 373 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/CascadesContext.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/CascadesContext.java
index a6ae32b5e4..88f3601361 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/CascadesContext.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/CascadesContext.java
@@ -32,12 +32,15 @@ import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleFactory;
import org.apache.doris.nereids.rules.RuleSet;
import org.apache.doris.nereids.rules.analysis.Scope;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.qe.ConnectContext;
import com.google.common.collect.ImmutableList;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import java.util.Optional;
/**
@@ -50,6 +53,8 @@ public class CascadesContext {
private JobPool jobPool;
private final JobScheduler jobScheduler;
private JobContext currentJobContext;
+ // subqueryExprIsAnalyzed: whether the subquery has been analyzed.
+ private Map<SubqueryExpr, Boolean> subqueryExprIsAnalyzed;
/**
* Constructor of OptimizerContext.
@@ -64,6 +69,7 @@ public class CascadesContext {
this.jobPool = new JobStack();
this.jobScheduler = new SimpleJobScheduler();
this.currentJobContext = new JobContext(this, PhysicalProperties.ANY, Double.MAX_VALUE);
+ this.subqueryExprIsAnalyzed = new HashMap<>();
}
public static CascadesContext newContext(StatementContext statementContext, Plan initPlan) {
@@ -127,6 +133,18 @@ public class CascadesContext {
return this;
}
+ public void setSubqueryExprIsAnalyzed(SubqueryExpr subqueryExpr, boolean isAnalyzed) {
+ subqueryExprIsAnalyzed.put(subqueryExpr, isAnalyzed);
+ }
+
+ public boolean subqueryIsAnalyzed(SubqueryExpr subqueryExpr) {
+ if (subqueryExprIsAnalyzed.get(subqueryExpr) == null) {
+ setSubqueryExprIsAnalyzed(subqueryExpr, false);
+ return false;
+ }
+ return subqueryExprIsAnalyzed.get(subqueryExpr);
+ }
+
public CascadesContext bottomUpRewrite(RuleFactory... rules) {
return execute(new RewriteBottomUpJob(memo.getRoot(), currentJobContext, ImmutableList.copyOf(rules)));
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/NereidsAnalyzer.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/NereidsAnalyzer.java
index 3b461e16a0..596bf9cd0e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/NereidsAnalyzer.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/NereidsAnalyzer.java
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.analyzer;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.jobs.batch.AnalyzeRulesJob;
+import org.apache.doris.nereids.jobs.batch.AnalyzeSubqueryRulesJob;
import org.apache.doris.nereids.jobs.batch.CheckAnalysisJob;
import org.apache.doris.nereids.jobs.batch.FinalizeAnalyzeJob;
import org.apache.doris.nereids.rules.analysis.Scope;
@@ -45,6 +46,7 @@ public class NereidsAnalyzer {
public void analyze() {
new AnalyzeRulesJob(cascadesContext, outerScope).execute();
+ new AnalyzeSubqueryRulesJob(cascadesContext).execute();
new FinalizeAnalyzeJob(cascadesContext).execute();
// check whether analyze result is meaningful
new CheckAnalysisJob(cascadesContext).execute();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
index 18b5bea6ef..ebfb969758 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
@@ -18,8 +18,10 @@
package org.apache.doris.nereids.glue.translator;
import org.apache.doris.analysis.ArithmeticExpr;
+import org.apache.doris.analysis.AssertNumRowsElement.Assertion;
import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.BinaryPredicate.Operator;
+import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.CaseExpr;
import org.apache.doris.analysis.CaseWhenClause;
import org.apache.doris.analysis.CastExpr;
@@ -32,15 +34,18 @@ import org.apache.doris.analysis.TimestampArithmeticExpr;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.And;
+import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
import org.apache.doris.nereids.trees.expressions.Between;
import org.apache.doris.nereids.trees.expressions.BinaryArithmetic;
import org.apache.doris.nereids.trees.expressions.CaseWhen;
import org.apache.doris.nereids.trees.expressions.Cast;
import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.GreaterThan;
import org.apache.doris.nereids.trees.expressions.GreaterThanEqual;
import org.apache.doris.nereids.trees.expressions.InPredicate;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
import org.apache.doris.nereids.trees.expressions.LessThan;
import org.apache.doris.nereids.trees.expressions.LessThanEqual;
import org.apache.doris.nereids.trees.expressions.Like;
@@ -148,10 +153,12 @@ public class ExpressionTranslator extends DefaultExpressionVisitor<Expr, PlanTra
true);
} else if (not.child() instanceof EqualTo) {
EqualTo equalTo = (EqualTo) not.child();
- BinaryPredicate binaryPredicate = new BinaryPredicate(Operator.NE,
+ BinaryPredicate binaryPredicate = new BinaryPredicate(Operator.NE,
equalTo.child(0).accept(this, context),
equalTo.child(1).accept(this, context));
return binaryPredicate;
+ } else if (not.child() instanceof InSubquery || not.child() instanceof Exists) {
+ return new BoolLiteral(true);
} else {
return new CompoundPredicate(CompoundPredicate.Operator.NOT,
not.child(0).accept(this, context),
@@ -276,4 +283,30 @@ public class ExpressionTranslator extends DefaultExpressionVisitor<Expr, PlanTra
arithmetic.getDataType().toCatalogDataType());
}
}
+
+ public static org.apache.doris.analysis.AssertNumRowsElement translateAssert(
+ AssertNumRowsElement assertNumRowsElement) {
+ return new org.apache.doris.analysis.AssertNumRowsElement(assertNumRowsElement.getDesiredNumOfRows(),
+ assertNumRowsElement.getSubqueryString(), translateAssertion(assertNumRowsElement.getAssertion()));
+ }
+
+ private static org.apache.doris.analysis.AssertNumRowsElement.Assertion translateAssertion(
+ AssertNumRowsElement.Assertion assertion) {
+ switch (assertion) {
+ case EQ:
+ return Assertion.EQ;
+ case NE:
+ return Assertion.NE;
+ case LT:
+ return Assertion.LT;
+ case LE:
+ return Assertion.LE;
+ case GT:
+ return Assertion.GT;
+ case GE:
+ return Assertion.GE;
+ default:
+ throw new AnalysisException("UnSupported type: " + assertion);
+ }
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
index 890b2e2cbf..9f2270eb35 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
@@ -45,6 +45,7 @@ import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
import org.apache.doris.nereids.trees.plans.physical.AbstractPhysicalSort;
import org.apache.doris.nereids.trees.plans.physical.PhysicalAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.physical.PhysicalDistribution;
import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter;
import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
@@ -58,9 +59,9 @@ import org.apache.doris.nereids.trees.plans.physical.PhysicalTopN;
import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanVisitor;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.JoinUtils;
-import org.apache.doris.nereids.util.SlotExtractor;
import org.apache.doris.nereids.util.Utils;
import org.apache.doris.planner.AggregationNode;
+import org.apache.doris.planner.AssertNumRowsNode;
import org.apache.doris.planner.CrossJoinNode;
import org.apache.doris.planner.DataPartition;
import org.apache.doris.planner.ExchangeNode;
@@ -96,24 +97,6 @@ import java.util.stream.Stream;
* </STRONG>
*/
public class PhysicalPlanTranslator extends DefaultPlanVisitor<PlanFragment, PlanTranslatorContext> {
- /**
- * The left and right child of origin predicates need to be swap sometimes.
- * Case A:
- * select * from t1 join t2 on t2.id=t1.id
- * The left plan node is t1 and the right plan node is t2.
- * The left child of origin predicate is t2.id and the right child of origin predicate is t1.id.
- * In this situation, the children of predicate need to be swap => t1.id=t2.id.
- */
- private static Expression swapEqualToForChildrenOrder(EqualTo equalTo, List<Slot> leftOutput) {
- Set<ExprId> leftSlots = SlotExtractor.extractSlot(equalTo.left()).stream()
- .map(NamedExpression::getExprId).collect(Collectors.toSet());
- if (leftOutput.stream().map(NamedExpression::getExprId).collect(Collectors.toSet()).containsAll(leftSlots)) {
- return equalTo;
- } else {
- return new EqualTo(equalTo.right(), equalTo.left());
- }
- }
-
/**
* Translate Nereids Physical Plan tree to Stale Planner PlanFragment tree.
*
@@ -367,7 +350,7 @@ public class PhysicalPlanTranslator extends DefaultPlanVisitor<PlanFragment, Pla
List<Expr> execEqConjuncts = hashJoin.getHashJoinConjuncts().stream()
.map(EqualTo.class::cast)
- .map(e -> swapEqualToForChildrenOrder(e, hashJoin.left().getOutput()))
+ .map(e -> JoinUtils.swapEqualToForChildrenOrder(e, hashJoin.left().getOutput()))
.map(e -> ExpressionTranslator.translate(e, context))
.collect(Collectors.toList());
@@ -543,6 +526,19 @@ public class PhysicalPlanTranslator extends DefaultPlanVisitor<PlanFragment, Pla
return distribution.child().accept(this, context);
}
+ @Override
+ public PlanFragment visitPhysicalAssertNumRows(PhysicalAssertNumRows<Plan> assertNumRows,
+ PlanTranslatorContext context) {
+ PlanFragment inputFragment = assertNumRows.child(0).accept(this, context);
+ //create assertNode
+ PlanNode child = inputFragment.getPlanRoot();
+ AssertNumRowsNode assertNumRowsNode = new AssertNumRowsNode(context.nextPlanNodeId(),
+ child, ExpressionTranslator.translateAssert(assertNumRows.getAssertNumRowsElement()));
+ PlanFragment mergeFragment = createParentFragment(inputFragment, DataPartition.UNPARTITIONED, context);
+ mergeFragment.addPlanRoot(assertNumRowsNode);
+ return mergeFragment;
+ }
+
private void extractExecSlot(Expr root, Set<Integer> slotRefList) {
if (root instanceof SlotRef) {
slotRefList.add(((SlotRef) root).getDesc().getId().asInt());
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AdjustApplyFromCorrelatToUnCorrelatJob.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AdjustApplyFromCorrelatToUnCorrelatJob.java
new file mode 100644
index 0000000000..c61bb007cf
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AdjustApplyFromCorrelatToUnCorrelatJob.java
@@ -0,0 +1,50 @@
+// 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.
+
+package org.apache.doris.nereids.jobs.batch;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.rewrite.logical.ApplyPullFilterOnAgg;
+import org.apache.doris.nereids.rules.rewrite.logical.ApplyPullFilterOnProjectUnderAgg;
+import org.apache.doris.nereids.rules.rewrite.logical.PushApplyUnderFilter;
+import org.apache.doris.nereids.rules.rewrite.logical.PushApplyUnderProject;
+
+import com.google.common.collect.ImmutableList;
+
+/**
+ * Adjust the plan in logicalApply so that there are no correlated columns in the subquery.
+ * Adjust the positions of apply and sub query nodes and apply,
+ * and adjust the correlated columns to apply to prepare for de nesting.
+ *
+ * For the project and filter on AGG, try to adjust them to apply.
+ * For the project and filter under AGG, bring the filter under AGG and merge it with agg.
+ */
+public class AdjustApplyFromCorrelatToUnCorrelatJob extends BatchRulesJob {
+ /**
+ * Constructor.
+ */
+ public AdjustApplyFromCorrelatToUnCorrelatJob(CascadesContext cascadesContext) {
+ super(cascadesContext);
+ rulesJob.addAll(ImmutableList.of(
+ topDownBatch(ImmutableList.of(
+ new PushApplyUnderProject(),
+ new PushApplyUnderFilter(),
+ new ApplyPullFilterOnAgg(),
+ new ApplyPullFilterOnProjectUnderAgg()
+ ))));
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AnalyzeSubqueryRulesJob.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AnalyzeSubqueryRulesJob.java
new file mode 100644
index 0000000000..1e41652e2b
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/AnalyzeSubqueryRulesJob.java
@@ -0,0 +1,34 @@
+// 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.
+
+package org.apache.doris.nereids.jobs.batch;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.analysis.AnalyzeSubquery;
+
+import com.google.common.collect.ImmutableList;
+
+/**
+ * Analyze subquery.
+ */
+public class AnalyzeSubqueryRulesJob extends BatchRulesJob {
+ public AnalyzeSubqueryRulesJob(CascadesContext cascadesContext) {
+ super(cascadesContext);
+ rulesJob.addAll(ImmutableList.of(
+ bottomUpBatch(ImmutableList.of(new AnalyzeSubquery()))));
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/ConvertApplyToJoinJob.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/ConvertApplyToJoinJob.java
new file mode 100644
index 0000000000..d086c4fb82
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/ConvertApplyToJoinJob.java
@@ -0,0 +1,43 @@
+// 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.
+
+package org.apache.doris.nereids.jobs.batch;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.rewrite.logical.ExistsApplyToJoin;
+import org.apache.doris.nereids.rules.rewrite.logical.InApplyToJoin;
+import org.apache.doris.nereids.rules.rewrite.logical.ScalarApplyToJoin;
+
+import com.google.common.collect.ImmutableList;
+
+/**
+ * Convert logicalApply without a correlated to a logicalJoin.
+ */
+public class ConvertApplyToJoinJob extends BatchRulesJob {
+ /**
+ * Constructor.
+ */
+ public ConvertApplyToJoinJob(CascadesContext cascadesContext) {
+ super(cascadesContext);
+ rulesJob.addAll(ImmutableList.of(
+ topDownBatch(ImmutableList.of(
+ new ScalarApplyToJoin(),
+ new InApplyToJoin(),
+ new ExistsApplyToJoin())
+ )));
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/RewriteJob.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/RewriteJob.java
index 7563ffddf7..ed145d1179 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/RewriteJob.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/RewriteJob.java
@@ -47,6 +47,14 @@ public class RewriteJob extends BatchRulesJob {
public RewriteJob(CascadesContext cascadesContext) {
super(cascadesContext);
ImmutableList<Job> jobs = new ImmutableList.Builder<Job>()
+ /*
+ * Subquery unnesting.
+ * 1. Adjust the plan in correlated logicalApply
+ * so that there are no correlated columns in the subquery.
+ * 2. Convert logicalApply to a logicalJoin.
+ */
+ .addAll(new AdjustApplyFromCorrelatToUnCorrelatJob(cascadesContext).rulesJob)
+ .addAll(new ConvertApplyToJoinJob(cascadesContext).rulesJob)
.add(topDownBatch(ImmutableList.of(new ExpressionNormalization())))
.add(topDownBatch(ImmutableList.of(new NormalizeAggregate())))
.add(topDownBatch(ImmutableList.of(new ReorderJoin())))
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index 36c894b873..92566ea317 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -912,7 +912,8 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
} else {
outExpression = new InSubquery(
valueExpression,
- new ListQuery(typedVisit(ctx.query()))
+ new ListQuery(typedVisit(ctx.query())),
+ ctx.NOT() != null
);
}
break;
@@ -943,7 +944,7 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
@Override
public Expression visitExist(ExistContext context) {
- return ParserUtils.withOrigin(context, () -> new Exists(typedVisit(context.query())));
+ return ParserUtils.withOrigin(context, () -> new Exists(typedVisit(context.query()), false));
}
public List<Expression> withInList(PredicateContext ctx) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
index d744c12064..fb4ec44c40 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
@@ -20,6 +20,7 @@ package org.apache.doris.nereids.rules;
import org.apache.doris.nereids.rules.exploration.join.JoinCommute;
import org.apache.doris.nereids.rules.exploration.join.JoinCommuteProject;
import org.apache.doris.nereids.rules.implementation.LogicalAggToPhysicalHashAgg;
+import org.apache.doris.nereids.rules.implementation.LogicalAssertNumRowsToPhysicalAssertNumRows;
import org.apache.doris.nereids.rules.implementation.LogicalFilterToPhysicalFilter;
import org.apache.doris.nereids.rules.implementation.LogicalJoinToHashJoin;
import org.apache.doris.nereids.rules.implementation.LogicalJoinToNestedLoopJoin;
@@ -58,6 +59,7 @@ public class RuleSet {
.add(new LogicalLimitToPhysicalLimit())
.add(new LogicalSortToPhysicalQuickSort())
.add(new LogicalTopNToPhysicalTopN())
+ .add(new LogicalAssertNumRowsToPhysicalAssertNumRows())
.build();
public List<Rule> getExplorationRules() {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
index b0be522fb8..d545013506 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
@@ -61,6 +61,17 @@ public enum RuleType {
JOIN_LEFT_CHILD_ELIMINATE_ALIAS_NODE(RuleTypeClass.REWRITE),
JOIN_RIGHT_CHILD_ELIMINATE_ALIAS_NODE(RuleTypeClass.REWRITE),
AGGREGATE_ELIMINATE_ALIAS_NODE(RuleTypeClass.REWRITE),
+
+ //subquery analyze
+ ANALYZE_FILTER_SUBQUERY(RuleTypeClass.REWRITE),
+ //subquery rewrite rule
+ PUSH_APPLY_UNDER_PROJECT(RuleTypeClass.REWRITE),
+ PUSH_APPLY_UNDER_FILTER(RuleTypeClass.REWRITE),
+ APPLY_PULL_FILTER_ON_AGG(RuleTypeClass.REWRITE),
+ APPLY_PULL_FILTER_ON_PROJECT_UNDER_AGG(RuleTypeClass.REWRITE),
+ SCALAR_APPLY_TO_JOIN(RuleTypeClass.REWRITE),
+ IN_APPLY_TO_JOIN(RuleTypeClass.REWRITE),
+ EXISTS_APPLY_TO_JOIN(RuleTypeClass.REWRITE),
// predicate push down rules
PUSH_DOWN_PREDICATE_THROUGH_JOIN(RuleTypeClass.REWRITE),
PUSH_DOWN_PREDICATE_THROUGH_AGGREGATION(RuleTypeClass.REWRITE),
@@ -100,6 +111,7 @@ public enum RuleType {
LOGICAL_TOP_N_TO_PHYSICAL_TOP_N_RULE(RuleTypeClass.IMPLEMENTATION),
LOGICAL_LIMIT_TO_PHYSICAL_LIMIT_RULE(RuleTypeClass.IMPLEMENTATION),
LOGICAL_OLAP_SCAN_TO_PHYSICAL_OLAP_SCAN_RULE(RuleTypeClass.IMPLEMENTATION),
+ LOGICAL_ASSERT_NUM_ROWS_TO_PHYSICAL_ASSERT_NUM_ROWS(RuleTypeClass.IMPLEMENTATION),
IMPLEMENTATION_SENTINEL(RuleTypeClass.IMPLEMENTATION),
// sentinel, use to count rules
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubquery.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubquery.java
new file mode 100644
index 0000000000..461b83298d
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubquery.java
@@ -0,0 +1,132 @@
+// 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.
+
+package org.apache.doris.nereids.rules.analysis;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Exists;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
+import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
+import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Optional;
+
+/**
+ * AnalyzeSubquery. translate from subquery to LogicalApply.
+ * In two steps
+ * The first step is to replace the predicate corresponding to the filter where the subquery is located.
+ * The second step converts the subquery into an apply node.
+ */
+public class AnalyzeSubquery implements AnalysisRuleFactory {
+ @Override
+ public List<Rule> buildRules() {
+ return ImmutableList.of(
+ RuleType.ANALYZE_FILTER_SUBQUERY.build(
+ logicalFilter().thenApply(ctx -> {
+ LogicalFilter filter = ctx.root;
+ List<SubqueryExpr> subqueryExprs = filter.getPredicates()
+ .collect(SubqueryExpr.class::isInstance);
+ if (subqueryExprs.isEmpty()) {
+ return filter;
+ }
+
+ // first step: Replace the subquery of predicate in LogicalFilter
+ // second step: Replace subquery with LogicalApply
+ return new LogicalFilter<>(new ReplaceSubquery().replace(filter.getPredicates()),
+ analyzedSubquery(
+ subqueryExprs, (LogicalPlan) filter.child(), ctx.cascadesContext
+ ));
+ })
+ )
+ );
+ }
+
+ private LogicalPlan analyzedSubquery(List<SubqueryExpr> subqueryExprs,
+ LogicalPlan childPlan, CascadesContext ctx) {
+ LogicalPlan tmpPlan = childPlan;
+ for (SubqueryExpr subqueryExpr : subqueryExprs) {
+ if (!ctx.subqueryIsAnalyzed(subqueryExpr)) {
+ tmpPlan = addApply(subqueryExpr, tmpPlan, ctx);
+ }
+ }
+ return tmpPlan;
+ }
+
+ private LogicalPlan addApply(SubqueryExpr subquery,
+ LogicalPlan childPlan, CascadesContext ctx) {
+ ctx.setSubqueryExprIsAnalyzed(subquery, true);
+ LogicalApply newApply = new LogicalApply(
+ subquery.getCorrelateSlots(),
+ subquery, Optional.empty(), childPlan, subquery.getQueryPlan());
+ List<Slot> projects = new ArrayList<>(childPlan.getOutput());
+ if (subquery instanceof ScalarSubquery) {
+ projects.add(subquery.getQueryPlan().getOutput().get(0));
+ }
+ return new LogicalProject(projects, newApply);
+ }
+
+ /**
+ * The Subquery in the LogicalFilter will change to LogicalApply, so we must replace the origin Subquery.
+ * LogicalFilter(predicate(contain subquery)) -> LogicalFilter(predicate(not contain subquery)
+ * Replace the subquery in logical with the relevant expression.
+ *
+ * The replacement rules are as follows:
+ * before:
+ * 1.filter(t1.a = scalarSubquery(output b));
+ * 2.filter(inSubquery); inSubquery = (t1.a in select ***);
+ * 3.filter(exists); exists = (select ***);
+ *
+ * after:
+ * 1.filter(t1.a = b);
+ * 2.filter(True);
+ * 3.filter(True);
+ */
+ private static class ReplaceSubquery extends DefaultExpressionRewriter {
+ public Expression replace(Expression expression) {
+ return (Expression) expression.accept(this, null);
+ }
+
+ @Override
+ public Object visitExistsSubquery(Exists exists, Object context) {
+ return BooleanLiteral.TRUE;
+ }
+
+ @Override
+ public Object visitInSubquery(InSubquery in, Object context) {
+ return BooleanLiteral.TRUE;
+ }
+
+ @Override
+ public Object visitScalarSubquery(ScalarSubquery scalar, Object context) {
+ return scalar.getQueryPlan().getOutput().get(0);
+ }
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
index 7ce820a483..fcb2bd2eda 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
@@ -22,14 +22,21 @@ import org.apache.doris.nereids.analyzer.UnboundAlias;
import org.apache.doris.nereids.analyzer.UnboundSlot;
import org.apache.doris.nereids.analyzer.UnboundStar;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.memo.Memo;
import org.apache.doris.nereids.properties.OrderKey;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
+import org.apache.doris.nereids.trees.expressions.ListQuery;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.Not;
+import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
import org.apache.doris.nereids.trees.expressions.Slot;
-import org.apache.doris.nereids.trees.expressions.visitor.DefaultSubExprRewriter;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
+import org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
import org.apache.doris.nereids.trees.plans.GroupPlan;
import org.apache.doris.nereids.trees.plans.LeafPlan;
import org.apache.doris.nereids.trees.plans.Plan;
@@ -39,11 +46,13 @@ import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
+import org.apache.doris.planner.PlannerContext;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import org.apache.commons.lang.StringUtils;
+import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
@@ -66,7 +75,7 @@ public class BindSlotReference implements AnalysisRuleFactory {
private Scope toScope(List<Slot> slots) {
if (outerScope.isPresent()) {
- return new Scope(outerScope, slots);
+ return new Scope(outerScope, slots, outerScope.get().getSubquery());
} else {
return new Scope(slots);
}
@@ -158,7 +167,7 @@ public class BindSlotReference implements AnalysisRuleFactory {
return (E) new SlotBinder(toScope(boundedSlots), plan, cascadesContext).bind(expr);
}
- private class SlotBinder extends DefaultSubExprRewriter<Void> {
+ private class SlotBinder extends SubExprAnalyzer {
private final Plan plan;
public SlotBinder(Scope scope, Plan plan, CascadesContext cascadesContext) {
@@ -171,7 +180,7 @@ public class BindSlotReference implements AnalysisRuleFactory {
}
@Override
- public Expression visitUnboundAlias(UnboundAlias unboundAlias, Void context) {
+ public Expression visitUnboundAlias(UnboundAlias unboundAlias, PlannerContext context) {
Expression child = unboundAlias.child().accept(this, context);
if (child instanceof NamedExpression) {
return new Alias(child, ((NamedExpression) child).getName());
@@ -182,19 +191,26 @@ public class BindSlotReference implements AnalysisRuleFactory {
}
@Override
- public Slot visitUnboundSlot(UnboundSlot unboundSlot, Void context) {
- Optional<List<Slot>> boundedOpt = getScope()
- .toScopeLink() // Scope Link from inner scope to outer scope
- .stream()
- .map(scope -> bindSlot(unboundSlot, scope.getSlots()))
- .filter(slots -> !slots.isEmpty())
- .findFirst();
+ public Slot visitUnboundSlot(UnboundSlot unboundSlot, PlannerContext context) {
+ Optional<List<Slot>> boundedOpt = Optional.of(bindSlot(unboundSlot, getScope().getSlots()));
+ boolean foundInThisScope = !boundedOpt.get().isEmpty();
+ // Currently only looking for symbols on the previous level.
+ if (!foundInThisScope && getScope().getOuterScope().isPresent()) {
+ boundedOpt = Optional.of(bindSlot(unboundSlot,
+ getScope()
+ .getOuterScope()
+ .get()
+ .getSlots()));
+ }
if (!boundedOpt.isPresent()) {
throw new AnalysisException("Cannot resolve " + unboundSlot.toString());
}
List<Slot> bounded = boundedOpt.get();
switch (bounded.size()) {
case 1:
+ if (!foundInThisScope) {
+ getScope().getOuterScope().get().getCorrelatedSlots().add(bounded.get(0));
+ }
return bounded.get(0);
default:
throw new AnalysisException(unboundSlot + " is ambiguous： "
@@ -205,7 +221,7 @@ public class BindSlotReference implements AnalysisRuleFactory {
}
@Override
- public Expression visitUnboundStar(UnboundStar unboundStar, Void context) {
+ public Expression visitUnboundStar(UnboundStar unboundStar, PlannerContext context) {
if (!(plan instanceof LogicalProject)) {
throw new AnalysisException("UnboundStar must exists in Projection");
}
@@ -222,7 +238,7 @@ public class BindSlotReference implements AnalysisRuleFactory {
}
}
- private BoundStar bindQualifiedStar(List<String> qualifierStar, Void context) {
+ private BoundStar bindQualifiedStar(List<String> qualifierStar, PlannerContext context) {
// FIXME: compatible with previous behavior:
// https://github.com/apache/doris/pull/10415/files/3fe9cb0c3f805ab3a9678033b281b16ad93ec60a#r910239452
List<Slot> slots = getScope().getSlots().stream().filter(boundSlot -> {
@@ -319,4 +335,148 @@ public class BindSlotReference implements AnalysisRuleFactory {
return (List) children();
}
}
+
+ /**
+ * Use the visitor to iterate sub expression.
+ */
+ private static class SubExprAnalyzer extends DefaultExpressionRewriter<PlannerContext> {
+ private final Scope scope;
+ private final CascadesContext cascadesContext;
+
+ public SubExprAnalyzer(Scope scope, CascadesContext cascadesContext) {
+ this.scope = scope;
+ this.cascadesContext = cascadesContext;
+ }
+
+ @Override
+ public Expression visitNot(Not not, PlannerContext context) {
+ Expression child = not.child();
+ if (child instanceof Exists) {
+ return visitExistsSubquery(
+ new Exists(((Exists) child).getQueryPlan(), true), context);
+ } else if (child instanceof InSubquery) {
+ return visitInSubquery(new InSubquery(((InSubquery) child).getCompareExpr(),
+ ((InSubquery) child).getListQuery(), true), context);
+ }
+ return visit(not, context);
+ }
+
+ @Override
+ public Expression visitExistsSubquery(Exists exists, PlannerContext context) {
+ AnalyzedResult analyzedResult = analyzeSubquery(exists);
+
+ return new Exists(analyzedResult.getLogicalPlan(),
+ analyzedResult.getCorrelatedSlots(), exists.isNot());
+ }
+
+ @Override
+ public Expression visitInSubquery(InSubquery expr, PlannerContext context) {
+ AnalyzedResult analyzedResult = analyzeSubquery(expr);
+
+ checkOutputColumn(analyzedResult.getLogicalPlan());
+ checkHasGroupBy(analyzedResult);
+
+ return new InSubquery(
+ expr.getCompareExpr().accept(this, context),
+ new ListQuery(analyzedResult.getLogicalPlan()),
+ analyzedResult.getCorrelatedSlots(), expr.isNot());
+ }
+
+ @Override
+ public Expression visitScalarSubquery(ScalarSubquery scalar, PlannerContext context) {
+ AnalyzedResult analyzedResult = analyzeSubquery(scalar);
+
+ checkOutputColumn(analyzedResult.getLogicalPlan());
+ checkRootIsAgg(analyzedResult);
+ checkHasGroupBy(analyzedResult);
+
+ return new ScalarSubquery(analyzedResult.getLogicalPlan(), analyzedResult.getCorrelatedSlots());
+ }
+
+ private void checkOutputColumn(LogicalPlan plan) {
+ if (plan.getOutput().size() != 1) {
+ throw new AnalysisException("Multiple columns returned by subquery are not yet supported. Found "
+ + plan.getOutput().size());
+ }
+ }
+
+ private void checkRootIsAgg(AnalyzedResult analyzedResult) {
+ if (!analyzedResult.isCorrelated()) {
+ return;
+ }
+ if (!analyzedResult.rootIsAgg()) {
+ throw new AnalysisException("The select item in correlated subquery of binary predicate "
+ + "should only be sum, min, max, avg and count. Current subquery: "
+ + analyzedResult.getLogicalPlan());
+ }
+ }
+
+ private void checkHasGroupBy(AnalyzedResult analyzedResult) {
+ if (!analyzedResult.isCorrelated()) {
+ return;
+ }
+ if (analyzedResult.hasGroupBy()) {
+ throw new AnalysisException("Unsupported correlated subquery with grouping and/or aggregation "
+ + analyzedResult.getLogicalPlan());
+ }
+ }
+
+ private AnalyzedResult analyzeSubquery(SubqueryExpr expr) {
+ CascadesContext subqueryContext = new Memo(expr.getQueryPlan())
+ .newCascadesContext((cascadesContext.getStatementContext()));
+ Scope subqueryScope = genScopeWithSubquery(expr);
+ subqueryContext
+ .newAnalyzer(Optional.of(subqueryScope))
+ .analyze();
+ return new AnalyzedResult((LogicalPlan) subqueryContext.getMemo().copyOut(false),
+ subqueryScope.getCorrelatedSlots());
+ }
+
+ private Scope genScopeWithSubquery(SubqueryExpr expr) {
+ return new Scope(getScope().getOuterScope(),
+ getScope().getSlots(),
+ Optional.ofNullable(expr));
+ }
+
+ public Scope getScope() {
+ return scope;
+ }
+
+ public CascadesContext getCascadesContext() {
+ return cascadesContext;
+ }
+ }
+
+ private static class AnalyzedResult {
+ private final LogicalPlan logicalPlan;
+ private final List<Slot> correlatedSlots;
+
+ public AnalyzedResult(LogicalPlan logicalPlan, List<Slot> correlatedSlots) {
+ this.logicalPlan = Objects.requireNonNull(logicalPlan, "logicalPlan can not be null");
+ this.correlatedSlots = correlatedSlots == null ? new ArrayList<>() : ImmutableList.copyOf(correlatedSlots);
+ }
+
+ public LogicalPlan getLogicalPlan() {
+ return logicalPlan;
+ }
+
+ public List<Slot> getCorrelatedSlots() {
+ return correlatedSlots;
+ }
+
+ public boolean isCorrelated() {
+ return !correlatedSlots.isEmpty();
+ }
+
+ public boolean rootIsAgg() {
+ return logicalPlan instanceof LogicalAggregate;
+ }
+
+ public boolean hasGroupBy() {
+ if (rootIsAgg()) {
+ return !((LogicalAggregate) logicalPlan).getGroupByExpressions().isEmpty();
+ }
+ return false;
+ }
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/Scope.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/Scope.java
index a9df300964..918392f9a5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/Scope.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/Scope.java
@@ -18,28 +18,58 @@
package org.apache.doris.nereids.rules.analysis;
import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableList.Builder;
+import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
/**
* The slot range required for expression analyze.
+ *
+ * slots: The symbols used at this level are stored in slots.
+ * outerScope: The scope information corresponding to the parent is stored in outerScope.
+ * ownerSubquery: The subquery corresponding to ownerSubquery.
+ * subqueryToOuterCorrelatedSlots: The slots correlated in the subquery,
+ * only the slots that cannot be resolved at this level.
+ *
+ * eg:
+ * t1(k1, v1) / t2(k2, v2)
+ * select * from t1 where t1.k1 = (select sum(k2) from t2 where t1.v1 = t2.v2);
+ *
+ * When analyzing subquery:
+ *
+ * slots: k2, v2;
+ * outerScope:
+ * slots: k1, v1;
+ * outerScope: Optional.empty();
+ * ownerSubquery: Optionsl.empty();
+ * subqueryToOuterCorrelatedSlots: empty();
+ * ownerSubquery: subquery((select sum(k2) from t2 where t1.v1 = t2.v2));
+ * subqueryToOuterCorrelatedSlots: (subquery, v1);
*/
public class Scope {
private final Optional<Scope> outerScope;
private final List<Slot> slots;
- public Scope(Optional<Scope> outerScope, List<Slot> slots) {
+ private final Optional<SubqueryExpr> ownerSubquery;
+ private List<Slot> correlatedSlots;
+
+ public Scope(Optional<Scope> outerScope, List<Slot> slots, Optional<SubqueryExpr> subqueryExpr) {
this.outerScope = outerScope;
this.slots = slots;
+ this.ownerSubquery = subqueryExpr;
+ this.correlatedSlots = new ArrayList<>();
}
public Scope(List<Slot> slots) {
this.outerScope = Optional.empty();
this.slots = slots;
+ this.ownerSubquery = Optional.empty();
+ this.correlatedSlots = new ArrayList<>();
}
public List<Slot> getSlots() {
@@ -50,8 +80,17 @@ public class Scope {
return outerScope;
}
+ public Optional<SubqueryExpr> getSubquery() {
+ return ownerSubquery;
+ }
+
+ public List<Slot> getCorrelatedSlots() {
+ return correlatedSlots;
+ }
+
/**
* generate scope link from inner to outer.
+ * Used for multi-level subquery parsing.
*/
public List<Scope> toScopeLink() {
Scope scope = this;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/LogicalAssertNumRowsToPhysicalAssertNumRows.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/LogicalAssertNumRowsToPhysicalAssertNumRows.java
new file mode 100644
index 0000000000..9c18dab5c3
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/LogicalAssertNumRowsToPhysicalAssertNumRows.java
@@ -0,0 +1,36 @@
+// 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.
+
+package org.apache.doris.nereids.rules.implementation;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalAssertNumRows;
+
+/**
+ * Implementation rule that convert logical assertNumRows to physical assertNumRows.
+ */
+public class LogicalAssertNumRowsToPhysicalAssertNumRows extends OneImplementationRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalAssertNumRows().then(topN -> new PhysicalAssertNumRows<>(
+ topN.getAssertNumRowsElement(),
+ topN.getLogicalProperties(),
+ topN.child())
+ ).toRule(RuleType.LOGICAL_ASSERT_NUM_ROWS_TO_PHYSICAL_ASSERT_NUM_ROWS);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnAgg.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnAgg.java
new file mode 100644
index 0000000000..293aff291f
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnAgg.java
@@ -0,0 +1,91 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+import org.apache.doris.nereids.util.Utils;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.stream.Collectors;
+
+/**
+ * Merge the correlated predicate and agg in the filter under apply.
+ * And keep the unCorrelated predicate under agg.
+ *
+ * Use the correlated column as the group by column of agg,
+ * the output column is the correlated column and the input column.
+ *
+ * before:
+ * apply
+ * / \
+ * Input(output:b) agg(output:fn; group by:null)
+ * |
+ * Filter(correlated predicate(Input.e = this.f)/Unapply predicate)
+ *
+ * end:
+ * apply(correlated predicate(Input.e = this.f))
+ * / \
+ * Input(output:b) agg(output:fn,this.f; group by:this.f)
+ * |
+ * Filter(Uncorrelated predicate)
+ */
+public class ApplyPullFilterOnAgg extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply(group(), logicalAggregate(logicalFilter())).when(LogicalApply::isCorrelated).then(apply -> {
+ LogicalAggregate<LogicalFilter<GroupPlan>> agg = apply.right();
+ LogicalFilter<GroupPlan> filter = agg.child();
+ List<Expression> predicates = ExpressionUtils.extractConjunction(filter.getPredicates());
+ Map<Boolean, List<Expression>> split = Utils.splitCorrelatedConjuncts(
+ predicates, apply.getCorrelationSlot());
+ List<Expression> correlatedPredicate = split.get(true);
+ List<Expression> unCorrelatedPredicate = split.get(false);
+
+ LogicalFilter<GroupPlan> newUnCorrelatedFilter = null;
+ if (!unCorrelatedPredicate.isEmpty()) {
+ newUnCorrelatedFilter = new LogicalFilter<>(ExpressionUtils.and(unCorrelatedPredicate),
+ filter.child());
+ }
+
+ List<NamedExpression> newAggOutput = new ArrayList<>(agg.getOutputExpressions());
+ List<Expression> newGroupby = Utils.getCorrelatedSlots(correlatedPredicate,
+ apply.getCorrelationSlot());
+ newGroupby.addAll(agg.getGroupByExpressions());
+ newAggOutput.addAll(newGroupby.stream().map(NamedExpression.class::cast).collect(Collectors.toList()));
+ LogicalAggregate newAgg = new LogicalAggregate<>(
+ newGroupby, newAggOutput,
+ newUnCorrelatedFilter == null ? filter.child() : newUnCorrelatedFilter);
+ return new LogicalApply<>(apply.getCorrelationSlot(),
+ apply.getSubqueryExpr(),
+ Optional.ofNullable(ExpressionUtils.and(correlatedPredicate)),
+ apply.left(), newAgg);
+ }).toRule(RuleType.APPLY_PULL_FILTER_ON_AGG);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnProjectUnderAgg.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnProjectUnderAgg.java
new file mode 100644
index 0000000000..0c5d8f29f6
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ApplyPullFilterOnProjectUnderAgg.java
@@ -0,0 +1,84 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import com.google.common.collect.Lists;
+
+import java.util.List;
+
+/**
+ * Swap the order of project and filter under agg in correlated subqueries.
+ * before:
+ * apply
+ * / \
+ * Input(output:b) agg
+ * |
+ * Project(output:a)
+ * |
+ * Filter(correlated predicate(Input.e = this.f)/Unapply predicate)
+ * |
+ * child
+ *
+ * after:
+ * apply
+ * / \
+ * Input(output:b) agg
+ * |
+ * Filter(correlated predicate(Input.e = this.f)/Unapply predicate)
+ * |
+ * Project(output:a,this.f, Unapply predicate(slots))
+ * |
+ * child
+ */
+public class ApplyPullFilterOnProjectUnderAgg extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply(group(), logicalAggregate(logicalProject(logicalFilter())))
+ .when(LogicalApply::isCorrelated).then(apply -> {
+ LogicalAggregate<LogicalProject<LogicalFilter<GroupPlan>>> agg = apply.right();
+
+ LogicalProject<LogicalFilter<GroupPlan>> project = agg.child();
+ LogicalFilter<GroupPlan> filter = project.child();
+ List<NamedExpression> newProjects = Lists.newArrayList();
+ newProjects.addAll(project.getProjects());
+ filter.child().getOutput()
+ .stream().forEach(slot -> {
+ if (!newProjects.contains(slot)) {
+ newProjects.add(slot);
+ }
+ });
+
+ LogicalProject newProject = new LogicalProject<>(newProjects, filter.child());
+ LogicalFilter newFilter = new LogicalFilter<>(filter.getPredicates(), newProject);
+ LogicalAggregate newAgg = new LogicalAggregate<>(
+ agg.getGroupByExpressions(), agg.getOutputExpressions(), newFilter);
+ return new LogicalApply<>(apply.getCorrelationSlot(), apply.getSubqueryExpr(),
+ apply.getCorrelationFilter(), apply.left(), newAgg);
+ }).toRule(RuleType.APPLY_PULL_FILTER_ON_PROJECT_UNDER_AGG);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ExistsApplyToJoin.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ExistsApplyToJoin.java
new file mode 100644
index 0000000000..5f02210d95
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ExistsApplyToJoin.java
@@ -0,0 +1,120 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Exists;
+import org.apache.doris.nereids.trees.expressions.functions.Count;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
+
+import java.util.ArrayList;
+
+/**
+ * Convert Existsapply to LogicalJoin.
+ *
+ * Exists
+ * Correlated -> LEFT_SEMI_JOIN
+ * apply LEFT_SEMI_JOIN(Correlated Predicate)
+ * / \ --> / \
+ * input queryPlan input queryPlan
+ *
+ * UnCorrelated -> CROSS_JOIN(limit(1))
+ * apply CROSS_JOIN
+ * / \ --> / \
+ * input queryPlan input limit(1)
+ * |
+ * queryPlan
+ *
+ * Not Exists
+ * Correlated -> LEFT_ANTI_JOIN
+ * apply LEFT_ANTI_JOIN(Correlated Predicate)
+ * / \ --> / \
+ * input queryPlan input queryPlan
+ *
+ * UnCorrelated -> CROSS_JOIN(Count(*))
+ * Filter(count(*) = 0)
+ * |
+ * apply Cross_Join
+ * / \ --> / \
+ * input queryPlan input agg(output:count(*))
+ * |
+ * limit(1)
+ * |
+ * queryPlan
+ */
+public class ExistsApplyToJoin extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply().when(LogicalApply::isExist).then(apply -> {
+ if (apply.isCorrelated()) {
+ return correlatedToJoin(apply);
+ } else {
+ return unCorrelatedToJoin(apply);
+ }
+ }).toRule(RuleType.EXISTS_APPLY_TO_JOIN);
+ }
+
+ private Plan correlatedToJoin(LogicalApply apply) {
+ if (((Exists) apply.getSubqueryExpr()).isNot()) {
+ return new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN, Lists.newArrayList(), apply.getCorrelationFilter(),
+ (LogicalPlan) apply.left(), (LogicalPlan) apply.right());
+ } else {
+ return new LogicalJoin<>(JoinType.LEFT_SEMI_JOIN, Lists.newArrayList(), apply.getCorrelationFilter(),
+ (LogicalPlan) apply.left(), (LogicalPlan) apply.right());
+ }
+ }
+
+ private Plan unCorrelatedToJoin(LogicalApply unapply) {
+ if (((Exists) unapply.getSubqueryExpr()).isNot()) {
+ return unCorrelatedNotExist(unapply);
+ } else {
+ return unCorrelatedExist(unapply);
+ }
+ }
+
+ private Plan unCorrelatedNotExist(LogicalApply unapply) {
+ LogicalLimit newLimit = new LogicalLimit<>(1, 0, (LogicalPlan) unapply.right());
+ Alias alias = new Alias(new Count(), "count(*)");
+ LogicalAggregate newAgg = new LogicalAggregate<>(new ArrayList<>(),
+ ImmutableList.of(alias), newLimit);
+ LogicalJoin newJoin = new LogicalJoin<>(JoinType.CROSS_JOIN,
+ (LogicalPlan) unapply.left(), newAgg);
+ return new LogicalFilter<>(new EqualTo(newAgg.getOutput().get(0),
+ new IntegerLiteral(0)), newJoin);
+ }
+
+ private Plan unCorrelatedExist(LogicalApply unapply) {
+ LogicalLimit newLimit = new LogicalLimit<>(1, 0, (LogicalPlan) unapply.right());
+ return new LogicalJoin<>(JoinType.CROSS_JOIN, (LogicalPlan) unapply.left(), newLimit);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
new file mode 100644
index 0000000000..baf459c983
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
@@ -0,0 +1,68 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+
+import java.util.Optional;
+
+/**
+ * Convert InApply to LogicalJoin.
+ *
+ * Not In -> LEFT_ANTI_JOIN
+ * In -> LEFT_SEMI_JOIN
+ */
+public class InApplyToJoin extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply().when(LogicalApply::isIn).then(apply -> {
+ Expression predicate;
+ if (apply.isCorrelated()) {
+ predicate = ExpressionUtils.and(
+ new EqualTo(((InSubquery) apply.getSubqueryExpr()).getCompareExpr(),
+ apply.right().getOutput().get(0)),
+ apply.getCorrelationFilter().get());
+ } else {
+ predicate = new EqualTo(((InSubquery) apply.getSubqueryExpr()).getCompareExpr(),
+ apply.right().getOutput().get(0));
+ }
+
+ LogicalJoin newJoin;
+ if (((InSubquery) apply.getSubqueryExpr()).isNot()) {
+ newJoin = new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN, Lists.newArrayList(), Optional.of(predicate),
+ apply.left(), apply.right());
+ } else {
+ newJoin = new LogicalJoin<>(JoinType.LEFT_SEMI_JOIN, Lists.newArrayList(), Optional.of(predicate),
+ apply.left(), apply.right());
+ }
+ return new LogicalProject(apply.left().getOutput(), newJoin);
+ }).toRule(RuleType.IN_APPLY_TO_JOIN);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderFilter.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderFilter.java
new file mode 100644
index 0000000000..ed9b565090
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderFilter.java
@@ -0,0 +1,79 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+import org.apache.doris.nereids.util.Utils;
+
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+
+/**
+ * Exchange apply and filter.
+ * Keep the non correlated predicate in apply and raise the correlated predicate.
+ *
+ * before:
+ * apply
+ * / \
+ * Input(output:b) Filter(Correlated predicate/UnCorrelated predicate)
+ *
+ * after:
+ * Filter(Correlated predicate)
+ * |
+ * apply
+ * / \
+ * Input(output:b) Filter(UnCorrelated predicate)
+ */
+public class PushApplyUnderFilter extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply(group(), logicalFilter()).when(LogicalApply::isCorrelated).then(apply -> {
+ LogicalFilter<GroupPlan> filter = apply.right();
+ List<Expression> predicates = ExpressionUtils.extractConjunction(filter.getPredicates());
+ Map<Boolean, List<Expression>> split = Utils.splitCorrelatedConjuncts(
+ predicates, apply.getCorrelationSlot());
+ List<Expression> correlatedPredicate = split.get(true);
+ List<Expression> unCorrelatedPredicate = split.get(false);
+
+ // the representative has experienced the rule and added the correlated predicate to the apply node
+ if (correlatedPredicate.isEmpty()) {
+ return apply;
+ }
+
+ if (unCorrelatedPredicate.isEmpty()) {
+ return new LogicalApply<>(apply.getCorrelationSlot(), apply.getSubqueryExpr(),
+ Optional.ofNullable(ExpressionUtils.and(correlatedPredicate)),
+ apply.left(), filter.child());
+ } else {
+ LogicalFilter<GroupPlan> newFilter = new LogicalFilter<>(
+ ExpressionUtils.and(unCorrelatedPredicate), filter.child());
+ return new LogicalApply<>(apply.getCorrelationSlot(), apply.getSubqueryExpr(),
+ Optional.ofNullable(ExpressionUtils.and(correlatedPredicate)),
+ apply.left(), newFilter);
+ }
+ }).toRule(RuleType.PUSH_APPLY_UNDER_FILTER);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderProject.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderProject.java
new file mode 100644
index 0000000000..faccf00998
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushApplyUnderProject.java
@@ -0,0 +1,64 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Adjust the order of Project and apply in correlated subqueries.
+ *
+ * before:
+ * apply
+ * / \
+ * Input(output:b) Project(output:a)
+ * |
+ * child
+ *
+ * after:
+ * Project(b,(if the Subquery is Scalar add 'a' as the output column))
+ * |
+ * apply
+ * / \
+ * Input(output:b) child
+ */
+public class PushApplyUnderProject extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply(group(), logicalProject()).when(LogicalApply::isCorrelated).then(apply -> {
+ LogicalProject<GroupPlan> project = apply.right();
+ LogicalApply newCorrelate = new LogicalApply<>(apply.getCorrelationSlot(), apply.getSubqueryExpr(),
+ apply.getCorrelationFilter(), apply.left(), project.child());
+ List<Slot> newSlots = new ArrayList<>();
+ newSlots.addAll(apply.left().getOutput());
+ if (apply.getSubqueryExpr() instanceof ScalarSubquery) {
+ newSlots.add(apply.right().getOutput().get(0));
+ }
+ return new LogicalProject(newSlots, newCorrelate);
+ }).toRule(RuleType.PUSH_APPLY_UNDER_PROJECT);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
new file mode 100644
index 0000000000..944f9c89a8
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
@@ -0,0 +1,74 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import com.google.common.collect.Lists;
+
+import java.util.List;
+
+/**
+ * Convert scalarApply to LogicalJoin.
+ *
+ * UnCorrelated -> CROSS_JOIN
+ * Correlated -> LEFT_OUTER_JOIN
+ */
+public class ScalarApplyToJoin extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalApply().when(LogicalApply::isScalar).then(apply -> {
+ if (apply.isCorrelated()) {
+ return correlatedToJoin(apply);
+ } else {
+ return unCorrelatedToJoin(apply);
+ }
+ }).toRule(RuleType.SCALAR_APPLY_TO_JOIN);
+ }
+
+ private Plan unCorrelatedToJoin(LogicalApply apply) {
+ LogicalAssertNumRows assertNumRows = new LogicalAssertNumRows(
+ new AssertNumRowsElement(
+ 1, apply.getSubqueryExpr().toString(),
+ AssertNumRowsElement.Assertion.EQ),
+ (LogicalPlan) apply.right());
+ LogicalJoin newJoin = new LogicalJoin<>(JoinType.CROSS_JOIN,
+ (LogicalPlan) apply.left(), assertNumRows);
+ List<Slot> projects = ((LogicalPlan) apply.left()).getOutput();
+ return new LogicalProject(projects, newJoin);
+ }
+
+ private Plan correlatedToJoin(LogicalApply apply) {
+ return new LogicalJoin<>(JoinType.LEFT_OUTER_JOIN,
+ Lists.newArrayList(),
+ apply.getCorrelationFilter(),
+ (LogicalPlan) apply.left(),
+ (LogicalPlan) apply.right());
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
index 2e315c5604..a7c47e6ce6 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
@@ -18,6 +18,7 @@
package org.apache.doris.nereids.stats;
import org.apache.doris.common.CheckedMath;
+import org.apache.doris.nereids.trees.expressions.Cast;
import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
@@ -69,6 +70,13 @@ public class JoinEstimation {
return statsDeriveResult;
}
+ private static Expression removeCast(Expression parent) {
+ if (parent instanceof Cast) {
+ return removeCast(((Cast) parent).child());
+ }
+ return parent;
+ }
+
// TODO: If the condition of Join Plan could any expression in addition to EqualTo type,
// we should handle that properly.
private static long getSemiJoinRowCount(StatsDeriveResult leftStats, StatsDeriveResult rightStats,
@@ -89,9 +97,9 @@ public class JoinEstimation {
Map<Slot, ColumnStats> rightSlotToColStats = rightStats.getSlotToColumnStats();
double minSelectivity = 1.0;
for (Expression eqJoinPredicate : eqConjunctList) {
- long lhsNdv = leftSlotToColStats.get(eqJoinPredicate.child(0)).getNdv();
+ long lhsNdv = leftSlotToColStats.get(removeCast(eqJoinPredicate.child(0))).getNdv();
lhsNdv = Math.min(lhsNdv, leftStats.getRowCount());
- long rhsNdv = rightSlotToColStats.get(eqJoinPredicate.child(1)).getNdv();
+ long rhsNdv = rightSlotToColStats.get(removeCast(eqJoinPredicate.child(1))).getNdv();
rhsNdv = Math.min(rhsNdv, rightStats.getRowCount());
// Skip conjuncts with unknown NDV on either side.
if (lhsNdv == -1 || rhsNdv == -1) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
index 50052a959b..85f480bec5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
@@ -34,6 +34,7 @@ import org.apache.doris.nereids.trees.plans.algebra.Project;
import org.apache.doris.nereids.trees.plans.algebra.Scan;
import org.apache.doris.nereids.trees.plans.algebra.TopN;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
@@ -42,6 +43,7 @@ import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
import org.apache.doris.nereids.trees.plans.logical.LogicalTopN;
import org.apache.doris.nereids.trees.plans.physical.PhysicalAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.physical.PhysicalDistribution;
import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter;
import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
@@ -140,6 +142,11 @@ public class StatsCalculator extends DefaultPlanVisitor<StatsDeriveResult, Void>
groupExpression.getCopyOfChildStats(1), join);
}
+ @Override
+ public StatsDeriveResult visitLogicalAssertNumRows(LogicalAssertNumRows<Plan> assertNumRows, Void context) {
+ return groupExpression.getCopyOfChildStats(0);
+ }
+
@Override
public StatsDeriveResult visitPhysicalAggregate(PhysicalAggregate<Plan> agg, Void context) {
return computeAggregate(agg);
@@ -190,6 +197,12 @@ public class StatsCalculator extends DefaultPlanVisitor<StatsDeriveResult, Void>
return groupExpression.getCopyOfChildStats(0);
}
+ @Override
+ public StatsDeriveResult visitPhysicalAssertNumRows(PhysicalAssertNumRows<Plan> assertNumRows,
+ Void context) {
+ return groupExpression.getCopyOfChildStats(0);
+ }
+
private StatsDeriveResult computeFilter(Filter filter) {
StatsDeriveResult stats = groupExpression.getCopyOfChildStats(0);
FilterSelectivityCalculator selectivityCalculator =
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
index a84955b287..d9f8735103 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
@@ -49,7 +49,7 @@ public class Alias extends NamedExpression implements UnaryExpression {
}
@VisibleForTesting
- Alias(ExprId exprId, Expression child, String name) {
+ public Alias(ExprId exprId, Expression child, String name) {
super(child);
this.exprId = exprId;
this.name = name;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
new file mode 100644
index 0000000000..60ae3a2e35
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
@@ -0,0 +1,109 @@
+// 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.
+
+package org.apache.doris.nereids.trees.expressions;
+
+import org.apache.doris.nereids.trees.expressions.shape.LeafExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.util.Utils;
+
+import com.google.common.base.Preconditions;
+
+import java.util.List;
+import java.util.Objects;
+
+/**
+ * Number of rows returned by inspection in subquery.
+ */
+public class AssertNumRowsElement extends Expression implements LeafExpression {
+ /**
+ * Assertion type.
+ */
+ public enum Assertion {
+ EQ, // val1 == val2
+ NE, // val1 != val2
+ LT, // val1 < val2
+ LE, // val1 <= val2
+ GT, // val1 > val2
+ GE; // val1 >= val2
+ }
+
+ private final long desiredNumOfRows;
+ private final String subqueryString;
+ private final Assertion assertion;
+
+ public AssertNumRowsElement(long desiredNumOfRows, String subqueryString,
+ Assertion assertion) {
+ this.desiredNumOfRows = desiredNumOfRows;
+ this.subqueryString = subqueryString;
+ this.assertion = assertion;
+ }
+
+ public long getDesiredNumOfRows() {
+ return desiredNumOfRows;
+ }
+
+ public String getSubqueryString() {
+ return subqueryString;
+ }
+
+ public Assertion getAssertion() {
+ return assertion;
+ }
+
+ @Override
+ public AssertNumRowsElement withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 0);
+ return this;
+ }
+
+ @Override
+ public String toString() {
+ return Utils.toSqlString("desiredNumOfRows: ",
+ Long.toString(desiredNumOfRows),
+ "assertion: " + assertion);
+ }
+
+ @Override
+ public String toSql() {
+ return toString();
+ }
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ AssertNumRowsElement that = (AssertNumRowsElement) o;
+ return Objects.equals(this.desiredNumOfRows, that.getDesiredNumOfRows())
+ && Objects.equals(this.subqueryString, that.getSubqueryString())
+ && Objects.equals(this.assertion, that.getAssertion());
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(desiredNumOfRows, subqueryString, assertion);
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitAssertNumRowsElement(this, context);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
index 121da5beb7..d9cd7c695e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
@@ -24,15 +24,30 @@ import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.types.BooleanType;
import org.apache.doris.nereids.types.DataType;
+import com.google.common.base.Preconditions;
+
+import java.util.List;
import java.util.Objects;
/**
* Exists subquery expression.
*/
public class Exists extends SubqueryExpr implements LeafExpression {
+ private final boolean isNot;
- public Exists(LogicalPlan subquery) {
+ public Exists(LogicalPlan subquery, boolean isNot) {
super(Objects.requireNonNull(subquery, "subquery can not be null"));
+ this.isNot = Objects.requireNonNull(isNot, "isNot can not be null");
+ }
+
+ public Exists(LogicalPlan subquery, List<Slot> correlateSlots, boolean isNot) {
+ super(Objects.requireNonNull(subquery, "subquery can not be null"),
+ Objects.requireNonNull(correlateSlots, "subquery can not be null"));
+ this.isNot = Objects.requireNonNull(isNot, "isNot can not be null");
+ }
+
+ public boolean isNot() {
+ return isNot;
}
@Override
@@ -53,4 +68,28 @@ public class Exists extends SubqueryExpr implements LeafExpression {
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitExistsSubquery(this, context);
}
+
+ @Override
+ public Expression withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new Exists(((Exists) children.get(0)).getQueryPlan(), isNot);
+ }
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ Exists other = (Exists) o;
+ return Objects.equals(this.queryPlan, other.getQueryPlan())
+ && Objects.equals(this.isNot, other.isNot());
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(this.queryPlan, this.isNot);
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java
index 39c655d66a..77423b165f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java
@@ -33,11 +33,21 @@ public class InSubquery extends SubqueryExpr {
private final Expression compareExpr;
private final ListQuery listQuery;
+ private final boolean isNot;
- public InSubquery(Expression compareExpression, ListQuery listQuery) {
+ public InSubquery(Expression compareExpression, ListQuery listQuery, boolean isNot) {
super(Objects.requireNonNull(listQuery.getQueryPlan(), "subquery can not be null"));
- this.compareExpr = Objects.requireNonNull(compareExpression);
- this.listQuery = listQuery;
+ this.compareExpr = Objects.requireNonNull(compareExpression, "compareExpr can not be null");
+ this.listQuery = Objects.requireNonNull(listQuery, "listQuery can not be null");
+ this.isNot = Objects.requireNonNull(isNot, "isNot can not be null");
+ }
+
+ public InSubquery(Expression compareExpr, ListQuery listQuery, List<Slot> correlateSlots, boolean isNot) {
+ super(Objects.requireNonNull(listQuery.getQueryPlan(), "subquery can not be null"),
+ Objects.requireNonNull(correlateSlots, "correlateSlots can not be null"));
+ this.compareExpr = Objects.requireNonNull(compareExpr, "compareExpr can not be null");
+ this.listQuery = Objects.requireNonNull(listQuery, "listQuery can not be null");
+ this.isNot = Objects.requireNonNull(isNot, "isNot can not be null");
}
@Override
@@ -52,12 +62,12 @@ public class InSubquery extends SubqueryExpr {
@Override
public String toSql() {
- return this.compareExpr.toSql() + "IN (SUBQUERY) " + super.toSql();
+ return this.compareExpr.toSql() + " IN (INSUBQUERY) " + super.toSql();
}
@Override
public String toString() {
- return this.compareExpr + "IN (SUBQUERY) " + super.toString();
+ return this.compareExpr + " IN (INSUBQUERY) " + super.toString();
}
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
@@ -68,11 +78,19 @@ public class InSubquery extends SubqueryExpr {
return this.compareExpr;
}
+ public boolean isNot() {
+ return isNot;
+ }
+
+ public ListQuery getListQuery() {
+ return listQuery;
+ }
+
@Override
public InSubquery withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() == 2);
Preconditions.checkArgument(children.get(1) instanceof ListQuery);
- return new InSubquery(children.get(0), (ListQuery) children.get(1));
+ return new InSubquery(children.get(0), (ListQuery) children.get(1), isNot);
}
@Override
@@ -85,11 +103,12 @@ public class InSubquery extends SubqueryExpr {
}
InSubquery inSubquery = (InSubquery) o;
return Objects.equals(this.compareExpr, inSubquery.getCompareExpr())
- && checkEquals(this.queryPlan, inSubquery.queryPlan);
+ && checkEquals(this.queryPlan, inSubquery.queryPlan)
+ && Objects.equals(this.isNot, inSubquery.isNot);
}
@Override
public int hashCode() {
- return Objects.hash(this.compareExpr, this.listQuery);
+ return Objects.hash(this.compareExpr, this.listQuery, this.isNot);
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ListQuery.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ListQuery.java
index 023195bcb2..961a46ff68 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ListQuery.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ListQuery.java
@@ -17,12 +17,13 @@
package org.apache.doris.nereids.trees.expressions;
-import org.apache.doris.nereids.exceptions.UnboundException;
import org.apache.doris.nereids.trees.expressions.shape.LeafExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.types.DataType;
+import com.google.common.base.Preconditions;
+
import java.util.Objects;
/**
@@ -35,11 +36,9 @@ public class ListQuery extends SubqueryExpr implements LeafExpression {
}
@Override
- public DataType getDataType() throws UnboundException {
- // TODO:
- // For multiple lines, struct type is returned, in the form of splicing,
- // but it seems that struct type is not currently supported
- throw new UnboundException("not support");
+ public DataType getDataType() {
+ Preconditions.checkArgument(queryPlan.getOutput().size() == 1);
+ return queryPlan.getOutput().get(0).getDataType();
}
@Override
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
index 8b664ce657..e49e514511 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
@@ -25,6 +25,7 @@ import org.apache.doris.nereids.types.DataType;
import com.google.common.base.Preconditions;
+import java.util.List;
import java.util.Objects;
/**
@@ -35,6 +36,11 @@ public class ScalarSubquery extends SubqueryExpr implements LeafExpression {
super(Objects.requireNonNull(subquery, "subquery can not be null"));
}
+ public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots) {
+ super(Objects.requireNonNull(subquery, "subquery can not be null"),
+ Objects.requireNonNull(correlateSlots, "correlateSlots can not be null"));
+ }
+
@Override
public DataType getDataType() throws UnboundException {
Preconditions.checkArgument(queryPlan.getOutput().size() == 1);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SlotReference.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SlotReference.java
index cc77e75f7f..ec62dbe25e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SlotReference.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SlotReference.java
@@ -116,10 +116,19 @@ public class SlotReference extends Slot {
return false;
}
SlotReference that = (SlotReference) o;
+ // The equals of slotRefrance only compares exprId,
+ // because in subqueries with aliases,
+ // there will be scenarios where the same exprId but different qualifiers are used,
+ // resulting in an error due to different qualifiers during comparison.
+ // eg:
+ // select * from t6 where t6.k1 < (select max(aa) from (select v1 as aa from t7 where t6.k2=t7.v2) t2 )
+ //
+ // For aa, the qualifier of aa in the subquery is empty, but in the output column of agg,
+ // the qualifier of aa is t2. but both actually represent the same column.
return exprId.equals(that.exprId);
-
}
+ // The contains method needs to use hashCode, so similar to equals, it only compares exprId
@Override
public int hashCode() {
return Objects.hash(exprId);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
index 836e13a064..37e3f4c0e5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
@@ -22,7 +22,7 @@ import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.types.DataType;
-import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
import java.util.List;
import java.util.Objects;
@@ -31,10 +31,21 @@ import java.util.Objects;
* Subquery Expression.
*/
public abstract class SubqueryExpr extends Expression {
- protected LogicalPlan queryPlan;
+ protected final LogicalPlan queryPlan;
+ protected final List<Slot> correlateSlots;
public SubqueryExpr(LogicalPlan subquery) {
this.queryPlan = Objects.requireNonNull(subquery, "subquery can not be null");
+ this.correlateSlots = ImmutableList.of();
+ }
+
+ public SubqueryExpr(LogicalPlan subquery, List<Slot> correlateSlots) {
+ this.queryPlan = Objects.requireNonNull(subquery, "subquery can not be null");
+ this.correlateSlots = ImmutableList.copyOf(correlateSlots);
+ }
+
+ public List<Slot> getCorrelateSlots() {
+ return correlateSlots;
}
@Override
@@ -44,19 +55,18 @@ public abstract class SubqueryExpr extends Expression {
@Override
public boolean nullable() throws UnboundException {
- // TODO:
- // Any child is nullable, the whole is nullable
- throw new UnboundException("not support");
+ return true;
}
@Override
public String toSql() {
- return "(" + queryPlan.toString() + ")";
+ return "(" + queryPlan + ")";
}
@Override
public String toString() {
- return "(" + queryPlan.toString() + ")";
+ return "(QueryPlan: " + queryPlan
+ + "), (CorrelatedSlots: " + correlateSlots + ")";
}
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
@@ -67,12 +77,6 @@ public abstract class SubqueryExpr extends Expression {
return queryPlan;
}
- @Override
- public Expression withChildren(List<Expression> children) {
- Preconditions.checkArgument(children.size() == 1);
- return children.get(0);
- }
-
@Override
public boolean equals(Object o) {
if (this == o) {
@@ -82,7 +86,8 @@ public abstract class SubqueryExpr extends Expression {
return false;
}
SubqueryExpr other = (SubqueryExpr) o;
- return checkEquals(queryPlan, other.queryPlan);
+ return checkEquals(queryPlan, other.queryPlan)
+ && Objects.equals(correlateSlots, other.correlateSlots);
}
/**
@@ -118,6 +123,10 @@ public abstract class SubqueryExpr extends Expression {
@Override
public int hashCode() {
- return Objects.hash(queryPlan);
+ return Objects.hash(queryPlan, correlateSlots);
+ }
+
+ public List<Slot> getOutput() {
+ return queryPlan.getOutput();
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/DefaultSubExprRewriter.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/DefaultSubExprRewriter.java
deleted file mode 100644
index 919e9095db..0000000000
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/DefaultSubExprRewriter.java
+++ /dev/null
@@ -1,76 +0,0 @@
-// 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.
-
-package org.apache.doris.nereids.trees.expressions.visitor;
-
-import org.apache.doris.nereids.CascadesContext;
-import org.apache.doris.nereids.exceptions.AnalysisException;
-import org.apache.doris.nereids.memo.Memo;
-import org.apache.doris.nereids.rules.analysis.Scope;
-import org.apache.doris.nereids.trees.expressions.Exists;
-import org.apache.doris.nereids.trees.expressions.Expression;
-import org.apache.doris.nereids.trees.expressions.InSubquery;
-import org.apache.doris.nereids.trees.expressions.ListQuery;
-import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
-import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
-import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
-
-import java.util.Optional;
-
-/**
- * Use the visitor to iterate sub expression.
- */
-public class DefaultSubExprRewriter<C> extends DefaultExpressionRewriter<C> {
- private final Scope scope;
- private final CascadesContext cascadesContext;
-
- public DefaultSubExprRewriter(Scope scope, CascadesContext cascadesContext) {
- this.scope = scope;
- this.cascadesContext = cascadesContext;
- }
-
- @Override
- public Expression visitExistsSubquery(Exists exists, C context) {
- return new Exists(analyzeSubquery(exists));
- }
-
- @Override
- public Expression visitInSubquery(InSubquery expr, C context) {
- return new InSubquery(expr.getCompareExpr(), new ListQuery(analyzeSubquery(expr)));
- }
-
- @Override
- public Expression visitScalarSubquery(ScalarSubquery scalar, C context) {
- LogicalPlan analyzed = analyzeSubquery(scalar);
- if (analyzed.getOutput().size() != 1) {
- throw new AnalysisException("Multiple columns returned by subquery are not yet supported. Found "
- + analyzed.getOutput().size());
- }
- return new ScalarSubquery(analyzed);
- }
-
- private LogicalPlan analyzeSubquery(SubqueryExpr expr) {
- CascadesContext subqueryContext = new Memo(expr.getQueryPlan())
- .newCascadesContext(cascadesContext.getStatementContext());
- subqueryContext.newAnalyzer(Optional.ofNullable(getScope())).analyze();
- return (LogicalPlan) subqueryContext.getMemo().copyOut();
- }
-
- public Scope getScope() {
- return scope;
- }
-}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
index d3f24956fb..08904725e5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
@@ -24,6 +24,7 @@ import org.apache.doris.nereids.analyzer.UnboundStar;
import org.apache.doris.nereids.trees.expressions.Add;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.And;
+import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
import org.apache.doris.nereids.trees.expressions.Between;
import org.apache.doris.nereids.trees.expressions.BinaryArithmetic;
import org.apache.doris.nereids.trees.expressions.BinaryOperator;
@@ -300,6 +301,10 @@ public abstract class ExpressionVisitor<R, C> {
return visitSubqueryExpr(listQuery, context);
}
+ public R visitAssertNumRowsElement(AssertNumRowsElement assertNumRowsElement, C context) {
+ return visit(assertNumRowsElement, context);
+ }
+
/* ********************************************************************************************
* Unbound expressions
* ********************************************************************************************/
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/PlanType.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/PlanType.java
index 8361cfeeac..742d16dc28 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/PlanType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/PlanType.java
@@ -36,9 +36,8 @@ public enum PlanType {
LOGICAL_LIMIT,
LOGICAL_OLAP_SCAN,
LOGICAL_APPLY,
- LOGICAL_CORRELATED_JOIN,
- LOGICAL_ENFORCE_SINGLE_ROW,
LOGICAL_SELECT_HINT,
+ LOGICAL_ASSERT_NUM_ROWS,
GROUP_PLAN,
// physical plan
@@ -53,5 +52,6 @@ public enum PlanType {
PHYSICAL_HASH_JOIN,
PHYSICAL_NESTED_LOOP_JOIN,
PHYSICAL_EXCHANGE,
- PHYSICAL_DISTRIBUTION;
+ PHYSICAL_DISTRIBUTION,
+ PHYSICAL_ASSERT_NUM_ROWS;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalApply.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalApply.java
index df9aaeeb34..bdc2e50580 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalApply.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalApply.java
@@ -19,8 +19,12 @@ package org.apache.doris.nereids.trees.plans.logical;
import org.apache.doris.nereids.memo.GroupExpression;
import org.apache.doris.nereids.properties.LogicalProperties;
+import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
+import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.PlanType;
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
@@ -34,43 +38,80 @@ import java.util.Objects;
import java.util.Optional;
/**
- * Apply Node for subquery.
* Use this node to display the subquery in the relational algebra tree.
- * refer to "Orthogonal Optimization of Subqueries and Aggregation"
- *
- * @param <LEFT_CHILD_TYPE> input
- * @param <RIGHT_CHILD_TYPE> subquery
+ * @param <LEFT_CHILD_TYPE> input.
+ * @param <RIGHT_CHILD_TYPE> subquery.
*/
public class LogicalApply<LEFT_CHILD_TYPE extends Plan, RIGHT_CHILD_TYPE extends Plan>
extends LogicalBinary<LEFT_CHILD_TYPE, RIGHT_CHILD_TYPE> {
- // correlation : subqueries and outer associated columns
- private final List<Expression> correlation;
+ // correlation column
+ private final List<Expression> correlationSlot;
+ // original subquery
+ private final SubqueryExpr subqueryExpr;
+ // correlation Conjunction
+ private final Optional<Expression> correlationFilter;
+
+ /**
+ * Constructor.
+ */
+ public LogicalApply(Optional<GroupExpression> groupExpression,
+ Optional<LogicalProperties> logicalProperties,
+ List<Expression> correlationSlot,
+ SubqueryExpr subqueryExpr, Optional<Expression> correlationFilter,
+ LEFT_CHILD_TYPE leftChild, RIGHT_CHILD_TYPE rightChild) {
+ super(PlanType.LOGICAL_APPLY, groupExpression, logicalProperties, leftChild, rightChild);
+ this.correlationSlot = correlationSlot == null ? ImmutableList.of() : ImmutableList.copyOf(correlationSlot);
+ this.subqueryExpr = Objects.requireNonNull(subqueryExpr, "subquery can not be null");
+ this.correlationFilter = correlationFilter;
+ }
- public LogicalApply(LEFT_CHILD_TYPE input, RIGHT_CHILD_TYPE subquery, List<Expression> correlation) {
- this(Optional.empty(), Optional.empty(), input, subquery, correlation);
+ public LogicalApply(List<Expression> correlationSlot, SubqueryExpr subqueryExpr,
+ Optional<Expression> correlationFilter,
+ LEFT_CHILD_TYPE input, RIGHT_CHILD_TYPE subquery) {
+ this(Optional.empty(), Optional.empty(), correlationSlot, subqueryExpr, correlationFilter,
+ input, subquery);
}
- public LogicalApply(Optional<GroupExpression> groupExpression, Optional<LogicalProperties> logicalProperties,
- LEFT_CHILD_TYPE leftChild, RIGHT_CHILD_TYPE rightChild, List<Expression> correlation) {
- super(PlanType.LOGICAL_APPLY, groupExpression, logicalProperties, leftChild, rightChild);
- this.correlation = ImmutableList.copyOf(correlation);
+ public List<Expression> getCorrelationSlot() {
+ return correlationSlot;
+ }
+
+ public Optional<Expression> getCorrelationFilter() {
+ return correlationFilter;
+ }
+
+ public SubqueryExpr getSubqueryExpr() {
+ return subqueryExpr;
}
- public List<Expression> getCorrelation() {
- return correlation;
+ public boolean isScalar() {
+ return this.subqueryExpr instanceof ScalarSubquery;
+ }
+
+ public boolean isIn() {
+ return this.subqueryExpr instanceof InSubquery;
+ }
+
+ public boolean isExist() {
+ return this.subqueryExpr instanceof Exists;
+ }
+
+ public boolean isCorrelated() {
+ return !correlationSlot.isEmpty();
}
@Override
public List<Slot> computeOutput() {
return ImmutableList.<Slot>builder()
.addAll(left().getOutput())
- .addAll(right().getOutput())
.build();
}
@Override
public String toString() {
- return Utils.toSqlString("LogicalApply");
+ return Utils.toSqlString("LogicalApply",
+ "correlationSlot", correlationSlot,
+ "correlationFilter", correlationFilter);
}
@Override
@@ -82,12 +123,15 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan, RIGHT_CHILD_TYPE extends
return false;
}
LogicalApply that = (LogicalApply) o;
- return Objects.equals(correlation, that.getCorrelation());
+ return Objects.equals(correlationSlot, that.getCorrelationSlot())
+ && Objects.equals(subqueryExpr, that.getSubqueryExpr())
+ && Objects.equals(correlationFilter, that.getCorrelationFilter());
}
@Override
public int hashCode() {
- return Objects.hash(correlation);
+ return Objects.hash(
+ correlationSlot, subqueryExpr, correlationFilter);
}
@Override
@@ -97,22 +141,33 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan, RIGHT_CHILD_TYPE extends
@Override
public List<Expression> getExpressions() {
- return correlation;
+ if (correlationFilter.isPresent()) {
+ return new ImmutableList.Builder<Expression>()
+ .addAll(correlationSlot)
+ .add(correlationFilter.get())
+ .build();
+ }
+ return new ImmutableList.Builder<Expression>()
+ .addAll(correlationSlot)
+ .build();
}
@Override
public LogicalBinary<Plan, Plan> withChildren(List<Plan> children) {
Preconditions.checkArgument(children.size() == 2);
- return new LogicalApply<>(children.get(0), children.get(1), correlation);
+ return new LogicalApply<>(correlationSlot, subqueryExpr, correlationFilter,
+ children.get(0), children.get(1));
}
@Override
public Plan withGroupExpression(Optional<GroupExpression> groupExpression) {
- return new LogicalApply<>(groupExpression, Optional.of(logicalProperties), left(), right(), correlation);
+ return new LogicalApply<>(groupExpression, Optional.of(logicalProperties),
+ correlationSlot, subqueryExpr, correlationFilter, left(), right());
}
@Override
public Plan withLogicalProperties(Optional<LogicalProperties> logicalProperties) {
- return new LogicalApply<>(Optional.empty(), logicalProperties, left(), right(), correlation);
+ return new LogicalApply<>(Optional.empty(), logicalProperties,
+ correlationSlot, subqueryExpr, correlationFilter, left(), right());
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
similarity index 52%
copy from fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java
copy to fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
index 4ab50449d3..9a43fb37e8 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
@@ -19,48 +19,47 @@ package org.apache.doris.nereids.trees.plans.logical;
import org.apache.doris.nereids.memo.GroupExpression;
import org.apache.doris.nereids.properties.LogicalProperties;
+import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.PlanType;
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
-import org.apache.doris.nereids.util.Utils;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import java.util.List;
+import java.util.Objects;
import java.util.Optional;
/**
- * Guaranteed to return a result of 1 row.
- * eg: select * from t1 where t1.a = (select sum(t2.b) from t2 where t1.b = t2.a);
- * filter()
- * +--enforceSingleRow()
- * +--apply()
- * @param <CHILD_TYPE> plan
+ * Assert num rows node is used to determine whether the number of rows is less than desired num of rows.
+ * The rows are the result of subqueryString.
+ * If the number of rows is more than the desired num of rows, the query will be cancelled.
+ * The cancelled reason will be reported by Backend and displayed back to the user.
*/
-public class LogicalEnforceSingleRow<CHILD_TYPE extends Plan> extends LogicalUnary<CHILD_TYPE> {
+public class LogicalAssertNumRows<CHILD_TYPE extends Plan> extends LogicalUnary<CHILD_TYPE> {
+ private final AssertNumRowsElement assertNumRowsElement;
- LogicalEnforceSingleRow(CHILD_TYPE plan) {
- this(Optional.empty(), Optional.empty(), plan);
+ public LogicalAssertNumRows(AssertNumRowsElement assertNumRowsElement, CHILD_TYPE child) {
+ this(assertNumRowsElement, Optional.empty(), Optional.empty(), child);
}
- LogicalEnforceSingleRow(Optional<GroupExpression> groupExpression, Optional<LogicalProperties> logicalProperties,
- CHILD_TYPE plan) {
- super(PlanType.LOGICAL_ENFORCE_SINGLE_ROW, groupExpression, logicalProperties, plan);
+ public LogicalAssertNumRows(AssertNumRowsElement assertNumRowsElement, Optional<GroupExpression> groupExpression,
+ Optional<LogicalProperties> logicalProperties, CHILD_TYPE child) {
+ super(PlanType.LOGICAL_ASSERT_NUM_ROWS, groupExpression, logicalProperties, child);
+ this.assertNumRowsElement = Objects.requireNonNull(assertNumRowsElement,
+ "assertNumRowsElement can not be null");
}
- @Override
- public List<Slot> computeOutput() {
- return ImmutableList.<Slot>builder()
- .addAll(child().getOutput())
- .build();
+ public AssertNumRowsElement getAssertNumRowsElement() {
+ return assertNumRowsElement;
}
@Override
public String toString() {
- return Utils.toSqlString("LogicalEnforceSingleRow");
+ return "LogicalAssertNumRows (" + assertNumRowsElement + ")";
}
@Override
@@ -71,32 +70,46 @@ public class LogicalEnforceSingleRow<CHILD_TYPE extends Plan> extends LogicalUna
if (o == null || getClass() != o.getClass()) {
return false;
}
- return true;
+ LogicalAssertNumRows that = (LogicalAssertNumRows) o;
+ return assertNumRowsElement.equals(that.assertNumRowsElement);
}
@Override
- public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
- return visitor.visitLogicalEnforceSingleRow((LogicalEnforceSingleRow<Plan>) this, context);
+ public int hashCode() {
+ return Objects.hash(assertNumRowsElement);
}
@Override
- public Plan withChildren(List<Plan> children) {
- Preconditions.checkArgument(children.size() == 1);
- return new LogicalEnforceSingleRow<>(children.get(0));
+ public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
+ return visitor.visitLogicalAssertNumRows((LogicalAssertNumRows<Plan>) this, context);
}
@Override
public List<Expression> getExpressions() {
- return ImmutableList.of();
+ return ImmutableList.of(assertNumRowsElement);
+ }
+
+ @Override
+ public LogicalUnary<Plan> withChildren(List<Plan> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new LogicalAssertNumRows<>(assertNumRowsElement, children.get(0));
}
@Override
public Plan withGroupExpression(Optional<GroupExpression> groupExpression) {
- return new LogicalEnforceSingleRow<>(groupExpression, Optional.of(logicalProperties), child());
+ return new LogicalAssertNumRows<>(assertNumRowsElement,
+ groupExpression, Optional.of(logicalProperties), child());
}
@Override
public Plan withLogicalProperties(Optional<LogicalProperties> logicalProperties) {
- return new LogicalEnforceSingleRow<>(Optional.empty(), logicalProperties, child());
+ return new LogicalAssertNumRows<>(assertNumRowsElement, Optional.empty(), logicalProperties, child());
+ }
+
+ @Override
+ public List<Slot> computeOutput() {
+ return ImmutableList.<Slot>builder()
+ .addAll(child().getOutput())
+ .build();
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalCorrelatedJoin.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalCorrelatedJoin.java
deleted file mode 100644
index 93ed2b7083..0000000000
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalCorrelatedJoin.java
+++ /dev/null
@@ -1,136 +0,0 @@
-// 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.
-
-package org.apache.doris.nereids.trees.plans.logical;
-
-import org.apache.doris.nereids.memo.GroupExpression;
-import org.apache.doris.nereids.properties.LogicalProperties;
-import org.apache.doris.nereids.trees.expressions.Expression;
-import org.apache.doris.nereids.trees.expressions.Slot;
-import org.apache.doris.nereids.trees.plans.Plan;
-import org.apache.doris.nereids.trees.plans.PlanType;
-import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
-import org.apache.doris.nereids.util.Utils;
-
-import com.google.common.base.Preconditions;
-import com.google.common.collect.ImmutableList;
-
-import java.util.List;
-import java.util.Objects;
-import java.util.Optional;
-
-/**
- * A relational algebra node with join type converted from apply node to subquery.
- *
- * @param <LEFT_CHILD_TYPE> input.
- * @param <RIGHT_CHILD_TYPE> subquery.
- */
-public class LogicalCorrelatedJoin<LEFT_CHILD_TYPE extends Plan, RIGHT_CHILD_TYPE extends Plan>
- extends LogicalBinary<LEFT_CHILD_TYPE, RIGHT_CHILD_TYPE> {
-
- private final List<Expression> correlation;
- private final Optional<Expression> filter;
-
- public LogicalCorrelatedJoin(LEFT_CHILD_TYPE input, RIGHT_CHILD_TYPE subquery, List<Expression> correlation,
- Optional<Expression> filter) {
- this(Optional.empty(), Optional.empty(), input, subquery,
- correlation, filter);
- }
-
- public LogicalCorrelatedJoin(Optional<GroupExpression> groupExpression,
- Optional<LogicalProperties> logicalProperties,
- LEFT_CHILD_TYPE leftChild, RIGHT_CHILD_TYPE rightChild, List<Expression> correlation,
- Optional<Expression> filter) {
- super(PlanType.LOGICAL_CORRELATED_JOIN, groupExpression, logicalProperties, leftChild, rightChild);
- this.correlation = ImmutableList.copyOf(correlation);
- this.filter = Objects.requireNonNull(filter, "filter can not be null");
- }
-
- public List<Expression> getCorrelation() {
- return correlation;
- }
-
- public Optional<Expression> getFilter() {
- return filter;
- }
-
- @Override
- public List<Slot> computeOutput() {
- return ImmutableList.<Slot>builder()
- .addAll(left().getOutput())
- .addAll(right().getOutput())
- .build();
- }
-
- @Override
- public String toString() {
- return Utils.toSqlString("LogicalCorrelated",
- "correlation", correlation,
- "filter", filter
- );
- }
-
- @Override
- public boolean equals(Object o) {
- if (this == o) {
- return true;
- }
- if (o == null || getClass() != o.getClass()) {
- return false;
- }
- LogicalCorrelatedJoin that = (LogicalCorrelatedJoin) o;
- return Objects.equals(correlation, that.getCorrelation())
- && Objects.equals(filter, that.getFilter());
- }
-
- @Override
- public int hashCode() {
- return Objects.hash(correlation, filter);
- }
-
- @Override
- public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
- return visitor.visitLogicalCorrelated((LogicalCorrelatedJoin<Plan, Plan>) this, context);
- }
-
- @Override
- public List<Expression> getExpressions() {
- return new ImmutableList.Builder<Expression>()
- .addAll(correlation)
- .add(filter.get())
- .build();
- }
-
- @Override
- public LogicalBinary<Plan, Plan> withChildren(List<Plan> children) {
- Preconditions.checkArgument(children.size() == 2);
- return new LogicalCorrelatedJoin<>(children.get(0), children.get(1),
- correlation, filter);
- }
-
- @Override
- public Plan withGroupExpression(Optional<GroupExpression> groupExpression) {
- return new LogicalCorrelatedJoin<>(groupExpression, Optional.of(logicalProperties),
- left(), right(), correlation, filter);
- }
-
- @Override
- public Plan withLogicalProperties(Optional<LogicalProperties> logicalProperties) {
- return new LogicalCorrelatedJoin<>(Optional.empty(), logicalProperties,
- left(), right(), correlation, filter);
- }
-}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalAssertNumRows.java
similarity index 54%
rename from fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java
rename to fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalAssertNumRows.java
index 4ab50449d3..6d06d08c8e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalEnforceSingleRow.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalAssertNumRows.java
@@ -15,10 +15,11 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.nereids.trees.plans.logical;
+package org.apache.doris.nereids.trees.plans.physical;
import org.apache.doris.nereids.memo.GroupExpression;
import org.apache.doris.nereids.properties.LogicalProperties;
+import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
@@ -30,25 +31,24 @@ import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import java.util.List;
+import java.util.Objects;
import java.util.Optional;
/**
- * Guaranteed to return a result of 1 row.
- * eg: select * from t1 where t1.a = (select sum(t2.b) from t2 where t1.b = t2.a);
- * filter()
- * +--enforceSingleRow()
- * +--apply()
- * @param <CHILD_TYPE> plan
+ * Physical assertNumRows.
*/
-public class LogicalEnforceSingleRow<CHILD_TYPE extends Plan> extends LogicalUnary<CHILD_TYPE> {
+public class PhysicalAssertNumRows<CHILD_TYPE extends Plan> extends PhysicalUnary<CHILD_TYPE> {
+ private final AssertNumRowsElement assertNumRowsElement;
- LogicalEnforceSingleRow(CHILD_TYPE plan) {
- this(Optional.empty(), Optional.empty(), plan);
+ public PhysicalAssertNumRows(AssertNumRowsElement assertNumRowsElement,
+ LogicalProperties logicalProperties, CHILD_TYPE child) {
+ this(assertNumRowsElement, Optional.empty(), logicalProperties, child);
}
- LogicalEnforceSingleRow(Optional<GroupExpression> groupExpression, Optional<LogicalProperties> logicalProperties,
- CHILD_TYPE plan) {
- super(PlanType.LOGICAL_ENFORCE_SINGLE_ROW, groupExpression, logicalProperties, plan);
+ public PhysicalAssertNumRows(AssertNumRowsElement assertNumRowsElement, Optional<GroupExpression> groupExpression,
+ LogicalProperties logicalProperties, CHILD_TYPE child) {
+ super(PlanType.PHYSICAL_ASSERT_NUM_ROWS, groupExpression, logicalProperties, child);
+ this.assertNumRowsElement = assertNumRowsElement;
}
@Override
@@ -58,9 +58,14 @@ public class LogicalEnforceSingleRow<CHILD_TYPE extends Plan> extends LogicalUna
.build();
}
+ public AssertNumRowsElement getAssertNumRowsElement() {
+ return assertNumRowsElement;
+ }
+
@Override
public String toString() {
- return Utils.toSqlString("LogicalEnforceSingleRow");
+ return Utils.toSqlString("PhysicalAssertNumRows",
+ "assertNumRowsElement", assertNumRowsElement);
}
@Override
@@ -71,32 +76,39 @@ public class LogicalEnforceSingleRow<CHILD_TYPE extends Plan> extends LogicalUna
if (o == null || getClass() != o.getClass()) {
return false;
}
- return true;
+
+ PhysicalAssertNumRows that = (PhysicalAssertNumRows) o;
+ return assertNumRowsElement.equals(that.assertNumRowsElement);
}
@Override
- public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
- return visitor.visitLogicalEnforceSingleRow((LogicalEnforceSingleRow<Plan>) this, context);
+ public int hashCode() {
+ return Objects.hash(assertNumRowsElement);
}
@Override
- public Plan withChildren(List<Plan> children) {
- Preconditions.checkArgument(children.size() == 1);
- return new LogicalEnforceSingleRow<>(children.get(0));
+ public List<Expression> getExpressions() {
+ return ImmutableList.of(assertNumRowsElement);
}
@Override
- public List<Expression> getExpressions() {
- return ImmutableList.of();
+ public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
+ return visitor.visitPhysicalAssertNumRows((PhysicalAssertNumRows<Plan>) this, context);
+ }
+
+ @Override
+ public PhysicalUnary<Plan> withChildren(List<Plan> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new PhysicalAssertNumRows<>(assertNumRowsElement, logicalProperties, children.get(0));
}
@Override
public Plan withGroupExpression(Optional<GroupExpression> groupExpression) {
- return new LogicalEnforceSingleRow<>(groupExpression, Optional.of(logicalProperties), child());
+ return new PhysicalAssertNumRows<>(assertNumRowsElement, groupExpression, logicalProperties, child());
}
@Override
public Plan withLogicalProperties(Optional<LogicalProperties> logicalProperties) {
- return new LogicalEnforceSingleRow<>(Optional.empty(), logicalProperties, child());
+ return new PhysicalAssertNumRows<>(assertNumRowsElement, Optional.empty(), logicalProperties.get(), child());
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/PlanVisitor.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/PlanVisitor.java
index cc5d914c1a..965fda7def 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/PlanVisitor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/PlanVisitor.java
@@ -24,8 +24,7 @@ import org.apache.doris.nereids.trees.plans.commands.Command;
import org.apache.doris.nereids.trees.plans.commands.ExplainCommand;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
-import org.apache.doris.nereids.trees.plans.logical.LogicalCorrelatedJoin;
-import org.apache.doris.nereids.trees.plans.logical.LogicalEnforceSingleRow;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
@@ -38,6 +37,7 @@ import org.apache.doris.nereids.trees.plans.logical.LogicalSubQueryAlias;
import org.apache.doris.nereids.trees.plans.logical.LogicalTopN;
import org.apache.doris.nereids.trees.plans.physical.AbstractPhysicalSort;
import org.apache.doris.nereids.trees.plans.physical.PhysicalAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.physical.PhysicalDistribution;
import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter;
import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
@@ -132,12 +132,8 @@ public abstract class PlanVisitor<R, C> {
return visit(apply, context);
}
- public R visitLogicalCorrelated(LogicalCorrelatedJoin<Plan, Plan> correlatedJoin, C context) {
- return visit(correlatedJoin, context);
- }
-
- public R visitLogicalEnforceSingleRow(LogicalEnforceSingleRow<Plan> enforceSingleRow, C context) {
- return visit(enforceSingleRow, context);
+ public R visitLogicalAssertNumRows(LogicalAssertNumRows<Plan> assertNumRows, C context) {
+ return visit(assertNumRows, context);
}
// *******************************
@@ -191,4 +187,8 @@ public abstract class PlanVisitor<R, C> {
public R visitPhysicalDistribution(PhysicalDistribution<Plan> distribution, C context) {
return visit(distribution, context);
}
+
+ public R visitPhysicalAssertNumRows(PhysicalAssertNumRows<Plan> assertNumRows, C context) {
+ return visit(assertNumRows, context);
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/Utils.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/Utils.java
index 02033f4e28..049619562b 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/Utils.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/Utils.java
@@ -17,15 +17,20 @@
package org.apache.doris.nereids.util;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.plans.Plan;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import org.apache.commons.lang3.StringUtils;
+import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
+import java.util.Map;
import java.util.stream.Collectors;
/**
@@ -146,4 +151,58 @@ public class Utils {
return stringBuilder.append(" )").toString();
}
+
+ /**
+ * See if there are correlated columns in a subquery expression.
+ */
+ public static boolean containCorrelatedSlot(List<Expression> correlatedSlots, Expression expr) {
+ if (correlatedSlots.isEmpty() || expr == null) {
+ return false;
+ }
+ if (expr instanceof SlotReference) {
+ return correlatedSlots.contains(expr);
+ }
+ for (Expression child : expr.children()) {
+ if (containCorrelatedSlot(correlatedSlots, child)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ /**
+ * Get the correlated columns that belong to the subquery,
+ * that is, the correlated columns that can be resolved within the subquery.
+ * eg:
+ * select * from t1 where t1.a = (select sum(t2.b) from t2 where t1.c = t2.d));
+ * correlatedPredicates : t1.c = t2.d
+ * correlatedSlots : t1.c
+ * return t2.d
+ */
+ public static List<Expression> getCorrelatedSlots(List<Expression> correlatedPredicates,
+ List<Expression> correlatedSlots) {
+ List<Expression> slots = new ArrayList<>();
+ correlatedPredicates.stream().forEach(predicate -> {
+ if (!(predicate instanceof BinaryExpression)) {
+ throw new AnalysisException("UnSupported expr type: " + correlatedPredicates);
+ }
+ BinaryExpression binaryExpression = (BinaryExpression) predicate;
+ if (binaryExpression.left().anyMatch(correlatedSlots::contains)) {
+ if (binaryExpression.right() instanceof SlotReference) {
+ slots.add(binaryExpression.right());
+ }
+ } else {
+ if (binaryExpression.left() instanceof SlotReference) {
+ slots.add(binaryExpression.left());
+ }
+ }
+ });
+ return slots;
+ }
+
+ public static Map<Boolean, List<Expression>> splitCorrelatedConjuncts(
+ List<Expression> conjuncts, List<Expression> slots) {
+ return conjuncts.stream().collect(Collectors.partitioningBy(
+ expr -> expr.anyMatch(slots::contains)));
+ }
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java
index ef082e48c5..1d4053bb58 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java
@@ -82,7 +82,7 @@ public class SubqueryTest extends AnalyzeCheckTestBase {
}
@Test
- public void scalarTest() {
+ public void filterScalarTest() {
// min will be rewritten as as, it needs to be bound
/*String sql = "select * from t0 where t0.id = "
+ "(select min(t1.id) from t1 where t0.k1 = t1.k1)";
@@ -94,19 +94,35 @@ public class SubqueryTest extends AnalyzeCheckTestBase {
assert sql1 != null;
String sql2 = "select * from t0 where t0.id = "
- + "(select t1.k1 from t1 where t0.k1 = t1.k1)";
+ + "(select sum(t1.k1) from t1 where t0.k1 = t1.k1)";
checkAnalyze(sql2);
String sql3 = "select * from t0 where t0.id = "
+ "(select * from t1 where t0.k1 = t1.k1)";
assert sql3 != null;
+
+ String sql4 = "select * from t0 where t0.id = "
+ + "(select sum(t1.k1) from t1 where t0.k1 = t1.k1) "
+ + "and t0.k2 = (select sum(t2.k1) from t2 where t0.v1 = t2.v2)";
+ checkAnalyze(sql4);
+
+ String sql5 = "select * from t0 where t0.id = "
+ + "(select sum(t1.k1) from t1 where t0.k1 = "
+ + "(select sum(t2.k1) from t2 where t1.id = t2.v2))";
+ checkAnalyze(sql5);
}
@Test
public void inScalarTest() {
String sql = "select * from t0 where t0.id in "
- + "(select * from t1 where t1.k1 = "
- + "(select t2.id from t2 where t0.id = t2.id));";
+ + "(select t1.k1 from t1 where t1.k1 = "
+ + "(select sum(t2.id) from t2 where t1.id = t2.id));";
checkAnalyze(sql);
+
+ String sql1 = "select * from t0 where t0.k1 in ("
+ + "select t1.k1 from t1 where "
+ + "t1.v2 in (select t2.k1 from t2 where t2.k1 = 3) "
+ + "and t1.v2 > (select sum(t2.k1) from t2 where t2.v2 = t1.id))";
+ checkAnalyze(sql1);
}
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/AnalyzeWhereSubqueryTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/AnalyzeWhereSubqueryTest.java
new file mode 100644
index 0000000000..ba90de7c94
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/AnalyzeWhereSubqueryTest.java
@@ -0,0 +1,451 @@
+// 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.
+
+package org.apache.doris.nereids.util;
+
+import org.apache.doris.nereids.NereidsPlanner;
+import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.glue.translator.PhysicalPlanTranslator;
+import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
+import org.apache.doris.nereids.parser.NereidsParser;
+import org.apache.doris.nereids.properties.PhysicalProperties;
+import org.apache.doris.nereids.rules.rewrite.logical.ApplyPullFilterOnAgg;
+import org.apache.doris.nereids.rules.rewrite.logical.ApplyPullFilterOnProjectUnderAgg;
+import org.apache.doris.nereids.rules.rewrite.logical.ExistsApplyToJoin;
+import org.apache.doris.nereids.rules.rewrite.logical.InApplyToJoin;
+import org.apache.doris.nereids.rules.rewrite.logical.PushApplyUnderFilter;
+import org.apache.doris.nereids.rules.rewrite.logical.PushApplyUnderProject;
+import org.apache.doris.nereids.rules.rewrite.logical.ScalarApplyToJoin;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.And;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.NamedExpressionUtil;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.Max;
+import org.apache.doris.nereids.trees.expressions.functions.Sum;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalPlan;
+import org.apache.doris.nereids.types.BigIntType;
+import org.apache.doris.nereids.types.VarcharType;
+import org.apache.doris.utframe.TestWithFeService;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.jupiter.api.Test;
+
+import java.util.List;
+import java.util.Optional;
+
+public class AnalyzeWhereSubqueryTest extends TestWithFeService implements PatternMatchSupported {
+ private final NereidsParser parser = new NereidsParser();
+
+ // scalar
+ private final String sql1 = "select * from t6 where t6.k1 < (select sum(t7.k3) from t7 where t7.v2 = t6.k2)";
+ private final String sql2 = "select * from t6 where t6.k1 = (select sum(t7.k3) from t7 where t7.v2 = t6.k2)";
+ private final String sql3 = "select * from t6 where t6.k1 != (select sum(t7.k3) from t7 where t7.v2 = t6.k2)";
+ // in
+ private final String sql4 = "select * from t6 where t6.k1 in (select t7.k3 from t7 where t7.v2 = t6.k2)";
+ private final String sql5 = "select * from t6 where t6.k1 not in (select t7.k3 from t7 where t7.v2 = t6.k2)";
+ // exists
+ private final String sql6 = "select * from t6 where exists (select t7.k3 from t7 where t6.k2 = t7.v2)";
+ private final String sql7 = "select * from t6 where not exists (select t7.k3 from t7 where t6.k2 = t7.v2)";
+ // in (in and scalar)
+ private final String sql8 = "select * from t6 where t6.k1 in ("
+ + "select t7.k3 from t7 where "
+ + "t7.k3 in (select t8.k1 from t8 where t8.k1 = 3) "
+ + "and t7.v2 > (select sum(t9.k2) from t9 where t9.k2 = t7.v1))";
+ // exists and not exists
+ private final String sql9 = "select * from t6 where exists (select t7.k3 from t7 where t6.k2 = t7.v2) and not exists (select t8.k2 from t8 where t6.k2 = t8.k2)";
+ // with subquery alias
+ private final String sql10 = "select * from t6 where t6.k1 < (select max(aa) from (select v1 as aa from t7 where t6.k2=t7.v2) t2 )";
+ private final List<String> testSql = ImmutableList.of(
+ sql1, sql2, sql3, sql4, sql5, sql6, sql7, sql8, sql9, sql10
+
+ );
+
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("default_cluster:test");
+
+ createTables(
+ "create table test.t6\n"
+ + "(k1 bigint, k2 bigint)\n"
+ + "duplicate key(k1)\n"
+ + "distributed by hash(k2) buckets 1\n"
+ + "properties('replication_num' = '1');",
+ "create table test.t7\n"
+ + "(k1 bigint not null, k2 varchar(128), k3 bigint, v1 bigint, v2 bigint)\n"
+ + "distributed by hash(k2) buckets 1\n"
+ + "properties('replication_num' = '1');",
+ "create table test.t8\n"
+ + "(k1 int, k2 int)\n"
+ + "duplicate key(k1)\n"
+ + "distributed by hash(k2) buckets 1\n"
+ + "properties('replication_num' = '1');",
+ "create table test.t9\n"
+ + "(k1 varchar(40), k2 bigint)\n"
+ + "partition by range(k2)\n"
+ + "(partition p1 values less than(\"10\"))\n"
+ + "distributed by hash(k2) buckets 1\n"
+ + "properties('replication_num' = '1');");
+ }
+
+ @Override
+ protected void runBeforeEach() throws Exception {
+ NamedExpressionUtil.clear();
+ }
+
+ @Test
+ public void testTranslateCase() throws Exception {
+ for (String sql : testSql) {
+ NamedExpressionUtil.clear();
+ StatementContext statementContext = MemoTestUtils.createStatementContext(connectContext, sql);
+ PhysicalPlan plan = new NereidsPlanner(statementContext).plan(
+ parser.parseSingle(sql),
+ PhysicalProperties.ANY
+ );
+ // Just to check whether translate will throw exception
+ new PhysicalPlanTranslator().translatePlan(plan, new PlanTranslatorContext());
+ }
+ }
+
+ @Test
+ public void testWhereSql2AfterAnalyzed() {
+ //after analyze
+ PlanChecker.from(connectContext)
+ .analyze(sql2)
+ .matches(
+ logicalApply(
+ any(),
+ logicalAggregate(
+ logicalFilter()
+ ).when(FieldChecker.check("outputExpressions", ImmutableList.of(
+ new Alias(new ExprId(7),
+ new Sum(
+ new SlotReference(new ExprId(4), "k3",
+ BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))),
+ "sum(k3)"))))
+ ).when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))
+ )))
+ );
+ }
+
+ @Test
+ public void testWhereSql2AfterAggFilterRule() {
+ //after aggFilter rule
+ PlanChecker.from(connectContext)
+ .analyze(sql2)
+ .applyBottomUp(new ApplyPullFilterOnAgg())
+ .matches(
+ logicalApply(
+ any(),
+ logicalAggregate().when(FieldChecker.check("outputExpressions", ImmutableList.of(
+ new Alias(new ExprId(7), new Sum(
+ new SlotReference(new ExprId(4), "k3", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))),
+ "sum(k3)"),
+ new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))
+ )))
+ .when(FieldChecker.check("groupByExpressions", ImmutableList.of(
+ new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))
+ )))
+ ).when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))
+ )))
+ .when(FieldChecker.check("correlationFilter", Optional.of(
+ new EqualTo(new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))
+ ))))
+ );
+ }
+
+ @Test
+ public void testWhereSql2AfterScalarToJoin() {
+ //after Scalar CorrelatedJoin to join
+ PlanChecker.from(connectContext)
+ .analyze(sql2)
+ .applyBottomUp(new ApplyPullFilterOnAgg())
+ .applyBottomUp(new ScalarApplyToJoin())
+ .matches(
+ logicalJoin(
+ any(),
+ logicalAggregate()
+ ).when(FieldChecker.check("joinType", JoinType.LEFT_OUTER_JOIN))
+ .when(FieldChecker.check("otherJoinCondition",
+ Optional.of(new EqualTo(new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))))))
+ );
+ }
+
+ @Test
+ public void testInSql4AfterAnalyze() {
+ //"select * from t6 where t6.k1 in (select t7.k3 from t7 where t7.v2 = t6.k2)"
+ PlanChecker.from(connectContext)
+ .analyze(sql4)
+ .matches(
+ logicalApply(
+ any(),
+ logicalProject().when(FieldChecker.check("projects", ImmutableList.of(
+ new SlotReference(new ExprId(4), "k3", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))))
+ )
+ ).when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ );
+ }
+
+ @Test
+ public void testInSql4AfterPushProjectRule() {
+ PlanChecker.from(connectContext)
+ .analyze(sql4)
+ .applyBottomUp(new PushApplyUnderProject())
+ .matches(
+ logicalProject(
+ logicalApply().when(FieldChecker.check("correlationFilter", Optional.empty()))
+ .when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ ).when(FieldChecker.check("projects", ImmutableList.of(
+ new SlotReference(new ExprId(0), "k1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ );
+ }
+
+ @Test
+ public void testInSql4AfterPushFilterRule() {
+ PlanChecker.from(connectContext)
+ .analyze(sql4)
+ .applyBottomUp(new PushApplyUnderProject())
+ .applyBottomUp(new PushApplyUnderFilter())
+ .matches(
+ logicalApply().when(FieldChecker.check("correlationFilter", Optional.of(
+ new EqualTo(new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))))))
+ );
+ }
+
+ @Test
+ public void testInSql4AfterInToJoin() {
+ PlanChecker.from(connectContext)
+ .analyze(sql4)
+ .applyBottomUp(new PushApplyUnderProject())
+ .applyBottomUp(new PushApplyUnderFilter())
+ .applyBottomUp(new InApplyToJoin())
+ .matches(
+ logicalJoin().when(FieldChecker.check("joinType", JoinType.LEFT_SEMI_JOIN))
+ .when(FieldChecker.check("otherJoinCondition", Optional.of(
+ new And(new EqualTo(new SlotReference(new ExprId(0), "k1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(2), "k1", BigIntType.INSTANCE, false,
+ ImmutableList.of("default_cluster:test", "t7"))),
+ new EqualTo(new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6"))))
+ )))
+ );
+ }
+
+ @Test
+ public void testExistSql6AfterAnalyze() {
+ //"select * from t6 where exists (select t7.k3 from t7 where t6.k2 = t7.v2)"
+ PlanChecker.from(connectContext)
+ .analyze(sql6)
+ .matches(
+ logicalApply(
+ any(),
+ logicalProject().when(FieldChecker.check("projects", ImmutableList.of(
+ new SlotReference(new ExprId(4), "k3", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))))
+ )
+ ).when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ );
+ }
+
+ @Test
+ public void testExistSql6AfterPushProjectRule() {
+ PlanChecker.from(connectContext)
+ .analyze(sql6)
+ .applyBottomUp(new PushApplyUnderProject())
+ .matches(
+ logicalProject(
+ logicalApply().when(FieldChecker.check("correlationFilter", Optional.empty()))
+ .when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ ).when(FieldChecker.check("projects", ImmutableList.of(
+ new SlotReference(new ExprId(0), "k1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ );
+ }
+
+ @Test
+ public void testExistSql6AfterPushFilterRule() {
+ PlanChecker.from(connectContext)
+ .analyze(sql6)
+ .applyBottomUp(new PushApplyUnderProject())
+ .applyBottomUp(new PushApplyUnderFilter())
+ .matches(
+ logicalApply().when(FieldChecker.check("correlationFilter", Optional.of(
+ new EqualTo(new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))))))
+ );
+ }
+
+ @Test
+ public void testExistSql6AfterInToJoin() {
+ PlanChecker.from(connectContext)
+ .analyze(sql6)
+ .applyBottomUp(new PushApplyUnderProject())
+ .applyBottomUp(new PushApplyUnderFilter())
+ .applyBottomUp(new ExistsApplyToJoin())
+ .matches(
+ logicalJoin().when(FieldChecker.check("joinType", JoinType.LEFT_SEMI_JOIN))
+ .when(FieldChecker.check("otherJoinCondition", Optional.of(
+ new EqualTo(new SlotReference(new ExprId(1), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(6), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")))
+ )))
+ );
+ }
+
+ @Test
+ public void testSql10AfterAnalyze() {
+ //select * from t6 where t6.k1 < (select max(aa) from (select v1 as aa from t7 where t6.k2=t7.v2) t2 )
+ PlanChecker.from(connectContext)
+ .analyze(sql10)
+ .matches(
+ logicalApply(
+ any(),
+ logicalAggregate(
+ logicalProject(
+ logicalFilter()
+ ).when(FieldChecker.check("projects", ImmutableList.of(
+ new Alias(new ExprId(0), new SlotReference(new ExprId(6), "v1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")), "aa")
+ )))
+ ).when(FieldChecker.check("outputExpressions", ImmutableList.of(
+ new Alias(new ExprId(8), new Max(new SlotReference(new ExprId(0), "aa", BigIntType.INSTANCE, true,
+ ImmutableList.of("t2"))), "max(aa)")
+ )))
+ .when(FieldChecker.check("groupByExpressions", ImmutableList.of()))
+ ).when(FieldChecker.check("correlationSlot", ImmutableList.of(
+ new SlotReference(new ExprId(2), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")))))
+ );
+ }
+
+ @Test
+ public void testSql10AfterChangeProjectFilter() {
+ PlanChecker.from(connectContext)
+ .analyze(sql10)
+ .applyBottomUp(new ApplyPullFilterOnProjectUnderAgg())
+ .matches(
+ logicalApply(
+ any(),
+ logicalAggregate(
+ logicalFilter(
+ logicalProject().when(FieldChecker.check("projects", ImmutableList.of(
+ new Alias(new ExprId(0), new SlotReference(new ExprId(6), "v1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")), "aa"),
+ new SlotReference(new ExprId(3), "k1", BigIntType.INSTANCE, false,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(4), "k2", new VarcharType(128), true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(5), "k3", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(6), "v1", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")),
+ new SlotReference(new ExprId(7), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))
+ )))
+ )
+ )
+ )
+ );
+ }
+
+ @Test
+ public void testSql10AfterChangeAggFilter() {
+ PlanChecker.from(connectContext)
+ .analyze(sql10)
+ .applyBottomUp(new ApplyPullFilterOnProjectUnderAgg())
+ .applyBottomUp(new ApplyPullFilterOnAgg())
+ .matches(
+ logicalApply(
+ any(),
+ logicalAggregate(
+ logicalProject()
+ ).when(FieldChecker.check("outputExpressions", ImmutableList.of(
+ new Alias(new ExprId(8), new Max(new SlotReference(new ExprId(0), "aa", BigIntType.INSTANCE, true,
+ ImmutableList.of("t2"))), "max(aa)"),
+ new SlotReference(new ExprId(7), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")))))
+ .when(FieldChecker.check("groupByExpressions", ImmutableList.of(
+ new SlotReference(new ExprId(7), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7"))
+ )))
+ )
+ );
+ }
+
+ @Test
+ public void testSql10AfterScalarTOJoin() {
+ PlanChecker.from(connectContext)
+ .analyze(sql10)
+ .applyBottomUp(new ApplyPullFilterOnProjectUnderAgg())
+ .applyBottomUp(new ApplyPullFilterOnAgg())
+ .applyBottomUp(new ScalarApplyToJoin())
+ .matches(
+ logicalJoin(
+ any(),
+ logicalAggregate(
+ logicalProject()
+ )
+ ).when(FieldChecker.check("joinType", JoinType.LEFT_OUTER_JOIN))
+ .when(FieldChecker.check("otherJoinCondition", Optional.of(
+ new EqualTo(new SlotReference(new ExprId(2), "k2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t6")),
+ new SlotReference(new ExprId(7), "v2", BigIntType.INSTANCE, true,
+ ImmutableList.of("default_cluster:test", "t7")))
+ )))
+ );
+ }
+}
diff --git a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
new file mode 100644
index 0000000000..038c47c53d
--- /dev/null
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
@@ -0,0 +1,243 @@
+// 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 ("sub_query_correlated") {
+ // enable nereids and vectorized engine
+ sql """
+ SET enable_vectorized_engine=true
+ """
+ sql """
+ SET enable_nereids_planner=true
+ """
+
+ sql """
+ DROP TABLE IF EXISTS `subquery1`
+ """
+
+ sql """
+ DROP TABLE IF EXISTS `subquery2`
+ """
+
+ sql """
+ DROP TABLE IF EXISTS `subquery3`
+ """
+
+ sql """
+ DROP TABLE IF EXISTS `subquery4`
+ """
+
+ sql """
+ create table subquery1
+ (k1 bigint, k2 bigint)
+ duplicate key(k1)
+ distributed by hash(k2) buckets 1
+ properties('replication_num' = '1')
+ """
+
+ sql """
+ create table subquery2
+ (k1 varchar(10), k2 bigint)
+ partition by range(k2)
+ (partition p1 values less than("10"))
+ distributed by hash(k2) buckets 1
+ properties('replication_num' = '1')
+ """
+
+ sql """
+ create table subquery3
+ (k1 int not null, k2 varchar(128), k3 bigint, v1 bigint, v2 bigint)
+ distributed by hash(k2) buckets 1
+ properties('replication_num' = '1')
+ """
+
+ sql """
+ create table subquery4
+ (k1 bigint, k2 bigint)
+ duplicate key(k1)
+ distributed by hash(k2) buckets 1
+ properties('replication_num' = '1')
+ """
+
+ sql """
+ insert into subquery1 values (1,2), (1,3), (2,4), (2,5), (3,3), (3,4), (20,2), (22,3), (24,4)
+ """
+
+ sql """
+ insert into subquery2 values ("abc",2),("abc",3),("abcd",2),("abcde",4),("abcdef",5)
+ """
+
+ sql """
+ insert into subquery3 values (1,"abc",2,3,4), (1,"abcd",3,3,4), (2,"xyz",2,4,2),
+ (2,"uvw",3,4,2), (2,"uvw",3,4,2), (3,"abc",4,5,3), (3,"abc",4,5,3)
+ """
+
+ sql """
+ insert into subquery4 values (5,4), (5,2), (8,3), (5,4), (6,7), (8,9)
+ """
+
+ // The query result is not necessarily correct, because there are some problems in the current new optimizer,
+ // just verify that the subquery de-nesting function can support the following scenarios,
+ // and the out file will be updated later
+
+ // unstable
+ //qt_scalar_less_than_corr """
+ sql """
+ select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2)
+ """
+
+ //qt_scalar_not_equal_corr """
+ sql """
+ select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2)
+ """
+
+ //qt_scalar_not_equal_uncorr """
+ /*sql """
+ select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3)
+ """*/
+
+ //qt_scalar_equal_to_corr """
+ sql """
+ select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2)
+ """
+
+ //qt_scalar_equal_to_uncorr """
+ /*sql """
+ select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3) from subquery3)
+ """*/
+
+ //qt_not_in_corr """
+ sql """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3 where subquery3.v2 = subquery1.k2)
+ """
+
+ //qt_not_in_uncorr """
+ sql """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3)
+ """
+
+ //qt_in_subquery_corr """
+ sql """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3 where subquery3.v2 = subquery1.k2)
+ """
+
+ //qt_in_subquery_uncorr """
+ sql """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3)
+ """
+
+ //qt_not_exist_corr """
+ sql """
+ select * from subquery1 where not exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2)
+ """
+
+ //qt_not_exist_uncorr """
+ /*sql """
+ select * from subquery1 where not exists (select subquery3.k3 from subquery3)
+ """*/
+
+ //qt_exist_corr """
+ sql """
+ select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2)
+ """
+
+ //qt_exist_uncorr """
+ /*sql """
+ select * from subquery1 where exists (select subquery3.k3 from subquery3)
+ """*/
+
+ //qt_in_with_in_and_scalar """
+ sql """
+ select * from subquery1 where subquery1.k1 in (
+ select subquery3.k3 from subquery3 where
+ subquery3.k3 in (select subquery4.k1 from subquery4 where subquery4.k1 = 3)
+ and subquery3.v2 > (select sum(subquery2.k2) from subquery2 where subquery2.k2 = subquery3.v1))
+ """
+
+ //qt_exist_and_not_exist """
+ sql """
+ select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2)
+ and not exists (select subquery4.k2 from subquery4 where subquery1.k2 = subquery4.k2)
+ """
+
+ //------------------unCorrelated-----------------
+ //qt_scalar_unCorrelated
+ /*sql """
+ select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3) from subquery3 where subquery3.v2 = 2)
+ """*/
+
+ //qt_not_scalar_unCorrelated
+ /*sql """
+ select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3 where subquery3.v2 = 2);
+ """*/
+
+ //qt_in_unCorrelated
+ sql """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3 where subquery3.v2 = 2);
+ """
+
+ //qt_not_in_unCorrelated
+ sql """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3 where subquery3.v2 = 2);
+ """
+
+ //qt_exist_unCorrelated
+ /*sql """
+ select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery3.v2 = 2);
+ """*/
+
+ //qt_not_exists_unCorrelated
+ /*sql """
+ select * from subquery1 where not exists (select subquery3.k3 from subquery3 where subquery3.v2 = 2);
+ """*/
+
+ //----------with subquery alias----------
+ //qt_scalar
+ //Open after the project is completed
+ //sql """
+ // select * from subquery1
+ // where subquery1.k1 < (select max(aa) from
+ // (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3)
+ //"""
+
+ //qt_in
+ sql """
+ select * from subquery1
+ where subquery1.k1 in (select aa from
+ (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3)
+ """
+
+ //qt_not_in
+ sql """
+ select * from subquery1
+ where subquery1.k1 not in (select aa from
+ (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3)
+ """
+
+ //qt_exist
+ sql """
+ select * from subquery1
+ where exists (select aa from
+ (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3)
+ """
+
+ //qt_not_exist
+ sql """
+ select * from subquery1
+ where not exists (select aa from
+ (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3)
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org