This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new d8317f5777c [fix](nereids)fix bug of unnest subuqery with having
clause (#31209)
d8317f5777c is described below
commit d8317f5777c3bf1c2480290783a4f4fe37e5ebd4
Author: starocean999 <[email protected]>
AuthorDate: Wed Feb 21 17:45:33 2024 +0800
[fix](nereids)fix bug of unnest subuqery with having clause (#31209)
pick from master #31152
---
.../doris/nereids/jobs/executor/Rewriter.java | 47 ++++++++++++-
.../rewrite/PushdownFilterThroughProject.java | 76 +++++++++++++++++-----
.../data/nereids_p0/subquery/test_subquery.out | 4 ++
.../nereids_p0/subquery/test_subquery.groovy | 14 ++++
4 files changed, 124 insertions(+), 17 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index 881750f23dd..00f8c0c7ac8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -92,6 +92,7 @@ import
org.apache.doris.nereids.rules.rewrite.PushConjunctsIntoOdbcScan;
import org.apache.doris.nereids.rules.rewrite.PushFilterInsideJoin;
import org.apache.doris.nereids.rules.rewrite.PushProjectIntoOneRowRelation;
import org.apache.doris.nereids.rules.rewrite.PushProjectThroughUnion;
+import org.apache.doris.nereids.rules.rewrite.PushdownFilterThroughAggregation;
import org.apache.doris.nereids.rules.rewrite.PushdownFilterThroughProject;
import org.apache.doris.nereids.rules.rewrite.PushdownLimit;
import org.apache.doris.nereids.rules.rewrite.PushdownLimitDistinctThroughJoin;
@@ -149,7 +150,51 @@ public class Rewriter extends AbstractBatchJobExecutor {
// after doing NormalizeAggregate in analysis job
// we need run the following 2 rules to make
AGG_SCALAR_SUBQUERY_TO_WINDOW_FUNCTION work
bottomUp(new PullUpProjectUnderApply()),
- topDown(new PushdownFilterThroughProject()),
+ topDown(
+ /*
+ * for subquery unnest, we need hand sql like
+ *
+ * SELECT *
+ * FROM table1 AS t1
+ * WHERE EXISTS
+ * (SELECT `pk`
+ * FROM table2 AS t2
+ * WHERE t1.pk = t2 .pk
+ * GROUP BY t2.pk
+ * HAVING t2.pk > 0) ;
+ *
+ * before:
+ * apply
+ * / \
+ * child Filter(t2.pk > 0)
+ * |
+ * Project(t2.pk)
+ * |
+ * agg
+ * |
+ * Project(t2.pk)
+ * |
+ * Filter(t1.pk=t2.pk)
+ * |
+ * child
+ *
+ * after:
+ * apply
+ * / \
+ * child agg
+ * |
+ * Project(t2.pk)
+ * |
+ * Filter(t1.pk=t2.pk and t2.pk >0)
+ * |
+ * child
+ *
+ * then
PullUpCorrelatedFilterUnderApplyAggregateProject rule can match the node pattern
+ */
+ new PushdownFilterThroughAggregation(),
+ new PushdownFilterThroughProject(),
+ new MergeFilters()
+ ),
custom(RuleType.AGG_SCALAR_SUBQUERY_TO_WINDOW_FUNCTION,
AggScalarSubQueryToWindowFunction::new),
bottomUp(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushdownFilterThroughProject.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushdownFilterThroughProject.java
index a0d64b1a609..cc71d1fa627 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushdownFilterThroughProject.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushdownFilterThroughProject.java
@@ -17,18 +17,24 @@
package org.apache.doris.nereids.rules.rewrite;
+import org.apache.doris.common.Pair;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.WindowExpression;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.util.ExpressionUtils;
+import org.apache.doris.nereids.util.PlanUtils;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Sets;
import java.util.List;
+import java.util.Set;
/**
* Push down filter through project.
@@ -51,27 +57,65 @@ public class PushdownFilterThroughProject implements
RewriteRuleFactory {
logicalFilter(logicalLimit(logicalProject()))
.whenNot(filter ->
filter.child().child().getProjects().stream()
.anyMatch(expr ->
expr.anyMatch(WindowExpression.class::isInstance)))
- .then(filter -> {
- LogicalLimit<LogicalProject<Plan>> limit =
filter.child();
- LogicalProject<Plan> project = limit.child();
-
- return
project.withProjectsAndChild(project.getProjects(),
- new LogicalFilter<>(
-
ExpressionUtils.replace(filter.getConjuncts(),
-
project.getAliasToProducer()),
-
limit.withChildren(project.child())));
-
}).toRule(RuleType.PUSHDOWN_FILTER_THROUGH_PROJECT_UNDER_LIMIT)
+
.then(PushdownFilterThroughProject::pushdownFilterThroughLimitProject)
+
.toRule(RuleType.PUSHDOWN_FILTER_THROUGH_PROJECT_UNDER_LIMIT)
);
}
/** pushdown Filter through project */
- public static Plan
pushdownFilterThroughProject(LogicalFilter<LogicalProject<Plan>> filter) {
+ private static Plan
pushdownFilterThroughProject(LogicalFilter<LogicalProject<Plan>> filter) {
LogicalProject<Plan> project = filter.child();
- return project.withChildren(
+ Set<Slot> childOutputs = project.getOutputSet();
+ // we need run this rule before subquey unnesting
+ // therefore the conjuncts may contain slots from outer query
+ // we should only push down conjuncts without any outer query's slot
+ // so we split the conjuncts into two parts:
+ // splitConjuncts.first -> conjuncts having outer query slots which
should NOT be pushed down
+ // splitConjuncts.second -> conjuncts without any outer query slots
which should be pushed down
+ Pair<Set<Expression>, Set<Expression>> splitConjuncts =
+ splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs);
+ if (splitConjuncts.second.isEmpty()) {
+ // all conjuncts contain outer query's slots, no conjunct can be
pushed down
+ // just return unchanged plan
+ return null;
+ }
+ project = (LogicalProject<Plan>) project.withChildren(new
LogicalFilter<>(
+ ExpressionUtils.replace(splitConjuncts.second,
project.getAliasToProducer()),
+ project.child()));
+ return PlanUtils.filterOrSelf(splitConjuncts.first, project);
+ }
+
+ private static Plan pushdownFilterThroughLimitProject(
+ LogicalFilter<LogicalLimit<LogicalProject<Plan>>> filter) {
+ LogicalLimit<LogicalProject<Plan>> limit = filter.child();
+ LogicalProject<Plan> project = limit.child();
+ Set<Slot> childOutputs = project.getOutputSet();
+ // split the conjuncts by child's output
+ Pair<Set<Expression>, Set<Expression>> splitConjuncts =
+ splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs);
+ if (splitConjuncts.second.isEmpty()) {
+ return null;
+ }
+ project = project.withProjectsAndChild(project.getProjects(),
new LogicalFilter<>(
- ExpressionUtils.replace(filter.getConjuncts(),
project.getAliasToProducer()),
- project.child()
- )
- );
+ ExpressionUtils.replace(splitConjuncts.second,
+ project.getAliasToProducer()),
+ limit.withChildren(project.child())));
+ return PlanUtils.filterOrSelf(splitConjuncts.first, project);
+ }
+
+ private static Pair<Set<Expression>, Set<Expression>>
splitConjunctsByChildOutput(
+ Set<Expression> conjuncts, Set<Slot> childOutputs) {
+ Set<Expression> pushDownPredicates = Sets.newLinkedHashSet();
+ Set<Expression> remainPredicates = Sets.newLinkedHashSet();
+ conjuncts.forEach(conjunct -> {
+ Set<Slot> conjunctSlots = conjunct.getInputSlots();
+ if (childOutputs.containsAll(conjunctSlots)) {
+ pushDownPredicates.add(conjunct);
+ } else {
+ remainPredicates.add(conjunct);
+ }
+ });
+ return Pair.of(remainPredicates, pushDownPredicates);
}
}
diff --git a/regression-test/data/nereids_p0/subquery/test_subquery.out
b/regression-test/data/nereids_p0/subquery/test_subquery.out
index 004aa0bcad0..7e44c264f5b 100644
--- a/regression-test/data/nereids_p0/subquery/test_subquery.out
+++ b/regression-test/data/nereids_p0/subquery/test_subquery.out
@@ -30,3 +30,7 @@ true 15 1992 3021 11011920 0.000 true
9999-12-12 2015-04-02T00:00 3.141592653 2
1 9
2 \N
+-- !select61 --
+
+-- !select62 --
+
diff --git a/regression-test/suites/nereids_p0/subquery/test_subquery.groovy
b/regression-test/suites/nereids_p0/subquery/test_subquery.groovy
index e84ceaa9682..1c8078ebc6f 100644
--- a/regression-test/suites/nereids_p0/subquery/test_subquery.groovy
+++ b/regression-test/suites/nereids_p0/subquery/test_subquery.groovy
@@ -243,4 +243,18 @@ suite("test_subquery") {
sql """drop table if exists table_20_undef_undef"""
sql """drop table if exists table_9_undef_undef"""
+ sql "drop table if exists t1"
+ sql """create table t1
+ (k1 bigint, k2 bigint)
+ ENGINE=OLAP
+ DUPLICATE KEY(k1, k2)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(k2) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );"""
+ sql """insert into t1 values (1,null),(null,1),(1,2), (null,2),(1,3),
(2,4), (2,5), (3,3), (3,4), (20,2), (22,3), (24,4),(null,null);"""
+ qt_select61 """SELECT * FROM t1 AS t1 WHERE EXISTS (SELECT k1 FROM t1 AS
t2 WHERE t1.k1 <> t2.k1 + 7 GROUP BY k1 HAVING k1 >= 100);"""
+ qt_select62 """select * from t1 left semi join ( select * from t1 where
t1.k1 < -1 ) l on true;"""
+ sql "drop table if exists t1"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
