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 f95d728d3e [shape](nereids) TPCDS check all query shape, except ds64
(#21742)
f95d728d3e is described below
commit f95d728d3e83074ed6c09d5e31f8f3aadf8fac0d
Author: minghong <engle...@gmail.com>
AuthorDate: Fri Jul 14 16:56:46 2023 +0800
[shape](nereids) TPCDS check all query shape, except ds64 (#21742)
there is a known bug on ds64 analyze. add ds 64 shape check latter
---
.../apache/doris/statistics/ColumnStatistic.java | 3 -
.../nereids_tpcds_shape_sf100_p0/shape/query16.out | 6 +-
.../nereids_tpcds_shape_sf100_p0/shape/query17.out | 61 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query25.out | 63 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query28.out | 72 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query29.out | 12 +-
.../nereids_tpcds_shape_sf100_p0/shape/query39.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query48.out | 38 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query50.out | 30 +--
.../nereids_tpcds_shape_sf100_p0/shape/query59.out | 47 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query61.out | 36 +--
.../nereids_tpcds_shape_sf100_p0/shape/query64.out | 133 +++++------
.../nereids_tpcds_shape_sf100_p0/shape/query85.out | 8 +-
.../shape/query13.groovy | 104 ++++-----
.../shape/query16.groovy | 62 +++---
.../shape/query17.groovy | 90 ++++----
.../shape/query25.groovy | 96 ++++----
.../shape/query28.groovy | 106 ++++-----
.../shape/query29.groovy | 94 ++++----
.../shape/query39.groovy | 56 ++---
.../shape/query48.groovy | 134 +++++------
.../shape/query50.groovy | 118 +++++-----
.../shape/query59.groovy | 88 ++++----
.../shape/query61.groovy | 88 ++++----
.../shape/query64.groovy | 244 ++++++++++-----------
.../shape/query85.groovy | 168 +++++++-------
.../shape/query88.groovy | 188 ++++++++--------
.../shape/query9.groovy | 102 ++++-----
28 files changed, 1118 insertions(+), 1131 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
index d791ee1e0d..e60d7d8697 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
@@ -167,9 +167,6 @@ public class ColumnStatistic {
double count =
Double.parseDouble(resultRow.getColumnValueWithDefault("count", "0"));
columnStatisticBuilder.setCount(count);
double ndv =
Double.parseDouble(resultRow.getColumnValueWithDefault("ndv", "0"));
- if (0.99 * count < ndv && ndv < 1.01 * count) {
- ndv = count;
- }
columnStatisticBuilder.setNdv(ndv);
String nullCount =
resultRow.getColumnValueWithDefault("null_count", "0");
columnStatisticBuilder.setNumNulls(Double.parseDouble(nullCount));
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
index 550b3e1360..1d8f699cb2 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
@@ -4,8 +4,8 @@ PhysicalTopN
--PhysicalTopN
----PhysicalProject
------hashAgg[GLOBAL]
---------hashAgg[LOCAL]
-----------PhysicalDistribute
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
------------PhysicalProject
--------------hashJoin[INNER_JOIN](cs1.cs_call_center_sk =
call_center.cc_call_center_sk)
----------------PhysicalProject
@@ -15,7 +15,7 @@ PhysicalTopN
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](cs1.cs_ship_date_sk =
date_dim.d_date_sk)
----------------------PhysicalProject
-------------------------filter((cast(d_date as DATETIMEV2(0)) <=
cast(days_add(cast('2002-4-01' as DATE), INTERVAL 60 DAY) as
DATETIMEV2(0)))(date_dim.d_date >= 2002-04-01))
+------------------------filter((cast(d_date as DATETIMEV2(0)) <=
cast(days_add(cast('2002-4-01' as DATEV2), INTERVAL 60 DAY) as
DATETIMEV2(0)))(date_dim.d_date >= 2002-04-01))
--------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalDistribute
------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query17.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query17.out
index 7a6ac4e437..3ea6acc6ee 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query17.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query17.out
@@ -8,40 +8,41 @@ PhysicalTopN
----------PhysicalDistribute
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN](store.s_store_sk =
store_sales.ss_store_sk)
+----------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk =
d3.d_date_sk)
------------------PhysicalProject
---------------------PhysicalOlapScan[store]
-------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](item.i_item_sk =
store_sales.ss_item_sk)
+--------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk =
catalog_sales.cs_item_sk)(store_returns.sr_customer_sk =
catalog_sales.cs_bill_customer_sk)
----------------------PhysicalProject
-------------------------PhysicalOlapScan[item]
+------------------------PhysicalOlapScan[catalog_sales]
----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk =
d3.d_date_sk)
-----------------------------PhysicalProject
-------------------------------filter(d_quarter_name IN ('2001Q1', '2001Q2',
'2001Q3'))
---------------------------------PhysicalOlapScan[date_dim]
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk
= catalog_sales.cs_item_sk)(store_returns.sr_customer_sk =
catalog_sales.cs_bill_customer_sk)
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------PhysicalDistribute
+------------------------hashJoin[INNER_JOIN](store.s_store_sk =
store_sales.ss_store_sk)
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN](item.i_item_sk =
store_sales.ss_item_sk)
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
+--------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_returns]
---------------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk
= store_sales.ss_sold_date_sk)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((cast(d_quarter_name
as VARCHAR(*)) = '2001Q1'))
-----------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalOlapScan[store_returns]
+----------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk =
store_sales.ss_sold_date_sk)
------------------------------------------PhysicalProject
---------------------------------------------filter(d_quarter_name IN
('2001Q1', '2001Q2', '2001Q3'))
-----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalOlapScan[store_sales]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((cast(d_quarter_name as
VARCHAR(*)) = '2001Q1'))
+------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter(d_quarter_name IN ('2001Q1',
'2001Q2', '2001Q3'))
+------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[store]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter(d_quarter_name IN ('2001Q1', '2001Q2', '2001Q3'))
+------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query25.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query25.out
index 83877b5550..52b9f72ff1 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query25.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query25.out
@@ -7,40 +7,41 @@ PhysicalTopN
--------PhysicalDistribute
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
+--------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk =
d3.d_date_sk)
----------------PhysicalProject
-------------------PhysicalOlapScan[store]
-----------------PhysicalDistribute
-------------------hashJoin[INNER_JOIN](item.i_item_sk = store_sales.ss_item_sk)
+------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk =
catalog_sales.cs_item_sk)(store_returns.sr_customer_sk =
catalog_sales.cs_bill_customer_sk)
--------------------PhysicalProject
-----------------------PhysicalOlapScan[item]
+----------------------PhysicalOlapScan[catalog_sales]
--------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk =
d3.d_date_sk)
---------------------------PhysicalProject
-----------------------------filter((d3.d_year = 2000)(d3.d_moy <= 10)(d3.d_moy
>= 4))
-------------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk =
catalog_sales.cs_item_sk)(store_returns.sr_customer_sk =
catalog_sales.cs_bill_customer_sk)
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales]
---------------------------------PhysicalDistribute
+----------------------hashJoin[INNER_JOIN](store.s_store_sk =
store_sales.ss_store_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](item.i_item_sk =
store_sales.ss_item_sk)
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_returns]
---------------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk
= store_sales.ss_sold_date_sk)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((d1.d_year =
2000)(d1.d_moy = 4))
-----------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
--------------------------------------PhysicalProject
-----------------------------------------filter((d2.d_moy <= 10)(d2.d_moy >=
4)(d2.d_year = 2000))
-------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalOlapScan[store_returns]
+--------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk =
store_sales.ss_sold_date_sk)
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------filter((d1.d_year = 2000)(d1.d_moy
= 4))
+----------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter((d2.d_moy <= 10)(d2.d_moy >=
4)(d2.d_year = 2000))
+----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[item]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((d3.d_year = 2000)(d3.d_moy <= 10)(d3.d_moy >= 4))
+----------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query28.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query28.out
index 7a07c8ac87..ed3a0e5d8e 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query28.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query28.out
@@ -18,73 +18,55 @@ PhysicalLimit
------------------------------NestedLoopJoin[CROSS_JOIN]
--------------------------------PhysicalLimit
----------------------------------PhysicalLimit
-------------------------------------hashAgg[DISTINCT_GLOBAL]
+------------------------------------hashAgg[GLOBAL]
--------------------------------------PhysicalDistribute
-----------------------------------------hashAgg[DISTINCT_LOCAL]
-------------------------------------------hashAgg[GLOBAL]
---------------------------------------------PhysicalDistribute
-----------------------------------------------hashAgg[LOCAL]
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((store_sales.ss_quantity
<= 5)((((store_sales.ss_list_price >= 131.00) AND (store_sales.ss_list_price
<= 141.00)) OR ((store_sales.ss_coupon_amt >= 16798.00) AND
(store_sales.ss_coupon_amt <= 17798.00))) OR ((store_sales.ss_wholesale_cost >=
25.00) AND (store_sales.ss_wholesale_cost <= 45.00)))(store_sales.ss_quantity
>= 0))
-----------------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------hashAgg[LOCAL]
+------------------------------------------PhysicalProject
+--------------------------------------------filter((store_sales.ss_quantity <=
5)((((store_sales.ss_list_price >= 131.00) AND (store_sales.ss_list_price <=
141.00)) OR ((store_sales.ss_coupon_amt >= 16798.00) AND
(store_sales.ss_coupon_amt <= 17798.00))) OR ((store_sales.ss_wholesale_cost >=
25.00) AND (store_sales.ss_wholesale_cost <= 45.00)))(store_sales.ss_quantity
>= 0))
+----------------------------------------------PhysicalOlapScan[store_sales]
--------------------------------PhysicalDistribute
----------------------------------PhysicalLimit
------------------------------------PhysicalLimit
---------------------------------------hashAgg[DISTINCT_GLOBAL]
-----------------------------------------PhysicalDistribute
-------------------------------------------hashAgg[DISTINCT_LOCAL]
---------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------PhysicalProject
-----------------------------------------------------filter((store_sales.ss_quantity
<= 10)((((store_sales.ss_list_price >= 145.00) AND (store_sales.ss_list_price
<= 155.00)) OR ((store_sales.ss_coupon_amt >= 14792.00) AND
(store_sales.ss_coupon_amt <= 15792.00))) OR ((store_sales.ss_wholesale_cost >=
46.00) AND (store_sales.ss_wholesale_cost <= 66.00)))(store_sales.ss_quantity
>= 6))
-------------------------------------------------------PhysicalOlapScan[store_sales]
---------------------------PhysicalDistribute
-----------------------------PhysicalLimit
-------------------------------PhysicalLimit
---------------------------------hashAgg[DISTINCT_GLOBAL]
-----------------------------------PhysicalDistribute
-------------------------------------hashAgg[DISTINCT_LOCAL]
--------------------------------------hashAgg[GLOBAL]
----------------------------------------PhysicalDistribute
------------------------------------------hashAgg[LOCAL]
--------------------------------------------PhysicalProject
-----------------------------------------------filter(((((store_sales.ss_list_price
>= 1.5E+2) AND (store_sales.ss_list_price <= 1.6E+2)) OR
((store_sales.ss_coupon_amt >= 6.6E+3) AND (store_sales.ss_coupon_amt <=
7.6E+3))) OR ((store_sales.ss_wholesale_cost >= 9.00) AND
(store_sales.ss_wholesale_cost <= 29.00)))(store_sales.ss_quantity >=
11)(store_sales.ss_quantity <= 15))
+----------------------------------------------filter((store_sales.ss_quantity
<= 10)((((store_sales.ss_list_price >= 145.00) AND (store_sales.ss_list_price
<= 155.00)) OR ((store_sales.ss_coupon_amt >= 14792.00) AND
(store_sales.ss_coupon_amt <= 15792.00))) OR ((store_sales.ss_wholesale_cost >=
46.00) AND (store_sales.ss_wholesale_cost <= 66.00)))(store_sales.ss_quantity
>= 6))
------------------------------------------------PhysicalOlapScan[store_sales]
---------------------PhysicalDistribute
-----------------------PhysicalLimit
-------------------------PhysicalLimit
---------------------------hashAgg[DISTINCT_GLOBAL]
-----------------------------PhysicalDistribute
-------------------------------hashAgg[DISTINCT_LOCAL]
+--------------------------PhysicalDistribute
+----------------------------PhysicalLimit
+------------------------------PhysicalLimit
--------------------------------hashAgg[GLOBAL]
----------------------------------PhysicalDistribute
------------------------------------hashAgg[LOCAL]
--------------------------------------PhysicalProject
-----------------------------------------filter((store_sales.ss_quantity <=
20)((((store_sales.ss_list_price >= 91.00) AND (store_sales.ss_list_price <=
101.00)) OR ((store_sales.ss_coupon_amt >= 13493.00) AND
(store_sales.ss_coupon_amt <= 14493.00))) OR ((store_sales.ss_wholesale_cost >=
36.00) AND (store_sales.ss_wholesale_cost <= 56.00)))(store_sales.ss_quantity
>= 16))
+----------------------------------------filter(((((store_sales.ss_list_price
>= 1.5E+2) AND (store_sales.ss_list_price <= 1.6E+2)) OR
((store_sales.ss_coupon_amt >= 6.6E+3) AND (store_sales.ss_coupon_amt <=
7.6E+3))) OR ((store_sales.ss_wholesale_cost >= 9.00) AND
(store_sales.ss_wholesale_cost <= 29.00)))(store_sales.ss_quantity >=
11)(store_sales.ss_quantity <= 15))
------------------------------------------PhysicalOlapScan[store_sales]
---------------PhysicalDistribute
-----------------PhysicalLimit
-------------------PhysicalLimit
---------------------hashAgg[DISTINCT_GLOBAL]
-----------------------PhysicalDistribute
-------------------------hashAgg[DISTINCT_LOCAL]
+--------------------PhysicalDistribute
+----------------------PhysicalLimit
+------------------------PhysicalLimit
--------------------------hashAgg[GLOBAL]
----------------------------PhysicalDistribute
------------------------------hashAgg[LOCAL]
--------------------------------PhysicalProject
-----------------------------------filter(((((store_sales.ss_list_price >=
0.00) AND (store_sales.ss_list_price <= 10.00)) OR ((store_sales.ss_coupon_amt
>= 7629.00) AND (store_sales.ss_coupon_amt <= 8629.00))) OR
((store_sales.ss_wholesale_cost >= 6.00) AND (store_sales.ss_wholesale_cost <=
26.00)))(store_sales.ss_quantity <= 25)(store_sales.ss_quantity >= 21))
+----------------------------------filter((store_sales.ss_quantity <=
20)((((store_sales.ss_list_price >= 91.00) AND (store_sales.ss_list_price <=
101.00)) OR ((store_sales.ss_coupon_amt >= 13493.00) AND
(store_sales.ss_coupon_amt <= 14493.00))) OR ((store_sales.ss_wholesale_cost >=
36.00) AND (store_sales.ss_wholesale_cost <= 56.00)))(store_sales.ss_quantity
>= 16))
------------------------------------PhysicalOlapScan[store_sales]
---------PhysicalDistribute
-----------PhysicalLimit
-------------PhysicalLimit
---------------hashAgg[DISTINCT_GLOBAL]
-----------------PhysicalDistribute
-------------------hashAgg[DISTINCT_LOCAL]
+--------------PhysicalDistribute
+----------------PhysicalLimit
+------------------PhysicalLimit
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------filter((store_sales.ss_quantity >=
26)((((store_sales.ss_list_price >= 89.00) AND (store_sales.ss_list_price <=
99.00)) OR ((store_sales.ss_coupon_amt >= 15257.00) AND
(store_sales.ss_coupon_amt <= 16257.00))) OR ((store_sales.ss_wholesale_cost >=
31.00) AND (store_sales.ss_wholesale_cost <= 51.00)))(store_sales.ss_quantity
<= 30))
+----------------------------filter(((((store_sales.ss_list_price >= 0.00) AND
(store_sales.ss_list_price <= 10.00)) OR ((store_sales.ss_coupon_amt >=
7629.00) AND (store_sales.ss_coupon_amt <= 8629.00))) OR
((store_sales.ss_wholesale_cost >= 6.00) AND (store_sales.ss_wholesale_cost <=
26.00)))(store_sales.ss_quantity <= 25)(store_sales.ss_quantity >= 21))
------------------------------PhysicalOlapScan[store_sales]
+--------PhysicalDistribute
+----------PhysicalLimit
+------------PhysicalLimit
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------filter((store_sales.ss_quantity >=
26)((((store_sales.ss_list_price >= 89.00) AND (store_sales.ss_list_price <=
99.00)) OR ((store_sales.ss_coupon_amt >= 15257.00) AND
(store_sales.ss_coupon_amt <= 16257.00))) OR ((store_sales.ss_wholesale_cost >=
31.00) AND (store_sales.ss_wholesale_cost <= 51.00)))(store_sales.ss_quantity
<= 30))
+------------------------PhysicalOlapScan[store_sales]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query29.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query29.out
index 95be784e2a..b23eeda8fa 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query29.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query29.out
@@ -27,20 +27,20 @@ PhysicalTopN
----------------------------------PhysicalOlapScan[catalog_sales]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
+------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk =
store_sales.ss_sold_date_sk)
--------------------------------------PhysicalProject
----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[store_returns]
-------------------------------------------hashJoin[INNER_JOIN](d1.d_date_sk =
store_sales.ss_sold_date_sk)
+--------------------------------------------PhysicalOlapScan[store_sales]
+------------------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------------PhysicalOlapScan[store_returns]
--------------------------------------------PhysicalDistribute
----------------------------------------------PhysicalProject
-------------------------------------------------filter((d1.d_year =
1999)(d1.d_moy = 4))
+------------------------------------------------filter((d2.d_moy <=
7)(d2.d_moy >= 4)(d2.d_year = 1999))
--------------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((d2.d_moy <= 7)(d2.d_moy >=
4)(d2.d_year = 1999))
+------------------------------------------filter((d1.d_year = 1999)(d1.d_moy =
4))
--------------------------------------------PhysicalOlapScan[date_dim]
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 7465afe902..48d9738a11 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
@@ -14,7 +14,7 @@ CteAnchor[cteId= ( CTEId#3=] )
----------------------PhysicalOlapScan[inventory]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((inv.d_moy = 1) OR (inv.d_moy =
2))(date_dim.d_year = 1998))
+--------------------------filter(d_moy IN (1, 2)(date_dim.d_year = 1998))
----------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query48.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query48.out
index 304b8fbcf0..20b2439b02 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query48.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query48.out
@@ -6,26 +6,26 @@ hashAgg[GLOBAL]
------PhysicalProject
--------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
----------PhysicalProject
-------------PhysicalOlapScan[store]
-----------PhysicalDistribute
-------------PhysicalProject
+------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
date_dim.d_date_sk)
--------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk =
customer_address.ca_address_sk)(((ca_state IN ('MD', 'MN', 'IA') AND
((store_sales.ss_net_profit >= 0.00) AND (store_sales.ss_net_profit <=
2000.00))) OR (ca_state IN ('VA', 'IL', 'TX') AND ((store_sales.ss_net_profit
>= 150.00) AND (store_sales.ss_net_profit <= 3000.00)))) OR (ca_state IN ('MI',
'WI', 'IN') AND ((store_sales.ss_net_profit >= 50.00) AND
(store_sales.ss_net_profit <= 25000.00))))
-----------------PhysicalProject
-------------------filter(((ca_state IN ('MD', 'MN', 'IA') OR ca_state IN
('VA', 'IL', 'TX')) OR ca_state IN ('MI', 'WI',
'IN'))(customer_address.ca_country = 'United States'))
---------------------PhysicalOlapScan[customer_address]
+----------------PhysicalDistribute
+------------------hashJoin[INNER_JOIN](customer_demographics.cd_demo_sk =
store_sales.ss_cdemo_sk)(((((cast(cd_marital_status as VARCHAR(*)) = 'U') AND
(cast(cd_education_status as VARCHAR(*)) = 'Primary')) AND
((store_sales.ss_sales_price >= 100.00) AND (store_sales.ss_sales_price <=
150.00))) OR (((cast(cd_marital_status as VARCHAR(*)) = 'W') AND
(cast(cd_education_status as VARCHAR(*)) = 'College')) AND
((store_sales.ss_sales_price >= 50.00) AND (store_sales.ss_sales_price <=
100.00)) [...]
+--------------------PhysicalProject
+----------------------filter(((((store_sales.ss_net_profit >= 0.00) AND
(store_sales.ss_net_profit <= 2000.00)) OR ((store_sales.ss_net_profit >=
150.00) AND (store_sales.ss_net_profit <= 3000.00))) OR
((store_sales.ss_net_profit >= 50.00) AND (store_sales.ss_net_profit <=
25000.00)))((((store_sales.ss_sales_price >= 100.00) AND
(store_sales.ss_sales_price <= 150.00)) OR ((store_sales.ss_sales_price >=
50.00) AND (store_sales.ss_sales_price <= 100.00))) OR
((store_sales.ss_sales_price >= [...]
+------------------------PhysicalOlapScan[store_sales]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter(((((cast(cd_marital_status as VARCHAR(*)) =
'U') AND (cast(cd_education_status as VARCHAR(*)) = 'Primary')) OR
((cast(cd_marital_status as VARCHAR(*)) = 'W') AND (cast(cd_education_status as
VARCHAR(*)) = 'College'))) OR ((cast(cd_marital_status as VARCHAR(*)) = 'D')
AND (cast(cd_education_status as VARCHAR(*)) = '2 yr Degree'))))
+--------------------------PhysicalOlapScan[customer_demographics]
----------------PhysicalDistribute
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
date_dim.d_date_sk)
-----------------------hashJoin[INNER_JOIN](customer_demographics.cd_demo_sk =
store_sales.ss_cdemo_sk)(((((cast(cd_marital_status as VARCHAR(*)) = 'U') AND
(cast(cd_education_status as VARCHAR(*)) = 'Primary')) AND
((store_sales.ss_sales_price >= 100.00) AND (store_sales.ss_sales_price <=
150.00))) OR (((cast(cd_marital_status as VARCHAR(*)) = 'W') AND
(cast(cd_education_status as VARCHAR(*)) = 'College')) AND
((store_sales.ss_sales_price >= 50.00) AND (store_sales.ss_sales_price <= 100.
[...]
-------------------------PhysicalProject
---------------------------filter(((((store_sales.ss_net_profit >= 0.00) AND
(store_sales.ss_net_profit <= 2000.00)) OR ((store_sales.ss_net_profit >=
150.00) AND (store_sales.ss_net_profit <= 3000.00))) OR
((store_sales.ss_net_profit >= 50.00) AND (store_sales.ss_net_profit <=
25000.00)))((((store_sales.ss_sales_price >= 100.00) AND
(store_sales.ss_sales_price <= 150.00)) OR ((store_sales.ss_sales_price >=
50.00) AND (store_sales.ss_sales_price <= 100.00))) OR
((store_sales.ss_sales_pric [...]
-----------------------------PhysicalOlapScan[store_sales]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------filter(((((cast(cd_marital_status as VARCHAR(*)) =
'U') AND (cast(cd_education_status as VARCHAR(*)) = 'Primary')) OR
((cast(cd_marital_status as VARCHAR(*)) = 'W') AND (cast(cd_education_status as
VARCHAR(*)) = 'College'))) OR ((cast(cd_marital_status as VARCHAR(*)) = 'D')
AND (cast(cd_education_status as VARCHAR(*)) = '2 yr Degree'))))
-------------------------------PhysicalOlapScan[customer_demographics]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((date_dim.d_year = 1999))
-----------------------------PhysicalOlapScan[date_dim]
+--------------------filter(((ca_state IN ('MD', 'MN', 'IA') OR ca_state IN
('VA', 'IL', 'TX')) OR ca_state IN ('MI', 'WI',
'IN'))(customer_address.ca_country = 'United States'))
+----------------------PhysicalOlapScan[customer_address]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------filter((date_dim.d_year = 1999))
+--------------------PhysicalOlapScan[date_dim]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[store]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query50.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query50.out
index f1338ac40a..921f84d72c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query50.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query50.out
@@ -8,22 +8,24 @@ PhysicalTopN
----------hashAgg[LOCAL]
------------PhysicalProject
--------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
-----------------PhysicalProject
-------------------PhysicalOlapScan[store]
----------------PhysicalDistribute
-------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
d1.d_date_sk)
---------------------PhysicalProject
-----------------------PhysicalOlapScan[date_dim]
---------------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
d1.d_date_sk)
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk =
store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[store_sales]
---------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
+------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk =
store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)(store_sales.ss_customer_sk =
store_returns.sr_customer_sk)
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[store_returns]
-----------------------------PhysicalDistribute
+------------------------------PhysicalOlapScan[store_sales]
+----------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk
= d2.d_date_sk)
------------------------------PhysicalProject
---------------------------------filter((d2.d_year = 2001)(d2.d_moy = 8))
-----------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalOlapScan[store_returns]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((d2.d_year = 2001)(d2.d_moy = 8))
+------------------------------------PhysicalOlapScan[date_dim]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[store]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
index 9af8591c8f..58e7e2211c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
@@ -17,32 +17,33 @@ CteAnchor[cteId= ( CTEId#4=] )
----PhysicalDistribute
------PhysicalTopN
--------PhysicalProject
-----------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq2)
+----------hashJoin[INNER_JOIN](y.s_store_id1 =
x.s_store_id2)(expr_cast(d_week_seq1 as BIGINT) = expr_(d_week_seq2 - 52))
------------PhysicalDistribute
--------------PhysicalProject
-----------------filter((d.d_month_seq <= 1219)(d.d_month_seq >= 1208))
-------------------PhysicalOlapScan[date_dim]
-------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](y.s_store_id1 =
x.s_store_id2)(wss.ss_store_sk = store.s_store_sk)
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------PhysicalOlapScan[store]
-----------------PhysicalDistribute
-------------------hashJoin[INNER_JOIN](expr_cast(d_week_seq1 as BIGINT) =
expr_(d_week_seq2 - 52))
+----------------hashJoin[INNER_JOIN](wss.ss_store_sk = store.s_store_sk)
+------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq2)
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------CteConsumer[cteId= ( CTEId#4=] )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((d.d_month_seq <= 1219)(d.d_month_seq >= 1208))
+--------------------------PhysicalOlapScan[date_dim]
+------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------CteConsumer[cteId= ( CTEId#4=] )
+----------------------PhysicalOlapScan[store]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](wss.ss_store_sk = store.s_store_sk)
+------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq1)
--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](wss.ss_store_sk =
store.s_store_sk)
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[store]
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq1)
-------------------------------PhysicalProject
---------------------------------CteConsumer[cteId= ( CTEId#4=] )
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((d.d_month_seq <= 1207)(d.d_month_seq
>= 1196))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------CteConsumer[cteId= ( CTEId#4=] )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((d.d_month_seq <= 1207)(d.d_month_seq >= 1196))
+--------------------------PhysicalOlapScan[date_dim]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[store]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
index 072c3b8f85..beaf2eb582 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
@@ -8,31 +8,31 @@ PhysicalTopN
----------PhysicalDistribute
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+----------------hashJoin[INNER_JOIN](customer_address.ca_address_sk =
customer.c_current_addr_sk)
------------------PhysicalProject
---------------------filter((cast(i_category as VARCHAR(*)) = 'Jewelry'))
-----------------------PhysicalOlapScan[item]
+--------------------filter((customer_address.ca_gmt_offset = -7.00))
+----------------------PhysicalOlapScan[customer_address]
------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk =
promotion.p_promo_sk)
+--------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk =
item.i_item_sk)
+----------------------PhysicalProject
+------------------------filter((cast(i_category as VARCHAR(*)) = 'Jewelry'))
+--------------------------PhysicalOlapScan[item]
+----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter((((cast(p_channel_dmail as VARCHAR(*)) = 'Y')
OR (cast(p_channel_email as VARCHAR(*)) = 'Y')) OR (cast(p_channel_tv as
VARCHAR(*)) = 'Y')))
-----------------------------PhysicalOlapScan[promotion]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
date_dim.d_date_sk)
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk =
customer.c_customer_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[customer]
+----------------------------PhysicalDistribute
------------------------------PhysicalProject
---------------------------------filter((date_dim.d_moy = 11)(date_dim.d_year =
1999))
-----------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalDistribute
---------------------------------hashJoin[INNER_JOIN](customer_address.ca_address_sk
= customer.c_current_addr_sk)
+--------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk =
promotion.p_promo_sk)
----------------------------------PhysicalProject
-------------------------------------filter((customer_address.ca_gmt_offset =
-7.00))
---------------------------------------PhysicalOlapScan[customer_address]
+------------------------------------filter((((cast(p_channel_dmail as
VARCHAR(*)) = 'Y') OR (cast(p_channel_email as VARCHAR(*)) = 'Y')) OR
(cast(p_channel_tv as VARCHAR(*)) = 'Y')))
+--------------------------------------PhysicalOlapScan[promotion]
----------------------------------PhysicalDistribute
-------------------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk
= customer.c_customer_sk)
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk
= date_dim.d_date_sk)
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer]
+----------------------------------------filter((date_dim.d_moy =
11)(date_dim.d_year = 1999))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk
= store.s_store_sk)
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query64.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query64.out
index 0a8fe40145..bcef9770a8 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query64.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query64.out
@@ -7,112 +7,117 @@ CteAnchor[cteId= ( CTEId#14=] )
--------PhysicalDistribute
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN](customer.c_current_addr_sk =
ad2.ca_address_sk)
+--------------hashJoin[INNER_JOIN](customer.c_first_shipto_date_sk =
d3.d_date_sk)
----------------PhysicalProject
-------------------PhysicalOlapScan[customer_address]
+------------------PhysicalOlapScan[date_dim]
----------------PhysicalDistribute
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk =
cs_ui.cs_item_sk)
+--------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk =
ad2.ca_address_sk)
----------------------PhysicalProject
-------------------------filter((sale > (2 * refund)))
---------------------------hashAgg[GLOBAL]
-----------------------------PhysicalDistribute
-------------------------------hashAgg[LOCAL]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk
= catalog_returns.cr_item_sk)(catalog_sales.cs_order_number =
catalog_returns.cr_order_number)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[catalog_sales]
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[catalog_returns]
+------------------------PhysicalOlapScan[customer_address]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
d1.d_date_sk)
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk =
cs_ui.cs_item_sk)
----------------------------PhysicalProject
-------------------------------filter(((d1.d_year = 2001) OR (d1.d_year =
2002)))
---------------------------------PhysicalOlapScan[date_dim]
+------------------------------filter((sale > (2 * refund)))
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk
= catalog_returns.cr_item_sk)(catalog_sales.cs_order_number =
catalog_returns.cr_order_number)
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[catalog_sales]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[catalog_returns]
----------------------------PhysicalDistribute
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](store_sales.ss_hdemo_sk =
hd1.hd_demo_sk)
+--------------------------------hashJoin[INNER_JOIN](hd2.hd_income_band_sk =
ib2.ib_income_band_sk)
----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](hd1.hd_income_band_sk
= ib1.ib_income_band_sk)
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[household_demographics]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[income_band]
+------------------------------------PhysicalOlapScan[income_band]
----------------------------------PhysicalDistribute
------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk
= promotion.p_promo_sk)
+--------------------------------------hashJoin[INNER_JOIN](customer.c_first_sales_date_sk
= d2.d_date_sk)
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[promotion]
+------------------------------------------PhysicalOlapScan[date_dim]
----------------------------------------PhysicalDistribute
------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN](customer.c_first_shipto_date_sk
= d3.d_date_sk)
+--------------------------------------------hashJoin[INNER_JOIN](customer.c_current_hdemo_sk
= hd2.hd_demo_sk)
----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------PhysicalOlapScan[household_demographics]
----------------------------------------------PhysicalDistribute
------------------------------------------------PhysicalProject
---------------------------------------------------hashJoin[INNER_JOIN](hd2.hd_income_band_sk
= ib2.ib_income_band_sk)
-----------------------------------------------------PhysicalDistribute
-------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[income_band]
+--------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk
= promotion.p_promo_sk)
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[promotion]
----------------------------------------------------PhysicalDistribute
------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN](customer.c_first_sales_date_sk
= d2.d_date_sk)
+--------------------------------------------------------hashJoin[INNER_JOIN](hd1.hd_income_band_sk
= ib1.ib_income_band_sk)
----------------------------------------------------------PhysicalProject
-------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------PhysicalOlapScan[income_band]
----------------------------------------------------------PhysicalDistribute
------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN](customer.c_current_hdemo_sk
= hd2.hd_demo_sk)
+--------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[household_demographics]
+------------------------------------------------------------------PhysicalOlapScan[store_returns]
----------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------PhysicalProject
--------------------------------------------------------------------hashJoin[INNER_JOIN](customer.c_current_cdemo_sk
= cd2.cd_demo_sk)( not (cd_marital_status = cd_marital_status))
-----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
----------------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk
= customer.c_customer_sk)
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------PhysicalOlapScan[customer]
+--------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_cdemo_sk
= cd1.cd_demo_sk)
+----------------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
----------------------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_cdemo_sk
= cd1.cd_demo_sk)
+--------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk
= d1.d_date_sk)
----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
-----------------------------------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk
= store.s_store_sk)
-----------------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk
= ad1.ca_address_sk)
+------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_hdemo_sk
= hd1.hd_demo_sk)
+--------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk
= customer.c_customer_sk)
+------------------------------------------------------------------------------------------PhysicalDistribute
--------------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------------PhysicalOlapScan[customer_address]
---------------------------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= store_returns.sr_item_sk)(store_sales.ss_ticket_number =
store_returns.sr_ticket_number)
---------------------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
---------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= item_sk)
-----------------------------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------------------------------------------------------------PhysicalOlapScan[customer]
+------------------------------------------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk
= store.s_store_sk)
+------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk
= ad1.ca_address_sk)
+----------------------------------------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk
= item_sk)
+--------------------------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------------------------------------------------------------------------PhysicalDistribute
+----------------------------------------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------------------------------------filter((item.i_current_price
>= 24.00)(item.i_current_price <= 33.00)i_color IN ('blanched', 'medium',
'brown', 'chocolate', 'burlywood', 'drab'))
+--------------------------------------------------------------------------------------------------------------PhysicalOlapScan[item]
----------------------------------------------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------------------filter((item.i_current_price
>= 24.00)(item.i_current_price <= 33.00)i_color IN ('blanched', 'medium',
'brown', 'chocolate', 'burlywood', 'drab'))
-----------------------------------------------------------------------------------------------------------PhysicalOlapScan[item]
-----------------------------------------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------PhysicalOlapScan[store]
+--------------------------------------------------------------------------------------------------------PhysicalOlapScan[customer_address]
+------------------------------------------------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------------------PhysicalOlapScan[store]
+--------------------------------------------------------------------------------------PhysicalDistribute
+----------------------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------filter(((d1.d_year
= 2001) OR (d1.d_year = 2002)))
+----------------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
--PhysicalQuickSort
----PhysicalDistribute
------PhysicalQuickSort
--------PhysicalProject
----------hashJoin[INNER_JOIN](cs1.item_sk = cs2.item_sk)(cs1.store_name =
cs2.store_name)(cs1.store_zip = cs2.store_zip)(cs2.cnt <= cs1.cnt)
-------------PhysicalProject
---------------filter((cs1.syear = 2001))
-----------------CteConsumer[cteId= ( CTEId#14=] )
------------PhysicalDistribute
--------------PhysicalProject
----------------filter((cs2.syear = 2002))
------------------CteConsumer[cteId= ( CTEId#14=] )
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((cs1.syear = 2001))
+------------------CteConsumer[cteId= ( CTEId#14=] )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query85.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query85.out
index e430099ada..a078d9cfe1 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query85.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query85.out
@@ -8,14 +8,14 @@ PhysicalTopN
----------PhysicalDistribute
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk =
web_page.wp_web_page_sk)
+----------------hashJoin[INNER_JOIN](cd2.cd_demo_sk =
web_returns.wr_returning_cdemo_sk)(cd1.cd_marital_status =
cd2.cd_marital_status)(cd1.cd_education_status = cd2.cd_education_status)
------------------PhysicalProject
---------------------PhysicalOlapScan[web_page]
+--------------------PhysicalOlapScan[customer_demographics]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN](cd2.cd_demo_sk =
web_returns.wr_returning_cdemo_sk)(cd1.cd_marital_status =
cd2.cd_marital_status)(cd1.cd_education_status = cd2.cd_education_status)
+----------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk =
web_page.wp_web_page_sk)
------------------------PhysicalProject
---------------------------PhysicalOlapScan[customer_demographics]
+--------------------------PhysicalOlapScan[web_page]
------------------------PhysicalDistribute
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN](cd1.cd_demo_sk =
web_returns.wr_refunded_cdemo_sk)(((((cast(cd_marital_status as VARCHAR(*)) =
'M') AND (cast(cd_education_status as VARCHAR(*)) = '4 yr Degree')) AND
((web_sales.ws_sales_price >= 100.00) AND (web_sales.ws_sales_price <=
150.00))) OR (((cast(cd_marital_status as VARCHAR(*)) = 'S') AND
(cast(cd_education_status as VARCHAR(*)) = 'Secondary')) AND
((web_sales.ws_sales_price >= 50.00) AND (web_sales.ws_sales_price <= 100.00)))
[...]
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query13.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query13.groovy
index 34f4dfc5bd..7f0877ffac 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query13.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query13.groovy
@@ -30,59 +30,59 @@ suite("query13") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_13 '''
-// explain shape plan
+ qt_ds_shape_13 '''
+ explain shape plan
-// select avg(ss_quantity)
-// ,avg(ss_ext_sales_price)
-// ,avg(ss_ext_wholesale_cost)
-// ,sum(ss_ext_wholesale_cost)
-// from store_sales
-// ,store
-// ,customer_demographics
-// ,household_demographics
-// ,customer_address
-// ,date_dim
-// where s_store_sk = ss_store_sk
-// and ss_sold_date_sk = d_date_sk and d_year = 2001
-// and((ss_hdemo_sk=hd_demo_sk
-// and cd_demo_sk = ss_cdemo_sk
-// and cd_marital_status = 'D'
-// and cd_education_status = 'Unknown'
-// and ss_sales_price between 100.00 and 150.00
-// and hd_dep_count = 3
-// )or
-// (ss_hdemo_sk=hd_demo_sk
-// and cd_demo_sk = ss_cdemo_sk
-// and cd_marital_status = 'S'
-// and cd_education_status = 'College'
-// and ss_sales_price between 50.00 and 100.00
-// and hd_dep_count = 1
-// ) or
-// (ss_hdemo_sk=hd_demo_sk
-// and cd_demo_sk = ss_cdemo_sk
-// and cd_marital_status = 'M'
-// and cd_education_status = '4 yr Degree'
-// and ss_sales_price between 150.00 and 200.00
-// and hd_dep_count = 1
-// ))
-// and((ss_addr_sk = ca_address_sk
-// and ca_country = 'United States'
-// and ca_state in ('SD', 'KS', 'MI')
-// and ss_net_profit between 100 and 200
-// ) or
-// (ss_addr_sk = ca_address_sk
-// and ca_country = 'United States'
-// and ca_state in ('MO', 'ND', 'CO')
-// and ss_net_profit between 150 and 300
-// ) or
-// (ss_addr_sk = ca_address_sk
-// and ca_country = 'United States'
-// and ca_state in ('NH', 'OH', 'TX')
-// and ss_net_profit between 50 and 250
-// ))
-// ;
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Unknown'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'S'
+ and cd_education_status = 'College'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('SD', 'KS', 'MI')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MO', 'ND', 'CO')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('NH', 'OH', 'TX')
+ and ss_net_profit between 50 and 250
+ ))
+;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query16.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query16.groovy
index 54a1b91282..4526ca4b1b 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query16.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query16.groovy
@@ -30,40 +30,40 @@ suite("query16") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_16 '''
-// explain shape plan
+ qt_ds_shape_16 '''
+ explain shape plan
-// select
-// count(distinct cs_order_number) as "order count"
-// ,sum(cs_ext_ship_cost) as "total shipping cost"
-// ,sum(cs_net_profit) as "total net profit"
-// from
-// catalog_sales cs1
-// ,date_dim
-// ,customer_address
-// ,call_center
-// where
-// d_date between '2002-4-01' and
-// (cast('2002-4-01' as date) + interval 60 day)
-// and cs1.cs_ship_date_sk = d_date_sk
-// and cs1.cs_ship_addr_sk = ca_address_sk
-// and ca_state = 'WV'
-// and cs1.cs_call_center_sk = cc_call_center_sk
-// and cc_county in ('Ziebach County','Luce County','Richland County','Daviess
County',
-// 'Barrow County'
-// )
-// and exists (select *
-// from catalog_sales cs2
-// where cs1.cs_order_number = cs2.cs_order_number
-// and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
-// and not exists(select *
-// from catalog_returns cr1
-// where cs1.cs_order_number = cr1.cr_order_number)
-// order by count(distinct cs_order_number)
-// limit 100;
+select
+ count(distinct cs_order_number) as "order count"
+ ,sum(cs_ext_ship_cost) as "total shipping cost"
+ ,sum(cs_net_profit) as "total net profit"
+from
+ catalog_sales cs1
+ ,date_dim
+ ,customer_address
+ ,call_center
+where
+ d_date between '2002-4-01' and
+ (cast('2002-4-01' as date) + interval 60 day)
+and cs1.cs_ship_date_sk = d_date_sk
+and cs1.cs_ship_addr_sk = ca_address_sk
+and ca_state = 'WV'
+and cs1.cs_call_center_sk = cc_call_center_sk
+and cc_county in ('Ziebach County','Luce County','Richland County','Daviess
County',
+ 'Barrow County'
+)
+and exists (select *
+ from catalog_sales cs2
+ where cs1.cs_order_number = cs2.cs_order_number
+ and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
+and not exists(select *
+ from catalog_returns cr1
+ where cs1.cs_order_number = cr1.cr_order_number)
+order by count(distinct cs_order_number)
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query17.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query17.groovy
index ae4dd67d4f..5ab0135501 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query17.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query17.groovy
@@ -30,54 +30,54 @@ suite("query17") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_17 '''
-// explain shape plan
+ qt_ds_shape_17 '''
+ explain shape plan
-// select i_item_id
-// ,i_item_desc
-// ,s_state
-// ,count(ss_quantity) as store_sales_quantitycount
-// ,avg(ss_quantity) as store_sales_quantityave
-// ,stddev_samp(ss_quantity) as store_sales_quantitystdev
-// ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
-// ,count(sr_return_quantity) as store_returns_quantitycount
-// ,avg(sr_return_quantity) as store_returns_quantityave
-// ,stddev_samp(sr_return_quantity) as store_returns_quantitystdev
-// ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as
store_returns_quantitycov
-// ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity)
as catalog_sales_quantityave
-// ,stddev_samp(cs_quantity) as catalog_sales_quantitystdev
-// ,stddev_samp(cs_quantity)/avg(cs_quantity) as
catalog_sales_quantitycov
-// from store_sales
-// ,store_returns
-// ,catalog_sales
-// ,date_dim d1
-// ,date_dim d2
-// ,date_dim d3
-// ,store
-// ,item
-// where d1.d_quarter_name = '2001Q1'
-// and d1.d_date_sk = ss_sold_date_sk
-// and i_item_sk = ss_item_sk
-// and s_store_sk = ss_store_sk
-// and ss_customer_sk = sr_customer_sk
-// and ss_item_sk = sr_item_sk
-// and ss_ticket_number = sr_ticket_number
-// and sr_returned_date_sk = d2.d_date_sk
-// and d2.d_quarter_name in ('2001Q1','2001Q2','2001Q3')
-// and sr_customer_sk = cs_bill_customer_sk
-// and sr_item_sk = cs_item_sk
-// and cs_sold_date_sk = d3.d_date_sk
-// and d3.d_quarter_name in ('2001Q1','2001Q2','2001Q3')
-// group by i_item_id
-// ,i_item_desc
-// ,s_state
-// order by i_item_id
-// ,i_item_desc
-// ,s_state
-// limit 100;
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as store_returns_quantitycount
+ ,avg(sr_return_quantity) as store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as
store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as
catalog_sales_quantityave
+ ,stddev_samp(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2001Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2001Q1','2001Q2','2001Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2001Q1','2001Q2','2001Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query25.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query25.groovy
index 8ee95faf2b..ac8febe1a4 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query25.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query25.groovy
@@ -30,56 +30,56 @@ suite("query25") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_25 '''
-// explain shape plan
+ qt_ds_shape_25 '''
+ explain shape plan
-// select
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// ,sum(ss_net_profit) as store_sales_profit
-// ,sum(sr_net_loss) as store_returns_loss
-// ,sum(cs_net_profit) as catalog_sales_profit
-// from
-// store_sales
-// ,store_returns
-// ,catalog_sales
-// ,date_dim d1
-// ,date_dim d2
-// ,date_dim d3
-// ,store
-// ,item
-// where
-// d1.d_moy = 4
-// and d1.d_year = 2000
-// and d1.d_date_sk = ss_sold_date_sk
-// and i_item_sk = ss_item_sk
-// and s_store_sk = ss_store_sk
-// and ss_customer_sk = sr_customer_sk
-// and ss_item_sk = sr_item_sk
-// and ss_ticket_number = sr_ticket_number
-// and sr_returned_date_sk = d2.d_date_sk
-// and d2.d_moy between 4 and 10
-// and d2.d_year = 2000
-// and sr_customer_sk = cs_bill_customer_sk
-// and sr_item_sk = cs_item_sk
-// and cs_sold_date_sk = d3.d_date_sk
-// and d3.d_moy between 4 and 10
-// and d3.d_year = 2000
-// group by
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// order by
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// limit 100;
+select
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ,sum(ss_net_profit) as store_sales_profit
+ ,sum(sr_net_loss) as store_returns_loss
+ ,sum(cs_net_profit) as catalog_sales_profit
+ from
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where
+ d1.d_moy = 4
+ and d1.d_year = 2000
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_moy between 4 and 10
+ and d2.d_year = 2000
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_moy between 4 and 10
+ and d3.d_year = 2000
+ group by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ order by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query28.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query28.groovy
index 60d06597c0..d9c8cb0fa8 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query28.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query28.groovy
@@ -30,62 +30,62 @@ suite("query28") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_28 '''
-// explain shape plan
+ qt_ds_shape_28 '''
+ explain shape plan
-// select *
-// from (select avg(ss_list_price) B1_LP
-// ,count(ss_list_price) B1_CNT
-// ,count(distinct ss_list_price) B1_CNTD
-// from store_sales
-// where ss_quantity between 0 and 5
-// and (ss_list_price between 131 and 131+10
-// or ss_coupon_amt between 16798 and 16798+1000
-// or ss_wholesale_cost between 25 and 25+20)) B1,
-// (select avg(ss_list_price) B2_LP
-// ,count(ss_list_price) B2_CNT
-// ,count(distinct ss_list_price) B2_CNTD
-// from store_sales
-// where ss_quantity between 6 and 10
-// and (ss_list_price between 145 and 145+10
-// or ss_coupon_amt between 14792 and 14792+1000
-// or ss_wholesale_cost between 46 and 46+20)) B2,
-// (select avg(ss_list_price) B3_LP
-// ,count(ss_list_price) B3_CNT
-// ,count(distinct ss_list_price) B3_CNTD
-// from store_sales
-// where ss_quantity between 11 and 15
-// and (ss_list_price between 150 and 150+10
-// or ss_coupon_amt between 6600 and 6600+1000
-// or ss_wholesale_cost between 9 and 9+20)) B3,
-// (select avg(ss_list_price) B4_LP
-// ,count(ss_list_price) B4_CNT
-// ,count(distinct ss_list_price) B4_CNTD
-// from store_sales
-// where ss_quantity between 16 and 20
-// and (ss_list_price between 91 and 91+10
-// or ss_coupon_amt between 13493 and 13493+1000
-// or ss_wholesale_cost between 36 and 36+20)) B4,
-// (select avg(ss_list_price) B5_LP
-// ,count(ss_list_price) B5_CNT
-// ,count(distinct ss_list_price) B5_CNTD
-// from store_sales
-// where ss_quantity between 21 and 25
-// and (ss_list_price between 0 and 0+10
-// or ss_coupon_amt between 7629 and 7629+1000
-// or ss_wholesale_cost between 6 and 6+20)) B5,
-// (select avg(ss_list_price) B6_LP
-// ,count(ss_list_price) B6_CNT
-// ,count(distinct ss_list_price) B6_CNTD
-// from store_sales
-// where ss_quantity between 26 and 30
-// and (ss_list_price between 89 and 89+10
-// or ss_coupon_amt between 15257 and 15257+1000
-// or ss_wholesale_cost between 31 and 31+20)) B6
-// limit 100;
+select *
+from (select avg(ss_list_price) B1_LP
+ ,count(ss_list_price) B1_CNT
+ ,count(distinct ss_list_price) B1_CNTD
+ from store_sales
+ where ss_quantity between 0 and 5
+ and (ss_list_price between 131 and 131+10
+ or ss_coupon_amt between 16798 and 16798+1000
+ or ss_wholesale_cost between 25 and 25+20)) B1,
+ (select avg(ss_list_price) B2_LP
+ ,count(ss_list_price) B2_CNT
+ ,count(distinct ss_list_price) B2_CNTD
+ from store_sales
+ where ss_quantity between 6 and 10
+ and (ss_list_price between 145 and 145+10
+ or ss_coupon_amt between 14792 and 14792+1000
+ or ss_wholesale_cost between 46 and 46+20)) B2,
+ (select avg(ss_list_price) B3_LP
+ ,count(ss_list_price) B3_CNT
+ ,count(distinct ss_list_price) B3_CNTD
+ from store_sales
+ where ss_quantity between 11 and 15
+ and (ss_list_price between 150 and 150+10
+ or ss_coupon_amt between 6600 and 6600+1000
+ or ss_wholesale_cost between 9 and 9+20)) B3,
+ (select avg(ss_list_price) B4_LP
+ ,count(ss_list_price) B4_CNT
+ ,count(distinct ss_list_price) B4_CNTD
+ from store_sales
+ where ss_quantity between 16 and 20
+ and (ss_list_price between 91 and 91+10
+ or ss_coupon_amt between 13493 and 13493+1000
+ or ss_wholesale_cost between 36 and 36+20)) B4,
+ (select avg(ss_list_price) B5_LP
+ ,count(ss_list_price) B5_CNT
+ ,count(distinct ss_list_price) B5_CNTD
+ from store_sales
+ where ss_quantity between 21 and 25
+ and (ss_list_price between 0 and 0+10
+ or ss_coupon_amt between 7629 and 7629+1000
+ or ss_wholesale_cost between 6 and 6+20)) B5,
+ (select avg(ss_list_price) B6_LP
+ ,count(ss_list_price) B6_CNT
+ ,count(distinct ss_list_price) B6_CNTD
+ from store_sales
+ where ss_quantity between 26 and 30
+ and (ss_list_price between 89 and 89+10
+ or ss_coupon_amt between 15257 and 15257+1000
+ or ss_wholesale_cost between 31 and 31+20)) B6
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query29.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query29.groovy
index 471879a185..b097a0715a 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query29.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query29.groovy
@@ -30,56 +30,56 @@ suite("query29") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_29 '''
-// explain shape plan
+ qt_ds_shape_29 '''
+ explain shape plan
-// select
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// ,avg(ss_quantity) as store_sales_quantity
-// ,avg(sr_return_quantity) as store_returns_quantity
-// ,avg(cs_quantity) as catalog_sales_quantity
-// from
-// store_sales
-// ,store_returns
-// ,catalog_sales
-// ,date_dim d1
-// ,date_dim d2
-// ,date_dim d3
-// ,store
-// ,item
-// where
-// d1.d_moy = 4
-// and d1.d_year = 1999
-// and d1.d_date_sk = ss_sold_date_sk
-// and i_item_sk = ss_item_sk
-// and s_store_sk = ss_store_sk
-// and ss_customer_sk = sr_customer_sk
-// and ss_item_sk = sr_item_sk
-// and ss_ticket_number = sr_ticket_number
-// and sr_returned_date_sk = d2.d_date_sk
-// and d2.d_moy between 4 and 4 + 3
-// and d2.d_year = 1999
-// and sr_customer_sk = cs_bill_customer_sk
-// and sr_item_sk = cs_item_sk
-// and cs_sold_date_sk = d3.d_date_sk
-// and d3.d_year in (1999,1999+1,1999+2)
-// group by
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// order by
-// i_item_id
-// ,i_item_desc
-// ,s_store_id
-// ,s_store_name
-// limit 100;
+select
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ,avg(ss_quantity) as store_sales_quantity
+ ,avg(sr_return_quantity) as store_returns_quantity
+ ,avg(cs_quantity) as catalog_sales_quantity
+ from
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where
+ d1.d_moy = 4
+ and d1.d_year = 1999
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_moy between 4 and 4 + 3
+ and d2.d_year = 1999
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_year in (1999,1999+1,1999+2)
+ group by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ order by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query39.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query39.groovy
index 05a550d181..6ff2d071d7 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query39.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query39.groovy
@@ -30,37 +30,37 @@ suite("query39") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_39 '''
-// explain shape plan
+ qt_ds_shape_39 '''
+ explain shape plan
-// with inv as
-// (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
-// ,stdev,mean, case mean when 0 then null else stdev/mean end cov
-// from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
-// ,stddev_samp(inv_quantity_on_hand)
stdev,avg(inv_quantity_on_hand) mean
-// from inventory
-// ,item
-// ,warehouse
-// ,date_dim
-// where inv_item_sk = i_item_sk
-// and inv_warehouse_sk = w_warehouse_sk
-// and inv_date_sk = d_date_sk
-// and d_year =1998
-// group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
-// where case mean when 0 then 0 else stdev/mean end > 1)
-// select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
-// ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-// from inv inv1,inv inv2
-// where inv1.i_item_sk = inv2.i_item_sk
-// and inv1.w_warehouse_sk = inv2.w_warehouse_sk
-// and inv1.d_moy=1
-// and inv2.d_moy=1+1
-// order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
-// ,inv2.d_moy,inv2.mean, inv2.cov
-// ;
+with inv as
+(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stdev,mean, case mean when 0 then null else stdev/mean end cov
+ from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
+ from inventory
+ ,item
+ ,warehouse
+ ,date_dim
+ where inv_item_sk = i_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_year =1998
+ group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
+ where case mean when 0 then 0 else stdev/mean end > 1)
+select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
+ ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
+from inv inv1,inv inv2
+where inv1.i_item_sk = inv2.i_item_sk
+ and inv1.w_warehouse_sk = inv2.w_warehouse_sk
+ and inv1.d_moy=1
+ and inv2.d_moy=1+1
+order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
+ ,inv2.d_moy,inv2.mean, inv2.cov
+;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query48.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query48.groovy
index 88dcc1fe86..6c54b2fe6e 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query48.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query48.groovy
@@ -30,76 +30,76 @@ suite("query48") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_48 '''
-// explain shape plan
+ qt_ds_shape_48 '''
+ explain shape plan
-// select sum (ss_quantity)
-// from store_sales, store, customer_demographics, customer_address, date_dim
-// where s_store_sk = ss_store_sk
-// and ss_sold_date_sk = d_date_sk and d_year = 1999
-// and
-// (
-// (
-// cd_demo_sk = ss_cdemo_sk
-// and
-// cd_marital_status = 'U'
-// and
-// cd_education_status = 'Primary'
-// and
-// ss_sales_price between 100.00 and 150.00
-// )
-// or
-// (
-// cd_demo_sk = ss_cdemo_sk
-// and
-// cd_marital_status = 'W'
-// and
-// cd_education_status = 'College'
-// and
-// ss_sales_price between 50.00 and 100.00
-// )
-// or
-// (
-// cd_demo_sk = ss_cdemo_sk
-// and
-// cd_marital_status = 'D'
-// and
-// cd_education_status = '2 yr Degree'
-// and
-// ss_sales_price between 150.00 and 200.00
-// )
-// )
-// and
-// (
-// (
-// ss_addr_sk = ca_address_sk
-// and
-// ca_country = 'United States'
-// and
-// ca_state in ('MD', 'MN', 'IA')
-// and ss_net_profit between 0 and 2000
-// )
-// or
-// (ss_addr_sk = ca_address_sk
-// and
-// ca_country = 'United States'
-// and
-// ca_state in ('VA', 'IL', 'TX')
-// and ss_net_profit between 150 and 3000
-// )
-// or
-// (ss_addr_sk = ca_address_sk
-// and
-// ca_country = 'United States'
-// and
-// ca_state in ('MI', 'WI', 'IN')
-// and ss_net_profit between 50 and 25000
-// )
-// )
-// ;
+select sum (ss_quantity)
+ from store_sales, store, customer_demographics, customer_address, date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 1999
+ and
+ (
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'U'
+ and
+ cd_education_status = 'Primary'
+ and
+ ss_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'W'
+ and
+ cd_education_status = 'College'
+ and
+ ss_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'D'
+ and
+ cd_education_status = '2 yr Degree'
+ and
+ ss_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('MD', 'MN', 'IA')
+ and ss_net_profit between 0 and 2000
+ )
+ or
+ (ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('VA', 'IL', 'TX')
+ and ss_net_profit between 150 and 3000
+ )
+ or
+ (ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('MI', 'WI', 'IN')
+ and ss_net_profit between 50 and 25000
+ )
+ )
+;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query50.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query50.groovy
index bf20c965a0..56efd40ac3 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query50.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query50.groovy
@@ -30,68 +30,68 @@ suite("query50") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_50 '''
-// explain shape plan
+ qt_ds_shape_50 '''
+ explain shape plan
-// select
-// s_store_name
-// ,s_company_id
-// ,s_street_number
-// ,s_street_name
-// ,s_street_type
-// ,s_suite_number
-// ,s_city
-// ,s_county
-// ,s_state
-// ,s_zip
-// ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else
0 end) as "30 days"
-// ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
-// (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else
0 end ) as "31-60 days"
-// ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
-// (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else
0 end) as "61-90 days"
-// ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
-// (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else
0 end) as "91-120 days"
-// ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else
0 end) as ">120 days"
-// from
-// store_sales
-// ,store_returns
-// ,store
-// ,date_dim d1
-// ,date_dim d2
-// where
-// d2.d_year = 2001
-// and d2.d_moy = 8
-// and ss_ticket_number = sr_ticket_number
-// and ss_item_sk = sr_item_sk
-// and ss_sold_date_sk = d1.d_date_sk
-// and sr_returned_date_sk = d2.d_date_sk
-// and ss_customer_sk = sr_customer_sk
-// and ss_store_sk = s_store_sk
-// group by
-// s_store_name
-// ,s_company_id
-// ,s_street_number
-// ,s_street_name
-// ,s_street_type
-// ,s_suite_number
-// ,s_city
-// ,s_county
-// ,s_state
-// ,s_zip
-// order by s_store_name
-// ,s_company_id
-// ,s_street_number
-// ,s_street_name
-// ,s_street_type
-// ,s_suite_number
-// ,s_city
-// ,s_county
-// ,s_state
-// ,s_zip
-// limit 100;
+select
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0
end) as "30 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0
end ) as "31-60 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0
end) as "61-90 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0
end) as "91-120 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0
end) as ">120 days"
+from
+ store_sales
+ ,store_returns
+ ,store
+ ,date_dim d1
+ ,date_dim d2
+where
+ d2.d_year = 2001
+and d2.d_moy = 8
+and ss_ticket_number = sr_ticket_number
+and ss_item_sk = sr_item_sk
+and ss_sold_date_sk = d1.d_date_sk
+and sr_returned_date_sk = d2.d_date_sk
+and ss_customer_sk = sr_customer_sk
+and ss_store_sk = s_store_sk
+group by
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+order by s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query59.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query59.groovy
index e9eb44aa2d..f36074309c 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query59.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query59.groovy
@@ -30,52 +30,52 @@ suite("query59") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_59 '''
-// explain shape plan
+ qt_ds_shape_59 '''
+ explain shape plan
-// with wss as
-// (select d_week_seq,
-// ss_store_sk,
-// sum(case when (d_day_name='Sunday') then ss_sales_price else null
end) sun_sales,
-// sum(case when (d_day_name='Monday') then ss_sales_price else null
end) mon_sales,
-// sum(case when (d_day_name='Tuesday') then ss_sales_price else null
end) tue_sales,
-// sum(case when (d_day_name='Wednesday') then ss_sales_price else
null end) wed_sales,
-// sum(case when (d_day_name='Thursday') then ss_sales_price else null
end) thu_sales,
-// sum(case when (d_day_name='Friday') then ss_sales_price else null
end) fri_sales,
-// sum(case when (d_day_name='Saturday') then ss_sales_price else null
end) sat_sales
-// from store_sales,date_dim
-// where d_date_sk = ss_sold_date_sk
-// group by d_week_seq,ss_store_sk
-// )
-// select s_store_name1,s_store_id1,d_week_seq1
-// ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
-// ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
-// ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
-// from
-// (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
-// ,s_store_id s_store_id1,sun_sales sun_sales1
-// ,mon_sales mon_sales1,tue_sales tue_sales1
-// ,wed_sales wed_sales1,thu_sales thu_sales1
-// ,fri_sales fri_sales1,sat_sales sat_sales1
-// from wss,store,date_dim d
-// where d.d_week_seq = wss.d_week_seq and
-// ss_store_sk = s_store_sk and
-// d_month_seq between 1196 and 1196 + 11) y,
-// (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
-// ,s_store_id s_store_id2,sun_sales sun_sales2
-// ,mon_sales mon_sales2,tue_sales tue_sales2
-// ,wed_sales wed_sales2,thu_sales thu_sales2
-// ,fri_sales fri_sales2,sat_sales sat_sales2
-// from wss,store,date_dim d
-// where d.d_week_seq = wss.d_week_seq and
-// ss_store_sk = s_store_sk and
-// d_month_seq between 1196+ 12 and 1196 + 23) x
-// where s_store_id1=s_store_id2
-// and d_week_seq1=d_week_seq2-52
-// order by s_store_name1,s_store_id1,d_week_seq1
-// limit 100;
+with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end)
sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end)
mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null
end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null
end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null
end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end)
fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null
end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196 and 1196 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196+ 12 and 1196 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query61.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query61.groovy
index d6a355b26f..183892d1f0 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query61.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query61.groovy
@@ -30,53 +30,53 @@ suite("query61") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_61 '''
-// explain shape plan
+ qt_ds_shape_61 '''
+ explain shape plan
-// select promotions,total,cast(promotions as decimal(15,4))/cast(total as
decimal(15,4))*100
-// from
-// (select sum(ss_ext_sales_price) promotions
-// from store_sales
-// ,store
-// ,promotion
-// ,date_dim
-// ,customer
-// ,customer_address
-// ,item
-// where ss_sold_date_sk = d_date_sk
-// and ss_store_sk = s_store_sk
-// and ss_promo_sk = p_promo_sk
-// and ss_customer_sk= c_customer_sk
-// and ca_address_sk = c_current_addr_sk
-// and ss_item_sk = i_item_sk
-// and ca_gmt_offset = -7
-// and i_category = 'Jewelry'
-// and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv =
'Y')
-// and s_gmt_offset = -7
-// and d_year = 1999
-// and d_moy = 11) promotional_sales,
-// (select sum(ss_ext_sales_price) total
-// from store_sales
-// ,store
-// ,date_dim
-// ,customer
-// ,customer_address
-// ,item
-// where ss_sold_date_sk = d_date_sk
-// and ss_store_sk = s_store_sk
-// and ss_customer_sk= c_customer_sk
-// and ca_address_sk = c_current_addr_sk
-// and ss_item_sk = i_item_sk
-// and ca_gmt_offset = -7
-// and i_category = 'Jewelry'
-// and s_gmt_offset = -7
-// and d_year = 1999
-// and d_moy = 11) all_sales
-// order by promotions, total
-// limit 100;
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as
decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query64.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query64.groovy
index f0361a55d5..f4ff8f0a8e 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query64.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query64.groovy
@@ -30,130 +30,128 @@ suite("query64") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_64 '''
-// explain shape plan
+ def ds64 = '''
+ with cs_ui as
+ (select cs_item_sk
+ ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+ cross_sales as
+ (select i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_street_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in
('blanched','medium','brown','chocolate','burlywood','drab') and
+ i_current_price between 23 and 23 + 10 and
+ i_current_price between 23 + 1 and 23 + 15
+ group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+ )
+ select cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_street_name
+ ,cs1.b_city
+ ,cs1.b_zip
+ ,cs1.c_street_number
+ ,cs1.c_street_name
+ ,cs1.c_city
+ ,cs1.c_zip
+ ,cs1.syear
+ ,cs1.cnt
+ ,cs1.s1 as s11
+ ,cs1.s2 as s21
+ ,cs1.s3 as s31
+ ,cs2.s1 as s12
+ ,cs2.s2 as s22
+ ,cs2.s3 as s32
+ ,cs2.syear
+ ,cs2.cnt
+ from cross_sales cs1,cross_sales cs2
+ where cs1.item_sk=cs2.item_sk and
+ cs1.syear = 2001 and
+ cs2.syear = 2001 + 1 and
+ cs2.cnt <= cs1.cnt and
+ cs1.store_name = cs2.store_name and
+ cs1.store_zip = cs2.store_zip
+ order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt
+ ,cs1.s1
+ ,cs2.s1;
+ '''
+// qt_ds_shape_64 'explain shape plan ' + ds64
-
-// with cs_ui as
-// (select cs_item_sk
-// ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
-// from catalog_sales
-// ,catalog_returns
-// where cs_item_sk = cr_item_sk
-// and cs_order_number = cr_order_number
-// group by cs_item_sk
-// having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-// cross_sales as
-// (select i_product_name product_name
-// ,i_item_sk item_sk
-// ,s_store_name store_name
-// ,s_zip store_zip
-// ,ad1.ca_street_number b_street_number
-// ,ad1.ca_street_name b_street_name
-// ,ad1.ca_city b_city
-// ,ad1.ca_zip b_zip
-// ,ad2.ca_street_number c_street_number
-// ,ad2.ca_street_name c_street_name
-// ,ad2.ca_city c_city
-// ,ad2.ca_zip c_zip
-// ,d1.d_year as syear
-// ,d2.d_year as fsyear
-// ,d3.d_year s2year
-// ,count(*) cnt
-// ,sum(ss_wholesale_cost) s1
-// ,sum(ss_list_price) s2
-// ,sum(ss_coupon_amt) s3
-// FROM store_sales
-// ,store_returns
-// ,cs_ui
-// ,date_dim d1
-// ,date_dim d2
-// ,date_dim d3
-// ,store
-// ,customer
-// ,customer_demographics cd1
-// ,customer_demographics cd2
-// ,promotion
-// ,household_demographics hd1
-// ,household_demographics hd2
-// ,customer_address ad1
-// ,customer_address ad2
-// ,income_band ib1
-// ,income_band ib2
-// ,item
-// WHERE ss_store_sk = s_store_sk AND
-// ss_sold_date_sk = d1.d_date_sk AND
-// ss_customer_sk = c_customer_sk AND
-// ss_cdemo_sk= cd1.cd_demo_sk AND
-// ss_hdemo_sk = hd1.hd_demo_sk AND
-// ss_addr_sk = ad1.ca_address_sk and
-// ss_item_sk = i_item_sk and
-// ss_item_sk = sr_item_sk and
-// ss_ticket_number = sr_ticket_number and
-// ss_item_sk = cs_ui.cs_item_sk and
-// c_current_cdemo_sk = cd2.cd_demo_sk AND
-// c_current_hdemo_sk = hd2.hd_demo_sk AND
-// c_current_addr_sk = ad2.ca_address_sk and
-// c_first_sales_date_sk = d2.d_date_sk and
-// c_first_shipto_date_sk = d3.d_date_sk and
-// ss_promo_sk = p_promo_sk and
-// hd1.hd_income_band_sk = ib1.ib_income_band_sk and
-// hd2.hd_income_band_sk = ib2.ib_income_band_sk and
-// cd1.cd_marital_status <> cd2.cd_marital_status and
-// i_color in
('blanched','medium','brown','chocolate','burlywood','drab') and
-// i_current_price between 23 and 23 + 10 and
-// i_current_price between 23 + 1 and 23 + 15
-// group by i_product_name
-// ,i_item_sk
-// ,s_store_name
-// ,s_zip
-// ,ad1.ca_street_number
-// ,ad1.ca_street_name
-// ,ad1.ca_city
-// ,ad1.ca_zip
-// ,ad2.ca_street_number
-// ,ad2.ca_street_name
-// ,ad2.ca_city
-// ,ad2.ca_zip
-// ,d1.d_year
-// ,d2.d_year
-// ,d3.d_year
-// )
-// select cs1.product_name
-// ,cs1.store_name
-// ,cs1.store_zip
-// ,cs1.b_street_number
-// ,cs1.b_street_name
-// ,cs1.b_city
-// ,cs1.b_zip
-// ,cs1.c_street_number
-// ,cs1.c_street_name
-// ,cs1.c_city
-// ,cs1.c_zip
-// ,cs1.syear
-// ,cs1.cnt
-// ,cs1.s1 as s11
-// ,cs1.s2 as s21
-// ,cs1.s3 as s31
-// ,cs2.s1 as s12
-// ,cs2.s2 as s22
-// ,cs2.s3 as s32
-// ,cs2.syear
-// ,cs2.cnt
-// from cross_sales cs1,cross_sales cs2
-// where cs1.item_sk=cs2.item_sk and
-// cs1.syear = 2001 and
-// cs2.syear = 2001 + 1 and
-// cs2.cnt <= cs1.cnt and
-// cs1.store_name = cs2.store_name and
-// cs1.store_zip = cs2.store_zip
-// order by cs1.product_name
-// ,cs1.store_name
-// ,cs2.cnt
-// ,cs1.s1
-// ,cs2.s1;
-
-// '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query85.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query85.groovy
index 52a8f621f6..208ef6b658 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query85.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query85.groovy
@@ -30,93 +30,93 @@ suite("query85") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_85 '''
-// explain shape plan
+ qt_ds_shape_85 '''
+ explain shape plan
-// select substr(r_reason_desc,1,20)
-// ,avg(ws_quantity)
-// ,avg(wr_refunded_cash)
-// ,avg(wr_fee)
-// from web_sales, web_returns, web_page, customer_demographics cd1,
-// customer_demographics cd2, customer_address, date_dim, reason
-// where ws_web_page_sk = wp_web_page_sk
-// and ws_item_sk = wr_item_sk
-// and ws_order_number = wr_order_number
-// and ws_sold_date_sk = d_date_sk and d_year = 2000
-// and cd1.cd_demo_sk = wr_refunded_cdemo_sk
-// and cd2.cd_demo_sk = wr_returning_cdemo_sk
-// and ca_address_sk = wr_refunded_addr_sk
-// and r_reason_sk = wr_reason_sk
-// and
-// (
-// (
-// cd1.cd_marital_status = 'M'
-// and
-// cd1.cd_marital_status = cd2.cd_marital_status
-// and
-// cd1.cd_education_status = '4 yr Degree'
-// and
-// cd1.cd_education_status = cd2.cd_education_status
-// and
-// ws_sales_price between 100.00 and 150.00
-// )
-// or
-// (
-// cd1.cd_marital_status = 'S'
-// and
-// cd1.cd_marital_status = cd2.cd_marital_status
-// and
-// cd1.cd_education_status = 'Secondary'
-// and
-// cd1.cd_education_status = cd2.cd_education_status
-// and
-// ws_sales_price between 50.00 and 100.00
-// )
-// or
-// (
-// cd1.cd_marital_status = 'W'
-// and
-// cd1.cd_marital_status = cd2.cd_marital_status
-// and
-// cd1.cd_education_status = 'Advanced Degree'
-// and
-// cd1.cd_education_status = cd2.cd_education_status
-// and
-// ws_sales_price between 150.00 and 200.00
-// )
-// )
-// and
-// (
-// (
-// ca_country = 'United States'
-// and
-// ca_state in ('FL', 'TX', 'DE')
-// and ws_net_profit between 100 and 200
-// )
-// or
-// (
-// ca_country = 'United States'
-// and
-// ca_state in ('IN', 'ND', 'ID')
-// and ws_net_profit between 150 and 300
-// )
-// or
-// (
-// ca_country = 'United States'
-// and
-// ca_state in ('MT', 'IL', 'OH')
-// and ws_net_profit between 50 and 250
-// )
-// )
-// group by r_reason_desc
-// order by substr(r_reason_desc,1,20)
-// ,avg(ws_quantity)
-// ,avg(wr_refunded_cash)
-// ,avg(wr_fee)
-// limit 100;
+select substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+ from web_sales, web_returns, web_page, customer_demographics cd1,
+ customer_demographics cd2, customer_address, date_dim, reason
+ where ws_web_page_sk = wp_web_page_sk
+ and ws_item_sk = wr_item_sk
+ and ws_order_number = wr_order_number
+ and ws_sold_date_sk = d_date_sk and d_year = 2000
+ and cd1.cd_demo_sk = wr_refunded_cdemo_sk
+ and cd2.cd_demo_sk = wr_returning_cdemo_sk
+ and ca_address_sk = wr_refunded_addr_sk
+ and r_reason_sk = wr_reason_sk
+ and
+ (
+ (
+ cd1.cd_marital_status = 'M'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = '4 yr Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'S'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Secondary'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'W'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Advanced Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('FL', 'TX', 'DE')
+ and ws_net_profit between 100 and 200
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('IN', 'ND', 'ID')
+ and ws_net_profit between 150 and 300
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('MT', 'IL', 'OH')
+ and ws_net_profit between 50 and 250
+ )
+ )
+group by r_reason_desc
+order by substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+limit 100;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query88.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query88.groovy
index b4bb1ab763..917d5bbb71 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query88.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query88.groovy
@@ -30,103 +30,103 @@ suite("query88") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_88 '''
-// explain shape plan
+ qt_ds_shape_88 '''
+ explain shape plan
-// select *
-// from
-// (select count(*) h8_30_to_9
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 8
-// and time_dim.t_minute >= 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s1,
-// (select count(*) h9_to_9_30
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 9
-// and time_dim.t_minute < 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s2,
-// (select count(*) h9_30_to_10
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 9
-// and time_dim.t_minute >= 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s3,
-// (select count(*) h10_to_10_30
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 10
-// and time_dim.t_minute < 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s4,
-// (select count(*) h10_30_to_11
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 10
-// and time_dim.t_minute >= 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s5,
-// (select count(*) h11_to_11_30
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 11
-// and time_dim.t_minute < 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s6,
-// (select count(*) h11_30_to_12
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 11
-// and time_dim.t_minute >= 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s7,
-// (select count(*) h12_to_12_30
-// from store_sales, household_demographics , time_dim, store
-// where ss_sold_time_sk = time_dim.t_time_sk
-// and ss_hdemo_sk = household_demographics.hd_demo_sk
-// and ss_store_sk = s_store_sk
-// and time_dim.t_hour = 12
-// and time_dim.t_minute < 30
-// and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
-// (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
-// (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
-// and store.s_store_name = 'ese') s8
-// ;
+select *
+from
+ (select count(*) h8_30_to_9
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 8
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s1,
+ (select count(*) h9_to_9_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 9
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s2,
+ (select count(*) h9_30_to_10
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 9
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s3,
+ (select count(*) h10_to_10_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 10
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s4,
+ (select count(*) h10_30_to_11
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 10
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s5,
+ (select count(*) h11_to_11_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 11
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s6,
+ (select count(*) h11_30_to_12
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 11
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s7,
+ (select count(*) h12_to_12_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 12
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = -1 and
household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 4 and
household_demographics.hd_vehicle_count<=4+2) or
+ (household_demographics.hd_dep_count = 3 and
household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s8
+;
-// '''
+ '''
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query9.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query9.groovy
index 2b0eedaab9..761ff09d8c 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query9.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/shape/query9.groovy
@@ -30,60 +30,60 @@ suite("query9") {
sql 'set enable_nereids_timeout = false'
sql 'SET enable_pipeline_engine = true'
-// qt_ds_shape_9 '''
-// explain shape plan
+ qt_ds_shape_9 '''
+ explain shape plan
-// select case when (select count(*)
-// from store_sales
-// where ss_quantity between 1 and 20) > 2972190
-// then (select avg(ss_ext_sales_price)
-// from store_sales
-// where ss_quantity between 1 and 20)
-// else (select avg(ss_net_profit)
-// from store_sales
-// where ss_quantity between 1 and 20) end bucket1 ,
-// case when (select count(*)
-// from store_sales
-// where ss_quantity between 21 and 40) > 4505785
-// then (select avg(ss_ext_sales_price)
-// from store_sales
-// where ss_quantity between 21 and 40)
-// else (select avg(ss_net_profit)
-// from store_sales
-// where ss_quantity between 21 and 40) end bucket2,
-// case when (select count(*)
-// from store_sales
-// where ss_quantity between 41 and 60) > 1575726
-// then (select avg(ss_ext_sales_price)
-// from store_sales
-// where ss_quantity between 41 and 60)
-// else (select avg(ss_net_profit)
-// from store_sales
-// where ss_quantity between 41 and 60) end bucket3,
-// case when (select count(*)
-// from store_sales
-// where ss_quantity between 61 and 80) > 3188917
-// then (select avg(ss_ext_sales_price)
-// from store_sales
-// where ss_quantity between 61 and 80)
-// else (select avg(ss_net_profit)
-// from store_sales
-// where ss_quantity between 61 and 80) end bucket4,
-// case when (select count(*)
-// from store_sales
-// where ss_quantity between 81 and 100) > 3525216
-// then (select avg(ss_ext_sales_price)
-// from store_sales
-// where ss_quantity between 81 and 100)
-// else (select avg(ss_net_profit)
-// from store_sales
-// where ss_quantity between 81 and 100) end bucket5
-// from reason
-// where r_reason_sk = 1
-// ;
+select case when (select count(*)
+ from store_sales
+ where ss_quantity between 1 and 20) > 2972190
+ then (select avg(ss_ext_sales_price)
+ from store_sales
+ where ss_quantity between 1 and 20)
+ else (select avg(ss_net_profit)
+ from store_sales
+ where ss_quantity between 1 and 20) end bucket1 ,
+ case when (select count(*)
+ from store_sales
+ where ss_quantity between 21 and 40) > 4505785
+ then (select avg(ss_ext_sales_price)
+ from store_sales
+ where ss_quantity between 21 and 40)
+ else (select avg(ss_net_profit)
+ from store_sales
+ where ss_quantity between 21 and 40) end bucket2,
+ case when (select count(*)
+ from store_sales
+ where ss_quantity between 41 and 60) > 1575726
+ then (select avg(ss_ext_sales_price)
+ from store_sales
+ where ss_quantity between 41 and 60)
+ else (select avg(ss_net_profit)
+ from store_sales
+ where ss_quantity between 41 and 60) end bucket3,
+ case when (select count(*)
+ from store_sales
+ where ss_quantity between 61 and 80) > 3188917
+ then (select avg(ss_ext_sales_price)
+ from store_sales
+ where ss_quantity between 61 and 80)
+ else (select avg(ss_net_profit)
+ from store_sales
+ where ss_quantity between 61 and 80) end bucket4,
+ case when (select count(*)
+ from store_sales
+ where ss_quantity between 81 and 100) > 3525216
+ then (select avg(ss_ext_sales_price)
+ from store_sales
+ where ss_quantity between 81 and 100)
+ else (select avg(ss_net_profit)
+ from store_sales
+ where ss_quantity between 81 and 100) end bucket5
+from reason
+where r_reason_sk = 1
+;
-// '''
+ '''
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
