This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
commit b20bb5bcff90a5f3fae6590023c4cd510ec20662
Author: jakevin <jakevin...@gmail.com>
AuthorDate: Fri Sep 8 16:58:33 2023 +0800
[feature](Nereids): enable convert CASE WHEN to IF (#24050)
enable rule to convert CASE WHEN to IF.
---
.../rules/expression/ExpressionOptimization.java | 7 ++-
.../doris/nereids/stats/ExpressionEstimation.java | 27 ++++++---
.../fold_constant/fold_constant_by_fe.out | 0
.../nereids_tpcds_shape_sf100_p0/shape/query11.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query21.out | 33 ++++++-----
.../nereids_tpcds_shape_sf100_p0/shape/query31.out | 4 +-
.../nereids_tpcds_shape_sf100_p0/shape/query34.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query39.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query4.out | 4 +-
.../nereids_tpcds_shape_sf100_p0/shape/query47.out | 9 +++
.../nereids_tpcds_shape_sf100_p0/shape/query53.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query57.out | 10 +++-
.../nereids_tpcds_shape_sf100_p0/shape/query63.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query73.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query74.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query89.out | 2 +-
.../nereids_p0/expression/case_when_to_if.groovy | 69 ++++++++++++++++++++++
.../fold_constant/fold_constant_by_be.groovy | 0
.../fold_constant/fold_constant_by_fe.groovy | 0
.../{ => expression}/fold_constant/load.groovy | 0
20 files changed, 141 insertions(+), 38 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 53f360d5ff..869cd6b99e 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
@@ -17,6 +17,8 @@
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.DistinctPredicatesRule;
import org.apache.doris.nereids.rules.expression.rules.ExtractCommonFactorRule;
import org.apache.doris.nereids.rules.expression.rules.OrToIn;
@@ -40,8 +42,9 @@ public class ExpressionOptimization extends ExpressionRewrite
{
SimplifyInPredicate.INSTANCE,
SimplifyDecimalV3Comparison.INSTANCE,
SimplifyRange.INSTANCE,
- OrToIn.INSTANCE
-
+ OrToIn.INSTANCE,
+ ArrayContainToArrayOverlap.INSTANCE,
+ CaseWhenToIf.INSTANCE
);
private static final ExpressionRuleExecutor EXECUTOR = new
ExpressionRuleExecutor(OPTIMIZE_REWRITE_RULES);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
index b45476cf93..1ff53a4abe 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
@@ -59,6 +59,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.FromDays;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Hour;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.If;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Least;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Minute;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
@@ -134,13 +135,25 @@ public class ExpressionEstimation extends
ExpressionVisitor<ColumnStatistic, Sta
//TODO: case-when need to re-implemented
@Override
public ColumnStatistic visitCaseWhen(CaseWhen caseWhen, Statistics
context) {
- ColumnStatisticBuilder columnStat = new ColumnStatisticBuilder();
- columnStat.setNdv(caseWhen.getWhenClauses().size() + 1);
- columnStat.setMinValue(0);
- columnStat.setMaxValue(Double.MAX_VALUE);
- columnStat.setAvgSizeByte(8);
- columnStat.setNumNulls(0);
- return columnStat.build();
+ return new ColumnStatisticBuilder()
+ .setNdv(caseWhen.getWhenClauses().size() + 1)
+ .setMinValue(0)
+ .setMaxValue(Double.MAX_VALUE)
+ .setAvgSizeByte(8)
+ .setNumNulls(0)
+ .build();
+ }
+
+ @Override
+ public ColumnStatistic visitIf(If function, Statistics context) {
+ // TODO: copy from visitCaseWhen, polish them.
+ return new ColumnStatisticBuilder()
+ .setNdv(2)
+ .setMinValue(0)
+ .setMaxValue(Double.MAX_VALUE)
+ .setAvgSizeByte(8)
+ .setNumNulls(0)
+ .build();
}
@Override
diff --git
a/regression-test/data/nereids_p0/fold_constant/fold_constant_by_fe.out
b/regression-test/data/nereids_p0/expression/fold_constant/fold_constant_by_fe.out
similarity index 100%
rename from
regression-test/data/nereids_p0/fold_constant/fold_constant_by_fe.out
rename to
regression-test/data/nereids_p0/expression/fold_constant/fold_constant_by_fe.out
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
index 345dcf525d..1e66f6d9ef 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
@@ -45,7 +45,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute
--------PhysicalTopN
----------PhysicalProject
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(CASE WHEN (year_total > 0.00) THEN (cast(year_total as
DOUBLE) / cast(year_total as DOUBLE)) ELSE 0 END > CASE WHEN (year_total >
0.00) THEN (cast(year_total as DOUBLE) / cast(year_total as DOUBLE)) ELSE 0 END)
+------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(if((year_total > 0.00), (cast(year_total as DOUBLE) /
cast(year_total as DOUBLE)), 0) > if((year_total > 0.00), (cast(year_total as
DOUBLE) / cast(year_total as DOUBLE)), 0))
--------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)
----------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)
------------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
index f950635ad6..58c63f10f1 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
@@ -3,24 +3,25 @@
PhysicalResultSink
--PhysicalTopN
----PhysicalDistribute
-------filter((CASE WHEN (inv_before > 0) THEN (cast(inv_after as DOUBLE) /
cast(inv_before as DOUBLE)) ELSE NULL END >= cast((2.000000 / 3.0) as
DOUBLE))(CASE WHEN (inv_before > 0) THEN (cast(inv_after as DOUBLE) /
cast(inv_before as DOUBLE)) ELSE NULL END <= 1.5))
---------hashAgg[GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[LOCAL]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN](inventory.inv_warehouse_sk =
warehouse.w_warehouse_sk)
-------------------hashJoin[INNER_JOIN](inventory.inv_date_sk =
date_dim.d_date_sk)
---------------------hashJoin[INNER_JOIN](item.i_item_sk =
inventory.inv_item_sk)
-----------------------PhysicalOlapScan[inventory]
+------PhysicalTopN
+--------filter((if((inv_before > 0), (cast(inv_after as DOUBLE) /
cast(inv_before as DOUBLE)), NULL) <= 1.5)(if((inv_before > 0), (cast(inv_after
as DOUBLE) / cast(inv_before as DOUBLE)), NULL) >= cast((2.000000 / 3.0) as
DOUBLE)))
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](inventory.inv_warehouse_sk =
warehouse.w_warehouse_sk)
+--------------------hashJoin[INNER_JOIN](inventory.inv_date_sk =
date_dim.d_date_sk)
+----------------------hashJoin[INNER_JOIN](item.i_item_sk =
inventory.inv_item_sk)
+------------------------PhysicalOlapScan[inventory]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((item.i_current_price <=
1.49)(item.i_current_price >= 0.99))
+------------------------------PhysicalOlapScan[item]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter((item.i_current_price <=
1.49)(item.i_current_price >= 0.99))
-----------------------------PhysicalOlapScan[item]
+--------------------------filter((date_dim.d_date >=
2002-01-28)(date_dim.d_date <= 2002-03-29))
+----------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------filter((date_dim.d_date >= 2002-01-28)(date_dim.d_date
<= 2002-03-29))
---------------------------PhysicalOlapScan[date_dim]
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------PhysicalOlapScan[warehouse]
+------------------------PhysicalOlapScan[warehouse]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query31.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query31.out
index 810af0b1ca..fca844c352 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query31.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query31.out
@@ -44,7 +44,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------PhysicalDistribute
----------PhysicalQuickSort
------------PhysicalProject
---------------hashJoin[INNER_JOIN](ws1.ca_county = ws3.ca_county)(CASE WHEN
(web_sales > 0.00) THEN (cast(web_sales as DOUBLE) / cast(web_sales as DOUBLE))
ELSE NULL END > CASE WHEN (store_sales > 0.00) THEN (cast(store_sales as
DOUBLE) / cast(store_sales as DOUBLE)) ELSE NULL END)
+--------------hashJoin[INNER_JOIN](ws1.ca_county =
ws3.ca_county)(if((web_sales > 0.00), (cast(web_sales as DOUBLE) /
cast(web_sales as DOUBLE)), NULL) > if((store_sales > 0.00), (cast(store_sales
as DOUBLE) / cast(store_sales as DOUBLE)), NULL))
----------------PhysicalDistribute
------------------PhysicalProject
--------------------filter((ws3.d_year = 2000)(ws3.d_qoy = 3))
@@ -55,7 +55,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalProject
------------------------filter((ss3.d_year = 2000)(ss3.d_qoy = 3))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------hashJoin[INNER_JOIN](ws1.ca_county = ws2.ca_county)(CASE
WHEN (web_sales > 0.00) THEN (cast(web_sales as DOUBLE) / cast(web_sales as
DOUBLE)) ELSE NULL END > CASE WHEN (store_sales > 0.00) THEN (cast(store_sales
as DOUBLE) / cast(store_sales as DOUBLE)) ELSE NULL END)
+--------------------hashJoin[INNER_JOIN](ws1.ca_county =
ws2.ca_county)(if((web_sales > 0.00), (cast(web_sales as DOUBLE) /
cast(web_sales as DOUBLE)), NULL) > if((store_sales > 0.00), (cast(store_sales
as DOUBLE) / cast(store_sales as DOUBLE)), NULL))
----------------------hashJoin[INNER_JOIN](ss1.ca_county = ws1.ca_county)
------------------------hashJoin[INNER_JOIN](ss1.ca_county = ss2.ca_county)
--------------------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query34.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query34.out
index 6fd519ecc6..4779144765 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query34.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query34.out
@@ -25,7 +25,7 @@ PhysicalResultSink
------------------------------------PhysicalOlapScan[date_dim]
----------------------------PhysicalDistribute
------------------------------PhysicalProject
---------------------------------filter(((cast(hd_buy_potential as VARCHAR(*))
= '1001-5000') OR (cast(hd_buy_potential as VARCHAR(*)) =
'0-500'))(household_demographics.hd_vehicle_count > 0)(CASE WHEN
(hd_vehicle_count > 0) THEN (cast(hd_dep_count as DOUBLE) /
cast(hd_vehicle_count as DOUBLE)) ELSE NULL END > 1.2))
+--------------------------------filter(((cast(hd_buy_potential as VARCHAR(*))
= '1001-5000') OR (cast(hd_buy_potential as VARCHAR(*)) =
'0-500'))(household_demographics.hd_vehicle_count > 0)(if((hd_vehicle_count >
0), (cast(hd_dep_count as DOUBLE) / cast(hd_vehicle_count as DOUBLE)), NULL) >
1.2))
----------------------------------PhysicalOlapScan[household_demographics]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query39.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query39.out
index 2bac250532..69cf5bded9 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query39.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query39.out
@@ -3,7 +3,7 @@
PhysicalCteAnchor ( cteId=CTEId#0 )
--PhysicalCteProducer ( cteId=CTEId#0 )
----PhysicalProject
-------filter((CASE WHEN (mean = 0) THEN 0 ELSE (stdev / mean) END > 1))
+------filter((if((mean = 0), 0, (stdev / mean)) > 1))
--------hashAgg[GLOBAL]
----------PhysicalDistribute
------------hashAgg[LOCAL]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
index d041a585d1..640b557743 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
@@ -62,11 +62,11 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute
--------PhysicalTopN
----------PhysicalProject
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN
(cast(year_total as DOUBLE) / cast(year_total as DOUBLE)) ELSE NULL END > CASE
WHEN (year_total > 0.000000) THEN (cast(year_total as DOUBLE) / cast(year_total
as DOUBLE)) ELSE NULL END)
+------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(if((year_total > 0.000000), (cast(year_total as
DOUBLE) / cast(year_total as DOUBLE)), NULL) > if((year_total > 0.000000),
(cast(year_total as DOUBLE) / cast(year_total as DOUBLE)), NULL))
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_secyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN
(cast(year_total as DOUBLE) / cast(year_total as DOUBLE)) ELSE NULL END > CASE
WHEN (year_total > 0.000000) THEN (cast(year_total as DOUBLE) / cast(year_total
as DOUBLE)) ELSE NULL END)
+--------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_secyear.customer_id)(if((year_total > 0.000000), (cast(year_total as
DOUBLE) / cast(year_total as DOUBLE)), NULL) > if((year_total > 0.000000),
(cast(year_total as DOUBLE) / cast(year_total as DOUBLE)), NULL))
----------------------PhysicalProject
------------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)
--------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_firstyear.customer_id)
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
index b705f7a2cb..9b9a3570d5 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
@@ -36,6 +36,15 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------PhysicalTopN
------------PhysicalProject
--------------hashJoin[INNER_JOIN](v1.i_category =
v1_lead.i_category)(v1.i_brand = v1_lead.i_brand)(v1.s_store_name =
v1_lead.s_store_name)(v1.s_company_name = v1_lead.s_company_name)(v1.rn =
expr_(rn - 1))
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](v1.i_category =
v1_lag.i_category)(v1.i_brand = v1_lag.i_brand)(v1.s_store_name =
v1_lag.s_store_name)(v1.s_company_name = v1_lag.s_company_name)(v1.rn =
expr_(rn + 1))
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((if((avg_monthly_sales > 0.0000),
(abs((cast(sum_sales as DOUBLE) - cast(avg_monthly_sales as DOUBLE))) /
cast(avg_monthly_sales as DOUBLE)), NULL) > 0.1)(v2.d_year =
2001)(v2.avg_monthly_sales > 0.0000))
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalDistribute
------------------PhysicalProject
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
index 87e4abe779..4de40612e7 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute
------PhysicalTopN
--------PhysicalProject
-----------filter((CASE WHEN (avg_quarterly_sales > 0.0000) THEN
(abs((cast(sum_sales as DOUBLE) - cast(avg_quarterly_sales as DOUBLE))) /
cast(avg_quarterly_sales as DOUBLE)) ELSE NULL END > 0.1))
+----------filter((if((avg_quarterly_sales > 0.0000), (abs((cast(sum_sales as
DOUBLE) - cast(avg_quarterly_sales as DOUBLE))) / cast(avg_quarterly_sales as
DOUBLE)), NULL) > 0.1))
------------PhysicalWindow
--------------PhysicalQuickSort
----------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query57.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query57.out
index d3d96f0926..14b4174b1a 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query57.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query57.out
@@ -35,7 +35,15 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------PhysicalDistribute
----------PhysicalTopN
------------PhysicalProject
---------------hashJoin[INNER_JOIN](v1.i_category =
v1_lead.i_category)(v1.i_brand = v1_lead.i_brand)(v1.cc_name =
v1_lead.cc_name)(v1.rn = expr_(rn - 1))
+--------------hashJoin[INNER_JOIN](v1.i_category =
v1_lag.i_category)(v1.i_brand = v1_lag.i_brand)(v1.cc_name =
v1_lag.cc_name)(v1.rn = expr_(rn + 1))
+----------------hashJoin[INNER_JOIN](v1.i_category =
v1_lead.i_category)(v1.i_brand = v1_lead.i_brand)(v1.cc_name =
v1_lead.cc_name)(v1.rn = expr_(rn - 1))
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((v2.d_year = 1999)(if((avg_monthly_sales >
0.0000), (abs((cast(sum_sales as DOUBLE) - cast(avg_monthly_sales as DOUBLE)))
/ cast(avg_monthly_sales as DOUBLE)), NULL) > 0.1)(v2.avg_monthly_sales >
0.0000))
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalDistribute
------------------PhysicalProject
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
index eea2cefd61..c90dbf0429 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute
------PhysicalTopN
--------PhysicalProject
-----------filter((CASE WHEN (avg_monthly_sales > 0.0000) THEN
(abs((cast(sum_sales as DOUBLE) - cast(avg_monthly_sales as DOUBLE))) /
cast(avg_monthly_sales as DOUBLE)) ELSE NULL END > 0.1))
+----------filter((if((avg_monthly_sales > 0.0000), (abs((cast(sum_sales as
DOUBLE) - cast(avg_monthly_sales as DOUBLE))) / cast(avg_monthly_sales as
DOUBLE)), NULL) > 0.1))
------------PhysicalWindow
--------------PhysicalQuickSort
----------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query73.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query73.out
index 34a39fb42f..534e5f4477 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query73.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query73.out
@@ -25,7 +25,7 @@ PhysicalResultSink
------------------------------------PhysicalOlapScan[date_dim]
----------------------------PhysicalDistribute
------------------------------PhysicalProject
---------------------------------filter(((cast(hd_buy_potential as VARCHAR(*))
= '501-1000') OR (cast(hd_buy_potential as VARCHAR(*)) =
'Unknown'))(household_demographics.hd_vehicle_count > 0)(CASE WHEN
(hd_vehicle_count > 0) THEN (cast(hd_dep_count as DOUBLE) /
cast(hd_vehicle_count as DOUBLE)) ELSE NULL END > 1))
+--------------------------------filter(((cast(hd_buy_potential as VARCHAR(*))
= '501-1000') OR (cast(hd_buy_potential as VARCHAR(*)) =
'Unknown'))(household_demographics.hd_vehicle_count > 0)(if((hd_vehicle_count >
0), (cast(hd_dep_count as DOUBLE) / cast(hd_vehicle_count as DOUBLE)), NULL) >
1))
----------------------------------PhysicalOlapScan[household_demographics]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
index cb6953fa83..f24a332aff 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
@@ -45,7 +45,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute
--------PhysicalTopN
----------PhysicalProject
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)(CASE WHEN (year_total > 0) THEN (year_total /
year_total) ELSE NULL END > CASE WHEN (year_total > 0) THEN (year_total /
year_total) ELSE NULL END)
+------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)(if((year_total > 0), (year_total / year_total),
NULL) > if((year_total > 0), (year_total / year_total), NULL))
--------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)
----------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)
------------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query89.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query89.out
index fff718f587..4cb57595f3 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query89.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query89.out
@@ -6,7 +6,7 @@ PhysicalResultSink
------PhysicalDistribute
--------PhysicalTopN
----------PhysicalProject
-------------filter((CASE WHEN ( not (avg_monthly_sales = 0.0000)) THEN
(abs((cast(sum_sales as DOUBLE) - cast(avg_monthly_sales as DOUBLE))) /
cast(avg_monthly_sales as DOUBLE)) ELSE NULL END > 0.1))
+------------filter((if(( not (avg_monthly_sales = 0.0000)),
(abs((cast(sum_sales as DOUBLE) - cast(avg_monthly_sales as DOUBLE))) /
cast(avg_monthly_sales as DOUBLE)), NULL) > 0.1))
--------------PhysicalWindow
----------------PhysicalQuickSort
------------------PhysicalDistribute
diff --git
a/regression-test/suites/nereids_p0/expression/case_when_to_if.groovy
b/regression-test/suites/nereids_p0/expression/case_when_to_if.groovy
new file mode 100644
index 0000000000..8c41b8d1c6
--- /dev/null
+++ b/regression-test/suites/nereids_p0/expression/case_when_to_if.groovy
@@ -0,0 +1,69 @@
+// 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_case_when_to_if") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ sql 'drop table if exists test_case_when_to_if;'
+
+ sql '''create table test_case_when_to_if (k1 int, k2 int) distributed by
hash(k1) buckets 3 properties('replication_num' = '1');'''
+
+ // else is empty
+ sql '''
+ select k2,
+ sum(case when (k1=1) then 1 end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ res = sql '''
+ explain rewritten plan select k2,
+ sum(case when (k1=1) then 1 end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ assertTrue(res.toString().contains("if"))
+
+ // else is null
+ sql '''
+ select k2,
+ sum(case when (k1=1) then 1 else null end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ res = sql '''
+ explain rewritten plan select k2,
+ sum(case when (k1=1) then 1 else null end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ assertTrue(res.toString().contains("if"))
+
+ sql '''
+ select k2,
+ sum(case when (k1>0) then k1 else abs(k1) end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ res = sql '''
+ explain rewritten plan select k2,
+ sum(case when (k1>0) then k1 else abs(k1) end) sum1
+ from test_case_when_to_if
+ group by k2;
+ '''
+ assertTrue(res.toString().contains("if"))
+}
diff --git
a/regression-test/suites/nereids_p0/fold_constant/fold_constant_by_be.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_be.groovy
similarity index 100%
rename from
regression-test/suites/nereids_p0/fold_constant/fold_constant_by_be.groovy
rename to
regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_be.groovy
diff --git
a/regression-test/suites/nereids_p0/fold_constant/fold_constant_by_fe.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
similarity index 100%
rename from
regression-test/suites/nereids_p0/fold_constant/fold_constant_by_fe.groovy
rename to
regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
diff --git a/regression-test/suites/nereids_p0/fold_constant/load.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/load.groovy
similarity index 100%
rename from regression-test/suites/nereids_p0/fold_constant/load.groovy
rename to regression-test/suites/nereids_p0/expression/fold_constant/load.groovy
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
