This is an automated email from the ASF dual-hosted git repository.
starocean999 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 e0639d7a9b9 [feature](nereids) add expression transformation rule
simplify_conditional_function (#37613)
e0639d7a9b9 is described below
commit e0639d7a9b9bc5de24b2dcd60da889c834faafe6
Author: feiniaofeiafei <53502832+feiniaofeia...@users.noreply.github.com>
AuthorDate: Tue Oct 15 12:26:03 2024 +0800
[feature](nereids) add expression transformation rule
simplify_conditional_function (#37613)
```sql
coalesce(null,null,expr,...) => coalesce(expr,...)
coalesce(expr1(not null able ), expr2, ...., expr_n) => expr1
coalesce(null,null) => null
coalesce(expr1) => expr1
nvl(null,R) => R
nvl(L(not-nullable ),R) => L
nullif(null, R) => Null
nullif(L, null) => Null
```
---
.../doris/nereids/jobs/executor/Rewriter.java | 6 +-
.../NullableDependentExpressionRewrite.java | 46 +++++++
.../rules/SimplifyConditionalFunction.java | 106 ++++++++++++++++
.../rules/SimplifyConditionalFunctionTest.java | 111 +++++++++++++++++
.../data/nereids_hint_tpcds_p0/shape/query41.out | 2 +-
.../simplify_conditional_function.out | 133 +++++++++++++++++++++
.../shape/query41.out | 2 +-
.../noStatsRfPrune/query41.out | 2 +-
.../no_stats_shape/query41.out | 2 +-
.../rf_prune/query41.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query41.out | 2 +-
.../shape/query41.out | 2 +-
.../tpcds_sf100/noStatsRfPrune/query41.out | 2 +-
.../tpcds_sf100/no_stats_shape/query41.out | 2 +-
.../new_shapes_p0/tpcds_sf100/rf_prune/query41.out | 2 +-
.../new_shapes_p0/tpcds_sf100/shape/query41.out | 2 +-
.../new_shapes_p0/tpcds_sf1000/shape/query41.out | 2 +-
.../simplify_conditional_function.groovy | 52 ++++++++
18 files changed, 465 insertions(+), 13 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 9b33f94c4c2..099765b5160 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
@@ -31,6 +31,7 @@ import
org.apache.doris.nereids.rules.analysis.NormalizeAggregate;
import org.apache.doris.nereids.rules.expression.CheckLegalityAfterRewrite;
import
org.apache.doris.nereids.rules.expression.ExpressionNormalizationAndOptimization;
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
+import
org.apache.doris.nereids.rules.expression.NullableDependentExpressionRewrite;
import org.apache.doris.nereids.rules.expression.QueryColumnCollector;
import org.apache.doris.nereids.rules.rewrite.AddDefaultLimit;
import org.apache.doris.nereids.rules.rewrite.AddProjectForJoin;
@@ -579,7 +580,10 @@ public class Rewriter extends AbstractBatchJobExecutor {
),
topic("whole plan check",
custom(RuleType.ADJUST_NULLABLE,
AdjustNullable::new)
- )
+ ),
+ // NullableDependentExpressionRewrite need to be done
after nullable fixed
+ topic("condition function", bottomUp(ImmutableList.of(
+ new NullableDependentExpressionRewrite())))
));
return rewriteJobs;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/NullableDependentExpressionRewrite.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/NullableDependentExpressionRewrite.java
new file mode 100644
index 00000000000..558e3884739
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/NullableDependentExpressionRewrite.java
@@ -0,0 +1,46 @@
+// 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;
+
+import org.apache.doris.nereids.annotation.DependsRules;
+import
org.apache.doris.nereids.rules.analysis.AdjustAggregateNullableForEmptySet;
+import
org.apache.doris.nereids.rules.expression.rules.SimplifyConditionalFunction;
+import org.apache.doris.nereids.rules.rewrite.AdjustNullable;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**NullableDependentExpressionRewrite*/
+@DependsRules({
+ AdjustNullable.class,
+ AdjustAggregateNullableForEmptySet.class
+})
+public class NullableDependentExpressionRewrite extends ExpressionRewrite {
+ public static final List<ExpressionRewriteRule> OPTIMIZE_REWRITE_RULES =
ImmutableList.of(
+ bottomUp(
+ SimplifyConditionalFunction.INSTANCE
+ )
+ );
+
+ private static final ExpressionRuleExecutor EXECUTOR = new
ExpressionRuleExecutor(OPTIMIZE_REWRITE_RULES);
+
+ public NullableDependentExpressionRewrite() {
+ super(EXECUTOR);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunction.java
new file mode 100644
index 00000000000..359cb887fe5
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunction.java
@@ -0,0 +1,106 @@
+// 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.ExpressionPatternMatcher;
+import org.apache.doris.nereids.rules.expression.ExpressionPatternRuleFactory;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Coalesce;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.NullIf;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Nvl;
+import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**SimplifyConditionalFunction*/
+public class SimplifyConditionalFunction implements
ExpressionPatternRuleFactory {
+ public static SimplifyConditionalFunction INSTANCE = new
SimplifyConditionalFunction();
+
+ @Override
+ public List<ExpressionPatternMatcher<? extends Expression>> buildRules() {
+ return ImmutableList.of(
+
matchesType(Coalesce.class).then(SimplifyConditionalFunction::rewriteCoalesce),
+
matchesType(Nvl.class).then(SimplifyConditionalFunction::rewriteNvl),
+
matchesType(NullIf.class).then(SimplifyConditionalFunction::rewriteNullIf)
+ );
+ }
+
+ /*
+ * coalesce(null,null,expr,...) => coalesce(expr,...)
+ * coalesce(expr1(not null able ), expr2, ...., expr_n) => expr1
+ * coalesce(null,null) => null
+ * coalesce(expr1) => expr1
+ * */
+ private static Expression rewriteCoalesce(Coalesce expression) {
+ if (1 == expression.arity()) {
+ return expression.child(0);
+ }
+ if (!(expression.child(0) instanceof NullLiteral) &&
expression.child(0).nullable()) {
+ return expression;
+ }
+ ImmutableList.Builder<Expression> childBuilder =
ImmutableList.builder();
+ for (int i = 0; i < expression.arity(); i++) {
+ Expression child = expression.children().get(i);
+ if (child instanceof NullLiteral) {
+ continue;
+ }
+ if (!child.nullable()) {
+ return child;
+ } else {
+ for (int j = i; j < expression.arity(); j++) {
+ childBuilder.add(expression.children().get(j));
+ }
+ break;
+ }
+ }
+ List<Expression> newChildren = childBuilder.build();
+ if (newChildren.isEmpty()) {
+ return new NullLiteral(expression.getDataType());
+ } else {
+ return expression.withChildren(newChildren);
+ }
+ }
+
+ /*
+ * nvl(null,R) => R
+ * nvl(L(not-nullable ),R) => L
+ * */
+ private static Expression rewriteNvl(Nvl nvl) {
+ if (nvl.child(0) instanceof NullLiteral) {
+ return nvl.child(1);
+ }
+ if (!nvl.child(0).nullable()) {
+ return nvl.child(0);
+ }
+ return nvl;
+ }
+
+ /*
+ * nullif(null, R) => Null
+ * nullif(L, null) => Null
+ */
+ private static Expression rewriteNullIf(NullIf nullIf) {
+ if (nullIf.child(0) instanceof NullLiteral || nullIf.child(1)
instanceof NullLiteral) {
+ return nullIf.child(0);
+ } else {
+ return nullIf;
+ }
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunctionTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunctionTest.java
new file mode 100644
index 00000000000..33c62f142c2
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyConditionalFunctionTest.java
@@ -0,0 +1,111 @@
+// 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.ExpressionRewriteTestHelper;
+import org.apache.doris.nereids.rules.expression.ExpressionRuleExecutor;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Coalesce;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.NullIf;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Nvl;
+import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
+import org.apache.doris.nereids.types.BooleanType;
+import org.apache.doris.nereids.types.StringType;
+import org.apache.doris.nereids.types.VarcharType;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.jupiter.api.Test;
+
+public class SimplifyConditionalFunctionTest extends
ExpressionRewriteTestHelper {
+ @Test
+ public void testCoalesce() {
+ executor = new
ExpressionRuleExecutor(ImmutableList.of(bottomUp((SimplifyConditionalFunction.INSTANCE))));
+ SlotReference slot = new SlotReference("a", StringType.INSTANCE, true);
+ SlotReference nonNullableSlot = new SlotReference("b",
StringType.INSTANCE, false);
+
+ // coalesce(null, null, nullable_slot) -> nullable_slot
+ assertRewrite(new Coalesce(NullLiteral.INSTANCE, NullLiteral.INSTANCE,
slot), slot);
+
+ // coalesce(null, null, nullable_slot, slot) ->
coalesce(nullable_slot, slot)
+ assertRewrite(new Coalesce(NullLiteral.INSTANCE, NullLiteral.INSTANCE,
slot, slot),
+ new Coalesce(slot, slot));
+
+ // coalesce(null, null, non-nullable_slot, slot) -> non-nullable_slot
+ assertRewrite(new Coalesce(NullLiteral.INSTANCE, NullLiteral.INSTANCE,
nonNullableSlot, slot),
+ nonNullableSlot);
+
+ // coalesce(non-nullable_slot, ...) -> non-nullable_slot
+ assertRewrite(new Coalesce(nonNullableSlot, NullLiteral.INSTANCE,
nonNullableSlot, slot),
+ nonNullableSlot);
+
+ // coalesce(nullable_slot, slot) -> coalesce(nullable_slot, slot)
+ assertRewrite(new Coalesce(slot, nonNullableSlot), new Coalesce(slot,
nonNullableSlot));
+
+ // coalesce(null, null) -> null
+ assertRewrite(new Coalesce(NullLiteral.INSTANCE,
NullLiteral.INSTANCE), new NullLiteral(BooleanType.INSTANCE));
+
+ // coalesce(null) -> null
+ assertRewrite(new Coalesce(NullLiteral.INSTANCE), new
NullLiteral(BooleanType.INSTANCE));
+
+ // coalesce(non-nullable_slot) -> non-nullable_slot
+ assertRewrite(new Coalesce(nonNullableSlot), nonNullableSlot);
+
+ // coalesce(non-nullable_slot) -> non-nullable_slot
+ assertRewrite(new Coalesce(slot), slot);
+
+ // coalesce(null, nullable_slot, literal) -> coalesce(nullable_slot,
slot, literal)
+ assertRewrite(new Coalesce(slot, nonNullableSlot), new Coalesce(slot,
nonNullableSlot));
+ }
+
+ @Test
+ public void testNvl() {
+ executor = new
ExpressionRuleExecutor(ImmutableList.of(bottomUp((SimplifyConditionalFunction.INSTANCE))));
+ SlotReference slot = new SlotReference("a", StringType.INSTANCE, true);
+ SlotReference nonNullableSlot = new SlotReference("b",
StringType.INSTANCE, false);
+ // nvl(null, nullable_slot) -> nullable_slot
+ assertRewrite(new Nvl(NullLiteral.INSTANCE, slot), slot);
+
+ // nvl(null, non-nullable_slot) -> non-nullable_slot
+ assertRewrite(new Nvl(NullLiteral.INSTANCE, nonNullableSlot),
nonNullableSlot);
+
+ // nvl(nullable_slot, nullable_slot) -> nvl(nullable_slot,
nullable_slot)
+ assertRewrite(new Nvl(slot, nonNullableSlot), new Nvl(slot,
nonNullableSlot));
+
+ // nvl(non-nullable_slot, null) -> non-nullable_slot
+ assertRewrite(new Nvl(nonNullableSlot, NullLiteral.INSTANCE),
nonNullableSlot);
+
+ // nvl(null, null) -> null
+ assertRewrite(new Nvl(NullLiteral.INSTANCE, NullLiteral.INSTANCE), new
NullLiteral(BooleanType.INSTANCE));
+ }
+
+ @Test
+ public void testNullIf() {
+ executor = new
ExpressionRuleExecutor(ImmutableList.of(bottomUp((SimplifyConditionalFunction.INSTANCE))));
+ SlotReference slot = new SlotReference("a", StringType.INSTANCE, true);
+ SlotReference nonNullableSlot = new SlotReference("b",
StringType.INSTANCE, false);
+ // nullif(null, slot) -> null
+ assertRewrite(new NullIf(NullLiteral.INSTANCE, slot), new
NullLiteral(VarcharType.SYSTEM_DEFAULT));
+
+ // nullif(nullable_slot, null) -> slot
+ assertRewrite(new NullIf(slot, NullLiteral.INSTANCE), slot);
+
+ // nullif(non-nullable_slot, null) -> non-nullable_slot
+ assertRewrite(new NullIf(nonNullableSlot, NullLiteral.INSTANCE),
nonNullableSlot);
+ }
+
+}
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query41.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query41.out
index a1be484e284..c870e728a39 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query41.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 744) and (i1.i_manufact_id >=
704))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.out
b/regression-test/data/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.out
new file mode 100644
index 00000000000..91f9110804b
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.out
@@ -0,0 +1,133 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_coalesce_null_begin1 --
+ab ab \N
+abc abc 2.7
+ccd ccd 7.8
+qwe qwe 8.0
+
+-- !test_coalesce_null_begin2 --
+ab
+abc
+ccd
+qwe
+
+-- !test_coalesce_null_begin3 --
+1
+2
+9
+qwe
+
+-- !test_coalesce_nonnull_begin --
+1
+2
+9
+qwe
+
+-- !test_coalesce_nullalbe_begin --
+2.7
+7.8
+8
+ab
+
+-- !test_coalesce_null_null --
+\N
+\N
+\N
+\N
+
+-- !test_coalesce_null --
+\N
+\N
+\N
+\N
+
+-- !test_coalesce_nonnull --
+\N qwe 8.0
+1 abc 2.7
+2 ccd 7.8
+9 ab \N
+
+-- !test_coalesce_nullable --
+\N
+2.7
+7.8
+8.0
+
+-- !test_nvl_null_nullable --
+ab
+abc
+ccd
+qwe
+
+-- !test_nvl_null_nonnullable --
+\N
+1
+2
+9
+
+-- !test_nvl_nullable_nullable --
+ab
+abc
+ccd
+qwe
+
+-- !test_nvl_nonnullable_null --
+\N
+1
+2
+9
+
+-- !test_nvl_null_null --
+\N
+\N
+\N
+\N
+
+-- !test_nullif_null_nullable --
+\N \N
+\N 1
+\N 2
+\N 9
+
+-- !test_nullif_null_nonnullable --
+\N
+\N
+\N
+\N
+
+-- !test_nullif_nonnullable_null --
+\N
+1
+2
+9
+
+-- !test_nullif_nullable_null --
+ab 9
+abc 1
+ccd 2
+qwe \N
+
+-- !test_nullif_nullable_nonnullable --
+ab
+abc
+ccd
+qwe
+
+-- !test_nullif_null_null --
+\N
+\N
+\N
+\N
+
+-- !test_outer_ref_coalesce --
+ab
+abc
+
+-- !test_outer_ref_nvl --
+\N
+1
+
+-- !test_outer_ref_nullif --
+ab
+abc
+
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query41.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query41.out
index a1be484e284..c870e728a39 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 744) and (i1.i_manufact_id >=
704))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query41.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query41.out
index 4cac3a912db..ff811ef082c 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query41.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query41.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query41.out
index 4cac3a912db..ff811ef082c 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query41.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query41.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf10t_orc/shape/query41.out
b/regression-test/data/nereids_tpcds_shape_sf10t_orc/shape/query41.out
index 11bcf3db2e7..a470709da68 100644
--- a/regression-test/data/nereids_tpcds_shape_sf10t_orc/shape/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf10t_orc/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 1010) and (i1.i_manufact_id >=
970))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/new_shapes_p0/tpcds_sf100/noStatsRfPrune/query41.out
b/regression-test/data/new_shapes_p0/tpcds_sf100/noStatsRfPrune/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/new_shapes_p0/tpcds_sf100/noStatsRfPrune/query41.out
+++ b/regression-test/data/new_shapes_p0/tpcds_sf100/noStatsRfPrune/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/new_shapes_p0/tpcds_sf100/no_stats_shape/query41.out
b/regression-test/data/new_shapes_p0/tpcds_sf100/no_stats_shape/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/new_shapes_p0/tpcds_sf100/no_stats_shape/query41.out
+++ b/regression-test/data/new_shapes_p0/tpcds_sf100/no_stats_shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/data/new_shapes_p0/tpcds_sf100/rf_prune/query41.out
b/regression-test/data/new_shapes_p0/tpcds_sf100/rf_prune/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/new_shapes_p0/tpcds_sf100/rf_prune/query41.out
+++ b/regression-test/data/new_shapes_p0/tpcds_sf100/rf_prune/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git a/regression-test/data/new_shapes_p0/tpcds_sf100/shape/query41.out
b/regression-test/data/new_shapes_p0/tpcds_sf100/shape/query41.out
index 4cac3a912db..ff811ef082c 100644
--- a/regression-test/data/new_shapes_p0/tpcds_sf100/shape/query41.out
+++ b/regression-test/data/new_shapes_p0/tpcds_sf100/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 788) and (i1.i_manufact_id >=
748))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git a/regression-test/data/new_shapes_p0/tpcds_sf1000/shape/query41.out
b/regression-test/data/new_shapes_p0/tpcds_sf1000/shape/query41.out
index a1be484e284..c870e728a39 100644
--- a/regression-test/data/new_shapes_p0/tpcds_sf1000/shape/query41.out
+++ b/regression-test/data/new_shapes_p0/tpcds_sf1000/shape/query41.out
@@ -13,7 +13,7 @@ PhysicalResultSink
--------------------filter((i1.i_manufact_id <= 744) and (i1.i_manufact_id >=
704))
----------------------PhysicalOlapScan[item] apply RFs: RF0
------------------PhysicalProject
---------------------filter((ifnull(item_cnt, 0) > 0))
+--------------------filter((item_cnt > 0))
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
diff --git
a/regression-test/suites/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.groovy
b/regression-test/suites/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.groovy
new file mode 100644
index 00000000000..18443bb152a
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/simplify_conditional_function/simplify_conditional_function.groovy
@@ -0,0 +1,52 @@
+// 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("simplify_conditional_function") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "drop table if exists test_simplify_conditional_function"
+ sql """create table test_simplify_conditional_function(c int null, b
double null, a varchar(100) not null) distributed by hash(c)
+ properties("replication_num"="1");
+ """
+ sql "insert into test_simplify_conditional_function
values(1,2.7,'abc'),(2,7.8,'ccd'),(null,8,'qwe'),(9,null,'ab')"
+
+ qt_test_coalesce_null_begin1 "select coalesce(null, null, a, b, c),a,b
from test_simplify_conditional_function order by 1,2,3"
+ qt_test_coalesce_null_begin2 "select coalesce(null, null, a) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_null_begin3 "select coalesce(null, null, c, a) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_nonnull_begin "select coalesce(c, a) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_nullalbe_begin "select coalesce(b, a, null) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_null_null "select coalesce(null, null) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_null "select coalesce(null) from
test_simplify_conditional_function order by 1"
+ qt_test_coalesce_nonnull "select coalesce(c),a,b from
test_simplify_conditional_function order by 1,2,3"
+ qt_test_coalesce_nullable "select coalesce(b) from
test_simplify_conditional_function order by 1"
+
+ qt_test_nvl_null_nullable "select ifnull(null, a) from
test_simplify_conditional_function order by 1 "
+ qt_test_nvl_null_nonnullable "select ifnull(null, c) from
test_simplify_conditional_function order by 1 "
+ qt_test_nvl_nullable_nullable "select ifnull(a, b) from
test_simplify_conditional_function order by 1 "
+ qt_test_nvl_nonnullable_null "select ifnull(c, null) from
test_simplify_conditional_function order by 1 "
+ qt_test_nvl_null_null "select ifnull(null, null) from
test_simplify_conditional_function order by 1 "
+
+ qt_test_nullif_null_nullable "select nullif(null, a),c from
test_simplify_conditional_function order by 1,2 "
+ qt_test_nullif_null_nonnullable "select nullif(null, c) from
test_simplify_conditional_function order by 1 "
+ qt_test_nullif_nonnullable_null "select nullif(c, null) from
test_simplify_conditional_function order by 1 "
+ qt_test_nullif_nullable_null "select nullif(a, null),c from
test_simplify_conditional_function order by 1,2 "
+ qt_test_nullif_nullable_nonnullable "select nullif(a, c) from
test_simplify_conditional_function order by 1 "
+ qt_test_nullif_null_null "select nullif(null, null) from
test_simplify_conditional_function order by 1 "
+
+ qt_test_outer_ref_coalesce "select c1 from (select coalesce(null,a,c)
c1,a,b from test_simplify_conditional_function order by c1,a,b limit 2) t group
by c1 order by c1"
+ qt_test_outer_ref_nvl "select c1 from (select ifnull(null, c) c1 from
test_simplify_conditional_function order by 1 limit 2) t group by c1 order by
c1"
+ qt_test_outer_ref_nullif "select c1 from (select nullif(a, null) c1,c from
test_simplify_conditional_function order by c1,c limit 2 ) t group by c1 order
by c1"
+}
\ 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
