This is an automated email from the ASF dual-hosted git repository.
korlov 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 9a1f2f8efad IGNITE-25435 Sql. Explain. Improve test coverage of scan
operators (#5859)
9a1f2f8efad is described below
commit 9a1f2f8efad8ee44d268f91d29a6fa77ec995a81
Author: korlov42 <[email protected]>
AuthorDate: Wed May 21 17:14:42 2025 +0300
IGNITE-25435 Sql. Explain. Improve test coverage of scan operators (#5859)
---
.../internal/sql/engine/ItSecondaryIndexTest.java | 2 +-
.../integrationTest/sql/group1/explain/scan.test | 274 ++++++++++++-----
.../sql/engine/prepare/ExplainRelAsTextWriter.java | 12 +-
.../internal/sql/engine/rel/IgniteIndexScan.java | 3 +-
.../engine/rel/ProjectableFilterableTableScan.java | 3 +-
.../src/test/resources/tpcds/plan/q64.plan | 330 ++++++++++-----------
.../src/test/resources/tpch/plan/q1.plan | 16 +-
.../src/test/resources/tpch/plan/q21.plan | 72 ++---
.../src/test/resources/tpch/plan/q5.plan | 52 ++--
.../src/test/resources/tpch/plan/q7.plan | 52 ++--
.../src/test/resources/tpch/plan/q8.plan | 70 ++---
.../src/test/resources/tpch/plan/q9.plan | 52 ++--
.../internal/sql/engine/util/QueryChecker.java | 15 +-
13 files changed, 552 insertions(+), 401 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
index 0c1d9c2692b..0982f6f1744 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItSecondaryIndexTest.java
@@ -197,7 +197,7 @@ public class ItSecondaryIndexTest extends
BaseSqlIntegrationTest {
@Test
public void testKeyEqualsFilter() {
assertQuery("SELECT * FROM Developer WHERE id=2")
- .matches(matchesOnce("KeyValueGet.*?table: \\[PUBLIC,
DEVELOPER\\]"))
+ .matches(matchesOnce("KeyValueGet.*?table:
PUBLIC\\.DEVELOPER"))
.returns(2, "Beethoven", 2, "Vienna", 44)
.check();
}
diff --git
a/modules/sql-engine/src/integrationTest/sql/group1/explain/scan.test
b/modules/sql-engine/src/integrationTest/sql/group1/explain/scan.test
index 6fd926f5352..31280b43e23 100644
--- a/modules/sql-engine/src/integrationTest/sql/group1/explain/scan.test
+++ b/modules/sql-engine/src/integrationTest/sql/group1/explain/scan.test
@@ -7,11 +7,11 @@ SELECT * FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [C1, C2, C3]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection, no renames
plan
@@ -19,11 +19,11 @@ SELECT c1, c2 FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [C1, C2]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection and renames
plan
@@ -31,11 +31,11 @@ SELECT c1 AS renamed_c1, c2 FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [RENAMED_C1, C2]
- est. row count: 1
+ est: (rows=1)
# select with expression projection, no renames
plan
@@ -43,12 +43,12 @@ SELECT c1 + c3, c2 FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [EXPR$0, C2]
projects: [+(C1, C3), C2]
- est. row count: 1
+ est: (rows=1)
# select with expression projection and renames
plan
@@ -56,12 +56,12 @@ SELECT c1 + c3 AS sum_of_c1_and_c3, c2 FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [SUM_OF_C1_AND_C3, C2]
projects: [+(C1, C3), C2]
- est. row count: 1
+ est: (rows=1)
# Similar set of tests but for IndexScan
statement ok
@@ -73,14 +73,14 @@ SELECT /*+ FORCE_INDEX(test_table_idx) */ * FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
IndexScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
index: TEST_TABLE_IDX
type: SORTED
fields: [C1, C2, C3]
collation: [C1 ASC, C2 ASC, C3 ASC]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection, no renames
plan
@@ -88,14 +88,14 @@ SELECT /*+ FORCE_INDEX(test_table_idx) */ c1, c2 FROM
test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
IndexScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
index: TEST_TABLE_IDX
type: SORTED
fields: [C1, C2]
collation: [C1 ASC, C2 ASC]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection and renames
plan
@@ -103,14 +103,14 @@ SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 AS
renamed_c1, c2 FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
IndexScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
index: TEST_TABLE_IDX
type: SORTED
fields: [RENAMED_C1, C2]
collation: [C1 ASC, C2 ASC]
- est. row count: 1
+ est: (rows=1)
# select with expression projection, no renames
plan
@@ -118,15 +118,15 @@ SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 + c3, c2
FROM test_table
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
IndexScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
index: TEST_TABLE_IDX
type: SORTED
fields: [EXPR$0, C2]
projects: [+(C1, C3), C2]
collation: []
- est. row count: 1
+ est: (rows=1)
# select with expression projection and renames
plan
@@ -134,15 +134,15 @@ SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 + c3 AS
sum_of_c1_and_c3, c2 FROM t
----
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
IndexScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
index: TEST_TABLE_IDX
type: SORTED
fields: [SUM_OF_C1_AND_C3, C2]
projects: [+(C1, C3), C2]
collation: []
- est. row count: 1
+ est: (rows=1)
# Similar set of tests but for KeyValueGet
# select all, no renames
@@ -150,52 +150,52 @@ plan
SELECT * FROM test_table WHERE c1 = 1
----
KeyValueGet
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [C1, C2, C3]
key: [1]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection, no renames
plan
SELECT c1, c2 FROM test_table WHERE c1 = 1
----
KeyValueGet
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [C1, C2]
key: [1]
- est. row count: 1
+ est: (rows=1)
# select with trimming projection and renames
plan
SELECT c1 AS renamed_c1, c2 FROM test_table WHERE c1 = 1
----
KeyValueGet
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [C1, C2]
key: [1]
- est. row count: 1
+ est: (rows=1)
# select with expression projection, no renames
plan
SELECT c1 + c3, c2 FROM test_table WHERE c1 = 1
----
KeyValueGet
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [EXPR$0, C2]
projects: [+(C1, C3), C2]
key: [1]
- est. row count: 1
+ est: (rows=1)
# select with expression projection and renames
plan
SELECT c1 + c3 AS sum_of_c1_and_c3, c2 FROM test_table WHERE c1 = 1
----
KeyValueGet
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [SUM_OF_C1_AND_C3, C2]
projects: [+(C1, C3), C2]
key: [1]
- est. row count: 1
+ est: (rows=1)
# Similar set of tests but for SystemViewScan
# select all, no renames
@@ -203,47 +203,47 @@ plan
SELECT * FROM system.system_views
----
SystemViewScan
- table: [SYSTEM, SYSTEM_VIEWS]
+ table: SYSTEM.SYSTEM_VIEWS
fields: [VIEW_ID, SCHEMA_NAME, VIEW_NAME, VIEW_TYPE, ID, SCHEMA, NAME,
TYPE]
- est. row count: 100
+ est: (rows=100)
# select with trimming projection, no renames
plan
SELECT view_id, view_name FROM system.system_views
----
SystemViewScan
- table: [SYSTEM, SYSTEM_VIEWS]
+ table: SYSTEM.SYSTEM_VIEWS
fields: [VIEW_ID, VIEW_NAME]
- est. row count: 100
+ est: (rows=100)
# select with trimming projection and renames
plan
SELECT view_id AS renamed_view_id, view_name FROM system.system_views
----
SystemViewScan
- table: [SYSTEM, SYSTEM_VIEWS]
+ table: SYSTEM.SYSTEM_VIEWS
fields: [RENAMED_VIEW_ID, VIEW_NAME]
- est. row count: 100
+ est: (rows=100)
# select with expression projection, no renames
plan
SELECT view_id + 5, view_name FROM system.system_views
----
SystemViewScan
- table: [SYSTEM, SYSTEM_VIEWS]
+ table: SYSTEM.SYSTEM_VIEWS
fields: [EXPR$0, VIEW_NAME]
projects: [+(VIEW_ID, 5), VIEW_NAME]
- est. row count: 100
+ est: (rows=100)
# select with expression projection and renames
plan
SELECT view_id + 5 AS adjusted_view_id, view_name FROM system.system_views
----
SystemViewScan
- table: [SYSTEM, SYSTEM_VIEWS]
+ table: SYSTEM.SYSTEM_VIEWS
fields: [ADJUSTED_VIEW_ID, VIEW_NAME]
projects: [+(VIEW_ID, 5), VIEW_NAME]
- est. row count: 100
+ est: (rows=100)
# Complex query where column of source table is renamed, and this renamed
column is referenced by
# correlated scalar query. This rename MUST remains in the plan to preserve
semantic of the query
@@ -258,31 +258,31 @@ SELECT
Project
fields: [EXPR$0]
exprs: [EXPR$0]
- est. row count: 1
+ est: (rows=1)
CorrelatedNestedLoopJoin
condition: true
joinType: left
variablesSet: [$cor0]
- est. row count: 1
+ est: (rows=1)
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [RENAMED_C1]
- est. row count: 1
+ est: (rows=1)
ColocatedHashAggregate
group: []
aggs: [COUNT()]
- est. row count: 1
+ est: (rows=1)
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
filters: =(C1, $cor0.RENAMED_C1)
fields: [C1]
- est. row count: 1
+ est: (rows=1)
# Complex query where expression involving a column of source table is
renamed, and this renamed expression is referenced by
# correlated scalar query. This rename MUST remains in the plan to preserve
semantic of the query
@@ -297,29 +297,167 @@ SELECT
Project
fields: [EXPR$0]
exprs: [EXPR$0]
- est. row count: 1
+ est: (rows=1)
CorrelatedNestedLoopJoin
condition: true
joinType: left
variablesSet: [$cor0]
- est. row count: 1
+ est: (rows=1)
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
fields: [DOUBLED_C1]
projects: [*(C1, 2)]
- est. row count: 1
+ est: (rows=1)
ColocatedHashAggregate
group: []
aggs: [COUNT()]
- est. row count: 1
+ est: (rows=1)
Exchange
distribution: single
- est. row count: 1
+ est: (rows=1)
TableScan
- table: [PUBLIC, TEST_TABLE]
+ table: PUBLIC.TEST_TABLE
filters: =(C1, $cor0.DOUBLED_C1)
fields: [C1]
- est. row count: 1
+ est: (rows=1)
+
+# table scan with simple predicate
+plan
+SELECT c1 FROM test_table WHERE c3 = 1
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ TableScan
+ table: PUBLIC.TEST_TABLE
+ filters: =(C3, 1)
+ fields: [C1]
+ projects: [C1]
+ est: (rows=1)
+
+# table scan with complex predicate
+plan
+SELECT c1 FROM test_table WHERE c3 IN (1, 2, 3) AND CURRENT_TIMESTAMP >
timestamp '1970-01-01'
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ TableScan
+ table: PUBLIC.TEST_TABLE
+ filters: AND(SEARCH(C3, Sarg[1, 2, 3]),
>(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
+ fields: [C1]
+ projects: [C1]
+ est: (rows=1)
+
+# index scan with simple predicate
+plan
+SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 FROM test_table WHERE c1 > 1
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ IndexScan
+ table: PUBLIC.TEST_TABLE
+ index: TEST_TABLE_IDX
+ type: SORTED
+ searchBounds: [RangeBounds [lowerBound=1, upperBound=null:INTEGER,
lowerInclude=false, upperInclude=false]]
+ filters: >(C1, 1)
+ fields: [C1]
+ collation: [C1 ASC]
+ est: (rows=1)
+
+# index scan with complex predicate
+plan
+SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 FROM test_table WHERE c1 IN (1,
2, 3) AND CURRENT_TIMESTAMP > timestamp '1970-01-01'
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ IndexScan
+ table: PUBLIC.TEST_TABLE
+ index: TEST_TABLE_IDX
+ type: SORTED
+ searchBounds: [MultiBounds [bounds=ArrayList [ExactBounds [bound=1],
ExactBounds [bound=2], ExactBounds [bound=3]]]]
+ filters: AND(SEARCH(C1, Sarg[1, 2, 3]),
>(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
+ fields: [C1]
+ collation: [C1 ASC]
+ est: (rows=1)
+
+statement ok
+CREATE INDEX hash_idx ON test_table USING HASH (c2);
+
+# look up by hash index
+plan
+SELECT /*+ FORCE_INDEX(hash_idx) */ c1 FROM test_table WHERE c2 = 10;
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ IndexScan
+ table: PUBLIC.TEST_TABLE
+ index: HASH_IDX
+ type: HASH
+ searchBounds: [ExactBounds [bound=10]]
+ filters: =(C2, 10)
+ fields: [C1]
+ projects: [C1]
+ est: (rows=1)
+
+statement ok
+CREATE INDEX test_table_different_collation_idx ON test_table USING SORTED (c2
DESC NULLS LAST);
+
+# index scan with different collation
+plan
+SELECT /*+ FORCE_INDEX(test_table_different_collation_idx) */ c1, c2 FROM
test_table
+----
+Exchange
+ distribution: single
+ est: (rows=1)
+ IndexScan
+ table: PUBLIC.TEST_TABLE
+ index: TEST_TABLE_DIFFERENT_COLLATION_IDX
+ type: SORTED
+ fields: [C1, C2]
+ collation: [C2 DESC NULLS LAST]
+ est: (rows=1)
+
+# system view scan with simple predicate
+plan
+SELECT view_id, view_name FROM system.system_views WHERE view_id = 5
+----
+SystemViewScan
+ table: SYSTEM.SYSTEM_VIEWS
+ filters: =(VIEW_ID, 5)
+ fields: [VIEW_ID, VIEW_NAME]
+ est: (rows=33)
+
+# table scan with complex predicate
+plan
+SELECT view_id, view_name FROM system.system_views WHERE view_id IN (1, 2, 3)
AND CURRENT_TIMESTAMP > timestamp '1970-01-01'
+----
+SystemViewScan
+ table: SYSTEM.SYSTEM_VIEWS
+ filters: AND(SEARCH(VIEW_ID, Sarg[1, 2, 3]),
>(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
+ fields: [VIEW_ID, VIEW_NAME]
+ est: (rows=38)
+
+# table function scan
+plan
+SELECT * FROM system_range(1, 10)
+----
+TableFunctionScan
+ invocation: SYSTEM_RANGE(1, 10)
+ rowType: RecordType(BIGINT X)
+ est: (rows=100)
+
+# another table function scan
+plan
+SELECT * FROM system_range(1, LENGTH(rand_uuid()::VARCHAR))
+----
+TableFunctionScan
+ invocation: SYSTEM_RANGE(1, LENGTH(CAST(RAND_UUID()):VARCHAR CHARACTER SET
"UTF-8" NOT NULL))
+ rowType: RecordType(BIGINT X)
+ est: (rows=100)
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/ExplainRelAsTextWriter.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/ExplainRelAsTextWriter.java
index 8bdf568a301..c114bfe73fb 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/ExplainRelAsTextWriter.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/ExplainRelAsTextWriter.java
@@ -27,6 +27,7 @@ import java.util.List;
import java.util.function.Function;
import java.util.stream.Collectors;
import org.apache.calcite.linq4j.Ord;
+import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.AggregateCall;
@@ -47,6 +48,7 @@ import org.apache.calcite.util.Pair;
import
org.apache.ignite.internal.sql.engine.rel.ProjectableFilterableTableScan;
import org.apache.ignite.internal.sql.engine.schema.IgniteDataSource;
import org.apache.ignite.internal.sql.engine.util.Commons;
+import org.apache.ignite.table.QualifiedNameHelper;
import org.jetbrains.annotations.Nullable;
/** Printer that dumps relation tree to a text representation for EXPLAIN
command output. */
@@ -88,7 +90,7 @@ public class ExplainRelAsTextWriter extends RelWriterImpl {
s.append(System.lineSeparator());
spacer.spaces(s);
- s.append("est. row count:
").append(BigDecimal.valueOf(mq.getRowCount(rel)).setScale(0,
RoundingMode.HALF_UP));
+ s.append("est:
(rows=").append(BigDecimal.valueOf(mq.getRowCount(rel)).setScale(0,
RoundingMode.HALF_UP)).append(')');
spacer.subtract(OPERATOR_ATTRIBUTES_INDENT);
@@ -104,6 +106,14 @@ public class ExplainRelAsTextWriter extends RelWriterImpl {
return null;
}
+ if (object instanceof RelOptTable) {
+ List<String> parts = ((RelOptTable) object).getQualifiedName();
+
+ assert parts.size() == 2 : parts;
+
+ return QualifiedNameHelper.fromNormalized(parts.get(0),
parts.get(1)).toCanonicalForm();
+ }
+
RelDataType rowTypeToResolveExpressions;
RelDataType rowTypeToResolveBitSet;
if (node instanceof ProjectableFilterableTableScan) {
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/IgniteIndexScan.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/IgniteIndexScan.java
index f0307ffe148..05969322c0d 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/IgniteIndexScan.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/IgniteIndexScan.java
@@ -32,6 +32,7 @@ import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.ignite.internal.sql.engine.prepare.bounds.SearchBounds;
import org.apache.ignite.internal.sql.engine.schema.IgniteIndex;
+import org.apache.ignite.internal.sql.engine.schema.IgniteIndex.Type;
import org.jetbrains.annotations.Nullable;
/**
@@ -147,7 +148,7 @@ public class IgniteIndexScan extends AbstractIndexScan
implements SourceAwareIgn
protected RelWriter explainTerms0(RelWriter pw) {
return super.explainTerms0(pw)
.itemIf("sourceId", sourceId, sourceId != -1)
- .item("collation", collation());
+ .itemIf("collation", collation(), type == Type.SORTED);
}
/** {@inheritDoc} */
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
index 647f00d88bf..9a348a01f29 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rel/ProjectableFilterableTableScan.java
@@ -131,7 +131,8 @@ public abstract class ProjectableFilterableTableScan
extends TableScan {
@Override
public RelWriter explainTerms(RelWriter pw) {
return explainTerms0(pw
- .item("table", table.getQualifiedName())
+ .itemIf("table", table.getQualifiedName(), !forExplain(pw))
+ .itemIf("table", table, forExplain(pw))
.itemIf("tableId",
Integer.toString(table.unwrap(IgniteDataSource.class).id()), !forExplain(pw)));
}
diff --git a/modules/sql-engine/src/test/resources/tpcds/plan/q64.plan
b/modules/sql-engine/src/test/resources/tpcds/plan/q64.plan
index af36ea22714..10da74cc295 100644
--- a/modules/sql-engine/src/test/resources/tpcds/plan/q64.plan
+++ b/modules/sql-engine/src/test/resources/tpcds/plan/q64.plan
@@ -1,473 +1,473 @@
Sort
collation: [PRODUCT_NAME ASC, STORE_NAME ASC, CNT0 ASC, S11 ASC, S12 ASC]
- est. row count: 21
+ 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. row count: 21
+ est: (rows=21)
HashJoin
condition: AND(=(ITEM_SK, ITEM_SK0), <=(CNT0, CNT), =(STORE_NAME,
STORE_NAME0), =(STORE_ZIP, STORE_ZIP0))
joinType: inner
- est. row count: 21
+ 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. row count: 2887
+ 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. row count: 2992
+ est: (rows=2992)
HashJoin
condition: AND(=(SS_ITEM_SK, SR_ITEM_SK), =(SS_TICKET_NUMBER,
SR_TICKET_NUMBER))
joinType: inner
- est. row count: 2992
+ est: (rows=2992)
Exchange
distribution: single
- est. row count: 287514
+ est: (rows=287514)
TableScan
- table: [PUBLIC, STORE_RETURNS]
+ table: PUBLIC.STORE_RETURNS
fields: [SR_ITEM_SK, SR_TICKET_NUMBER]
- est. row count: 287514
+ est: (rows=287514)
HashJoin
condition: =(C_FIRST_SALES_DATE_SK, D_DATE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 73049
+ est: (rows=73049)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
fields: [D_DATE_SK, D_YEAR]
- est. row count: 73049
+ est: (rows=73049)
HashJoin
condition: =(C_FIRST_SHIPTO_DATE_SK, D_DATE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 73049
+ est: (rows=73049)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
fields: [D_DATE_SK, D_YEAR]
- est. row count: 73049
+ est: (rows=73049)
HashJoin
condition: =(SS_STORE_SK, S_STORE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_PROMO_SK, P_PROMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_ADDR_SK, CA_ADDRESS_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 50000
+ est: (rows=50000)
TableScan
- table: [PUBLIC, CUSTOMER_ADDRESS]
+ table: PUBLIC.CUSTOMER_ADDRESS
fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
- est. row count: 50000
+ est: (rows=50000)
HashJoin
condition: =(C_CURRENT_ADDR_SK, CA_ADDRESS_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 50000
+ est: (rows=50000)
TableScan
- table: [PUBLIC, CUSTOMER_ADDRESS]
+ table: PUBLIC.CUSTOMER_ADDRESS
fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
- est. row count: 50000
+ est: (rows=50000)
HashJoin
condition: =(HD_INCOME_BAND_SK, IB_INCOME_BAND_SK0)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_HDEMO_SK, HD_DEMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 7200
+ est: (rows=7200)
TableScan
- table: [PUBLIC, HOUSEHOLD_DEMOGRAPHICS]
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
- est. row count: 7200
+ est: (rows=7200)
HashJoin
condition: =(HD_INCOME_BAND_SK,
IB_INCOME_BAND_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(C_CURRENT_HDEMO_SK, HD_DEMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 7200
+ est: (rows=7200)
TableScan
- table: [PUBLIC, HOUSEHOLD_DEMOGRAPHICS]
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
- est. row count: 7200
+ est: (rows=7200)
HashJoin
condition: AND(<>(CD_MARITAL_STATUS,
CD_MARITAL_STATUS0), =(SS_CDEMO_SK, CD_DEMO_SK))
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 1920800
+ est: (rows=1920800)
TableScan
- table: [PUBLIC, CUSTOMER_DEMOGRAPHICS]
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
fields: [CD_DEMO_SK, CD_MARITAL_STATUS]
- est. row count: 1920800
+ est: (rows=1920800)
HashJoin
condition: =(C_CURRENT_CDEMO_SK,
CD_DEMO_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
Exchange
distribution: single
- est. row count: 1920800
+ est: (rows=1920800)
TableScan
- table: [PUBLIC,
CUSTOMER_DEMOGRAPHICS]
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
fields: [CD_DEMO_SK,
CD_MARITAL_STATUS]
- est. row count: 1920800
+ est: (rows=1920800)
HashJoin
condition: =(SS_CUSTOMER_SK,
C_CUSTOMER_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
Exchange
distribution: single
- est. row count: 100000
+ est: (rows=100000)
TableScan
- table: [PUBLIC, CUSTOMER]
+ 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. row count: 100000
+ est: (rows=100000)
HashJoin
condition: =(SS_SOLD_DATE_SK,
D_DATE_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
HashJoin
condition: =(SS_ITEM_SK,
CS_ITEM_SK)
joinType: inner
- est. row count: 389035465
+ est: (rows=389035465)
HashJoin
condition: =(SS_ITEM_SK,
I_ITEM_SK)
joinType: inner
- est. row count: 180025
+ est: (rows=180025)
Exchange
distribution: single
- est. row count: 2880404
+ est: (rows=2880404)
TableScan
- table: [PUBLIC, STORE_SALES]
+ 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. row count: 2880404
+ est: (rows=2880404)
Exchange
distribution: single
- est. row count: 1125
+ est: (rows=1125)
TableScan
- table: [PUBLIC, ITEM]
+ 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. row count: 1125
+ est: (rows=1125)
Filter
condition: >(SALE, *(2, REFUND))
- est. row count: 14407
+ 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. row count: 28813
+ est: (rows=28813)
Exchange
distribution: single
- est. row count: 28813
+ est: (rows=28813)
MapHashAggregate
group: [CS_ITEM_SK]
aggs:
[SUM(CS_EXT_LIST_PRICE), SUM($f2)]
- est. row count: 28813
+ 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. row count: 144067
+ est: (rows=144067)
HashJoin
condition:
AND(=(CS_ITEM_SK, CR_ITEM_SK), =(CS_ORDER_NUMBER, CR_ORDER_NUMBER))
joinType: inner
- est. row count: 144067
+ est: (rows=144067)
TableScan
- table: [PUBLIC,
CATALOG_SALES]
+ table:
PUBLIC.CATALOG_SALES
fields: [CS_ITEM_SK,
CS_ORDER_NUMBER, CS_EXT_LIST_PRICE]
- est. row count:
1441548
+ est: (rows=1441548)
Exchange
distribution:
affinity[tableId=51, zoneId=51][0, 1]
- est. row count:
144067
+ est: (rows=144067)
TableScan
- table: [PUBLIC,
CATALOG_RETURNS]
+ table:
PUBLIC.CATALOG_RETURNS
fields:
[CR_ITEM_SK, CR_ORDER_NUMBER, CR_REFUNDED_CASH, CR_REVERSED_CHARGE,
CR_STORE_CREDIT]
- est. row count:
144067
+ est: (rows=144067)
Exchange
distribution: single
- est. row count: 24325
+ est: (rows=24325)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
filters: =(D_YEAR, 1999)
fields: [D_DATE_SK, D_YEAR]
- est. row count: 24325
+ est: (rows=24325)
Exchange
distribution: single
- est. row count: 20
+ est: (rows=20)
TableScan
- table: [PUBLIC, INCOME_BAND]
+ table: PUBLIC.INCOME_BAND
fields: [IB_INCOME_BAND_SK]
- est. row count: 20
+ est: (rows=20)
Exchange
distribution: single
- est. row count: 20
+ est: (rows=20)
TableScan
- table: [PUBLIC, INCOME_BAND]
+ table: PUBLIC.INCOME_BAND
fields: [IB_INCOME_BAND_SK]
- est. row count: 20
+ est: (rows=20)
Exchange
distribution: single
- est. row count: 300
+ est: (rows=300)
TableScan
- table: [PUBLIC, PROMOTION]
+ table: PUBLIC.PROMOTION
fields: [P_PROMO_SK]
- est. row count: 300
+ est: (rows=300)
Exchange
distribution: single
- est. row count: 12
+ est: (rows=12)
TableScan
- table: [PUBLIC, STORE]
+ table: PUBLIC.STORE
fields: [S_STORE_SK, S_STORE_NAME, S_ZIP]
- est. row count: 12
+ 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. row count: 2887
+ 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. row count: 2992
+ est: (rows=2992)
HashJoin
condition: AND(=(SS_ITEM_SK, SR_ITEM_SK), =(SS_TICKET_NUMBER,
SR_TICKET_NUMBER))
joinType: inner
- est. row count: 2992
+ est: (rows=2992)
Exchange
distribution: single
- est. row count: 287514
+ est: (rows=287514)
TableScan
- table: [PUBLIC, STORE_RETURNS]
+ table: PUBLIC.STORE_RETURNS
fields: [SR_ITEM_SK, SR_TICKET_NUMBER]
- est. row count: 287514
+ est: (rows=287514)
HashJoin
condition: =(C_FIRST_SALES_DATE_SK, D_DATE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 73049
+ est: (rows=73049)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
fields: [D_DATE_SK, D_YEAR]
- est. row count: 73049
+ est: (rows=73049)
HashJoin
condition: =(C_FIRST_SHIPTO_DATE_SK, D_DATE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 73049
+ est: (rows=73049)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
fields: [D_DATE_SK, D_YEAR]
- est. row count: 73049
+ est: (rows=73049)
HashJoin
condition: =(SS_STORE_SK, S_STORE_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_PROMO_SK, P_PROMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_ADDR_SK, CA_ADDRESS_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 50000
+ est: (rows=50000)
TableScan
- table: [PUBLIC, CUSTOMER_ADDRESS]
+ table: PUBLIC.CUSTOMER_ADDRESS
fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
- est. row count: 50000
+ est: (rows=50000)
HashJoin
condition: =(C_CURRENT_ADDR_SK, CA_ADDRESS_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 50000
+ est: (rows=50000)
TableScan
- table: [PUBLIC, CUSTOMER_ADDRESS]
+ table: PUBLIC.CUSTOMER_ADDRESS
fields: [CA_ADDRESS_SK, CA_STREET_NUMBER,
CA_STREET_NAME, CA_CITY, CA_ZIP]
- est. row count: 50000
+ est: (rows=50000)
HashJoin
condition: =(HD_INCOME_BAND_SK, IB_INCOME_BAND_SK0)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(SS_HDEMO_SK, HD_DEMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 7200
+ est: (rows=7200)
TableScan
- table: [PUBLIC, HOUSEHOLD_DEMOGRAPHICS]
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
- est. row count: 7200
+ est: (rows=7200)
HashJoin
condition: =(HD_INCOME_BAND_SK,
IB_INCOME_BAND_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
HashJoin
condition: =(C_CURRENT_HDEMO_SK, HD_DEMO_SK)
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 7200
+ est: (rows=7200)
TableScan
- table: [PUBLIC, HOUSEHOLD_DEMOGRAPHICS]
+ table: PUBLIC.HOUSEHOLD_DEMOGRAPHICS
fields: [HD_DEMO_SK, HD_INCOME_BAND_SK]
- est. row count: 7200
+ est: (rows=7200)
HashJoin
condition: AND(<>(CD_MARITAL_STATUS,
CD_MARITAL_STATUS0), =(SS_CDEMO_SK, CD_DEMO_SK))
joinType: inner
- est. row count: 90684167
+ est: (rows=90684167)
Exchange
distribution: single
- est. row count: 1920800
+ est: (rows=1920800)
TableScan
- table: [PUBLIC, CUSTOMER_DEMOGRAPHICS]
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
fields: [CD_DEMO_SK, CD_MARITAL_STATUS]
- est. row count: 1920800
+ est: (rows=1920800)
HashJoin
condition: =(C_CURRENT_CDEMO_SK,
CD_DEMO_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
Exchange
distribution: single
- est. row count: 1920800
+ est: (rows=1920800)
TableScan
- table: [PUBLIC,
CUSTOMER_DEMOGRAPHICS]
+ table: PUBLIC.CUSTOMER_DEMOGRAPHICS
fields: [CD_DEMO_SK,
CD_MARITAL_STATUS]
- est. row count: 1920800
+ est: (rows=1920800)
HashJoin
condition: =(SS_CUSTOMER_SK,
C_CUSTOMER_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
Exchange
distribution: single
- est. row count: 100000
+ est: (rows=100000)
TableScan
- table: [PUBLIC, CUSTOMER]
+ 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. row count: 100000
+ est: (rows=100000)
HashJoin
condition: =(SS_SOLD_DATE_SK,
D_DATE_SK)
joinType: inner
- est. row count: 129548810
+ est: (rows=129548810)
HashJoin
condition: =(SS_ITEM_SK,
CS_ITEM_SK)
joinType: inner
- est. row count: 389035465
+ est: (rows=389035465)
HashJoin
condition: =(SS_ITEM_SK,
I_ITEM_SK)
joinType: inner
- est. row count: 180025
+ est: (rows=180025)
Exchange
distribution: single
- est. row count: 2880404
+ est: (rows=2880404)
TableScan
- table: [PUBLIC, STORE_SALES]
+ 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. row count: 2880404
+ est: (rows=2880404)
Exchange
distribution: single
- est. row count: 1125
+ est: (rows=1125)
TableScan
- table: [PUBLIC, ITEM]
+ 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. row count: 1125
+ est: (rows=1125)
Filter
condition: >(SALE, *(2, REFUND))
- est. row count: 14407
+ 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. row count: 28813
+ est: (rows=28813)
Exchange
distribution: single
- est. row count: 28813
+ est: (rows=28813)
MapHashAggregate
group: [CS_ITEM_SK]
aggs:
[SUM(CS_EXT_LIST_PRICE), SUM($f2)]
- est. row count: 28813
+ 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. row count: 144067
+ est: (rows=144067)
HashJoin
condition:
AND(=(CS_ITEM_SK, CR_ITEM_SK), =(CS_ORDER_NUMBER, CR_ORDER_NUMBER))
joinType: inner
- est. row count: 144067
+ est: (rows=144067)
TableScan
- table: [PUBLIC,
CATALOG_SALES]
+ table:
PUBLIC.CATALOG_SALES
fields: [CS_ITEM_SK,
CS_ORDER_NUMBER, CS_EXT_LIST_PRICE]
- est. row count:
1441548
+ est: (rows=1441548)
Exchange
distribution:
affinity[tableId=51, zoneId=51][0, 1]
- est. row count:
144067
+ est: (rows=144067)
TableScan
- table: [PUBLIC,
CATALOG_RETURNS]
+ table:
PUBLIC.CATALOG_RETURNS
fields:
[CR_ITEM_SK, CR_ORDER_NUMBER, CR_REFUNDED_CASH, CR_REVERSED_CHARGE,
CR_STORE_CREDIT]
- est. row count:
144067
+ est: (rows=144067)
Exchange
distribution: single
- est. row count: 24325
+ est: (rows=24325)
TableScan
- table: [PUBLIC, DATE_DIM]
+ table: PUBLIC.DATE_DIM
filters: =(D_YEAR, +(1999, 1))
fields: [D_DATE_SK, D_YEAR]
- est. row count: 24325
+ est: (rows=24325)
Exchange
distribution: single
- est. row count: 20
+ est: (rows=20)
TableScan
- table: [PUBLIC, INCOME_BAND]
+ table: PUBLIC.INCOME_BAND
fields: [IB_INCOME_BAND_SK]
- est. row count: 20
+ est: (rows=20)
Exchange
distribution: single
- est. row count: 20
+ est: (rows=20)
TableScan
- table: [PUBLIC, INCOME_BAND]
+ table: PUBLIC.INCOME_BAND
fields: [IB_INCOME_BAND_SK]
- est. row count: 20
+ est: (rows=20)
Exchange
distribution: single
- est. row count: 300
+ est: (rows=300)
TableScan
- table: [PUBLIC, PROMOTION]
+ table: PUBLIC.PROMOTION
fields: [P_PROMO_SK]
- est. row count: 300
+ est: (rows=300)
Exchange
distribution: single
- est. row count: 12
+ est: (rows=12)
TableScan
- table: [PUBLIC, STORE]
+ table: PUBLIC.STORE
fields: [S_STORE_SK, S_STORE_NAME, S_ZIP]
- est. row count: 12
+ est: (rows=12)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q1.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q1.plan
index 3e9a9bc1fd0..3029e9a4a8d 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q1.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q1.plan
@@ -1,28 +1,28 @@
Sort
collation: [L_RETURNFLAG ASC, L_LINESTATUS ASC]
- est. row count: 1080219
+ est: (rows=1080219)
Project
fields: [L_RETURNFLAG, L_LINESTATUS, SUM_QTY, SUM_BASE_PRICE,
SUM_DISC_PRICE, SUM_CHARGE, AVG_QTY, AVG_PRICE, AVG_DISC, COUNT_ORDER]
exprs: [f0, f1, f0_2, f0_3, f0_4, f0_5, DECIMAL_DIVIDE(f0_6, f1_7, 29,
16), DECIMAL_DIVIDE(f0_8, f1_9, 29, 16), DECIMAL_DIVIDE(f0_10, f1_11, 29, 16),
CAST(f0_12):BIGINT NOT NULL]
- est. row count: 1080219
+ est: (rows=1080219)
ReduceHashAggregate
rowType: RecordType(VARCHAR(1) f0, VARCHAR(1) f1, DECIMAL(32767, 2)
f0_2, DECIMAL(32767, 2) f0_3, DECIMAL(32767, 4) f0_4, DECIMAL(32767, 6) f0_5,
DECIMAL(32767, 2) f0_6, DECIMAL(32767, 0) f1_7, DECIMAL(32767, 2) f0_8,
DECIMAL(32767, 0) f1_9, DECIMAL(32767, 2) f0_10, DECIMAL(32767, 0) f1_11,
BIGINT f0_12)
group: [0, 1]
aggs: [SUM(2), SUM(3), SUM(4), SUM(5), SUM(6), $SUM0(7), SUM(8),
$SUM0(9), SUM(10), $SUM0(11), $SUM0(12)]
- est. row count: 1080219
+ est: (rows=1080219)
Exchange
distribution: single
- est. row count: 1080219
+ est: (rows=1080219)
Project
fields: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]
exprs: [L_RETURNFLAG, L_LINESTATUS, _ACC0, _ACC1, _ACC2, _ACC3,
_ACC4, CAST(_ACC5):DECIMAL(32767, 0) NOT NULL, _ACC6,
CAST(_ACC7):DECIMAL(32767, 0) NOT NULL, _ACC8, CAST(_ACC9):DECIMAL(32767, 0)
NOT NULL, _ACC10, _GROUP_ID]
- est. row count: 1080219
+ est: (rows=1080219)
MapHashAggregate
group: [L_RETURNFLAG, L_LINESTATUS]
aggs: [SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), SUM($f4),
SUM($f5), SUM(L_QUANTITY), COUNT(L_QUANTITY), SUM(L_EXTENDEDPRICE),
COUNT(L_EXTENDEDPRICE), SUM(L_DISCOUNT), COUNT(L_DISCOUNT), COUNT()]
- est. row count: 1080219
+ est: (rows=1080219)
IndexScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
index: L_SD
type: SORTED
searchBounds: [RangeBounds [lowerBound=null,
upperBound=-(1998-12-01, 7776000000:INTERVAL DAY), lowerInclude=true,
upperInclude=true]]
@@ -30,4 +30,4 @@ Sort
fields: [L_RETURNFLAG, L_LINESTATUS, L_QUANTITY,
L_EXTENDEDPRICE, $f4, $f5, L_DISCOUNT]
projects: [L_RETURNFLAG, L_LINESTATUS, L_QUANTITY,
L_EXTENDEDPRICE, *(L_EXTENDEDPRICE, -(1, L_DISCOUNT)), *(*(L_EXTENDEDPRICE,
-(1, L_DISCOUNT)), +(1, L_TAX)), L_DISCOUNT]
collation: []
- est. row count: 3000608
+ est: (rows=3000608)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q21.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q21.plan
index 5b440ee5371..7acd4887340 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q21.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q21.plan
@@ -1,107 +1,107 @@
Limit
fetch: 100
- est. row count: 100
+ est: (rows=100)
Sort
collation: [NUMWAIT DESC, S_NAME ASC]
fetch: 100
- est. row count: 100
+ est: (rows=100)
ColocatedSortAggregate
group: [S_NAME]
aggs: [COUNT()]
collation: [S_NAME ASC]
- est. row count: 332203730726418
+ est: (rows=332203730726418)
Project
fields: [S_NAME]
exprs: [S_NAME]
- est. row count: 1661018653632089
+ est: (rows=1661018653632089)
Filter
condition: IS NULL(i)
- est. row count: 1661018653632089
+ est: (rows=1661018653632089)
CorrelatedNestedLoopJoin
condition: true
joinType: left
variablesSet: [$cor5]
- est. row count: 6644074614528357
+ est: (rows=6644074614528357)
CorrelatedNestedLoopJoin
condition: true
joinType: inner
variablesSet: [$cor6]
- est. row count: 66493788407
+ est: (rows=66493788407)
Sort
collation: [S_NAME ASC]
- est. row count: 332734
+ est: (rows=332734)
Project
fields: [S_NAME, L_ORDERKEY, L_SUPPKEY]
exprs: [S_NAME, L_ORDERKEY, L_SUPPKEY]
- est. row count: 332734
+ est: (rows=332734)
HashJoin
condition: =(O_ORDERKEY, L_ORDERKEY)
joinType: inner
- est. row count: 332734
+ est: (rows=332734)
HashJoin
condition: =(S_SUPPKEY, L_SUPPKEY)
joinType: inner
- est. row count: 999202
+ est: (rows=999202)
Exchange
distribution: single
- est. row count: 3000608
+ est: (rows=3000608)
TableScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
filters: >(L_RECEIPTDATE, L_COMMITDATE)
fields: [L_ORDERKEY, L_SUPPKEY, L_COMMITDATE,
L_RECEIPTDATE]
- est. row count: 3000608
+ est: (rows=3000608)
MergeJoin
condition: =(S_NATIONKEY, N_NATIONKEY)
joinType: inner
leftCollation: [S_NATIONKEY ASC]
rightCollation: [S_SUPPKEY ASC]
- est. row count: 3330
+ est: (rows=3330)
Exchange
distribution: single
- est. row count: 10000
+ est: (rows=10000)
IndexScan
- table: [PUBLIC, SUPPLIER]
+ table: PUBLIC.SUPPLIER
index: S_NK
type: SORTED
fields: [S_SUPPKEY, S_NAME, S_NATIONKEY]
collation: [S_NATIONKEY ASC]
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 8
+ est: (rows=8)
Sort
collation: [N_NATIONKEY ASC]
- est. row count: 8
+ est: (rows=8)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
filters: =(N_NAME, _UTF-8'SAUDI ARABIA')
fields: [N_NATIONKEY, N_NAME]
- est. row count: 8
+ est: (rows=8)
Exchange
distribution: single
- est. row count: 499500
+ est: (rows=499500)
TableScan
- table: [PUBLIC, ORDERS]
+ table: PUBLIC.ORDERS
filters: =(O_ORDERSTATUS, _UTF-8'F')
fields: [O_ORDERKEY, O_ORDERSTATUS]
- est. row count: 499500
+ est: (rows=499500)
Project
fields: []
exprs: []
- est. row count: 199840
+ est: (rows=199840)
ReduceHashAggregate
rowType: RecordType(BOOLEAN i)
group: [i]
- est. row count: 199840
+ est: (rows=199840)
Exchange
distribution: single
- est. row count: 199840
+ est: (rows=199840)
MapHashAggregate
group: [i]
aggs: []
- est. row count: 199840
+ est: (rows=199840)
IndexScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
index: L_OK
type: SORTED
searchBounds: [ExactBounds [bound=$cor6.L_ORDERKEY]]
@@ -109,20 +109,20 @@ Limit
fields: [i]
projects: [true]
collation: []
- est. row count: 999202
+ est: (rows=999202)
ReduceHashAggregate
rowType: RecordType(BOOLEAN i)
group: [i]
- est. row count: 99920
+ est: (rows=99920)
Exchange
distribution: single
- est. row count: 99920
+ est: (rows=99920)
MapHashAggregate
group: [i]
aggs: []
- est. row count: 99920
+ est: (rows=99920)
IndexScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
index: L_OK
type: SORTED
searchBounds: [ExactBounds [bound=$cor5.L_ORDERKEY]]
@@ -130,4 +130,4 @@ Limit
fields: [i]
projects: [true]
collation: []
- est. row count: 499601
+ est: (rows=499601)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q5.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q5.plan
index 799dfa255c9..60381a64442 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q5.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q5.plan
@@ -1,75 +1,75 @@
Sort
collation: [REVENUE DESC]
- est. row count: 69944
+ est: (rows=69944)
ColocatedHashAggregate
group: [N_NAME]
aggs: [SUM($f1)]
- est. row count: 69944
+ est: (rows=69944)
Project
fields: [N_NAME, $f1]
exprs: [N_NAME, *(L_EXTENDEDPRICE, -(1, L_DISCOUNT))]
- est. row count: 349721
+ est: (rows=349721)
HashJoin
condition: AND(=(C_NATIONKEY, S_NATIONKEY), =(L_SUPPKEY, S_SUPPKEY))
joinType: inner
- est. row count: 349721
+ est: (rows=349721)
HashJoin
condition: =(L_ORDERKEY, O_ORDERKEY)
joinType: inner
- est. row count: 1500304
+ est: (rows=1500304)
Exchange
distribution: single
- est. row count: 6001215
+ est: (rows=6001215)
TableScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
fields: [L_ORDERKEY, L_SUPPKEY, L_EXTENDEDPRICE, L_DISCOUNT]
- est. row count: 6001215
+ est: (rows=6001215)
HashJoin
condition: =(C_CUSTKEY, O_CUSTKEY)
joinType: inner
- est. row count: 375000
+ est: (rows=375000)
Exchange
distribution: single
- est. row count: 375000
+ est: (rows=375000)
TableScan
- table: [PUBLIC, ORDERS]
+ table: PUBLIC.ORDERS
filters: AND(>=(O_ORDERDATE, 1994-01-01), <(O_ORDERDATE,
+(1994-01-01, 12:INTERVAL YEAR)))
fields: [O_ORDERKEY, O_CUSTKEY, O_ORDERDATE]
- est. row count: 375000
+ est: (rows=375000)
Exchange
distribution: single
- est. row count: 150000
+ est: (rows=150000)
TableScan
- table: [PUBLIC, CUSTOMER]
+ table: PUBLIC.CUSTOMER
fields: [C_CUSTKEY, C_NATIONKEY]
- est. row count: 150000
+ est: (rows=150000)
HashJoin
condition: =(S_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 3330
+ est: (rows=3330)
Exchange
distribution: single
- est. row count: 10000
+ est: (rows=10000)
TableScan
- table: [PUBLIC, SUPPLIER]
+ table: PUBLIC.SUPPLIER
fields: [S_SUPPKEY, S_NATIONKEY]
- est. row count: 10000
+ est: (rows=10000)
HashJoin
condition: =(N_REGIONKEY, R_REGIONKEY)
joinType: inner
- est. row count: 8
+ est: (rows=8)
Exchange
distribution: single
- est. row count: 25
+ est: (rows=25)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
fields: [N_NATIONKEY, N_NAME, N_REGIONKEY]
- est. row count: 25
+ est: (rows=25)
Exchange
distribution: single
- est. row count: 2
+ est: (rows=2)
TableScan
- table: [PUBLIC, REGION]
+ table: PUBLIC.REGION
filters: =(R_NAME, _UTF-8'ASIA')
fields: [R_REGIONKEY, R_NAME]
- est. row count: 2
+ est: (rows=2)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q7.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q7.plan
index f4d5db42f0d..cefcfbc893b 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q7.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q7.plan
@@ -1,76 +1,76 @@
Sort
collation: [SUPP_NATION ASC, CUST_NATION ASC, L_YEAR ASC]
- est. row count: 165618
+ est: (rows=165618)
ColocatedHashAggregate
group: [SUPP_NATION, CUST_NATION, L_YEAR]
aggs: [SUM(VOLUME)]
- est. row count: 165618
+ est: (rows=165618)
Project
fields: [SUPP_NATION, CUST_NATION, L_YEAR, VOLUME]
exprs: [N_NAME, N_NAME0, EXTRACT(FLAG(YEAR), L_SHIPDATE),
*(L_EXTENDEDPRICE, -(1, L_DISCOUNT))]
- est. row count: 339381
+ est: (rows=339381)
HashJoin
condition: AND(=(O_ORDERKEY, L_ORDERKEY), OR(=(N_NAME,
_UTF-8'FRANCE'), =(N_NAME0, _UTF-8'FRANCE')), OR(=(N_NAME0, _UTF-8'GERMANY'),
=(N_NAME, _UTF-8'GERMANY')))
joinType: inner
- est. row count: 339381
+ est: (rows=339381)
HashJoin
condition: =(S_SUPPKEY, L_SUPPKEY)
joinType: inner
- est. row count: 852873
+ est: (rows=852873)
Exchange
distribution: single
- est. row count: 1500304
+ est: (rows=1500304)
TableScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
filters: SEARCH(L_SHIPDATE, Sarg[[1995-01-01..1996-12-31]])
fields: [L_ORDERKEY, L_SUPPKEY, L_EXTENDEDPRICE, L_DISCOUNT,
L_SHIPDATE]
- est. row count: 1500304
+ est: (rows=1500304)
HashJoin
condition: =(S_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 5685
+ est: (rows=5685)
Exchange
distribution: single
- est. row count: 10000
+ est: (rows=10000)
TableScan
- table: [PUBLIC, SUPPLIER]
+ table: PUBLIC.SUPPLIER
fields: [S_SUPPKEY, S_NATIONKEY]
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 14
+ est: (rows=14)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
filters: SEARCH(N_NAME, Sarg[_UTF-8'FRANCE':CHAR(7)
CHARACTER SET "UTF-8", _UTF-8'GERMANY']:CHAR(7) CHARACTER SET "UTF-8")
fields: [N_NATIONKEY, N_NAME]
- est. row count: 14
+ est: (rows=14)
HashJoin
condition: =(C_CUSTKEY, O_CUSTKEY)
joinType: inner
- est. row count: 852700
+ est: (rows=852700)
Exchange
distribution: single
- est. row count: 1500000
+ est: (rows=1500000)
TableScan
- table: [PUBLIC, ORDERS]
+ table: PUBLIC.ORDERS
fields: [O_ORDERKEY, O_CUSTKEY]
- est. row count: 1500000
+ est: (rows=1500000)
HashJoin
condition: =(C_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 85270
+ est: (rows=85270)
Exchange
distribution: single
- est. row count: 150000
+ est: (rows=150000)
TableScan
- table: [PUBLIC, CUSTOMER]
+ table: PUBLIC.CUSTOMER
fields: [C_CUSTKEY, C_NATIONKEY]
- est. row count: 150000
+ est: (rows=150000)
Exchange
distribution: single
- est. row count: 14
+ est: (rows=14)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
filters: SEARCH(N_NAME, Sarg[_UTF-8'FRANCE':CHAR(7)
CHARACTER SET "UTF-8", _UTF-8'GERMANY']:CHAR(7) CHARACTER SET "UTF-8")
fields: [N_NATIONKEY, N_NAME]
- est. row count: 14
+ est: (rows=14)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q8.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q8.plan
index 29d5503f6ea..465cbc5d37b 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q8.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q8.plan
@@ -1,102 +1,102 @@
Sort
collation: [O_YEAR ASC]
- est. row count: 16635
+ est: (rows=16635)
Project
fields: [O_YEAR, MKT_SHARE]
exprs: [O_YEAR, /($f1, $f2)]
- est. row count: 16635
+ est: (rows=16635)
ColocatedHashAggregate
group: [O_YEAR]
aggs: [SUM($f1), SUM(VOLUME)]
- est. row count: 16635
+ est: (rows=16635)
Project
fields: [O_YEAR, $f1, VOLUME]
exprs: [EXTRACT(FLAG(YEAR), O_ORDERDATE), CASE(=(N_NAME,
_UTF-8'BRAZIL'), *(L_EXTENDEDPRICE, -(1, L_DISCOUNT)), 0.0000:DECIMAL(31, 4)),
*(L_EXTENDEDPRICE, -(1, L_DISCOUNT))]
- est. row count: 166367
+ est: (rows=166367)
HashJoin
condition: =(S_SUPPKEY, L_SUPPKEY)
joinType: inner
- est. row count: 166367
+ est: (rows=166367)
HashJoin
condition: =(P_PARTKEY, L_PARTKEY)
joinType: inner
- est. row count: 166367
+ est: (rows=166367)
HashJoin
condition: =(L_ORDERKEY, O_ORDERKEY)
joinType: inner
- est. row count: 499601
+ est: (rows=499601)
Exchange
distribution: single
- est. row count: 6001215
+ est: (rows=6001215)
TableScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
fields: [L_ORDERKEY, L_PARTKEY, L_SUPPKEY,
L_EXTENDEDPRICE, L_DISCOUNT]
- est. row count: 6001215
+ est: (rows=6001215)
HashJoin
condition: =(O_CUSTKEY, C_CUSTKEY)
joinType: inner
- est. row count: 124875
+ est: (rows=124875)
Exchange
distribution: single
- est. row count: 375000
+ est: (rows=375000)
TableScan
- table: [PUBLIC, ORDERS]
+ table: PUBLIC.ORDERS
filters: SEARCH(O_ORDERDATE,
Sarg[[1995-01-01..1996-12-31]])
fields: [O_ORDERKEY, O_CUSTKEY, O_ORDERDATE]
- est. row count: 375000
+ est: (rows=375000)
HashJoin
condition: =(C_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 49950
+ est: (rows=49950)
Exchange
distribution: single
- est. row count: 150000
+ est: (rows=150000)
TableScan
- table: [PUBLIC, CUSTOMER]
+ table: PUBLIC.CUSTOMER
fields: [C_CUSTKEY, C_NATIONKEY]
- est. row count: 150000
+ est: (rows=150000)
HashJoin
condition: =(N_REGIONKEY, R_REGIONKEY)
joinType: inner
- est. row count: 8
+ est: (rows=8)
Exchange
distribution: single
- est. row count: 25
+ est: (rows=25)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
fields: [N_NATIONKEY, N_REGIONKEY]
- est. row count: 25
+ est: (rows=25)
Exchange
distribution: single
- est. row count: 2
+ est: (rows=2)
TableScan
- table: [PUBLIC, REGION]
+ table: PUBLIC.REGION
filters: =(R_NAME, _UTF-8'AMERICA')
fields: [R_REGIONKEY, R_NAME]
- est. row count: 2
+ est: (rows=2)
Exchange
distribution: single
- est. row count: 66600
+ est: (rows=66600)
TableScan
- table: [PUBLIC, PART]
+ table: PUBLIC.PART
filters: =(P_TYPE, _UTF-8'ECONOMY ANODIZED STEEL')
fields: [P_PARTKEY, P_TYPE]
- est. row count: 66600
+ est: (rows=66600)
HashJoin
condition: =(S_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 10000
+ est: (rows=10000)
TableScan
- table: [PUBLIC, SUPPLIER]
+ table: PUBLIC.SUPPLIER
fields: [S_SUPPKEY, S_NATIONKEY]
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 25
+ est: (rows=25)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
fields: [N_NATIONKEY, N_NAME]
- est. row count: 25
+ est: (rows=25)
diff --git a/modules/sql-engine/src/test/resources/tpch/plan/q9.plan
b/modules/sql-engine/src/test/resources/tpch/plan/q9.plan
index 325a1aa0288..56afeb20124 100644
--- a/modules/sql-engine/src/test/resources/tpch/plan/q9.plan
+++ b/modules/sql-engine/src/test/resources/tpch/plan/q9.plan
@@ -1,74 +1,74 @@
Sort
collation: [NATION ASC, O_YEAR DESC]
- est. row count: 540109
+ est: (rows=540109)
ColocatedHashAggregate
group: [NATION, O_YEAR]
aggs: [SUM(AMOUNT)]
- est. row count: 540109
+ est: (rows=540109)
Project
fields: [NATION, O_YEAR, AMOUNT]
exprs: [N_NAME, EXTRACT(FLAG(YEAR), O_ORDERDATE), -(*(L_EXTENDEDPRICE,
-(1, L_DISCOUNT)), *(PS_SUPPLYCOST, L_QUANTITY))]
- est. row count: 1500304
+ est: (rows=1500304)
HashJoin
condition: AND(=(PS_SUPPKEY, L_SUPPKEY), =(PS_PARTKEY, L_PARTKEY))
joinType: inner
- est. row count: 1500304
+ est: (rows=1500304)
HashJoin
condition: =(O_ORDERKEY, L_ORDERKEY)
joinType: inner
- est. row count: 1500304
+ est: (rows=1500304)
HashJoin
condition: =(S_SUPPKEY, L_SUPPKEY)
joinType: inner
- est. row count: 1500304
+ est: (rows=1500304)
HashJoin
condition: =(P_PARTKEY, L_PARTKEY)
joinType: inner
- est. row count: 1500304
+ est: (rows=1500304)
Exchange
distribution: single
- est. row count: 6001215
+ est: (rows=6001215)
TableScan
- table: [PUBLIC, LINEITEM]
+ table: PUBLIC.LINEITEM
fields: [L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_QUANTITY,
L_EXTENDEDPRICE, L_DISCOUNT]
- est. row count: 6001215
+ est: (rows=6001215)
Exchange
distribution: single
- est. row count: 50000
+ est: (rows=50000)
TableScan
- table: [PUBLIC, PART]
+ table: PUBLIC.PART
filters: LIKE(P_NAME, _UTF-8'%green%')
fields: [P_PARTKEY, P_NAME]
- est. row count: 50000
+ est: (rows=50000)
HashJoin
condition: =(S_NATIONKEY, N_NATIONKEY)
joinType: inner
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 10000
+ est: (rows=10000)
TableScan
- table: [PUBLIC, SUPPLIER]
+ table: PUBLIC.SUPPLIER
fields: [S_SUPPKEY, S_NATIONKEY]
- est. row count: 10000
+ est: (rows=10000)
Exchange
distribution: single
- est. row count: 25
+ est: (rows=25)
TableScan
- table: [PUBLIC, NATION]
+ table: PUBLIC.NATION
fields: [N_NATIONKEY, N_NAME]
- est. row count: 25
+ est: (rows=25)
Exchange
distribution: single
- est. row count: 1500000
+ est: (rows=1500000)
TableScan
- table: [PUBLIC, ORDERS]
+ table: PUBLIC.ORDERS
fields: [O_ORDERKEY, O_ORDERDATE]
- est. row count: 1500000
+ est: (rows=1500000)
Exchange
distribution: single
- est. row count: 800000
+ est: (rows=800000)
TableScan
- table: [PUBLIC, PARTSUPP]
+ table: PUBLIC.PARTSUPP
fields: [PS_PARTKEY, PS_SUPPKEY, PS_SUPPLYCOST]
- est. row count: 800000
+ est: (rows=800000)
diff --git
a/modules/sql-engine/src/testFixtures/java/org/apache/ignite/internal/sql/engine/util/QueryChecker.java
b/modules/sql-engine/src/testFixtures/java/org/apache/ignite/internal/sql/engine/util/QueryChecker.java
index 740b36be8f8..8ab541e5b17 100644
---
a/modules/sql-engine/src/testFixtures/java/org/apache/ignite/internal/sql/engine/util/QueryChecker.java
+++
b/modules/sql-engine/src/testFixtures/java/org/apache/ignite/internal/sql/engine/util/QueryChecker.java
@@ -34,6 +34,7 @@ import java.util.Objects;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import org.apache.ignite.internal.lang.IgniteStringBuilder;
+import org.apache.ignite.table.QualifiedName;
import org.hamcrest.BaseMatcher;
import org.hamcrest.Description;
import org.hamcrest.Matcher;
@@ -61,7 +62,7 @@ public interface QueryChecker {
* @return Matcher.
*/
static Matcher<String> containsTableScan(String schema, String tblName) {
- return matchesOnce("TableScan.*?table: \\[" + schema + ", " + tblName
+ "\\]");
+ return matchesOnce("TableScan.*?table: " + QualifiedName.of(schema,
tblName).toCanonicalForm());
}
/**
@@ -73,7 +74,7 @@ public interface QueryChecker {
*/
static Matcher<String> nodeRowCount(String nodePattern, Matcher<Integer>
rowCountMatcher) {
Pattern pattern = Pattern.compile(".*" + nodePattern
- + ".*?est\\. row count: (?<rowcount>\\d+).*");
+ + ".*?est: \\(rows=(?<rowcount>\\d+).*");
return new BaseMatcher<>() {
@Override
@@ -110,7 +111,7 @@ public interface QueryChecker {
* @return Matcher.
*/
static Matcher<String> containsIndexScan(String schema, String tblName) {
- return matchesOnce("IndexScan.*?table: \\[" + schema + ", " + tblName
+ "\\]");
+ return matchesOnce("IndexScan.*?table: " + QualifiedName.of(schema,
tblName).toCanonicalForm());
}
/**
@@ -122,7 +123,7 @@ public interface QueryChecker {
* @return Matcher.
*/
static Matcher<String> containsIndexScan(String schema, String tblName,
String idxName) {
- return matchesOnce("IndexScan.*?table: \\[" + schema + ", " + tblName
+ "\\]"
+ return matchesOnce("IndexScan.*?table: " + QualifiedName.of(schema,
tblName).toCanonicalForm()
+ ".*?index: " + idxName);
}
@@ -135,8 +136,8 @@ public interface QueryChecker {
* @return Matcher.
*/
static Matcher<String> containsProject(String schema, String tblName,
String... names) {
- return matchesOnce("(Table|Index)Scan.*?table: \\[" + schema + ", "
- + tblName + "\\].*?fields: \\[" + String.join(", ",
List.of(names)) + "\\]");
+ return matchesOnce("(Table|Index)Scan.*?table: " +
QualifiedName.of(schema, tblName).toCanonicalForm()
+ + ".*?fields: \\[" + String.join(", ", List.of(names)) +
"\\]");
}
/**
@@ -276,7 +277,7 @@ public interface QueryChecker {
*/
static Matcher<String> containsAnyScan(String schema, String tblName,
String... idxNames) {
if (nullOrEmpty(idxNames)) {
- return matchesOnce("(Table|Index)Scan.*?table: \\[" + schema + ",
" + tblName + "\\]");
+ return matchesOnce("(Table|Index)Scan.*?table: " +
QualifiedName.of(schema, tblName).toCanonicalForm());
}
return anyOf(
