This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 94b1a8f4a78 [fix](nereids) pull up left join right predicate with or
is null (#58372)
94b1a8f4a78 is described below
commit 94b1a8f4a78e8d09f8f73d5731c8d4fdc4ac852c
Author: feiniaofeiafei <[email protected]>
AuthorDate: Mon Dec 1 12:17:30 2025 +0800
[fix](nereids) pull up left join right predicate with or is null (#58372)
### What problem does this PR solve?
Related PR: #41731
Problem Summary:
The optimizer cannot derive predicates across multiple LEFT JOINs. For
example, given a filter on the leftmost table in a chain of LEFT JOINs,
the optimizer should be able to derive predicates on the rightmost
table, but it currently fails to do so.
create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);
insert into t1 values(1,2);
insert into t2 values(1,2);
insert into t3 values(1,2);
insert into t3 values(null,2);
explain logical plan
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where
t1.a=1;
LogicalResultSink[110] ( outputExprs=[a#0, b#1, a#2, b#3, a#4, b#5] )
+--LogicalProject[109] ( distinct=false, projects=[a#0, b#1, a#2, b#3, a#4,
b#5] )
+--LogicalJoin[108] ( type=LEFT_OUTER_JOIN,
markJoinSlotReference=Optional.empty, hashJoinConjuncts=[(a#2 = a#4)],
otherJoinConjuncts=[], markJoinConjuncts=[] )
|--LogicalProject[105] ( distinct=false, projects=[a#0, b#1, a#2,
b#3] )
| +--LogicalJoin[104] ( type=LEFT_OUTER_JOIN,
markJoinSlotReference=Optional.empty, hashJoinConjuncts=[(a#0 = a#2)],
otherJoinConjuncts=[], markJoinConjuncts=[] )
| |--LogicalFilter[101] ( predicates=(a#0 = 1) )
| | +--LogicalOlapScan ( qualified=internal.maldb.t1,
indexName=<index_not_selected>, selectedIndexId=1764043369852, preAgg=ON,
operativeCol=[a#0], virtualColumns=[] )
| +--LogicalFilter[103] ( predicates=(a#2 = 1) )
| +--LogicalOlapScan ( qualified=internal.maldb.t2,
indexName=<index_not_selected>, selectedIndexId=1764043369875, preAgg=ON,
operativeCol=[a#2], virtualColumns=[] )
+--LogicalOlapScan ( qualified=internal.maldb.t3,
indexName=<index_not_selected>, selectedIndexId=1764043369898, preAgg=ON,
operativeCol=[a#4], virtualColumns=[] )
The optimizer should derive t3.a=1 from t1.a=1 and the join conditions,
but it currently doesn't.
The root cause is that the PullUpPredicates rule doesn't properly handle
predicate pull-up from the right side of LEFT JOINs. This PR fixes this
by generating null-tolerant predicates when pulling up from RIGHT JOIN's
right table and strengthening them when possible based on upper-level
join conditions.
after this pr:
LogicalResultSink[110] ( outputExprs=[a#0, b#1, a#2, b#3, a#4, b#5] )
+--LogicalProject[109] ( distinct=false, projects=[a#0, b#1, a#2, b#3, a#4,
b#5] )
+--LogicalJoin[108] ( type=LEFT_OUTER_JOIN,
markJoinSlotReference=Optional.empty, hashJoinConjuncts=[(a#2 = a#4)],
otherJoinConjuncts=[], markJoinConjuncts=[] )
|--LogicalProject[105] ( distinct=false, projects=[a#0, b#1, a#2,
b#3] )
| +--LogicalJoin[104] ( type=LEFT_OUTER_JOIN,
markJoinSlotReference=Optional.empty, hashJoinConjuncts=[(a#0 = a#2)],
otherJoinConjuncts=[], markJoinConjuncts=[] )
| |--LogicalFilter[101] ( predicates=(a#0 = 1) )
| | +--LogicalOlapScan ( qualified=internal.maldb.t1,
indexName=<index_not_selected>, selectedIndexId=1764043369852, preAgg=ON,
operativeCol=[a#0], virtualColumns=[] )
| +--LogicalFilter[103] ( predicates=(a#2 = 1) )
| +--LogicalOlapScan ( qualified=internal.maldb.t2,
indexName=<index_not_selected>, selectedIndexId=1764043369875, preAgg=ON,
operativeCol=[a#2], virtualColumns=[] )
+--LogicalFilter[107] ( predicates=(a#4 = 1) )
+--LogicalOlapScan ( qualified=internal.maldb.t3,
indexName=<index_not_selected>, selectedIndexId=1764043369898, preAgg=ON,
operativeCol=[a#4], virtualColumns=[] )
---
.../rules/rewrite/InferPredicateByReplace.java | 9 ++
.../nereids/rules/rewrite/InferPredicates.java | 103 +++++++++++++++---
.../nereids/rules/rewrite/PullUpPredicates.java | 32 ++++++
.../trees/expressions/CompoundPredicate.java | 6 +-
.../apache/doris/nereids/trees/expressions/Or.java | 19 +++-
.../trees/plans/logical/LogicalAggregate.java | 2 +-
.../rules/rewrite/InferPredicateByReplaceTest.java | 2 +-
.../extend_infer_equal_predicate.out | 119 ++++++++++++++++++++-
.../infer_predicate/pull_up_predicate_agg.out | 12 ++-
.../extend_infer_equal_predicate.groovy | 35 ++++++
10 files changed, 313 insertions(+), 26 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplace.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplace.java
index 7b052b8e870..c2ca99f0b61 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplace.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplace.java
@@ -29,6 +29,7 @@ import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.InPredicate;
import org.apache.doris.nereids.trees.expressions.Like;
import org.apache.doris.nereids.trees.expressions.Not;
+import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.functions.ExpressionTrait;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
@@ -126,6 +127,14 @@ public class InferPredicateByReplace {
return null;
}
+ @Override
+ public Void visitOr(Or or, Map<Expression, Set<Expression>> context) {
+ for (Expression expr : getAllSubExpressions(or)) {
+ context.computeIfAbsent(expr, k -> new
LinkedHashSet<>()).add(or);
+ }
+ return null;
+ }
+
private boolean validComparisonPredicate(ComparisonPredicate
comparisonPredicate) {
return comparisonPredicate.right() instanceof Literal;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicates.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicates.java
index d4c5af6e578..f0d4deac147 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicates.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicates.java
@@ -18,9 +18,12 @@
package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.mysql.MysqlCommand;
+import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.jobs.JobContext;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.IsNull;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
@@ -37,15 +40,20 @@ import org.apache.doris.nereids.util.PlanUtils;
import org.apache.doris.nereids.util.PredicateInferUtils;
import org.apache.doris.qe.ConnectContext;
+import com.google.common.base.Suppliers;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.LinkedHashSet;
+import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
+import java.util.function.Supplier;
/**
* infer additional predicates for `LogicalFilter` and `LogicalJoin`.
@@ -91,20 +99,23 @@ public class InferPredicates extends
DefaultPlanRewriter<JobContext> implements
Plan right = join.right();
Set<Expression> expressions = getAllExpressions(left, right,
join.getOnClauseCondition());
switch (join.getJoinType()) {
- case INNER_JOIN:
case CROSS_JOIN:
- case LEFT_SEMI_JOIN:
- case RIGHT_SEMI_JOIN:
left = inferNewPredicate(left, expressions);
right = inferNewPredicate(right, expressions);
break;
+ case INNER_JOIN:
+ case LEFT_SEMI_JOIN:
+ case RIGHT_SEMI_JOIN:
+ left = inferNewPredicateRemoveUselessIsNull(left, expressions,
join, context.getCascadesContext());
+ right = inferNewPredicateRemoveUselessIsNull(right,
expressions, join, context.getCascadesContext());
+ break;
case LEFT_OUTER_JOIN:
case LEFT_ANTI_JOIN:
- right = inferNewPredicate(right, expressions);
+ right = inferNewPredicateRemoveUselessIsNull(right,
expressions, join, context.getCascadesContext());
break;
case RIGHT_OUTER_JOIN:
case RIGHT_ANTI_JOIN:
- left = inferNewPredicate(left, expressions);
+ left = inferNewPredicateRemoveUselessIsNull(left, expressions,
join, context.getCascadesContext());
break;
default:
break;
@@ -122,12 +133,16 @@ public class InferPredicates extends
DefaultPlanRewriter<JobContext> implements
return new
LogicalEmptyRelation(StatementScopeIdGenerator.newRelationId(),
filter.getOutput());
}
filter = visitChildren(this, filter, context);
- Set<Expression> filterPredicates = pullUpPredicates(filter);
- filterPredicates.removeAll(pullUpAllPredicates(filter.child()));
- if (filterPredicates.isEmpty()) {
+ Set<Expression> inferredPredicates = pullUpPredicates(filter);
+ inferredPredicates.removeAll(pullUpAllPredicates(filter.child()));
+ if (inferredPredicates.isEmpty()) {
return filter.child();
}
- return new LogicalFilter<>(ImmutableSet.copyOf(filterPredicates),
filter.child());
+ if (inferredPredicates.equals(filter.getConjuncts())) {
+ return filter;
+ } else {
+ return new
LogicalFilter<>(ImmutableSet.copyOf(inferredPredicates), filter.child());
+ }
}
@Override
@@ -139,15 +154,18 @@ public class InferPredicates extends
DefaultPlanRewriter<JobContext> implements
}
ImmutableList.Builder<Plan> builder = ImmutableList.builder();
builder.add(except.child(0));
+ boolean changed = false;
for (int i = 1; i < except.arity(); ++i) {
Map<Expression, Expression> replaceMap = new HashMap<>();
for (int j = 0; j < except.getOutput().size(); ++j) {
NamedExpression output = except.getOutput().get(j);
replaceMap.put(output, except.getRegularChildOutput(i).get(j));
}
- builder.add(inferNewPredicate(except.child(i),
ExpressionUtils.replace(baseExpressions, replaceMap)));
+ Plan newChild = inferNewPredicate(except.child(i),
ExpressionUtils.replace(baseExpressions, replaceMap));
+ changed = changed || newChild != except.child(i);
+ builder.add(newChild);
}
- return except.withChildren(builder.build());
+ return changed ? except.withChildren(builder.build()) : except;
}
@Override
@@ -158,15 +176,18 @@ public class InferPredicates extends
DefaultPlanRewriter<JobContext> implements
return intersect;
}
ImmutableList.Builder<Plan> builder = ImmutableList.builder();
+ boolean changed = false;
for (int i = 0; i < intersect.arity(); ++i) {
Map<Expression, Expression> replaceMap = new HashMap<>();
for (int j = 0; j < intersect.getOutput().size(); ++j) {
NamedExpression output = intersect.getOutput().get(j);
replaceMap.put(output,
intersect.getRegularChildOutput(i).get(j));
}
- builder.add(inferNewPredicate(intersect.child(i),
ExpressionUtils.replace(baseExpressions, replaceMap)));
+ Plan newChild = inferNewPredicate(intersect.child(i),
ExpressionUtils.replace(baseExpressions, replaceMap));
+ changed = changed || newChild != intersect.child(i);
+ builder.add(newChild);
}
- return intersect.withChildren(builder.build());
+ return changed ? intersect.withChildren(builder.build()) : intersect;
}
private Set<Expression> getAllExpressions(Plan left, Plan right,
Optional<Expression> condition) {
@@ -196,4 +217,60 @@ public class InferPredicates extends
DefaultPlanRewriter<JobContext> implements
predicates.removeAll(plan.accept(pullUpAllPredicates, null));
return PlanUtils.filterOrSelf(predicates, plan);
}
+
+ // Remove redundant "or is null" from expressions.
+ // For example, when we have a t2 left join t3 condition t2.a=t3.a, we can
infer that t3.a is not null.
+ // If we find a predicate like "t3.a = 1 or t3.a is null" in expressions,
we change it to "t3.a=1".
+ private Plan inferNewPredicateRemoveUselessIsNull(Plan plan,
Set<Expression> expressions,
+ LogicalJoin<? extends Plan, ? extends Plan> join, CascadesContext
cascadesContext) {
+ Supplier<Set<Slot>> supplier = Suppliers.memoize(() -> {
+ Set<Expression> all = new HashSet<>();
+ all.addAll(join.getHashJoinConjuncts());
+ all.addAll(join.getOtherJoinConjuncts());
+ return ExpressionUtils.inferNotNullSlots(all, cascadesContext);
+ });
+
+ Set<Expression> predicates = new LinkedHashSet<>();
+ Set<Slot> planOutputs = plan.getOutputSet();
+ for (Expression expr : expressions) {
+ Set<Slot> slots = expr.getInputSlots();
+ if (slots.isEmpty() || !planOutputs.containsAll(slots)) {
+ continue;
+ }
+ if (expr instanceof Or && expr.isInferred()) {
+ List<Expression> orChildren =
ExpressionUtils.extractDisjunction(expr);
+ List<Expression> newOrChildren = Lists.newArrayList();
+ boolean changed = false;
+ for (Expression orChild : orChildren) {
+ if (orChild instanceof IsNull && orChild.child(0)
instanceof Slot
+ && supplier.get().contains(orChild.child(0))) {
+ changed = true;
+ continue;
+ }
+ newOrChildren.add(orChild);
+ }
+ if (changed) {
+ if (newOrChildren.size() == 1) {
+
predicates.add(withInferredIfSupported(newOrChildren.get(0), expr));
+ } else if (newOrChildren.size() > 1) {
+
predicates.add(ExpressionUtils.or(newOrChildren).withInferred(true));
+ }
+ } else {
+ predicates.add(expr);
+ }
+ } else {
+ predicates.add(expr);
+ }
+ }
+ predicates.removeAll(plan.accept(pullUpAllPredicates, null));
+ return PlanUtils.filterOrSelf(predicates, plan);
+ }
+
+ private Expression withInferredIfSupported(Expression expression,
Expression originExpr) {
+ try {
+ return expression.withInferred(true);
+ } catch (RuntimeException e) {
+ return originExpr;
+ }
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpPredicates.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpPredicates.java
index 8575ab9e5a2..8da895d34ab 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpPredicates.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpPredicates.java
@@ -260,6 +260,10 @@ public class PullUpPredicates extends
PlanVisitor<ImmutableSet<Expression>, Void
break;
}
case LEFT_OUTER_JOIN:
+ predicates.addAll(leftPredicates.get());
+ predicates.addAll(
+
generateNullTolerantPredicates(rightPredicates.get(),
join.right().getOutputSet()));
+ break;
case LEFT_SEMI_JOIN:
case LEFT_ANTI_JOIN:
case NULL_AWARE_LEFT_ANTI_JOIN: {
@@ -267,6 +271,10 @@ public class PullUpPredicates extends
PlanVisitor<ImmutableSet<Expression>, Void
break;
}
case RIGHT_OUTER_JOIN:
+ predicates.addAll(rightPredicates.get());
+ predicates.addAll(
+
generateNullTolerantPredicates(leftPredicates.get(),
join.left().getOutputSet()));
+ break;
case RIGHT_SEMI_JOIN:
case RIGHT_ANTI_JOIN: {
predicates.addAll(rightPredicates.get());
@@ -384,6 +392,30 @@ public class PullUpPredicates extends
PlanVisitor<ImmutableSet<Expression>, Void
return supportAggFunctions.contains(expr.getClass());
}
+ private Set<Expression> generateNullTolerantPredicates(Set<Expression>
predicates, Set<Slot> nullableSlots) {
+ if (predicates.isEmpty() || nullableSlots.isEmpty()) {
+ return predicates;
+ }
+ Set<Expression> tolerant =
Sets.newLinkedHashSetWithExpectedSize(predicates.size());
+ for (Expression predicate : predicates) {
+ Set<Slot> predicateSlots = predicate.getInputSlots();
+ List<Expression> orChildren = new ArrayList<>();
+ if (predicateSlots.size() == 1) {
+ Slot slot = predicateSlots.iterator().next();
+ if (nullableSlots.contains(slot)) {
+ orChildren.add(new IsNull(slot));
+ }
+ }
+ if (!orChildren.isEmpty()) {
+ List<Expression> expandedOr = new ArrayList<>(2);
+ expandedOr.add(predicate);
+ expandedOr.addAll(orChildren);
+ tolerant.add(ExpressionUtils.or(expandedOr));
+ }
+ }
+ return tolerant;
+ }
+
private ImmutableSet<Expression> getFiltersFromUnionChild(LogicalUnion
union, Void context) {
Set<Expression> filters = new LinkedHashSet<>();
for (int i = 0; i < union.getArity(); ++i) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CompoundPredicate.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CompoundPredicate.java
index 13f99839ffb..f2d6a350b16 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CompoundPredicate.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CompoundPredicate.java
@@ -37,7 +37,11 @@ public abstract class CompoundPredicate extends Expression
implements ExpectsInp
private String symbol;
public CompoundPredicate(List<Expression> children, String symbol) {
- super(children);
+ this(children, symbol, false);
+ }
+
+ public CompoundPredicate(List<Expression> children, String symbol, boolean
inferred) {
+ super(children, inferred);
this.symbol = symbol;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Or.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Or.java
index 235c1bc2f0a..b62c0e76b40 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Or.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Or.java
@@ -36,20 +36,28 @@ public class Or extends CompoundPredicate {
* @param right right child of comparison predicate
*/
public Or(Expression left, Expression right) {
+ this(left, right, false);
+ }
+
+ public Or(Expression left, Expression right, boolean inferred) {
this(ExpressionUtils.mergeList(
ExpressionUtils.extractDisjunction(left),
- ExpressionUtils.extractDisjunction(right)));
+ ExpressionUtils.extractDisjunction(right)), inferred);
}
public Or(List<Expression> children) {
- super(children, "OR");
+ this(children, false);
+ }
+
+ public Or(List<Expression> children, boolean inferred) {
+ super(children, "OR", inferred);
Preconditions.checkArgument(children.size() >= 2);
}
@Override
public Expression withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() >= 2);
- return new Or(children);
+ return new Or(children, this.isInferred());
}
@Override
@@ -90,4 +98,9 @@ public class Or extends CompoundPredicate {
}
return flattenChildren;
}
+
+ @Override
+ public Expression withInferred(boolean inferred) {
+ return new Or(children, inferred);
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAggregate.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAggregate.java
index 07a2d1b7d97..9499dc601ca 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAggregate.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAggregate.java
@@ -354,7 +354,7 @@ public class LogicalAggregate<CHILD_TYPE extends Plan>
public LogicalAggregate<Plan>
withChildGroupByAndOutputAndSourceRepeat(List<Expression> groupByExprList,
List<NamedExpression> outputExpressionList, Plan newChild,
-
Optional<LogicalRepeat<?>> sourceRepeat) {
+
Optional<LogicalRepeat<? extends Plan>> sourceRepeat) {
return new LogicalAggregate<>(groupByExprList, outputExpressionList,
normalized, ordinalIsResolved, generated,
hasPushed, withInProjection, sourceRepeat, Optional.empty(),
Optional.empty(), newChild);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplaceTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplaceTest.java
index 98fbbfbec13..ad35028d7b9 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplaceTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/InferPredicateByReplaceTest.java
@@ -152,7 +152,7 @@ public class InferPredicateByReplaceTest {
inputs.add(equalTo);
Set<Expression> result = InferPredicateByReplace.infer(inputs);
- Assertions.assertEquals(2, result.size());
+ Assertions.assertEquals(3, result.size());
}
@Test
diff --git
a/regression-test/data/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.out
b/regression-test/data/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.out
index 2cda3936670..c646bf2e485 100644
---
a/regression-test/data/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.out
+++
b/regression-test/data/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.out
@@ -114,14 +114,16 @@ PhysicalResultSink
--hashJoin[INNER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
----filter(OR[(t1.a < 2),(t1.a > 10)])
------PhysicalOlapScan[extend_infer_t3(t1)]
-----PhysicalOlapScan[extend_infer_t4(t2)]
+----filter(OR[(t2.a < 2),(t2.a > 10)])
+------PhysicalOlapScan[extend_infer_t4(t2)]
-- !test_or2 --
PhysicalResultSink
--hashJoin[INNER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
----filter(OR[(t1.a < 2),(t1.a > 10)])
------PhysicalOlapScan[extend_infer_t3(t1)]
-----PhysicalOlapScan[extend_infer_t4(t2)]
+----filter(OR[(t2.a < 2),(t2.a > 10)])
+------PhysicalOlapScan[extend_infer_t4(t2)]
-- !test_sign_predicate --
PhysicalResultSink
@@ -772,3 +774,116 @@ PhysicalResultSink
-- !pull_up_from_agg --
0
+-- !qt_leftjoin_right_pull_up_shape_shape --
+PhysicalResultSink
+--hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
+------filter((t1.a = 1))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------filter((t2.a = 1))
+--------PhysicalOlapScan[extend_infer_t4(t2)]
+----filter((t3.a = 1))
+------PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_leftjoin_right_pull_up_shape_result --
+
+-- !qt_multi_leftjoin_right_pull_up_shape_shape --
+PhysicalResultSink
+--hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t5.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t4.a = t3.a)) otherCondition=()
+------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+--------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a))
otherCondition=()
+----------filter((t1.a = 1))
+------------PhysicalOlapScan[extend_infer_t3(t1)]
+----------filter((t2.a = 1))
+------------PhysicalOlapScan[extend_infer_t4(t2)]
+--------filter((t3.a = 1))
+----------PhysicalOlapScan[extend_infer_t5(t3)]
+------filter((t4.a = 1))
+--------PhysicalOlapScan[extend_infer_t5(t4)]
+----filter((t5.a = 1))
+------PhysicalOlapScan[extend_infer_t5(t5)]
+
+-- !qt_multi_leftjoin_right_pull_up_shape_result --
+
+-- !qt_leftjoin_right_pull_up_in_shape_shape --
+PhysicalResultSink
+--hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
+------filter(a IN (1, 2))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------filter(a IN (1, 2))
+--------PhysicalOlapScan[extend_infer_t4(t2)]
+----filter(a IN (1, 2))
+------PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_leftjoin_right_pull_up_in_shape_result --
+
+-- !qt_leftjoin_right_pull_up_is_null_shape_shape --
+PhysicalResultSink
+--hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
+------filter(a IS NULL)
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------PhysicalOlapScan[extend_infer_t4(t2)]
+----PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_leftjoin_right_pull_up_is_null_shape_result --
+\N \N 9 3 \N \N \N \N \N \N
\N \N
+\N d2 3 55 \N \N \N \N \N \N
\N \N
+
+-- !qt_leftjoin_right_pull_up_is_not_null_shape_shape --
+PhysicalResultSink
+--hashJoin[LEFT_OUTER_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
+------filter(( not a IS NULL))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------PhysicalOlapScan[extend_infer_t4(t2)]
+----PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_leftjoin_right_pull_up_is_not_null_shape_result --
+0 d2 3 5 0 d2 2 2 \N \N
\N \N
+100 d2 3 5 100 d2 3 \N \N \N
\N \N
+12 \N 9 3 \N \N \N \N \N \N
\N \N
+33 d2 2 5 33 d2 23 5 \N \N
\N \N
+78 \N 9 3 78 d2 23 5 \N \N
\N \N
+
+-- !qt_left_join_inner_shape --
+PhysicalResultSink
+--NestedLoopJoin[INNER_JOIN]
+----NestedLoopJoin[INNER_JOIN]
+------filter((t1.a = 1))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------filter((t2.a = 1))
+--------PhysicalOlapScan[extend_infer_t4(t2)]
+----filter((t3.a = 1))
+------PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_left_join_inner_result --
+
+-- !qt_left_join_semi_shape --
+PhysicalResultSink
+--NestedLoopJoin[LEFT_SEMI_JOIN]
+----NestedLoopJoin[INNER_JOIN]
+------filter((t1.a = 1))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------filter((t2.a = 1))
+--------PhysicalOlapScan[extend_infer_t4(t2)]
+----filter((t3.a = 1))
+------PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_left_join_semi_result --
+
+-- !qt_left_join_anti_shape --
+PhysicalResultSink
+--hashJoin[LEFT_ANTI_JOIN] hashCondition=((t2.a = t3.a)) otherCondition=()
+----hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.a = t2.a)) otherCondition=()
+------filter((t1.a = 1))
+--------PhysicalOlapScan[extend_infer_t3(t1)]
+------filter((t2.a = 1))
+--------PhysicalOlapScan[extend_infer_t4(t2)]
+----filter((t3.a = 1))
+------PhysicalOlapScan[extend_infer_t5(t3)]
+
+-- !qt_left_join_anti_result --
+
diff --git
a/regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_agg.out
b/regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_agg.out
index 330b2e93f2d..81529b85b48 100644
---
a/regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_agg.out
+++
b/regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_agg.out
@@ -46,11 +46,13 @@ PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
----PhysicalQuickSort[LOCAL_SORT]
------hashJoin[INNER_JOIN] hashCondition=((t.col1 = t2.a) and (t.col2 = t2.c))
otherCondition=()
---------hashAgg[GLOBAL]
-----------hashAgg[LOCAL]
-------------filter((test_pull_up_agg_t1.a <= 20) and (test_pull_up_agg_t1.c <
200))
---------------PhysicalOlapScan[test_pull_up_agg_t1]
---------PhysicalOlapScan[test_pull_up_agg_t2(t2)]
+--------filter((t.col1 <= 20) and (t.col2 < 200))
+----------hashAgg[GLOBAL]
+------------hashAgg[LOCAL]
+--------------filter((test_pull_up_agg_t1.a <= 20) and (test_pull_up_agg_t1.c
< 200))
+----------------PhysicalOlapScan[test_pull_up_agg_t1]
+--------filter((t2.a <= 20) and (t2.c < 200))
+----------PhysicalOlapScan[test_pull_up_agg_t2(t2)]
-- !pull_up_from_agg_to_filter_with_same_cond_shape --
PhysicalResultSink
diff --git
a/regression-test/suites/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.groovy
b/regression-test/suites/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.groovy
index b7a6090e901..de730c5b574 100644
---
a/regression-test/suites/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.groovy
+++
b/regression-test/suites/nereids_rules_p0/infer_predicate/extend_infer_equal_predicate.groovy
@@ -377,4 +377,39 @@ suite("extend_infer_equal_predicate") {
qt_pull_up_from_intersect """select a from(select a from (select t1.a from
extend_infer_t3 t1 where t1.a<10 intersect select t2.a from extend_infer_t4 t2
where t2.a<10 ) tt
limit 10) t where a<10 order by 1 ;"""
qt_pull_up_from_agg """select a from (select a from extend_infer_t3 t1
where a<10 group by a limit 10) t where a<10 order by 1"""
+
+ def explain_and_result = { tag, sql ->
+ "qt_${tag}_shape" "explain shape plan ${sql}"
+ "order_qt_${tag}_result" "${sql}"
+ }
+
+ // test left join right table predicate pull up
+ explain_and_result 'qt_leftjoin_right_pull_up_shape', '''
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left join extend_infer_t5 t3 on t2.a= t3.a where t1.a=1;
+ '''
+ // test multi left join right table predicate pull up
+ explain_and_result "qt_multi_leftjoin_right_pull_up_shape", """
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left join extend_infer_t5 t3 on t2.a= t3.a left join extend_infer_t5
t4 on t4.a=t3.a left join extend_infer_t5 t5 on t2.a=t5.a where t1.a=1;
+ """
+ explain_and_result "qt_leftjoin_right_pull_up_in_shape", """
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left join extend_infer_t5 t3 on t2.a= t3.a where t1.a in (1,2);
+ """
+ // is null may be can be inferred but we do not infer it now
+ explain_and_result "qt_leftjoin_right_pull_up_is_null_shape", """
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left join extend_infer_t5 t3 on t2.a= t3.a where t1.a is null;
+ """
+ // is not null may be need not be innfered
+ explain_and_result "qt_leftjoin_right_pull_up_is_not_null_shape", """
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left join extend_infer_t5 t3 on t2.a= t3.a where t1.a is not null;
+ """
+
+ explain_and_result 'qt_left_join_inner', '''
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a inner join extend_infer_t5 t3 on t2.a= t3.a where t1.a=1;
+ '''
+ explain_and_result 'qt_left_join_semi', '''
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left semi join extend_infer_t5 t3 on t2.a= t3.a where t1.a=1;
+ '''
+ explain_and_result 'qt_left_join_anti', '''
+ select * from extend_infer_t3 t1 left join extend_infer_t4 t2 on
t1.a=t2.a left anti join extend_infer_t5 t3 on t2.a= t3.a where t1.a=1;
+ '''
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
