This is an automated email from the ASF dual-hosted git repository. englefly 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 2b914aebb64 [opt](nereids)improve partition prune when Date function is used (#27960) 2b914aebb64 is described below commit 2b914aebb64b7e91afc8c9ef9a54174d29e48d24 Author: minghong <engle...@gmail.com> AuthorDate: Fri Dec 8 21:53:39 2023 +0800 [opt](nereids)improve partition prune when Date function is used (#27960) date func in partition prune --- .../rules/expression/ExpressionOptimization.java | 2 + .../expression/rules/DateFunctionRewrite.java | 151 +++++++++++++++++++++ .../rules/expression/rules/PartitionPruner.java | 2 +- .../rules/PredicateRewriteForPartitionPrune.java | 98 +++++++++++++ .../rules/rewrite/PredicatePropagation.java | 4 +- .../trees/expressions/literal/DateLiteral.java | 29 ++++ .../trees/expressions/literal/DateV2Literal.java | 29 ++++ .../test_date_function_prune.groovy | 77 +++++++++++ 8 files changed, 390 insertions(+), 2 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java index a37e6f4754e..e7b3a308f0f 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java @@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.expression; import org.apache.doris.nereids.rules.expression.rules.ArrayContainToArrayOverlap; import org.apache.doris.nereids.rules.expression.rules.CaseWhenToIf; +import org.apache.doris.nereids.rules.expression.rules.DateFunctionRewrite; import org.apache.doris.nereids.rules.expression.rules.DistinctPredicatesRule; import org.apache.doris.nereids.rules.expression.rules.ExtractCommonFactorRule; import org.apache.doris.nereids.rules.expression.rules.OrToIn; @@ -43,6 +44,7 @@ public class ExpressionOptimization extends ExpressionRewrite { SimplifyInPredicate.INSTANCE, SimplifyDecimalV3Comparison.INSTANCE, SimplifyRange.INSTANCE, + DateFunctionRewrite.INSTANCE, OrToIn.INSTANCE, ArrayContainToArrayOverlap.INSTANCE, CaseWhenToIf.INSTANCE, diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/DateFunctionRewrite.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/DateFunctionRewrite.java new file mode 100644 index 00000000000..688271cc07b --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/DateFunctionRewrite.java @@ -0,0 +1,151 @@ +// 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.expression.rules; + +import org.apache.doris.nereids.rules.expression.AbstractExpressionRewriteRule; +import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext; +import org.apache.doris.nereids.trees.expressions.And; +import org.apache.doris.nereids.trees.expressions.EqualTo; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.GreaterThan; +import org.apache.doris.nereids.trees.expressions.GreaterThanEqual; +import org.apache.doris.nereids.trees.expressions.LessThan; +import org.apache.doris.nereids.trees.expressions.LessThanEqual; +import org.apache.doris.nereids.trees.expressions.functions.scalar.Date; +import org.apache.doris.nereids.trees.expressions.literal.DateLiteral; +import org.apache.doris.nereids.trees.expressions.literal.DateTimeLiteral; +import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal; +import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal; +import org.apache.doris.nereids.types.DateTimeType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +/** + * F: a DateTime or DateTimeV2 column + * Date(F) > 2020-01-01 => F > 2020-01-02 00:00:00 + * Date(F) >= 2020-01-01 => F > 2020-01-01 00:00:00 + * + */ +public class DateFunctionRewrite extends AbstractExpressionRewriteRule { + public static DateFunctionRewrite INSTANCE = new DateFunctionRewrite(); + + @Override + public Expression visitEqualTo(EqualTo equalTo, ExpressionRewriteContext context) { + if (equalTo.left() instanceof Date) { + // V1 + if (equalTo.left().child(0).getDataType() instanceof DateTimeType + && equalTo.right() instanceof DateLiteral) { + DateTimeLiteral lowerBound = ((DateLiteral) equalTo.right()).toBeginOfTheDay(); + DateTimeLiteral upperBound = ((DateLiteral) equalTo.right()).toEndOfTheDay(); + Expression newLeft = equalTo.left().child(0); + return new And(new GreaterThanEqual(newLeft, lowerBound), + new LessThanEqual(newLeft, upperBound)); + } + // V2 + if (equalTo.left().child(0).getDataType() instanceof DateTimeV2Type + && equalTo.right() instanceof DateV2Literal) { + DateTimeV2Literal lowerBound = ((DateV2Literal) equalTo.right()).toBeginOfTheDay(); + DateTimeV2Literal upperBound = ((DateV2Literal) equalTo.right()).toEndOfTheDay(); + Expression newLeft = equalTo.left().child(0); + return new And(new GreaterThanEqual(newLeft, lowerBound), + new LessThanEqual(newLeft, upperBound)); + } + } + return equalTo; + } + + @Override + public Expression visitGreaterThan(GreaterThan greaterThan, ExpressionRewriteContext context) { + if (greaterThan.left() instanceof Date) { + // V1 + if (greaterThan.left().child(0).getDataType() instanceof DateTimeType + && greaterThan.right() instanceof DateLiteral) { + DateTimeLiteral newLiteral = ((DateLiteral) greaterThan.right()).toBeginOfTomorrow(); + return new GreaterThan(greaterThan.left().child(0), newLiteral); + } + + // V2 + if (greaterThan.left().child(0).getDataType() instanceof DateTimeV2Type + && greaterThan.right() instanceof DateV2Literal) { + DateTimeV2Literal newLiteral = ((DateV2Literal) greaterThan.right()).toBeginOfTomorrow(); + return new GreaterThan(greaterThan.left().child(0), newLiteral); + } + } + + return greaterThan; + } + + @Override + public Expression visitGreaterThanEqual(GreaterThanEqual greaterThanEqual, ExpressionRewriteContext context) { + if (greaterThanEqual.left() instanceof Date) { + // V1 + if (greaterThanEqual.left().child(0).getDataType() instanceof DateTimeType + && greaterThanEqual.right() instanceof DateLiteral) { + DateTimeLiteral newLiteral = ((DateLiteral) greaterThanEqual.right()).toBeginOfTheDay(); + return new GreaterThan(greaterThanEqual.left().child(0), newLiteral); + } + + // V2 + if (greaterThanEqual.left().child(0).getDataType() instanceof DateTimeV2Type + && greaterThanEqual.right() instanceof DateV2Literal) { + DateTimeV2Literal newLiteral = ((DateV2Literal) greaterThanEqual.right()).toBeginOfTheDay(); + return new GreaterThan(greaterThanEqual.left().child(0), newLiteral); + } + } + return greaterThanEqual; + } + + @Override + public Expression visitLessThan(LessThan lessThan, ExpressionRewriteContext context) { + if (lessThan.left() instanceof Date) { + // V1 + if (lessThan.left().child(0).getDataType() instanceof DateTimeType + && lessThan.right() instanceof DateLiteral) { + DateTimeLiteral newLiteral = ((DateLiteral) lessThan.right()).toBeginOfTheDay(); + return new LessThan(lessThan.left().child(0), newLiteral); + } + + // V2 + if (lessThan.left().child(0).getDataType() instanceof DateTimeV2Type + && lessThan.right() instanceof DateV2Literal) { + DateTimeV2Literal newLiteral = ((DateV2Literal) lessThan.right()).toBeginOfTheDay(); + return new LessThan(lessThan.left().child(0), newLiteral); + } + } + return lessThan; + } + + @Override + public Expression visitLessThanEqual(LessThanEqual lessThanEqual, ExpressionRewriteContext context) { + if (lessThanEqual.left() instanceof Date) { + // V1 + if (lessThanEqual.left().child(0).getDataType() instanceof DateTimeType + && lessThanEqual.right() instanceof DateLiteral) { + DateTimeLiteral newLiteral = ((DateLiteral) lessThanEqual.right()).toEndOfTheDay(); + return new LessThanEqual(lessThanEqual.left().child(0), newLiteral); + } + + // V2 + if (lessThanEqual.left().child(0).getDataType() instanceof DateTimeV2Type + && lessThanEqual.right() instanceof DateV2Literal) { + DateTimeV2Literal newLiteral = ((DateV2Literal) lessThanEqual.right()).toEndOfTheDay(); + return new LessThanEqual(lessThanEqual.left().child(0), newLiteral); + } + } + return lessThanEqual; + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PartitionPruner.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PartitionPruner.java index ae3a5add083..4c169658348 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PartitionPruner.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PartitionPruner.java @@ -105,7 +105,7 @@ public class PartitionPruner extends DefaultExpressionRewriter<Void> { PartitionTableType partitionTableType) { partitionPredicate = TryEliminateUninterestedPredicates.rewrite( partitionPredicate, ImmutableSet.copyOf(partitionSlots), cascadesContext); - + partitionPredicate = PredicateRewriteForPartitionPrune.rewrite(partitionPredicate, cascadesContext); List<OnePartitionEvaluator> evaluators = idToPartitions.entrySet() .stream() .map(kv -> toPartitionEvaluator(kv.getKey(), kv.getValue(), partitionSlots, cascadesContext, diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PredicateRewriteForPartitionPrune.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PredicateRewriteForPartitionPrune.java new file mode 100644 index 00000000000..c227c89b939 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/PredicateRewriteForPartitionPrune.java @@ -0,0 +1,98 @@ +// 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.expression.rules; + +import org.apache.doris.nereids.CascadesContext; +import org.apache.doris.nereids.trees.expressions.And; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.GreaterThanEqual; +import org.apache.doris.nereids.trees.expressions.InPredicate; +import org.apache.doris.nereids.trees.expressions.LessThanEqual; +import org.apache.doris.nereids.trees.expressions.Or; +import org.apache.doris.nereids.trees.expressions.functions.scalar.Date; +import org.apache.doris.nereids.trees.expressions.literal.DateLiteral; +import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal; +import org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter; +import org.apache.doris.nereids.types.DateTimeType; +import org.apache.doris.nereids.types.DateTimeV2Type; +import org.apache.doris.nereids.util.ExpressionUtils; + +import java.util.ArrayList; +import java.util.List; + +/** + rewrite predicate for partition prune + */ +public class PredicateRewriteForPartitionPrune + extends DefaultExpressionRewriter<CascadesContext> { + public static Expression rewrite(Expression expression, + CascadesContext cascadesContext) { + PredicateRewriteForPartitionPrune rewriter = new PredicateRewriteForPartitionPrune(); + return expression.accept(rewriter, cascadesContext); + } + + /* F: a DateTime or DateTimeV2 column + * Date(F) in (2020-01-02, 2020-01-01) => + * (2020-01-01 24:00:00 >= F >= 2020-01-01 00:00:00) + * or (2020-01-02 24:00:00 >= F >= 2020-01-02 00:00:00) + */ + @Override + public Expression visitInPredicate(InPredicate in, CascadesContext context) { + if (in.getCompareExpr() instanceof Date) { + Expression dateChild = in.getCompareExpr().child(0); + boolean convertable = true; + List<Expression> splitIn = new ArrayList<>(); + // V1 + if (dateChild.getDataType() instanceof DateTimeType) { + for (Expression opt : in.getOptions()) { + if (opt instanceof DateLiteral) { + GreaterThanEqual ge = new GreaterThanEqual(dateChild, ((DateLiteral) opt).toBeginOfTheDay()); + LessThanEqual le = new LessThanEqual(dateChild, ((DateLiteral) opt).toEndOfTheDay()); + splitIn.add(new And(ge, le)); + } else { + convertable = false; + break; + } + } + if (convertable) { + Expression or = ExpressionUtils.combine(Or.class, splitIn); + return or; + } + } else if (dateChild.getDataType() instanceof DateTimeV2Type) { + // V2 + convertable = true; + for (Expression opt : in.getOptions()) { + if (opt instanceof DateLiteral) { + GreaterThanEqual ge = new GreaterThanEqual(dateChild, ((DateV2Literal) opt).toBeginOfTheDay()); + LessThanEqual le = new LessThanEqual(dateChild, ((DateV2Literal) opt).toEndOfTheDay()); + splitIn.add(new And(ge, le)); + } else { + convertable = false; + break; + } + } + if (convertable) { + Expression or = ExpressionUtils.combine(Or.class, splitIn); + return or; + } + } + } + return in; + } + +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PredicatePropagation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PredicatePropagation.java index 9341c3db5c1..ecb1c5499bd 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PredicatePropagation.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PredicatePropagation.java @@ -18,6 +18,7 @@ package org.apache.doris.nereids.rules.rewrite; import org.apache.doris.nereids.parser.NereidsParser; +import org.apache.doris.nereids.rules.expression.rules.DateFunctionRewrite; import org.apache.doris.nereids.rules.expression.rules.SimplifyComparisonPredicate; import org.apache.doris.nereids.trees.expressions.Cast; import org.apache.doris.nereids.trees.expressions.ComparisonPredicate; @@ -127,8 +128,9 @@ public class PredicatePropagation { } ComparisonPredicate newPredicate = (ComparisonPredicate) predicateInfo .comparisonPredicate.withChildren(newLeft, newRight); - return SimplifyComparisonPredicate.INSTANCE + Expression expr = SimplifyComparisonPredicate.INSTANCE .rewrite(TypeCoercionUtils.processComparisonPredicate(newPredicate), null); + return DateFunctionRewrite.INSTANCE.rewrite(expr, null); } private Expression inferOneSide(Expression predicateOneSide, Expression equalLeft, Expression equalRight) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java index c6552778c81..19720b68dc3 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java @@ -392,4 +392,33 @@ public class DateLiteral extends Literal { ? new NullLiteral(DateType.INSTANCE) : new DateLiteral(dateTime.getYear(), dateTime.getMonthValue(), dateTime.getDayOfMonth()); } + + /** + * 2020-01-01 + * @return 2020-01-01 00:00:00 + */ + public DateTimeLiteral toBeginOfTheDay() { + return new DateTimeLiteral(year, month, day, 0, 0, 0); + } + + /** + * 2020-01-01 + * @return 2020-01-01 24:00:00 + */ + public DateTimeLiteral toEndOfTheDay() { + return new DateTimeLiteral(year, month, day, 24, 0, 0); + } + + /** + * 2020-01-01 + * @return 2020-01-02 0:0:0 + */ + public DateTimeLiteral toBeginOfTomorrow() { + Expression tomorrow = plusDays(1); + if (tomorrow instanceof DateLiteral) { + return ((DateLiteral) tomorrow).toBeginOfTheDay(); + } else { + return toEndOfTheDay(); + } + } } diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java index 54de4a76a07..9a25a5241c9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java @@ -75,4 +75,33 @@ public class DateV2Literal extends DateLiteral { ? new NullLiteral(DateV2Type.INSTANCE) : new DateV2Literal(dateTime.getYear(), dateTime.getMonthValue(), dateTime.getDayOfMonth()); } + + /** + * 2020-01-01 + * @return 2020-01-01 24:00:00 + */ + public DateTimeV2Literal toBeginOfTheDay() { + return new DateTimeV2Literal(year, month, day, 0, 0, 0); + } + + /** + * 2020-01-01 + * @return 2020-01-01 00:00:00 + */ + public DateTimeV2Literal toEndOfTheDay() { + return new DateTimeV2Literal(year, month, day, 24, 0, 0); + } + + /** + * 2020-01-01 + * @return 2020-01-02 0:0:0 + */ + public DateTimeV2Literal toBeginOfTomorrow() { + Expression tomorrow = plusDays(1); + if (tomorrow instanceof DateV2Literal) { + return ((DateV2Literal) tomorrow).toBeginOfTheDay(); + } else { + return toEndOfTheDay(); + } + } } diff --git a/regression-test/suites/nereids_rules_p0/partition_prune/test_date_function_prune.groovy b/regression-test/suites/nereids_rules_p0/partition_prune/test_date_function_prune.groovy new file mode 100644 index 00000000000..1523bbb662f --- /dev/null +++ b/regression-test/suites/nereids_rules_p0/partition_prune/test_date_function_prune.groovy @@ -0,0 +1,77 @@ +// 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_date_function_prune") { + String db = context.config.getDbNameByFile(context.file) + sql "use ${db}" + sql "SET enable_nereids_planner=true" + sql "SET enable_fallback_to_original_planner=false" + sql "set partition_pruning_expand_threshold=10;" + sql "drop table if exists dp" + sql """ + CREATE TABLE `dp` ( + `node_name` varchar(100) NULL COMMENT '', + `date_time` datetime NULL COMMENT '', + ) ENGINE=OLAP + DUPLICATE KEY(`node_name`) + COMMENT '' + PARTITION BY RANGE(`date_time`)( + partition p1 values less than ('2020-01-02 00:00:00'), + partition p2 values less than ('2020-01-03 00:00:00'), + partition p3 values less than ('2020-01-04 00:00:00') + ) + DISTRIBUTED BY HASH(`node_name`) BUCKETS 1 + PROPERTIES ( + "replication_num" = "1" + );""" + sql "insert into dp values('a', '2020-01-01 11:00:00'), ('b', '2020-01-02 11:00:00'), ('c', '2020-01-03 11:00:00');" + + explain { + sql "select * from dp where Date(date_time) > '2020-01-02'" + contains("partitions=1/3 (p3)") + } + + explain { + sql "select * from dp where '2020-01-02' < Date(date_time);" + contains("partitions=1/3 (p3)") + } + + explain { + sql "select * from dp where Date(date_time) >= '2020-01-02'" + contains("partitions=2/3 (p2,p3)") + } + + explain { + sql "select * from dp where Date(date_time) < '2020-01-02'" + contains("partitions=1/3 (p1)") + } + + explain { + sql "select * from dp where Date(date_time) <= '2020-01-02'" + contains("partitions=2/3 (p1,p2)") + } + + explain { + sql "select * from dp where Date(date_time) between '2020-01-01' and '2020-01-02'" + contains("partitions=2/3 (p1,p2)") + } + + explain { + sql "select * from dp where Date(date_time) in ('2020-01-01', '2020-01-03')" + contains("partitions=2/3 (p1,p3)") + } +} \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org