[GitHub] [doris] EmmyMiao87 commented on a diff in pull request #10462: [Enhancement](Nereids) push down predicate through join

[GitBox <gi...@apache.org>]

EmmyMiao87 commented on code in PR #10462:
URL: https://github.com/apache/doris/pull/10462#discussion_r909324956


##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {

Review Comment:
   Can't find where this rule is registered



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {
+
+    @Override
+    public Rule<Plan> build() {
+        return logicalFilter(logicalJoin()).then(plan -> {
+
+            Expression filterPredicates = plan.operator.getPredicates();
+            Optional<Expression> onPredicates = plan.child().operator.getCondition();
+
+            List<Slot> leftInput = plan.child().left().getOutput();
+            List<Slot> rightInput = plan.child().right().getOutput();
+
+            List<Expression> joinConditions = Lists.newArrayList();
+            List<Expression> otherConjuncts = Lists.newArrayList();
+
+            Expression onCondition = Literal.TRUE_LITERAL;
+            if (onPredicates.isPresent()) {
+                onCondition = onPredicates.get();
+            }
+            ExpressionUtils.extractConjunct(ExpressionUtils.add(onCondition, filterPredicates)).forEach(predicate -> {
+                if (Objects.nonNull(getJoinCondition(predicate, leftInput, rightInput))) {
+                    joinConditions.add(predicate);
+                } else {
+                    otherConjuncts.add(predicate);
+                }
+            });
+
+            List<Expression> leftPredicates = Lists.newArrayList();
+            List<Expression> rightPredicates = Lists.newArrayList();
+
+            for (Expression conjunct : otherConjuncts) {
+                Set<Slot> slots = SlotExtractor.extractSlot(conjunct);
+
+                if (slots.isEmpty()) {
+                    leftPredicates.add(conjunct);
+                    rightPredicates.add(conjunct);
+                    continue;
+                }
+                if (leftInput.containsAll(slots)) {
+                    leftPredicates.add(conjunct);
+                }
+                if (rightInput.containsAll(slots)) {
+                    rightPredicates.add(conjunct);
+                }
+            }
+            otherConjuncts.removeAll(leftPredicates);
+            otherConjuncts.removeAll(rightPredicates);
+
+            joinConditions.addAll(otherConjuncts);
+
+            return pushDownPredicate(plan.child(), joinConditions, leftPredicates, rightPredicates);
+        }).toRule(RuleType.PUSH_DOWN_PREDICATE_THROUGH_JOIN);
+    }
+
+    private Plan pushDownPredicate(LogicalBinaryPlan<LogicalJoin, GroupPlan, GroupPlan> joinPlan,
+            List<Expression> joinConditions, List<Expression> leftPredicates, List<Expression> rightPredicates) {
+
+        Expression left = ExpressionUtils.add(leftPredicates);
+        Expression right = ExpressionUtils.add(rightPredicates);
+        ExpressionRuleExecutor exprRewriter = new ExpressionRuleExecutor();

Review Comment:
   Whether the rules related to expressions should be **split into two types** in the future.
   One is the normalization rule on the original query. For example, ```BetweenToCompoundRule```, ```NormalizeExpressionRule``` etc.
   One is optimization rules. Such as common factor extraction, deduplication etc.
   
   



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {
+
+    @Override
+    public Rule<Plan> build() {
+        return logicalFilter(logicalJoin()).then(plan -> {
+
+            Expression filterPredicates = plan.operator.getPredicates();
+            Optional<Expression> onPredicates = plan.child().operator.getCondition();
+
+            List<Slot> leftInput = plan.child().left().getOutput();
+            List<Slot> rightInput = plan.child().right().getOutput();
+
+            List<Expression> joinConditions = Lists.newArrayList();
+            List<Expression> otherConjuncts = Lists.newArrayList();
+
+            Expression onCondition = Literal.TRUE_LITERAL;
+            if (onPredicates.isPresent()) {
+                onCondition = onPredicates.get();
+            }
+            ExpressionUtils.extractConjunct(ExpressionUtils.add(onCondition, filterPredicates)).forEach(predicate -> {
+                if (Objects.nonNull(getJoinCondition(predicate, leftInput, rightInput))) {
+                    joinConditions.add(predicate);
+                } else {
+                    otherConjuncts.add(predicate);
+                }
+            });
+
+            List<Expression> leftPredicates = Lists.newArrayList();
+            List<Expression> rightPredicates = Lists.newArrayList();
+
+            for (Expression conjunct : otherConjuncts) {
+                Set<Slot> slots = SlotExtractor.extractSlot(conjunct);
+
+                if (slots.isEmpty()) {
+                    leftPredicates.add(conjunct);
+                    rightPredicates.add(conjunct);
+                    continue;
+                }
+                if (leftInput.containsAll(slots)) {
+                    leftPredicates.add(conjunct);
+                }
+                if (rightInput.containsAll(slots)) {
+                    rightPredicates.add(conjunct);
+                }
+            }
+            otherConjuncts.removeAll(leftPredicates);
+            otherConjuncts.removeAll(rightPredicates);
+
+            joinConditions.addAll(otherConjuncts);
+
+            return pushDownPredicate(plan.child(), joinConditions, leftPredicates, rightPredicates);
+        }).toRule(RuleType.PUSH_DOWN_PREDICATE_THROUGH_JOIN);
+    }
+
+    private Plan pushDownPredicate(LogicalBinaryPlan<LogicalJoin, GroupPlan, GroupPlan> joinPlan,
+            List<Expression> joinConditions, List<Expression> leftPredicates, List<Expression> rightPredicates) {
+
+        Expression left = ExpressionUtils.add(leftPredicates);
+        Expression right = ExpressionUtils.add(rightPredicates);
+        ExpressionRuleExecutor exprRewriter = new ExpressionRuleExecutor();
+        Plan leftPlan = joinPlan.left();

Review Comment:
   Predicate pushdown should theoretically trigger expression rewriting rules again. But It actually only needs to call the second type rules.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {
+
+    @Override
+    public Rule<Plan> build() {
+        return logicalFilter(logicalJoin()).then(plan -> {
+
+            Expression filterPredicates = plan.operator.getPredicates();
+            Optional<Expression> onPredicates = plan.child().operator.getCondition();
+
+            List<Slot> leftInput = plan.child().left().getOutput();
+            List<Slot> rightInput = plan.child().right().getOutput();
+
+            List<Expression> joinConditions = Lists.newArrayList();
+            List<Expression> otherConjuncts = Lists.newArrayList();
+
+            Expression onCondition = Literal.TRUE_LITERAL;
+            if (onPredicates.isPresent()) {
+                onCondition = onPredicates.get();
+            }
+            ExpressionUtils.extractConjunct(ExpressionUtils.add(onCondition, filterPredicates)).forEach(predicate -> {
+                if (Objects.nonNull(getJoinCondition(predicate, leftInput, rightInput))) {
+                    joinConditions.add(predicate);
+                } else {
+                    otherConjuncts.add(predicate);
+                }
+            });
+
+            List<Expression> leftPredicates = Lists.newArrayList();
+            List<Expression> rightPredicates = Lists.newArrayList();
+
+            for (Expression conjunct : otherConjuncts) {

Review Comment:
   When pushing down the predicate of filter below join, you need to consider the type of join.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {

Review Comment:
   Please include a clear real-world example in the comments.
   Including the original query of the query, the original plan tree of the query, and the transformed new plan tree.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {
+
+    @Override
+    public Rule<Plan> build() {
+        return logicalFilter(logicalJoin()).then(plan -> {
+
+            Expression filterPredicates = plan.operator.getPredicates();
+            Optional<Expression> onPredicates = plan.child().operator.getCondition();
+
+            List<Slot> leftInput = plan.child().left().getOutput();
+            List<Slot> rightInput = plan.child().right().getOutput();
+
+            List<Expression> joinConditions = Lists.newArrayList();
+            List<Expression> otherConjuncts = Lists.newArrayList();
+
+            Expression onCondition = Literal.TRUE_LITERAL;
+            if (onPredicates.isPresent()) {
+                onCondition = onPredicates.get();
+            }
+            ExpressionUtils.extractConjunct(ExpressionUtils.add(onCondition, filterPredicates)).forEach(predicate -> {
+                if (Objects.nonNull(getJoinCondition(predicate, leftInput, rightInput))) {

Review Comment:
   The fusion of join conditions cannot simply be extracted directly from the where and on clauses. 
   For example, the where predicate of outer join cannot be directly used as the condition of on. If it does, the result will be wrong.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later

Review Comment:
   The predicate pushdown rules on the on clause are completely different from the predicate pushdown rules in the where clause.
   What you're providing here is predicate pushdown in the where clause, so I think your comment is wrong.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later
+ */
+public class PushPredicateThroughJoin extends OneRewriteRuleFactory {
+
+    @Override
+    public Rule<Plan> build() {
+        return logicalFilter(logicalJoin()).then(plan -> {
+
+            Expression filterPredicates = plan.operator.getPredicates();
+            Optional<Expression> onPredicates = plan.child().operator.getCondition();
+
+            List<Slot> leftInput = plan.child().left().getOutput();
+            List<Slot> rightInput = plan.child().right().getOutput();
+
+            List<Expression> joinConditions = Lists.newArrayList();
+            List<Expression> otherConjuncts = Lists.newArrayList();
+
+            Expression onCondition = Literal.TRUE_LITERAL;
+            if (onPredicates.isPresent()) {
+                onCondition = onPredicates.get();
+            }
+            ExpressionUtils.extractConjunct(ExpressionUtils.add(onCondition, filterPredicates)).forEach(predicate -> {
+                if (Objects.nonNull(getJoinCondition(predicate, leftInput, rightInput))) {
+                    joinConditions.add(predicate);
+                } else {
+                    otherConjuncts.add(predicate);
+                }
+            });
+
+            List<Expression> leftPredicates = Lists.newArrayList();
+            List<Expression> rightPredicates = Lists.newArrayList();
+
+            for (Expression conjunct : otherConjuncts) {
+                Set<Slot> slots = SlotExtractor.extractSlot(conjunct);
+
+                if (slots.isEmpty()) {
+                    leftPredicates.add(conjunct);
+                    rightPredicates.add(conjunct);
+                    continue;
+                }
+                if (leftInput.containsAll(slots)) {
+                    leftPredicates.add(conjunct);
+                }
+                if (rightInput.containsAll(slots)) {
+                    rightPredicates.add(conjunct);
+                }
+            }
+            otherConjuncts.removeAll(leftPredicates);
+            otherConjuncts.removeAll(rightPredicates);
+
+            joinConditions.addAll(otherConjuncts);
+
+            return pushDownPredicate(plan.child(), joinConditions, leftPredicates, rightPredicates);

Review Comment:
   There are actually three predicate pushdowns related to join
   1. The filter conditions are pushed up to join
   2. The conditions of filter are pushed directly to join
   3. The join condition is pushed to the following
   
   where 1 is the basis of 2,3. Your pr only implements 1 and 2.
   According to your implementation, the pushdown of these three predicates should be in one Rule rather than split into three Rules. I'm fine with that. 
   But you need to clearly represent these three different stages in the code (such as separate function processing).



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/PushPredicateThroughJoin.java:
##########
@@ -0,0 +1,150 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite.logical;
+
+import org.apache.doris.nereids.operators.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.operators.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.expression.rewrite.ExpressionRuleExecutor;
+import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Literal;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotExtractor;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalBinaryPlan;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Push the predicate in the LogicalFilter or LogicalJoin to the join children.
+ * todo: Now, only support eq on condition, support other on condition later

Review Comment:
   ```suggestion
    * todo: Now, only support eq condition in where clause, support other condition later
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org