This is an automated email from the ASF dual-hosted git repository.
amashenkov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new baf16be610b IGNITE-28213 Enable JOIN_PUSH_TRANSITIVE_PREDICATES rule
(#7766)
baf16be610b is described below
commit baf16be610b321ad5ff521356f65445ed05b02d3
Author: Andrew V. Mashenkov <[email protected]>
AuthorDate: Mon Mar 30 11:36:16 2026 +0300
IGNITE-28213 Enable JOIN_PUSH_TRANSITIVE_PREDICATES rule (#7766)
---
.../internal/sql/engine/prepare/PlannerPhase.java | 1 +
.../planner/PredicatePushDownPlannerTest.java | 23 ++++
.../src/test/resources/mapping/correlated.test | 5 +-
.../resources/mapping/test_partition_pruning.test | 27 ++--
.../src/test/resources/tpcds/plan/q1.plan | 63 ++++-----
.../src/test/resources/tpcds/plan/q16.plan | 27 ++--
.../src/test/resources/tpcds/plan/q2.plan | 79 +++++------
.../src/test/resources/tpcds/plan/q31.plan | 8 +-
.../src/test/resources/tpcds/plan/q59.plan | 65 ++++-----
.../src/test/resources/tpcds/plan/q6.plan | 26 ++--
.../src/test/resources/tpcds/plan/q78.plan | 146 +++++++++++----------
.../src/test/resources/tpcds/plan/q85.plan | 116 ++++++++--------
.../src/test/resources/tpcds/plan/q94.plan | 27 ++--
13 files changed, 329 insertions(+), 284 deletions(-)
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
index 93161a67e8d..303f08400a4 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerPhase.java
@@ -205,6 +205,7 @@ public enum PlannerPhase {
CoreRules.JOIN_PUSH_EXPRESSIONS,
IgniteJoinConditionPushRule.INSTANCE,
+ CoreRules.JOIN_PUSH_TRANSITIVE_PREDICATES,
FilterIntoJoinRule.FilterIntoJoinRuleConfig.DEFAULT
.withOperandSupplier(b0 ->
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PredicatePushDownPlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PredicatePushDownPlannerTest.java
index 140e4346192..778932e3175 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PredicatePushDownPlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PredicatePushDownPlannerTest.java
@@ -53,6 +53,29 @@ public class PredicatePushDownPlannerTest extends
AbstractPlannerTest {
}
+ @Test
+ protected void transitivePredicatePushedDownToJoinSources() throws
Exception {
+ IgniteSchema schema = createSchema(
+ createTable("T1"),
+ createTable("T2")
+ );
+
+ String sql = ""
+ + " SELECT * "
+ + " FROM t1 "
+ + " JOIN t2 ON t1.c1 = t2.c1"
+ + " WHERE t1.c1 = 10";
+
+ assertPlan(sql, schema, nodeOrAnyChild(isInstanceOf(Join.class))
+
.and(hasChildThat(isInstanceOf(ProjectableFilterableTableScan.class)
+ .and(scan ->
scan.condition().toString().contains("=($t0, 10)")
+ &&
scan.getTable().getQualifiedName().contains("T1"))))
+
.and(hasChildThat(isInstanceOf(ProjectableFilterableTableScan.class)
+ .and(scan ->
scan.condition().toString().contains("=($t0, 10)")
+ &&
scan.getTable().getQualifiedName().contains("T2"))))
+ );
+ }
+
private static IgniteTable createTable(String tableName) {
return TestBuilders.table()
.name(tableName)
diff --git a/modules/sql-engine/src/test/resources/mapping/correlated.test
b/modules/sql-engine/src/test/resources/mapping/correlated.test
index 70d95670e3e..4fd6027d50e 100644
--- a/modules/sql-engine/src/test/resources/mapping/correlated.test
+++ b/modules/sql-engine/src/test/resources/mapping/correlated.test
@@ -280,12 +280,13 @@ Fragment#3
Sender
distribution: single
targetFragmentId: 2
- est: (rows=100008)
+ est: (rows=1)
TableScan
table: PUBLIC.T_N1
+ predicate: =(MOD(CAST(ID):BIGINT NOT NULL, 10), 0)
fieldNames: [C1, ID0]
projection: [C1, CAST(ID):BIGINT NOT NULL]
- est: (rows=100008)
+ est: (rows=1)
---
N0
diff --git
a/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
b/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
index 0bafb7ce56e..d0932226f45 100644
--- a/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
+++ b/modules/sql-engine/src/test/resources/mapping/test_partition_pruning.test
@@ -164,6 +164,7 @@ Fragment#4
est: (rows=1)
---
# Self join, different predicates that produce disjoint set of partitions
+# TODO https://issues.apache.org/jira/browse/IGNITE-28389: Fix the test. We
expect the mapper should eliminate all the disjoined parts.
N1
SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'HashJoinConverter',
'CorrelatedNestedLoopJoin') */ *
FROM t1_n1n2n3 as t1, t1_n1n2n3 as t2
@@ -172,7 +173,7 @@ SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter',
'HashJoinConverter', 'Correla
Fragment#2 root
distribution: single
executionNodes: [N1]
- exchangeSourceNodes: {3=[N2, N3]}
+ exchangeSourceNodes: {3=[N1, N2, N3]}
colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 3], assignments={},
partitionsWithConsistencyTokens={N1=[]}}
colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 3], assignments={},
partitionsWithConsistencyTokens={N1=[]}}
tree:
@@ -183,37 +184,37 @@ Fragment#2 root
Fragment#3
distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1
- executionNodes: [N2, N3]
+ executionNodes: [N1, N2, N3]
targetNodes: [N1]
- colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3},
partitionsWithConsistencyTokens={N2=[part_1:3]}}
- colocationGroup[1]: {nodes=[N3], sourceIds=[1], assignments={part_2=N3:3},
partitionsWithConsistencyTokens={N3=[part_2:3]}}
- partitions: [T1_N1N2N3=[N2={1}, N3={2}]]
+ colocationGroup[0]: {nodes=[N1, N2, N3], sourceIds=[0, 1],
assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3},
partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}}
+ colocationGroup[1]: {nodes=[N1, N2, N3], sourceIds=[0, 1],
assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3},
partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}}
+ partitions: [T1_N1N2N3=[N1={0}, N2={1}, N3={2}]]
tree:
Sender
distribution: single
targetFragmentId: 2
- est: (rows=32316)
+ est: (rows=6250)
MergeJoin
predicate: =(ID, ID$0)
fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0]
type: inner
- est: (rows=32316)
+ est: (rows=6250)
Sort
collation: [ID ASC]
- est: (rows=56847)
+ est: (rows=25000)
TableScan
table: PUBLIC.T1_N1N2N3
- predicate: SEARCH(ID, Sarg[1, 3])
+ predicate: false
fieldNames: [ID, C1, C2]
- est: (rows=56847)
+ est: (rows=25000)
Sort
collation: [ID ASC]
- est: (rows=56847)
+ est: (rows=25000)
TableScan
table: PUBLIC.T1_N1N2N3
- predicate: SEARCH(ID, Sarg[42, 44])
+ predicate: false
fieldNames: [ID, C1, C2]
- est: (rows=56847)
+ est: (rows=25000)
---
# Correlated
# Prune partitions from left arm statically, and pass meta to the right arm.
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q1.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q1.plan
index 9251c0f581d..b7f9579bab6 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q1.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q1.plan
@@ -4,11 +4,11 @@ Limit
Project
fieldNames: [C_CUSTOMER_ID]
projection: [C_CUSTOMER_ID]
- est: (rows=26702964)
+ est: (rows=24032667)
HashJoin
predicate: =(CTR_CUSTOMER_SK, C_CUSTOMER_SK)
type: inner
- est: (rows=26702964)
+ est: (rows=24032667)
Exchange
distribution: single
est: (rows=100000)
@@ -22,7 +22,7 @@ Limit
MergeJoin
predicate: =(S_STORE_SK, CTR_STORE_SK)
type: inner
- est: (rows=26702964)
+ est: (rows=24032667)
Exchange
distribution: single
est: (rows=4)
@@ -38,38 +38,41 @@ Limit
predicate: AND(=(CTR_STORE_SK, CTR_STORE_SK$0),
>(CAST(CTR_TOTAL_RETURN):DECIMAL(30, 17), *($f1, 1.2:DECIMAL(2, 1))))
fieldNames: [CTR_CUSTOMER_SK, CTR_STORE_SK, CTR_TOTAL_RETURN,
CTR_STORE_SK$0, $f1]
type: inner
- est: (rows=80189081)
+ est: (rows=72170173)
Sort
collation: [CTR_STORE_SK ASC]
- est: (rows=34467)
- ColocatedHashAggregate
- fieldNames: [CTR_CUSTOMER_SK, CTR_STORE_SK, CTR_TOTAL_RETURN]
- group: [CTR_CUSTOMER_SK, CTR_STORE_SK]
- aggregation: [SUM(SR_FEE)]
- est: (rows=34467)
- Project
- fieldNames: [CTR_CUSTOMER_SK, CTR_STORE_SK, SR_FEE]
- projection: [SR_CUSTOMER_SK, SR_STORE_SK, SR_FEE]
- est: (rows=95742)
- HashJoin
- predicate: =(SR_RETURNED_DATE_SK, D_DATE_SK)
- type: inner
+ est: (rows=31020)
+ Filter
+ predicate: IS NOT NULL(CTR_STORE_SK)
+ est: (rows=31020)
+ ColocatedHashAggregate
+ fieldNames: [CTR_CUSTOMER_SK, CTR_STORE_SK, CTR_TOTAL_RETURN]
+ group: [CTR_CUSTOMER_SK, CTR_STORE_SK]
+ aggregation: [SUM(SR_FEE)]
+ est: (rows=34467)
+ Project
+ fieldNames: [CTR_CUSTOMER_SK, CTR_STORE_SK, SR_FEE]
+ projection: [SR_CUSTOMER_SK, SR_STORE_SK, SR_FEE]
est: (rows=95742)
- Exchange
- distribution: single
- est: (rows=287514)
- TableScan
- table: PUBLIC.STORE_RETURNS
- fieldNames: [SR_RETURNED_DATE_SK, SR_CUSTOMER_SK,
SR_STORE_SK, SR_FEE]
+ HashJoin
+ predicate: =(SR_RETURNED_DATE_SK, D_DATE_SK)
+ type: inner
+ est: (rows=95742)
+ Exchange
+ distribution: single
est: (rows=287514)
- Exchange
- distribution: single
- est: (rows=24325)
- TableScan
- table: PUBLIC.DATE_DIM
- predicate: =(D_YEAR, 2000)
- fieldNames: [D_DATE_SK, D_YEAR]
+ TableScan
+ table: PUBLIC.STORE_RETURNS
+ fieldNames: [SR_RETURNED_DATE_SK, SR_CUSTOMER_SK,
SR_STORE_SK, SR_FEE]
+ est: (rows=287514)
+ Exchange
+ distribution: single
est: (rows=24325)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ predicate: =(D_YEAR, 2000)
+ fieldNames: [D_DATE_SK, D_YEAR]
+ est: (rows=24325)
ColocatedHashAggregate
fieldNames: [CTR_STORE_SK, $f1]
group: [CTR_STORE_SK]
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q16.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q16.plan
index 9ce2e36a302..9c96df5f528 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q16.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q16.plan
@@ -4,56 +4,57 @@ Sort
est: (rows=100)
Project
projection: [order count, CAST(total shipping cost):DECIMAL(14, 2),
CAST(total net profit):DECIMAL(14, 2)]
- est: (rows=981399350335625)
+ est: (rows=883259415302063)
ColocatedSortAggregate
fieldNames: [order count, total shipping cost, total net profit]
collation: []
group: []
aggregation: [COUNT(CS_ORDER_NUMBER), SUM(total shipping cost),
SUM(total net profit)]
- est: (rows=981399350335625)
+ est: (rows=883259415302063)
ColocatedSortAggregate
fieldNames: [CS_ORDER_NUMBER, total shipping cost, total net profit]
collation: [CS_ORDER_NUMBER ASC]
group: [CS_ORDER_NUMBER]
aggregation: [SUM(CS_EXT_SHIP_COST), SUM(CS_NET_PROFIT)]
- est: (rows=981399350335625)
+ est: (rows=883259415302063)
Project
fieldNames: [CS_ORDER_NUMBER, CS_EXT_SHIP_COST, CS_NET_PROFIT]
projection: [CS_ORDER_NUMBER, CS_EXT_SHIP_COST, CS_NET_PROFIT]
- est: (rows=4906996751678128)
+ est: (rows=4416297076510316)
Filter
predicate: IS NULL($f1)
- est: (rows=4906996751678128)
+ est: (rows=4416297076510316)
MergeJoin
predicate: =(CS_ORDER_NUMBER, CR_ORDER_NUMBER)
type: left
- est: (rows=19627987006712512)
+ est: (rows=17665188306041262)
HashJoin
predicate: AND(=(CS_WAREHOUSE_SK, CS_WAREHOUSE_SK0),
=(CS_ORDER_NUMBER, CS_ORDER_NUMBER0))
type: inner
- est: (rows=4540509525340)
+ est: (rows=4086458572806)
HashJoin
predicate: =(CS_CALL_CENTER_SK, CC_CALL_CENTER_SK)
type: inner
- est: (rows=120009)
+ est: (rows=108008)
HashJoin
predicate: =(CS_SHIP_ADDR_SK, CA_ADDRESS_SK)
type: inner
- est: (rows=120009)
+ est: (rows=108008)
HashJoin
predicate: =(CS_SHIP_DATE_SK, D_DATE_SK)
type: inner
- est: (rows=360387)
+ est: (rows=324348)
Exchange
distribution: single
- est: (rows=1441548)
+ est: (rows=1297393)
Sort
collation: [CS_ORDER_NUMBER ASC, CS_WAREHOUSE_SK
ASC]
- est: (rows=1441548)
+ est: (rows=1297393)
TableScan
table: PUBLIC.CATALOG_SALES
+ predicate: IS NOT NULL(CS_WAREHOUSE_SK)
fieldNames: [CS_SHIP_DATE_SK, CS_SHIP_ADDR_SK,
CS_CALL_CENTER_SK, CS_WAREHOUSE_SK, CS_ORDER_NUMBER, CS_EXT_SHIP_COST,
CS_NET_PROFIT]
- est: (rows=1441548)
+ est: (rows=1297393)
Exchange
distribution: single
est: (rows=18262)
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q2.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q2.plan
index 427680a7b34..aed9add3960 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q2.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q2.plan
@@ -1,11 +1,11 @@
Project
fieldNames: [D_WEEK_SEQ1, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6,
EXPR$7]
projection: [D_WEEK_SEQ1, ROUND(/(SUN_SALES1, SUN_SALES2), 2),
ROUND(/(MON_SALES1, MON_SALES2), 2), ROUND(/(TUE_SALES1, TUE_SALES2), 2),
ROUND(/(WED_SALES1, WED_SALES2), 2), ROUND(/(THU_SALES1, THU_SALES2), 2),
ROUND(/(FRI_SALES1, FRI_SALES2), 2), ROUND(/(SAT_SALES1, SAT_SALES2), 2)]
- est: (rows=44786183521985440000000000)
+ est: (rows=36276808652808206000000000)
HashJoin
predicate: =(D_WEEK_SEQ1, $f8)
type: inner
- est: (rows=44786183521985440000000000)
+ est: (rows=36276808652808206000000000)
Project
fieldNames: [D_WEEK_SEQ1, SUN_SALES1, MON_SALES1, TUE_SALES1,
WED_SALES1, THU_SALES1, FRI_SALES1, SAT_SALES1]
projection: [D_WEEK_SEQ$0, SUN_SALES, MON_SALES, TUE_SALES, WED_SALES,
THU_SALES, FRI_SALES, SAT_SALES]
@@ -68,53 +68,56 @@ Project
Project
fieldNames: [D_WEEK_SEQ2, SUN_SALES2, MON_SALES2, TUE_SALES2,
WED_SALES2, THU_SALES2, FRI_SALES2, SAT_SALES2, $f8]
projection: [D_WEEK_SEQ$0, SUN_SALES, MON_SALES, TUE_SALES, WED_SALES,
THU_SALES, FRI_SALES, SAT_SALES, -(D_WEEK_SEQ$0, 53)]
- est: (rows=17279310079203)
+ est: (rows=13996241164154)
HashJoin
predicate: =(D_WEEK_SEQ, D_WEEK_SEQ$0)
fieldNames: [D_WEEK_SEQ, D_YEAR, D_WEEK_SEQ$0, SUN_SALES, MON_SALES,
TUE_SALES, WED_SALES, THU_SALES, FRI_SALES, SAT_SALES]
type: inner
- est: (rows=17279310079203)
+ est: (rows=13996241164154)
Exchange
distribution: single
- est: (rows=24325)
+ est: (rows=21893)
TableScan
table: PUBLIC.DATE_DIM
- predicate: =(D_YEAR, 1999)
+ predicate: AND(=(D_YEAR, 1999), IS NOT NULL(D_WEEK_SEQ))
fieldNames: [D_WEEK_SEQ, D_YEAR]
- est: (rows=24325)
- ColocatedHashAggregate
- fieldNames: [D_WEEK_SEQ, SUN_SALES, MON_SALES, TUE_SALES,
WED_SALES, THU_SALES, FRI_SALES, SAT_SALES]
- group: [D_WEEK_SEQ]
- aggregation: [SUM($f1), SUM($f2), SUM($f3), SUM($f4), SUM($f5),
SUM($f6), SUM($f7)]
- est: (rows=4735617650)
- Project
- fieldNames: [D_WEEK_SEQ, $f1, $f2, $f3, $f4, $f5, $f6, $f7]
- projection: [D_WEEK_SEQ, CASE(=(D_DAY_NAME, _UTF-8'Sunday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Monday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Tuesday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Wednesday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Thursday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Friday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'S [...]
- est: (rows=23678088250)
- HashJoin
- predicate: =(D_DATE_SK, SOLD_DATE_SK)
- type: inner
+ est: (rows=21893)
+ Filter
+ predicate: IS NOT NULL(D_WEEK_SEQ)
+ est: (rows=4262055885)
+ ColocatedHashAggregate
+ fieldNames: [D_WEEK_SEQ, SUN_SALES, MON_SALES, TUE_SALES,
WED_SALES, THU_SALES, FRI_SALES, SAT_SALES]
+ group: [D_WEEK_SEQ]
+ aggregation: [SUM($f1), SUM($f2), SUM($f3), SUM($f4), SUM($f5),
SUM($f6), SUM($f7)]
+ est: (rows=4735617650)
+ Project
+ fieldNames: [D_WEEK_SEQ, $f1, $f2, $f3, $f4, $f5, $f6, $f7]
+ projection: [D_WEEK_SEQ, CASE(=(D_DAY_NAME, _UTF-8'Sunday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Monday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Tuesday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Wednesday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Thursday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8'Friday'),
SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME, _UTF-8 [...]
est: (rows=23678088250)
- UnionAll
- est: (rows=2160932)
- Exchange
- distribution: single
- est: (rows=719384)
- TableScan
- table: PUBLIC.WEB_SALES
- fieldNames: [SOLD_DATE_SK, SALES_PRICE]
+ HashJoin
+ predicate: =(D_DATE_SK, SOLD_DATE_SK)
+ type: inner
+ est: (rows=23678088250)
+ UnionAll
+ est: (rows=2160932)
+ Exchange
+ distribution: single
est: (rows=719384)
+ TableScan
+ table: PUBLIC.WEB_SALES
+ fieldNames: [SOLD_DATE_SK, SALES_PRICE]
+ est: (rows=719384)
+ Exchange
+ distribution: single
+ est: (rows=1441548)
+ TableScan
+ table: PUBLIC.CATALOG_SALES
+ fieldNames: [SOLD_DATE_SK, SALES_PRICE]
+ est: (rows=1441548)
Exchange
distribution: single
- est: (rows=1441548)
- TableScan
- table: PUBLIC.CATALOG_SALES
- fieldNames: [SOLD_DATE_SK, SALES_PRICE]
- est: (rows=1441548)
- Exchange
- distribution: single
- est: (rows=73049)
- TableScan
- table: PUBLIC.DATE_DIM
- fieldNames: [D_DATE_SK, D_WEEK_SEQ, D_DAY_NAME]
est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fieldNames: [D_DATE_SK, D_WEEK_SEQ, D_DAY_NAME]
+ est: (rows=73049)
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q31.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q31.plan
index e69f365a348..2d49f4b9b73 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q31.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q31.plan
@@ -1,12 +1,12 @@
Project
fieldNames: [CA_COUNTY, D_YEAR, WEB_Q1_Q2_INCREASE, STORE_Q1_Q2_INCREASE,
WEB_Q2_Q3_INCREASE, STORE_Q2_Q3_INCREASE]
projection: [CA_COUNTY, D_YEAR, /(WEB_SALES, WEB_SALES$0),
/(STORE_SALES$0, STORE_SALES), /(WEB_SALES$0$0, WEB_SALES), /(STORE_SALES$0$0,
STORE_SALES$0)]
- est: (rows=1060299571221102000000000)
+ est: (rows=159044935683165300000000)
HashJoin
predicate: AND(CASE(>(STORE_SALES, 0.00), CASE(>(WEB_SALES$0, 0.00),
>(/(WEB_SALES, WEB_SALES$0), /(STORE_SALES$0, STORE_SALES)), false), false),
=(CA_COUNTY, CA_COUNTY$0$0$0))
fieldNames: [CA_COUNTY, D_QOY, D_YEAR, STORE_SALES, CA_COUNTY$0,
D_QOY$0, D_YEAR$0, STORE_SALES$0, CA_COUNTY$0$0, D_QOY$0$0, D_YEAR$0$0,
STORE_SALES$0$0, CA_COUNTY$1, D_QOY$1, D_YEAR$1, WEB_SALES, CA_COUNTY$0$0$0,
D_QOY$0$0$0, D_YEAR$0$0$0, WEB_SALES$0, CA_COUNTY$0$0$0$0, D_QOY$0$0$0$0,
D_YEAR$0$0$0$0, WEB_SALES$0$0]
type: inner
- est: (rows=1060299571221102000000000)
+ est: (rows=159044935683165300000000)
Sort
collation: [D_YEAR ASC]
est: (rows=155870)
@@ -50,10 +50,10 @@ Project
fieldNames: [CA_ADDRESS_SK, CA_COUNTY]
est: (rows=50000)
HashJoin
- predicate: AND(CASE(>(STORE_SALES, 0.00), CASE(>(WEB_SALES, 0.00),
>(/(WEB_SALES$0$0, WEB_SALES), /(STORE_SALES$0, STORE_SALES)), false), false),
=(CA_COUNTY$0$0, CA_COUNTY))
+ predicate: AND(CASE(>(STORE_SALES, 0.00), CASE(>(WEB_SALES, 0.00),
>(/(WEB_SALES$0$0, WEB_SALES), /(STORE_SALES$0, STORE_SALES)), false), false),
=(CA_COUNTY$0, CA_COUNTY$0$0), =(CA_COUNTY, CA_COUNTY$0$0$0))
fieldNames: [CA_COUNTY, D_QOY, D_YEAR, STORE_SALES, CA_COUNTY$0,
D_QOY$0, D_YEAR$0, STORE_SALES$0, CA_COUNTY$1, D_QOY$1, D_YEAR$1, WEB_SALES,
CA_COUNTY$0$0, D_QOY$0$0, D_YEAR$0$0, WEB_SALES$0, CA_COUNTY$0$0$0,
D_QOY$0$0$0, D_YEAR$0$0$0, WEB_SALES$0$0]
type: inner
- est: (rows=181399307039732700000)
+ est: (rows=27209896055959904000)
HashJoin
predicate: =(CA_COUNTY, CA_COUNTY$0)
fieldNames: [CA_COUNTY, D_QOY, D_YEAR, STORE_SALES, CA_COUNTY$0,
D_QOY$0, D_YEAR$0, STORE_SALES$0]
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q59.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q59.plan
index b860efe7d5a..dbc932e941e 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q59.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q59.plan
@@ -4,11 +4,11 @@ Limit
Project
fieldNames: [S_STORE_NAME1, S_STORE_ID1, D_WEEK_SEQ1, EXPR$3, EXPR$4,
EXPR$5, EXPR$6, EXPR$7, EXPR$8, EXPR$9]
projection: [S_STORE_NAME1, S_STORE_ID1, D_WEEK_SEQ1, /(SUN_SALES1,
SUN_SALES2), /(MON_SALES1, MON_SALES2), /(TUE_SALES1, TUE_SALES2),
/(WED_SALES1, WED_SALES2), /(THU_SALES1, THU_SALES2), /(FRI_SALES1,
FRI_SALES2), /(SAT_SALES1, SAT_SALES2)]
- est: (rows=181545469684679584)
+ est: (rows=147051830444590464)
HashJoin
predicate: AND(=(S_STORE_ID1, S_STORE_ID2), =(D_WEEK_SEQ1, $f9))
type: inner
- est: (rows=181545469684679584)
+ est: (rows=147051830444590464)
Sort
collation: [S_STORE_NAME1 ASC, S_STORE_ID1 ASC, D_WEEK_SEQ1 ASC]
est: (rows=2840543529)
@@ -70,24 +70,24 @@ Limit
Project
fieldNames: [D_WEEK_SEQ2, S_STORE_ID2, SUN_SALES2, MON_SALES2,
TUE_SALES2, WED_SALES2, THU_SALES2, FRI_SALES2, SAT_SALES2, $f9]
projection: [D_WEEK_SEQ$0, S_STORE_ID, SUN_SALES, MON_SALES,
TUE_SALES, WED_SALES, THU_SALES, FRI_SALES, SAT_SALES, -(D_WEEK_SEQ$0, 52)]
- est: (rows=2840543529)
+ est: (rows=2300840259)
HashJoin
predicate: =(D_WEEK_SEQ, D_WEEK_SEQ$0)
fieldNames: [D_MONTH_SEQ, D_WEEK_SEQ, S_STORE_SK, S_STORE_ID,
D_WEEK_SEQ$0, SS_STORE_SK, SUN_SALES, MON_SALES, TUE_SALES, WED_SALES,
THU_SALES, FRI_SALES, SAT_SALES]
type: inner
- est: (rows=2840543529)
+ est: (rows=2300840259)
Exchange
distribution: single
- est: (rows=18262)
+ est: (rows=16436)
TableScan
table: PUBLIC.DATE_DIM
- predicate: SEARCH(D_MONTH_SEQ, Sarg[[1217..1228]])
+ predicate: AND(SEARCH(D_MONTH_SEQ, Sarg[[1217..1228]]), IS NOT
NULL(D_WEEK_SEQ))
fieldNames: [D_MONTH_SEQ, D_WEEK_SEQ]
- est: (rows=18262)
+ est: (rows=16436)
HashJoin
predicate: =(SS_STORE_SK, S_STORE_SK)
type: inner
- est: (rows=1036945)
+ est: (rows=933251)
Exchange
distribution: single
est: (rows=12)
@@ -95,30 +95,33 @@ Limit
table: PUBLIC.STORE
fieldNames: [S_STORE_SK, S_STORE_ID]
est: (rows=12)
- ColocatedHashAggregate
- fieldNames: [D_WEEK_SEQ, SS_STORE_SK, SUN_SALES, MON_SALES,
TUE_SALES, WED_SALES, THU_SALES, FRI_SALES, SAT_SALES]
- group: [D_WEEK_SEQ, SS_STORE_SK]
- aggregation: [SUM($f2), SUM($f3), SUM($f4), SUM($f5),
SUM($f6), SUM($f7), SUM($f8)]
- est: (rows=1036945)
- Project
- fieldNames: [D_WEEK_SEQ, SS_STORE_SK, $f2, $f3, $f4, $f5,
$f6, $f7, $f8]
- projection: [D_WEEK_SEQ, SS_STORE_SK, CASE(=(D_DAY_NAME,
_UTF-8'Sunday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Monday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Tuesday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Wednesday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Thursday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Friday'), SS_SALES_PRICE, null:DECIMAL( [...]
- est: (rows=2880404)
- HashJoin
- predicate: =(D_DATE_SK, SS_SOLD_DATE_SK)
- type: inner
+ Filter
+ predicate: IS NOT NULL(D_WEEK_SEQ)
+ est: (rows=933251)
+ ColocatedHashAggregate
+ fieldNames: [D_WEEK_SEQ, SS_STORE_SK, SUN_SALES, MON_SALES,
TUE_SALES, WED_SALES, THU_SALES, FRI_SALES, SAT_SALES]
+ group: [D_WEEK_SEQ, SS_STORE_SK]
+ aggregation: [SUM($f2), SUM($f3), SUM($f4), SUM($f5),
SUM($f6), SUM($f7), SUM($f8)]
+ est: (rows=1036945)
+ Project
+ fieldNames: [D_WEEK_SEQ, SS_STORE_SK, $f2, $f3, $f4, $f5,
$f6, $f7, $f8]
+ projection: [D_WEEK_SEQ, SS_STORE_SK, CASE(=(D_DAY_NAME,
_UTF-8'Sunday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Monday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Tuesday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Wednesday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Thursday'), SS_SALES_PRICE, null:DECIMAL(7, 2)), CASE(=(D_DAY_NAME,
_UTF-8'Friday'), SS_SALES_PRICE, null:DECIMA [...]
est: (rows=2880404)
- Exchange
- distribution: single
+ HashJoin
+ predicate: =(D_DATE_SK, SS_SOLD_DATE_SK)
+ type: inner
est: (rows=2880404)
- TableScan
- table: PUBLIC.STORE_SALES
- fieldNames: [SS_SOLD_DATE_SK, SS_STORE_SK,
SS_SALES_PRICE]
+ Exchange
+ distribution: single
est: (rows=2880404)
- Exchange
- distribution: single
- est: (rows=73049)
- TableScan
- table: PUBLIC.DATE_DIM
- fieldNames: [D_DATE_SK, D_WEEK_SEQ, D_DAY_NAME]
+ TableScan
+ table: PUBLIC.STORE_SALES
+ fieldNames: [SS_SOLD_DATE_SK, SS_STORE_SK,
SS_SALES_PRICE]
+ est: (rows=2880404)
+ Exchange
+ distribution: single
est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fieldNames: [D_DATE_SK, D_WEEK_SEQ, D_DAY_NAME]
+ est: (rows=73049)
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q6.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q6.plan
index cefb1e0e8f1..6424334f626 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q6.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q6.plan
@@ -4,36 +4,40 @@ Sort
est: (rows=100)
Filter
predicate: >=(CNT, 10)
- est: (rows=28746)
+ est: (rows=25872)
ColocatedHashAggregate
fieldNames: [STATE, CNT]
group: [STATE]
aggregation: [COUNT()]
- est: (rows=57493)
+ est: (rows=51744)
Project
fieldNames: [STATE]
projection: [CA_STATE]
- est: (rows=287465)
+ est: (rows=258718)
HashJoin
predicate: =(C_CUSTOMER_SK, SS_CUSTOMER_SK)
type: inner
- est: (rows=287465)
+ est: (rows=258718)
HashJoin
predicate: =(SS_ITEM_SK, I_ITEM_SK)
type: inner
- est: (rows=287465)
+ est: (rows=258718)
HashJoin
predicate: AND(=(I_CATEGORY, I_CATEGORY$0),
>(CAST(I_CURRENT_PRICE):DECIMAL(23, 17), *(1.2:DECIMAL(2, 1), EXPR$0)))
fieldNames: [I_ITEM_SK, I_CURRENT_PRICE, I_CATEGORY,
I_CATEGORY$0, EXPR$0]
type: inner
- est: (rows=4374000)
+ est: (rows=3936600)
Exchange
distribution: single
- est: (rows=18000)
- TableScan
- table: PUBLIC.ITEM
- fieldNames: [I_ITEM_SK, I_CURRENT_PRICE, I_CATEGORY]
- est: (rows=18000)
+ est: (rows=16200)
+ Sort
+ collation: [I_ITEM_SK ASC]
+ est: (rows=16200)
+ TableScan
+ table: PUBLIC.ITEM
+ predicate: IS NOT NULL(I_CATEGORY)
+ fieldNames: [I_ITEM_SK, I_CURRENT_PRICE, I_CATEGORY]
+ est: (rows=16200)
Project
fieldNames: [I_CATEGORY, EXPR$0]
projection: [f0, DECIMAL_DIVIDE(CAST(f0_1):DECIMAL(14, 2),
f1_2, 21, 16)]
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q78.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q78.plan
index 07699e582b6..c5a6fa3a2d5 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q78.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q78.plan
@@ -5,18 +5,18 @@ Sort
Project
fieldNames: [SS_CUSTOMER_SK, RATIO, STORE_QTY, STORE_WHOLESALE_COST,
STORE_SALES_PRICE, OTHER_CHAN_QTY, OTHER_CHAN_WHOLESALE_COST,
OTHER_CHAN_SALES_PRICE]
projection: [SS_CUSTOMER_SK, ROUND(/(SS_QTY, +(CASE(IS NOT NULL(WS_QTY),
CAST(WS_QTY):BIGINT NOT NULL, 0:BIGINT), CASE(IS NOT NULL(CS_QTY),
CAST(CS_QTY):BIGINT NOT NULL, 0:BIGINT))), 2), SS_QTY, SS_WC, SS_SP, +(CASE(IS
NOT NULL(WS_QTY), CAST(WS_QTY):BIGINT NOT NULL, 0:BIGINT), CASE(IS NOT
NULL(CS_QTY), CAST(CS_QTY):BIGINT NOT NULL, 0:BIGINT)), +(CASE(IS NOT
NULL(WS_WC), CAST(WS_WC):DECIMAL(14, 2) NOT NULL, 0.00:DECIMAL(14, 2)), CASE(IS
NOT NULL(CS_WC), CAST(CS_WC):DECIMAL(14, 2) NO [...]
- est: (rows=5169421725)
+ est: (rows=119648002)
Filter
predicate: OR(>(CAST(WS_QTY):BIGINT NOT NULL, 0),
>(CAST(CS_QTY):BIGINT NOT NULL, 0))
- est: (rows=5169421725)
+ est: (rows=119648002)
MergeJoin
predicate: AND(=(CS_SOLD_YEAR, SS_SOLD_YEAR), =(CS_ITEM_SK,
SS_ITEM_SK), =(CS_CUSTOMER_SK, SS_CUSTOMER_SK))
type: left
- est: (rows=20677686899)
+ est: (rows=478592006)
MergeJoin
predicate: AND(=(WS_SOLD_YEAR, SS_SOLD_YEAR), =(WS_ITEM_SK,
SS_ITEM_SK), =(WS_CUSTOMER_SK, SS_CUSTOMER_SK))
type: left
- est: (rows=34778483)
+ est: (rows=5315903)
Sort
collation: [SS_SOLD_YEAR ASC, SS_ITEM_SK ASC, SS_CUSTOMER_SK ASC]
est: (rows=117019)
@@ -64,41 +64,91 @@ Sort
est: (rows=24325)
Sort
collation: [WS_SOLD_YEAR ASC, WS_ITEM_SK ASC, WS_CUSTOMER_SK ASC]
- est: (rows=87765)
+ est: (rows=13165)
+ Filter
+ predicate: =(WS_SOLD_YEAR, 2001)
+ est: (rows=13165)
+ ColocatedHashAggregate
+ fieldNames: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK,
WS_QTY, WS_WC, WS_SP]
+ group: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK]
+ aggregation: [SUM(WS_QUANTITY), SUM(WS_WHOLESALE_COST),
SUM(WS_SALES_PRICE)]
+ est: (rows=87765)
+ Project
+ fieldNames: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK,
WS_QUANTITY, WS_WHOLESALE_COST, WS_SALES_PRICE]
+ projection: [D_YEAR, WS_ITEM_SK, WS_BILL_CUSTOMER_SK,
WS_QUANTITY, WS_WHOLESALE_COST, WS_SALES_PRICE]
+ est: (rows=179846)
+ HashJoin
+ predicate: =(WS_SOLD_DATE_SK, D_DATE_SK)
+ type: inner
+ est: (rows=179846)
+ Filter
+ predicate: IS NULL(WR_ORDER_NUMBER)
+ est: (rows=179846)
+ HashJoin
+ predicate: AND(=(WR_ORDER_NUMBER, WS_ORDER_NUMBER),
=(WS_ITEM_SK, WR_ITEM_SK))
+ type: left
+ est: (rows=719384)
+ Exchange
+ distribution: single
+ est: (rows=719384)
+ TableScan
+ table: PUBLIC.WEB_SALES
+ fieldNames: [WS_SOLD_DATE_SK, WS_ITEM_SK,
WS_BILL_CUSTOMER_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST,
WS_SALES_PRICE]
+ est: (rows=719384)
+ Exchange
+ distribution: single
+ est: (rows=71763)
+ TableScan
+ table: PUBLIC.WEB_RETURNS
+ fieldNames: [WR_ITEM_SK, WR_ORDER_NUMBER]
+ est: (rows=71763)
+ Exchange
+ distribution: single
+ est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fieldNames: [D_DATE_SK, D_YEAR]
+ est: (rows=73049)
+ Sort
+ collation: [CS_SOLD_YEAR ASC, CS_ITEM_SK ASC, CS_CUSTOMER_SK ASC]
+ est: (rows=26380)
+ Filter
+ predicate: =(CS_SOLD_YEAR, 2001)
+ est: (rows=26380)
ColocatedHashAggregate
- fieldNames: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK, WS_QTY,
WS_WC, WS_SP]
- group: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK]
- aggregation: [SUM(WS_QUANTITY), SUM(WS_WHOLESALE_COST),
SUM(WS_SALES_PRICE)]
- est: (rows=87765)
+ fieldNames: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK, CS_QTY,
CS_WC, CS_SP]
+ group: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK]
+ aggregation: [SUM(CS_QUANTITY), SUM(CS_WHOLESALE_COST),
SUM(CS_SALES_PRICE)]
+ est: (rows=175869)
Project
- fieldNames: [WS_SOLD_YEAR, WS_ITEM_SK, WS_CUSTOMER_SK,
WS_QUANTITY, WS_WHOLESALE_COST, WS_SALES_PRICE]
- projection: [D_YEAR, WS_ITEM_SK, WS_BILL_CUSTOMER_SK,
WS_QUANTITY, WS_WHOLESALE_COST, WS_SALES_PRICE]
- est: (rows=179846)
+ fieldNames: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK,
CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
+ projection: [D_YEAR, CS_ITEM_SK, CS_BILL_CUSTOMER_SK,
CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
+ est: (rows=360387)
HashJoin
- predicate: =(WS_SOLD_DATE_SK, D_DATE_SK)
+ predicate: =(CS_SOLD_DATE_SK, D_DATE_SK)
type: inner
- est: (rows=179846)
+ est: (rows=360387)
Filter
- predicate: IS NULL(WR_ORDER_NUMBER)
- est: (rows=179846)
+ predicate: IS NULL(CR_ORDER_NUMBER)
+ est: (rows=360387)
HashJoin
- predicate: AND(=(WR_ORDER_NUMBER, WS_ORDER_NUMBER),
=(WS_ITEM_SK, WR_ITEM_SK))
+ predicate: AND(=(CR_ORDER_NUMBER, CS_ORDER_NUMBER),
=(CS_ITEM_SK, CR_ITEM_SK))
type: left
- est: (rows=719384)
+ est: (rows=1441548)
Exchange
distribution: single
- est: (rows=719384)
+ est: (rows=1441548)
TableScan
- table: PUBLIC.WEB_SALES
- fieldNames: [WS_SOLD_DATE_SK, WS_ITEM_SK,
WS_BILL_CUSTOMER_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST,
WS_SALES_PRICE]
- est: (rows=719384)
+ table: PUBLIC.CATALOG_SALES
+ fieldNames: [CS_SOLD_DATE_SK, CS_BILL_CUSTOMER_SK,
CS_ITEM_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
+ est: (rows=1441548)
Exchange
distribution: single
- est: (rows=71763)
+ est: (rows=144067)
TableScan
- table: PUBLIC.WEB_RETURNS
- fieldNames: [WR_ITEM_SK, WR_ORDER_NUMBER]
- est: (rows=71763)
+ table: PUBLIC.CATALOG_RETURNS
+ fieldNames: [CR_ITEM_SK, CR_ORDER_NUMBER]
+ est: (rows=144067)
Exchange
distribution: single
est: (rows=73049)
@@ -106,47 +156,3 @@ Sort
table: PUBLIC.DATE_DIM
fieldNames: [D_DATE_SK, D_YEAR]
est: (rows=73049)
- Sort
- collation: [CS_SOLD_YEAR ASC, CS_ITEM_SK ASC, CS_CUSTOMER_SK ASC]
- est: (rows=175869)
- ColocatedHashAggregate
- fieldNames: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK, CS_QTY,
CS_WC, CS_SP]
- group: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK]
- aggregation: [SUM(CS_QUANTITY), SUM(CS_WHOLESALE_COST),
SUM(CS_SALES_PRICE)]
- est: (rows=175869)
- Project
- fieldNames: [CS_SOLD_YEAR, CS_ITEM_SK, CS_CUSTOMER_SK,
CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
- projection: [D_YEAR, CS_ITEM_SK, CS_BILL_CUSTOMER_SK,
CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
- est: (rows=360387)
- HashJoin
- predicate: =(CS_SOLD_DATE_SK, D_DATE_SK)
- type: inner
- est: (rows=360387)
- Filter
- predicate: IS NULL(CR_ORDER_NUMBER)
- est: (rows=360387)
- HashJoin
- predicate: AND(=(CR_ORDER_NUMBER, CS_ORDER_NUMBER),
=(CS_ITEM_SK, CR_ITEM_SK))
- type: left
- est: (rows=1441548)
- Exchange
- distribution: single
- est: (rows=1441548)
- TableScan
- table: PUBLIC.CATALOG_SALES
- fieldNames: [CS_SOLD_DATE_SK, CS_BILL_CUSTOMER_SK,
CS_ITEM_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_SALES_PRICE]
- est: (rows=1441548)
- Exchange
- distribution: single
- est: (rows=144067)
- TableScan
- table: PUBLIC.CATALOG_RETURNS
- fieldNames: [CR_ITEM_SK, CR_ORDER_NUMBER]
- est: (rows=144067)
- Exchange
- distribution: single
- est: (rows=73049)
- TableScan
- table: PUBLIC.DATE_DIM
- fieldNames: [D_DATE_SK, D_YEAR]
- est: (rows=73049)
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q85.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q85.plan
index 9894cda19ae..063f7a292da 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q85.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q85.plan
@@ -14,11 +14,11 @@ Sort
Project
fieldNames: [R_REASON_DESC, WS_QUANTITY, WR_REFUNDED_CASH, WR_FEE]
projection: [R_REASON_DESC, WS_QUANTITY, WR_REFUNDED_CASH, WR_FEE]
- est: (rows=2)
+ est: (rows=1)
HashJoin
predicate: =(WS_WEB_PAGE_SK, WP_WEB_PAGE_SK)
type: inner
- est: (rows=2)
+ est: (rows=1)
Exchange
distribution: single
est: (rows=60)
@@ -29,7 +29,7 @@ Sort
HashJoin
predicate: =(R_REASON_SK, WR_REASON_SK)
type: inner
- est: (rows=2)
+ est: (rows=1)
Exchange
distribution: single
est: (rows=35)
@@ -37,73 +37,71 @@ Sort
table: PUBLIC.REASON
fieldNames: [R_REASON_SK, R_REASON_DESC]
est: (rows=35)
- Exchange
- distribution: single
- est: (rows=2)
- HashJoin
- predicate: AND(=(CD_DEMO_SK, WR_RETURNING_CDEMO_SK),
=(CD_MARITAL_STATUS$0, CD_MARITAL_STATUS), =(CD_EDUCATION_STATUS$0,
CD_EDUCATION_STATUS), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'M',
_UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"), =(CD_MARITAL_STATUS$0,
CD_MARITAL_STATUS)), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'M',
_UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"), =(CD_EDUCATION_STATUS$0,
CD_EDUCATION_STATUS)), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'D',
_UTF-8'M']:C [...]
- fieldNames: [CD_DEMO_SK, CD_MARITAL_STATUS,
CD_EDUCATION_STATUS, D_DATE_SK, D_YEAR, CA_ADDRESS_SK, CA_STATE, CA_COUNTRY,
WS_SOLD_DATE_SK, WS_ITEM_SK, WS_WEB_PAGE_SK, WS_ORDER_NUMBER, WS_QUANTITY,
WS_SALES_PRICE, WS_NET_PROFIT, WR_ITEM_SK, WR_REFUNDED_CDEMO_SK,
WR_REFUNDED_ADDR_SK, WR_RETURNING_CDEMO_SK, WR_REASON_SK, WR_ORDER_NUMBER,
WR_FEE, WR_REFUNDED_CASH, CD_DEMO_SK$0, CD_MARITAL_STATUS$0,
CD_EDUCATION_STATUS$0]
- type: inner
- est: (rows=2)
+ HashJoin
+ predicate: AND(=(CD_DEMO_SK, WR_RETURNING_CDEMO_SK),
=(CD_MARITAL_STATUS$0, CD_MARITAL_STATUS), =(CD_EDUCATION_STATUS$0,
CD_EDUCATION_STATUS), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'M',
_UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"), =(CD_MARITAL_STATUS$0,
CD_MARITAL_STATUS)), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'M',
_UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"), =(CD_EDUCATION_STATUS$0,
CD_EDUCATION_STATUS)), OR(SEARCH(CD_MARITAL_STATUS$0, Sarg[_UTF-8'D',
_UTF-8'M']:CHA [...]
+ fieldNames: [CD_DEMO_SK, CD_MARITAL_STATUS,
CD_EDUCATION_STATUS, D_DATE_SK, D_YEAR, CA_ADDRESS_SK, CA_STATE, CA_COUNTRY,
WS_SOLD_DATE_SK, WS_ITEM_SK, WS_WEB_PAGE_SK, WS_ORDER_NUMBER, WS_QUANTITY,
WS_SALES_PRICE, WS_NET_PROFIT, WR_ITEM_SK, WR_REFUNDED_CDEMO_SK,
WR_REFUNDED_ADDR_SK, WR_RETURNING_CDEMO_SK, WR_REASON_SK, WR_ORDER_NUMBER,
WR_FEE, WR_REFUNDED_CASH, CD_DEMO_SK$0, CD_MARITAL_STATUS$0,
CD_EDUCATION_STATUS$0]
+ type: inner
+ est: (rows=1)
+ Exchange
+ distribution: single
+ est: (rows=37512)
TableScan
table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ predicate: AND(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'D',
_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'Secondary')),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'D', _UTF-8'M']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'College')), OR(=(CD_MARITAL_STATUS,
_UTF-8'M'), SEARCH(CD_EDUCATION_STATUS, Sarg[_UTF-8'College':VARCHAR(9) CHA
[...]
fieldNames: [CD_DEMO_SK, CD_MARITAL_STATUS,
CD_EDUCATION_STATUS]
- est: (rows=1920800)
+ est: (rows=37512)
+ HashJoin
+ predicate: =(WS_SOLD_DATE_SK, D_DATE_SK)
+ type: inner
+ est: (rows=3)
Exchange
- distribution: table PUBLIC.CUSTOMER_DEMOGRAPHICS in zone
"Default" by [WR_RETURNING_CDEMO_SK]
- est: (rows=3)
+ distribution: single
+ est: (rows=24325)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ predicate: =(D_YEAR, 2001)
+ fieldNames: [D_DATE_SK, D_YEAR]
+ est: (rows=24325)
+ HashJoin
+ predicate: AND(=(CA_ADDRESS_SK, WR_REFUNDED_ADDR_SK),
OR(=(CA_COUNTRY, _UTF-8'United States'), SEARCH(CAST(WS_NET_PROFIT):DECIMAL(12,
2), Sarg[[50.00:DECIMAL(12, 2)..250.00:DECIMAL(12, 2)]]:DECIMAL(12, 2))),
OR(=(CA_COUNTRY, _UTF-8'United States'), SEARCH(CA_STATE,
Sarg[_UTF-8'AR':VARCHAR(2) CHARACTER SET "UTF-8", _UTF-8'MO':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'NE':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCHAR(2)
CHARACTER SET "UTF-8"), SEARCH(CAST(WS_NET_PROFIT):DECI [...]
+ type: inner
+ est: (rows=10)
+ Exchange
+ distribution: single
+ est: (rows=12138)
+ TableScan
+ table: PUBLIC.CUSTOMER_ADDRESS
+ predicate: AND(=(CA_COUNTRY, _UTF-8'United States'),
OR(IS NOT NULL(CA_COUNTRY), SEARCH(CA_STATE, Sarg[_UTF-8'IA':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'MS':VARCHAR(2) CHARACTER SET "UTF-8",
_UTF-8'WA':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCHAR(2) CHARACTER SET
"UTF-8")), OR(IS NOT NULL(CA_COUNTRY), SEARCH(CA_STATE,
Sarg[_UTF-8'AR':VARCHAR(2) CHARACTER SET "UTF-8", _UTF-8'MO':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'NE':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCHAR(2
[...]
+ fieldNames: [CA_ADDRESS_SK, CA_STATE, CA_COUNTRY]
+ est: (rows=12138)
HashJoin
- predicate: =(WS_SOLD_DATE_SK, D_DATE_SK)
+ predicate: AND(=(WS_ITEM_SK, WR_ITEM_SK),
=(WS_ORDER_NUMBER, WR_ORDER_NUMBER), OR(SEARCH(CD_MARITAL_STATUS,
Sarg[_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"),
SEARCH(WS_SALES_PRICE, Sarg[[150.00:DECIMAL(5, 2)..200.00:DECIMAL(5,
2)]]:DECIMAL(5, 2))), OR(=(CD_MARITAL_STATUS, _UTF-8'M'),
=(CD_EDUCATION_STATUS, _UTF-8'College'), SEARCH(WS_SALES_PRICE,
Sarg[[150.00:DECIMAL(5, 2)..200.00:DECIMAL(5, 2)]]:DECIMAL(5, 2))),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'D', [...]
type: inner
- est: (rows=3)
+ est: (rows=61)
Exchange
distribution: single
- est: (rows=24325)
+ est: (rows=44962)
TableScan
- table: PUBLIC.DATE_DIM
- predicate: =(D_YEAR, 2001)
- fieldNames: [D_DATE_SK, D_YEAR]
- est: (rows=24325)
+ table: PUBLIC.WEB_SALES
+ predicate:
AND(SEARCH(CAST(WS_NET_PROFIT):DECIMAL(12, 2), Sarg[[50.00:DECIMAL(12,
2)..300.00:DECIMAL(12, 2)]]:DECIMAL(12, 2)), SEARCH(WS_SALES_PRICE,
Sarg[[50.00:DECIMAL(5, 2)..200.00:DECIMAL(5, 2)]]:DECIMAL(5, 2)))
+ fieldNames: [WS_SOLD_DATE_SK, WS_ITEM_SK,
WS_WEB_PAGE_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_SALES_PRICE, WS_NET_PROFIT]
+ est: (rows=44962)
HashJoin
- predicate: AND(=(CA_ADDRESS_SK, WR_REFUNDED_ADDR_SK),
OR(=(CA_COUNTRY, _UTF-8'United States'), SEARCH(CAST(WS_NET_PROFIT):DECIMAL(12,
2), Sarg[[50.00:DECIMAL(12, 2)..250.00:DECIMAL(12, 2)]]:DECIMAL(12, 2))),
OR(=(CA_COUNTRY, _UTF-8'United States'), SEARCH(CA_STATE,
Sarg[_UTF-8'AR':VARCHAR(2) CHARACTER SET "UTF-8", _UTF-8'MO':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'NE':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCHAR(2)
CHARACTER SET "UTF-8"), SEARCH(CAST(WS_NET_PROFIT): [...]
+ predicate: =(CD_DEMO_SK, WR_REFUNDED_CDEMO_SK)
type: inner
- est: (rows=10)
+ est: (rows=1401)
+ Exchange
+ distribution: single
+ est: (rows=71763)
+ TableScan
+ table: PUBLIC.WEB_RETURNS
+ fieldNames: [WR_ITEM_SK, WR_REFUNDED_CDEMO_SK,
WR_REFUNDED_ADDR_SK, WR_RETURNING_CDEMO_SK, WR_REASON_SK, WR_ORDER_NUMBER,
WR_FEE, WR_REFUNDED_CASH]
+ est: (rows=71763)
Exchange
distribution: single
- est: (rows=12138)
+ est: (rows=37512)
TableScan
- table: PUBLIC.CUSTOMER_ADDRESS
- predicate: AND(=(CA_COUNTRY, _UTF-8'United
States'), OR(IS NOT NULL(CA_COUNTRY), SEARCH(CA_STATE,
Sarg[_UTF-8'IA':VARCHAR(2) CHARACTER SET "UTF-8", _UTF-8'MS':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'WA':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCHAR(2)
CHARACTER SET "UTF-8")), OR(IS NOT NULL(CA_COUNTRY), SEARCH(CA_STATE,
Sarg[_UTF-8'AR':VARCHAR(2) CHARACTER SET "UTF-8", _UTF-8'MO':VARCHAR(2)
CHARACTER SET "UTF-8", _UTF-8'NE':VARCHAR(2) CHARACTER SET "UTF-8"]:VARCH [...]
- fieldNames: [CA_ADDRESS_SK, CA_STATE, CA_COUNTRY]
- est: (rows=12138)
- HashJoin
- predicate: AND(=(WS_ITEM_SK, WR_ITEM_SK),
=(WS_ORDER_NUMBER, WR_ORDER_NUMBER), OR(SEARCH(CD_MARITAL_STATUS,
Sarg[_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"),
SEARCH(WS_SALES_PRICE, Sarg[[150.00:DECIMAL(5, 2)..200.00:DECIMAL(5,
2)]]:DECIMAL(5, 2))), OR(=(CD_MARITAL_STATUS, _UTF-8'M'),
=(CD_EDUCATION_STATUS, _UTF-8'College'), SEARCH(WS_SALES_PRICE,
Sarg[[150.00:DECIMAL(5, 2)..200.00:DECIMAL(5, 2)]]:DECIMAL(5, 2))),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8 [...]
- type: inner
- est: (rows=61)
- Exchange
- distribution: single
- est: (rows=44962)
- TableScan
- table: PUBLIC.WEB_SALES
- predicate:
AND(SEARCH(CAST(WS_NET_PROFIT):DECIMAL(12, 2), Sarg[[50.00:DECIMAL(12,
2)..300.00:DECIMAL(12, 2)]]:DECIMAL(12, 2)), SEARCH(WS_SALES_PRICE,
Sarg[[50.00:DECIMAL(5, 2)..200.00:DECIMAL(5, 2)]]:DECIMAL(5, 2)))
- fieldNames: [WS_SOLD_DATE_SK, WS_ITEM_SK,
WS_WEB_PAGE_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_SALES_PRICE, WS_NET_PROFIT]
- est: (rows=44962)
- HashJoin
- predicate: =(CD_DEMO_SK, WR_REFUNDED_CDEMO_SK)
- type: inner
- est: (rows=1401)
- Exchange
- distribution: single
- est: (rows=71763)
- TableScan
- table: PUBLIC.WEB_RETURNS
- fieldNames: [WR_ITEM_SK, WR_REFUNDED_CDEMO_SK,
WR_REFUNDED_ADDR_SK, WR_RETURNING_CDEMO_SK, WR_REASON_SK, WR_ORDER_NUMBER,
WR_FEE, WR_REFUNDED_CASH]
- est: (rows=71763)
- Exchange
- distribution: single
- est: (rows=37512)
- TableScan
- table: PUBLIC.CUSTOMER_DEMOGRAPHICS
- predicate: AND(SEARCH(CD_MARITAL_STATUS,
Sarg[_UTF-8'D', _UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'Secondary')),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'D', _UTF-8'M']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'College')), OR(=(CD_MARITAL_STATUS,
_UTF-8'M'), SEARCH(CD_EDUCATION_STATUS, Sarg[_UTF-8'College':VA [...]
- fieldNames: [CD_DEMO_SK, CD_MARITAL_STATUS,
CD_EDUCATION_STATUS]
- est: (rows=37512)
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ predicate: AND(SEARCH(CD_MARITAL_STATUS,
Sarg[_UTF-8'D', _UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET "UTF-8"),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'M', _UTF-8'S']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'Secondary')),
OR(SEARCH(CD_MARITAL_STATUS, Sarg[_UTF-8'D', _UTF-8'M']:CHAR(1) CHARACTER SET
"UTF-8"), =(CD_EDUCATION_STATUS, _UTF-8'College')), OR(=(CD_MARITAL_STATUS,
_UTF-8'M'), SEARCH(CD_EDUCATION_STATUS, Sarg[_UTF-8'College':VARCHA [...]
+ fieldNames: [CD_DEMO_SK, CD_MARITAL_STATUS,
CD_EDUCATION_STATUS]
+ est: (rows=37512)
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q94.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q94.plan
index 23c790772d4..c0f8b989115 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q94.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q94.plan
@@ -4,56 +4,57 @@ Sort
est: (rows=100)
Project
projection: [order count, CAST(total shipping cost):DECIMAL(14, 2),
CAST(total net profit):DECIMAL(14, 2)]
- est: (rows=6738315409742)
+ est: (rows=6064483868767)
ColocatedSortAggregate
fieldNames: [order count, total shipping cost, total net profit]
collation: []
group: []
aggregation: [COUNT(WS_ORDER_NUMBER), SUM(total shipping cost),
SUM(total net profit)]
- est: (rows=6738315409742)
+ est: (rows=6064483868767)
ColocatedSortAggregate
fieldNames: [WS_ORDER_NUMBER, total shipping cost, total net profit]
collation: [WS_ORDER_NUMBER ASC]
group: [WS_ORDER_NUMBER]
aggregation: [SUM(WS_EXT_SHIP_COST), SUM(WS_NET_PROFIT)]
- est: (rows=6738315409742)
+ est: (rows=6064483868767)
Project
fieldNames: [WS_ORDER_NUMBER, WS_EXT_SHIP_COST, WS_NET_PROFIT]
projection: [WS_ORDER_NUMBER, WS_EXT_SHIP_COST, WS_NET_PROFIT]
- est: (rows=33691577048708)
+ est: (rows=30322419343837)
Filter
predicate: IS NULL($f1)
- est: (rows=33691577048708)
+ est: (rows=30322419343837)
MergeJoin
predicate: =(WS_ORDER_NUMBER, WR_ORDER_NUMBER)
type: left
- est: (rows=134766308194832)
+ est: (rows=121289677375349)
HashJoin
predicate: AND(=(WS_WAREHOUSE_SK, WS_WAREHOUSE_SK0),
=(WS_ORDER_NUMBER, WS_ORDER_NUMBER0))
type: inner
- est: (rows=62573155625)
+ est: (rows=56315840062)
HashJoin
predicate: =(WS_WEB_SITE_SK, WEB_SITE_SK)
type: inner
- est: (rows=19943)
+ est: (rows=17949)
HashJoin
predicate: =(WS_SHIP_ADDR_SK, CA_ADDRESS_SK)
type: inner
- est: (rows=59889)
+ est: (rows=53900)
HashJoin
predicate: =(WS_SHIP_DATE_SK, D_DATE_SK)
type: inner
- est: (rows=179846)
+ est: (rows=161861)
Exchange
distribution: single
- est: (rows=719384)
+ est: (rows=647446)
Sort
collation: [WS_ORDER_NUMBER ASC, WS_WAREHOUSE_SK
ASC]
- est: (rows=719384)
+ est: (rows=647446)
TableScan
table: PUBLIC.WEB_SALES
+ predicate: IS NOT NULL(WS_WAREHOUSE_SK)
fieldNames: [WS_SHIP_DATE_SK, WS_SHIP_ADDR_SK,
WS_WEB_SITE_SK, WS_WAREHOUSE_SK, WS_ORDER_NUMBER, WS_EXT_SHIP_COST,
WS_NET_PROFIT]
- est: (rows=719384)
+ est: (rows=647446)
Exchange
distribution: single
est: (rows=18262)
