You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by en...@apache.org on 2024/09/22 15:00:48 UTC
(doris) branch branch-2.0 updated: ids) fix bug for A>n, where A.max is infinity #39936 (2.0) (#41094)
This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 52996aff519 ids) fix bug for A>n, where A.max is infinity #39936 (2.0) (#41094)
52996aff519 is described below
commit 52996aff5194b9558809c47999b0b7e9dcc9dff5
Author: minghong <en...@gmail.com>
AuthorDate: Sun Sep 22 23:00:39 2024 +0800
ids) fix bug for A>n, where A.max is infinity #39936 (2.0) (#41094)
## Proposed changes
pick #39936
Issue Number: close #xxx
<!--Describe your changes.-->
---
.../doris/nereids/stats/FilterEstimation.java | 8 +-
.../apache/doris/statistics/StatisticRange.java | 23 +++-
.../doris/nereids/stats/FilterEstimationTest.java | 36 ++++++
.../nereids_tpcds_shape_sf100_p0/shape/query1.out | 33 ++---
.../nereids_tpcds_shape_sf100_p0/shape/query14.out | 105 ++++++++--------
.../nereids_tpcds_shape_sf100_p0/shape/query15.out | 13 +-
.../nereids_tpcds_shape_sf100_p0/shape/query19.out | 43 ++++---
.../nereids_tpcds_shape_sf100_p0/shape/query21.out | 13 +-
.../nereids_tpcds_shape_sf100_p0/shape/query26.out | 12 +-
.../nereids_tpcds_shape_sf100_p0/shape/query3.out | 13 +-
.../nereids_tpcds_shape_sf100_p0/shape/query30.out | 5 +-
.../nereids_tpcds_shape_sf100_p0/shape/query33.out | 61 +++++-----
.../nereids_tpcds_shape_sf100_p0/shape/query4.out | 45 ++++---
.../nereids_tpcds_shape_sf100_p0/shape/query40.out | 21 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query41.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query42.out | 13 +-
.../nereids_tpcds_shape_sf100_p0/shape/query44.out | 57 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query46.out | 15 +--
.../nereids_tpcds_shape_sf100_p0/shape/query47.out | 17 +--
.../nereids_tpcds_shape_sf100_p0/shape/query5.out | 25 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query51.out | 77 ++++++------
.../nereids_tpcds_shape_sf100_p0/shape/query53.out | 28 ++---
.../nereids_tpcds_shape_sf100_p0/shape/query54.out | 57 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query56.out | 60 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query58.out | 107 ++++++++--------
.../nereids_tpcds_shape_sf100_p0/shape/query60.out | 102 ++++++++--------
.../nereids_tpcds_shape_sf100_p0/shape/query62.out | 19 +--
.../nereids_tpcds_shape_sf100_p0/shape/query63.out | 28 ++---
.../nereids_tpcds_shape_sf100_p0/shape/query66.out | 46 ++++---
.../nereids_tpcds_shape_sf100_p0/shape/query69.out | 5 +-
.../nereids_tpcds_shape_sf100_p0/shape/query7.out | 37 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query70.out | 36 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query72.out | 83 ++++++-------
.../nereids_tpcds_shape_sf100_p0/shape/query74.out | 27 +++--
.../nereids_tpcds_shape_sf100_p0/shape/query76.out | 39 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query77.out | 49 ++++----
.../nereids_tpcds_shape_sf100_p0/shape/query78.out | 60 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query80.out | 134 ++++++++++-----------
.../nereids_tpcds_shape_sf100_p0/shape/query83.out | 95 +++++++--------
.../nereids_tpcds_shape_sf100_p0/shape/query84.out | 15 ++-
.../nereids_tpcds_shape_sf100_p0/shape/query90.out | 77 ++++++------
.../nereids_tpcds_shape_sf100_p0/shape/query91.out | 51 ++++----
.../nereids_tpcds_shape_sf100_p0/shape/query92.out | 43 ++++---
.../nereids_tpcds_shape_sf100_p0/shape/query93.out | 13 +-
.../nereids_tpcds_shape_sf100_p0/shape/query94.out | 22 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query95.out | 32 ++---
.../nereids_tpcds_shape_sf100_p0/shape/query96.out | 41 +++----
.../nereids_tpcds_shape_sf100_p0/shape/query99.out | 19 +--
.../nereids_tpch_shape_sf1000_p0/shape/q15.out | 39 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q16.out | 13 +-
.../data/nereids_tpch_shape_sf1000_p0/shape/q2.out | 5 +-
.../shape/q20-rewrite.out | 13 +-
.../nereids_tpch_shape_sf1000_p0/shape/q20.out | 13 +-
.../nereids_tpch_shape_sf1000_p0/shape/q21.out | 33 ++---
.../nereids_tpch_shape_sf1000_p0/shape/q22.out | 23 ++--
.../data/nereids_tpch_shape_sf1000_p0/shape/q5.out | 5 +-
.../shape_no_stats/q15.out | 39 +++---
.../shape_no_stats/q16.out | 21 ++--
.../shape_no_stats/q22.out | 23 ++--
.../shape_no_stats/q5.out | 27 ++---
.../shape_no_stats/q8.out | 35 +++---
.../shape_no_stats/q9.out | 33 +++--
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf78.groovy | 2 +-
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf85.groovy | 2 +-
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf95.groovy | 2 +-
65 files changed, 1148 insertions(+), 1142 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
index faa9fd323d6..71a4b64c841 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
@@ -602,11 +602,13 @@ public class FilterEstimation extends ExpressionVisitor<Statistics, EstimationCo
.setMaxExpr(intersectRange.getHighExpr())
.setNdv(intersectRange.getDistinctValues())
.setNumNulls(0);
- double sel = leftRange.overlapPercentWith(rightRange);
+ double sel = leftRange.getDistinctValues() == 0
+ ? 1.0
+ : intersectRange.getDistinctValues() / leftRange.getDistinctValues();
if (!(dataType instanceof RangeScalable) && (sel != 0.0 && sel != 1.0)) {
sel = DEFAULT_INEQUALITY_COEFFICIENT;
- } else if (sel < RANGE_SELECTIVITY_THRESHOLD) {
- sel = RANGE_SELECTIVITY_THRESHOLD;
+ } else {
+ sel = Math.max(sel, RANGE_SELECTIVITY_THRESHOLD);
}
sel = getNotNullSelectivity(leftStats, sel);
updatedStatistics = context.statistics.withSel(sel);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticRange.java b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticRange.java
index 7b7b08ab246..2ca9b50eaee 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticRange.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticRange.java
@@ -124,6 +124,10 @@ public class StatisticRange {
return Double.isInfinite(low) || Double.isInfinite(high);
}
+ public boolean isOneSideInfinite() {
+ return isInfinite() && !isBothInfinite();
+ }
+
public boolean isFinite() {
return Double.isFinite(low) && Double.isFinite(high);
}
@@ -175,22 +179,29 @@ public class StatisticRange {
}
public StatisticRange cover(StatisticRange other) {
- // double newLow = Math.max(low, other.low);
- // double newHigh = Math.min(high, other.high);
+ StatisticRange resultRange;
Pair<Double, LiteralExpr> biggerLow = maxPair(low, lowExpr, other.low, other.lowExpr);
double newLow = biggerLow.first;
LiteralExpr newLowExpr = biggerLow.second;
Pair<Double, LiteralExpr> smallerHigh = minPair(high, highExpr, other.high, other.highExpr);
double newHigh = smallerHigh.first;
LiteralExpr newHighExpr = smallerHigh.second;
-
if (newLow <= newHigh) {
double overlapPercentOfLeft = overlapPercentWith(other);
double overlapDistinctValuesLeft = overlapPercentOfLeft * distinctValues;
double coveredDistinctValues = minExcludeNaN(distinctValues, overlapDistinctValuesLeft);
- return new StatisticRange(newLow, newLowExpr, newHigh, newHighExpr, coveredDistinctValues, dataType);
+ if (this.isBothInfinite() && other.isOneSideInfinite()) {
+ resultRange = new StatisticRange(newLow, newLowExpr, newHigh, newHighExpr,
+ distinctValues * INFINITE_TO_INFINITE_RANGE_INTERSECT_OVERLAP_HEURISTIC_FACTOR,
+ dataType);
+ } else {
+ resultRange = new StatisticRange(newLow, newLowExpr, newHigh, newHighExpr, coveredDistinctValues,
+ dataType);
+ }
+ } else {
+ resultRange = empty(dataType);
}
- return empty(dataType);
+ return resultRange;
}
public StatisticRange union(StatisticRange other) {
@@ -241,6 +252,6 @@ public class StatisticRange {
@Override
public String toString() {
- return "(" + lowExpr + "," + highExpr + ")";
+ return "range=(" + lowExpr + "," + highExpr + "), ndv=" + distinctValues;
}
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/stats/FilterEstimationTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/stats/FilterEstimationTest.java
index d0f6b3da077..a5410965b69 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/stats/FilterEstimationTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/stats/FilterEstimationTest.java
@@ -1292,4 +1292,40 @@ class FilterEstimationTest {
Statistics agrtc = new FilterEstimation().estimate(new GreaterThan(a, c), baseStats);
Assertions.assertEquals(50, agrtc.getRowCount());
}
+
+ @Test
+ void testAndWithInfinity() {
+ Double row = 1000.0;
+ SlotReference a = new SlotReference("a", new VarcharType(25));
+ ColumnStatisticBuilder columnStatisticBuilderA = new ColumnStatisticBuilder()
+ .setNdv(10)
+ .setAvgSizeByte(4)
+ .setNumNulls(0)
+ .setCount(row);
+
+ SlotReference b = new SlotReference("b", IntegerType.INSTANCE);
+ ColumnStatisticBuilder columnStatisticBuilderB = new ColumnStatisticBuilder()
+ .setNdv(488)
+ .setAvgSizeByte(25)
+ .setNumNulls(0)
+ .setCount(row);
+ StatisticsBuilder statsBuilder = new StatisticsBuilder();
+ statsBuilder.setRowCount(row);
+ statsBuilder.putColumnStatistics(a, columnStatisticBuilderA.build());
+ statsBuilder.putColumnStatistics(b, columnStatisticBuilderB.build());
+ Expression strGE = new GreaterThanEqual(a,
+ new org.apache.doris.nereids.trees.expressions.literal.StringLiteral("2024-05-14"));
+ Statistics strStats = new FilterEstimation().estimate(strGE, statsBuilder.build());
+ Assertions.assertEquals(500, strStats.getRowCount());
+
+ Expression intGE = new GreaterThan(b, new IntegerLiteral(0));
+ Statistics intStats = new FilterEstimation().estimate(intGE, statsBuilder.build());
+ Assertions.assertEquals(500, intStats.getRowCount());
+
+ Expression predicate = new And(strGE, intGE);
+
+ Statistics stats = new FilterEstimation().estimate(predicate, statsBuilder.build());
+ Assertions.assertEquals(250, stats.getRowCount());
+ }
+
}
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query1.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query1.out
index 700186ae78d..9b201bb9804 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query1.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query1.out
@@ -24,20 +24,21 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------PhysicalProject
------------------PhysicalOlapScan[customer]
--------------PhysicalDistribute
-----------------hashJoin[INNER_JOIN](ctr1.ctr_store_sk = ctr2.ctr_store_sk)(cast(ctr_total_return as DOUBLE) > cast((avg(cast(ctr_total_return as DECIMALV3(38, 4))) * 1.2) as DOUBLE))
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](store.s_store_sk = ctr1.ctr_store_sk)
-----------------------PhysicalDistribute
-------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((store.s_state = 'SD'))
-----------------------------PhysicalOlapScan[store]
-------------------PhysicalDistribute
---------------------hashAgg[GLOBAL]
-----------------------PhysicalDistribute
-------------------------hashAgg[LOCAL]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](ctr1.ctr_store_sk = ctr2.ctr_store_sk)(cast(ctr_total_return as DOUBLE) > cast((avg(cast(ctr_total_return as DECIMALV3(38, 4))) * 1.2) as DOUBLE))
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](store.s_store_sk = ctr1.ctr_store_sk)
+------------------------PhysicalDistribute
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((store.s_state = 'SD'))
+------------------------------PhysicalOlapScan[store]
+--------------------PhysicalDistribute
+----------------------hashAgg[GLOBAL]
+------------------------PhysicalDistribute
+--------------------------hashAgg[LOCAL]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
index 18a3538a32c..0496973cea7 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
@@ -95,78 +95,75 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalUnion
------------------------PhysicalProject
--------------------------NestedLoopJoin[INNER_JOIN](cast(sales as DOUBLE) > cast(average_sales as DOUBLE))
-----------------------------PhysicalProject
-------------------------------hashAgg[GLOBAL]
---------------------------------PhysicalDistribute
-----------------------------------hashAgg[LOCAL]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN](store_sales.ss_item_sk = cross_items.ss_item_sk)
+----------------------------hashAgg[GLOBAL]
+------------------------------PhysicalDistribute
+--------------------------------hashAgg[LOCAL]
+----------------------------------PhysicalProject
+------------------------------------hashJoin[RIGHT_SEMI_JOIN](store_sales.ss_item_sk = cross_items.ss_item_sk)
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
-------------------------------------------PhysicalDistribute
---------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_year = 2002)(date_dim.d_moy = 11))
-----------------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
+------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+----------------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_year = 2002)(date_dim.d_moy = 11))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalDistribute
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute
----------------------------------PhysicalCteConsumer ( cteId=CTEId#1 )
------------------------PhysicalProject
--------------------------NestedLoopJoin[INNER_JOIN](cast(sales as DOUBLE) > cast(average_sales as DOUBLE))
-----------------------------PhysicalProject
-------------------------------hashAgg[GLOBAL]
---------------------------------PhysicalDistribute
-----------------------------------hashAgg[LOCAL]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN](catalog_sales.cs_item_sk = cross_items.ss_item_sk)
+----------------------------hashAgg[GLOBAL]
+------------------------------PhysicalDistribute
+--------------------------------hashAgg[LOCAL]
+----------------------------------PhysicalProject
+------------------------------------hashJoin[RIGHT_SEMI_JOIN](catalog_sales.cs_item_sk = cross_items.ss_item_sk)
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
-------------------------------------------PhysicalDistribute
---------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_moy = 11)(date_dim.d_year = 2002))
-----------------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
+------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+----------------------------------------------PhysicalOlapScan[catalog_sales]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_moy = 11)(date_dim.d_year = 2002))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalDistribute
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute
----------------------------------PhysicalCteConsumer ( cteId=CTEId#1 )
------------------------PhysicalProject
--------------------------NestedLoopJoin[INNER_JOIN](cast(sales as DOUBLE) > cast(average_sales as DOUBLE))
-----------------------------PhysicalProject
-------------------------------hashAgg[GLOBAL]
---------------------------------PhysicalDistribute
-----------------------------------hashAgg[LOCAL]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN](web_sales.ws_item_sk = cross_items.ss_item_sk)
+----------------------------hashAgg[GLOBAL]
+------------------------------PhysicalDistribute
+--------------------------------hashAgg[LOCAL]
+----------------------------------PhysicalProject
+------------------------------------hashJoin[RIGHT_SEMI_JOIN](web_sales.ws_item_sk = cross_items.ss_item_sk)
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
-------------------------------------------PhysicalDistribute
---------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[web_sales]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_year = 2002)(date_dim.d_moy = 11))
-----------------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
+------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+----------------------------------------------PhysicalOlapScan[web_sales]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_year = 2002)(date_dim.d_moy = 11))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalDistribute
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query15.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query15.out
index abb2fe6156f..3746b5b247c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query15.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query15.out
@@ -10,13 +10,14 @@ PhysicalResultSink
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_customer_sk = customer.c_customer_sk)((substring(ca_zip, 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') OR ca_state IN ('CA', 'WA', 'GA')) OR (catalog_sales.cs_sales_price > 500.00))
------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[catalog_sales]
-----------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
------------------------PhysicalProject
---------------------------filter((date_dim.d_qoy = 1)(date_dim.d_year = 2001))
-----------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalOlapScan[catalog_sales]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_qoy = 1)(date_dim.d_year = 2001))
+------------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = customer_address.ca_address_sk)
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query19.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query19.out
index 60cc009a7b3..f01f7b5a8e5 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query19.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query19.out
@@ -10,31 +10,30 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)( not (substring(ca_zip, 1, 5) = substring(s_zip, 1, 5)))
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = customer_address.ca_address_sk)
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = customer_address.ca_address_sk)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[customer_address]
+------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_address]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk = customer.c_customer_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
---------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((item.i_manager_id = 2))
-----------------------------------------------PhysicalOlapScan[item]
+----------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk = customer.c_customer_sk)
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[customer]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[store_sales]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_moy = 12)(date_dim.d_year = 1999))
---------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------filter((item.i_manager_id = 2))
+--------------------------------------------PhysicalOlapScan[item]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((date_dim.d_moy = 12)(date_dim.d_year = 1999))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
index 7e04b2502b1..78149630a0e 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query21.out
@@ -12,12 +12,13 @@ PhysicalResultSink
------------------hashJoin[INNER_JOIN](inventory.inv_warehouse_sk = warehouse.w_warehouse_sk)
--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](inventory.inv_date_sk = date_dim.d_date_sk)
-------------------------hashJoin[INNER_JOIN](item.i_item_sk = inventory.inv_item_sk)
---------------------------PhysicalOlapScan[inventory]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------filter((item.i_current_price <= 1.49)(item.i_current_price >= 0.99))
---------------------------------PhysicalOlapScan[item]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](item.i_item_sk = inventory.inv_item_sk)
+----------------------------PhysicalOlapScan[inventory]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((item.i_current_price <= 1.49)(item.i_current_price >= 0.99))
+----------------------------------PhysicalOlapScan[item]
------------------------PhysicalDistribute
--------------------------PhysicalProject
----------------------------filter((date_dim.d_date >= '2002-01-28')(date_dim.d_date <= '2002-03-29'))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query26.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query26.out
index c827c6f8235..9fe9cb16a09 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query26.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query26.out
@@ -10,8 +10,8 @@ PhysicalResultSink
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](catalog_sales.cs_promo_sk = promotion.p_promo_sk)
-----------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](catalog_sales.cs_promo_sk = promotion.p_promo_sk)
------------------------PhysicalProject
--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
----------------------------PhysicalProject
@@ -26,10 +26,10 @@ PhysicalResultSink
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_year = 2001))
----------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter(((promotion.p_channel_email = 'N') OR (promotion.p_channel_event = 'N')))
-----------------------------PhysicalOlapScan[promotion]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter(((promotion.p_channel_email = 'N') OR (promotion.p_channel_event = 'N')))
+------------------------------PhysicalOlapScan[promotion]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query3.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query3.out
index fc17a5bab55..6516eca4168 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query3.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query3.out
@@ -10,14 +10,13 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](dt.d_date_sk = store_sales.ss_sold_date_sk)
---------------------PhysicalDistribute
-----------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+--------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[store_sales]
+----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------filter((item.i_manufact_id = 816))
-------------------------------PhysicalOlapScan[item]
+--------------------------filter((item.i_manufact_id = 816))
+----------------------------PhysicalOlapScan[item]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------filter((dt.d_moy = 11))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query30.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query30.out
index b3d184b6480..7ea8c8644fe 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query30.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query30.out
@@ -32,9 +32,8 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalDistribute
--------------------hashJoin[INNER_JOIN](customer_address.ca_address_sk = customer.c_current_addr_sk)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
----------------------PhysicalDistribute
------------------------PhysicalProject
--------------------------filter((customer_address.ca_state = 'IN'))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
index d479e8c20c6..cea4ec9ccc2 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
@@ -32,9 +32,8 @@ PhysicalResultSink
----------------------------------------PhysicalOlapScan[customer_address]
----------------------------PhysicalDistribute
------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_manufact_id = item.i_manufact_id)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((item.i_category = 'Home'))
@@ -63,39 +62,39 @@ PhysicalResultSink
----------------------------------------PhysicalOlapScan[customer_address]
----------------------------PhysicalDistribute
------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_manufact_id = item.i_manufact_id)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((item.i_category = 'Home'))
--------------------------------------PhysicalOlapScan[item]
----------------PhysicalProject
-------------------hashAgg[LOCAL]
---------------------PhysicalProject
-----------------------hashJoin[LEFT_SEMI_JOIN](item.i_manufact_id = item.i_manufact_id)
-------------------------PhysicalDistribute
---------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[item]
-----------------------------PhysicalDistribute
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[LEFT_SEMI_JOIN](item.i_manufact_id = item.i_manufact_id)
+----------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](web_sales.ws_bill_addr_sk = customer_address.ca_address_sk)
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[web_sales]
-----------------------------------------PhysicalDistribute
+--------------------------------PhysicalOlapScan[item]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_sales.ws_bill_addr_sk = customer_address.ca_address_sk)
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
------------------------------------------PhysicalProject
---------------------------------------------filter((date_dim.d_moy = 1)(date_dim.d_year = 2002))
-----------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------filter((customer_address.ca_gmt_offset = -5.00))
-----------------------------------------PhysicalOlapScan[customer_address]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------filter((item.i_category = 'Home'))
-------------------------------PhysicalOlapScan[item]
+--------------------------------------------PhysicalOlapScan[web_sales]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((date_dim.d_moy = 1)(date_dim.d_year = 2002))
+------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((customer_address.ca_gmt_offset = -5.00))
+------------------------------------------PhysicalOlapScan[customer_address]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((item.i_category = 'Home'))
+----------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
index 928ef78cd26..34f698aa52d 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
@@ -15,7 +15,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalOlapScan[store_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(d_year IN (2000, 1999))
+--------------------------filter(d_year IN (1999, 2000))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
@@ -32,7 +32,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalOlapScan[catalog_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(d_year IN (2000, 1999))
+--------------------------filter(d_year IN (1999, 2000))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
@@ -49,7 +49,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalOlapScan[web_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(d_year IN (2000, 1999))
+--------------------------filter(d_year IN (1999, 2000))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
@@ -61,31 +61,30 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------PhysicalProject
------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)(if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL) > if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL))
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_secyear.customer_id)(if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL) > if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL))
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
---------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_firstyear.customer_id)
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------filter((t_s_firstyear.year_total > 0.000000)(t_s_firstyear.dyear = 1999)(t_s_firstyear.sale_type = 's'))
-----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------filter((t_c_firstyear.year_total > 0.000000)(t_c_firstyear.dyear = 1999)(t_c_firstyear.sale_type = 'c'))
-----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_secyear.customer_id)(if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL) > if((year_total > 0.000000), (cast(year_total as DECIMALV3(38, 16)) / year_total), NULL))
+------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)
+------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_firstyear.customer_id)
--------------------------PhysicalDistribute
----------------------------PhysicalProject
-------------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2000))
+------------------------------filter((t_s_firstyear.year_total > 0.000000)(t_s_firstyear.dyear = 1999)(t_s_firstyear.sale_type = 's'))
--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((t_c_secyear.dyear = 2000)(t_c_secyear.sale_type = 'c'))
-----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------filter((t_c_firstyear.year_total > 0.000000)(t_c_firstyear.dyear = 1999)(t_c_firstyear.sale_type = 'c'))
+--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((t_w_firstyear.dyear = 1999)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.year_total > 0.000000))
+------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2000))
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.dyear = 1999)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.year_total > 0.000000))
+----------------------filter((t_c_secyear.dyear = 2000)(t_c_secyear.sale_type = 'c'))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------PhysicalDistribute
----------------PhysicalProject
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query40.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query40.out
index 665050611ca..e50ac4dd8f2 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query40.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query40.out
@@ -13,18 +13,19 @@ PhysicalResultSink
--------------------hashJoin[RIGHT_OUTER_JOIN](catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)(catalog_sales.cs_order_number = catalog_returns.cr_order_number)
----------------------PhysicalProject
------------------------PhysicalOlapScan[catalog_returns]
-----------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
-------------------------hashJoin[INNER_JOIN](item.i_item_sk = catalog_sales.cs_item_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+--------------------------hashJoin[INNER_JOIN](item.i_item_sk = catalog_sales.cs_item_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[catalog_sales]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((item.i_current_price >= 0.99)(item.i_current_price <= 1.49))
+----------------------------------PhysicalOlapScan[item]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
-------------------------------filter((item.i_current_price >= 0.99)(item.i_current_price <= 1.49))
---------------------------------PhysicalOlapScan[item]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------filter((date_dim.d_date >= '2001-03-03')(date_dim.d_date <= '2001-05-02'))
-------------------------------PhysicalOlapScan[date_dim]
+------------------------------filter((date_dim.d_date >= '2001-03-03')(date_dim.d_date <= '2001-05-02'))
+--------------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------PhysicalOlapScan[warehouse]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
index d60ffc84813..dd434fc7339 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
@@ -19,6 +19,6 @@ PhysicalResultSink
--------------------------PhysicalDistribute
----------------------------hashAgg[LOCAL]
------------------------------PhysicalProject
---------------------------------filter((((((((((((item.i_category = 'Women') AND i_color IN ('aquamarine', 'gainsboro')) AND i_units IN ('Dozen', 'Ounce')) AND i_size IN ('economy', 'medium')) OR ((((item.i_category = 'Women') AND i_color IN ('violet', 'chiffon')) AND i_units IN ('Pound', 'Ton')) AND i_size IN ('small', 'extra large'))) OR ((((item.i_category = 'Women') AND i_color IN ('tomato', 'blanched')) AND i_units IN ('Case', 'Tbl')) AND i_size IN ('economy', 'medium'))) OR ((((ite [...]
+--------------------------------filter((((((((((((item.i_category = 'Women') AND i_color IN ('gainsboro', 'aquamarine')) AND i_units IN ('Ounce', 'Dozen')) AND i_size IN ('medium', 'economy')) OR ((((item.i_category = 'Women') AND i_color IN ('chiffon', 'violet')) AND i_units IN ('Ton', 'Pound')) AND i_size IN ('extra large', 'small'))) OR ((((item.i_category = 'Women') AND i_color IN ('blanched', 'tomato')) AND i_units IN ('Tbl', 'Case')) AND i_size IN ('medium', 'economy'))) OR ((((ite [...]
----------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query42.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query42.out
index 298efbd8632..795319e23da 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query42.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query42.out
@@ -9,13 +9,14 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](dt.d_date_sk = store_sales.ss_sold_date_sk)
-------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
---------------------PhysicalProject
-----------------------PhysicalOlapScan[store_sales]
---------------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
----------------------PhysicalProject
-------------------------filter((item.i_manager_id = 1))
---------------------------PhysicalOlapScan[item]
+------------------------PhysicalOlapScan[store_sales]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter((item.i_manager_id = 1))
+----------------------------PhysicalOlapScan[item]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------filter((dt.d_year = 2002)(dt.d_moy = 11))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
index 48f68b04e2b..53a11c2dabd 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
@@ -11,37 +11,36 @@ PhysicalResultSink
------------PhysicalDistribute
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](asceding.rnk = descending.rnk)
-------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](i1.i_item_sk = asceding.item_sk)
+------------------hashJoin[INNER_JOIN](i1.i_item_sk = asceding.item_sk)
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[item]
+--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------PhysicalOlapScan[item]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((rnk < 11))
-----------------------------PhysicalWindow
-------------------------------PhysicalQuickSort
---------------------------------PhysicalDistribute
-----------------------------------PhysicalQuickSort
-------------------------------------PhysicalPartitionTopN
---------------------------------------PhysicalProject
-----------------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE))
-------------------------------------------PhysicalProject
---------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------PhysicalProject
-----------------------------------------------------filter((ss1.ss_store_sk = 146))
-------------------------------------------------------PhysicalOlapScan[store_sales]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalAssertNumRows
-----------------------------------------------PhysicalDistribute
+------------------------filter((rnk < 11))
+--------------------------PhysicalWindow
+----------------------------PhysicalQuickSort
+------------------------------PhysicalDistribute
+--------------------------------PhysicalQuickSort
+----------------------------------PhysicalPartitionTopN
+------------------------------------PhysicalProject
+--------------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE))
+----------------------------------------PhysicalProject
+------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------hashAgg[LOCAL]
------------------------------------------------PhysicalProject
---------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------PhysicalDistribute
-------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------filter(ss_addr_sk IS NULL(store_sales.ss_store_sk = 146))
-------------------------------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------------------filter((ss1.ss_store_sk = 146))
+----------------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalAssertNumRows
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------------PhysicalDistribute
+----------------------------------------------------hashAgg[LOCAL]
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------filter(ss_addr_sk IS NULL(store_sales.ss_store_sk = 146))
+----------------------------------------------------------PhysicalOlapScan[store_sales]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------filter((rnk < 11))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
index 8a6e45f9139..3565440b382 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
@@ -35,11 +35,12 @@ PhysicalResultSink
------------------------PhysicalProject
--------------------------PhysicalOlapScan[customer_address]
------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = current_addr.ca_address_sk)
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------PhysicalOlapScan[customer_address]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = current_addr.ca_address_sk)
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[customer]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[customer_address]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
index 96569a9c77b..bb00dd5c668 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query47.out
@@ -36,17 +36,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------PhysicalDistribute
----------PhysicalTopN
------------PhysicalProject
---------------hashJoin[INNER_JOIN](v1.i_category = v1_lag.i_category)(v1.i_brand = v1_lag.i_brand)(v1.s_store_name = v1_lag.s_store_name)(v1.s_company_name = v1_lag.s_company_name)(v1.rn = expr_(rn + 1))
+--------------hashJoin[INNER_JOIN](v1.i_category = v1_lead.i_category)(v1.i_brand = v1_lead.i_brand)(v1.s_store_name = v1_lead.s_store_name)(v1.s_company_name = v1_lead.s_company_name)(v1.rn = expr_(rn - 1))
----------------PhysicalDistribute
------------------PhysicalProject
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalDistribute
-------------------hashJoin[INNER_JOIN](v1.i_category = v1_lead.i_category)(v1.i_brand = v1_lead.i_brand)(v1.s_store_name = v1_lead.s_store_name)(v1.s_company_name = v1_lead.s_company_name)(v1.rn = expr_(rn - 1))
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------filter((if((avg_monthly_sales > 0.0000), (cast(abs((sum_sales - cast(avg_monthly_sales as DECIMALV3(38, 2)))) as DECIMALV3(38, 10)) / avg_monthly_sales), NULL) > 0.100000)(v2.d_year = 2001)(v2.avg_monthly_sales > 0.0000))
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](v1.i_category = v1_lag.i_category)(v1.i_brand = v1_lag.i_brand)(v1.s_store_name = v1_lag.s_store_name)(v1.s_company_name = v1_lag.s_company_name)(v1.rn = expr_(rn + 1))
+----------------------PhysicalDistribute
+------------------------PhysicalProject
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter((if((avg_monthly_sales > 0.0000), (cast(abs((sum_sales - cast(avg_monthly_sales as DECIMALV3(38, 2)))) as DECIMALV3(38, 10)) / avg_monthly_sales), NULL) > 0.100000)(v2.d_year = 2001)(v2.avg_monthly_sales > 0.0000))
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query5.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query5.out
index a2e8c26203b..e5764dbbd01 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query5.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query5.out
@@ -38,20 +38,19 @@ PhysicalResultSink
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN](salesreturns.page_sk = catalog_page.cp_catalog_page_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](salesreturns.date_sk = date_dim.d_date_sk)
---------------------------------------PhysicalUnion
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[catalog_returns]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](salesreturns.date_sk = date_dim.d_date_sk)
+------------------------------------PhysicalUnion
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[catalog_sales]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_date >= '2000-08-19')(date_dim.d_date <= '2000-09-02'))
---------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalOlapScan[catalog_returns]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((date_dim.d_date >= '2000-08-19')(date_dim.d_date <= '2000-09-02'))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[catalog_page]
@@ -76,7 +75,7 @@ PhysicalResultSink
----------------------------------------------PhysicalOlapScan[web_returns]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------filter((date_dim.d_date >= '2000-08-19')(date_dim.d_date <= '2000-09-02'))
+----------------------------------------filter((date_dim.d_date <= '2000-09-02')(date_dim.d_date >= '2000-08-19'))
------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
index b9cef62510c..e8564c2c922 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN
----PhysicalDistribute
------PhysicalTopN
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN](web.item_sk = store.item_sk)(web.d_date = store.d_date)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN](web.item_sk = store.item_sk)(web.d_date = store.d_date)
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales]
+------------------------------------------PhysicalDistribute
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales]
---------------------------------------------PhysicalDistribute
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227)(date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+----------------------------------------------filter((date_dim.d_month_seq <= 1227)(date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales]
+------------------------------------------PhysicalDistribute
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales]
---------------------------------------------PhysicalDistribute
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq >= 1216)(date_dim.d_month_seq <= 1227))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq >= 1216)(date_dim.d_month_seq <= 1227))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
index 87229687332..e7edc630a2f 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query53.out
@@ -15,22 +15,20 @@ PhysicalResultSink
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[store_sales]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalProject
-----------------------------------------------filter((((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help')) AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR ((i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants')) AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))))
-------------------------------------------------PhysicalOlapScan[item]
-------------------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
--------------------------------------PhysicalProject
-----------------------------------------filter(d_month_seq IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211))
-------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------filter((((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help')) AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR ((i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants')) AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))))
+--------------------------------------------PhysicalOlapScan[item]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter(d_month_seq IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211))
+----------------------------------------PhysicalOlapScan[date_dim]
------------------------------PhysicalDistribute
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query54.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query54.out
index 04d6c5a8f7c..5180c2191e0 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query54.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query54.out
@@ -26,39 +26,38 @@ PhysicalResultSink
------------------------------------------PhysicalDistribute
--------------------------------------------PhysicalProject
----------------------------------------------hashJoin[INNER_JOIN](customer_address.ca_county = store.s_county)(customer_address.ca_state = store.s_state)
-------------------------------------------------PhysicalDistribute
---------------------------------------------------hashJoin[INNER_JOIN](my_customers.c_current_addr_sk = customer_address.ca_address_sk)
+------------------------------------------------hashJoin[INNER_JOIN](my_customers.c_current_addr_sk = customer_address.ca_address_sk)
+--------------------------------------------------PhysicalProject
+----------------------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------------------------PhysicalDistribute
----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[customer_address]
-----------------------------------------------------PhysicalDistribute
-------------------------------------------------------PhysicalProject
---------------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------hashJoin[INNER_JOIN](customer.c_customer_sk = cs_or_ws_sales.customer_sk)
+------------------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------------------PhysicalDistribute
+----------------------------------------------------------hashAgg[LOCAL]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------hashJoin[INNER_JOIN](customer.c_customer_sk = cs_or_ws_sales.customer_sk)
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
+----------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------PhysicalOlapScan[customer]
-------------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------hashJoin[INNER_JOIN](cs_or_ws_sales.sold_date_sk = date_dim.d_date_sk)
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------hashJoin[INNER_JOIN](cs_or_ws_sales.item_sk = item.i_item_sk)
-----------------------------------------------------------------------------PhysicalUnion
-------------------------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
-------------------------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------PhysicalOlapScan[web_sales]
+--------------------------------------------------------------------hashJoin[INNER_JOIN](cs_or_ws_sales.sold_date_sk = date_dim.d_date_sk)
+----------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------hashJoin[INNER_JOIN](cs_or_ws_sales.item_sk = item.i_item_sk)
+--------------------------------------------------------------------------PhysicalUnion
----------------------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------filter((item.i_class = 'maternity')(item.i_category = 'Women'))
-----------------------------------------------------------------------------------PhysicalOlapScan[item]
-------------------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------filter((date_dim.d_year = 1998)(date_dim.d_moy = 5))
-------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
+----------------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------PhysicalOlapScan[web_sales]
+--------------------------------------------------------------------------PhysicalDistribute
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------filter((item.i_class = 'maternity')(item.i_category = 'Women'))
+--------------------------------------------------------------------------------PhysicalOlapScan[item]
+----------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------filter((date_dim.d_year = 1998)(date_dim.d_moy = 5))
+----------------------------------------------------------------------------PhysicalOlapScan[date_dim]
------------------------------------------------PhysicalDistribute
--------------------------------------------------PhysicalProject
----------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query56.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query56.out
index 8903c671616..330145cdc50 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query56.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query56.out
@@ -16,20 +16,18 @@ PhysicalResultSink
--------------------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk = customer_address.ca_address_sk)
----------------------------PhysicalDistribute
------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[store_sales]
+------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store_sales]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
---------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[item]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[item]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter(i_color IN ('powder', 'green', 'cyan'))
@@ -49,20 +47,18 @@ PhysicalResultSink
--------------------------------PhysicalOlapScan[customer_address]
----------------------------PhysicalDistribute
------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[catalog_sales]
+------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[catalog_sales]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
---------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[item]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[item]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter(i_color IN ('powder', 'green', 'cyan'))
@@ -78,20 +74,18 @@ PhysicalResultSink
--------------------------------PhysicalOlapScan[customer_address]
----------------------------PhysicalDistribute
------------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[web_sales]
+------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_sales]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
---------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 2))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[item]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[item]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter(i_color IN ('powder', 'green', 'cyan'))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query58.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query58.out
index 4456679c6fc..40019178d3f 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query58.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query58.out
@@ -34,61 +34,62 @@ PhysicalResultSink
----------------------------------------------PhysicalProject
------------------------------------------------filter((date_dim.d_date = '2001-03-24'))
--------------------------------------------------PhysicalOlapScan[date_dim]
-------------hashJoin[INNER_JOIN](ss_items.item_id = ws_items.item_id)(cast(ws_item_rev as DOUBLE) >= cast((0.9 * ss_item_rev) as DOUBLE))(cast(ss_item_rev as DOUBLE) >= cast((0.9 * ws_item_rev) as DOUBLE))(cast(ws_item_rev as DOUBLE) <= cast((1.1 * ss_item_rev) as DOUBLE))(cast(ss_item_rev as DOUBLE) <= cast((1.1 * ws_item_rev) as DOUBLE))
---------------PhysicalProject
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item]
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales]
-------------------------------PhysicalDistribute
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](ss_items.item_id = ws_items.item_id)(cast(ws_item_rev as DOUBLE) >= cast((0.9 * ss_item_rev) as DOUBLE))(cast(ss_item_rev as DOUBLE) >= cast((0.9 * ws_item_rev) as DOUBLE))(cast(ws_item_rev as DOUBLE) <= cast((1.1 * ss_item_rev) as DOUBLE))(cast(ss_item_rev as DOUBLE) <= cast((1.1 * ws_item_rev) as DOUBLE))
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[item]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------PhysicalDistribute
+----------------------------------PhysicalOlapScan[store_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
--------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalAssertNumRows
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_date = '2001-03-24'))
-----------------------------------------------------PhysicalOlapScan[date_dim]
---------------PhysicalProject
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item]
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_sales]
-------------------------------PhysicalDistribute
+----------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalAssertNumRows
+------------------------------------------------PhysicalDistribute
+--------------------------------------------------PhysicalProject
+----------------------------------------------------filter((date_dim.d_date = '2001-03-24'))
+------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[item]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------PhysicalDistribute
+----------------------------------PhysicalOlapScan[web_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
--------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalAssertNumRows
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_date = '2001-03-24'))
-----------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalAssertNumRows
+------------------------------------------------PhysicalDistribute
+--------------------------------------------------PhysicalProject
+----------------------------------------------------filter((date_dim.d_date = '2001-03-24'))
+------------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query60.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query60.out
index bd043d536a7..225b470a916 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query60.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query60.out
@@ -14,27 +14,25 @@ PhysicalResultSink
----------------------hashAgg[LOCAL]
------------------------PhysicalProject
--------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk = customer_address.ca_address_sk)
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](store_sales.ss_addr_sk = customer_address.ca_address_sk)
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 8))
-----------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
-----------------------------------------PhysicalOlapScan[customer_address]
-----------------------------PhysicalDistribute
-------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 8))
+--------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
+------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
+--------------------------------------PhysicalOlapScan[customer_address]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((item.i_category = 'Children'))
@@ -45,27 +43,25 @@ PhysicalResultSink
----------------------hashAgg[LOCAL]
------------------------PhysicalProject
--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_addr_sk = customer_address.ca_address_sk)
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_addr_sk = customer_address.ca_address_sk)
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[catalog_sales]
+--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((date_dim.d_moy = 8)(date_dim.d_year = 2000))
-----------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
-----------------------------------------PhysicalOlapScan[customer_address]
-----------------------------PhysicalDistribute
-------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+------------------------------------------filter((date_dim.d_moy = 8)(date_dim.d_year = 2000))
+--------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
+------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
+--------------------------------------PhysicalOlapScan[customer_address]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((item.i_category = 'Children'))
@@ -76,27 +72,25 @@ PhysicalResultSink
----------------------hashAgg[LOCAL]
------------------------PhysicalProject
--------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](web_sales.ws_bill_addr_sk = customer_address.ca_address_sk)
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](web_sales.ws_bill_addr_sk = customer_address.ca_address_sk)
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[web_sales]
+--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[web_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 8))
-----------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
-----------------------------------------PhysicalOlapScan[customer_address]
-----------------------------PhysicalDistribute
-------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+------------------------------------------filter((date_dim.d_year = 2000)(date_dim.d_moy = 8))
+--------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
+------------------------------------filter((customer_address.ca_gmt_offset = -7.00))
+--------------------------------------PhysicalOlapScan[customer_address]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[LEFT_SEMI_JOIN](item.i_item_id = item.i_item_id)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((item.i_category = 'Children'))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query62.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query62.out
index ec78b77b89e..3cd4ec1cbac 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query62.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query62.out
@@ -11,17 +11,18 @@ PhysicalResultSink
----------------hashJoin[INNER_JOIN](web_sales.ws_warehouse_sk = warehouse.w_warehouse_sk)
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk)
-----------------------hashJoin[INNER_JOIN](web_sales.ws_web_site_sk = web_site.web_site_sk)
-------------------------hashJoin[INNER_JOIN](web_sales.ws_ship_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[web_sales]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](web_sales.ws_web_site_sk = web_site.web_site_sk)
+--------------------------hashJoin[INNER_JOIN](web_sales.ws_ship_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[web_sales]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq >= 1194)(date_dim.d_month_seq <= 1205))
+----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq >= 1194)(date_dim.d_month_seq <= 1205))
---------------------------------PhysicalOlapScan[date_dim]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[web_site]
+------------------------------PhysicalOlapScan[web_site]
----------------------PhysicalDistribute
------------------------PhysicalProject
--------------------------PhysicalOlapScan[ship_mode]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
index 1b979039047..0c75b1ba2cf 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query63.out
@@ -15,22 +15,20 @@ PhysicalResultSink
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[store_sales]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalProject
-----------------------------------------------filter((((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help')) AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR ((i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants')) AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))))
-------------------------------------------------PhysicalOlapScan[item]
-------------------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
--------------------------------------PhysicalProject
-----------------------------------------filter(d_month_seq IN (1181, 1182, 1183, 1184, 1185, 1186, 1187, 1188, 1189, 1190, 1191, 1192))
-------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------filter((((i_category IN ('Books', 'Children', 'Electronics') AND i_class IN ('personal', 'portable', 'reference', 'self-help')) AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) OR ((i_category IN ('Women', 'Music', 'Men') AND i_class IN ('accessories', 'classical', 'fragrances', 'pants')) AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))))
+--------------------------------------------PhysicalOlapScan[item]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter(d_month_seq IN (1181, 1182, 1183, 1184, 1185, 1186, 1187, 1188, 1189, 1190, 1191, 1192))
+----------------------------------------PhysicalOlapScan[date_dim]
------------------------------PhysicalDistribute
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query66.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query66.out
index 5d8a503ccae..978389da3b3 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query66.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query66.out
@@ -16,20 +16,19 @@ PhysicalResultSink
--------------------------hashJoin[INNER_JOIN](web_sales.ws_warehouse_sk = warehouse.w_warehouse_sk)
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_time_sk = time_dim.t_time_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
---------------------------------------hashJoin[INNER_JOIN](web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk)
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[web_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter(sm_carrier IN ('GREAT EASTERN', 'LATVIAN'))
-----------------------------------------------PhysicalOlapScan[ship_mode]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+------------------------------------hashJoin[INNER_JOIN](web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[web_sales]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_year = 1998))
---------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------filter(sm_carrier IN ('GREAT EASTERN', 'LATVIAN'))
+--------------------------------------------PhysicalOlapScan[ship_mode]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((date_dim.d_year = 1998))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((cast(t_time as BIGINT) <= 77621)(time_dim.t_time >= 48821))
@@ -45,20 +44,19 @@ PhysicalResultSink
--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk)
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_time_sk = time_dim.t_time_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
---------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_mode_sk = ship_mode.sm_ship_mode_sk)
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter(sm_carrier IN ('GREAT EASTERN', 'LATVIAN'))
-----------------------------------------------PhysicalOlapScan[ship_mode]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_mode_sk = ship_mode.sm_ship_mode_sk)
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[catalog_sales]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_year = 1998))
---------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------filter(sm_carrier IN ('GREAT EASTERN', 'LATVIAN'))
+--------------------------------------------PhysicalOlapScan[ship_mode]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((date_dim.d_year = 1998))
+------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((time_dim.t_time >= 48821)(cast(t_time as BIGINT) <= 77621))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
index 96ae616ceb1..27f92562177 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
@@ -48,9 +48,8 @@ PhysicalResultSink
--------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[INNER_JOIN](c.c_current_addr_sk = ca.ca_address_sk)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[customer]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter(ca_state IN ('TX', 'VA', 'MI'))
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query7.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query7.out
index 58908af1bc5..7f889521bb8 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query7.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query7.out
@@ -10,25 +10,26 @@ PhysicalResultSink
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk = promotion.p_promo_sk)
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((customer_demographics.cd_gender = 'F')(customer_demographics.cd_marital_status = 'W')(customer_demographics.cd_education_status = 'College'))
-------------------------------------PhysicalOlapScan[customer_demographics]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_year = 2001))
---------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk = promotion.p_promo_sk)
------------------------PhysicalProject
---------------------------filter(((promotion.p_channel_email = 'N') OR (promotion.p_channel_event = 'N')))
-----------------------------PhysicalOlapScan[promotion]
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[store_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------filter((customer_demographics.cd_gender = 'F')(customer_demographics.cd_marital_status = 'W')(customer_demographics.cd_education_status = 'College'))
+--------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_year = 2001))
+----------------------------------PhysicalOlapScan[date_dim]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter(((promotion.p_channel_email = 'N') OR (promotion.p_channel_event = 'N')))
+------------------------------PhysicalOlapScan[promotion]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
index 2771503c464..318e3f111b2 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
@@ -26,30 +26,28 @@ PhysicalResultSink
------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[LEFT_SEMI_JOIN](store.s_state = tmp1.s_state)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[store]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter((ranking <= 5))
------------------------------------------PhysicalWindow
--------------------------------------------PhysicalQuickSort
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
---------------------------------------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq >= 1213)(date_dim.d_month_seq <= 1224))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------PhysicalPartitionTopN
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------PhysicalDistribute
+------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
+------------------------------------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store_sales]
--------------------------------------------------------------PhysicalDistribute
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------------------------------filter((date_dim.d_month_seq >= 1213)(date_dim.d_month_seq <= 1224))
+--------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
index 01fd2572b9e..b7b91d09a74 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
@@ -15,52 +15,49 @@ PhysicalResultSink
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------hashJoin[LEFT_OUTER_JOIN](catalog_sales.cs_promo_sk = promotion.p_promo_sk)
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](item.i_item_sk = catalog_sales.cs_item_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](item.i_item_sk = catalog_sales.cs_item_sk)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[item]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = inventory.inv_item_sk)(inventory.inv_date_sk = d2.d_date_sk)(inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalOlapScan[inventory]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN](d1.d_week_seq = d2.d_week_seq)
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk = d3.d_date_sk)(d3.d_date > cast((cast(d_date as BIGINT) + 5) as DATEV2))
-----------------------------------------------------PhysicalDistribute
-------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk)
---------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = d1.d_date_sk)
-------------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk)
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------PhysicalOlapScan[catalog_sales]
---------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------filter((household_demographics.hd_buy_potential = '501-1000'))
---------------------------------------------------------------------PhysicalOlapScan[household_demographics]
-------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------filter((d1.d_year = 2002))
-------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = inventory.inv_item_sk)(inventory.inv_date_sk = d2.d_date_sk)(inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalOlapScan[inventory]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN](d1.d_week_seq = d2.d_week_seq)
+--------------------------------------------PhysicalProject
+----------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk = d3.d_date_sk)(d3.d_date > cast((cast(d_date as BIGINT) + 5) as DATEV2))
+------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk)
+--------------------------------------------------PhysicalDistribute
+----------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = d1.d_date_sk)
+------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk)
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[catalog_sales]
--------------------------------------------------------PhysicalDistribute
----------------------------------------------------------PhysicalProject
-------------------------------------------------------------filter((customer_demographics.cd_marital_status = 'W'))
---------------------------------------------------------------PhysicalOlapScan[customer_demographics]
-----------------------------------------------------PhysicalDistribute
-------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[warehouse]
+------------------------------------------------------------filter((household_demographics.hd_buy_potential = '501-1000'))
+--------------------------------------------------------------PhysicalOlapScan[household_demographics]
+------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------filter((d1.d_year = 2002))
+------------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------------PhysicalDistribute
+----------------------------------------------------PhysicalProject
+------------------------------------------------------filter((customer_demographics.cd_marital_status = 'W'))
+--------------------------------------------------------PhysicalOlapScan[customer_demographics]
+------------------------------------------------PhysicalDistribute
+--------------------------------------------------PhysicalProject
+----------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[warehouse]
------------------------PhysicalDistribute
--------------------------PhysicalProject
----------------------------PhysicalOlapScan[promotion]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
index d6477a3695c..73305f15762 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
@@ -15,7 +15,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalOlapScan[store_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(d_year IN (2000, 1999))
+--------------------------filter(d_year IN (1999, 2000))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
@@ -32,7 +32,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalOlapScan[web_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(d_year IN (2000, 1999))
+--------------------------filter(d_year IN (1999, 2000))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
@@ -42,23 +42,24 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute
--------PhysicalTopN
----------PhysicalProject
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)(if((year_total > 0), (year_total / year_total), NULL) > if((year_total > 0), (year_total / year_total), NULL))
---------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)
+------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)(if((year_total > 0), (year_total / year_total), NULL) > if((year_total > 0), (year_total / year_total), NULL))
+--------------PhysicalProject
----------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------filter((t_s_firstyear.year = 1999)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0))
-------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_s_firstyear.year = 1999)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0))
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_w_firstyear.year = 1999)(t_w_firstyear.year_total > 0)(t_w_firstyear.sale_type = 'w'))
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.year = 2000))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((t_w_secyear.year = 2000)(t_w_secyear.sale_type = 'w'))
-----------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------PhysicalDistribute
----------------PhysicalProject
-------------------filter((t_w_firstyear.year = 1999)(t_w_firstyear.year_total > 0)(t_w_firstyear.sale_type = 'w'))
+------------------filter((t_w_secyear.year = 2000)(t_w_secyear.sale_type = 'w'))
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query76.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query76.out
index e66c5338ae8..f3499519a81 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query76.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query76.out
@@ -14,37 +14,40 @@ PhysicalResultSink
----------------------PhysicalProject
------------------------PhysicalOlapScan[item]
----------------------PhysicalDistribute
-------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
----------------------------PhysicalProject
-------------------------------filter(ss_hdemo_sk IS NULL)
---------------------------------PhysicalOlapScan[store_sales]
+------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter(ss_hdemo_sk IS NULL)
+----------------------------------PhysicalOlapScan[store_sales]
----------------PhysicalDistribute
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
----------------------PhysicalProject
------------------------PhysicalOlapScan[item]
----------------------PhysicalDistribute
-------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
----------------------------PhysicalProject
-------------------------------filter(ws_bill_addr_sk IS NULL)
---------------------------------PhysicalOlapScan[web_sales]
+------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter(ws_bill_addr_sk IS NULL)
+----------------------------------PhysicalOlapScan[web_sales]
----------------PhysicalDistribute
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
----------------------PhysicalProject
------------------------PhysicalOlapScan[item]
----------------------PhysicalDistribute
-------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
----------------------------PhysicalProject
-------------------------------filter(cs_warehouse_sk IS NULL)
---------------------------------PhysicalOlapScan[catalog_sales]
+------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter(cs_warehouse_sk IS NULL)
+----------------------------------PhysicalOlapScan[catalog_sales]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query77.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query77.out
index f1df37846ba..170e60a5b89 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query77.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query77.out
@@ -58,7 +58,7 @@ PhysicalResultSink
--------------------------------------PhysicalOlapScan[catalog_sales]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------filter((date_dim.d_date >= '1998-08-05')(date_dim.d_date <= '1998-09-04'))
+----------------------------------------filter((date_dim.d_date <= '1998-09-04')(date_dim.d_date >= '1998-08-05'))
------------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalDistribute
--------------------------PhysicalProject
@@ -76,27 +76,28 @@ PhysicalResultSink
--------------------PhysicalProject
----------------------hashJoin[LEFT_OUTER_JOIN](ws.wp_web_page_sk = wr.wp_web_page_sk)
------------------------PhysicalProject
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_sales]
---------------------------------------PhysicalDistribute
+--------------------------hashAgg[GLOBAL]
+----------------------------PhysicalDistribute
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_date >= '1998-08-05')(date_dim.d_date <= '1998-09-04'))
---------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[web_page]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](web_returns.wr_web_page_sk = web_page.wp_web_page_sk)
-----------------------------------PhysicalDistribute
+------------------------------------------PhysicalOlapScan[web_sales]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------filter((date_dim.d_date >= '1998-08-05')(date_dim.d_date <= '1998-09-04'))
+----------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[web_page]
+------------------------PhysicalProject
+--------------------------hashAgg[GLOBAL]
+----------------------------PhysicalDistribute
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_returns.wr_web_page_sk = web_page.wp_web_page_sk)
------------------------------------PhysicalProject
--------------------------------------hashJoin[INNER_JOIN](web_returns.wr_returned_date_sk = date_dim.d_date_sk)
----------------------------------------PhysicalProject
@@ -105,7 +106,7 @@ PhysicalResultSink
------------------------------------------PhysicalProject
--------------------------------------------filter((date_dim.d_date >= '1998-08-05')(date_dim.d_date <= '1998-09-04'))
----------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[web_page]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[web_page]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query78.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query78.out
index 3cd8d0185ab..1eeff0d0be8 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query78.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query78.out
@@ -14,50 +14,44 @@ PhysicalResultSink
----------------------PhysicalDistribute
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------filter((date_dim.d_year = 2000))
-----------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalDistribute
+----------------------------hashJoin[LEFT_ANTI_JOIN](store_sales.ss_item_sk = store_returns.sr_item_sk)(store_returns.sr_ticket_number = store_sales.ss_ticket_number)
+------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------filter(sr_ticket_number IS NULL)
-------------------------------------hashJoin[LEFT_OUTER_JOIN](store_sales.ss_item_sk = store_returns.sr_item_sk)(store_returns.sr_ticket_number = store_sales.ss_ticket_number)
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store_sales]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store_returns]
+----------------------------------PhysicalOlapScan[store_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------filter((date_dim.d_year = 2000))
+--------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_returns]
------------------PhysicalProject
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------filter((date_dim.d_year = 2000))
-----------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalDistribute
+----------------------------hashJoin[LEFT_ANTI_JOIN](web_sales.ws_item_sk = web_returns.wr_item_sk)(web_returns.wr_order_number = web_sales.ws_order_number)
+------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------filter(wr_order_number IS NULL)
-------------------------------------hashJoin[LEFT_OUTER_JOIN](web_sales.ws_item_sk = web_returns.wr_item_sk)(web_returns.wr_order_number = web_sales.ws_order_number)
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_sales]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_returns]
+----------------------------------PhysicalOlapScan[web_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------filter((date_dim.d_year = 2000))
+--------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_returns]
--------------PhysicalProject
----------------hashAgg[GLOBAL]
------------------PhysicalDistribute
--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------filter((date_dim.d_year = 2000))
-------------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
+------------------------hashJoin[LEFT_ANTI_JOIN](catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)(catalog_returns.cr_order_number = catalog_sales.cs_order_number)
+--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
----------------------------PhysicalProject
-------------------------------filter(cr_order_number IS NULL)
---------------------------------hashJoin[LEFT_OUTER_JOIN](catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)(catalog_returns.cr_order_number = catalog_sales.cs_order_number)
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_returns]
+------------------------------PhysicalOlapScan[catalog_sales]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_year = 2000))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_returns]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query80.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query80.out
index a7d48e8011a..7b4e8ce3197 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query80.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query80.out
@@ -16,66 +16,59 @@ PhysicalResultSink
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
------------------------------hashJoin[RIGHT_OUTER_JOIN](store_sales.ss_item_sk = store_returns.sr_item_sk)(store_sales.ss_ticket_number = store_returns.sr_ticket_number)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[store_returns]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
---------------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk = promotion.p_promo_sk)
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
---------------------------------------------PhysicalDistribute
-----------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[store_sales]
-------------------------------------------------PhysicalDistribute
---------------------------------------------------PhysicalProject
-----------------------------------------------------filter((date_dim.d_date >= '1998-08-28')(date_dim.d_date <= '1998-09-27'))
-------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[store_returns]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk = promotion.p_promo_sk)
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_item_sk = item.i_item_sk)
+------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[store_sales]
--------------------------------------------PhysicalDistribute
----------------------------------------------PhysicalProject
-------------------------------------------------filter((item.i_current_price > 50.00))
---------------------------------------------------PhysicalOlapScan[item]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((promotion.p_channel_tv = 'N'))
-----------------------------------------------PhysicalOlapScan[promotion]
+------------------------------------------------filter((date_dim.d_date >= '1998-08-28')(date_dim.d_date <= '1998-09-27'))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((item.i_current_price > 50.00))
+------------------------------------------------PhysicalOlapScan[item]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[store]
+------------------------------------------filter((promotion.p_channel_tv = 'N'))
+--------------------------------------------PhysicalOlapScan[promotion]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[store]
--------------------PhysicalProject
----------------------hashAgg[GLOBAL]
------------------------PhysicalDistribute
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_catalog_page_sk = catalog_page.cp_catalog_page_sk)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[RIGHT_OUTER_JOIN](catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)(catalog_sales.cs_order_number = catalog_returns.cr_order_number)
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[catalog_returns]
---------------------------------------PhysicalDistribute
-----------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_promo_sk = promotion.p_promo_sk)
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
-----------------------------------------------PhysicalDistribute
-------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[catalog_sales]
---------------------------------------------------PhysicalDistribute
-----------------------------------------------------PhysicalProject
-------------------------------------------------------filter((date_dim.d_date >= '1998-08-28')(date_dim.d_date <= '1998-09-27'))
---------------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((item.i_current_price > 50.00))
-----------------------------------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[RIGHT_OUTER_JOIN](catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)(catalog_sales.cs_order_number = catalog_returns.cr_order_number)
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[catalog_returns]
+------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_promo_sk = promotion.p_promo_sk)
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk = item.i_item_sk)
+------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[catalog_sales]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_date <= '1998-09-27')(date_dim.d_date >= '1998-08-28'))
+--------------------------------------------------PhysicalOlapScan[date_dim]
------------------------------------------PhysicalDistribute
--------------------------------------------PhysicalProject
-----------------------------------------------filter((promotion.p_channel_tv = 'N'))
-------------------------------------------------PhysicalOlapScan[promotion]
+----------------------------------------------filter((item.i_current_price > 50.00))
+------------------------------------------------PhysicalOlapScan[item]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------filter((promotion.p_channel_tv = 'N'))
+--------------------------------------------PhysicalOlapScan[promotion]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[catalog_page]
@@ -85,32 +78,29 @@ PhysicalResultSink
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
------------------------------hashJoin[RIGHT_OUTER_JOIN](web_sales.ws_item_sk = web_returns.wr_item_sk)(web_sales.ws_order_number = web_returns.wr_order_number)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[web_returns]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](web_sales.ws_web_site_sk = web_site.web_site_sk)
---------------------------------------hashJoin[INNER_JOIN](web_sales.ws_promo_sk = promotion.p_promo_sk)
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
---------------------------------------------PhysicalDistribute
-----------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[web_sales]
-------------------------------------------------PhysicalDistribute
---------------------------------------------------PhysicalProject
-----------------------------------------------------filter((date_dim.d_date >= '1998-08-28')(date_dim.d_date <= '1998-09-27'))
-------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[web_returns]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](web_sales.ws_web_site_sk = web_site.web_site_sk)
+------------------------------------hashJoin[INNER_JOIN](web_sales.ws_promo_sk = promotion.p_promo_sk)
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](web_sales.ws_item_sk = item.i_item_sk)
+------------------------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk = date_dim.d_date_sk)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[web_sales]
--------------------------------------------PhysicalDistribute
----------------------------------------------PhysicalProject
-------------------------------------------------filter((item.i_current_price > 50.00))
---------------------------------------------------PhysicalOlapScan[item]
-----------------------------------------PhysicalDistribute
-------------------------------------------PhysicalProject
---------------------------------------------filter((promotion.p_channel_tv = 'N'))
-----------------------------------------------PhysicalOlapScan[promotion]
+------------------------------------------------filter((date_dim.d_date >= '1998-08-28')(date_dim.d_date <= '1998-09-27'))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((item.i_current_price > 50.00))
+------------------------------------------------PhysicalOlapScan[item]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[web_site]
+------------------------------------------filter((promotion.p_channel_tv = 'N'))
+--------------------------------------------PhysicalOlapScan[promotion]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[web_site]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query83.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query83.out
index 6a6c1a787b5..5ba79b76108 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query83.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query83.out
@@ -32,57 +32,58 @@ PhysicalResultSink
------------------------------------------PhysicalProject
--------------------------------------------filter(d_date IN ('2001-06-06', '2001-09-02', '2001-11-11'))
----------------------------------------------PhysicalOlapScan[date_dim]
-------------hashJoin[INNER_JOIN](sr_items.item_id = wr_items.item_id)
---------------PhysicalProject
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk = item.i_item_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item]
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_returns]
-------------------------------PhysicalDistribute
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](sr_items.item_id = wr_items.item_id)
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](store_returns.sr_item_sk = item.i_item_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[item]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[INNER_JOIN](store_returns.sr_returned_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------PhysicalDistribute
+----------------------------------PhysicalOlapScan[store_returns]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
--------------------------------------PhysicalProject
-----------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
+----------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
--------------------------------------------PhysicalProject
-----------------------------------------------filter(d_date IN ('2001-06-06', '2001-09-02', '2001-11-11'))
-------------------------------------------------PhysicalOlapScan[date_dim]
---------------PhysicalProject
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](web_returns.wr_item_sk = item.i_item_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item]
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](web_returns.wr_returned_date_sk = date_dim.d_date_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_returns]
-------------------------------PhysicalDistribute
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter(d_date IN ('2001-06-06', '2001-09-02', '2001-11-11'))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](web_returns.wr_item_sk = item.i_item_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[item]
+----------------------------PhysicalDistribute
+------------------------------hashJoin[INNER_JOIN](web_returns.wr_returned_date_sk = date_dim.d_date_sk)
--------------------------------PhysicalProject
-----------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------PhysicalDistribute
+----------------------------------PhysicalOlapScan[web_returns]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_date = date_dim.d_date)
--------------------------------------PhysicalProject
-----------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[date_dim]
-------------------------------------------PhysicalDistribute
+----------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[LEFT_SEMI_JOIN](date_dim.d_week_seq = date_dim.d_week_seq)
--------------------------------------------PhysicalProject
-----------------------------------------------filter(d_date IN ('2001-06-06', '2001-09-02', '2001-11-11'))
-------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter(d_date IN ('2001-06-06', '2001-09-02', '2001-11-11'))
+--------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query84.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query84.out
index 3d5febe35de..710652c2f1b 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query84.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query84.out
@@ -16,15 +16,14 @@ PhysicalResultSink
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](household_demographics.hd_demo_sk = customer.c_current_hdemo_sk)
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = customer_address.ca_address_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](customer.c_current_addr_sk = customer_address.ca_address_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[customer]
+----------------------------PhysicalDistribute
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((customer_address.ca_city = 'Oakwood'))
-------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------filter((customer_address.ca_city = 'Oakwood'))
+----------------------------------PhysicalOlapScan[customer_address]
------------------------PhysicalDistribute
--------------------------hashJoin[INNER_JOIN](income_band.ib_income_band_sk = household_demographics.hd_income_band_sk)
----------------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query90.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query90.out
index 327ff7b7af0..5a8742f29e6 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query90.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query90.out
@@ -2,56 +2,51 @@
-- !ds_shape_90 --
PhysicalResultSink
--PhysicalTopN
-----PhysicalTopN
-------PhysicalProject
---------NestedLoopJoin[CROSS_JOIN]
+----PhysicalProject
+------NestedLoopJoin[CROSS_JOIN]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](web_sales.ws_ship_hdemo_sk = household_demographics.hd_demo_sk)
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](web_sales.ws_sold_time_sk = time_dim.t_time_sk)
+----------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[web_sales]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((web_page.wp_char_count >= 5000)(web_page.wp_char_count <= 5200))
+------------------------------PhysicalOlapScan[web_page]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter((time_dim.t_hour >= 10)(time_dim.t_hour <= 11))
+----------------------------PhysicalOlapScan[time_dim]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((household_demographics.hd_dep_count = 2))
+------------------------PhysicalOlapScan[household_demographics]
+--------PhysicalDistribute
----------hashAgg[GLOBAL]
------------PhysicalDistribute
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](web_sales.ws_ship_hdemo_sk = household_demographics.hd_demo_sk)
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_time_sk = time_dim.t_time_sk)
---------------------------PhysicalDistribute
-----------------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_sales]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((web_page.wp_char_count >= 5000)(web_page.wp_char_count <= 5200))
-------------------------------------PhysicalOlapScan[web_page]
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](web_sales.ws_sold_time_sk = time_dim.t_time_sk)
+------------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
-------------------------------filter((time_dim.t_hour >= 10)(time_dim.t_hour <= 11))
---------------------------------PhysicalOlapScan[time_dim]
+------------------------------filter((web_page.wp_char_count >= 5000)(web_page.wp_char_count <= 5200))
+--------------------------------PhysicalOlapScan[web_page]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((time_dim.t_hour <= 17)(time_dim.t_hour >= 16))
+------------------------------PhysicalOlapScan[time_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------filter((household_demographics.hd_dep_count = 2))
--------------------------PhysicalOlapScan[household_demographics]
-----------PhysicalDistribute
-------------hashAgg[GLOBAL]
---------------PhysicalDistribute
-----------------hashAgg[LOCAL]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](web_sales.ws_ship_hdemo_sk = household_demographics.hd_demo_sk)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](web_sales.ws_sold_time_sk = time_dim.t_time_sk)
-----------------------------PhysicalDistribute
-------------------------------hashJoin[INNER_JOIN](web_sales.ws_web_page_sk = web_page.wp_web_page_sk)
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------filter((web_page.wp_char_count >= 5000)(web_page.wp_char_count <= 5200))
---------------------------------------PhysicalOlapScan[web_page]
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------filter((time_dim.t_hour <= 17)(time_dim.t_hour >= 16))
-----------------------------------PhysicalOlapScan[time_dim]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((household_demographics.hd_dep_count = 2))
-----------------------------PhysicalOlapScan[household_demographics]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query91.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query91.out
index ba4a23e3f0b..06812568dc9 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query91.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query91.out
@@ -10,37 +10,36 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](catalog_returns.cr_call_center_sk = call_center.cc_call_center_sk)
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](catalog_returns.cr_returned_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](catalog_returns.cr_returning_customer_sk = customer.c_customer_sk)
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](catalog_returns.cr_returned_date_sk = date_dim.d_date_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](catalog_returns.cr_returning_customer_sk = customer.c_customer_sk)
+----------------------------PhysicalDistribute
------------------------------PhysicalProject
--------------------------------PhysicalOlapScan[catalog_returns]
-------------------------------PhysicalDistribute
---------------------------------hashJoin[INNER_JOIN](customer_address.ca_address_sk = customer.c_current_addr_sk)
-----------------------------------PhysicalProject
-------------------------------------filter((customer_address.ca_gmt_offset = -6.00))
---------------------------------------PhysicalOlapScan[customer_address]
-----------------------------------PhysicalDistribute
-------------------------------------hashJoin[INNER_JOIN](household_demographics.hd_demo_sk = customer.c_current_hdemo_sk)
+----------------------------PhysicalDistribute
+------------------------------hashJoin[INNER_JOIN](customer_address.ca_address_sk = customer.c_current_addr_sk)
+--------------------------------PhysicalProject
+----------------------------------filter((customer_address.ca_gmt_offset = -6.00))
+------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------PhysicalDistribute
+----------------------------------hashJoin[INNER_JOIN](household_demographics.hd_demo_sk = customer.c_current_hdemo_sk)
+------------------------------------hashJoin[INNER_JOIN](customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk)
--------------------------------------PhysicalDistribute
-----------------------------------------hashJoin[INNER_JOIN](customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk)
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[customer]
-------------------------------------------PhysicalDistribute
---------------------------------------------PhysicalProject
-----------------------------------------------filter((((customer_demographics.cd_marital_status = 'M') AND (customer_demographics.cd_education_status = 'Unknown')) OR ((customer_demographics.cd_marital_status = 'W') AND (customer_demographics.cd_education_status = 'Advanced Degree'))))
-------------------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[customer]
--------------------------------------PhysicalDistribute
----------------------------------------PhysicalProject
-------------------------------------------filter((hd_buy_potential like '1001-5000%'))
---------------------------------------------PhysicalOlapScan[household_demographics]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_moy = 11)(date_dim.d_year = 2001))
---------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------filter((((customer_demographics.cd_marital_status = 'M') AND (customer_demographics.cd_education_status = 'Unknown')) OR ((customer_demographics.cd_marital_status = 'W') AND (customer_demographics.cd_education_status = 'Advanced Degree'))))
+--------------------------------------------PhysicalOlapScan[customer_demographics]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((hd_buy_potential like '1001-5000%'))
+------------------------------------------PhysicalOlapScan[household_demographics]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_moy = 11)(date_dim.d_year = 2001))
+------------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------PhysicalOlapScan[call_center]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query92.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query92.out
index 295f54863da..c5bb64a7623 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query92.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query92.out
@@ -2,27 +2,26 @@
-- !ds_shape_92 --
PhysicalResultSink
--PhysicalTopN
-----PhysicalTopN
-------hashAgg[GLOBAL]
---------PhysicalDistribute
-----------hashAgg[LOCAL]
-------------PhysicalProject
---------------filter((cast(ws_ext_discount_amt as DECIMALV3(38, 5)) > (1.3 * avg(cast(ws_ext_discount_amt as DECIMALV3(9, 4))) OVER(PARTITION BY i_item_sk))))
-----------------PhysicalWindow
-------------------PhysicalQuickSort
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = web_sales.ws_sold_date_sk)
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](item.i_item_sk = web_sales.ws_item_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_sales]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((item.i_manufact_id = 320))
-------------------------------------PhysicalOlapScan[item]
---------------------------PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------filter((cast(ws_ext_discount_amt as DECIMALV3(38, 5)) > (1.3 * avg(cast(ws_ext_discount_amt as DECIMALV3(9, 4))) OVER(PARTITION BY i_item_sk))))
+--------------PhysicalWindow
+----------------PhysicalQuickSort
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = web_sales.ws_sold_date_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](item.i_item_sk = web_sales.ws_item_sk)
----------------------------PhysicalProject
-------------------------------filter((date_dim.d_date <= '2002-05-27')(date_dim.d_date >= '2002-02-26'))
---------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalOlapScan[web_sales]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((item.i_manufact_id = 320))
+----------------------------------PhysicalOlapScan[item]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_date <= '2002-05-27')(date_dim.d_date >= '2002-02-26'))
+------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query93.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query93.out
index cc6eba640d6..98559a81403 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query93.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query93.out
@@ -11,11 +11,12 @@ PhysicalResultSink
----------------hashJoin[INNER_JOIN](store_returns.sr_item_sk = store_sales.ss_item_sk)(store_returns.sr_ticket_number = store_sales.ss_ticket_number)
------------------PhysicalProject
--------------------PhysicalOlapScan[store_sales]
-------------------hashJoin[INNER_JOIN](store_returns.sr_reason_sk = reason.r_reason_sk)
---------------------PhysicalProject
-----------------------PhysicalOlapScan[store_returns]
---------------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](store_returns.sr_reason_sk = reason.r_reason_sk)
----------------------PhysicalProject
-------------------------filter((reason.r_reason_desc = 'duplicate purchase'))
---------------------------PhysicalOlapScan[reason]
+------------------------PhysicalOlapScan[store_returns]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter((reason.r_reason_desc = 'duplicate purchase'))
+----------------------------PhysicalOlapScan[reason]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
index 8d508bacd9b..b805eb188ad 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
@@ -2,17 +2,17 @@
-- !ds_shape_94 --
PhysicalResultSink
--PhysicalTopN
-----PhysicalTopN
-------hashAgg[DISTINCT_GLOBAL]
---------PhysicalDistribute
-----------hashAgg[DISTINCT_LOCAL]
-------------hashAgg[GLOBAL]
---------------hashAgg[LOCAL]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN](ws1.ws_order_number = ws2.ws_order_number)( not (ws_warehouse_sk = ws_warehouse_sk))
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[web_sales]
+----hashAgg[DISTINCT_GLOBAL]
+------PhysicalDistribute
+--------hashAgg[DISTINCT_LOCAL]
+----------hashAgg[GLOBAL]
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[RIGHT_SEMI_JOIN](ws1.ws_order_number = ws2.ws_order_number)( not (ws_warehouse_sk = ws_warehouse_sk))
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[web_sales]
+------------------PhysicalProject
--------------------hashJoin[RIGHT_ANTI_JOIN](ws1.ws_order_number = wr1.wr_order_number)
----------------------PhysicalDistribute
------------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query95.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query95.out
index b3a19d18c18..605d94861d2 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query95.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query95.out
@@ -12,22 +12,22 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------PhysicalOlapScan[web_sales]
--PhysicalResultSink
----PhysicalTopN
-------PhysicalTopN
---------hashAgg[DISTINCT_GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[DISTINCT_LOCAL]
---------------hashAgg[GLOBAL]
-----------------hashAgg[LOCAL]
-------------------PhysicalProject
---------------------hashJoin[RIGHT_SEMI_JOIN](ws1.ws_order_number = web_returns.wr_order_number)
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](web_returns.wr_order_number = ws_wh.ws_order_number)
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_returns]
+------hashAgg[DISTINCT_GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[DISTINCT_LOCAL]
+------------hashAgg[GLOBAL]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN](ws1.ws_order_number = web_returns.wr_order_number)
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](web_returns.wr_order_number = ws_wh.ws_order_number)
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_returns]
+--------------------PhysicalProject
----------------------hashJoin[RIGHT_SEMI_JOIN](ws1.ws_order_number = ws_wh.ws_order_number)
------------------------PhysicalDistribute
--------------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query96.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query96.out
index 6d7df70b717..34e7fc5538e 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query96.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query96.out
@@ -2,27 +2,26 @@
-- !ds_shape_96 --
PhysicalResultSink
--PhysicalTopN
-----PhysicalTopN
-------hashAgg[GLOBAL]
---------PhysicalDistribute
-----------hashAgg[LOCAL]
-------------PhysicalProject
---------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN](store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)
---------------------hashJoin[INNER_JOIN](store_sales.ss_sold_time_sk = time_dim.t_time_sk)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((time_dim.t_minute >= 30)(time_dim.t_hour = 8))
-----------------------------PhysicalOlapScan[time_dim]
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN](store_sales.ss_store_sk = store.s_store_sk)
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)
+------------------hashJoin[INNER_JOIN](store_sales.ss_sold_time_sk = time_dim.t_time_sk)
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[store_sales]
--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------filter((household_demographics.hd_dep_count = 3))
---------------------------PhysicalOlapScan[household_demographics]
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((store.s_store_name = 'ese'))
-----------------------PhysicalOlapScan[store]
+------------------------filter((time_dim.t_minute >= 30)(time_dim.t_hour = 8))
+--------------------------PhysicalOlapScan[time_dim]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((household_demographics.hd_dep_count = 3))
+------------------------PhysicalOlapScan[household_demographics]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------filter((store.s_store_name = 'ese'))
+--------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
index 4f20f7bf82a..2874c226f78 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
@@ -11,17 +11,18 @@ PhysicalResultSink
----------------hashJoin[INNER_JOIN](catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk)
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_mode_sk = ship_mode.sm_ship_mode_sk)
-----------------------hashJoin[INNER_JOIN](catalog_sales.cs_call_center_sk = call_center.cc_call_center_sk)
-------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk = date_dim.d_date_sk)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](catalog_sales.cs_call_center_sk = call_center.cc_call_center_sk)
+--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk = date_dim.d_date_sk)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[catalog_sales]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq >= 1224)(date_dim.d_month_seq <= 1235))
+----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq >= 1224)(date_dim.d_month_seq <= 1235))
---------------------------------PhysicalOlapScan[date_dim]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[call_center]
+------------------------------PhysicalOlapScan[call_center]
----------------------PhysicalDistribute
------------------------PhysicalProject
--------------------------PhysicalOlapScan[ship_mode]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q15.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q15.out
index ca1de573ae6..878b6c708fb 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q15.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q15.out
@@ -9,23 +9,24 @@ PhysicalResultSink
------------PhysicalProject
--------------PhysicalOlapScan[supplier]
------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
-----------------PhysicalProject
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute
-----------------------hashAgg[LOCAL]
-------------------------PhysicalProject
---------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
-----------------------------PhysicalOlapScan[lineitem]
-----------------PhysicalDistribute
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute
-----------------------hashAgg[LOCAL]
-------------------------PhysicalProject
---------------------------hashAgg[GLOBAL]
-----------------------------PhysicalDistribute
-------------------------------hashAgg[LOCAL]
---------------------------------PhysicalProject
-----------------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
-------------------------------------PhysicalOlapScan[lineitem]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
+------------------PhysicalProject
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
+------------------------------PhysicalOlapScan[lineitem]
+------------------PhysicalDistribute
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------hashAgg[GLOBAL]
+------------------------------PhysicalDistribute
+--------------------------------hashAgg[LOCAL]
+----------------------------------PhysicalProject
+------------------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
+--------------------------------------PhysicalOlapScan[lineitem]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
index e2b58de0eb7..728ebb37a8e 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
@@ -10,12 +10,13 @@ PhysicalResultSink
--------------PhysicalProject
----------------hashJoin[LEFT_ANTI_JOIN](partsupp.ps_suppkey = supplier.s_suppkey)
------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[partsupp]
-----------------------PhysicalProject
-------------------------filter(( not (p_type like 'MEDIUM POLISHED%'))( not (p_brand = 'Brand#45'))p_size IN (3, 9, 14, 19, 23, 36, 45, 49))
---------------------------PhysicalOlapScan[part]
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[partsupp]
+------------------------PhysicalProject
+--------------------------filter(( not (p_brand = 'Brand#45'))( not (p_type like 'MEDIUM POLISHED%'))p_size IN (3, 9, 14, 19, 23, 36, 45, 49))
+----------------------------PhysicalOlapScan[part]
------------------PhysicalDistribute
--------------------PhysicalProject
----------------------filter((s_comment like '%Customer%Complaints%'))
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
index 4c8fc44b7c2..cb45679eac7 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
@@ -25,9 +25,8 @@ PhysicalResultSink
----------------------------PhysicalOlapScan[supplier]
----------------------------PhysicalDistribute
------------------------------hashJoin[INNER_JOIN](nation.n_regionkey = region.r_regionkey)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[nation]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[nation]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------filter((region.r_name = 'EUROPE'))
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20-rewrite.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20-rewrite.out
index 5a7a1020814..528a9f78ed9 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20-rewrite.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20-rewrite.out
@@ -24,11 +24,12 @@ PhysicalResultSink
------------------------filter((p_name like 'forest%'))
--------------------------PhysicalOlapScan[part]
------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
-----------------PhysicalProject
-------------------PhysicalOlapScan[supplier]
-----------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------PhysicalProject
---------------------filter((nation.n_name = 'CANADA'))
-----------------------PhysicalOlapScan[nation]
+--------------------PhysicalOlapScan[supplier]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((nation.n_name = 'CANADA'))
+------------------------PhysicalOlapScan[nation]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20.out
index 8cb5b171bc7..6b1e9f37b4b 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q20.out
@@ -23,11 +23,12 @@ PhysicalResultSink
------------------------filter((p_name like 'forest%'))
--------------------------PhysicalOlapScan[part]
------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
-----------------PhysicalProject
-------------------PhysicalOlapScan[supplier]
-----------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------PhysicalProject
---------------------filter((nation.n_name = 'CANADA'))
-----------------------PhysicalOlapScan[nation]
+--------------------PhysicalOlapScan[supplier]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((nation.n_name = 'CANADA'))
+------------------------PhysicalOlapScan[nation]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q21.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q21.out
index 2c0a3b2a637..312a23d2bef 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q21.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q21.out
@@ -11,24 +11,25 @@ PhysicalResultSink
----------------hashJoin[RIGHT_SEMI_JOIN](l2.l_orderkey = l1.l_orderkey)( not (l_suppkey = l_suppkey))
------------------PhysicalProject
--------------------PhysicalOlapScan[lineitem]
-------------------hashJoin[INNER_JOIN](orders.o_orderkey = l1.l_orderkey)
---------------------PhysicalProject
-----------------------filter((orders.o_orderstatus = 'F'))
-------------------------PhysicalOlapScan[orders]
---------------------hashJoin[RIGHT_ANTI_JOIN](l3.l_orderkey = l1.l_orderkey)( not (l_suppkey = l_suppkey))
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](orders.o_orderkey = l1.l_orderkey)
----------------------PhysicalProject
-------------------------filter((l3.l_receiptdate > l3.l_commitdate))
---------------------------PhysicalOlapScan[lineitem]
-----------------------hashJoin[INNER_JOIN](supplier.s_suppkey = l1.l_suppkey)
+------------------------filter((orders.o_orderstatus = 'F'))
+--------------------------PhysicalOlapScan[orders]
+----------------------hashJoin[RIGHT_ANTI_JOIN](l3.l_orderkey = l1.l_orderkey)( not (l_suppkey = l_suppkey))
------------------------PhysicalProject
---------------------------filter((l1.l_receiptdate > l1.l_commitdate))
+--------------------------filter((l3.l_receiptdate > l3.l_commitdate))
----------------------------PhysicalOlapScan[lineitem]
-------------------------PhysicalDistribute
---------------------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[supplier]
-----------------------------PhysicalDistribute
+------------------------hashJoin[INNER_JOIN](supplier.s_suppkey = l1.l_suppkey)
+--------------------------PhysicalProject
+----------------------------filter((l1.l_receiptdate > l1.l_commitdate))
+------------------------------PhysicalOlapScan[lineitem]
+--------------------------PhysicalDistribute
+----------------------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------------------PhysicalProject
---------------------------------filter((nation.n_name = 'SAUDI ARABIA'))
-----------------------------------PhysicalOlapScan[nation]
+--------------------------------PhysicalOlapScan[supplier]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((nation.n_name = 'SAUDI ARABIA'))
+------------------------------------PhysicalOlapScan[nation]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q22.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q22.out
index 7845eba2baf..dc8f7970dcd 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q22.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q22.out
@@ -13,15 +13,16 @@ PhysicalResultSink
--------------------PhysicalProject
----------------------PhysicalOlapScan[orders]
------------------PhysicalDistribute
---------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4)) > avg(cast(c_acctbal as DECIMALV3(17, 4))))
-----------------------PhysicalProject
-------------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
---------------------------PhysicalOlapScan[customer]
-----------------------PhysicalDistribute
-------------------------hashAgg[GLOBAL]
---------------------------PhysicalDistribute
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------filter((customer.c_acctbal > 0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
-----------------------------------PhysicalOlapScan[customer]
+--------------------PhysicalProject
+----------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4)) > avg(cast(c_acctbal as DECIMALV3(17, 4))))
+------------------------PhysicalProject
+--------------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
+----------------------------PhysicalOlapScan[customer]
+------------------------PhysicalDistribute
+--------------------------hashAgg[GLOBAL]
+----------------------------PhysicalDistribute
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------filter((customer.c_acctbal > 0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
+------------------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q5.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q5.out
index 57e1aad20cd..02ade98d230 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q5.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q5.out
@@ -22,9 +22,8 @@ PhysicalResultSink
----------------------------------PhysicalOlapScan[supplier]
--------------------------------PhysicalDistribute
----------------------------------hashJoin[INNER_JOIN](nation.n_regionkey = region.r_regionkey)
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[nation]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[nation]
------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
----------------------------------------filter((region.r_name = 'ASIA'))
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q15.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q15.out
index 6d60f8b8052..48482b276f3 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q15.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q15.out
@@ -6,25 +6,26 @@ PhysicalResultSink
------PhysicalQuickSort
--------PhysicalProject
----------hashJoin[INNER_JOIN](supplier.s_suppkey = revenue0.supplier_no)
-------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
---------------PhysicalProject
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
---------------------------PhysicalOlapScan[lineitem]
---------------PhysicalDistribute
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashAgg[GLOBAL]
---------------------------PhysicalDistribute
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
-----------------------------------PhysicalOlapScan[lineitem]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
+----------------------------PhysicalOlapScan[lineitem]
+----------------PhysicalDistribute
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashAgg[GLOBAL]
+----------------------------PhysicalDistribute
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------filter((lineitem.l_shipdate >= '1996-01-01')(lineitem.l_shipdate < '1996-04-01'))
+------------------------------------PhysicalOlapScan[lineitem]
------------PhysicalDistribute
--------------PhysicalProject
----------------PhysicalOlapScan[supplier]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q16.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q16.out
index 6eabacb321d..1eb948e315b 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q16.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q16.out
@@ -9,17 +9,14 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
-------------------PhysicalDistribute
---------------------hashJoin[LEFT_ANTI_JOIN](partsupp.ps_suppkey = supplier.s_suppkey)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[partsupp]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((s_comment like '%Customer%Complaints%'))
-----------------------------PhysicalOlapScan[supplier]
-------------------PhysicalDistribute
+------------------hashJoin[LEFT_ANTI_JOIN](partsupp.ps_suppkey = supplier.s_suppkey)
--------------------PhysicalProject
-----------------------filter(( not (p_type like 'MEDIUM POLISHED%'))( not (p_brand = 'Brand#45'))p_size IN (3, 9, 14, 19, 23, 36, 45, 49))
-------------------------PhysicalOlapScan[part]
+----------------------PhysicalOlapScan[partsupp]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((s_comment like '%Customer%Complaints%'))
+--------------------------PhysicalOlapScan[supplier]
+------------------PhysicalProject
+--------------------filter(( not (p_brand = 'Brand#45'))( not (p_type like 'MEDIUM POLISHED%'))p_size IN (3, 9, 14, 19, 23, 36, 45, 49))
+----------------------PhysicalOlapScan[part]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q22.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q22.out
index 7845eba2baf..dc8f7970dcd 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q22.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q22.out
@@ -13,15 +13,16 @@ PhysicalResultSink
--------------------PhysicalProject
----------------------PhysicalOlapScan[orders]
------------------PhysicalDistribute
---------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4)) > avg(cast(c_acctbal as DECIMALV3(17, 4))))
-----------------------PhysicalProject
-------------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
---------------------------PhysicalOlapScan[customer]
-----------------------PhysicalDistribute
-------------------------hashAgg[GLOBAL]
---------------------------PhysicalDistribute
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------filter((customer.c_acctbal > 0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
-----------------------------------PhysicalOlapScan[customer]
+--------------------PhysicalProject
+----------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4)) > avg(cast(c_acctbal as DECIMALV3(17, 4))))
+------------------------PhysicalProject
+--------------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
+----------------------------PhysicalOlapScan[customer]
+------------------------PhysicalDistribute
+--------------------------hashAgg[GLOBAL]
+----------------------------PhysicalDistribute
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------filter((customer.c_acctbal > 0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
+------------------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q5.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q5.out
index 9fc242ea918..a2071db0cd1 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q5.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q5.out
@@ -13,21 +13,20 @@ PhysicalResultSink
--------------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
----------------------PhysicalProject
------------------------hashJoin[INNER_JOIN](customer.c_nationkey = supplier.s_nationkey)(lineitem.l_suppkey = supplier.s_suppkey)
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[lineitem]
+------------------------------PhysicalDistribute
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[lineitem]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((orders.o_orderdate < '1995-01-01')(orders.o_orderdate >= '1994-01-01'))
---------------------------------------------PhysicalOlapScan[orders]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[customer]
+----------------------------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((orders.o_orderdate < '1995-01-01')(orders.o_orderdate >= '1994-01-01'))
+------------------------------------------PhysicalOlapScan[orders]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[customer]
--------------------------PhysicalDistribute
----------------------------PhysicalProject
------------------------------PhysicalOlapScan[supplier]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q8.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q8.out
index 4ce12cd85a3..ae4326989d5 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q8.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q8.out
@@ -16,25 +16,24 @@ PhysicalResultSink
--------------------------hashJoin[INNER_JOIN](customer.c_nationkey = n1.n_nationkey)
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN](supplier.s_suppkey = lineitem.l_suppkey)
---------------------------------PhysicalDistribute
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
---------------------------------------PhysicalDistribute
-----------------------------------------hashJoin[INNER_JOIN](orders.o_custkey = customer.c_custkey)
-------------------------------------------PhysicalDistribute
---------------------------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[lineitem]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((orders.o_orderdate <= '1996-12-31')(orders.o_orderdate >= '1995-01-01'))
---------------------------------------------------PhysicalOlapScan[orders]
-------------------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
+------------------------------------PhysicalDistribute
+--------------------------------------hashJoin[INNER_JOIN](orders.o_custkey = customer.c_custkey)
+----------------------------------------PhysicalDistribute
+------------------------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[lineitem]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[customer]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((part.p_type = 'ECONOMY ANODIZED STEEL'))
---------------------------------------------PhysicalOlapScan[part]
+----------------------------------------------filter((orders.o_orderdate <= '1996-12-31')(orders.o_orderdate >= '1995-01-01'))
+------------------------------------------------PhysicalOlapScan[orders]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[customer]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((part.p_type = 'ECONOMY ANODIZED STEEL'))
+------------------------------------------PhysicalOlapScan[part]
--------------------------------PhysicalDistribute
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[supplier]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q9.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q9.out
index e173453102b..98ca2fe323d 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q9.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape_no_stats/q9.out
@@ -11,24 +11,23 @@ PhysicalResultSink
----------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](supplier.s_suppkey = lineitem.l_suppkey)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](partsupp.ps_partkey = lineitem.l_partkey)(partsupp.ps_suppkey = lineitem.l_suppkey)
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
---------------------------------PhysicalDistribute
-----------------------------------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[lineitem]
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[orders]
---------------------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](partsupp.ps_partkey = lineitem.l_partkey)(partsupp.ps_suppkey = lineitem.l_suppkey)
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
+------------------------------PhysicalDistribute
+--------------------------------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
----------------------------------PhysicalProject
-------------------------------------filter((p_name like '%green%'))
---------------------------------------PhysicalOlapScan[part]
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[partsupp]
+------------------------------------PhysicalOlapScan[lineitem]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[orders]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((p_name like '%green%'))
+------------------------------------PhysicalOlapScan[part]
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[partsupp]
----------------------PhysicalDistribute
------------------------PhysicalProject
--------------------------PhysicalOlapScan[supplier]
diff --git a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf78.groovy b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf78.groovy
index 4d0dcc1fd02..ff672d6ce35 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf78.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf78.groovy
@@ -108,5 +108,5 @@ limit 100;
//File file = new File(outFile)
//file.write(getRuntimeFilters(plan))
- assertEquals("RF2[ss_sold_date_sk->[d_date_sk],RF1[ws_sold_date_sk->[d_date_sk],RF0[cs_sold_date_sk->[d_date_sk]", getRuntimeFilters(plan))
+ assertEquals("RF2[d_date_sk->[ss_sold_date_sk],RF1[d_date_sk->[ws_sold_date_sk],RF0[d_date_sk->[cs_sold_date_sk]", getRuntimeFilters(plan))
}
diff --git a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf85.groovy b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf85.groovy
index 91ab923b212..d95634b54d1 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf85.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf85.groovy
@@ -134,5 +134,5 @@ limit 100;
//File file = new File(outFile)
//file.write(getRuntimeFilters(plan))
- assertEquals("RF9[wr_reason_sk->[r_reason_sk],RF8[ws_web_page_sk->[wp_web_page_sk],RF5[cd_marital_status->[cd_marital_status],RF6[cd_education_status->[cd_education_status],RF7[wr_returning_cdemo_sk->[cd_demo_sk],RF4[wr_refunded_cdemo_sk->[cd_demo_sk],RF3[wr_refunded_addr_sk->[ca_address_sk],RF1[ws_item_sk->[wr_item_sk],RF2[ws_order_number->[wr_order_number],RF0[d_date_sk->[ws_sold_date_sk]", getRuntimeFilters(plan))
+ assertEquals("RF9[wr_reason_sk->[r_reason_sk],RF8[ws_web_page_sk->[wp_web_page_sk],RF5[cd_marital_status->[cd_marital_status],RF6[cd_education_status->[cd_education_status],RF7[wr_returning_cdemo_sk->[cd_demo_sk],RF4[wr_refunded_addr_sk->[ca_address_sk],RF3[wr_refunded_cdemo_sk->[cd_demo_sk],RF1[ws_item_sk->[wr_item_sk],RF2[ws_order_number->[wr_order_number],RF0[d_date_sk->[ws_sold_date_sk]", getRuntimeFilters(plan))
}
diff --git a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf95.groovy b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf95.groovy
index 490dc720f8c..d69a6c3eace 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf95.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf95.groovy
@@ -82,5 +82,5 @@ limit 100;
//File file = new File(outFile)
//file.write(getRuntimeFilters(plan))
- assertEquals("RF3[ws_order_number->[ws_order_number],RF5[wr_order_number->[ws_order_number, ws_order_number],RF4[ws_order_number->[ws_order_number, ws_order_number],RF2[web_site_sk->[ws_web_site_sk],RF1[d_date_sk->[ws_ship_date_sk],RF0[ca_address_sk->[ws_ship_addr_sk]", getRuntimeFilters(plan))
+ assertEquals("RF3[ws_order_number->[ws_order_number],RF4[wr_order_number->[ws_order_number, ws_order_number],RF5[ws_order_number->[ws_order_number, ws_order_number],RF2[web_site_sk->[ws_web_site_sk],RF1[d_date_sk->[ws_ship_date_sk],RF0[ca_address_sk->[ws_ship_addr_sk]", getRuntimeFilters(plan))
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org