This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new c6771da240d [fix](nereids) fix bug for A>n, where A.max is infinity
#39936 (#40368)
c6771da240d is described below
commit c6771da240d5ca55d8c73320781f3cfa9ec43adf
Author: minghong <[email protected]>
AuthorDate: Thu Sep 5 08:33:04 2024 +0800
[fix](nereids) fix bug for A>n, where A.max is infinity #39936 (#40368)
## Proposed changes
pick #39936
Issue Number: close #xxx
<!--Describe your changes.-->
---
.../doris/nereids/stats/FilterEstimation.java | 4 +-
.../apache/doris/statistics/StatisticRange.java | 22 ++-
.../doris/nereids/stats/FilterEstimationTest.java | 35 +++++
.../nereids_tpcds_shape_sf1000_p0/shape/query4.out | 16 +-
.../noStatsRfPrune/query11.out | 14 +-
.../noStatsRfPrune/query4.out | 28 ++--
.../noStatsRfPrune/query74.out | 14 +-
.../no_stats_shape/query11.out | 14 +-
.../no_stats_shape/query4.out | 28 ++--
.../no_stats_shape/query74.out | 14 +-
.../rf_prune/query4.out | 24 +--
.../rf_prune/query64.out | 173 +++++++++++----------
.../rf_prune/query74.out | 16 +-
.../nereids_tpcds_shape_sf100_p0/shape/query4.out | 24 +--
.../nereids_tpcds_shape_sf100_p0/shape/query74.out | 16 +-
15 files changed, 246 insertions(+), 196 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 047bc9b0dde..33b7e02b332 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
@@ -622,7 +622,9 @@ 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;
}
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..ca9735b5665 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,8 +179,7 @@ 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;
@@ -188,9 +191,18 @@ public class StatisticRange {
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 +253,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 d7c44e082cf..54843e40b9a 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
@@ -1365,4 +1365,39 @@ 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_sf1000_p0/shape/query4.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query4.out
index 4e38885b431..8522c6c215d 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query4.out
@@ -69,18 +69,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
---------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
+--------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------PhysicalProject
-------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
---------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query11.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query11.out
index 6922272ef0a..b7c625a6af1 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query11.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query11.out
@@ -39,16 +39,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((if((year_total > 0.00),
(cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000) > if((year_total
> 0.00), (cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.dyear = 2002) and (t_w_secyear.sale_type
= 'w'))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((if((year_total > 0.00),
(cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000) > if((year_total
> 0.00), (cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000)))
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.dyear = 2001) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.00))
+----------------------filter((t_w_secyear.dyear = 2002) and
(t_w_secyear.sale_type = 'w'))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -59,4 +55,8 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalProject
------------------------filter((t_s_firstyear.dyear = 2001) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.00))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.dyear = 2001) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.00))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query4.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query4.out
index e1934aa4667..5936085dc15 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query4.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query4.out
@@ -51,28 +51,20 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((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)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.dyear = 2000) and (t_w_secyear.sale_type
= 'w'))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((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] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.dyear = 1999) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.000000))
+----------------------filter((t_w_secyear.dyear = 2000) and
(t_w_secyear.sale_type = 'w'))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=((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)))
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
-----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=((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]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=()
+------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
--------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------PhysicalProject
-------------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
----------------------------PhysicalDistribute[DistributionSpecHash]
@@ -83,4 +75,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------PhysicalProject
--------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.dyear = 1999) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.000000))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query74.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query74.out
index 6aaf94a5249..334c6842b83 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query74.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query74.out
@@ -39,16 +39,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((if((year_total > 0.0), (year_total /
year_total), NULL) > if((year_total > 0.0), (year_total / year_total), NULL)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.sale_type = 'w') and (t_w_secyear.year =
2000))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((if((year_total > 0.0), (year_total
/ year_total), NULL) > if((year_total > 0.0), (year_total / year_total), NULL)))
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
+----------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -59,4 +55,8 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalProject
------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query11.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query11.out
index 80827cee429..4640c53beb3 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query11.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query11.out
@@ -39,16 +39,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((if((year_total > 0.00),
(cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000) > if((year_total
> 0.00), (cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.dyear = 2002) and (t_w_secyear.sale_type
= 'w'))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((if((year_total > 0.00),
(cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000) > if((year_total
> 0.00), (cast(year_total as DECIMALV3(38, 8)) / year_total), 0.000000)))
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.dyear = 2001) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.00))
+----------------------filter((t_w_secyear.dyear = 2002) and
(t_w_secyear.sale_type = 'w'))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -59,4 +55,8 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalProject
------------------------filter((t_s_firstyear.dyear = 2001) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.00))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.dyear = 2001) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.00))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query4.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query4.out
index 4a3163d9b6d..b266d982b54 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query4.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query4.out
@@ -51,28 +51,20 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((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)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.dyear = 2000) and (t_w_secyear.sale_type
= 'w'))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((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] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.dyear = 1999) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.000000))
+----------------------filter((t_w_secyear.dyear = 2000) and
(t_w_secyear.sale_type = 'w'))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=((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)))
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
-----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=((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]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=()
+------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
--------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------PhysicalProject
-------------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
----------------------------PhysicalDistribute[DistributionSpecHash]
@@ -83,4 +75,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------PhysicalProject
--------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.dyear = 1999) and
(t_w_firstyear.sale_type = 'w') and (t_w_firstyear.year_total > 0.000000))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query74.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query74.out
index b921c457fb9..a0bc49ecda7 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query74.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query74.out
@@ -39,16 +39,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------PhysicalDistribute[DistributionSpecGather]
--------PhysicalTopN[LOCAL_SORT]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_secyear.customer_id)) otherCondition=((if((year_total > 0.0), (year_total /
year_total), NULL) > if((year_total > 0.0), (year_total / year_total), NULL)))
---------------PhysicalDistribute[DistributionSpecHash]
-----------------PhysicalProject
-------------------filter((t_w_secyear.sale_type = 'w') and (t_w_secyear.year =
2000))
---------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id =
t_w_firstyear.customer_id)) otherCondition=((if((year_total > 0.0), (year_total
/ year_total), NULL) > if((year_total > 0.0), (year_total / year_total), NULL)))
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
+----------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -59,4 +55,8 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----------------------PhysicalProject
------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query4.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query4.out
index 7dc2ff7cfb5..d2aacccf205 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query4.out
@@ -64,23 +64,23 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=((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)))
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
-----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
---------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
+--------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------PhysicalProject
-------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
---------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query64.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query64.out
index 3639f4ef241..3d48307e63a 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query64.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query64.out
@@ -7,106 +7,107 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
--------PhysicalDistribute[DistributionSpecHash]
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
+--------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk =
item.i_item_sk)) otherCondition=() build RFs:RF20
i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
+------------------hashJoin[INNER_JOIN] hashCondition=((hd2.hd_income_band_sk =
ib2.ib_income_band_sk)) otherCondition=()
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF17
ss_customer_sk->[c_customer_sk]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk)) otherCondition=()
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=()
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[customer] apply
RFs: RF17
-------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=()
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=()
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
--------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=()
+----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=()
------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[household_demographics]
-------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[income_band]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer_demographics]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_address]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF11 ss_item_sk->[sr_item_sk];RF12
ss_ticket_number->[sr_ticket_number]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_returns] apply RFs:
RF11 RF12
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
build RFs:RF10 ss_cdemo_sk->[cd_demo_sk]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer_demographics]
apply RFs: RF10
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF9 ss_addr_sk->[ca_address_sk]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[customer_address]
apply RFs: RF9
-------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF8 i_item_sk->[cr_item_sk,cs_item_sk,ss_item_sk]
-----------------------------------------------PhysicalProject
-------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=()
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
+--------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status)))
+----------------------------------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=()
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF3 d_date_sk->[ss_sold_date_sk]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk)) otherCondition=()
build RFs:RF2 cs_item_sk->[ss_item_sk]
-----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF8
-----------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------filter((sale
> (2 * refund)))
-----------------------------------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+--------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
+------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF6 d_date_sk->[ss_sold_date_sk]
----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=()
+------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF4 sr_item_sk->[cr_item_sk,cs_item_sk]
--------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF8
+----------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk)) otherCondition=()
build RFs:RF2 cs_item_sk->[ss_item_sk]
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF6 RF20
+------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------------filter((sale
> (2 * refund)))
+------------------------------------------------------------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------------------------------------------hashAgg[LOCAL]
+------------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=()
+----------------------------------------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF4 RF20
+----------------------------------------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF4 RF20
--------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF8
-------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------filter(d_year
IN (2001, 2002))
+----------------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF20
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------filter(d_year
IN (2001, 2002))
+----------------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+--------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------PhysicalOlapScan[customer]
+--------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------------------------------------PhysicalProject
------------------------------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[household_demographics]
-----------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[income_band]
-------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------PhysicalOlapScan[store]
---------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[promotion]
-----------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((item.i_current_price
<= 33.00) and (item.i_current_price >= 24.00) and i_color IN ('blanched',
'brown', 'burlywood', 'chocolate', 'drab', 'medium'))
-----------------------------------------------------PhysicalOlapScan[item]
+--------------------------------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------PhysicalOlapScan[customer_demographics]
+------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------PhysicalProject
+----------------------------------------------------PhysicalOlapScan[promotion]
+--------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[customer_address]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer_address]
+------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[income_band]
--------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[date_dim]
+------------------------PhysicalOlapScan[income_band]
----------------PhysicalDistribute[DistributionSpecReplicated]
------------------PhysicalProject
---------------------PhysicalOlapScan[date_dim]
+--------------------filter((item.i_current_price <= 33.00) and
(item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
+----------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query74.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query74.out
index 88963927ca3..04d9e3487c9 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query74.out
@@ -45,18 +45,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
-------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id =
t_s_firstyear.customer_id)) otherCondition=()
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------PhysicalProject
+----------------------filter((t_s_secyear.sale_type = 's') and
(t_s_secyear.year = 2000))
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_w_secyear.customer_id))
otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------PhysicalProject
-------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
+------------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------PhysicalProject
-------------------------filter((t_s_secyear.sale_type = 's') and
(t_s_secyear.year = 2000))
+------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------PhysicalProject
-----------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
-------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
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 4e38885b431..12fe14c3d4d 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
@@ -64,23 +64,23 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_firstyear.customer_id))
otherCondition=((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)))
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
-----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
---------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_secyear.customer_id = t_s_firstyear.customer_id))
otherCondition=()
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_c_secyear.customer_id))
otherCondition=()
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
+--------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------filter((t_s_secyear.dyear = 2000) and
(t_s_secyear.sale_type = 's'))
+--------------------------------filter((t_s_firstyear.dyear = 1999) and
(t_s_firstyear.sale_type = 's') and (t_s_firstyear.year_total > 0.000000))
----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------PhysicalProject
-------------------------------filter((t_c_secyear.dyear = 2000) and
(t_c_secyear.sale_type = 'c'))
---------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((t_c_firstyear.dyear = 1999) and
(t_c_firstyear.sale_type = 'c') and (t_c_firstyear.year_total > 0.000000))
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
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 05d89d51e4b..f189a6a8da6 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
@@ -45,18 +45,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------filter((t_w_firstyear.sale_type = 'w') and
(t_w_firstyear.year = 1999) and (t_w_firstyear.year_total > 0.0))
--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t_s_firstyear.customer_id
= t_w_secyear.customer_id)) otherCondition=()
-------------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id
= t_s_firstyear.customer_id)) otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((t_s_secyear.customer_id =
t_s_firstyear.customer_id)) otherCondition=()
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------PhysicalProject
+----------------------filter((t_s_secyear.sale_type = 's') and
(t_s_secyear.year = 2000))
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------hashJoin[INNER_JOIN]
hashCondition=((t_s_firstyear.customer_id = t_w_secyear.customer_id))
otherCondition=()
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------PhysicalProject
-------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
+------------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------PhysicalProject
-------------------------filter((t_s_secyear.sale_type = 's') and
(t_s_secyear.year = 2000))
+------------------------filter((t_s_firstyear.sale_type = 's') and
(t_s_firstyear.year = 1999) and (t_s_firstyear.year_total > 0.0))
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------PhysicalProject
-----------------------filter((t_w_secyear.sale_type = 'w') and
(t_w_secyear.year = 2000))
-------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
