This is an automated email from the ASF dual-hosted git repository.
starocean999 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 fbd514c100f [enhancement](nereids)remove subqueryExpr member from
LogicalApply (#50378)
fbd514c100f is described below
commit fbd514c100f74740a874398d5e69f1cc8c945a82
Author: starocean999 <[email protected]>
AuthorDate: Fri Apr 25 18:01:52 2025 +0800
[enhancement](nereids)remove subqueryExpr member from LogicalApply (#50378)
---
.../nereids/rules/analysis/SubqueryToApply.java | 18 +-
.../nereids/rules/rewrite/ExistsApplyToJoin.java | 5 +-
.../doris/nereids/rules/rewrite/InApplyToJoin.java | 21 +-
.../nereids/rules/rewrite/PullUpCteAnchor.java | 24 +-
.../rules/rewrite/PullUpProjectUnderApply.java | 3 +-
.../nereids/rules/rewrite/ScalarApplyToJoin.java | 2 +-
.../rewrite/UnCorrelatedApplyAggregateFilter.java | 3 +-
.../rules/rewrite/UnCorrelatedApplyFilter.java | 3 +-
.../rewrite/UnCorrelatedApplyProjectFilter.java | 3 +-
.../trees/copier/LogicalPlanDeepCopier.java | 10 +-
.../nereids/trees/plans/logical/LogicalApply.java | 104 ++-
.../rules/rewrite/ExistsApplyToJoinTest.java | 17 +-
.../data/nereids_p0/test_cte_subquery_many.out | Bin 0 -> 117 bytes
.../nereids_p0/test_cte_subquery_many.groovy | 756 +++++++++++++++++++++
14 files changed, 883 insertions(+), 86 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
index 4bb55eab9d4..22e2c0fca99 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
@@ -20,6 +20,7 @@ package org.apache.doris.nereids.rules.analysis;
import org.apache.doris.common.Pair;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
@@ -471,9 +472,24 @@ public class SubqueryToApply implements
AnalysisRuleFactory {
needRuntimeAssertCount = true;
}
}
+ LogicalApply.SubQueryType subQueryType;
+ boolean isNot = false;
+ Optional<Expression> compareExpr = Optional.empty();
+ if (subquery instanceof InSubquery) {
+ subQueryType = LogicalApply.SubQueryType.IN_SUBQUERY;
+ isNot = ((InSubquery) subquery).isNot();
+ compareExpr = Optional.of(((InSubquery)
subquery).getCompareExpr());
+ } else if (subquery instanceof Exists) {
+ subQueryType = LogicalApply.SubQueryType.EXITS_SUBQUERY;
+ isNot = ((Exists) subquery).isNot();
+ } else if (subquery instanceof ScalarSubquery) {
+ subQueryType = LogicalApply.SubQueryType.SCALAR_SUBQUERY;
+ } else {
+ throw new AnalysisException(String.format("Unsupported subquery :
%s", subquery.toString()));
+ }
LogicalApply newApply = new LogicalApply(
subquery.getCorrelateSlots(),
- subquery, Optional.empty(),
+ subQueryType, isNot, compareExpr,
subquery.getTypeCoercionExpr(), Optional.empty(),
markJoinSlot,
needAddScalarSubqueryOutputToProjects, isProject,
isMarkJoinSlotNotNull,
childPlan, subquery.getQueryPlan());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoin.java
index 930e4c467df..68383324856 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoin.java
@@ -22,7 +22,6 @@ 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.EqualTo;
-import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.agg.Count;
import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
@@ -94,7 +93,7 @@ public class ExistsApplyToJoin extends OneRewriteRuleFactory {
private Plan correlatedToJoin(LogicalApply<?, ?> apply) {
Optional<Expression> correlationFilter = apply.getCorrelationFilter();
- if (((Exists) apply.getSubqueryExpr()).isNot()) {
+ if (apply.isNot()) {
return new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN,
ExpressionUtils.EMPTY_CONDITION,
correlationFilter.map(ExpressionUtils::extractConjunction).orElse(ExpressionUtils.EMPTY_CONDITION),
new DistributeHint(DistributeType.NONE),
@@ -110,7 +109,7 @@ public class ExistsApplyToJoin extends
OneRewriteRuleFactory {
}
private Plan unCorrelatedToJoin(LogicalApply<?, ?> unapply) {
- if (((Exists) unapply.getSubqueryExpr()).isNot()) {
+ if (unapply.isNot()) {
return unCorrelatedNotExist(unapply);
} else {
return unCorrelatedExist(unapply);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InApplyToJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InApplyToJoin.java
index 67276e92bb3..a004b7fc569 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InApplyToJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InApplyToJoin.java
@@ -24,7 +24,6 @@ import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.Alias;
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.expressions.IsNull;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Not;
@@ -36,7 +35,6 @@ 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.LogicalJoin;
-import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.ImmutableList;
@@ -82,9 +80,9 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
List<NamedExpression> outputExpressions =
Lists.newArrayList(alias);
LogicalAggregate agg = new LogicalAggregate(groupExpressions,
outputExpressions, apply.right());
- Expression compareExpr = ((InSubquery)
apply.getSubqueryExpr()).getCompareExpr();
+ Expression compareExpr = apply.getCompareExpr().get();
Expression expr = new BitmapContains(agg.getOutput().get(0),
compareExpr);
- if (((InSubquery) apply.getSubqueryExpr()).isNot()) {
+ if (apply.isNot()) {
expr = new Not(expr);
}
return new LogicalJoin<>(JoinType.LEFT_SEMI_JOIN,
Lists.newArrayList(),
@@ -95,19 +93,18 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
}
//in-predicate to equal
- InSubquery inSubquery = ((InSubquery) apply.getSubqueryExpr());
Expression predicate;
- Expression left = inSubquery.getCompareExpr();
+ Expression left = apply.getCompareExpr().get();
// TODO: trick here, because when deep copy logical plan the apply
right child
// is not same with query plan in subquery expr, since the scan
node copy twice
- Expression right = inSubquery.getSubqueryOutput((LogicalPlan)
apply.right());
+ Expression right = apply.getSubqueryOutput();
if (apply.isMarkJoin()) {
List<Expression> joinConjuncts =
apply.getCorrelationFilter().isPresent()
?
ExpressionUtils.extractConjunction(apply.getCorrelationFilter().get())
: Lists.newArrayList();
predicate = new EqualTo(left, right);
List<Expression> markConjuncts = Lists.newArrayList(predicate);
- if (!predicate.nullable() || (apply.isMarkJoinSlotNotNull() &&
!inSubquery.isNot())) {
+ if (!predicate.nullable() || (apply.isMarkJoinSlotNotNull() &&
!apply.isNot())) {
// we can merge mark conjuncts with hash conjuncts in 2
scenarios
// 1. the mark join predicate is not nullable, so no null
value would be produced
// 2. semi join with non-nullable mark slot.
@@ -117,7 +114,7 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
markConjuncts.clear();
}
return new LogicalJoin<>(
- inSubquery.isNot() ? JoinType.LEFT_ANTI_JOIN :
JoinType.LEFT_SEMI_JOIN,
+ apply.isNot() ? JoinType.LEFT_ANTI_JOIN :
JoinType.LEFT_SEMI_JOIN,
Lists.newArrayList(), joinConjuncts, markConjuncts,
new DistributeHint(DistributeType.NONE),
apply.getMarkJoinSlotReference(),
apply.children(), null);
@@ -127,7 +124,7 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
// so we need check both correlated slot and correlation
filter exists
// before creating LogicalJoin node
if (apply.isCorrelated() &&
apply.getCorrelationFilter().isPresent()) {
- if (inSubquery.isNot()) {
+ if (apply.isNot()) {
predicate = ExpressionUtils.and(ExpressionUtils.or(new
EqualTo(left, right),
new IsNull(left), new IsNull(right)),
apply.getCorrelationFilter().get());
@@ -140,7 +137,7 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
}
List<Expression> conjuncts =
ExpressionUtils.extractConjunction(predicate);
- if (inSubquery.isNot()) {
+ if (apply.isNot()) {
return new LogicalJoin<>(
predicate.nullable() && !apply.isCorrelated()
? JoinType.NULL_AWARE_LEFT_ANTI_JOIN
@@ -159,6 +156,6 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
private boolean needBitmapUnion(LogicalApply<Plan, Plan> apply) {
return apply.right().getOutput().get(0).getDataType().isBitmapType()
- && !((InSubquery)
apply.getSubqueryExpr()).getCompareExpr().getDataType().isBitmapType();
+ && !apply.getCompareExpr().get().getDataType().isBitmapType();
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpCteAnchor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpCteAnchor.java
index 4bfb36db813..09b7098b933 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpCteAnchor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpCteAnchor.java
@@ -18,12 +18,10 @@
package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.nereids.jobs.JobContext;
-import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
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.LogicalCTEAnchor;
import org.apache.doris.nereids.trees.plans.logical.LogicalCTEProducer;
-import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
@@ -69,7 +67,10 @@ public class PullUpCteAnchor extends
DefaultPlanRewriter<List<LogicalCTEProducer
List<LogicalCTEProducer<Plan>> producers) {
List<LogicalCTEProducer<Plan>> childProducers = Lists.newArrayList();
Plan child = cteProducer.child().accept(this, childProducers);
- LogicalCTEProducer<Plan> newProducer = (LogicalCTEProducer<Plan>)
cteProducer.withChildren(child);
+ LogicalCTEProducer<Plan> newProducer = (LogicalCTEProducer<Plan>)
cteProducer;
+ if (child != cteProducer.child()) {
+ newProducer = (LogicalCTEProducer<Plan>)
cteProducer.withChildren(child);
+ }
// because current producer relay on it child's producers, so add
current producer first.
producers.add(newProducer);
producers.addAll(childProducers);
@@ -79,17 +80,7 @@ public class PullUpCteAnchor extends
DefaultPlanRewriter<List<LogicalCTEProducer
@Override
public Plan visitLogicalApply(LogicalApply<? extends Plan, ? extends Plan>
apply,
List<LogicalCTEProducer<Plan>> producers) {
- SubqueryExpr subqueryExpr = apply.getSubqueryExpr();
- PullUpCteAnchor pullSubqueryExpr = new PullUpCteAnchor();
- List<LogicalCTEProducer<Plan>> subqueryExprProducers =
Lists.newArrayList();
- Plan newPlanInExpr =
pullSubqueryExpr.rewriteRoot(subqueryExpr.getQueryPlan(),
subqueryExprProducers);
- while (newPlanInExpr instanceof LogicalCTEAnchor) {
- newPlanInExpr = ((LogicalCTEAnchor<?, ?>) newPlanInExpr).right();
- }
- SubqueryExpr newSubqueryExpr = subqueryExpr.withSubquery((LogicalPlan)
newPlanInExpr);
-
Plan newApplyLeft = apply.left().accept(this, producers);
-
Plan applyRight = apply.right();
PullUpCteAnchor pullApplyRight = new PullUpCteAnchor();
List<LogicalCTEProducer<Plan>> childProducers = Lists.newArrayList();
@@ -98,7 +89,10 @@ public class PullUpCteAnchor extends
DefaultPlanRewriter<List<LogicalCTEProducer
newApplyRight = ((LogicalCTEAnchor<?, ?>) newApplyRight).right();
}
producers.addAll(childProducers);
- return apply.withSubqueryExprAndChildren(newSubqueryExpr,
- ImmutableList.of(newApplyLeft, newApplyRight));
+ if (newApplyLeft != apply.left() || newApplyRight != apply.right()) {
+ return apply.withChildren(ImmutableList.of(newApplyLeft,
newApplyRight));
+ } else {
+ return apply;
+ }
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpProjectUnderApply.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpProjectUnderApply.java
index b2398ee3b56..e428e02e1e0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpProjectUnderApply.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpProjectUnderApply.java
@@ -20,7 +20,6 @@ package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
-import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
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.LogicalFilter;
@@ -60,7 +59,7 @@ public class PullUpProjectUnderApply extends
OneRewriteRuleFactory {
LogicalProject<Plan> project = apply.right();
Plan newCorrelate = apply.withChildren(apply.left(),
project.child());
List<NamedExpression> newProjects = new
ArrayList<>(apply.left().getOutput());
- if (apply.getSubqueryExpr() instanceof ScalarSubquery) {
+ if (apply.isScalar()) {
// unnest correlated scalar subquery may add count(*)
and any_value() to project list
// the previous SubqueryToApply rule will make sure of
it. So the output column
// may be 1 or 2, we add a check here.
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ScalarApplyToJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ScalarApplyToJoin.java
index 5cb11914b66..34bb5723a72 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ScalarApplyToJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ScalarApplyToJoin.java
@@ -58,7 +58,7 @@ public class ScalarApplyToJoin extends OneRewriteRuleFactory {
private Plan unCorrelatedToJoin(LogicalApply apply) {
LogicalAssertNumRows assertNumRows = new LogicalAssertNumRows<>(new
AssertNumRowsElement(1,
- apply.getSubqueryExpr().toString(),
AssertNumRowsElement.Assertion.EQ),
+ apply.right().toString(), AssertNumRowsElement.Assertion.EQ),
(LogicalPlan) apply.right());
return new LogicalJoin<>(JoinType.CROSS_JOIN,
ExpressionUtils.EMPTY_CONDITION,
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyAggregateFilter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyAggregateFilter.java
index f4acd847652..258698b1f7f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyAggregateFilter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyAggregateFilter.java
@@ -115,7 +115,8 @@ public class UnCorrelatedApplyAggregateFilter implements
RewriteRuleFactory {
correlatedPredicate = ExpressionUtils.replace(correlatedPredicate,
unCorrelatedExprToSlot);
LogicalAggregate newAgg = new LogicalAggregate<>(newGroupby,
newAggOutput,
PlanUtils.filterOrSelf(ImmutableSet.copyOf(unCorrelatedPredicate),
filter.child()));
- return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryExpr(),
+ return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryType(), apply.isNot(),
+ apply.getCompareExpr(), apply.getTypeCoercionExpr(),
ExpressionUtils.optionalAnd(correlatedPredicate),
apply.getMarkJoinSlotReference(),
apply.isNeedAddSubOutputToProjects(), apply.isInProject(),
apply.isMarkJoinSlotNotNull(), apply.left(),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyFilter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyFilter.java
index 30b5cfcef3e..b5732a604ca 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyFilter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyFilter.java
@@ -66,7 +66,8 @@ public class UnCorrelatedApplyFilter extends
OneRewriteRuleFactory {
}
Plan child =
PlanUtils.filterOrSelf(ImmutableSet.copyOf(unCorrelatedPredicate),
filter.child());
- return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryExpr(),
+ return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryType(), apply.isNot(),
+ apply.getCompareExpr(), apply.getTypeCoercionExpr(),
ExpressionUtils.optionalAnd(correlatedPredicate),
apply.getMarkJoinSlotReference(),
apply.isNeedAddSubOutputToProjects(),
apply.isInProject(), apply.isMarkJoinSlotNotNull(),
apply.left(), child);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyProjectFilter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyProjectFilter.java
index 82950e14dc7..4f31d672a16 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyProjectFilter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/UnCorrelatedApplyProjectFilter.java
@@ -87,7 +87,8 @@ public class UnCorrelatedApplyProjectFilter extends
OneRewriteRuleFactory {
.map(NamedExpression.class::cast)
.forEach(projects::add);
LogicalProject newProject =
project.withProjectsAndChild(projects, child);
- return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryExpr(),
+ return new LogicalApply<>(apply.getCorrelationSlot(),
apply.getSubqueryType(), apply.isNot(),
+ apply.getCompareExpr(),
apply.getTypeCoercionExpr(),
ExpressionUtils.optionalAnd(correlatedPredicate),
apply.getMarkJoinSlotReference(),
apply.isNeedAddSubOutputToProjects(),
apply.isInProject(),
apply.isMarkJoinSlotNotNull(), apply.left(), newProject);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/LogicalPlanDeepCopier.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/LogicalPlanDeepCopier.java
index 5d55ac04130..e95aaa4b837 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/LogicalPlanDeepCopier.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/LogicalPlanDeepCopier.java
@@ -27,7 +27,6 @@ import
org.apache.doris.nereids.trees.expressions.OrderExpression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
-import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.expressions.functions.Function;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
@@ -129,13 +128,16 @@ public class LogicalPlanDeepCopier extends
DefaultPlanRewriter<DeepCopierContext
List<Expression> correlationSlot = apply.getCorrelationSlot().stream()
.map(s -> ExpressionDeepCopier.INSTANCE.deepCopy(s, context))
.collect(ImmutableList.toImmutableList());
- SubqueryExpr subqueryExpr = (SubqueryExpr)
ExpressionDeepCopier.INSTANCE
- .deepCopy(apply.getSubqueryExpr(), context);
+ Optional<Expression> compareExpr = apply.getCompareExpr()
+ .map(f -> ExpressionDeepCopier.INSTANCE.deepCopy(f, context));
+ Optional<Expression> typeCoercionExpr = apply.getTypeCoercionExpr()
+ .map(f -> ExpressionDeepCopier.INSTANCE.deepCopy(f, context));
Optional<Expression> correlationFilter = apply.getCorrelationFilter()
.map(f -> ExpressionDeepCopier.INSTANCE.deepCopy(f, context));
Optional<MarkJoinSlotReference> markJoinSlotReference =
apply.getMarkJoinSlotReference()
.map(m -> (MarkJoinSlotReference)
ExpressionDeepCopier.INSTANCE.deepCopy(m, context));
- return new LogicalApply<>(correlationSlot, subqueryExpr,
correlationFilter,
+ return new LogicalApply<>(correlationSlot, apply.getSubqueryType(),
apply.isNot(),
+ compareExpr, typeCoercionExpr, correlationFilter,
markJoinSlotReference, apply.isNeedAddSubOutputToProjects(),
apply.isInProject(),
apply.isMarkJoinSlotNotNull(), left, right);
}
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 517048c209a..0b12e225311 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,13 +19,9 @@ 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.MarkJoinSlotReference;
-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.PropagateFuncDeps;
@@ -46,11 +42,26 @@ import java.util.Optional;
*/
public class LogicalApply<LEFT_CHILD_TYPE extends Plan, RIGHT_CHILD_TYPE
extends Plan>
extends LogicalBinary<LEFT_CHILD_TYPE, RIGHT_CHILD_TYPE> implements
PropagateFuncDeps {
+ /**
+ * SubQueryType
+ */
+ public enum SubQueryType {
+ IN_SUBQUERY,
+ EXITS_SUBQUERY,
+ SCALAR_SUBQUERY
+ }
+
+ private final SubQueryType subqueryType;
+ private final boolean isNot;
+
+ // only for InSubquery
+ private final Optional<Expression> compareExpr;
+
+ // only for InSubquery
+ private final Optional<Expression> typeCoercionExpr;
// correlation column
private final List<Expression> correlationSlot;
- // original subquery
- private final SubqueryExpr subqueryExpr;
// correlation Conjunction
private final Optional<Expression> correlationFilter;
// The slot replaced by the subquery in MarkJoin
@@ -72,16 +83,23 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan,
RIGHT_CHILD_TYPE extends
private LogicalApply(Optional<GroupExpression> groupExpression,
Optional<LogicalProperties> logicalProperties,
- List<Expression> correlationSlot,
- SubqueryExpr subqueryExpr, Optional<Expression> correlationFilter,
+ List<Expression> correlationSlot, SubQueryType subqueryType,
boolean isNot,
+ Optional<Expression> compareExpr, Optional<Expression>
typeCoercionExpr,
+ Optional<Expression> correlationFilter,
Optional<MarkJoinSlotReference> markJoinSlotReference,
boolean needAddSubOutputToProjects,
boolean inProject,
boolean isMarkJoinSlotNotNull,
LEFT_CHILD_TYPE leftChild, RIGHT_CHILD_TYPE rightChild) {
super(PlanType.LOGICAL_APPLY, groupExpression, logicalProperties,
leftChild, rightChild);
+ if (subqueryType == SubQueryType.IN_SUBQUERY) {
+ Preconditions.checkArgument(compareExpr.isPresent(), "InSubquery
must have compareExpr");
+ }
this.correlationSlot = correlationSlot == null ? ImmutableList.of() :
ImmutableList.copyOf(correlationSlot);
- this.subqueryExpr = Objects.requireNonNull(subqueryExpr, "subquery can
not be null");
+ this.subqueryType = subqueryType;
+ this.isNot = isNot;
+ this.compareExpr = compareExpr;
+ this.typeCoercionExpr = typeCoercionExpr;
this.correlationFilter = correlationFilter;
this.markJoinSlotReference = markJoinSlotReference;
this.needAddSubOutputToProjects = needAddSubOutputToProjects;
@@ -89,13 +107,26 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan,
RIGHT_CHILD_TYPE extends
this.isMarkJoinSlotNotNull = isMarkJoinSlotNotNull;
}
- public LogicalApply(List<Expression> correlationSlot, SubqueryExpr
subqueryExpr,
+ public LogicalApply(List<Expression> correlationSlot, SubQueryType
subqueryType, boolean isNot,
+ Optional<Expression> compareExpr, Optional<Expression>
typeCoercionExpr,
Optional<Expression> correlationFilter,
Optional<MarkJoinSlotReference> markJoinSlotReference,
boolean needAddSubOutputToProjects, boolean inProject, boolean
isMarkJoinSlotNotNull,
LEFT_CHILD_TYPE input, RIGHT_CHILD_TYPE subquery) {
- this(Optional.empty(), Optional.empty(), correlationSlot,
subqueryExpr, correlationFilter,
- markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull, input,
- subquery);
+ this(Optional.empty(), Optional.empty(), correlationSlot,
subqueryType, isNot, compareExpr, typeCoercionExpr,
+ correlationFilter, markJoinSlotReference,
needAddSubOutputToProjects, inProject, isMarkJoinSlotNotNull,
+ input, subquery);
+ }
+
+ public Optional<Expression> getCompareExpr() {
+ return compareExpr;
+ }
+
+ public Optional<Expression> getTypeCoercionExpr() {
+ return typeCoercionExpr;
+ }
+
+ public Expression getSubqueryOutput() {
+ return typeCoercionExpr.orElseGet(() -> right().getOutput().get(0));
}
public List<Expression> getCorrelationSlot() {
@@ -106,20 +137,24 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan,
RIGHT_CHILD_TYPE extends
return correlationFilter;
}
- public SubqueryExpr getSubqueryExpr() {
- return subqueryExpr;
- }
-
public boolean isScalar() {
- return this.subqueryExpr instanceof ScalarSubquery;
+ return subqueryType == SubQueryType.SCALAR_SUBQUERY;
}
public boolean isIn() {
- return this.subqueryExpr instanceof InSubquery;
+ return subqueryType == SubQueryType.IN_SUBQUERY;
}
public boolean isExist() {
- return this.subqueryExpr instanceof Exists;
+ return subqueryType == SubQueryType.EXITS_SUBQUERY;
+ }
+
+ public SubQueryType getSubqueryType() {
+ return subqueryType;
+ }
+
+ public boolean isNot() {
+ return isNot;
}
public boolean isCorrelated() {
@@ -181,19 +216,22 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan,
RIGHT_CHILD_TYPE extends
}
LogicalApply<?, ?> that = (LogicalApply<?, ?>) o;
return Objects.equals(correlationSlot, that.getCorrelationSlot())
- && Objects.equals(subqueryExpr, that.getSubqueryExpr())
+ && Objects.equals(subqueryType, that.subqueryType)
+ && Objects.equals(compareExpr, that.compareExpr)
+ && Objects.equals(typeCoercionExpr, that.typeCoercionExpr)
&& Objects.equals(correlationFilter,
that.getCorrelationFilter())
&& Objects.equals(markJoinSlotReference,
that.getMarkJoinSlotReference())
&& needAddSubOutputToProjects ==
that.needAddSubOutputToProjects
&& inProject == that.inProject
- && isMarkJoinSlotNotNull == that.isMarkJoinSlotNotNull;
+ && isMarkJoinSlotNotNull == that.isMarkJoinSlotNotNull
+ && isNot == that.isNot;
}
@Override
public int hashCode() {
return Objects.hash(
- correlationSlot, subqueryExpr, correlationFilter,
- markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull);
+ correlationSlot, subqueryType, compareExpr, typeCoercionExpr,
correlationFilter,
+ markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull, isNot);
}
@Override
@@ -215,33 +253,27 @@ public class LogicalApply<LEFT_CHILD_TYPE extends Plan,
RIGHT_CHILD_TYPE extends
}
}
- public LogicalApply<Plan, Plan> withSubqueryExprAndChildren(SubqueryExpr
subqueryExpr, List<Plan> children) {
- return new LogicalApply<>(correlationSlot, subqueryExpr,
correlationFilter,
- markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull,
- children.get(0), children.get(1));
- }
-
@Override
public LogicalApply<Plan, Plan> withChildren(List<Plan> children) {
Preconditions.checkArgument(children.size() == 2);
- return new LogicalApply<>(correlationSlot, subqueryExpr,
correlationFilter,
- markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull,
+ return new LogicalApply<>(correlationSlot, subqueryType, isNot,
compareExpr, typeCoercionExpr,
+ correlationFilter, markJoinSlotReference,
needAddSubOutputToProjects, inProject, isMarkJoinSlotNotNull,
children.get(0), children.get(1));
}
@Override
public Plan withGroupExpression(Optional<GroupExpression> groupExpression)
{
return new LogicalApply<>(groupExpression,
Optional.of(getLogicalProperties()),
- correlationSlot, subqueryExpr, correlationFilter,
markJoinSlotReference,
- needAddSubOutputToProjects, inProject, isMarkJoinSlotNotNull,
left(), right());
+ correlationSlot, subqueryType, isNot, compareExpr,
typeCoercionExpr, correlationFilter,
+ markJoinSlotReference, needAddSubOutputToProjects, inProject,
isMarkJoinSlotNotNull, left(), right());
}
@Override
public Plan withGroupExprLogicalPropChildren(Optional<GroupExpression>
groupExpression,
Optional<LogicalProperties> logicalProperties, List<Plan>
children) {
Preconditions.checkArgument(children.size() == 2);
- return new LogicalApply<>(groupExpression, logicalProperties,
correlationSlot, subqueryExpr,
- correlationFilter, markJoinSlotReference,
+ return new LogicalApply<>(groupExpression, logicalProperties,
correlationSlot, subqueryType, isNot,
+ compareExpr, typeCoercionExpr, correlationFilter,
markJoinSlotReference,
needAddSubOutputToProjects, inProject, isMarkJoinSlotNotNull,
children.get(0), children.get(1));
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoinTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoinTest.java
index 5f617eaacc4..597eadf7a55 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoinTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoinTest.java
@@ -18,7 +18,6 @@
package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.nereids.trees.expressions.EqualTo;
-import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.JoinType;
import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
@@ -44,10 +43,10 @@ class ExistsApplyToJoinTest implements
MemoPatternMatchSupported {
LogicalOlapScan right = PlanConstructor.newLogicalOlapScan(0, "t2", 1);
List<Slot> rightSlots = right.getOutput();
EqualTo equalTo = new EqualTo(leftSlots.get(0), rightSlots.get(0));
- Exists exists = new Exists(right, false);
LogicalApply<LogicalOlapScan, LogicalOlapScan> apply =
new LogicalApply<>(ImmutableList.of(leftSlots.get(0),
rightSlots.get(0)),
- exists, Optional.of(equalTo), Optional.empty(),
+ LogicalApply.SubQueryType.EXITS_SUBQUERY, false,
Optional.empty(), Optional.empty(),
+ Optional.of(equalTo), Optional.empty(),
false, false, false, left, right);
PlanChecker.from(MemoTestUtils.createConnectContext(), apply)
.applyTopDown(new ExistsApplyToJoin())
@@ -64,10 +63,10 @@ class ExistsApplyToJoinTest implements
MemoPatternMatchSupported {
LogicalOlapScan right = PlanConstructor.newLogicalOlapScan(0, "t2", 1);
List<Slot> rightSlots = right.getOutput();
EqualTo equalTo = new EqualTo(leftSlots.get(0), rightSlots.get(0));
- Exists exists = new Exists(right, false);
LogicalApply<LogicalOlapScan, LogicalOlapScan> apply =
new LogicalApply<>(Collections.emptyList(),
- exists, Optional.of(equalTo), Optional.empty(),
+ LogicalApply.SubQueryType.EXITS_SUBQUERY, false,
Optional.empty(), Optional.empty(),
+ Optional.of(equalTo), Optional.empty(),
false, false, false, left, right);
PlanChecker.from(MemoTestUtils.createConnectContext(), apply)
.applyTopDown(new ExistsApplyToJoin())
@@ -84,10 +83,10 @@ class ExistsApplyToJoinTest implements
MemoPatternMatchSupported {
LogicalOlapScan right = PlanConstructor.newLogicalOlapScan(0, "t2", 1);
List<Slot> rightSlots = right.getOutput();
EqualTo equalTo = new EqualTo(leftSlots.get(0), rightSlots.get(0));
- Exists exists = new Exists(right, true);
LogicalApply<LogicalOlapScan, LogicalOlapScan> apply =
new LogicalApply<>(Collections.emptyList(),
- exists, Optional.of(equalTo), Optional.empty(),
+ LogicalApply.SubQueryType.EXITS_SUBQUERY, true,
Optional.empty(), Optional.empty(),
+ Optional.of(equalTo), Optional.empty(),
false, false, false, left, right);
PlanChecker.from(MemoTestUtils.createConnectContext(), apply)
.applyTopDown(new ExistsApplyToJoin())
@@ -105,10 +104,10 @@ class ExistsApplyToJoinTest implements
MemoPatternMatchSupported {
LogicalOlapScan right = PlanConstructor.newLogicalOlapScan(0, "t2", 1);
List<Slot> rightSlots = right.getOutput();
EqualTo equalTo = new EqualTo(leftSlots.get(0), rightSlots.get(0));
- Exists exists = new Exists(right, true);
LogicalApply<LogicalOlapScan, LogicalOlapScan> apply =
new LogicalApply<>(ImmutableList.of(leftSlots.get(0),
rightSlots.get(0)),
- exists, Optional.of(equalTo), Optional.empty(),
+ LogicalApply.SubQueryType.EXITS_SUBQUERY, true,
Optional.empty(), Optional.empty(),
+ Optional.of(equalTo), Optional.empty(),
false, false, false, left, right);
PlanChecker.from(MemoTestUtils.createConnectContext(), apply)
.applyTopDown(new ExistsApplyToJoin())
diff --git a/regression-test/data/nereids_p0/test_cte_subquery_many.out
b/regression-test/data/nereids_p0/test_cte_subquery_many.out
new file mode 100644
index 00000000000..a95e8f993c9
Binary files /dev/null and
b/regression-test/data/nereids_p0/test_cte_subquery_many.out differ
diff --git a/regression-test/suites/nereids_p0/test_cte_subquery_many.groovy
b/regression-test/suites/nereids_p0/test_cte_subquery_many.groovy
new file mode 100644
index 00000000000..317bf51576f
--- /dev/null
+++ b/regression-test/suites/nereids_p0/test_cte_subquery_many.groovy
@@ -0,0 +1,756 @@
+// 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("test_cte_subquery_many") {
+ multi_sql """
+ drop database if exists test_cte_subquery_many_db;
+ create database test_cte_subquery_many_db;
+ use test_cte_subquery_many_db;
+
+ CREATE TABLE IF NOT EXISTS tbl_1 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_2 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_3 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_4 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_5 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_6 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_7 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_8 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_9 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_10 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_11 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_12 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_13 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_14 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_15 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_16 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_17 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_18 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_19 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ CREATE TABLE IF NOT EXISTS tbl_20 (
+ id BIGINT,
+ col1 VARCHAR(255),
+ col2 INT,
+ col3 DECIMAL(10,2),
+ col4 DATE,
+ col5 BOOLEAN,
+ related_id BIGINT,
+ join_key VARCHAR(50)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES ("replication_num" = "1");
+
+
+ INSERT INTO tbl_1 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_2 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_3 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_4 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_5 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_6 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_7 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_8 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_9 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_10 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_11 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_12 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_13 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_14 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_15 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_16 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_17 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_18 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_19 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+
+ INSERT INTO tbl_20 VALUES (1, 'data1-1', 10, 15.0, '2023-01-01', TRUE,
1, 'key1');
+ """
+ qt_sql """
+ WITH cte AS (
+ SELECT
+ t0.id,
+ SUM(t0.col2) AS agg_value,
+ CASE
+ WHEN SUM(t0.col2) > 0 THEN 'BASE'
+ WHEN SUM(t0.col2) BETWEEN 0 AND 0 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_1 t0
+ INNER JOIN tbl_6 j0
+ ON t0.join_key = j0.join_key
+ WHERE EXISTS (SELECT sq0.id
+ FROM (SELECT
+ t1.id,
+ SUM(t1.col2) AS agg_value,
+ CASE
+ WHEN SUM(t1.col2) > 100 THEN 'BASE'
+ WHEN SUM(t1.col2) BETWEEN 50 AND 100 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_2 t1
+ LEFT JOIN tbl_7 j1
+ ON t1.join_key = j1.join_key
+ WHERE NOT EXISTS (SELECT sq1.id
+ FROM (SELECT
+ t2.id,
+ SUM(t2.col2) AS agg_value,
+ CASE
+ WHEN SUM(t2.col2) > 200 THEN 'BASE'
+ WHEN SUM(t2.col2) BETWEEN 100 AND 200 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_3 t2
+ RIGHT JOIN tbl_8 j2
+ ON t2.join_key = j2.join_key
+ WHERE t2.id IN (SELECT sq2.id
+ FROM (SELECT
+ t3.id,
+ SUM(t3.col2) AS agg_value,
+ CASE
+ WHEN SUM(t3.col2) > 300 THEN 'BASE'
+ WHEN SUM(t3.col2) BETWEEN 150 AND 300 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_4 t3
+ INNER JOIN tbl_9 j3
+ ON t3.join_key = j3.join_key
+ WHERE t3.id NOT IN (SELECT sq3.id
+ FROM (SELECT
+ t4.id,
+ SUM(t4.col2) AS agg_value,
+ CASE
+ WHEN SUM(t4.col2) > 400 THEN 'BASE'
+ WHEN SUM(t4.col2) BETWEEN 200 AND 400 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_5 t4
+ LEFT JOIN tbl_10 j4
+ ON t4.join_key = j4.join_key
+ WHERE EXISTS (SELECT sq4.id
+ FROM (SELECT
+ t5.id,
+ SUM(t5.col2) AS agg_value,
+ CASE
+ WHEN SUM(t5.col2) > 500 THEN 'BASE'
+ WHEN SUM(t5.col2) BETWEEN 250 AND 500 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_6 t5
+ RIGHT JOIN tbl_11 j5
+ ON t5.join_key = j5.join_key
+ WHERE NOT EXISTS (SELECT sq5.id
+ FROM (SELECT
+ t6.id,
+ SUM(t6.col2) AS agg_value,
+ CASE
+ WHEN SUM(t6.col2) > 600 THEN 'BASE'
+ WHEN SUM(t6.col2) BETWEEN 300 AND 600 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_7 t6
+ INNER JOIN tbl_12 j6
+ ON t6.join_key = j6.join_key
+ WHERE t6.id IN (SELECT sq6.id
+ FROM (SELECT
+ t7.id,
+ SUM(t7.col2) AS agg_value,
+ CASE
+ WHEN SUM(t7.col2) > 700 THEN 'BASE'
+ WHEN SUM(t7.col2) BETWEEN 350 AND 700 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_8 t7
+ LEFT JOIN tbl_13 j7
+ ON t7.join_key = j7.join_key
+ WHERE t7.id NOT IN (SELECT sq7.id
+ FROM (SELECT
+ t8.id,
+ SUM(t8.col2) AS agg_value,
+ CASE
+ WHEN SUM(t8.col2) > 800 THEN 'BASE'
+ WHEN SUM(t8.col2) BETWEEN 400 AND 800 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_9 t8
+ RIGHT JOIN tbl_14 j8
+ ON t8.join_key = j8.join_key
+ WHERE EXISTS (SELECT sq8.id
+ FROM (SELECT
+ t9.id,
+ SUM(t9.col2) AS agg_value,
+ CASE
+ WHEN SUM(t9.col2) > 900 THEN 'BASE'
+ WHEN SUM(t9.col2) BETWEEN 450 AND 900 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_10 t9
+ INNER JOIN tbl_15 j9
+ ON t9.join_key = j9.join_key
+ WHERE NOT EXISTS (SELECT sq9.id
+ FROM (SELECT
+ t10.id,
+ SUM(t10.col2) AS agg_value,
+ CASE
+ WHEN SUM(t10.col2) > 1000 THEN 'BASE'
+ WHEN SUM(t10.col2) BETWEEN 500 AND 1000 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_11 t10
+ LEFT JOIN tbl_16 j10
+ ON t10.join_key = j10.join_key
+ WHERE t10.id IN (SELECT sq10.id
+ FROM (SELECT
+ t11.id,
+ SUM(t11.col2) AS agg_value,
+ CASE
+ WHEN SUM(t11.col2) > 1100 THEN 'BASE'
+ WHEN SUM(t11.col2) BETWEEN 550 AND 1100 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_12 t11
+ RIGHT JOIN tbl_17 j11
+ ON t11.join_key = j11.join_key
+ WHERE t11.id NOT IN (SELECT sq11.id
+ FROM (SELECT
+ t12.id,
+ SUM(t12.col2) AS agg_value,
+ CASE
+ WHEN SUM(t12.col2) > 1200 THEN 'BASE'
+ WHEN SUM(t12.col2) BETWEEN 600 AND 1200 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_13 t12
+ INNER JOIN tbl_18 j12
+ ON t12.join_key = j12.join_key
+ WHERE EXISTS (SELECT sq12.id
+ FROM (SELECT
+ t13.id,
+ SUM(t13.col2) AS agg_value,
+ CASE
+ WHEN SUM(t13.col2) > 1300 THEN 'BASE'
+ WHEN SUM(t13.col2) BETWEEN 650 AND 1300 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_14 t13
+ LEFT JOIN tbl_19 j13
+ ON t13.join_key = j13.join_key
+ WHERE NOT EXISTS (SELECT sq13.id
+ FROM (SELECT
+ t14.id,
+ SUM(t14.col2) AS agg_value,
+ CASE
+ WHEN SUM(t14.col2) > 1400 THEN 'BASE'
+ WHEN SUM(t14.col2) BETWEEN 700 AND 1400 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_15 t14
+ RIGHT JOIN tbl_20 j14
+ ON t14.join_key = j14.join_key
+ WHERE t14.id IN (SELECT sq14.id
+ FROM (SELECT
+ t15.id,
+ SUM(t15.col2) AS agg_value,
+ CASE
+ WHEN SUM(t15.col2) > 1500 THEN 'BASE'
+ WHEN SUM(t15.col2) BETWEEN 750 AND 1500 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_16 t15
+ INNER JOIN tbl_1 j15
+ ON t15.join_key = j15.join_key
+ WHERE t15.id NOT IN (SELECT sq15.id
+ FROM (SELECT
+ t16.id,
+ SUM(t16.col2) AS agg_value,
+ CASE
+ WHEN SUM(t16.col2) > 1600 THEN 'BASE'
+ WHEN SUM(t16.col2) BETWEEN 800 AND 1600 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_17 t16
+ LEFT JOIN tbl_2 j16
+ ON t16.join_key = j16.join_key
+ WHERE EXISTS (SELECT sq16.id
+ FROM (SELECT
+ t17.id,
+ SUM(t17.col2) AS agg_value,
+ CASE
+ WHEN SUM(t17.col2) > 1700 THEN 'BASE'
+ WHEN SUM(t17.col2) BETWEEN 850 AND 1700 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_18 t17
+ RIGHT JOIN tbl_3 j17
+ ON t17.join_key = j17.join_key
+ WHERE NOT EXISTS (SELECT sq17.id
+ FROM (SELECT
+ t18.id,
+ SUM(t18.col2) AS agg_value,
+ CASE
+ WHEN SUM(t18.col2) > 1800 THEN 'BASE'
+ WHEN SUM(t18.col2) BETWEEN 900 AND 1800 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_19 t18
+ INNER JOIN tbl_4 j18
+ ON t18.join_key = j18.join_key
+ WHERE t18.id IN (SELECT sq18.id
+ FROM (SELECT
+ t19.id,
+ SUM(t19.col2) AS agg_value,
+ CASE
+ WHEN SUM(t19.col2) > 1900 THEN 'BASE'
+ WHEN SUM(t19.col2) BETWEEN 950 AND 1900 THEN 'BASE'
+ ELSE 'BASE'
+ END AS case_label
+ FROM tbl_20 t19
+ LEFT JOIN tbl_5 j19
+ ON t19.join_key = j19.join_key
+ WHERE t19.id NOT IN (SELECT sq19.id
+ FROM (SELECT
+ id,
+ col2 AS agg_value,
+ 'BASE' AS case_label
+ FROM tbl_1
+ WHERE col3 > 10
+ LIMIT 1) sq19
+ WHERE sq19.agg_value = j19.col2
+ AND sq19.case_label LIKE '%BASE%'
+ AND j19.col2 BETWEEN 100 AND 1000)
+ AND t19.col5 = true
+ AND j19.col1 LIKE '%data%'
+ AND t19.col3 IN (SELECT col3 FROM tbl_3 WHERE id > 0)
+ GROUP BY t19.id) sq18
+ WHERE sq18.agg_value = j18.col2
+ AND sq18.case_label LIKE '%BASE%'
+ AND j18.col2 BETWEEN 10 AND 100)
+ AND t18.col5 = true
+ AND j18.col1 LIKE '%data%'
+ AND t18.col3 IN (SELECT col3 FROM tbl_2 WHERE id > 0)
+ GROUP BY t18.id) sq17
+ WHERE sq17.agg_value = j17.col2
+ AND sq17.case_label LIKE '%BASE%'
+ AND j17.col2 BETWEEN 100 AND 1000)
+ AND t17.col5 = true
+ AND j17.col1 LIKE '%data%'
+ AND t17.col3 IN (SELECT col3 FROM tbl_1 WHERE id > 0)
+ GROUP BY t17.id) sq16
+ WHERE sq16.agg_value = j16.col2
+ AND sq16.case_label LIKE '%BASE%'
+ AND j16.col2 BETWEEN 10 AND 100)
+ AND t16.col5 = true
+ AND j16.col1 LIKE '%data%'
+ AND t16.col3 IN (SELECT col3 FROM tbl_20 WHERE id > 0)
+ GROUP BY t16.id) sq15
+ WHERE sq15.agg_value = j15.col2
+ AND sq15.case_label LIKE '%BASE%'
+ AND j15.col2 BETWEEN 100 AND 1000)
+ AND t15.col5 = true
+ AND j15.col1 LIKE '%data%'
+ AND t15.col3 IN (SELECT col3 FROM tbl_19 WHERE id > 0)
+ GROUP BY t15.id) sq14
+ WHERE sq14.agg_value = j14.col2
+ AND sq14.case_label LIKE '%BASE%'
+ AND j14.col2 BETWEEN 10 AND 100)
+ AND t14.col5 = true
+ AND j14.col1 LIKE '%data%'
+ AND t14.col3 IN (SELECT col3 FROM tbl_18 WHERE id > 0)
+ GROUP BY t14.id) sq13
+ WHERE sq13.agg_value = j13.col2
+ AND sq13.case_label LIKE '%BASE%'
+ AND j13.col2 BETWEEN 100 AND 1000)
+ AND t13.col5 = true
+ AND j13.col1 LIKE '%data%'
+ AND t13.col3 IN (SELECT col3 FROM tbl_17 WHERE id > 0)
+ GROUP BY t13.id) sq12
+ WHERE sq12.agg_value = j12.col2
+ AND sq12.case_label LIKE '%BASE%'
+ AND j12.col2 BETWEEN 10 AND 100)
+ AND t12.col5 = true
+ AND j12.col1 LIKE '%data%'
+ AND t12.col3 IN (SELECT col3 FROM tbl_16 WHERE id > 0)
+ GROUP BY t12.id) sq11
+ WHERE sq11.agg_value = j11.col2
+ AND sq11.case_label LIKE '%BASE%'
+ AND j11.col2 BETWEEN 100 AND 1000)
+ AND t11.col5 = true
+ AND j11.col1 LIKE '%data%'
+ AND t11.col3 IN (SELECT col3 FROM tbl_15 WHERE id > 0)
+ GROUP BY t11.id) sq10
+ WHERE sq10.agg_value = j10.col2
+ AND sq10.case_label LIKE '%BASE%'
+ AND j10.col2 BETWEEN 10 AND 100)
+ AND t10.col5 = true
+ AND j10.col1 LIKE '%data%'
+ AND t10.col3 IN (SELECT col3 FROM tbl_14 WHERE id > 0)
+ GROUP BY t10.id) sq9
+ WHERE sq9.agg_value = j9.col2
+ AND sq9.case_label LIKE '%BASE%'
+ AND j9.col2 BETWEEN 100 AND 1000)
+ AND t9.col5 = true
+ AND j9.col1 LIKE '%data%'
+ AND t9.col3 IN (SELECT col3 FROM tbl_13 WHERE id > 0)
+ GROUP BY t9.id) sq8
+ WHERE sq8.agg_value = j8.col2
+ AND sq8.case_label LIKE '%BASE%'
+ AND j8.col2 BETWEEN 10 AND 100)
+ AND t8.col5 = true
+ AND j8.col1 LIKE '%data%'
+ AND t8.col3 IN (SELECT col3 FROM tbl_12 WHERE id > 0)
+ GROUP BY t8.id) sq7
+ WHERE sq7.agg_value = j7.col2
+ AND sq7.case_label LIKE '%BASE%'
+ AND j7.col2 BETWEEN 100 AND 1000)
+ AND t7.col5 = true
+ AND j7.col1 LIKE '%data%'
+ AND t7.col3 IN (SELECT col3 FROM tbl_11 WHERE id > 0)
+ GROUP BY t7.id) sq6
+ WHERE sq6.agg_value = j6.col2
+ AND sq6.case_label LIKE '%BASE%'
+ AND j6.col2 BETWEEN 10 AND 100)
+ AND t6.col5 = true
+ AND j6.col1 LIKE '%data%'
+ AND t6.col3 IN (SELECT col3 FROM tbl_10 WHERE id > 0)
+ GROUP BY t6.id) sq5
+ WHERE sq5.agg_value = j5.col2
+ AND sq5.case_label LIKE '%BASE%'
+ AND j5.col2 BETWEEN 100 AND 1000)
+ AND t5.col5 = true
+ AND j5.col1 LIKE '%data%'
+ AND t5.col3 IN (SELECT col3 FROM tbl_9 WHERE id > 0)
+ GROUP BY t5.id) sq4
+ WHERE sq4.agg_value = j4.col2
+ AND sq4.case_label LIKE '%BASE%'
+ AND j4.col2 BETWEEN 10 AND 100)
+ AND t4.col5 = true
+ AND j4.col1 LIKE '%data%'
+ AND t4.col3 IN (SELECT col3 FROM tbl_8 WHERE id > 0)
+ GROUP BY t4.id) sq3
+ WHERE sq3.agg_value = j3.col2
+ AND sq3.case_label LIKE '%BASE%'
+ AND j3.col2 BETWEEN 100 AND 1000)
+ AND t3.col5 = true
+ AND j3.col1 LIKE '%data%'
+ AND t3.col3 IN (SELECT col3 FROM tbl_7 WHERE id > 0)
+ GROUP BY t3.id) sq2
+ WHERE sq2.agg_value = j2.col2
+ AND sq2.case_label LIKE '%BASE%'
+ AND j2.col2 BETWEEN 10 AND 100)
+ AND t2.col5 = true
+ AND j2.col1 LIKE '%data%'
+ AND t2.col3 IN (SELECT col3 FROM tbl_6 WHERE id > 0)
+ GROUP BY t2.id) sq1
+ WHERE sq1.agg_value = j1.col2
+ AND sq1.case_label LIKE '%BASE%'
+ AND j1.col2 BETWEEN 100 AND 1000)
+ AND t1.col5 = true
+ AND j1.col1 LIKE '%data%'
+ AND t1.col3 IN (SELECT col3 FROM tbl_5 WHERE id > 0)
+ GROUP BY t1.id) sq0
+ WHERE sq0.agg_value = j0.col2
+ AND sq0.case_label LIKE '%BASE%'
+ AND j0.col2 BETWEEN 10 AND 100)
+ AND t0.col5 = true
+ AND j0.col1 LIKE '%data%'
+ AND t0.col3 IN (SELECT col3 FROM tbl_4 WHERE id > 0)
+ GROUP BY t0.id
+ )
+ SELECT * FROM cte;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
