This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new e2fb2566dda [fix](nereids)should distinguish hash and other conjuncts
for outer join in MultiJoin (#53184)
e2fb2566dda is described below
commit e2fb2566ddab9a3f7c3d21c2eb8769ec27cebee8
Author: starocean999 <[email protected]>
AuthorDate: Wed Jul 16 09:30:04 2025 +0800
[fix](nereids)should distinguish hash and other conjuncts for outer join in
MultiJoin (#53184)
pick from master https://github.com/apache/doris/pull/50378 and
https://github.com/apache/doris/pull/53051
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../nereids/rules/analysis/SubqueryToApply.java | 18 +-
.../nereids/rules/rewrite/ExistsApplyToJoin.java | 5 +-
.../doris/nereids/rules/rewrite/InApplyToJoin.java | 21 +-
.../doris/nereids/rules/rewrite/MultiJoin.java | 24 +-
.../nereids/rules/rewrite/PullUpCteAnchor.java | 24 +-
.../rules/rewrite/PullUpProjectUnderApply.java | 3 +-
.../doris/nereids/rules/rewrite/ReorderJoin.java | 17 +-
.../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/join/test_many_multi_join.groovy | 905 +++++++++++++++++++++
.../nereids_p0/test_cte_subquery_many.groovy | 696 ++++++++++++++++
17 files changed, 1754 insertions(+), 101 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 cfc5b2ba24a..da0ff4c0a25 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
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.analysis;
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;
@@ -390,9 +391,24 @@ public class SubqueryToApply implements
AnalysisRuleFactory {
ctx.setSubqueryExprIsAnalyzed(subquery, true);
boolean needAddScalarSubqueryOutputToProjects =
isConjunctContainsScalarSubqueryOutput(
subquery, conjunct, isProject, singleSubquery);
+ 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(),
subqueryToMarkJoinSlot.get(subquery),
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/MultiJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MultiJoin.java
index 83e849086ab..bc6ea17d8a6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MultiJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MultiJoin.java
@@ -69,14 +69,16 @@ public class MultiJoin extends AbstractLogicalPlan
implements BlockFuncDepsPropa
// MultiJoin just contains one OUTER/SEMI/ANTI.
private final JoinType joinType;
// When contains one OUTER/SEMI/ANTI join, keep separately its condition.
- private final List<Expression> notInnerJoinConditions;
+ private final List<Expression> notInnerJoinHashConditions;
+ private final List<Expression> notInnerJoinOtherConditions;
public MultiJoin(List<Plan> inputs, List<Expression> joinFilter, JoinType
joinType,
- List<Expression> notInnerJoinConditions) {
+ List<Expression> notInnerJoinHashConditions, List<Expression>
notInnerJoinOtherConditions) {
super(PlanType.LOGICAL_MULTI_JOIN, inputs);
this.joinFilter = Objects.requireNonNull(joinFilter);
this.joinType = joinType;
- this.notInnerJoinConditions =
Objects.requireNonNull(notInnerJoinConditions);
+ this.notInnerJoinHashConditions =
Objects.requireNonNull(notInnerJoinHashConditions);
+ this.notInnerJoinOtherConditions =
Objects.requireNonNull(notInnerJoinOtherConditions);
}
public JoinType getJoinType() {
@@ -87,13 +89,17 @@ public class MultiJoin extends AbstractLogicalPlan
implements BlockFuncDepsPropa
return joinFilter;
}
- public List<Expression> getNotInnerJoinConditions() {
- return notInnerJoinConditions;
+ public List<Expression> getNotInnerHashJoinConditions() {
+ return notInnerJoinHashConditions;
+ }
+
+ public List<Expression> getNotInnerOtherJoinConditions() {
+ return notInnerJoinOtherConditions;
}
@Override
public MultiJoin withChildren(List<Plan> children) {
- return new MultiJoin(children, joinFilter, joinType,
notInnerJoinConditions);
+ return new MultiJoin(children, joinFilter, joinType,
notInnerJoinHashConditions, notInnerJoinOtherConditions);
}
@Override
@@ -160,7 +166,8 @@ public class MultiJoin extends AbstractLogicalPlan
implements BlockFuncDepsPropa
public List<? extends Expression> getExpressions() {
return new Builder<Expression>()
.addAll(joinFilter)
- .addAll(notInnerJoinConditions)
+ .addAll(notInnerJoinHashConditions)
+ .addAll(notInnerJoinOtherConditions)
.build();
}
@@ -180,7 +187,8 @@ public class MultiJoin extends AbstractLogicalPlan
implements BlockFuncDepsPropa
return Utils.toSqlString("MultiJoin",
"joinType", joinType,
"joinFilter", joinFilter,
- "notInnerJoinConditions", notInnerJoinConditions
+ "notInnerHashJoinConditions", notInnerJoinHashConditions,
+ "notInnerOtherJoinConditions", notInnerJoinOtherConditions
);
}
}
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 79750d55f6f..59762427059 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()) {
Preconditions.checkState(project.getProjects().size()
== 1,
"ScalarSubquery should only have one output
column");
newProjects.add(project.getProjects().get(0));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReorderJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReorderJoin.java
index 238db403be6..99713273339 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReorderJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReorderJoin.java
@@ -112,7 +112,8 @@ public class ReorderJoin extends OneRewriteRuleFactory {
List<Plan> inputs = Lists.newArrayList();
List<Expression> joinFilter = Lists.newArrayList();
- List<Expression> notInnerJoinConditions = Lists.newArrayList();
+ List<Expression> notInnerHashJoinConditions = Lists.newArrayList();
+ List<Expression> notInnerOtherJoinConditions = Lists.newArrayList();
LogicalJoin<?, ?> join;
// Implicit rely on {rule: MergeFilters}, so don't exist
filter--filter--join.
@@ -128,8 +129,8 @@ public class ReorderJoin extends OneRewriteRuleFactory {
joinFilter.addAll(join.getHashJoinConjuncts());
joinFilter.addAll(join.getOtherJoinConjuncts());
} else {
- notInnerJoinConditions.addAll(join.getHashJoinConjuncts());
- notInnerJoinConditions.addAll(join.getOtherJoinConjuncts());
+ notInnerHashJoinConditions.addAll(join.getHashJoinConjuncts());
+ notInnerOtherJoinConditions.addAll(join.getOtherJoinConjuncts());
}
// recursively convert children.
@@ -162,7 +163,8 @@ public class ReorderJoin extends OneRewriteRuleFactory {
inputs,
joinFilter,
join.getJoinType(),
- notInnerJoinConditions);
+ notInnerHashJoinConditions,
+ notInnerOtherJoinConditions);
}
/**
@@ -254,7 +256,7 @@ public class ReorderJoin extends OneRewriteRuleFactory {
multiJoinHandleChildren.children().subList(0,
multiJoinHandleChildren.arity() - 1),
pushedFilter,
JoinType.INNER_JOIN,
- ExpressionUtils.EMPTY_CONDITION), planToHintType);
+ ExpressionUtils.EMPTY_CONDITION,
ExpressionUtils.EMPTY_CONDITION), planToHintType);
} else if (multiJoinHandleChildren.getJoinType().isRightJoin()) {
left = multiJoinHandleChildren.child(0);
Set<ExprId> leftOutputExprIdSet = left.getOutputExprIdSet();
@@ -268,7 +270,7 @@ public class ReorderJoin extends OneRewriteRuleFactory {
multiJoinHandleChildren.children().subList(1,
multiJoinHandleChildren.arity()),
pushedFilter,
JoinType.INNER_JOIN,
- ExpressionUtils.EMPTY_CONDITION), planToHintType);
+ ExpressionUtils.EMPTY_CONDITION,
ExpressionUtils.EMPTY_CONDITION), planToHintType);
} else {
remainingFilter = multiJoin.getJoinFilter();
Preconditions.checkState(multiJoinHandleChildren.arity() == 2);
@@ -285,7 +287,8 @@ public class ReorderJoin extends OneRewriteRuleFactory {
return
PlanUtils.filterOrSelf(ImmutableSet.copyOf(remainingFilter), new LogicalJoin<>(
multiJoinHandleChildren.getJoinType(),
- ExpressionUtils.EMPTY_CONDITION,
multiJoinHandleChildren.getNotInnerJoinConditions(),
+ multiJoinHandleChildren.getNotInnerHashJoinConditions(),
+ multiJoinHandleChildren.getNotInnerOtherJoinConditions(),
new DistributeHint(DistributeType.fromRightPlanHintType(
planToHintType.getOrDefault(right,
JoinDistributeType.NONE))),
Optional.empty(),
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 c499c2dddb9..e7b755a6d53 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/join/test_many_multi_join.groovy
b/regression-test/suites/nereids_p0/join/test_many_multi_join.groovy
new file mode 100644
index 00000000000..132be5c6d17
--- /dev/null
+++ b/regression-test/suites/nereids_p0/join/test_many_multi_join.groovy
@@ -0,0 +1,905 @@
+// 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_many_multi_join", "nereids_p0") {
+ def DBname = "nereids_regression_test_many_multi_join"
+ sql "DROP DATABASE IF EXISTS ${DBname}"
+ sql "CREATE DATABASE IF NOT EXISTS ${DBname}"
+ sql "use ${DBname}"
+
+ multi_sql """
+ drop table if exists table1;
+ drop table if exists table2;
+ drop table if exists table3;
+ drop table if exists table4;
+ drop table if exists table5;
+ drop table if exists table6;
+ drop table if exists table7;
+ drop table if exists table8;
+ drop table if exists table9;
+ drop table if exists table10;
+ drop table if exists table11;
+ drop table if exists table12;
+ drop table if exists table13;
+ drop table if exists table14;
+ drop table if exists table15;
+ drop table if exists table16;
+ drop table if exists table17;
+ drop table if exists table18;
+ drop table if exists table19;
+ drop table if exists table20;
+ drop table if exists table21;
+
+ drop table if exists seq;
+ CREATE TABLE seq (number INT) ENGINE=OLAP UNIQUE KEY(number)
+ DISTRIBUTED BY HASH(number) BUCKETS 1
+ PROPERTIES("replication_num" = "1");
+
+ INSERT INTO seq VALUES (1),(2),(3),(4),(5);
+
+
+ CREATE TABLE IF NOT EXISTS table1 (
+ id BIGINT NOT NULL,
+ value1 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table2 (
+ id BIGINT NOT NULL,
+ value2 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table3 (
+ id BIGINT NOT NULL,
+ value3 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table4 (
+ id BIGINT NOT NULL,
+ value4 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table5 (
+ id BIGINT NOT NULL,
+ value5 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table6 (
+ id BIGINT NOT NULL,
+ value6 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table7 (
+ id BIGINT NOT NULL,
+ value7 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table8 (
+ id BIGINT NOT NULL,
+ value8 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table9 (
+ id BIGINT NOT NULL,
+ value9 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table10 (
+ id BIGINT NOT NULL,
+ value10 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table11 (
+ id BIGINT NOT NULL,
+ value11 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table12 (
+ id BIGINT NOT NULL,
+ value12 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table13 (
+ id BIGINT NOT NULL,
+ value13 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table14 (
+ id BIGINT NOT NULL,
+ value14 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table15 (
+ id BIGINT NOT NULL,
+ value15 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table16 (
+ id BIGINT NOT NULL,
+ value16 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table17 (
+ id BIGINT NOT NULL,
+ value17 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table18 (
+ id BIGINT NOT NULL,
+ value18 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table19 (
+ id BIGINT NOT NULL,
+ value19 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table20 (
+ id BIGINT NOT NULL,
+ value20 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ CREATE TABLE IF NOT EXISTS table21 (
+ id BIGINT NOT NULL,
+ value21 DECIMAL(20,6),
+ related_id BIGINT,
+ ts DATETIME
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 16
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+
+ INSERT INTO table1
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table2
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table3
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table4
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table5
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table6
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table7
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table8
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table9
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table10
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table11
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table12
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table13
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table14
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table15
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table16
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table17
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table18
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table19
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table20
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+ INSERT INTO table21
+ SELECT
+ number,
+ RAND()*2,
+ CAST(RAND()*2 AS BIGINT),
+ NOW() - INTERVAL CAST(RAND()*365 AS INT) DAY
+ FROM seq WHERE number = 2;
+
+
+ INSERT INTO table1 (id, value1, related_id, ts) VALUES
+ (1, -9.432227, 114, '2025-06-17 17:28:08'),
+ (2, -2.109207, 130, '2025-06-05 17:28:08'),
+ (3, -4.487591, 118, '2025-06-15 17:28:08'),
+ (4, -8.823451, 133, '2025-06-13 17:28:08'),
+ (5, 7.741022, 138, '2025-06-08 17:28:08');
+ """
+
+ sql """
+ SELECT * FROM (SELECT
+ t1.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t2.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t3.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t4.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t5.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t6.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t7.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t8.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t9.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t10.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t11.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t12.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t13.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t14.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t15.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t16.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t17.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t18.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t19.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t20.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t21.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t22.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t23.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t24.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t25.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t26.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t27.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t28.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t29.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t30.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t31.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t32.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t33.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t34.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t35.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t36.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t37.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t38.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t39.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t40.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t41.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t42.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t43.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t44.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t45.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t46.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t47.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t48.id,
+ (SELECT sum(ratio) FROM (SELECT
+ t49.id,
+ (SELECT total FROM (SELECT
+ SUM(t50.value11) AS total
+ FROM table11 t50
+ WHERE t50.related_id = t49.id
+ ) AS layer50) * 1.0 / COUNT(*) AS ratio
+ FROM table10 t49
+ WHERE EXISTS (
+ SELECT 1 FROM table11
+ WHERE id = t49.related_id
+ )
+ GROUP BY t49.id) AS layer49) * 1.0 / COUNT(*) AS ratio
+ FROM table9 t48
+ WHERE EXISTS (
+ SELECT 1 FROM table10
+ WHERE id = t48.related_id
+ )
+ GROUP BY t48.id) AS layer48) * 1.0 / COUNT(*) AS ratio
+ FROM table8 t47
+ WHERE EXISTS (
+ SELECT 1 FROM table9
+ WHERE id = t47.related_id
+ )
+ GROUP BY t47.id) AS layer47) * 1.0 / COUNT(*) AS ratio
+ FROM table7 t46
+ WHERE EXISTS (
+ SELECT 1 FROM table8
+ WHERE id = t46.related_id
+ )
+ GROUP BY t46.id) AS layer46) * 1.0 / COUNT(*) AS ratio
+ FROM table6 t45
+ WHERE EXISTS (
+ SELECT 1 FROM table7
+ WHERE id = t45.related_id
+ )
+ GROUP BY t45.id) AS layer45) * 1.0 / COUNT(*) AS ratio
+ FROM table5 t44
+ WHERE EXISTS (
+ SELECT 1 FROM table6
+ WHERE id = t44.related_id
+ )
+ GROUP BY t44.id) AS layer44) * 1.0 / COUNT(*) AS ratio
+ FROM table4 t43
+ WHERE EXISTS (
+ SELECT 1 FROM table5
+ WHERE id = t43.related_id
+ )
+ GROUP BY t43.id) AS layer43) * 1.0 / COUNT(*) AS ratio
+ FROM table3 t42
+ WHERE EXISTS (
+ SELECT 1 FROM table4
+ WHERE id = t42.related_id
+ )
+ GROUP BY t42.id) AS layer42) * 1.0 / COUNT(*) AS ratio
+ FROM table2 t41
+ WHERE EXISTS (
+ SELECT 1 FROM table3
+ WHERE id = t41.related_id
+ )
+ GROUP BY t41.id) AS layer41) * 1.0 / COUNT(*) AS ratio
+ FROM table1 t40
+ WHERE EXISTS (
+ SELECT 1 FROM table2
+ WHERE id = t40.related_id
+ )
+ GROUP BY t40.id) AS layer40) * 1.0 / COUNT(*) AS ratio
+ FROM table20 t39
+ WHERE EXISTS (
+ SELECT 1 FROM table1
+ WHERE id = t39.related_id
+ )
+ GROUP BY t39.id) AS layer39) * 1.0 / COUNT(*) AS ratio
+ FROM table19 t38
+ WHERE EXISTS (
+ SELECT 1 FROM table20
+ WHERE id = t38.related_id
+ )
+ GROUP BY t38.id) AS layer38) * 1.0 / COUNT(*) AS ratio
+ FROM table18 t37
+ WHERE EXISTS (
+ SELECT 1 FROM table19
+ WHERE id = t37.related_id
+ )
+ GROUP BY t37.id) AS layer37) * 1.0 / COUNT(*) AS ratio
+ FROM table17 t36
+ WHERE EXISTS (
+ SELECT 1 FROM table18
+ WHERE id = t36.related_id
+ )
+ GROUP BY t36.id) AS layer36) * 1.0 / COUNT(*) AS ratio
+ FROM table16 t35
+ WHERE EXISTS (
+ SELECT 1 FROM table17
+ WHERE id = t35.related_id
+ )
+ GROUP BY t35.id) AS layer35) * 1.0 / COUNT(*) AS ratio
+ FROM table15 t34
+ WHERE EXISTS (
+ SELECT 1 FROM table16
+ WHERE id = t34.related_id
+ )
+ GROUP BY t34.id) AS layer34) * 1.0 / COUNT(*) AS ratio
+ FROM table14 t33
+ WHERE EXISTS (
+ SELECT 1 FROM table15
+ WHERE id = t33.related_id
+ )
+ GROUP BY t33.id) AS layer33) * 1.0 / COUNT(*) AS ratio
+ FROM table13 t32
+ WHERE EXISTS (
+ SELECT 1 FROM table14
+ WHERE id = t32.related_id
+ )
+ GROUP BY t32.id) AS layer32) * 1.0 / COUNT(*) AS ratio
+ FROM table12 t31
+ WHERE EXISTS (
+ SELECT 1 FROM table13
+ WHERE id = t31.related_id
+ )
+ GROUP BY t31.id) AS layer31) * 1.0 / COUNT(*) AS ratio
+ FROM table11 t30
+ WHERE EXISTS (
+ SELECT 1 FROM table12
+ WHERE id = t30.related_id
+ )
+ GROUP BY t30.id) AS layer30) * 1.0 / COUNT(*) AS ratio
+ FROM table10 t29
+ WHERE EXISTS (
+ SELECT 1 FROM table11
+ WHERE id = t29.related_id
+ )
+ GROUP BY t29.id) AS layer29) * 1.0 / COUNT(*) AS ratio
+ FROM table9 t28
+ WHERE EXISTS (
+ SELECT 1 FROM table10
+ WHERE id = t28.related_id
+ )
+ GROUP BY t28.id) AS layer28) * 1.0 / COUNT(*) AS ratio
+ FROM table8 t27
+ WHERE EXISTS (
+ SELECT 1 FROM table9
+ WHERE id = t27.related_id
+ )
+ GROUP BY t27.id) AS layer27) * 1.0 / COUNT(*) AS ratio
+ FROM table7 t26
+ WHERE EXISTS (
+ SELECT 1 FROM table8
+ WHERE id = t26.related_id
+ )
+ GROUP BY t26.id) AS layer26) * 1.0 / COUNT(*) AS ratio
+ FROM table6 t25
+ WHERE EXISTS (
+ SELECT 1 FROM table7
+ WHERE id = t25.related_id
+ )
+ GROUP BY t25.id) AS layer25) * 1.0 / COUNT(*) AS ratio
+ FROM table5 t24
+ WHERE EXISTS (
+ SELECT 1 FROM table6
+ WHERE id = t24.related_id
+ )
+ GROUP BY t24.id) AS layer24) * 1.0 / COUNT(*) AS ratio
+ FROM table4 t23
+ WHERE EXISTS (
+ SELECT 1 FROM table5
+ WHERE id = t23.related_id
+ )
+ GROUP BY t23.id) AS layer23) * 1.0 / COUNT(*) AS ratio
+ FROM table3 t22
+ WHERE EXISTS (
+ SELECT 1 FROM table4
+ WHERE id = t22.related_id
+ )
+ GROUP BY t22.id) AS layer22) * 1.0 / COUNT(*) AS ratio
+ FROM table2 t21
+ WHERE EXISTS (
+ SELECT 1 FROM table3
+ WHERE id = t21.related_id
+ )
+ GROUP BY t21.id) AS layer21) * 1.0 / COUNT(*) AS ratio
+ FROM table1 t20
+ WHERE EXISTS (
+ SELECT 1 FROM table2
+ WHERE id = t20.related_id
+ )
+ GROUP BY t20.id) AS layer20) * 1.0 / COUNT(*) AS ratio
+ FROM table20 t19
+ WHERE EXISTS (
+ SELECT 1 FROM table1
+ WHERE id = t19.related_id
+ )
+ GROUP BY t19.id) AS layer19) * 1.0 / COUNT(*) AS ratio
+ FROM table19 t18
+ WHERE EXISTS (
+ SELECT 1 FROM table20
+ WHERE id = t18.related_id
+ )
+ GROUP BY t18.id) AS layer18) * 1.0 / COUNT(*) AS ratio
+ FROM table18 t17
+ WHERE EXISTS (
+ SELECT 1 FROM table19
+ WHERE id = t17.related_id
+ )
+ GROUP BY t17.id) AS layer17) * 1.0 / COUNT(*) AS ratio
+ FROM table17 t16
+ WHERE EXISTS (
+ SELECT 1 FROM table18
+ WHERE id = t16.related_id
+ )
+ GROUP BY t16.id) AS layer16) * 1.0 / COUNT(*) AS ratio
+ FROM table16 t15
+ WHERE EXISTS (
+ SELECT 1 FROM table17
+ WHERE id = t15.related_id
+ )
+ GROUP BY t15.id) AS layer15) * 1.0 / COUNT(*) AS ratio
+ FROM table15 t14
+ WHERE EXISTS (
+ SELECT 1 FROM table16
+ WHERE id = t14.related_id
+ )
+ GROUP BY t14.id) AS layer14) * 1.0 / COUNT(*) AS ratio
+ FROM table14 t13
+ WHERE EXISTS (
+ SELECT 1 FROM table15
+ WHERE id = t13.related_id
+ )
+ GROUP BY t13.id) AS layer13) * 1.0 / COUNT(*) AS ratio
+ FROM table13 t12
+ WHERE EXISTS (
+ SELECT 1 FROM table14
+ WHERE id = t12.related_id
+ )
+ GROUP BY t12.id) AS layer12) * 1.0 / COUNT(*) AS ratio
+ FROM table12 t11
+ WHERE EXISTS (
+ SELECT 1 FROM table13
+ WHERE id = t11.related_id
+ )
+ GROUP BY t11.id) AS layer11) * 1.0 / COUNT(*) AS ratio
+ FROM table11 t10
+ WHERE EXISTS (
+ SELECT 1 FROM table12
+ WHERE id = t10.related_id
+ )
+ GROUP BY t10.id) AS layer10) * 1.0 / COUNT(*) AS ratio
+ FROM table10 t9
+ WHERE EXISTS (
+ SELECT 1 FROM table11
+ WHERE id = t9.related_id
+ )
+ GROUP BY t9.id) AS layer9) * 1.0 / COUNT(*) AS ratio
+ FROM table9 t8
+ WHERE EXISTS (
+ SELECT 1 FROM table10
+ WHERE id = t8.related_id
+ )
+ GROUP BY t8.id) AS layer8) * 1.0 / COUNT(*) AS ratio
+ FROM table8 t7
+ WHERE EXISTS (
+ SELECT 1 FROM table9
+ WHERE id = t7.related_id
+ )
+ GROUP BY t7.id) AS layer7) * 1.0 / COUNT(*) AS ratio
+ FROM table7 t6
+ WHERE EXISTS (
+ SELECT 1 FROM table8
+ WHERE id = t6.related_id
+ )
+ GROUP BY t6.id) AS layer6) * 1.0 / COUNT(*) AS ratio
+ FROM table6 t5
+ WHERE EXISTS (
+ SELECT 1 FROM table7
+ WHERE id = t5.related_id
+ )
+ GROUP BY t5.id) AS layer5) * 1.0 / COUNT(*) AS ratio
+ FROM table5 t4
+ WHERE EXISTS (
+ SELECT 1 FROM table6
+ WHERE id = t4.related_id
+ )
+ GROUP BY t4.id) AS layer4) * 1.0 / COUNT(*) AS ratio
+ FROM table4 t3
+ WHERE EXISTS (
+ SELECT 1 FROM table5
+ WHERE id = t3.related_id
+ )
+ GROUP BY t3.id) AS layer3) * 1.0 / COUNT(*) AS ratio
+ FROM table3 t2
+ WHERE EXISTS (
+ SELECT 1 FROM table4
+ WHERE id = t2.related_id
+ )
+ GROUP BY t2.id) AS layer2) * 1.0 / COUNT(*) AS ratio
+ FROM table2 t1
+ WHERE EXISTS (
+ SELECT 1 FROM table3
+ WHERE id = t1.related_id
+ )
+ GROUP BY t1.id) AS layer1;
+ """
+}
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..fbd6a93e251
--- /dev/null
+++ b/regression-test/suites/nereids_p0/test_cte_subquery_many.groovy
@@ -0,0 +1,696 @@
+// 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]
