This is an automated email from the ASF dual-hosted git repository.
sk0x50 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 565517c55dc IGNITE-25432 Fix SqlSchemaManagerImplTest and
TpcdsQueryPlannerTest when the colocation enabled (#5880)
565517c55dc is described below
commit 565517c55dc17684b229a27a2e7f7198ae127791
Author: Slava Koptilin <[email protected]>
AuthorDate: Thu May 22 15:58:11 2025 +0300
IGNITE-25432 Fix SqlSchemaManagerImplTest and TpcdsQueryPlannerTest when
the colocation enabled (#5880)
---
.../sql/engine/planner/TpcdsQueryPlannerTest.java | 6 +-
.../engine/schema/SqlSchemaManagerImplTest.java | 16 +-
.../test/resources/tpcds/plan/q64_colocated.plan | 467 +++++++++++++++++++++
3 files changed, 485 insertions(+), 4 deletions(-)
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/TpcdsQueryPlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/TpcdsQueryPlannerTest.java
index dcd4165f9ff..95e546839d3 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/TpcdsQueryPlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/TpcdsQueryPlannerTest.java
@@ -17,6 +17,7 @@
package org.apache.ignite.internal.sql.engine.planner;
+import static
org.apache.ignite.internal.lang.IgniteSystemProperties.enabledColocation;
import static
org.apache.ignite.internal.sql.engine.planner.AbstractTpcQueryPlannerTest.TpcSuiteInfo;
import org.apache.ignite.internal.sql.engine.util.tpcds.TpcdsHelper;
@@ -62,7 +63,10 @@ public class TpcdsQueryPlannerTest extends
AbstractTpcQueryPlannerTest {
var variantQueryFile =
String.format("tpcds/plan/variant_q%d.plan", numericId);
return loadFromResource(variantQueryFile);
} else {
- var queryFile = String.format("tpcds/plan/q%s.plan", numericId);
+ var queryFile = enabledColocation()
+ ? String.format("tpcds/plan/q%s_colocated.plan", numericId)
+ : String.format("tpcds/plan/q%s.plan", numericId);
+
return loadFromResource(queryFile);
}
}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/schema/SqlSchemaManagerImplTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/schema/SqlSchemaManagerImplTest.java
index a1506b62a5d..f3fa8f4f677 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/schema/SqlSchemaManagerImplTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/schema/SqlSchemaManagerImplTest.java
@@ -18,6 +18,7 @@
package org.apache.ignite.internal.sql.engine.schema;
import static
org.apache.ignite.internal.catalog.CatalogService.DEFAULT_STORAGE_PROFILE;
+import static
org.apache.ignite.internal.lang.IgniteSystemProperties.enabledColocation;
import static
org.apache.ignite.internal.sql.engine.util.TypeUtils.columnType2NativeType;
import static
org.apache.ignite.internal.testframework.IgniteTestUtils.assertThrowsWithCause;
import static org.apache.ignite.internal.testframework.IgniteTestUtils.await;
@@ -472,21 +473,30 @@ public class SqlSchemaManagerImplTest extends
BaseIgniteAbstractTest {
IgniteTable table = getTable(unwrapSchema(schemaPlus), "T1");
IgniteDistribution distribution =
table.descriptor().distribution();
- assertThat(distribution,
equalTo(IgniteDistributions.affinity(List.of(1), table.id(), table.id())));
+ assertThat(distribution, equalTo(IgniteDistributions.affinity(
+ List.of(1),
+ table.id(),
+ enabledColocation() ? table.zoneId() : table.id())));
}
{
IgniteTable table = getTable(unwrapSchema(schemaPlus), "T2");
IgniteDistribution distribution =
table.descriptor().distribution();
- assertThat(distribution,
equalTo(IgniteDistributions.affinity(List.of(3, 1), table.id(), table.id())));
+ assertThat(distribution, equalTo(IgniteDistributions.affinity(
+ List.of(3, 1),
+ table.id(),
+ enabledColocation() ? table.zoneId() : table.id())));
}
{
IgniteTable table = getTable(unwrapSchema(schemaPlus), "T3");
IgniteDistribution distribution =
table.descriptor().distribution();
- assertThat(distribution,
equalTo(IgniteDistributions.affinity(List.of(2, 1, 0), table.id(),
table.id())));
+ assertThat(distribution, equalTo(IgniteDistributions.affinity(
+ List.of(2, 1, 0),
+ table.id(),
+ enabledColocation() ? table.zoneId() : table.id())));
}
}
diff --git
a/modules/sql-engine/src/test/resources/tpcds/plan/q64_colocated.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q64_colocated.plan
new file mode 100644
index 00000000000..ab202c2da26
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q64_colocated.plan
@@ -0,0 +1,467 @@
+Sort
+ collation: [PRODUCT_NAME ASC, STORE_NAME ASC, CNT0 ASC, S11 ASC, S12 ASC]
+ est: (rows=21)
+ Project
+ fields: [PRODUCT_NAME, STORE_NAME, STORE_ZIP, B_STREET_NUMBER,
B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME, C_CITY, C_ZIP,
SYEAR, CNT, S11, S21, S31, S12, S22, S32, SYEAR0, CNT0]
+ exprs: [PRODUCT_NAME, STORE_NAME, STORE_ZIP, B_STREET_NUMBER,
B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME, C_CITY, C_ZIP,
SYEAR, CNT, S1, S2, S3, S10, S20, S30, SYEAR0, CNT0]
+ est: (rows=21)
+ HashJoin
+ condition: AND(=(ITEM_SK, ITEM_SK0), <=(CNT0, CNT), =(STORE_NAME,
STORE_NAME0), =(STORE_ZIP, STORE_ZIP0))
+ joinType: inner
+ est: (rows=21)
+ ColocatedHashAggregate
+ group: [PRODUCT_NAME, ITEM_SK, STORE_NAME, STORE_ZIP,
B_STREET_NUMBER, B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME,
C_CITY, C_ZIP, SYEAR, FSYEAR, S2YEAR]
+ aggs: [COUNT(), SUM(SS_WHOLESALE_COST), SUM(SS_LIST_PRICE),
SUM(SS_COUPON_AMT)]
+ est: (rows=2887)
+ Project
+ fields: [PRODUCT_NAME, ITEM_SK, STORE_NAME, STORE_ZIP,
B_STREET_NUMBER, B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME,
C_CITY, C_ZIP, SYEAR, FSYEAR, S2YEAR, SS_WHOLESALE_COST, SS_LIST_PRICE,
SS_COUPON_AMT]
+ exprs: [I_PRODUCT_NAME, I_ITEM_SK, S_STORE_NAME, S_ZIP,
CA_STREET_NUMBER, CA_STREET_NAME, CA_CITY, CA_ZIP, CA_STREET_NUMBER0,
CA_STREET_NAME0, CA_CITY0, CA_ZIP0, D_YEAR00, D_YEAR, D_YEAR0,
SS_WHOLESALE_COST, SS_LIST_PRICE, SS_COUPON_AMT]
+ est: (rows=2992)
+ HashJoin
+ condition: AND(=(SS_ITEM_SK, SR_ITEM_SK), =(SS_TICKET_NUMBER,
SR_TICKET_NUMBER))
+ joinType: inner
+ est: (rows=2992)
+ Exchange
+ distribution: single
+ est: (rows=287514)
+ TableScan
+ table: PUBLIC.STORE_RETURNS
+ fields: [SR_ITEM_SK, SR_TICKET_NUMBER]
+ est: (rows=287514)
+ HashJoin
+ condition: =(C_FIRST_SALES_DATE_SK, D_DATE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=73049)
+ HashJoin
+ condition: =(C_FIRST_SHIPTO_DATE_SK, D_DATE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=73049)
+ HashJoin
+ condition: =(SS_STORE_SK, S_STORE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_PROMO_SK, P_PROMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_ADDR_SK, CA_ADDRESS_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=50000)
+ TableScan
+ table: PUBLIC.CUSTOMER_ADDRESS
+ fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
+ est: (rows=50000)
+ HashJoin
+ condition: =(C_CURRENT_ADDR_SK, CA_ADDRESS_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=50000)
+ TableScan
+ table: PUBLIC.CUSTOMER_ADDRESS
+ fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
+ est: (rows=50000)
+ HashJoin
+ condition: =(HD_INCOME_BAND_SK, IB_INCOME_BAND_SK0)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_HDEMO_SK, HD_DEMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=7200)
+ TableScan
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
+ fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
+ est: (rows=7200)
+ HashJoin
+ condition: =(HD_INCOME_BAND_SK,
IB_INCOME_BAND_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(C_CURRENT_HDEMO_SK, HD_DEMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=7200)
+ TableScan
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
+ fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
+ est: (rows=7200)
+ HashJoin
+ condition: AND(<>(CD_MARITAL_STATUS,
CD_MARITAL_STATUS0), =(SS_CDEMO_SK, CD_DEMO_SK))
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=1920800)
+ TableScan
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ fields: [CD_DEMO_SK, CD_MARITAL_STATUS]
+ est: (rows=1920800)
+ HashJoin
+ condition: =(C_CURRENT_CDEMO_SK,
CD_DEMO_SK)
+ joinType: inner
+ est: (rows=129548810)
+ Exchange
+ distribution: single
+ est: (rows=1920800)
+ TableScan
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ fields: [CD_DEMO_SK,
CD_MARITAL_STATUS]
+ est: (rows=1920800)
+ HashJoin
+ condition: =(SS_CUSTOMER_SK,
C_CUSTOMER_SK)
+ joinType: inner
+ est: (rows=129548810)
+ Exchange
+ distribution: single
+ est: (rows=100000)
+ TableScan
+ table: PUBLIC.CUSTOMER
+ fields: [C_CUSTOMER_SK,
C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK]
+ est: (rows=100000)
+ HashJoin
+ condition: =(SS_SOLD_DATE_SK,
D_DATE_SK)
+ joinType: inner
+ est: (rows=129548810)
+ HashJoin
+ condition: =(SS_ITEM_SK,
CS_ITEM_SK)
+ joinType: inner
+ est: (rows=389035465)
+ HashJoin
+ condition: =(SS_ITEM_SK,
I_ITEM_SK)
+ joinType: inner
+ est: (rows=180025)
+ Exchange
+ distribution: single
+ est: (rows=2880404)
+ TableScan
+ table: PUBLIC.STORE_SALES
+ fields: [SS_SOLD_DATE_SK,
SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK,
SS_PROMO_SK, SS_TICKET_NUMBER, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_COUPON_AMT]
+ est: (rows=2880404)
+ Exchange
+ distribution: single
+ est: (rows=1125)
+ TableScan
+ table: PUBLIC.ITEM
+ filters: AND(SEARCH(I_COLOR,
Sarg[_UTF-8'azure':VARCHAR(20) CHARACTER SET "UTF-8", _UTF-8'blush':VARCHAR(20)
CHARACTER SET "UTF-8", _UTF-8'gainsboro':VARCHAR(20) CHARACTER SET "UTF-8",
_UTF-8'hot':VARCHAR(20) CHARACTER SET "UTF-8", _UTF-8'lemon':VARCHAR(20)
CHARACTER SET "UTF-8", _UTF-8'misty':VARCHAR(20) CHARACTER SET
"UTF-8"]:VARCHAR(20) CHARACTER SET "UTF-8"),
>=(CAST(I_CURRENT_PRICE):DECIMAL(12, 2), 80.00), <=(CAST(I_CURRENT_PRICE):DE
[...]
+ fields: [I_ITEM_SK,
I_CURRENT_PRICE, I_COLOR, I_PRODUCT_NAME]
+ est: (rows=1125)
+ Filter
+ condition: >(SALE, *(2, REFUND))
+ est: (rows=14407)
+ ReduceHashAggregate
+ rowType: RecordType(INTEGER
CS_ITEM_SK, DECIMAL(32767, 2) SALE, DECIMAL(32767, 2) REFUND)
+ group: [CS_ITEM_SK]
+ aggs: [SUM(_ACC0), SUM(_ACC1)]
+ est: (rows=28813)
+ Exchange
+ distribution: single
+ est: (rows=28813)
+ MapHashAggregate
+ group: [CS_ITEM_SK]
+ aggs:
[SUM(CS_EXT_LIST_PRICE), SUM($f2)]
+ est: (rows=28813)
+ Project
+ fields: [CS_ITEM_SK,
CS_EXT_LIST_PRICE, $f2]
+ exprs: [CS_ITEM_SK,
CS_EXT_LIST_PRICE, +(+(CR_REFUNDED_CASH, CR_REVERSED_CHARGE), CR_STORE_CREDIT)]
+ est: (rows=144067)
+ HashJoin
+ condition:
AND(=(CS_ITEM_SK, CR_ITEM_SK), =(CS_ORDER_NUMBER, CR_ORDER_NUMBER))
+ joinType: inner
+ est: (rows=144067)
+ TableScan
+ table:
PUBLIC.CATALOG_SALES
+ fields: [CS_ITEM_SK,
CS_ORDER_NUMBER, CS_EXT_LIST_PRICE]
+ est: (rows=1441548)
+ TableScan
+ table:
PUBLIC.CATALOG_RETURNS
+ fields: [CR_ITEM_SK,
CR_ORDER_NUMBER, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT]
+ est: (rows=144067)
+ Exchange
+ distribution: single
+ est: (rows=24325)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ filters: =(D_YEAR, 1999)
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=24325)
+ Exchange
+ distribution: single
+ est: (rows=20)
+ TableScan
+ table: PUBLIC.INCOME_BAND
+ fields: [IB_INCOME_BAND_SK]
+ est: (rows=20)
+ Exchange
+ distribution: single
+ est: (rows=20)
+ TableScan
+ table: PUBLIC.INCOME_BAND
+ fields: [IB_INCOME_BAND_SK]
+ est: (rows=20)
+ Exchange
+ distribution: single
+ est: (rows=300)
+ TableScan
+ table: PUBLIC.PROMOTION
+ fields: [P_PROMO_SK]
+ est: (rows=300)
+ Exchange
+ distribution: single
+ est: (rows=12)
+ TableScan
+ table: PUBLIC.STORE
+ fields: [S_STORE_SK, S_STORE_NAME, S_ZIP]
+ est: (rows=12)
+ ColocatedHashAggregate
+ group: [PRODUCT_NAME, ITEM_SK, STORE_NAME, STORE_ZIP,
B_STREET_NUMBER, B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME,
C_CITY, C_ZIP, SYEAR, FSYEAR, S2YEAR]
+ aggs: [COUNT(), SUM(SS_WHOLESALE_COST), SUM(SS_LIST_PRICE),
SUM(SS_COUPON_AMT)]
+ est: (rows=2887)
+ Project
+ fields: [PRODUCT_NAME, ITEM_SK, STORE_NAME, STORE_ZIP,
B_STREET_NUMBER, B_STREET_NAME, B_CITY, B_ZIP, C_STREET_NUMBER, C_STREET_NAME,
C_CITY, C_ZIP, SYEAR, FSYEAR, S2YEAR, SS_WHOLESALE_COST, SS_LIST_PRICE,
SS_COUPON_AMT]
+ exprs: [I_PRODUCT_NAME, I_ITEM_SK, S_STORE_NAME, S_ZIP,
CA_STREET_NUMBER, CA_STREET_NAME, CA_CITY, CA_ZIP, CA_STREET_NUMBER0,
CA_STREET_NAME0, CA_CITY0, CA_ZIP0, D_YEAR00, D_YEAR, D_YEAR0,
SS_WHOLESALE_COST, SS_LIST_PRICE, SS_COUPON_AMT]
+ est: (rows=2992)
+ HashJoin
+ condition: AND(=(SS_ITEM_SK, SR_ITEM_SK), =(SS_TICKET_NUMBER,
SR_TICKET_NUMBER))
+ joinType: inner
+ est: (rows=2992)
+ Exchange
+ distribution: single
+ est: (rows=287514)
+ TableScan
+ table: PUBLIC.STORE_RETURNS
+ fields: [SR_ITEM_SK, SR_TICKET_NUMBER]
+ est: (rows=287514)
+ HashJoin
+ condition: =(C_FIRST_SALES_DATE_SK, D_DATE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=73049)
+ HashJoin
+ condition: =(C_FIRST_SHIPTO_DATE_SK, D_DATE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=73049)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=73049)
+ HashJoin
+ condition: =(SS_STORE_SK, S_STORE_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_PROMO_SK, P_PROMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_ADDR_SK, CA_ADDRESS_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=50000)
+ TableScan
+ table: PUBLIC.CUSTOMER_ADDRESS
+ fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
+ est: (rows=50000)
+ HashJoin
+ condition: =(C_CURRENT_ADDR_SK, CA_ADDRESS_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=50000)
+ TableScan
+ table: PUBLIC.CUSTOMER_ADDRESS
+ fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
+ est: (rows=50000)
+ HashJoin
+ condition: =(HD_INCOME_BAND_SK, IB_INCOME_BAND_SK0)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(SS_HDEMO_SK, HD_DEMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=7200)
+ TableScan
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
+ fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
+ est: (rows=7200)
+ HashJoin
+ condition: =(HD_INCOME_BAND_SK,
IB_INCOME_BAND_SK)
+ joinType: inner
+ est: (rows=90684167)
+ HashJoin
+ condition: =(C_CURRENT_HDEMO_SK, HD_DEMO_SK)
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=7200)
+ TableScan
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
+ fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
+ est: (rows=7200)
+ HashJoin
+ condition: AND(<>(CD_MARITAL_STATUS,
CD_MARITAL_STATUS0), =(SS_CDEMO_SK, CD_DEMO_SK))
+ joinType: inner
+ est: (rows=90684167)
+ Exchange
+ distribution: single
+ est: (rows=1920800)
+ TableScan
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ fields: [CD_DEMO_SK, CD_MARITAL_STATUS]
+ est: (rows=1920800)
+ HashJoin
+ condition: =(C_CURRENT_CDEMO_SK,
CD_DEMO_SK)
+ joinType: inner
+ est: (rows=129548810)
+ Exchange
+ distribution: single
+ est: (rows=1920800)
+ TableScan
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
+ fields: [CD_DEMO_SK,
CD_MARITAL_STATUS]
+ est: (rows=1920800)
+ HashJoin
+ condition: =(SS_CUSTOMER_SK,
C_CUSTOMER_SK)
+ joinType: inner
+ est: (rows=129548810)
+ Exchange
+ distribution: single
+ est: (rows=100000)
+ TableScan
+ table: PUBLIC.CUSTOMER
+ fields: [C_CUSTOMER_SK,
C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK]
+ est: (rows=100000)
+ HashJoin
+ condition: =(SS_SOLD_DATE_SK,
D_DATE_SK)
+ joinType: inner
+ est: (rows=129548810)
+ HashJoin
+ condition: =(SS_ITEM_SK,
CS_ITEM_SK)
+ joinType: inner
+ est: (rows=389035465)
+ HashJoin
+ condition: =(SS_ITEM_SK,
I_ITEM_SK)
+ joinType: inner
+ est: (rows=180025)
+ Exchange
+ distribution: single
+ est: (rows=2880404)
+ TableScan
+ table: PUBLIC.STORE_SALES
+ fields: [SS_SOLD_DATE_SK,
SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK,
SS_PROMO_SK, SS_TICKET_NUMBER, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_COUPON_AMT]
+ est: (rows=2880404)
+ Exchange
+ distribution: single
+ est: (rows=1125)
+ TableScan
+ table: PUBLIC.ITEM
+ filters: AND(SEARCH(I_COLOR,
Sarg[_UTF-8'azure':VARCHAR(20) CHARACTER SET "UTF-8", _UTF-8'blush':VARCHAR(20)
CHARACTER SET "UTF-8", _UTF-8'gainsboro':VARCHAR(20) CHARACTER SET "UTF-8",
_UTF-8'hot':VARCHAR(20) CHARACTER SET "UTF-8", _UTF-8'lemon':VARCHAR(20)
CHARACTER SET "UTF-8", _UTF-8'misty':VARCHAR(20) CHARACTER SET
"UTF-8"]:VARCHAR(20) CHARACTER SET "UTF-8"),
>=(CAST(I_CURRENT_PRICE):DECIMAL(12, 2), 80.00), <=(CAST(I_CURRENT_PRICE):DE
[...]
+ fields: [I_ITEM_SK,
I_CURRENT_PRICE, I_COLOR, I_PRODUCT_NAME]
+ est: (rows=1125)
+ Filter
+ condition: >(SALE, *(2, REFUND))
+ est: (rows=14407)
+ ReduceHashAggregate
+ rowType: RecordType(INTEGER
CS_ITEM_SK, DECIMAL(32767, 2) SALE, DECIMAL(32767, 2) REFUND)
+ group: [CS_ITEM_SK]
+ aggs: [SUM(_ACC0), SUM(_ACC1)]
+ est: (rows=28813)
+ Exchange
+ distribution: single
+ est: (rows=28813)
+ MapHashAggregate
+ group: [CS_ITEM_SK]
+ aggs:
[SUM(CS_EXT_LIST_PRICE), SUM($f2)]
+ est: (rows=28813)
+ Project
+ fields: [CS_ITEM_SK,
CS_EXT_LIST_PRICE, $f2]
+ exprs: [CS_ITEM_SK,
CS_EXT_LIST_PRICE, +(+(CR_REFUNDED_CASH, CR_REVERSED_CHARGE), CR_STORE_CREDIT)]
+ est: (rows=144067)
+ HashJoin
+ condition:
AND(=(CS_ITEM_SK, CR_ITEM_SK), =(CS_ORDER_NUMBER, CR_ORDER_NUMBER))
+ joinType: inner
+ est: (rows=144067)
+ TableScan
+ table:
PUBLIC.CATALOG_SALES
+ fields: [CS_ITEM_SK,
CS_ORDER_NUMBER, CS_EXT_LIST_PRICE]
+ est: (rows=1441548)
+ TableScan
+ table:
PUBLIC.CATALOG_RETURNS
+ fields: [CR_ITEM_SK,
CR_ORDER_NUMBER, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT]
+ est: (rows=144067)
+ Exchange
+ distribution: single
+ est: (rows=24325)
+ TableScan
+ table: PUBLIC.DATE_DIM
+ filters: =(D_YEAR, +(1999, 1))
+ fields: [D_DATE_SK, D_YEAR]
+ est: (rows=24325)
+ Exchange
+ distribution: single
+ est: (rows=20)
+ TableScan
+ table: PUBLIC.INCOME_BAND
+ fields: [IB_INCOME_BAND_SK]
+ est: (rows=20)
+ Exchange
+ distribution: single
+ est: (rows=20)
+ TableScan
+ table: PUBLIC.INCOME_BAND
+ fields: [IB_INCOME_BAND_SK]
+ est: (rows=20)
+ Exchange
+ distribution: single
+ est: (rows=300)
+ TableScan
+ table: PUBLIC.PROMOTION
+ fields: [P_PROMO_SK]
+ est: (rows=300)
+ Exchange
+ distribution: single
+ est: (rows=12)
+ TableScan
+ table: PUBLIC.STORE
+ fields: [S_STORE_SK, S_STORE_NAME, S_ZIP]
+ est: (rows=12)
