You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ta...@apache.org on 2020/07/14 03:13:36 UTC
[impala] 01/02: IMPALA-9898: generate grouping set plans
This is an automated email from the ASF dual-hosted git repository.
tarmstrong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
commit 3e1e7da229913ac1651ea908e8817d6e41adf8b0
Author: Tim Armstrong <ta...@cloudera.com>
AuthorDate: Tue Jun 30 09:51:05 2020 -0700
IMPALA-9898: generate grouping set plans
Integrates the parsing and analysis with plan generation.
Testing:
* Add analysis test to make sure we reject unsupported queries.
* Added targeted planner tests to ensure we generate the correct
aggregation classes for a variety of cases.
* Add targeted end-to-end functional tests.
Added five TPC-DS queries that use ROLLUP, building on some work done
by Fang-Yu Rao. Some tweaks were required for these tests.
* Add an extra ORDER BY clause to q77 to make fully deterministic.
* Add backticks around `returns` to avoid reserved word.
* Add INTERVAL keyword to date/timestamp arithmetic.
We can run q80, too, but I haven't added or verified results yet -
that can be done in a follow-up.
Change-Id: Ie454c5bf7aee266321dee615548d7f2b71380197
Reviewed-on: http://gerrit.cloudera.org:8080/16128
Reviewed-by: Tim Armstrong <ta...@cloudera.com>
Tested-by: Tim Armstrong <ta...@cloudera.com>
---
.../main/java/org/apache/impala/analysis/Expr.java | 21 +
.../org/apache/impala/analysis/GroupByClause.java | 35 +-
.../apache/impala/analysis/MultiAggregateInfo.java | 58 +-
.../org/apache/impala/analysis/SelectStmt.java | 12 +-
.../java/org/apache/impala/common/RuntimeEnv.java | 11 -
.../apache/impala/planner/SingleNodePlanner.java | 32 +-
.../apache/impala/analysis/AnalyzeStmtsTest.java | 35 +-
.../org/apache/impala/analysis/ParserTest.java | 25 +
.../java/org/apache/impala/analysis/ToSqlTest.java | 4 -
.../org/apache/impala/planner/PlannerTest.java | 5 +
.../queries/PlannerTest/grouping-sets.test | 1197 +++++++++++++
.../queries/PlannerTest/subquery-rewrite.test | 175 ++
.../queries/PlannerTest/tpcds-all.test | 1769 ++++++++++++++++++++
.../queries/QueryTest/grouping-sets.test | 155 ++
.../tpcds/queries/tpcds-decimal_v2-q18.test | 137 ++
.../tpcds/queries/tpcds-decimal_v2-q22.test | 125 ++
.../tpcds/queries/tpcds-decimal_v2-q5.test | 231 +++
.../tpcds/queries/tpcds-decimal_v2-q67.test | 147 ++
.../tpcds/queries/tpcds-decimal_v2-q77.test | 155 ++
tests/query_test/test_aggregation.py | 6 +
tests/query_test/test_tpcds_queries.py | 15 +
tests/util/parse_util.py | 2 +-
22 files changed, 4287 insertions(+), 65 deletions(-)
diff --git a/fe/src/main/java/org/apache/impala/analysis/Expr.java b/fe/src/main/java/org/apache/impala/analysis/Expr.java
index a73eeb4..b110515 100644
--- a/fe/src/main/java/org/apache/impala/analysis/Expr.java
+++ b/fe/src/main/java/org/apache/impala/analysis/Expr.java
@@ -1121,6 +1121,27 @@ abstract public class Expr extends TreeNode<Expr> implements ParseNode, Cloneabl
}
/**
+ * Create a deep copy of 'ls'. The elements of the returned list are of the same
+ * type as the input list.
+ */
+ public static <C extends Expr> List<List<C>> deepCopy(List<List<C>> ls) {
+ Preconditions.checkNotNull(ls);
+ List<List<C>> result = new ArrayList<>(ls.size());
+ for (List<C> l : ls) {
+ if (l == null) {
+ result.add(null);
+ continue;
+ }
+ List<C> l2 = new ArrayList<>(l.size());
+ for (Expr element : l) {
+ l2.add((C) element.clone());
+ }
+ result.add(l2);
+ }
+ return result;
+ }
+
+ /**
* Removes duplicate exprs (according to equals()).
*/
public static <C extends Expr> void removeDuplicates(List<C> l) {
diff --git a/fe/src/main/java/org/apache/impala/analysis/GroupByClause.java b/fe/src/main/java/org/apache/impala/analysis/GroupByClause.java
index d0276f3..2a663d8 100644
--- a/fe/src/main/java/org/apache/impala/analysis/GroupByClause.java
+++ b/fe/src/main/java/org/apache/impala/analysis/GroupByClause.java
@@ -51,6 +51,10 @@ public class GroupByClause {
// clause. Non-null iff groupingSetsType is SETS.
private final List<List<Integer>> groupingSetsList_;
+ /////////////////////////////////////////
+ // BEGIN: Members that need to be reset()
+ boolean isAnalyzed_ = false;
+
// ID of each of the distinct grouping sets. Each ID is a bitfield with a bit set if
// that distinct grouping expr is part of that grouping set. Populated during analysis.
private final List<Long> groupingIDs_;
@@ -59,6 +63,9 @@ public class GroupByClause {
// analysis.
private final List<List<Expr>> analyzedGroupingSets_;
+ // END: Members that need to be reset()
+ /////////////////////////////////////////
+
/**
* Constructor for regular GROUP BY, ROLLUP and CUBE.
*/
@@ -110,15 +117,25 @@ public class GroupByClause {
}
public List<Expr> getOrigGroupingExprs() { return origGroupingExprs_; }
- public List<Long> getGroupingIDs() { return groupingIDs_; }
+ public List<Long> getGroupingIDs() {
+ Preconditions.checkState(isAnalyzed_);
+ return groupingIDs_;
+ }
+ public List<List<Expr>> getAnalyzedGroupingSets() {
+ Preconditions.checkState(isAnalyzed_);
+ return analyzedGroupingSets_;
+ }
/**
* Add a new grouping ID if it is not already present in 'groupingIDs_'. If it is a new
* grouping ID, expand the list of expressions it references and append them to
* 'analyzedGroupingSets_'.
* @param groupingExprs duplicated list of analyzed grouping exprs
+ * @param addtlGroupingExprs additional grouping exprs that will be added to each
+ * grouping set, e.g. correlated columns from a subquery rewrite.
*/
- private void addGroupingID(long id, List<Expr> groupingExprs) throws AnalysisException {
+ private void addGroupingID(long id, List<Expr> groupingExprs,
+ List<Expr> addtlGroupingExprs) throws AnalysisException {
Preconditions.checkState(id >= 0 && id < (1L << groupingExprs.size()),
"bad id: " + id);
if (groupingIDs_.contains(id)) return;
@@ -140,6 +157,8 @@ public class GroupByClause {
groupingSet.add(NullLiteral.create(groupingExpr.getType()));
}
}
+ // The additional grouping expressions are a part of each grouping set.
+ groupingSet.addAll(addtlGroupingExprs);
analyzedGroupingSets_.add(groupingSet);
}
@@ -195,6 +214,10 @@ public class GroupByClause {
}
}
+ List<Expr> addtlGroupingExprs =
+ groupingExprs.subList(numOrigGroupingExprs, groupingExprs.size());
+ Expr.removeDuplicates(addtlGroupingExprs);
+
int numGroupingSetExprs = dedupedGroupingSetExprs.size();
if (numGroupingSetExprs >= (Long.SIZE - 1)) {
throw new AnalysisException(
@@ -209,7 +232,7 @@ public class GroupByClause {
// E.g. for CUBE(a, b, c), we enumerate 111, 110, 101, 100, 010, 001, 000,
// meaning the sets (a, b, c), (b, c), (a, c), (c), (b), (a), ().
for (long id = (1L << numGroupingSetExprs) - 1; id >= 0; id--) {
- addGroupingID(id, dedupedGroupingSetExprs);
+ addGroupingID(id, dedupedGroupingSetExprs, addtlGroupingExprs);
}
} else if (groupingSetsType_ == GroupingSetsType.ROLLUP) {
Preconditions.checkState(numGroupingSetExprs > 0);
@@ -220,7 +243,7 @@ public class GroupByClause {
long bit = (1L << numGroupingSetExprs);
long id = bit - 1;
while (bit != 0) {
- addGroupingID(id, dedupedGroupingSetExprs);
+ addGroupingID(id, dedupedGroupingSetExprs, addtlGroupingExprs);
bit >>= 1;
id &= ~bit;
}
@@ -240,9 +263,10 @@ public class GroupByClause {
"bad pos" + dedupedPos);
mask |= (1L << dedupedPos);
}
- addGroupingID(mask, dedupedGroupingSetExprs);
+ addGroupingID(mask, dedupedGroupingSetExprs, addtlGroupingExprs);
}
}
+ isAnalyzed_ = true;
}
/**
@@ -253,6 +277,7 @@ public class GroupByClause {
// groupingIDs_ were generated during analysis, so clear them out.
groupingIDs_.clear();
analyzedGroupingSets_.clear();
+ isAnalyzed_ = false;
}
/**
diff --git a/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java b/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
index 0483df2..0d998ef 100644
--- a/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
+++ b/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
@@ -157,12 +157,22 @@ public class MultiAggregateInfo {
// Indicates if this MultiAggregateInfo is associated with grouping sets.
private boolean isGroupingSet_;
+
+ // Grouping sets provided in the constructor. Null if 'isGroupingSet_' is false or if
+ // the list of grouping sets will be provided later in analyzeCustomClasses(). Each
+ // list must have the same number of expressions, and the expression types must match.
+ // TODO: could generalise this to allow omitting NULL expressions.
+ private List<List<Expr>> groupingSets_;
+
// Indicates whether to generate the grouping_id column for grouping sets
private boolean generateGroupingId_;
- public MultiAggregateInfo(List<Expr> groupingExprs, List<FunctionCallExpr> aggExprs) {
+ public MultiAggregateInfo(List<Expr> groupingExprs, List<FunctionCallExpr> aggExprs,
+ List<List<Expr>> groupingSets) {
groupingExprs_ = Expr.cloneList(Preconditions.checkNotNull(groupingExprs));
aggExprs_ = Expr.cloneList(Preconditions.checkNotNull(aggExprs));
+ groupingSets_ = groupingSets == null ? null : Expr.deepCopy(groupingSets);
+ isGroupingSet_ = groupingSets != null;
}
/**
@@ -211,6 +221,12 @@ public class MultiAggregateInfo {
public void analyze(Analyzer analyzer) throws AnalysisException {
if (isAnalyzed_) return;
+
+ if (groupingSets_ != null) {
+ analyzeGroupingSets(analyzer);
+ return;
+ }
+
isAnalyzed_ = true;
// Group the agg exprs by their DISTINCT exprs and move the non-distinct agg exprs
@@ -270,6 +286,7 @@ public class MultiAggregateInfo {
for (List<FunctionCallExpr> aggClass : aggClasses_) {
aggInfos_.add(AggregateInfo.create(groupingExprs_, aggClass, analyzer));
}
+
if (aggInfos_.size() == 1) {
// Only a single aggregation class, no transposition step is needed.
outputSmap_ = aggInfos_.get(0).getResultSmap();
@@ -306,10 +323,36 @@ public class MultiAggregateInfo {
}
/**
+ * Implementation of analyze() for aggregation with grouping sets.
+ * Does not handle distinct aggregate functions yet.
+ *
+ * @throws AnalysisException if a distinct aggregation function is present or any other
+ * analysis error occurs.
+ */
+ private void analyzeGroupingSets(Analyzer analyzer) throws AnalysisException {
+ for (FunctionCallExpr aggExpr : aggExprs_) {
+ if (aggExpr.isDistinct()) {
+ // We can't handle this now - it would require enumerating more aggregation
+ // classes.
+ throw new AnalysisException("Distinct aggregate functions and grouping sets " +
+ "are not supported in the same query block.");
+ }
+ }
+
+ List<List<FunctionCallExpr>> aggClasses = new ArrayList<>(groupingSets_.size());
+ List<AggregateInfo> aggInfos = new ArrayList<>(groupingSets_.size());
+ for (List<Expr> groupingSet : groupingSets_) {
+ aggClasses.add(aggExprs_);
+ aggInfos.add(AggregateInfo.create(groupingSet, aggExprs_, analyzer));
+ }
+ // analyzeCustomClasses() will do the rest of the analysis and set 'isAnalyzed_'
+ analyzeCustomClasses(analyzer, aggClasses, aggInfos);
+ }
+
+ /**
* Version of analyze method that accepts list of aggregation class and aggregation
- * info that may be created by an external planner. This is needed for supporting
- * grouping sets/rollup functionality. This is unlike the default analyze method which
- * internally generates these lists.
+ * info. This is needed for supporting grouping sets/rollup functionality. Does not
+ * handle distinct aggregate functions.
*/
public void analyzeCustomClasses(Analyzer analyzer,
List<List<FunctionCallExpr>> aggClasses,
@@ -432,8 +475,8 @@ public class MultiAggregateInfo {
* Example:
* SELECT a1, b1, SUM(c1), MIN(d1) FROM t1 GROUP BY ROLLUP(a1, b1)
*
- * Currently, Impala does not support ROLLUP directly but suppose an external planner
- * converts the above to the following 3 Grouping Sets: {(a1, b1), (a1), ()}
+ * For example, the above statement results in the following 3 Grouping Sets:
+ * {(a1, b1), (a1), ()}
* We will map these to the following aggregation classes:
* Class 1:
* Aggregate output exprs: SUM(c1), MIN(d1)
@@ -534,7 +577,8 @@ public class MultiAggregateInfo {
// for a particular aggInfo, we only need to consider the group-by
// exprs relevant to that aggInfo
TupleDescriptor aggTuple = aggInfo.getResultTupleDesc();
- Preconditions.checkState(gbIndex < aggTuple.getSlots().size());
+ Preconditions.checkState(gbIndex < aggTuple.getSlots().size(),
+ groupingExprs.toString() + " " + aggTuple.debugString());
Expr whenExpr = NumericLiteral.create(aggTuple.getId().asInt());
if (aggInfo.getGroupingExprs().size() == 0) {
Type nullType = groupingExprs.get(gbIndex).getType();
diff --git a/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java b/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
index 12df0d8..2fca33d 100644
--- a/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
+++ b/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
@@ -758,10 +758,6 @@ public class SelectStmt extends QueryStmt {
groupingExprs_ = groupingExprsCopy_;
if (groupByClause_ != null && groupByClause_.hasGroupingSets()) {
- if (RuntimeEnv.INSTANCE.isGroupingSetsValidationEnabled()) {
- throw new AnalysisException(groupByClause_.getTypeString() +
- " not supported in GROUP BY");
- }
groupByClause_.analyzeGroupingSets(groupingExprsCopy_);
}
}
@@ -837,10 +833,14 @@ public class SelectStmt extends QueryStmt {
Preconditions.checkState(aggExprs_.isEmpty());
groupingExprs = Expr.cloneList(resultExprs_);
}
+ // All expressions passed into the MultiAggregateInfo must be deduplicated.
+ // The analyzed grouping sets were already deduplicated.
Expr.removeDuplicates(aggExprs_);
Expr.removeDuplicates(groupingExprs);
- // TODO: IMPALA-9898: need to pass in grouping set info for MultiAggregateInfo.
- multiAggInfo_ = new MultiAggregateInfo(groupingExprs, aggExprs_);
+ List<List<Expr>> groupingSets =
+ (groupByClause_ != null && groupByClause_.hasGroupingSets()) ?
+ groupByClause_.getAnalyzedGroupingSets() : null;
+ multiAggInfo_ = new MultiAggregateInfo(groupingExprs, aggExprs_, groupingSets);
multiAggInfo_.analyze(analyzer_);
}
diff --git a/fe/src/main/java/org/apache/impala/common/RuntimeEnv.java b/fe/src/main/java/org/apache/impala/common/RuntimeEnv.java
index 6c00c82..4342cf0 100644
--- a/fe/src/main/java/org/apache/impala/common/RuntimeEnv.java
+++ b/fe/src/main/java/org/apache/impala/common/RuntimeEnv.java
@@ -34,9 +34,6 @@ public class RuntimeEnv {
// service.
private boolean enableMtDopValidation_;
- // Whether we should allow ROLLUP/CUBE/GROUPING SETS queries to pass analysis
- private boolean enableGroupingSetsValidation_;
-
public RuntimeEnv() {
reset();
}
@@ -48,7 +45,6 @@ public class RuntimeEnv {
numCores_ = Runtime.getRuntime().availableProcessors();
isTestEnv_ = false;
enableMtDopValidation_ = false;
- enableGroupingSetsValidation_ = true;
}
public int getNumCores() { return numCores_; }
@@ -57,11 +53,4 @@ public class RuntimeEnv {
public boolean isTestEnv() { return isTestEnv_; }
public boolean isMtDopValidationEnabled() { return enableMtDopValidation_; }
public void setEnableMtDopValidation(boolean v) { enableMtDopValidation_ = v; }
- public boolean isGroupingSetsValidationEnabled() {
- return enableGroupingSetsValidation_;
- }
- public void setEnableGroupingSetsValidation(boolean v) {
- enableGroupingSetsValidation_ = v;
- }
-
}
diff --git a/fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java b/fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java
index 54ca1d1..bbc7140 100644
--- a/fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java
+++ b/fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java
@@ -962,23 +962,27 @@ public class SingleNodePlanner {
private AggregationNode createAggregationPlan(PlanNode root,
MultiAggregateInfo multiAggInfo, Analyzer analyzer) throws InternalException {
Preconditions.checkNotNull(multiAggInfo);
- AggregationNode firstPhaseAgg =
+ AggregationNode agg =
new AggregationNode(ctx_.getNextNodeId(), root, multiAggInfo, AggPhase.FIRST);
- firstPhaseAgg.init(analyzer);
- if (!multiAggInfo.hasSecondPhase()) return firstPhaseAgg;
-
- firstPhaseAgg.unsetNeedsFinalize();
- firstPhaseAgg.setIntermediateTuple();
+ agg.init(analyzer);
+ if (!multiAggInfo.hasSecondPhase() && !multiAggInfo.hasTransposePhase()) {
+ return agg;
+ }
- AggregationNode secondPhaseAgg = new AggregationNode(
- ctx_.getNextNodeId(), firstPhaseAgg, multiAggInfo, AggPhase.SECOND);
- secondPhaseAgg.init(analyzer);
- if (!multiAggInfo.hasTransposePhase()) return secondPhaseAgg;
+ agg.setIntermediateTuple();
- AggregationNode transposePhaseAgg = new AggregationNode(
- ctx_.getNextNodeId(), secondPhaseAgg, multiAggInfo, AggPhase.TRANSPOSE);
- transposePhaseAgg.init(analyzer);
- return transposePhaseAgg;
+ if (multiAggInfo.hasSecondPhase()) {
+ agg.unsetNeedsFinalize();
+ agg = new AggregationNode(
+ ctx_.getNextNodeId(), agg, multiAggInfo, AggPhase.SECOND);
+ agg.init(analyzer);
+ }
+ if (multiAggInfo.hasTransposePhase()) {
+ agg = new AggregationNode(
+ ctx_.getNextNodeId(), agg, multiAggInfo, AggPhase.TRANSPOSE);
+ agg.init(analyzer);
+ }
+ return agg;
}
/**
diff --git a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
index 67703b9..0a86d20 100644
--- a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
@@ -36,7 +36,6 @@ import org.apache.impala.catalog.Table;
import org.apache.impala.catalog.Type;
import org.apache.impala.common.AnalysisException;
import org.apache.impala.common.ImpalaException;
-import org.apache.impala.common.RuntimeEnv;
import org.apache.impala.thrift.TFunctionCategory;
import org.junit.Assert;
import org.junit.Test;
@@ -2286,7 +2285,6 @@ public class AnalyzeStmtsTest extends AnalyzerTest {
*/
@Test
public void TestGroupingSets() throws AnalysisException {
- RuntimeEnv.INSTANCE.setEnableGroupingSetsValidation(false);
// Basic examples of each clause.
AnalyzesOk("select count(*) from functional.alltypes " +
"group by rollup(int_col, string_col)");
@@ -2476,22 +2474,25 @@ public class AnalyzeStmtsTest extends AnalyzerTest {
" group by rollup(t.string_col, t.bool_col)) " +
" group by g.int_col",
"Unsupported correlated subquery with grouping and/or aggregation");
- }
- /**
- * Test that ROLLUP, CUBE and GROUPING SETS result in AnalysException for now.
- */
- @Test
- public void TestGroupingSetsValidation() throws AnalysisException {
- AnalysisError("select count(*) from functional.alltypes " +
- "group by rollup(int_col, string_col)",
- "ROLLUP not supported in GROUP BY");
- AnalysisError("select count(*) from functional.alltypes " +
- "group by cube(int_col, string_col)",
- "CUBE not supported in GROUP BY");
- AnalysisError("select count(*) from functional.alltypes " +
- "group by GROUPING SETS((int_col), (string_col))",
- "SETS not supported in GROUP BY");
+ // Combining grouping sets and distinct aggregations is not supported at this point.
+ // See IMPALA-9914.
+ AnalysisError("select count(distinct id) from functional.alltypes " +
+ "group by rollup(int_col, bool_col)",
+ "Distinct aggregate functions and grouping sets are not supported in the same " +
+ "query block.");
+ AnalysisError("select count(distinct id), count(distinct string_col) " +
+ "from functional.alltypes " +
+ "group by rollup(int_col, bool_col)",
+ "Distinct aggregate functions and grouping sets are not supported in the same " +
+ "query block.");
+
+ // Combining DISTINCT and GROUP BY is not supported in general, not just for grouping
+ // sets.
+ AnalysisError("select distinct int_col, bool_col, count(*) " +
+ "from functional.alltypes " +
+ "group by rollup(int_col, bool_col)",
+ "cannot combine SELECT DISTINCT with aggregate functions or GROUP BY");
}
@Test
diff --git a/fe/src/test/java/org/apache/impala/analysis/ParserTest.java b/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
index 02b5822..0eae3c9 100644
--- a/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
@@ -3666,6 +3666,9 @@ public class ParserTest extends FrontendTestBase {
ParsesOk("SELECT a FROM foo GROUP BY a WITH ROLLUP");
ParsesOk("SELECT a, b FROM foo GROUP BY a, b WITH ROLLUP");
+ // Can't combine syntaxes
+ ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, b) WITH ROLLUP");
+
// Nested grouping clauses not supported.
ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, ROLLUP(b, c))");
ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, CUBE(b, c))");
@@ -3675,6 +3678,12 @@ public class ParserTest extends FrontendTestBase {
ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, b), c");
ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, b), ROLLUP(c)");
ParserError("SELECT a, b FROM foo GROUP BY ROLLUP(a, b), CUBE(c, d)");
+
+ // Empty clause not supported
+ ParserError("SELECT count(*) FROM foo GROUP BY ROLLUP()");
+
+ // Extra parentheses in list elements are supported.
+ ParsesOk("SELECT a, b FROM foo GROUP BY ROLLUP((a), (b))");
}
@Test
@@ -3686,6 +3695,9 @@ public class ParserTest extends FrontendTestBase {
ParsesOk("SELECT a FROM foo GROUP BY a WITH CUBE");
ParsesOk("SELECT a, b FROM foo GROUP BY a, b WITH CUBE");
+ // Can't combine syntaxes
+ ParserError("SELECT a, b FROM foo GROUP BY CUBE(a, b) WITH CUBE");
+
// Nested grouping clauses not supported.
ParserError("SELECT a, b FROM foo GROUP BY CUBE(a, ROLLUP(b, c))");
ParserError("SELECT a, b FROM foo GROUP BY CUBE(a, CUBE(b, c))");
@@ -3694,6 +3706,12 @@ public class ParserTest extends FrontendTestBase {
ParserError("SELECT a, b FROM foo GROUP BY c, CUBE(a, b)");
ParserError("SELECT a, b FROM foo GROUP BY CUBE(a, b), c");
ParserError("SELECT a, b FROM foo GROUP BY CUBE(a, b), CUBE(c)");
+
+ // Empty clause not supported
+ ParserError("SELECT count(*) FROM foo GROUP BY CUBE()");
+
+ // Extra parentheses in list elements are supported.
+ ParsesOk("SELECT a, b FROM foo GROUP BY CUBE((a), (b))");
}
@Test
@@ -3710,6 +3728,13 @@ public class ParserTest extends FrontendTestBase {
// Multiple clauses not supported with GROUPING SETS - parser does not handle yet.
ParserError("SELECT a FROM foo GROUP BY a, b, GROUPING SETS(a, b)");
ParserError("SELECT a FROM foo GROUP BY CUBE(a, b), GROUPING SETS(a, b)");
+
+ // Empty clause not supported, but empty grouping sets are supported.
+ ParsesOk("SELECT a FROM foo GROUP BY GROUPING SETS(())");
+ ParserError("SELECT a FROM foo GROUP BY GROUPING SETS()");
+
+ // Extra parentheses around expressions in list elements are supported.
+ ParserError("SELECT a FROM foo GROUP BY GROUPING SETS((), ((a), (b))");
}
@Test
diff --git a/fe/src/test/java/org/apache/impala/analysis/ToSqlTest.java b/fe/src/test/java/org/apache/impala/analysis/ToSqlTest.java
index 4102c7d..950fd3e 100644
--- a/fe/src/test/java/org/apache/impala/analysis/ToSqlTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/ToSqlTest.java
@@ -23,7 +23,6 @@ import static org.junit.Assert.fail;
import org.apache.impala.authorization.Privilege;
import org.apache.impala.common.AnalysisException;
import org.apache.impala.common.FrontendTestBase;
-import org.apache.impala.common.RuntimeEnv;
import org.apache.impala.testutil.TestUtils;
import org.junit.Test;
@@ -817,9 +816,6 @@ public class ToSqlTest extends FrontendTestBase {
"HAVING avg(tinyint_col) > 10 AND count(tinyint_col) > 5");
// CUBE, ROLLUP.
- // Temporarily disable validation of support for CUBE/ROLLUP/GROUPING SETS
- // so we can run toSql() on these statements.
- RuntimeEnv.INSTANCE.setEnableGroupingSetsValidation(false);
testToSql("select int_col, string_col, sum(id) from functional.alltypes " +
"group by rollup(int_col, string_col)",
"SELECT int_col, string_col, sum(id) FROM functional.alltypes " +
diff --git a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
index d2b89eb..7052721 100644
--- a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
+++ b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
@@ -180,6 +180,11 @@ public class PlannerTest extends PlannerTestBase {
}
@Test
+ public void testGroupingSets() {
+ runPlannerTestFile("grouping-sets");
+ }
+
+ @Test
public void testAnalyticFns() {
runPlannerTestFile("analytic-fns",
ImmutableSet.of(PlannerTestOption.VALIDATE_CARDINALITY));
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/grouping-sets.test b/testdata/workloads/functional-planner/queries/PlannerTest/grouping-sets.test
new file mode 100644
index 0000000..a81d0af
--- /dev/null
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/grouping-sets.test
@@ -0,0 +1,1197 @@
+# Basic ROLLUP
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, bool_col, string_col)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# WITH ROLLUP results in same plan
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by int_col, bool_col, string_col with rollup
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Basic CUBE
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by cube(int_col, bool_col, string_col)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4,5,6,7,8) IN (1, 2, 3, 4, 5, 6, 7, 8), CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) WHEN 5 THEN count(*) WHEN 6 THEN count(*) WHEN 7 THEN count(*) WHEN 8 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN int_col WHEN 6 THEN NULL WHEN 7 THEN int_col WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN bool_col WHEN 6 THEN bool_col WHEN 7 THEN NULL WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN string_col WHEN [...]
+| row-size=29B cardinality=363
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: NULL, bool_col, string_col
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, string_col
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, string_col
+| Class 4
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 5
+| output: count(*)
+| group by: NULL, bool_col, NULL
+| Class 6
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4,5,6,7,8) IN (1, 2, 3, 4, 5, 6, 7, 8), CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) WHEN 5 THEN count(*) WHEN 6 THEN count(*) WHEN 7 THEN count(*) WHEN 8 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN int_col WHEN 6 THEN NULL WHEN 7 THEN int_col WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN bool_col WHEN 6 THEN bool_col WHEN 7 THEN NULL WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN string_col WHEN [...]
+| row-size=29B cardinality=363
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: NULL, bool_col, string_col
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, string_col
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, string_col
+| Class 4
+| output: count:merge(*)
+| group by: int_col, bool_col, NULL
+| Class 5
+| output: count:merge(*)
+| group by: NULL, bool_col, NULL
+| Class 6
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) WHEN 5 THEN murmur_hash(int_col) WHEN 6 THEN murmur_hash(NULL) WHEN 7 THEN murmur_hash(int_col) WHEN 8 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) WHEN 5 THEN murmur_hash(bo [...]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: NULL, bool_col, string_col
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, string_col
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, string_col
+| Class 4
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 5
+| output: count(*)
+| group by: NULL, bool_col, NULL
+| Class 6
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# WITH CUBE results in same plan.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by int_col, bool_col, string_col with cube
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4,5,6,7,8) IN (1, 2, 3, 4, 5, 6, 7, 8), CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) WHEN 5 THEN count(*) WHEN 6 THEN count(*) WHEN 7 THEN count(*) WHEN 8 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN int_col WHEN 6 THEN NULL WHEN 7 THEN int_col WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN bool_col WHEN 6 THEN bool_col WHEN 7 THEN NULL WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN string_col WHEN [...]
+| row-size=29B cardinality=363
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: NULL, bool_col, string_col
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, string_col
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, string_col
+| Class 4
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 5
+| output: count(*)
+| group by: NULL, bool_col, NULL
+| Class 6
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Basic GROUPING SETS
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by grouping sets((int_col, bool_col, string_col), (), (bool_col, string_col))
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3) IN (1, 2, 3), CASE valid_tid(1,2,3) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN bool_col WHEN 2 THEN NULL WHEN 3 THEN bool_col END, CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN string_col END, CASE valid_tid(1,2,3) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END
+| row-size=29B cardinality=221
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: NULL, NULL, NULL
+| Class 2
+| output: count(*)
+| group by: NULL, bool_col, string_col
+| row-size=77B cardinality=221
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3) IN (1, 2, 3), CASE valid_tid(1,2,3) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN bool_col WHEN 2 THEN NULL WHEN 3 THEN bool_col END, CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN string_col END, CASE valid_tid(1,2,3) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END
+| row-size=29B cardinality=221
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| Class 2
+| output: count:merge(*)
+| group by: NULL, bool_col, string_col
+| row-size=77B cardinality=221
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(bool_col) END,CASE valid_tid(1,2,3) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(string_col) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: NULL, NULL, NULL
+| Class 2
+| output: count(*)
+| group by: NULL, bool_col, string_col
+| row-size=77B cardinality=221
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that ordinals are replaced correctly.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, 2, 3)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that duplicate column references are deduplicated. and that
+# the order of elements is respected for rollup.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, 3, 2, 3, string_col)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=311
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count(*)
+| group by: int_col, string_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=102B cardinality=311
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=311
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count:merge(*)
+| group by: int_col, string_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=102B cardinality=311
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(string_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count(*)
+| group by: int_col, string_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=102B cardinality=311
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that duplicate column references are deduplicated, even if the same expression
+# appears in the select list multiple times.
+select int_col, bool_col, string_col, bool_col, string_col, count(*)
+from functional.alltypes
+group by cube(int_col, 3, 2, 4, string_col, 5)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4,5,6,7,8) IN (1, 2, 3, 4, 5, 6, 7, 8), CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) WHEN 5 THEN count(*) WHEN 6 THEN count(*) WHEN 7 THEN count(*) WHEN 8 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN int_col WHEN 6 THEN NULL WHEN 7 THEN int_col WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN string_col WHEN 6 THEN string_col WHEN 7 THEN NULL WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN bool_col WH [...]
+| row-size=29B cardinality=363
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count(*)
+| group by: NULL, string_col, bool_col
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, bool_col
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, bool_col
+| Class 4
+| output: count(*)
+| group by: int_col, string_col, NULL
+| Class 5
+| output: count(*)
+| group by: NULL, string_col, NULL
+| Class 6
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4,5,6,7,8) IN (1, 2, 3, 4, 5, 6, 7, 8), CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) WHEN 5 THEN count(*) WHEN 6 THEN count(*) WHEN 7 THEN count(*) WHEN 8 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN int_col WHEN 2 THEN NULL WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN int_col WHEN 6 THEN NULL WHEN 7 THEN int_col WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN string_col WHEN 2 THEN string_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN string_col WHEN 6 THEN string_col WHEN 7 THEN NULL WHEN 8 THEN NULL END, CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN bool_col WH [...]
+| row-size=29B cardinality=363
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count:merge(*)
+| group by: NULL, string_col, bool_col
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, bool_col
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, bool_col
+| Class 4
+| output: count:merge(*)
+| group by: int_col, string_col, NULL
+| Class 5
+| output: count:merge(*)
+| group by: NULL, string_col, NULL
+| Class 6
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) WHEN 5 THEN murmur_hash(int_col) WHEN 6 THEN murmur_hash(NULL) WHEN 7 THEN murmur_hash(int_col) WHEN 8 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4,5,6,7,8) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(string_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) WHEN 5 THEN murmur_has [...]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col, bool_col
+| Class 1
+| output: count(*)
+| group by: NULL, string_col, bool_col
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, bool_col
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, bool_col
+| Class 4
+| output: count(*)
+| group by: int_col, string_col, NULL
+| Class 5
+| output: count(*)
+| group by: NULL, string_col, NULL
+| Class 6
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 7
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=204B cardinality=363
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that non-trivial expressions can be used
+select int_col * bigint_col, bool_col, string_col || 'foo', count(*)
+from functional.alltypes
+group by rollup(int_col * bigint_col, bool_col, string_col || 'foo')
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col * bigint_col WHEN 2 THEN int_col * bigint_col WHEN 3 THEN int_col * bigint_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN concat(string_col, 'foo') WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=33B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count(*)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=116B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=26B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col * bigint_col WHEN 2 THEN int_col * bigint_col WHEN 3 THEN int_col * bigint_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN concat(string_col, 'foo') WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=33B cardinality=231
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count:merge(*)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=116B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col * bigint_col) WHEN 2 THEN murmur_hash(int_col * bigint_col) WHEN 3 THEN murmur_hash(int_col * bigint_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(concat(string_col, 'foo')) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmu [...]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count(*)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=116B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=26B cardinality=7.30K
+====
+# Test that mixed aggregate functions can be used.
+select int_col * bigint_col, bool_col, string_col || 'foo',
+ count(*), sum(tinyint_col), min(date_string_col)
+from functional.alltypes
+group by rollup(int_col * bigint_col, bool_col, string_col || 'foo')
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN sum(tinyint_col) WHEN 2 THEN sum(tinyint_col) WHEN 3 THEN sum(tinyint_col) WHEN 4 THEN sum(tinyint_col) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(date_string_col) WHEN 2 THEN min(date_string_col) WHEN [...]
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col * bigint_col WHEN 2 THEN int_col * bigint_col WHEN 3 THEN int_col * bigint_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN concat(string_col, 'foo') WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=53B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: NULL, NULL, NULL
+| row-size=196B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=47B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN sum(tinyint_col) WHEN 2 THEN sum(tinyint_col) WHEN 3 THEN sum(tinyint_col) WHEN 4 THEN sum(tinyint_col) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(date_string_col) WHEN 2 THEN min(date_string_col) WHEN [...]
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col * bigint_col WHEN 2 THEN int_col * bigint_col WHEN 3 THEN int_col * bigint_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN concat(string_col, 'foo') WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=53B cardinality=231
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*), sum:merge(tinyint_col), min:merge(date_string_col)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count:merge(*), sum:merge(tinyint_col), min:merge(date_string_col)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count:merge(*), sum:merge(tinyint_col), min:merge(date_string_col)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count:merge(*), sum:merge(tinyint_col), min:merge(date_string_col)
+| group by: NULL, NULL, NULL
+| row-size=196B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col * bigint_col) WHEN 2 THEN murmur_hash(int_col * bigint_col) WHEN 3 THEN murmur_hash(int_col * bigint_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(concat(string_col, 'foo')) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmu [...]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, bool_col, concat(string_col, 'foo')
+| Class 1
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, bool_col, NULL
+| Class 2
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: int_col * bigint_col, NULL, NULL
+| Class 3
+| output: count(*), sum(tinyint_col), min(date_string_col)
+| group by: NULL, NULL, NULL
+| row-size=196B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=47B cardinality=7.30K
+====
+# Test that limit is placed in final aggregate node.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, 2, 3)
+limit 5
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| limit: 5
+| row-size=29B cardinality=5
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+| limit: 5
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| limit: 5
+| row-size=29B cardinality=5
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that HAVING predicates are placed at final aggregate node.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, 2, 3)
+having count(*) > 1 and min(int_col) > 0
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(int_col) WHEN 2 THEN min(int_col) WHEN 3 THEN min(int_col) WHEN 4 THEN min(int_col) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| having: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END) > 1, aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(int_col) WHEN 2 THEN min(int_col) WHEN 3 THEN min(int_col) WHEN 4 THEN min(int_col) END) > 0
+| row-size=33B cardinality=23
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*), min(int_col)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*), min(int_col)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*), min(int_col)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*), min(int_col)
+| group by: NULL, NULL, NULL
+| row-size=117B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END), aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(int_col) WHEN 2 THEN min(int_col) WHEN 3 THEN min(int_col) WHEN 4 THEN min(int_col) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| having: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END) > 1, aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN min(int_col) WHEN 2 THEN min(int_col) WHEN 3 THEN min(int_col) WHEN 4 THEN min(int_col) END) > 0
+| row-size=33B cardinality=23
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*), min:merge(int_col)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*), min:merge(int_col)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count:merge(*), min:merge(int_col)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*), min:merge(int_col)
+| group by: NULL, NULL, NULL
+| row-size=117B cardinality=231
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*), min(int_col)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*), min(int_col)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*), min(int_col)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*), min(int_col)
+| group by: NULL, NULL, NULL
+| row-size=117B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Test that ORDER BY is handled correctly.
+select int_col, bool_col, string_col, count(*) from functional.alltypes
+group by rollup(int_col, bool_col, string_col)
+order by string_col, int_col desc, bool_col
+---- PLAN
+PLAN-ROOT SINK
+|
+03:SORT
+| order by: CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC, CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END DESC, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC
+| row-size=25B cardinality=231
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+06:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC, CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END DESC, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC
+|
+03:SORT
+| order by: CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC, CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END DESC, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END ASC
+| row-size=25B cardinality=231
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN bool_col WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=29B cardinality=231
+|
+05:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count:merge(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+04:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(bool_col) WHEN 2 THEN murmur_hash(bool_col) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(string_col) WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, bool_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, bool_col, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=101B cardinality=231
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=18B cardinality=7.30K
+====
+# Grouping sets with constants in group by. We still produce the same grouping sets
+# as if they were variable expressions.
+select int_col, 1234 i, 'test', count(*) from functional.alltypes
+group by rollup(int_col, i, 'test')
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1234 WHEN 2 THEN 1234 WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 'test' WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=30B cardinality=31
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, 1234, 'test'
+| Class 1
+| output: count(*)
+| group by: int_col, 1234, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=104B cardinality=31
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=4B cardinality=7.30K
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+05:EXCHANGE [UNPARTITIONED]
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3,4) IN (1, 2, 3, 4), CASE valid_tid(1,2,3,4) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) WHEN 4 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3,4) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1234 WHEN 2 THEN 1234 WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 'test' WHEN 2 THEN NULL WHEN 3 THEN NULL WHEN 4 THEN NULL END, CASE valid_tid(1,2,3,4) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
+| row-size=30B cardinality=31
+|
+04:AGGREGATE [FINALIZE]
+| Class 0
+| output: count:merge(*)
+| group by: int_col, 1234, 'test'
+| Class 1
+| output: count:merge(*)
+| group by: int_col, 1234, NULL
+| Class 2
+| output: count:merge(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count:merge(*)
+| group by: NULL, NULL, NULL
+| row-size=104B cardinality=31
+|
+03:EXCHANGE [HASH(CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(int_col) WHEN 2 THEN murmur_hash(int_col) WHEN 3 THEN murmur_hash(int_col) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash(1234) WHEN 2 THEN murmur_hash(1234) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END,CASE valid_tid(1,2,3,4) WHEN 1 THEN murmur_hash('test') WHEN 2 THEN murmur_hash(NULL) WHEN 3 THEN murmur_hash(NULL) WHEN 4 THEN murmur_hash(NULL) END)]
+|
+01:AGGREGATE [STREAMING]
+| Class 0
+| output: count(*)
+| group by: int_col, 1234, 'test'
+| Class 1
+| output: count(*)
+| group by: int_col, 1234, NULL
+| Class 2
+| output: count(*)
+| group by: int_col, NULL, NULL
+| Class 3
+| output: count(*)
+| group by: NULL, NULL, NULL
+| row-size=104B cardinality=31
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=4B cardinality=7.30K
+====
+# Empty grouping sets results in non-grouping aggregation.
+select count(*) from functional.alltypes
+group by grouping sets(())
+---- PLAN
+PLAN-ROOT SINK
+|
+01:AGGREGATE [FINALIZE]
+| output: count(*)
+| row-size=8B cardinality=1
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=0B cardinality=7.30K
+====
+# Rollup of single column results in multiple aggregation classes.
+select int_col, count(*) from functional.alltypes
+group by rollup(int_col)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2) IN (1, 2), CASE valid_tid(1,2) WHEN 1 THEN count(*) WHEN 2 THEN count(*) END)
+| group by: CASE valid_tid(1,2) WHEN 1 THEN int_col WHEN 2 THEN NULL END, CASE valid_tid(1,2) WHEN 1 THEN 1 WHEN 2 THEN 2 END
+| row-size=16B cardinality=11
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col
+| Class 1
+| output: count(*)
+| group by: NULL
+| row-size=24B cardinality=11
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=4B cardinality=7.30K
+====
+# Cube of single column results in multiple aggregation classes.
+select int_col, count(*) from functional.alltypes
+group by rollup(int_col)
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2) IN (1, 2), CASE valid_tid(1,2) WHEN 1 THEN count(*) WHEN 2 THEN count(*) END)
+| group by: CASE valid_tid(1,2) WHEN 1 THEN int_col WHEN 2 THEN NULL END, CASE valid_tid(1,2) WHEN 1 THEN 1 WHEN 2 THEN 2 END
+| row-size=16B cardinality=11
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col
+| Class 1
+| output: count(*)
+| group by: NULL
+| row-size=24B cardinality=11
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=4B cardinality=7.30K
+====
+# Grouping sets referencing single column results in simple grouping aggregation.
+select int_col, count(*) from functional.alltypes
+group by grouping sets((int_col), (1))
+---- PLAN
+PLAN-ROOT SINK
+|
+01:AGGREGATE [FINALIZE]
+| output: count(*)
+| group by: int_col
+| row-size=12B cardinality=10
+|
+00:SCAN HDFS [functional.alltypes]
+ HDFS partitions=24/24 files=24 size=478.45KB
+ row-size=4B cardinality=7.30K
+====
+# Having predicates must be evaluated after aggregation because the aggregation can
+# introduce NULLs.
+select int_col, string_col, count(*)
+from functional.alltypesagg
+group by rollup(int_col, string_col)
+having int_col is NULL and string_col > 'abc'
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3) IN (1, 2, 3), CASE valid_tid(1,2,3) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END
+| having: CASE valid_tid(1,2,3) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN NULL END IS NULL, CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL END > 'abc'
+| row-size=28B cardinality=7
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, NULL
+| Class 2
+| output: count(*)
+| group by: NULL, NULL
+| row-size=75B cardinality=11.96K
+|
+00:SCAN HDFS [functional.alltypesagg]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ row-size=19B cardinality=11.00K
+====
+# Missing optimization: the HAVING predicate could eliminate 2/3 of the agg classes.
+select int_col, string_col, count(*)
+from functional.alltypesagg
+group by rollup(int_col, string_col)
+having string_col is not NULL
+---- PLAN
+PLAN-ROOT SINK
+|
+02:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(1,2,3) IN (1, 2, 3), CASE valid_tid(1,2,3) WHEN 1 THEN count(*) WHEN 2 THEN count(*) WHEN 3 THEN count(*) END)
+| group by: CASE valid_tid(1,2,3) WHEN 1 THEN int_col WHEN 2 THEN int_col WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL END, CASE valid_tid(1,2,3) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END
+| having: CASE valid_tid(1,2,3) WHEN 1 THEN string_col WHEN 2 THEN NULL WHEN 3 THEN NULL END IS NOT NULL
+| row-size=28B cardinality=11.96K
+|
+01:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(*)
+| group by: int_col, string_col
+| Class 1
+| output: count(*)
+| group by: int_col, NULL
+| Class 2
+| output: count(*)
+| group by: NULL, NULL
+| row-size=75B cardinality=11.96K
+|
+00:SCAN HDFS [functional.alltypesagg]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ row-size=19B cardinality=11.00K
+====
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/subquery-rewrite.test b/testdata/workloads/functional-planner/queries/PlannerTest/subquery-rewrite.test
index b562e9e..f7326ea 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/subquery-rewrite.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/subquery-rewrite.test
@@ -4541,3 +4541,178 @@ PLAN-ROOT SINK
HDFS partitions=4/4 files=4 size=460B
row-size=4B cardinality=8
====
+# Uncorrelated EXISTS with a group by clause with rollup.
+select 1
+from functional.alltypesagg a
+where exists
+ (select id
+ from functional.alltypestiny b
+ group by rollup(id, int_col, bool_col))
+and tinyint_col < 10
+---- PLAN
+PLAN-ROOT SINK
+|
+04:NESTED LOOP JOIN [LEFT SEMI JOIN]
+| row-size=1B cardinality=1.10K
+|
+|--03:AGGREGATE [FINALIZE]
+| | group by: CASE valid_tid(2,3,4,5) WHEN 2 THEN id WHEN 3 THEN id WHEN 4 THEN id WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 END
+| | limit: 1
+| | row-size=13B cardinality=1
+| |
+| 02:AGGREGATE [FINALIZE]
+| | Class 0
+| | group by: id, int_col, bool_col
+| | Class 1
+| | group by: id, int_col, NULL
+| | Class 2
+| | group by: id, NULL, NULL
+| | Class 3
+| | group by: NULL, NULL, NULL
+| | row-size=36B cardinality=25
+| |
+| 01:SCAN HDFS [functional.alltypestiny b]
+| HDFS partitions=4/4 files=4 size=460B
+| row-size=9B cardinality=8
+|
+00:SCAN HDFS [functional.alltypesagg a]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ predicates: tinyint_col < 10
+ row-size=1B cardinality=1.10K
+====
+# Correlated EXISTS with a group by clause with rollup.
+# The correlated columns are pushed into the GROUP BY.
+# TODO: This plan could be improved by removing the ROLLUP in the subquery or by
+# reordering the JOIN and AGGREGATE.
+select 1
+from functional.alltypesagg a
+where exists
+ (select id
+ from functional.alltypestiny b
+ where a.tinyint_col = b.tinyint_col and a.string_col = b.string_col
+ group by rollup(id, int_col, bool_col))
+and tinyint_col < 10
+---- PLAN
+PLAN-ROOT SINK
+|
+04:HASH JOIN [LEFT SEMI JOIN]
+| hash predicates: a.tinyint_col = CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, a.string_col = CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WHEN 3 THEN b.string_col WHEN 4 THEN b.string_col WHEN 5 THEN b.string_col END
+| runtime filters: RF000 <- CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, RF001 <- CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WHEN 3 THEN b.string_col WHEN 4 THEN b.string_col WHEN 5 THEN b.string_col END
+| row-size=16B cardinality=3
+|
+|--03:AGGREGATE [FINALIZE]
+| | group by: CASE valid_tid(2,3,4,5) WHEN 2 THEN id WHEN 3 THEN id WHEN 4 THEN id WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col [...]
+| | row-size=26B cardinality=28
+| |
+| 02:AGGREGATE [FINALIZE]
+| | Class 0
+| | group by: id, int_col, bool_col, b.tinyint_col, b.string_col
+| | Class 1
+| | group by: id, int_col, NULL, b.tinyint_col, b.string_col
+| | Class 2
+| | group by: id, NULL, NULL, b.tinyint_col, b.string_col
+| | Class 3
+| | group by: NULL, NULL, NULL, b.tinyint_col, b.string_col
+| | row-size=92B cardinality=28
+| |
+| 01:SCAN HDFS [functional.alltypestiny b]
+| HDFS partitions=4/4 files=4 size=460B
+| row-size=23B cardinality=8
+|
+00:SCAN HDFS [functional.alltypesagg a]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ predicates: tinyint_col < 10
+ runtime filters: RF000 -> a.tinyint_col, RF001 -> a.string_col
+ row-size=16B cardinality=1.10K
+====
+# Correlated EXISTS with a group by clause with rollup and having.
+# The correlated columns are pushed into the GROUP BY.
+# This plan could be improved by reordering the JOIN and AGGREGATE
+# or by transforming the ROLLUP (the HAVING predicate interacts with
+# the ROLLUP since it is always true for some aggregation classes).
+select 1
+from functional.alltypesagg a
+where exists
+ (select id
+ from functional.alltypestiny b
+ where a.tinyint_col = b.tinyint_col and a.string_col = b.string_col
+ group by rollup(id, int_col, bool_col)
+ having int_col is null)
+and tinyint_col < 10
+---- PLAN
+PLAN-ROOT SINK
+|
+04:HASH JOIN [LEFT SEMI JOIN]
+| hash predicates: a.tinyint_col = CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, a.string_col = CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WHEN 3 THEN b.string_col WHEN 4 THEN b.string_col WHEN 5 THEN b.string_col END
+| runtime filters: RF000 <- CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, RF001 <- CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WHEN 3 THEN b.string_col WHEN 4 THEN b.string_col WHEN 5 THEN b.string_col END
+| row-size=16B cardinality=1
+|
+|--03:AGGREGATE [FINALIZE]
+| | group by: CASE valid_tid(2,3,4,5) WHEN 2 THEN id WHEN 3 THEN id WHEN 4 THEN id WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col [...]
+| | having: CASE valid_tid(2,3,4,5) WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN NULL END IS NULL
+| | row-size=26B cardinality=1
+| |
+| 02:AGGREGATE [FINALIZE]
+| | Class 0
+| | group by: id, int_col, bool_col, b.tinyint_col, b.string_col
+| | Class 1
+| | group by: id, int_col, NULL, b.tinyint_col, b.string_col
+| | Class 2
+| | group by: id, NULL, NULL, b.tinyint_col, b.string_col
+| | Class 3
+| | group by: NULL, NULL, NULL, b.tinyint_col, b.string_col
+| | row-size=92B cardinality=28
+| |
+| 01:SCAN HDFS [functional.alltypestiny b]
+| HDFS partitions=4/4 files=4 size=460B
+| row-size=23B cardinality=8
+|
+00:SCAN HDFS [functional.alltypesagg a]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ predicates: tinyint_col < 10
+ runtime filters: RF000 -> a.tinyint_col, RF001 -> a.string_col
+ row-size=16B cardinality=1.10K
+====
+# Correlated EXISTS with a group by clause with rollup.
+# Same query as above but with tables flipped so that the join is transformed into a
+# RIGHT OUTER JOIN and the aggregation ends up on the left side of of the join. Runtime
+# filters cannot be pushed to the scan because the rollup aggregation introduces NULLs.
+select 1
+from functional.alltypestiny a
+where exists
+ (select id
+ from functional.alltypesagg b
+ where a.tinyint_col = b.tinyint_col and a.string_col = b.string_col
+ group by rollup(id, int_col, bool_col))
+and tinyint_col < 10
+---- PLAN
+PLAN-ROOT SINK
+|
+04:HASH JOIN [RIGHT SEMI JOIN]
+| hash predicates: CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END = a.tinyint_col, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WHEN 3 THEN b.string_col WHEN 4 THEN b.string_col WHEN 5 THEN b.string_col END = a.string_col
+| row-size=14B cardinality=1
+|
+|--00:SCAN HDFS [functional.alltypestiny a]
+| HDFS partitions=4/4 files=4 size=460B
+| predicates: tinyint_col < 10
+| row-size=14B cardinality=1
+|
+03:AGGREGATE [FINALIZE]
+| group by: CASE valid_tid(2,3,4,5) WHEN 2 THEN id WHEN 3 THEN id WHEN 4 THEN id WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN int_col WHEN 3 THEN int_col WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN bool_col WHEN 3 THEN NULL WHEN 4 THEN NULL WHEN 5 THEN NULL END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.tinyint_col WHEN 3 THEN b.tinyint_col WHEN 4 THEN b.tinyint_col WHEN 5 THEN b.tinyint_col END, CASE valid_tid(2,3,4,5) WHEN 2 THEN b.string_col WH [...]
+| row-size=26B cardinality=41.67K
+|
+02:AGGREGATE [FINALIZE]
+| Class 0
+| group by: id, int_col, bool_col, b.tinyint_col, b.string_col
+| Class 1
+| group by: id, int_col, NULL, b.tinyint_col, b.string_col
+| Class 2
+| group by: id, NULL, NULL, b.tinyint_col, b.string_col
+| Class 3
+| group by: NULL, NULL, NULL, b.tinyint_col, b.string_col
+| row-size=100B cardinality=41.67K
+|
+01:SCAN HDFS [functional.alltypesagg b]
+ HDFS partitions=11/11 files=11 size=814.73KB
+ row-size=25B cardinality=11.00K
+====
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test
index c95a47d..f60fa9b 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds-all.test
@@ -10443,3 +10443,1772 @@ PLAN-ROOT SINK
runtime filters: RF000 -> ws_item_sk, RF004 -> ws_bill_customer_sk, RF006 -> ws_sold_date_sk
row-size=20B cardinality=719.38K
====
+# TPCDS-Q18
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as decimal(12,2))) agg1,
+ avg( cast(cs_list_price as decimal(12,2))) agg2,
+ avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
+ avg( cast(cs_sales_price as decimal(12,2))) agg4,
+ avg( cast(cs_net_profit as decimal(12,2))) agg5,
+ avg( cast(c_birth_year as decimal(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100;
+---- PLAN
+Max Per-Host Resource Reservation: Memory=111.38MB Threads=8
+Per-Host Resource Estimates: Memory=702MB
+PLAN-ROOT SINK
+|
+15:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_country WHEN 10 THEN ca_country WHEN 12 THEN ca_country WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_state WHEN 10 THEN ca_state WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_county WHEN 10 THEN NULL WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN i_item_id WHEN 10 THEN [...]
+| row-size=104B cardinality=100
+|
+14:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(16,8,10,12,14) IN (8, 10, 12, 14, 16), CASE valid_tid(16,8,10,12,14) WHEN 8 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 10 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 12 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 14 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 16 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) END), aggif(valid_tid(16,8,10,12,14) IN (8, 10, 12, 14, 16), CASE valid_tid(16,8,10,12,14) WHEN 8 THEN avg(CAST(cs_list_price AS DEC [...]
+| group by: CASE valid_tid(16,8,10,12,14) WHEN 8 THEN i_item_id WHEN 10 THEN i_item_id WHEN 12 THEN i_item_id WHEN 14 THEN i_item_id WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_country WHEN 10 THEN ca_country WHEN 12 THEN ca_country WHEN 14 THEN NULL WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_state WHEN 10 THEN ca_state WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_county WHEN 10 T [...]
+| row-size=108B cardinality=75.61K
+|
+13:AGGREGATE [FINALIZE]
+| Class 0
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, ca_county
+| Class 1
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, NULL
+| Class 2
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, NULL, NULL
+| Class 3
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, NULL, NULL, NULL
+| Class 4
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: NULL, NULL, NULL, NULL
+| row-size=641B cardinality=75.61K
+|
+12:HASH JOIN [INNER JOIN]
+| hash predicates: cs_item_sk = i_item_sk
+| runtime filters: RF000 <- i_item_sk
+| row-size=220B cardinality=28.95K
+|
+|--06:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=36B cardinality=18.00K
+|
+11:HASH JOIN [INNER JOIN]
+| hash predicates: cd2.cd_demo_sk = c_current_cdemo_sk
+| runtime filters: RF002 <- c_current_cdemo_sk
+| row-size=184B cardinality=28.95K
+|
+|--10:HASH JOIN [INNER JOIN]
+| | hash predicates: cd1.cd_demo_sk = cs_bill_cdemo_sk
+| | runtime filters: RF004 <- cs_bill_cdemo_sk
+| | row-size=180B cardinality=28.95K
+| |
+| |--09:HASH JOIN [INNER JOIN]
+| | | hash predicates: c_current_addr_sk = ca_address_sk
+| | | runtime filters: RF006 <- ca_address_sk
+| | | row-size=137B cardinality=28.95K
+| | |
+| | |--04:SCAN HDFS [tpcds.customer_address]
+| | | HDFS partitions=1/1 files=1 size=5.25MB
+| | | predicates: ca_state IN ('ND', 'WI', 'AL', 'NC', 'OK', 'MS', 'TN')
+| | | row-size=69B cardinality=6.86K
+| | |
+| | 08:HASH JOIN [INNER JOIN]
+| | | hash predicates: cs_bill_customer_sk = c_customer_sk
+| | | runtime filters: RF008 <- c_customer_sk
+| | | row-size=68B cardinality=181.77K
+| | |
+| | |--03:SCAN HDFS [tpcds.customer]
+| | | HDFS partitions=1/1 files=1 size=12.60MB
+| | | predicates: c_birth_month IN (9, 5, 12, 4, 1, 10)
+| | | runtime filters: RF006 -> c_current_addr_sk
+| | | row-size=20B cardinality=50.00K
+| | |
+| | 07:HASH JOIN [INNER JOIN]
+| | | hash predicates: cs_sold_date_sk = d_date_sk
+| | | runtime filters: RF010 <- d_date_sk
+| | | row-size=48B cardinality=294.63K
+| | |
+| | |--05:SCAN HDFS [tpcds.date_dim]
+| | | HDFS partitions=1/1 files=1 size=9.84MB
+| | | predicates: d_year = 2001
+| | | row-size=8B cardinality=373
+| | |
+| | 00:SCAN HDFS [tpcds.catalog_sales]
+| | HDFS partitions=1/1 files=1 size=282.20MB
+| | runtime filters: RF000 -> cs_item_sk, RF008 -> cs_bill_customer_sk, RF010 -> cs_sold_date_sk
+| | row-size=40B cardinality=1.44M
+| |
+| 01:SCAN HDFS [tpcds.customer_demographics cd1]
+| HDFS partitions=1/1 files=1 size=76.92MB
+| predicates: cd1.cd_gender = 'M', cd1.cd_education_status = 'College'
+| runtime filters: RF004 -> cd1.cd_demo_sk
+| row-size=43B cardinality=194.03K
+|
+02:SCAN HDFS [tpcds.customer_demographics cd2]
+ HDFS partitions=1/1 files=1 size=76.92MB
+ runtime filters: RF002 -> cd2.cd_demo_sk
+ row-size=4B cardinality=1.92M
+---- DISTRIBUTEDPLAN
+Max Per-Host Resource Reservation: Memory=228.94MB Threads=18
+Per-Host Resource Estimates: Memory=911MB
+PLAN-ROOT SINK
+|
+26:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_country WHEN 10 THEN ca_country WHEN 12 THEN ca_country WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_state WHEN 10 THEN ca_state WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_county WHEN 10 THEN NULL WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN i_item_id WHEN 10 THEN [...]
+| limit: 100
+|
+15:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_country WHEN 10 THEN ca_country WHEN 12 THEN ca_country WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_state WHEN 10 THEN ca_state WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_county WHEN 10 THEN NULL WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END ASC, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN i_item_id WHEN 10 THEN [...]
+| row-size=104B cardinality=100
+|
+14:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(16,8,10,12,14) IN (8, 10, 12, 14, 16), CASE valid_tid(16,8,10,12,14) WHEN 8 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 10 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 12 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 14 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) WHEN 16 THEN avg(CAST(cs_quantity AS DECIMAL(12,2))) END), aggif(valid_tid(16,8,10,12,14) IN (8, 10, 12, 14, 16), CASE valid_tid(16,8,10,12,14) WHEN 8 THEN avg(CAST(cs_list_price AS DEC [...]
+| group by: CASE valid_tid(16,8,10,12,14) WHEN 8 THEN i_item_id WHEN 10 THEN i_item_id WHEN 12 THEN i_item_id WHEN 14 THEN i_item_id WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_country WHEN 10 THEN ca_country WHEN 12 THEN ca_country WHEN 14 THEN NULL WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_state WHEN 10 THEN ca_state WHEN 12 THEN NULL WHEN 14 THEN NULL WHEN 16 THEN NULL END, CASE valid_tid(16,8,10,12,14) WHEN 8 THEN ca_county WHEN 10 T [...]
+| row-size=108B cardinality=75.61K
+|
+25:AGGREGATE [FINALIZE]
+| Class 0
+| output: avg:merge(CAST(cs_quantity AS DECIMAL(12,2))), avg:merge(CAST(cs_list_price AS DECIMAL(12,2))), avg:merge(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg:merge(CAST(cs_sales_price AS DECIMAL(12,2))), avg:merge(CAST(cs_net_profit AS DECIMAL(12,2))), avg:merge(CAST(c_birth_year AS DECIMAL(12,2))), avg:merge(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, ca_county
+| Class 1
+| output: avg:merge(CAST(cs_quantity AS DECIMAL(12,2))), avg:merge(CAST(cs_list_price AS DECIMAL(12,2))), avg:merge(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg:merge(CAST(cs_sales_price AS DECIMAL(12,2))), avg:merge(CAST(cs_net_profit AS DECIMAL(12,2))), avg:merge(CAST(c_birth_year AS DECIMAL(12,2))), avg:merge(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, NULL
+| Class 2
+| output: avg:merge(CAST(cs_quantity AS DECIMAL(12,2))), avg:merge(CAST(cs_list_price AS DECIMAL(12,2))), avg:merge(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg:merge(CAST(cs_sales_price AS DECIMAL(12,2))), avg:merge(CAST(cs_net_profit AS DECIMAL(12,2))), avg:merge(CAST(c_birth_year AS DECIMAL(12,2))), avg:merge(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, NULL, NULL
+| Class 3
+| output: avg:merge(CAST(cs_quantity AS DECIMAL(12,2))), avg:merge(CAST(cs_list_price AS DECIMAL(12,2))), avg:merge(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg:merge(CAST(cs_sales_price AS DECIMAL(12,2))), avg:merge(CAST(cs_net_profit AS DECIMAL(12,2))), avg:merge(CAST(c_birth_year AS DECIMAL(12,2))), avg:merge(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, NULL, NULL, NULL
+| Class 4
+| output: avg:merge(CAST(cs_quantity AS DECIMAL(12,2))), avg:merge(CAST(cs_list_price AS DECIMAL(12,2))), avg:merge(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg:merge(CAST(cs_sales_price AS DECIMAL(12,2))), avg:merge(CAST(cs_net_profit AS DECIMAL(12,2))), avg:merge(CAST(c_birth_year AS DECIMAL(12,2))), avg:merge(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: NULL, NULL, NULL, NULL
+| row-size=641B cardinality=75.61K
+|
+24:EXCHANGE [HASH(CASE valid_tid(7,9,11,13,15) WHEN 7 THEN murmur_hash(i_item_id) WHEN 9 THEN murmur_hash(i_item_id) WHEN 11 THEN murmur_hash(i_item_id) WHEN 13 THEN murmur_hash(i_item_id) WHEN 15 THEN murmur_hash(NULL) END,CASE valid_tid(7,9,11,13,15) WHEN 7 THEN murmur_hash(ca_country) WHEN 9 THEN murmur_hash(ca_country) WHEN 11 THEN murmur_hash(ca_country) WHEN 13 THEN murmur_hash(NULL) WHEN 15 THEN murmur_hash(NULL) END,CASE valid_tid(7,9,11,13,15) WHEN 7 THEN murmur_hash(ca_state) W [...]
+|
+13:AGGREGATE [STREAMING]
+| Class 0
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, ca_county
+| Class 1
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, ca_state, NULL
+| Class 2
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, ca_country, NULL, NULL
+| Class 3
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: i_item_id, NULL, NULL, NULL
+| Class 4
+| output: avg(CAST(cs_quantity AS DECIMAL(12,2))), avg(CAST(cs_list_price AS DECIMAL(12,2))), avg(CAST(cs_coupon_amt AS DECIMAL(12,2))), avg(CAST(cs_sales_price AS DECIMAL(12,2))), avg(CAST(cs_net_profit AS DECIMAL(12,2))), avg(CAST(c_birth_year AS DECIMAL(12,2))), avg(CAST(cd1.cd_dep_count AS DECIMAL(12,2)))
+| group by: NULL, NULL, NULL, NULL
+| row-size=641B cardinality=75.61K
+|
+12:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: cs_item_sk = i_item_sk
+| runtime filters: RF000 <- i_item_sk
+| row-size=220B cardinality=28.95K
+|
+|--23:EXCHANGE [BROADCAST]
+| |
+| 06:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=36B cardinality=18.00K
+|
+11:HASH JOIN [INNER JOIN, PARTITIONED]
+| hash predicates: c_current_cdemo_sk = cd2.cd_demo_sk
+| runtime filters: RF002 <- cd2.cd_demo_sk
+| row-size=184B cardinality=28.95K
+|
+|--22:EXCHANGE [HASH(cd2.cd_demo_sk)]
+| |
+| 02:SCAN HDFS [tpcds.customer_demographics cd2]
+| HDFS partitions=1/1 files=1 size=76.92MB
+| row-size=4B cardinality=1.92M
+|
+21:EXCHANGE [HASH(c_current_cdemo_sk)]
+|
+10:HASH JOIN [INNER JOIN, PARTITIONED]
+| hash predicates: cs_bill_cdemo_sk = cd1.cd_demo_sk
+| runtime filters: RF004 <- cd1.cd_demo_sk
+| row-size=180B cardinality=28.95K
+|
+|--20:EXCHANGE [HASH(cd1.cd_demo_sk)]
+| |
+| 01:SCAN HDFS [tpcds.customer_demographics cd1]
+| HDFS partitions=1/1 files=1 size=76.92MB
+| predicates: cd1.cd_gender = 'M', cd1.cd_education_status = 'College'
+| row-size=43B cardinality=194.03K
+|
+19:EXCHANGE [HASH(cs_bill_cdemo_sk)]
+|
+09:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: c_current_addr_sk = ca_address_sk
+| runtime filters: RF006 <- ca_address_sk
+| row-size=137B cardinality=28.95K
+|
+|--18:EXCHANGE [BROADCAST]
+| |
+| 04:SCAN HDFS [tpcds.customer_address]
+| HDFS partitions=1/1 files=1 size=5.25MB
+| predicates: ca_state IN ('ND', 'WI', 'AL', 'NC', 'OK', 'MS', 'TN')
+| row-size=69B cardinality=6.86K
+|
+08:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: cs_bill_customer_sk = c_customer_sk
+| runtime filters: RF008 <- c_customer_sk
+| row-size=68B cardinality=181.77K
+|
+|--17:EXCHANGE [BROADCAST]
+| |
+| 03:SCAN HDFS [tpcds.customer]
+| HDFS partitions=1/1 files=1 size=12.60MB
+| predicates: c_birth_month IN (9, 5, 12, 4, 1, 10)
+| runtime filters: RF002 -> c_current_cdemo_sk, RF006 -> c_current_addr_sk
+| row-size=20B cardinality=50.00K
+|
+07:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: cs_sold_date_sk = d_date_sk
+| runtime filters: RF010 <- d_date_sk
+| row-size=48B cardinality=294.63K
+|
+|--16:EXCHANGE [BROADCAST]
+| |
+| 05:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_year = 2001
+| row-size=8B cardinality=373
+|
+00:SCAN HDFS [tpcds.catalog_sales]
+ HDFS partitions=1/1 files=1 size=282.20MB
+ runtime filters: RF000 -> cs_item_sk, RF004 -> cs_bill_cdemo_sk, RF008 -> cs_bill_customer_sk, RF010 -> cs_sold_date_sk
+ row-size=40B cardinality=1.44M
+====
+# TPCDS-Q22
+select i_product_name
+ ,i_brand
+ ,i_class
+ ,i_category
+ ,avg(inv_quantity_on_hand) qoh
+ from inventory
+ ,date_dim
+ ,item
+ ,warehouse
+ where inv_date_sk=d_date_sk
+ and inv_item_sk=i_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and d_month_seq between 1212 and 1212 + 11
+ group by rollup(i_product_name
+ ,i_brand
+ ,i_class
+ ,i_category)
+order by qoh, i_product_name, i_brand, i_class, i_category
+limit 100;
+---- PLAN
+Max Per-Host Resource Reservation: Memory=149.44MB Threads=5
+Per-Host Resource Estimates: Memory=17.43GB
+PLAN-ROOT SINK
+|
+09:TOP-N [LIMIT=100]
+| order by: aggif(valid_tid(5,7,9,11,13) IN (5, 7, 9, 11, 13), CASE valid_tid(5,7,9,11,13) WHEN 5 THEN avg(inv_quantity_on_hand) WHEN 7 THEN avg(inv_quantity_on_hand) WHEN 9 THEN avg(inv_quantity_on_hand) WHEN 11 THEN avg(inv_quantity_on_hand) WHEN 13 THEN avg(inv_quantity_on_hand) END) ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_product_name WHEN 7 THEN i_product_name WHEN 9 THEN i_product_name WHEN 11 THEN i_product_name WHEN 13 THEN NULL END ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 [...]
+| row-size=56B cardinality=100
+|
+08:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(5,7,9,11,13) IN (5, 7, 9, 11, 13), CASE valid_tid(5,7,9,11,13) WHEN 5 THEN avg(inv_quantity_on_hand) WHEN 7 THEN avg(inv_quantity_on_hand) WHEN 9 THEN avg(inv_quantity_on_hand) WHEN 11 THEN avg(inv_quantity_on_hand) WHEN 13 THEN avg(inv_quantity_on_hand) END)
+| group by: CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_product_name WHEN 7 THEN i_product_name WHEN 9 THEN i_product_name WHEN 11 THEN i_product_name WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_brand WHEN 7 THEN i_brand WHEN 9 THEN i_brand WHEN 11 THEN NULL WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_class WHEN 7 THEN i_class WHEN 9 THEN NULL WHEN 11 THEN NULL WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_category WHEN 7 THEN N [...]
+| row-size=60B cardinality=35.25M
+|
+07:AGGREGATE [FINALIZE]
+| Class 0
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, i_category
+| Class 1
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, NULL
+| Class 2
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, NULL, NULL
+| Class 3
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, NULL, NULL, NULL
+| Class 4
+| output: avg(inv_quantity_on_hand)
+| group by: NULL, NULL, NULL, NULL
+| row-size=422B cardinality=35.25M
+|
+06:HASH JOIN [INNER JOIN]
+| hash predicates: inv_warehouse_sk = w_warehouse_sk
+| runtime filters: RF000 <- w_warehouse_sk
+| row-size=136B cardinality=11.74M
+|
+|--03:SCAN HDFS [tpcds.warehouse]
+| HDFS partitions=1/1 files=1 size=585B
+| row-size=4B cardinality=5
+|
+05:HASH JOIN [INNER JOIN]
+| hash predicates: inv_item_sk = i_item_sk
+| runtime filters: RF002 <- i_item_sk
+| row-size=132B cardinality=11.74M
+|
+|--02:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=104B cardinality=18.00K
+|
+04:HASH JOIN [INNER JOIN]
+| hash predicates: inv_date_sk = d_date_sk
+| runtime filters: RF004 <- d_date_sk
+| row-size=28B cardinality=11.74M
+|
+|--01:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_month_seq <= 1223, d_month_seq >= 1212
+| row-size=8B cardinality=7.30K
+|
+00:SCAN HDFS [tpcds.inventory]
+ HDFS partitions=1/1 files=1 size=225.47MB
+ runtime filters: RF000 -> inv_warehouse_sk, RF002 -> inv_item_sk, RF004 -> inv_date_sk
+ row-size=20B cardinality=11.74M
+---- DISTRIBUTEDPLAN
+Max Per-Host Resource Reservation: Memory=293.20MB Threads=10
+Per-Host Resource Estimates: Memory=41.64GB
+PLAN-ROOT SINK
+|
+15:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: aggif(valid_tid(5,7,9,11,13) IN (5, 7, 9, 11, 13), CASE valid_tid(5,7,9,11,13) WHEN 5 THEN avg(inv_quantity_on_hand) WHEN 7 THEN avg(inv_quantity_on_hand) WHEN 9 THEN avg(inv_quantity_on_hand) WHEN 11 THEN avg(inv_quantity_on_hand) WHEN 13 THEN avg(inv_quantity_on_hand) END) ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_product_name WHEN 7 THEN i_product_name WHEN 9 THEN i_product_name WHEN 11 THEN i_product_name WHEN 13 THEN NULL END ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 [...]
+| limit: 100
+|
+09:TOP-N [LIMIT=100]
+| order by: aggif(valid_tid(5,7,9,11,13) IN (5, 7, 9, 11, 13), CASE valid_tid(5,7,9,11,13) WHEN 5 THEN avg(inv_quantity_on_hand) WHEN 7 THEN avg(inv_quantity_on_hand) WHEN 9 THEN avg(inv_quantity_on_hand) WHEN 11 THEN avg(inv_quantity_on_hand) WHEN 13 THEN avg(inv_quantity_on_hand) END) ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_product_name WHEN 7 THEN i_product_name WHEN 9 THEN i_product_name WHEN 11 THEN i_product_name WHEN 13 THEN NULL END ASC, CASE valid_tid(5,7,9,11,13) WHEN 5 [...]
+| row-size=56B cardinality=100
+|
+08:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(5,7,9,11,13) IN (5, 7, 9, 11, 13), CASE valid_tid(5,7,9,11,13) WHEN 5 THEN avg(inv_quantity_on_hand) WHEN 7 THEN avg(inv_quantity_on_hand) WHEN 9 THEN avg(inv_quantity_on_hand) WHEN 11 THEN avg(inv_quantity_on_hand) WHEN 13 THEN avg(inv_quantity_on_hand) END)
+| group by: CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_product_name WHEN 7 THEN i_product_name WHEN 9 THEN i_product_name WHEN 11 THEN i_product_name WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_brand WHEN 7 THEN i_brand WHEN 9 THEN i_brand WHEN 11 THEN NULL WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_class WHEN 7 THEN i_class WHEN 9 THEN NULL WHEN 11 THEN NULL WHEN 13 THEN NULL END, CASE valid_tid(5,7,9,11,13) WHEN 5 THEN i_category WHEN 7 THEN N [...]
+| row-size=60B cardinality=35.25M
+|
+14:AGGREGATE [FINALIZE]
+| Class 0
+| output: avg:merge(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, i_category
+| Class 1
+| output: avg:merge(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, NULL
+| Class 2
+| output: avg:merge(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, NULL, NULL
+| Class 3
+| output: avg:merge(inv_quantity_on_hand)
+| group by: i_product_name, NULL, NULL, NULL
+| Class 4
+| output: avg:merge(inv_quantity_on_hand)
+| group by: NULL, NULL, NULL, NULL
+| row-size=422B cardinality=35.25M
+|
+13:EXCHANGE [HASH(CASE valid_tid(4,6,8,10,12) WHEN 4 THEN murmur_hash(i_product_name) WHEN 6 THEN murmur_hash(i_product_name) WHEN 8 THEN murmur_hash(i_product_name) WHEN 10 THEN murmur_hash(i_product_name) WHEN 12 THEN murmur_hash(NULL) END,CASE valid_tid(4,6,8,10,12) WHEN 4 THEN murmur_hash(i_brand) WHEN 6 THEN murmur_hash(i_brand) WHEN 8 THEN murmur_hash(i_brand) WHEN 10 THEN murmur_hash(NULL) WHEN 12 THEN murmur_hash(NULL) END,CASE valid_tid(4,6,8,10,12) WHEN 4 THEN murmur_hash(i_cla [...]
+|
+07:AGGREGATE [STREAMING]
+| Class 0
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, i_category
+| Class 1
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, i_class, NULL
+| Class 2
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, i_brand, NULL, NULL
+| Class 3
+| output: avg(inv_quantity_on_hand)
+| group by: i_product_name, NULL, NULL, NULL
+| Class 4
+| output: avg(inv_quantity_on_hand)
+| group by: NULL, NULL, NULL, NULL
+| row-size=422B cardinality=35.25M
+|
+06:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: inv_warehouse_sk = w_warehouse_sk
+| runtime filters: RF000 <- w_warehouse_sk
+| row-size=136B cardinality=11.74M
+|
+|--12:EXCHANGE [BROADCAST]
+| |
+| 03:SCAN HDFS [tpcds.warehouse]
+| HDFS partitions=1/1 files=1 size=585B
+| row-size=4B cardinality=5
+|
+05:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: inv_item_sk = i_item_sk
+| runtime filters: RF002 <- i_item_sk
+| row-size=132B cardinality=11.74M
+|
+|--11:EXCHANGE [BROADCAST]
+| |
+| 02:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=104B cardinality=18.00K
+|
+04:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: inv_date_sk = d_date_sk
+| runtime filters: RF004 <- d_date_sk
+| row-size=28B cardinality=11.74M
+|
+|--10:EXCHANGE [BROADCAST]
+| |
+| 01:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_month_seq <= 1223, d_month_seq >= 1212
+| row-size=8B cardinality=7.30K
+|
+00:SCAN HDFS [tpcds.inventory]
+ HDFS partitions=1/1 files=1 size=225.47MB
+ runtime filters: RF000 -> inv_warehouse_sk, RF002 -> inv_item_sk, RF004 -> inv_date_sk
+ row-size=20B cardinality=11.74M
+====
+# TPCDS-Q67
+select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100;
+---- PLAN
+Max Per-Host Resource Reservation: Memory=251.75MB Threads=5
+Per-Host Resource Estimates: Memory=18.41GB
+PLAN-ROOT SINK
+|
+12:TOP-N [LIMIT=100]
+| order by: i_category ASC, i_class ASC, i_brand ASC, i_product_name ASC, d_year ASC, d_qoy ASC, d_moy ASC, s_store_id ASC, sumsales ASC, rk ASC
+| row-size=96B cardinality=100
+|
+11:SELECT
+| predicates: rank() <= 100
+| row-size=100B cardinality=1.51M
+|
+10:ANALYTIC
+| functions: rank()
+| partition by: i_category
+| order by: aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 6 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 7 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 8 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 9 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 10 THEN su [...]
+| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| row-size=100B cardinality=15.09M
+|
+09:SORT
+| order by: CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_category WHEN 5 THEN i_category WHEN 6 THEN i_category WHEN 7 THEN i_category WHEN 8 THEN i_category WHEN 9 THEN i_category WHEN 10 THEN i_category WHEN 11 THEN i_category WHEN 12 THEN NULL END ASC NULLS FIRST, aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_qua [...]
+| row-size=92B cardinality=15.09M
+|
+08:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 6 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 7 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 8 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 9 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 10 THEN sum( [...]
+| group by: CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_category WHEN 5 THEN i_category WHEN 6 THEN i_category WHEN 7 THEN i_category WHEN 8 THEN i_category WHEN 9 THEN i_category WHEN 10 THEN i_category WHEN 11 THEN i_category WHEN 12 THEN NULL END, CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_class WHEN 5 THEN i_class WHEN 6 THEN i_class WHEN 7 THEN i_class WHEN 8 THEN i_class WHEN 9 THEN i_class WHEN 10 THEN i_class WHEN 11 THEN NULL WHEN 12 THEN NULL END, CASE valid_tid [...]
+| row-size=92B cardinality=15.09M
+|
+07:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id
+| Class 1
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, NULL
+| Class 2
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, NULL, NULL
+| Class 3
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, NULL, NULL, NULL
+| Class 4
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, NULL, NULL, NULL, NULL
+| Class 5
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, NULL, NULL, NULL, NULL, NULL
+| Class 6
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 7
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 8
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| row-size=1.07KB cardinality=15.09M
+|
+06:HASH JOIN [INNER JOIN]
+| hash predicates: ss_item_sk = i_item_sk
+| runtime filters: RF000 <- i_item_sk
+| row-size=180B cardinality=2.88M
+|
+|--03:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=104B cardinality=18.00K
+|
+05:HASH JOIN [INNER JOIN]
+| hash predicates: ss_store_sk = s_store_sk
+| runtime filters: RF002 <- s_store_sk
+| row-size=76B cardinality=2.88M
+|
+|--02:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=32B cardinality=12
+|
+04:HASH JOIN [INNER JOIN]
+| hash predicates: ss_sold_date_sk = d_date_sk
+| runtime filters: RF004 <- d_date_sk
+| row-size=44B cardinality=2.88M
+|
+|--01:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_month_seq <= 1223, d_month_seq >= 1212
+| row-size=20B cardinality=7.30K
+|
+00:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss_item_sk, RF002 -> ss_store_sk, RF004 -> ss_sold_date_sk
+ row-size=24B cardinality=2.88M
+---- DISTRIBUTEDPLAN
+Max Per-Host Resource Reservation: Memory=505.51MB Threads=11
+Per-Host Resource Estimates: Memory=53.08GB
+PLAN-ROOT SINK
+|
+19:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: i_category ASC, i_class ASC, i_brand ASC, i_product_name ASC, d_year ASC, d_qoy ASC, d_moy ASC, s_store_id ASC, sumsales ASC, rk ASC
+| limit: 100
+|
+12:TOP-N [LIMIT=100]
+| order by: i_category ASC, i_class ASC, i_brand ASC, i_product_name ASC, d_year ASC, d_qoy ASC, d_moy ASC, s_store_id ASC, sumsales ASC, rk ASC
+| row-size=96B cardinality=100
+|
+11:SELECT
+| predicates: rank() <= 100
+| row-size=100B cardinality=1.51M
+|
+10:ANALYTIC
+| functions: rank()
+| partition by: i_category
+| order by: aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 6 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 7 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 8 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 9 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 10 THEN su [...]
+| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| row-size=100B cardinality=15.09M
+|
+09:SORT
+| order by: CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_category WHEN 5 THEN i_category WHEN 6 THEN i_category WHEN 7 THEN i_category WHEN 8 THEN i_category WHEN 9 THEN i_category WHEN 10 THEN i_category WHEN 11 THEN i_category WHEN 12 THEN NULL END ASC NULLS FIRST, aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_qua [...]
+| row-size=92B cardinality=15.09M
+|
+18:EXCHANGE [HASH(CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_category WHEN 5 THEN i_category WHEN 6 THEN i_category WHEN 7 THEN i_category WHEN 8 THEN i_category WHEN 9 THEN i_category WHEN 10 THEN i_category WHEN 11 THEN i_category WHEN 12 THEN NULL END)]
+|
+08:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(4,5,6,7,8,9,10,11,12) IN (4, 5, 6, 7, 8, 9, 10, 11, 12), CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 5 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 6 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 7 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 8 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 9 THEN sum(coalesce(ss_sales_price * ss_quantity, 0)) WHEN 10 THEN sum( [...]
+| group by: CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_category WHEN 5 THEN i_category WHEN 6 THEN i_category WHEN 7 THEN i_category WHEN 8 THEN i_category WHEN 9 THEN i_category WHEN 10 THEN i_category WHEN 11 THEN i_category WHEN 12 THEN NULL END, CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN i_class WHEN 5 THEN i_class WHEN 6 THEN i_class WHEN 7 THEN i_class WHEN 8 THEN i_class WHEN 9 THEN i_class WHEN 10 THEN i_class WHEN 11 THEN NULL WHEN 12 THEN NULL END, CASE valid_tid [...]
+| row-size=92B cardinality=15.09M
+|
+17:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id
+| Class 1
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, NULL
+| Class 2
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, NULL, NULL
+| Class 3
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, NULL, NULL, NULL
+| Class 4
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, NULL, NULL, NULL, NULL
+| Class 5
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, NULL, NULL, NULL, NULL, NULL
+| Class 6
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 7
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 8
+| output: sum:merge(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| row-size=1.07KB cardinality=15.09M
+|
+16:EXCHANGE [HASH(CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN murmur_hash(i_category) WHEN 5 THEN murmur_hash(i_category) WHEN 6 THEN murmur_hash(i_category) WHEN 7 THEN murmur_hash(i_category) WHEN 8 THEN murmur_hash(i_category) WHEN 9 THEN murmur_hash(i_category) WHEN 10 THEN murmur_hash(i_category) WHEN 11 THEN murmur_hash(i_category) WHEN 12 THEN murmur_hash(NULL) END,CASE valid_tid(4,5,6,7,8,9,10,11,12) WHEN 4 THEN murmur_hash(i_class) WHEN 5 THEN murmur_hash(i_class) WHEN 6 TH [...]
+|
+07:AGGREGATE [STREAMING]
+| Class 0
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id
+| Class 1
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, NULL
+| Class 2
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, d_qoy, NULL, NULL
+| Class 3
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, d_year, NULL, NULL, NULL
+| Class 4
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, i_product_name, NULL, NULL, NULL, NULL
+| Class 5
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, i_brand, NULL, NULL, NULL, NULL, NULL
+| Class 6
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, i_class, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 7
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: i_category, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| Class 8
+| output: sum(coalesce(ss_sales_price * ss_quantity, 0))
+| group by: NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
+| row-size=1.07KB cardinality=15.09M
+|
+06:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss_item_sk = i_item_sk
+| runtime filters: RF000 <- i_item_sk
+| row-size=180B cardinality=2.88M
+|
+|--15:EXCHANGE [BROADCAST]
+| |
+| 03:SCAN HDFS [tpcds.item]
+| HDFS partitions=1/1 files=1 size=4.82MB
+| row-size=104B cardinality=18.00K
+|
+05:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss_store_sk = s_store_sk
+| runtime filters: RF002 <- s_store_sk
+| row-size=76B cardinality=2.88M
+|
+|--14:EXCHANGE [BROADCAST]
+| |
+| 02:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=32B cardinality=12
+|
+04:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss_sold_date_sk = d_date_sk
+| runtime filters: RF004 <- d_date_sk
+| row-size=44B cardinality=2.88M
+|
+|--13:EXCHANGE [BROADCAST]
+| |
+| 01:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_month_seq <= 1223, d_month_seq >= 1212
+| row-size=20B cardinality=7.30K
+|
+00:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss_item_sk, RF002 -> ss_store_sk, RF004 -> ss_sold_date_sk
+ row-size=24B cardinality=2.88M
+====
+# TPCDS-Q5
+with ssr as
+ (select s_store_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select ss_store_sk as store_sk,
+ ss_sold_date_sk as date_sk,
+ ss_ext_sales_price as sales_price,
+ ss_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from store_sales
+ union all
+ select sr_store_sk as store_sk,
+ sr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ sr_return_amt as return_amt,
+ sr_net_loss as net_loss
+ from store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and store_sk = s_store_sk
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select cs_catalog_page_sk as page_sk,
+ cs_sold_date_sk as date_sk,
+ cs_ext_sales_price as sales_price,
+ cs_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from catalog_sales
+ union all
+ select cr_catalog_page_sk as page_sk,
+ cr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ cr_return_amount as return_amt,
+ cr_net_loss as net_loss
+ from catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and page_sk = cp_catalog_page_sk
+ group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select ws_web_site_sk as wsr_web_site_sk,
+ ws_sold_date_sk as date_sk,
+ ws_ext_sales_price as sales_price,
+ ws_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from web_sales
+ union all
+ select ws_web_site_sk as wsr_web_site_sk,
+ wr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ wr_return_amt as return_amt,
+ wr_net_loss as net_loss
+ from web_returns left outer join web_sales on
+ ( wr_item_sk = ws_item_sk
+ and wr_order_number = ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and wsr_web_site_sk = web_site_sk
+ group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(`returns`) as `returns`
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || s_store_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || cp_catalog_page_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100;
+---- PLAN
+Max Per-Host Resource Reservation: Memory=47.39MB Threads=5
+Per-Host Resource Estimates: Memory=333MB
+PLAN-ROOT SINK
+|
+29:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
+| row-size=72B cardinality=100
+|
+28:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(sales) WHEN 28 THEN sum(sales) WHEN 29 THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(`returns`) WHEN 28 THEN sum(`returns`) WHEN 29 THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(profit) WHEN 28 THEN sum(profit) WHEN 29 THEN sum(profit) END)
+| group by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN 27 WHEN 28 THEN 28 WHEN 29 THEN 29 END
+| row-size=76B cardinality=11.56K
+|
+27:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, id
+| Class 1
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum(sales), sum(returns), sum(profit)
+| group by: NULL, NULL
+| row-size=216B cardinality=11.56K
+|
+00:UNION
+| row-size=72B cardinality=11.56K
+|
+|--26:AGGREGATE [FINALIZE]
+| | output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| | group by: web_site_id
+| | row-size=92B cardinality=15
+| |
+| 25:HASH JOIN [INNER JOIN]
+| | hash predicates: wsr_web_site_sk = web_site_sk
+| | runtime filters: RF008 <- web_site_sk
+| | row-size=82B cardinality=791.15K
+| |
+| |--23:SCAN HDFS [tpcds.web_site]
+| | HDFS partitions=1/1 files=1 size=8.57KB
+| | row-size=32B cardinality=30
+| |
+| 24:HASH JOIN [INNER JOIN]
+| | hash predicates: date_sk = d_date_sk
+| | runtime filters: RF010 <- d_date_sk
+| | row-size=50B cardinality=791.15K
+| |
+| |--22:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 17:UNION
+| | row-size=24B cardinality=791.15K
+| |
+| |--21:HASH JOIN [RIGHT OUTER JOIN]
+| | | hash predicates: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
+| | | runtime filters: RF012 <- wr_item_sk, RF013 <- wr_order_number
+| | | row-size=48B cardinality=71.76K
+| | |
+| | |--19:SCAN HDFS [tpcds.web_returns]
+| | | HDFS partitions=1/1 files=1 size=9.35MB
+| | | runtime filters: RF010 -> tpcds.web_returns.wr_returned_date_sk
+| | | row-size=28B cardinality=71.76K
+| | |
+| | 20:SCAN HDFS [tpcds.web_sales]
+| | HDFS partitions=1/1 files=1 size=140.07MB
+| | runtime filters: RF008 -> tpcds.web_sales.ws_web_site_sk, RF012 -> ws_item_sk, RF013 -> ws_order_number
+| | row-size=20B cardinality=719.38K
+| |
+| 18:SCAN HDFS [tpcds.web_sales]
+| HDFS partitions=1/1 files=1 size=140.07MB
+| runtime filters: RF008 -> tpcds.web_sales.ws_web_site_sk, RF010 -> tpcds.web_sales.ws_sold_date_sk
+| row-size=16B cardinality=719.38K
+|
+|--16:AGGREGATE [FINALIZE]
+| | output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| | group by: cp_catalog_page_id
+| | row-size=92B cardinality=11.54K
+| |
+| 15:HASH JOIN [INNER JOIN]
+| | hash predicates: page_sk = cp_catalog_page_sk
+| | runtime filters: RF004 <- cp_catalog_page_sk
+| | row-size=82B cardinality=1.59M
+| |
+| |--13:SCAN HDFS [tpcds.catalog_page]
+| | HDFS partitions=1/1 files=1 size=1.56MB
+| | row-size=32B cardinality=11.72K
+| |
+| 14:HASH JOIN [INNER JOIN]
+| | hash predicates: date_sk = d_date_sk
+| | runtime filters: RF006 <- d_date_sk
+| | row-size=50B cardinality=1.59M
+| |
+| |--12:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 09:UNION
+| | row-size=24B cardinality=1.59M
+| |
+| |--11:SCAN HDFS [tpcds.catalog_returns]
+| | HDFS partitions=1/1 files=1 size=20.39MB
+| | runtime filters: RF004 -> tpcds.catalog_returns.cr_catalog_page_sk, RF006 -> tpcds.catalog_returns.cr_returned_date_sk
+| | row-size=16B cardinality=144.07K
+| |
+| 10:SCAN HDFS [tpcds.catalog_sales]
+| HDFS partitions=1/1 files=1 size=282.20MB
+| runtime filters: RF004 -> tpcds.catalog_sales.cs_catalog_page_sk, RF006 -> tpcds.catalog_sales.cs_sold_date_sk
+| row-size=16B cardinality=1.44M
+|
+08:AGGREGATE [FINALIZE]
+| output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| group by: s_store_id
+| row-size=92B cardinality=6
+|
+07:HASH JOIN [INNER JOIN]
+| hash predicates: store_sk = s_store_sk
+| runtime filters: RF000 <- s_store_sk
+| row-size=82B cardinality=3.17M
+|
+|--05:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=32B cardinality=12
+|
+06:HASH JOIN [INNER JOIN]
+| hash predicates: date_sk = d_date_sk
+| runtime filters: RF002 <- d_date_sk
+| row-size=50B cardinality=3.17M
+|
+|--04:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| row-size=26B cardinality=7.30K
+|
+01:UNION
+| row-size=24B cardinality=3.17M
+|
+|--03:SCAN HDFS [tpcds.store_returns]
+| HDFS partitions=1/1 files=1 size=31.19MB
+| runtime filters: RF000 -> tpcds.store_returns.sr_store_sk, RF002 -> tpcds.store_returns.sr_returned_date_sk
+| row-size=16B cardinality=287.51K
+|
+02:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> tpcds.store_sales.ss_store_sk, RF002 -> tpcds.store_sales.ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+---- DISTRIBUTEDPLAN
+Max Per-Host Resource Reservation: Memory=114.27MB Threads=25
+Per-Host Resource Estimates: Memory=1.02GB
+PLAN-ROOT SINK
+|
+46:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
+| limit: 100
+|
+29:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END ASC, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END ASC
+| row-size=72B cardinality=100
+|
+28:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(sales) WHEN 28 THEN sum(sales) WHEN 29 THEN sum(sales) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(`returns`) WHEN 28 THEN sum(`returns`) WHEN 29 THEN sum(`returns`) END), aggif(valid_tid(27,28,29) IN (27, 28, 29), CASE valid_tid(27,28,29) WHEN 27 THEN sum(profit) WHEN 28 THEN sum(profit) WHEN 29 THEN sum(profit) END)
+| group by: CASE valid_tid(27,28,29) WHEN 27 THEN channel WHEN 28 THEN channel WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN id WHEN 28 THEN NULL WHEN 29 THEN NULL END, CASE valid_tid(27,28,29) WHEN 27 THEN 27 WHEN 28 THEN 28 WHEN 29 THEN 29 END
+| row-size=76B cardinality=11.56K
+|
+45:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: channel, id
+| Class 1
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: NULL, NULL
+| row-size=216B cardinality=11.56K
+|
+44:EXCHANGE [HASH(CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(channel) WHEN 28 THEN murmur_hash(channel) WHEN 29 THEN murmur_hash(NULL) END,CASE valid_tid(27,28,29) WHEN 27 THEN murmur_hash(id) WHEN 28 THEN murmur_hash(NULL) WHEN 29 THEN murmur_hash(NULL) END)]
+|
+27:AGGREGATE [STREAMING]
+| Class 0
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, id
+| Class 1
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum(sales), sum(returns), sum(profit)
+| group by: NULL, NULL
+| row-size=216B cardinality=11.56K
+|
+00:UNION
+| row-size=72B cardinality=11.56K
+|
+|--43:AGGREGATE [FINALIZE]
+| | output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
+| | group by: web_site_id
+| | row-size=92B cardinality=15
+| |
+| 42:EXCHANGE [HASH(web_site_id)]
+| |
+| 26:AGGREGATE [STREAMING]
+| | output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| | group by: web_site_id
+| | row-size=92B cardinality=15
+| |
+| 25:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: wsr_web_site_sk = web_site_sk
+| | runtime filters: RF008 <- web_site_sk
+| | row-size=82B cardinality=791.15K
+| |
+| |--41:EXCHANGE [BROADCAST]
+| | |
+| | 23:SCAN HDFS [tpcds.web_site]
+| | HDFS partitions=1/1 files=1 size=8.57KB
+| | row-size=32B cardinality=30
+| |
+| 24:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: date_sk = d_date_sk
+| | runtime filters: RF010 <- d_date_sk
+| | row-size=50B cardinality=791.15K
+| |
+| |--40:EXCHANGE [BROADCAST]
+| | |
+| | 22:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 17:UNION
+| | row-size=24B cardinality=791.15K
+| |
+| |--21:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
+| | | hash predicates: ws_item_sk = wr_item_sk, ws_order_number = wr_order_number
+| | | runtime filters: RF012 <- wr_item_sk, RF013 <- wr_order_number
+| | | row-size=48B cardinality=71.76K
+| | |
+| | |--39:EXCHANGE [HASH(wr_item_sk,wr_order_number)]
+| | | |
+| | | 19:SCAN HDFS [tpcds.web_returns]
+| | | HDFS partitions=1/1 files=1 size=9.35MB
+| | | runtime filters: RF010 -> tpcds.web_returns.wr_returned_date_sk
+| | | row-size=28B cardinality=71.76K
+| | |
+| | 38:EXCHANGE [HASH(ws_item_sk,ws_order_number)]
+| | |
+| | 20:SCAN HDFS [tpcds.web_sales]
+| | HDFS partitions=1/1 files=1 size=140.07MB
+| | runtime filters: RF008 -> tpcds.web_sales.ws_web_site_sk, RF012 -> ws_item_sk, RF013 -> ws_order_number
+| | row-size=20B cardinality=719.38K
+| |
+| 18:SCAN HDFS [tpcds.web_sales]
+| HDFS partitions=1/1 files=1 size=140.07MB
+| runtime filters: RF008 -> tpcds.web_sales.ws_web_site_sk, RF010 -> tpcds.web_sales.ws_sold_date_sk
+| row-size=16B cardinality=719.38K
+|
+|--37:AGGREGATE [FINALIZE]
+| | output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
+| | group by: cp_catalog_page_id
+| | row-size=92B cardinality=11.54K
+| |
+| 36:EXCHANGE [HASH(cp_catalog_page_id)]
+| |
+| 16:AGGREGATE [STREAMING]
+| | output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| | group by: cp_catalog_page_id
+| | row-size=92B cardinality=11.54K
+| |
+| 15:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: page_sk = cp_catalog_page_sk
+| | runtime filters: RF004 <- cp_catalog_page_sk
+| | row-size=82B cardinality=1.59M
+| |
+| |--35:EXCHANGE [BROADCAST]
+| | |
+| | 13:SCAN HDFS [tpcds.catalog_page]
+| | HDFS partitions=1/1 files=1 size=1.56MB
+| | row-size=32B cardinality=11.72K
+| |
+| 14:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: date_sk = d_date_sk
+| | runtime filters: RF006 <- d_date_sk
+| | row-size=50B cardinality=1.59M
+| |
+| |--34:EXCHANGE [BROADCAST]
+| | |
+| | 12:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 09:UNION
+| | row-size=24B cardinality=1.59M
+| |
+| |--11:SCAN HDFS [tpcds.catalog_returns]
+| | HDFS partitions=1/1 files=1 size=20.39MB
+| | runtime filters: RF004 -> tpcds.catalog_returns.cr_catalog_page_sk, RF006 -> tpcds.catalog_returns.cr_returned_date_sk
+| | row-size=16B cardinality=144.07K
+| |
+| 10:SCAN HDFS [tpcds.catalog_sales]
+| HDFS partitions=1/1 files=1 size=282.20MB
+| runtime filters: RF004 -> tpcds.catalog_sales.cs_catalog_page_sk, RF006 -> tpcds.catalog_sales.cs_sold_date_sk
+| row-size=16B cardinality=1.44M
+|
+33:AGGREGATE [FINALIZE]
+| output: sum:merge(sales_price), sum:merge(profit), sum:merge(return_amt), sum:merge(net_loss)
+| group by: s_store_id
+| row-size=92B cardinality=6
+|
+32:EXCHANGE [HASH(s_store_id)]
+|
+08:AGGREGATE [STREAMING]
+| output: sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)
+| group by: s_store_id
+| row-size=92B cardinality=6
+|
+07:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: store_sk = s_store_sk
+| runtime filters: RF000 <- s_store_sk
+| row-size=82B cardinality=3.17M
+|
+|--31:EXCHANGE [BROADCAST]
+| |
+| 05:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=32B cardinality=12
+|
+06:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: date_sk = d_date_sk
+| runtime filters: RF002 <- d_date_sk
+| row-size=50B cardinality=3.17M
+|
+|--30:EXCHANGE [BROADCAST]
+| |
+| 04:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_date <= DATE '1998-08-18', d_date >= DATE '1998-08-04'
+| row-size=26B cardinality=7.30K
+|
+01:UNION
+| row-size=24B cardinality=3.17M
+|
+|--03:SCAN HDFS [tpcds.store_returns]
+| HDFS partitions=1/1 files=1 size=31.19MB
+| runtime filters: RF000 -> tpcds.store_returns.sr_store_sk, RF002 -> tpcds.store_returns.sr_returned_date_sk
+| row-size=16B cardinality=287.51K
+|
+02:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> tpcds.store_sales.ss_store_sk, RF002 -> tpcds.store_sales.ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+====
+# TPCDS-Q77
+with ss as
+ (select s_store_sk,
+ sum(ss_ext_sales_price) as sales,
+ sum(ss_net_profit) as profit
+ from store_sales,
+ date_dim,
+ store
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and ss_store_sk = s_store_sk
+ group by s_store_sk)
+ ,
+ sr as
+ (select s_store_sk,
+ sum(sr_return_amt) as `returns`,
+ sum(sr_net_loss) as profit_loss
+ from store_returns,
+ date_dim,
+ store
+ where sr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and sr_store_sk = s_store_sk
+ group by s_store_sk),
+ cs as
+ (select cs_call_center_sk,
+ sum(cs_ext_sales_price) as sales,
+ sum(cs_net_profit) as profit
+ from catalog_sales,
+ date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ group by cs_call_center_sk
+ ),
+ cr as
+ (select
+ sum(cr_return_amount) as `returns`,
+ sum(cr_net_loss) as profit_loss
+ from catalog_returns,
+ date_dim
+ where cr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ ),
+ ws as
+ ( select wp_web_page_sk,
+ sum(ws_ext_sales_price) as sales,
+ sum(ws_net_profit) as profit
+ from web_sales,
+ date_dim,
+ web_page
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and ws_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk),
+ wr as
+ (select wp_web_page_sk,
+ sum(wr_return_amt) as `returns`,
+ sum(wr_net_loss) as profit_loss
+ from web_returns,
+ date_dim,
+ web_page
+ where wr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and wr_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(`returns`) as `returns`
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , ss.s_store_sk as id
+ , sales
+ , coalesce(`returns`, 0) as `returns`
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ss left join sr
+ on ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+ , cs_call_center_sk as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from cs
+ , cr
+ union all
+ select 'web channel' as channel
+ , ws.wp_web_page_sk as id
+ , sales
+ , coalesce(`returns`, 0) `returns`
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ws left join wr
+ on ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ ,sales desc
+ limit 100;
+---- PLAN
+Max Per-Host Resource Reservation: Memory=55.58MB Threads=7
+Per-Host Resource Estimates: Memory=416MB
+PLAN-ROOT SINK
+|
+38:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(32,30,31) WHEN 30 THEN channel WHEN 31 THEN channel WHEN 32 THEN NULL END ASC, CASE valid_tid(32,30,31) WHEN 30 THEN id WHEN 31 THEN NULL WHEN 32 THEN NULL END ASC, aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(sales) WHEN 31 THEN sum(sales) WHEN 32 THEN sum(sales) END) DESC
+| row-size=64B cardinality=82
+|
+37:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(sales) WHEN 31 THEN sum(sales) WHEN 32 THEN sum(sales) END), aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(`returns`) WHEN 31 THEN sum(`returns`) WHEN 32 THEN sum(`returns`) END), aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(profit) WHEN 31 THEN sum(profit) WHEN 32 THEN sum(profit) END)
+| group by: CASE valid_tid(32,30,31) WHEN 30 THEN channel WHEN 31 THEN channel WHEN 32 THEN NULL END, CASE valid_tid(32,30,31) WHEN 30 THEN id WHEN 31 THEN NULL WHEN 32 THEN NULL END, CASE valid_tid(32,30,31) WHEN 30 THEN 30 WHEN 31 THEN 31 WHEN 32 THEN 32 END
+| row-size=68B cardinality=82
+|
+36:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, id
+| Class 1
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum(sales), sum(returns), sum(profit)
+| group by: NULL, NULL
+| row-size=192B cardinality=82
+|
+00:UNION
+| row-size=64B cardinality=78
+|
+|--35:HASH JOIN [LEFT OUTER JOIN]
+| | hash predicates: wp_web_page_sk = wp_web_page_sk
+| | row-size=72B cardinality=60
+| |
+| |--34:AGGREGATE [FINALIZE]
+| | | output: sum(wr_return_amt), sum(wr_net_loss)
+| | | group by: wp_web_page_sk
+| | | row-size=36B cardinality=60
+| | |
+| | 33:HASH JOIN [INNER JOIN]
+| | | hash predicates: wr_web_page_sk = wp_web_page_sk
+| | | runtime filters: RF016 <- wp_web_page_sk
+| | | row-size=46B cardinality=71.76K
+| | |
+| | |--31:SCAN HDFS [tpcds.web_page]
+| | | HDFS partitions=1/1 files=1 size=5.64KB
+| | | row-size=4B cardinality=60
+| | |
+| | 32:HASH JOIN [INNER JOIN]
+| | | hash predicates: wr_returned_date_sk = d_date_sk
+| | | runtime filters: RF018 <- d_date_sk
+| | | row-size=42B cardinality=71.76K
+| | |
+| | |--30:SCAN HDFS [tpcds.date_dim]
+| | | HDFS partitions=1/1 files=1 size=9.84MB
+| | | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | | row-size=26B cardinality=7.30K
+| | |
+| | 29:SCAN HDFS [tpcds.web_returns]
+| | HDFS partitions=1/1 files=1 size=9.35MB
+| | runtime filters: RF016 -> wr_web_page_sk, RF018 -> wr_returned_date_sk
+| | row-size=16B cardinality=71.76K
+| |
+| 28:AGGREGATE [FINALIZE]
+| | output: sum(ws_ext_sales_price), sum(ws_net_profit)
+| | group by: wp_web_page_sk
+| | row-size=36B cardinality=60
+| |
+| 27:HASH JOIN [INNER JOIN]
+| | hash predicates: ws_web_page_sk = wp_web_page_sk
+| | runtime filters: RF012 <- wp_web_page_sk
+| | row-size=46B cardinality=719.38K
+| |
+| |--25:SCAN HDFS [tpcds.web_page]
+| | HDFS partitions=1/1 files=1 size=5.64KB
+| | row-size=4B cardinality=60
+| |
+| 26:HASH JOIN [INNER JOIN]
+| | hash predicates: ws_sold_date_sk = d_date_sk
+| | runtime filters: RF014 <- d_date_sk
+| | row-size=42B cardinality=719.38K
+| |
+| |--24:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 23:SCAN HDFS [tpcds.web_sales]
+| HDFS partitions=1/1 files=1 size=140.07MB
+| runtime filters: RF012 -> ws_web_page_sk, RF014 -> ws_sold_date_sk
+| row-size=16B cardinality=719.38K
+|
+|--22:NESTED LOOP JOIN [CROSS JOIN]
+| | row-size=68B cardinality=6
+| |
+| |--21:AGGREGATE [FINALIZE]
+| | | output: sum(cr_return_amount), sum(cr_net_loss)
+| | | row-size=32B cardinality=1
+| | |
+| | 20:HASH JOIN [INNER JOIN]
+| | | hash predicates: cr_returned_date_sk = d_date_sk
+| | | runtime filters: RF010 <- d_date_sk
+| | | row-size=38B cardinality=144.07K
+| | |
+| | |--19:SCAN HDFS [tpcds.date_dim]
+| | | HDFS partitions=1/1 files=1 size=9.84MB
+| | | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | | row-size=26B cardinality=7.30K
+| | |
+| | 18:SCAN HDFS [tpcds.catalog_returns]
+| | HDFS partitions=1/1 files=1 size=20.39MB
+| | runtime filters: RF010 -> cr_returned_date_sk
+| | row-size=12B cardinality=144.07K
+| |
+| 17:AGGREGATE [FINALIZE]
+| | output: sum(cs_ext_sales_price), sum(cs_net_profit)
+| | group by: cs_call_center_sk
+| | row-size=36B cardinality=6
+| |
+| 16:HASH JOIN [INNER JOIN]
+| | hash predicates: cs_sold_date_sk = d_date_sk
+| | runtime filters: RF008 <- d_date_sk
+| | row-size=42B cardinality=1.44M
+| |
+| |--15:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 14:SCAN HDFS [tpcds.catalog_sales]
+| HDFS partitions=1/1 files=1 size=282.20MB
+| runtime filters: RF008 -> cs_sold_date_sk
+| row-size=16B cardinality=1.44M
+|
+13:HASH JOIN [LEFT OUTER JOIN]
+| hash predicates: s_store_sk = s_store_sk
+| row-size=72B cardinality=12
+|
+|--12:AGGREGATE [FINALIZE]
+| | output: sum(sr_return_amt), sum(sr_net_loss)
+| | group by: s_store_sk
+| | row-size=36B cardinality=12
+| |
+| 11:HASH JOIN [INNER JOIN]
+| | hash predicates: sr_store_sk = s_store_sk
+| | runtime filters: RF004 <- s_store_sk
+| | row-size=46B cardinality=287.51K
+| |
+| |--09:SCAN HDFS [tpcds.store]
+| | HDFS partitions=1/1 files=1 size=3.08KB
+| | row-size=4B cardinality=12
+| |
+| 10:HASH JOIN [INNER JOIN]
+| | hash predicates: sr_returned_date_sk = d_date_sk
+| | runtime filters: RF006 <- d_date_sk
+| | row-size=42B cardinality=287.51K
+| |
+| |--08:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 07:SCAN HDFS [tpcds.store_returns]
+| HDFS partitions=1/1 files=1 size=31.19MB
+| runtime filters: RF004 -> sr_store_sk, RF006 -> sr_returned_date_sk
+| row-size=16B cardinality=287.51K
+|
+06:AGGREGATE [FINALIZE]
+| output: sum(ss_ext_sales_price), sum(ss_net_profit)
+| group by: s_store_sk
+| row-size=36B cardinality=12
+|
+05:HASH JOIN [INNER JOIN]
+| hash predicates: ss_store_sk = s_store_sk
+| runtime filters: RF000 <- s_store_sk
+| row-size=46B cardinality=2.88M
+|
+|--03:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=4B cardinality=12
+|
+04:HASH JOIN [INNER JOIN]
+| hash predicates: ss_sold_date_sk = d_date_sk
+| runtime filters: RF002 <- d_date_sk
+| row-size=42B cardinality=2.88M
+|
+|--02:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| row-size=26B cardinality=7.30K
+|
+01:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss_store_sk, RF002 -> ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+---- DISTRIBUTEDPLAN
+Max Per-Host Resource Reservation: Memory=154.97MB Threads=36
+Per-Host Resource Estimates: Memory=1.23GB
+PLAN-ROOT SINK
+|
+64:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: CASE valid_tid(32,30,31) WHEN 30 THEN channel WHEN 31 THEN channel WHEN 32 THEN NULL END ASC, CASE valid_tid(32,30,31) WHEN 30 THEN id WHEN 31 THEN NULL WHEN 32 THEN NULL END ASC, aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(sales) WHEN 31 THEN sum(sales) WHEN 32 THEN sum(sales) END) DESC
+| limit: 100
+|
+38:TOP-N [LIMIT=100]
+| order by: CASE valid_tid(32,30,31) WHEN 30 THEN channel WHEN 31 THEN channel WHEN 32 THEN NULL END ASC, CASE valid_tid(32,30,31) WHEN 30 THEN id WHEN 31 THEN NULL WHEN 32 THEN NULL END ASC, aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(sales) WHEN 31 THEN sum(sales) WHEN 32 THEN sum(sales) END) DESC
+| row-size=64B cardinality=82
+|
+37:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(sales) WHEN 31 THEN sum(sales) WHEN 32 THEN sum(sales) END), aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(`returns`) WHEN 31 THEN sum(`returns`) WHEN 32 THEN sum(`returns`) END), aggif(valid_tid(32,30,31) IN (30, 31, 32), CASE valid_tid(32,30,31) WHEN 30 THEN sum(profit) WHEN 31 THEN sum(profit) WHEN 32 THEN sum(profit) END)
+| group by: CASE valid_tid(32,30,31) WHEN 30 THEN channel WHEN 31 THEN channel WHEN 32 THEN NULL END, CASE valid_tid(32,30,31) WHEN 30 THEN id WHEN 31 THEN NULL WHEN 32 THEN NULL END, CASE valid_tid(32,30,31) WHEN 30 THEN 30 WHEN 31 THEN 31 WHEN 32 THEN 32 END
+| row-size=68B cardinality=82
+|
+63:AGGREGATE [FINALIZE]
+| Class 0
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: channel, id
+| Class 1
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum:merge(sales), sum:merge(`returns`), sum:merge(profit)
+| group by: NULL, NULL
+| row-size=192B cardinality=82
+|
+62:EXCHANGE [HASH(CASE valid_tid(32,30,31) WHEN 30 THEN murmur_hash(channel) WHEN 31 THEN murmur_hash(channel) WHEN 32 THEN murmur_hash(NULL) END,CASE valid_tid(32,30,31) WHEN 30 THEN murmur_hash(id) WHEN 31 THEN murmur_hash(NULL) WHEN 32 THEN murmur_hash(NULL) END)]
+|
+36:AGGREGATE [STREAMING]
+| Class 0
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, id
+| Class 1
+| output: sum(sales), sum(returns), sum(profit)
+| group by: channel, NULL
+| Class 2
+| output: sum(sales), sum(returns), sum(profit)
+| group by: NULL, NULL
+| row-size=192B cardinality=82
+|
+00:UNION
+| row-size=64B cardinality=78
+|
+|--35:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]
+| | hash predicates: wp_web_page_sk = wp_web_page_sk
+| | row-size=72B cardinality=60
+| |
+| |--61:AGGREGATE [FINALIZE]
+| | | output: sum:merge(wr_return_amt), sum:merge(wr_net_loss)
+| | | group by: wp_web_page_sk
+| | | row-size=36B cardinality=60
+| | |
+| | 60:EXCHANGE [HASH(wp_web_page_sk)]
+| | |
+| | 34:AGGREGATE [STREAMING]
+| | | output: sum(wr_return_amt), sum(wr_net_loss)
+| | | group by: wp_web_page_sk
+| | | row-size=36B cardinality=60
+| | |
+| | 33:HASH JOIN [INNER JOIN, BROADCAST]
+| | | hash predicates: wr_web_page_sk = wp_web_page_sk
+| | | runtime filters: RF016 <- wp_web_page_sk
+| | | row-size=46B cardinality=71.76K
+| | |
+| | |--59:EXCHANGE [BROADCAST]
+| | | |
+| | | 31:SCAN HDFS [tpcds.web_page]
+| | | HDFS partitions=1/1 files=1 size=5.64KB
+| | | row-size=4B cardinality=60
+| | |
+| | 32:HASH JOIN [INNER JOIN, BROADCAST]
+| | | hash predicates: wr_returned_date_sk = d_date_sk
+| | | runtime filters: RF018 <- d_date_sk
+| | | row-size=42B cardinality=71.76K
+| | |
+| | |--58:EXCHANGE [BROADCAST]
+| | | |
+| | | 30:SCAN HDFS [tpcds.date_dim]
+| | | HDFS partitions=1/1 files=1 size=9.84MB
+| | | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | | row-size=26B cardinality=7.30K
+| | |
+| | 29:SCAN HDFS [tpcds.web_returns]
+| | HDFS partitions=1/1 files=1 size=9.35MB
+| | runtime filters: RF016 -> wr_web_page_sk, RF018 -> wr_returned_date_sk
+| | row-size=16B cardinality=71.76K
+| |
+| 57:AGGREGATE [FINALIZE]
+| | output: sum:merge(ws_ext_sales_price), sum:merge(ws_net_profit)
+| | group by: wp_web_page_sk
+| | row-size=36B cardinality=60
+| |
+| 56:EXCHANGE [HASH(wp_web_page_sk)]
+| |
+| 28:AGGREGATE [STREAMING]
+| | output: sum(ws_ext_sales_price), sum(ws_net_profit)
+| | group by: wp_web_page_sk
+| | row-size=36B cardinality=60
+| |
+| 27:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: ws_web_page_sk = wp_web_page_sk
+| | runtime filters: RF012 <- wp_web_page_sk
+| | row-size=46B cardinality=719.38K
+| |
+| |--55:EXCHANGE [BROADCAST]
+| | |
+| | 25:SCAN HDFS [tpcds.web_page]
+| | HDFS partitions=1/1 files=1 size=5.64KB
+| | row-size=4B cardinality=60
+| |
+| 26:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: ws_sold_date_sk = d_date_sk
+| | runtime filters: RF014 <- d_date_sk
+| | row-size=42B cardinality=719.38K
+| |
+| |--54:EXCHANGE [BROADCAST]
+| | |
+| | 24:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 23:SCAN HDFS [tpcds.web_sales]
+| HDFS partitions=1/1 files=1 size=140.07MB
+| runtime filters: RF012 -> ws_web_page_sk, RF014 -> ws_sold_date_sk
+| row-size=16B cardinality=719.38K
+|
+|--22:NESTED LOOP JOIN [CROSS JOIN, BROADCAST]
+| | row-size=68B cardinality=6
+| |
+| |--53:EXCHANGE [BROADCAST]
+| | |
+| | 52:AGGREGATE [FINALIZE]
+| | | output: sum:merge(cr_return_amount), sum:merge(cr_net_loss)
+| | | row-size=32B cardinality=1
+| | |
+| | 51:EXCHANGE [UNPARTITIONED]
+| | |
+| | 21:AGGREGATE
+| | | output: sum(cr_return_amount), sum(cr_net_loss)
+| | | row-size=32B cardinality=1
+| | |
+| | 20:HASH JOIN [INNER JOIN, BROADCAST]
+| | | hash predicates: cr_returned_date_sk = d_date_sk
+| | | runtime filters: RF010 <- d_date_sk
+| | | row-size=38B cardinality=144.07K
+| | |
+| | |--50:EXCHANGE [BROADCAST]
+| | | |
+| | | 19:SCAN HDFS [tpcds.date_dim]
+| | | HDFS partitions=1/1 files=1 size=9.84MB
+| | | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | | row-size=26B cardinality=7.30K
+| | |
+| | 18:SCAN HDFS [tpcds.catalog_returns]
+| | HDFS partitions=1/1 files=1 size=20.39MB
+| | runtime filters: RF010 -> cr_returned_date_sk
+| | row-size=12B cardinality=144.07K
+| |
+| 49:AGGREGATE [FINALIZE]
+| | output: sum:merge(cs_ext_sales_price), sum:merge(cs_net_profit)
+| | group by: cs_call_center_sk
+| | row-size=36B cardinality=6
+| |
+| 48:EXCHANGE [HASH(cs_call_center_sk)]
+| |
+| 17:AGGREGATE [STREAMING]
+| | output: sum(cs_ext_sales_price), sum(cs_net_profit)
+| | group by: cs_call_center_sk
+| | row-size=36B cardinality=6
+| |
+| 16:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: cs_sold_date_sk = d_date_sk
+| | runtime filters: RF008 <- d_date_sk
+| | row-size=42B cardinality=1.44M
+| |
+| |--47:EXCHANGE [BROADCAST]
+| | |
+| | 15:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 14:SCAN HDFS [tpcds.catalog_sales]
+| HDFS partitions=1/1 files=1 size=282.20MB
+| runtime filters: RF008 -> cs_sold_date_sk
+| row-size=16B cardinality=1.44M
+|
+13:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]
+| hash predicates: s_store_sk = s_store_sk
+| row-size=72B cardinality=12
+|
+|--46:AGGREGATE [FINALIZE]
+| | output: sum:merge(sr_return_amt), sum:merge(sr_net_loss)
+| | group by: s_store_sk
+| | row-size=36B cardinality=12
+| |
+| 45:EXCHANGE [HASH(s_store_sk)]
+| |
+| 12:AGGREGATE [STREAMING]
+| | output: sum(sr_return_amt), sum(sr_net_loss)
+| | group by: s_store_sk
+| | row-size=36B cardinality=12
+| |
+| 11:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: sr_store_sk = s_store_sk
+| | runtime filters: RF004 <- s_store_sk
+| | row-size=46B cardinality=287.51K
+| |
+| |--44:EXCHANGE [BROADCAST]
+| | |
+| | 09:SCAN HDFS [tpcds.store]
+| | HDFS partitions=1/1 files=1 size=3.08KB
+| | row-size=4B cardinality=12
+| |
+| 10:HASH JOIN [INNER JOIN, BROADCAST]
+| | hash predicates: sr_returned_date_sk = d_date_sk
+| | runtime filters: RF006 <- d_date_sk
+| | row-size=42B cardinality=287.51K
+| |
+| |--43:EXCHANGE [BROADCAST]
+| | |
+| | 08:SCAN HDFS [tpcds.date_dim]
+| | HDFS partitions=1/1 files=1 size=9.84MB
+| | predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| | row-size=26B cardinality=7.30K
+| |
+| 07:SCAN HDFS [tpcds.store_returns]
+| HDFS partitions=1/1 files=1 size=31.19MB
+| runtime filters: RF004 -> sr_store_sk, RF006 -> sr_returned_date_sk
+| row-size=16B cardinality=287.51K
+|
+42:AGGREGATE [FINALIZE]
+| output: sum:merge(ss_ext_sales_price), sum:merge(ss_net_profit)
+| group by: s_store_sk
+| row-size=36B cardinality=12
+|
+41:EXCHANGE [HASH(s_store_sk)]
+|
+06:AGGREGATE [STREAMING]
+| output: sum(ss_ext_sales_price), sum(ss_net_profit)
+| group by: s_store_sk
+| row-size=36B cardinality=12
+|
+05:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss_store_sk = s_store_sk
+| runtime filters: RF000 <- s_store_sk
+| row-size=46B cardinality=2.88M
+|
+|--40:EXCHANGE [BROADCAST]
+| |
+| 03:SCAN HDFS [tpcds.store]
+| HDFS partitions=1/1 files=1 size=3.08KB
+| row-size=4B cardinality=12
+|
+04:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss_sold_date_sk = d_date_sk
+| runtime filters: RF002 <- d_date_sk
+| row-size=42B cardinality=2.88M
+|
+|--39:EXCHANGE [BROADCAST]
+| |
+| 02:SCAN HDFS [tpcds.date_dim]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| predicates: d_date <= DATE '1998-09-03', d_date >= DATE '1998-08-04'
+| row-size=26B cardinality=7.30K
+|
+01:SCAN HDFS [tpcds.store_sales]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss_store_sk, RF002 -> ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/grouping-sets.test b/testdata/workloads/functional-query/queries/QueryTest/grouping-sets.test
new file mode 100644
index 0000000..aba927d
--- /dev/null
+++ b/testdata/workloads/functional-query/queries/QueryTest/grouping-sets.test
@@ -0,0 +1,155 @@
+====
+---- QUERY
+# Basic ROLLUP.
+select int_col, string_col, bool_col, count(*)
+from alltypesagg
+group by rollup(1, 2, 3)
+order by count(*) desc, 1, 2, 3
+limit 20
+---- RESULTS
+NULL,'NULL',NULL,11000
+10,'10',true,20
+10,'10',NULL,20
+10,'NULL',NULL,20
+20,'20',true,20
+20,'20',NULL,20
+20,'NULL',NULL,20
+30,'30',true,20
+30,'30',NULL,20
+30,'NULL',NULL,20
+40,'40',true,20
+40,'40',NULL,20
+40,'NULL',NULL,20
+50,'50',true,20
+50,'50',NULL,20
+50,'NULL',NULL,20
+60,'60',true,20
+60,'60',NULL,20
+60,'NULL',NULL,20
+70,'70',true,20
+---- TYPES
+INT,STRING,BOOLEAN,BIGINT
+====
+---- QUERY
+# Basic CUBE
+select int_col, string_col, bool_col, count(*)
+from alltypesagg
+group by cube(1, 2, 3)
+order by count(*) desc, 1, 2, 3
+limit 20
+---- RESULTS
+NULL,'NULL',NULL,11000
+NULL,'NULL',true,6000
+NULL,'NULL',false,5000
+10,'10',true,20
+10,'10',NULL,20
+10,'NULL',true,20
+10,'NULL',NULL,20
+20,'20',true,20
+20,'20',NULL,20
+20,'NULL',true,20
+20,'NULL',NULL,20
+30,'30',true,20
+30,'30',NULL,20
+30,'NULL',true,20
+30,'NULL',NULL,20
+40,'40',true,20
+40,'40',NULL,20
+40,'NULL',true,20
+40,'NULL',NULL,20
+50,'50',true,20
+---- TYPES
+INT,STRING,BOOLEAN,BIGINT
+====
+---- QUERY
+# Basic GROUPING SETS
+select int_col, string_col, bool_col, count(*)
+from alltypesagg
+group by grouping sets((int_col), (string_col), (int_col, bool_col))
+order by count(*) desc, 1, 2, 3
+limit 20
+---- RESULTS
+10,'NULL',true,20
+10,'NULL',NULL,20
+20,'NULL',true,20
+20,'NULL',NULL,20
+30,'NULL',true,20
+30,'NULL',NULL,20
+40,'NULL',true,20
+40,'NULL',NULL,20
+50,'NULL',true,20
+50,'NULL',NULL,20
+60,'NULL',true,20
+60,'NULL',NULL,20
+70,'NULL',true,20
+70,'NULL',NULL,20
+80,'NULL',true,20
+80,'NULL',NULL,20
+90,'NULL',true,20
+90,'NULL',NULL,20
+100,'NULL',true,20
+100,'NULL',NULL,20
+---- TYPES
+INT,STRING,BOOLEAN,BIGINT
+====
+---- QUERY
+# Output should contain a row for both the NULL value in
+# int_col and the rollup of all values in int_col.
+select year, int_col, count(*)
+from alltypesagg
+group by rollup(year, int_col)
+order by 2 desc, 1, 3
+limit 10
+---- RESULTS
+2010,NULL,20
+2010,NULL,11000
+NULL,NULL,11000
+2010,999,10
+2010,998,10
+2010,997,10
+2010,996,10
+2010,995,10
+2010,994,10
+2010,993,10
+---- TYPES
+INT, INT, BIGINT
+====
+---- QUERY
+# Output should contain a row for both the NULL value in
+# int_col and the rollup of all values in int_col.
+select year, int_col, count(*)
+from alltypesagg
+group by rollup(year, int_col)
+having int_col is NULL
+---- RESULTS
+NULL,NULL,11000
+2010,NULL,20
+2010,NULL,11000
+---- TYPES
+INT, INT, BIGINT
+====
+---- QUERY
+# Correlated subquery with rollup. Results are the same as
+# if rollup was not present.
+select id
+from functional.alltypesagg a
+where exists
+ (select id
+ from functional.alltypestiny b
+ where a.tinyint_col = b.tinyint_col and a.string_col = b.string_col
+ group by rollup(id, int_col, bool_col))
+ and tinyint_col < 10
+---- RESULTS
+1
+1001
+2001
+3001
+4001
+5001
+6001
+7001
+8001
+9001
+---- TYPES
+INT
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q18.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q18.test
new file mode 100644
index 0000000..3e828ab
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q18.test
@@ -0,0 +1,137 @@
+====
+---- QUERY: TPCDS-Q18
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as decimal(12,2))) agg1,
+ avg( cast(cs_list_price as decimal(12,2))) agg2,
+ avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
+ avg( cast(cs_sales_price as decimal(12,2))) agg4,
+ avg( cast(cs_net_profit as decimal(12,2))) agg5,
+ avg( cast(c_birth_year as decimal(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100;
+---- RESULTS
+'AAAAAAAAAMJBAAAA','United States','AL','Bullock County',20.000000,192.920000,0.000000,167.840000,1978.800000,1930.000000,6.000000
+'AAAAAAAABHACAAAA','United States','AL','Bullock County',20.000000,117.530000,0.000000,62.290000,-665.400000,1930.000000,6.000000
+'AAAAAAAAGMBDAAAA','United States','AL','Bullock County',87.000000,281.890000,0.000000,253.700000,13405.830000,1930.000000,6.000000
+'AAAAAAAAMFNBAAAA','United States','AL','Bullock County',58.000000,12.070000,0.000000,6.510000,-71.340000,1930.000000,6.000000
+'AAAAAAAANGBCAAAA','United States','AL','Bullock County',62.000000,63.470000,0.000000,0.000000,-1847.600000,1930.000000,6.000000
+'AAAAAAAANKGCAAAA','United States','AL','Bullock County',50.000000,196.520000,0.000000,51.090000,-1557.000000,1930.000000,6.000000
+'AAAAAAAAOJBCAAAA','United States','AL','Bullock County',81.000000,28.570000,0.000000,15.990000,499.770000,1930.000000,6.000000
+'AAAAAAAAPAKDAAAA','United States','AL','Bullock County',48.000000,169.210000,0.000000,69.370000,-345.600000,1930.000000,6.000000
+'AAAAAAAAPNMAAAAA','United States','AL','Bullock County',80.000000,228.040000,0.000000,54.720000,-3068.800000,1930.000000,6.000000
+'AAAAAAAAABLCAAAA','United States','AL','Dale County',97.000000,14.950000,0.000000,13.600000,445.230000,1944.000000,0.000000
+'AAAAAAAAACGCAAAA','United States','AL','Dale County',88.000000,152.900000,451.890000,18.340000,-3954.290000,1944.000000,0.000000
+'AAAAAAAAAHHDAAAA','United States','AL','Dale County',21.000000,62.600000,0.000000,5.000000,-820.890000,1944.000000,0.000000
+'AAAAAAAAAPHBAAAA','United States','AL','Dale County',14.000000,168.640000,0.000000,57.330000,-554.260000,1944.000000,0.000000
+'AAAAAAAACHOAAAAA','United States','AL','Dale County',54.000000,115.260000,0.000000,101.420000,2300.940000,1944.000000,0.000000
+'AAAAAAAADLHBAAAA','United States','AL','Dale County',42.000000,3.450000,0.000000,0.000000,-54.180000,1944.000000,0.000000
+'AAAAAAAAELDBAAAA','United States','AL','Dale County',22.000000,9.120000,0.000000,2.370000,-84.480000,1944.000000,0.000000
+'AAAAAAAAHHACAAAA','United States','AL','Dale County',11.000000,56.970000,0.000000,21.640000,-158.620000,1944.000000,0.000000
+'AAAAAAAAIKAEAAAA','United States','AL','Dale County',41.000000,86.200000,38.160000,31.030000,-1308.750000,1944.000000,0.000000
+'AAAAAAAAIPACAAAA','United States','AL','Dale County',94.000000,45.130000,144.530000,4.960000,-2102.550000,1944.000000,0.000000
+'AAAAAAAALHPBAAAA','United States','AL','Dale County',64.000000,88.570000,0.000000,56.680000,-871.680000,1944.000000,0.000000
+'AAAAAAAAOHMCAAAA','United States','AL','Dale County',78.000000,132.430000,0.000000,11.910000,-3074.760000,1944.000000,0.000000
+'AAAAAAAAOMCEAAAA','United States','AL','Dale County',24.000000,36.350000,0.000000,32.710000,133.920000,1944.000000,0.000000
+'AAAAAAAAPFLDAAAA','United States','AL','Dale County',93.000000,13.250000,0.000000,7.420000,233.430000,1944.000000,0.000000
+'AAAAAAAAAFDDAAAA','United States','AL','Jefferson County',75.000000,88.760000,0.000000,50.590000,-192.000000,1933.000000,0.000000
+'AAAAAAAACIFBAAAA','United States','AL','Jefferson County',64.000000,182.430000,3992.830000,138.640000,423.810000,1933.000000,0.000000
+'AAAAAAAACPMAAAAA','United States','AL','Jefferson County',63.000000,57.100000,0.000000,53.670000,2023.560000,1933.000000,0.000000
+'AAAAAAAAEJOCAAAA','United States','AL','Jefferson County',83.000000,100.530000,0.000000,15.070000,-2457.630000,1933.000000,0.000000
+'AAAAAAAAFLKCAAAA','United States','AL','Jefferson County',88.000000,74.080000,0.000000,40.740000,566.720000,1933.000000,0.000000
+'AAAAAAAAKBDCAAAA','United States','AL','Jefferson County',30.000000,48.600000,0.000000,45.680000,481.200000,1933.000000,0.000000
+'AAAAAAAAMIPCAAAA','United States','AL','Jefferson County',24.000000,212.110000,0.000000,16.960000,-1896.480000,1933.000000,0.000000
+'AAAAAAAAAFPCAAAA','United States','AL','Lamar County',56.000000,121.720000,0.000000,26.770000,-2927.120000,1972.000000,6.000000
+'AAAAAAAAAHIBAAAA','United States','AL','Lamar County',68.000000,281.940000,0.000000,8.450000,-5946.600000,1972.000000,6.000000
+'AAAAAAAAAIFAAAAA','United States','AL','Lamar County',52.000000,205.590000,0.000000,98.680000,271.960000,1972.000000,6.000000
+'AAAAAAAABIDBAAAA','United States','AL','Lamar County',29.000000,94.220000,0.000000,91.390000,887.400000,1972.000000,6.000000
+'AAAAAAAABJADAAAA','United States','AL','Lamar County',62.000000,31.210000,142.730000,12.790000,-407.470000,1972.000000,6.000000
+'AAAAAAAACCNDAAAA','United States','AL','Lamar County',82.000000,63.880000,0.000000,49.180000,1373.500000,1972.000000,6.000000
+'AAAAAAAACJAAAAAA','United States','AL','Lamar County',57.000000,133.260000,0.000000,63.960000,239.400000,1972.000000,6.000000
+'AAAAAAAACJFAAAAA','United States','AL','Lamar County',4.000000,106.250000,0.000000,97.750000,101.880000,1972.000000,6.000000
+'AAAAAAAAGBADAAAA','United States','AL','Lamar County',69.000000,93.170000,0.000000,0.000000,-3716.340000,1972.000000,6.000000
+'AAAAAAAAGDFDAAAA','United States','AL','Lamar County',58.000000,160.040000,0.000000,108.820000,976.720000,1972.000000,6.000000
+'AAAAAAAAIBHDAAAA','United States','AL','Lamar County',89.000000,141.980000,0.000000,53.950000,-377.360000,1974.000000,6.000000
+'AAAAAAAAIGECAAAA','United States','AL','Lamar County',23.000000,36.680000,0.000000,2.200000,-454.710000,1974.000000,6.000000
+'AAAAAAAAJHJAAAAA','United States','AL','Lamar County',11.000000,124.840000,0.000000,92.380000,383.350000,1972.000000,6.000000
+'AAAAAAAAJLDCAAAA','United States','AL','Lamar County',87.000000,7.040000,154.870000,3.870000,-370.630000,1972.000000,6.000000
+'AAAAAAAAMKBDAAAA','United States','AL','Lamar County',7.000000,55.570000,0.000000,32.780000,92.960000,1974.000000,6.000000
+'AAAAAAAAOFHDAAAA','United States','AL','Lamar County',77.000000,85.960000,0.000000,85.100000,3354.890000,1974.000000,6.000000
+'AAAAAAAAOHKAAAAA','United States','AL','Lamar County',87.000000,140.150000,0.000000,9.810000,-5530.590000,1972.000000,6.000000
+'AAAAAAAABEFDAAAA','United States','AL','Lauderdale County',50.000000,16.470000,0.000000,15.480000,-49.500000,1935.000000,5.000000
+'AAAAAAAACMDDAAAA','United States','AL','Lauderdale County',37.000000,197.130000,0.000000,80.820000,-167.240000,1935.000000,5.000000
+'AAAAAAAADEFBAAAA','United States','AL','Lauderdale County',94.000000,40.190000,0.000000,17.680000,-509.480000,1935.000000,5.000000
+'AAAAAAAAEEODAAAA','United States','AL','Lauderdale County',19.000000,82.240000,0.000000,37.000000,2.280000,1935.000000,5.000000
+'AAAAAAAAGFEEAAAA','United States','AL','Lauderdale County',70.000000,89.240000,0.000000,33.910000,-1176.000000,1935.000000,5.000000
+'AAAAAAAAGMKBAAAA','United States','AL','Lauderdale County',69.000000,34.440000,76.870000,6.190000,-701.320000,1935.000000,5.000000
+'AAAAAAAAJGKDAAAA','United States','AL','Lauderdale County',47.000000,6.750000,0.000000,4.520000,30.080000,1935.000000,5.000000
+'AAAAAAAAKLHBAAAA','United States','AL','Lauderdale County',45.000000,62.560000,0.000000,45.040000,717.300000,1935.000000,5.000000
+'AAAAAAAALEKDAAAA','United States','AL','Lauderdale County',2.000000,68.050000,0.000000,19.730000,-41.560000,1935.000000,5.000000
+'AAAAAAAAMLLBAAAA','United States','AL','Lauderdale County',79.000000,14.010000,0.000000,10.640000,417.910000,1935.000000,5.000000
+'AAAAAAAAOIDBAAAA','United States','AL','Lauderdale County',49.000000,117.910000,0.000000,68.380000,-172.480000,1935.000000,5.000000
+'AAAAAAAAOKIDAAAA','United States','AL','Lauderdale County',58.000000,243.790000,0.000000,138.960000,2470.800000,1935.000000,5.000000
+'AAAAAAAAAHFCAAAA','United States','AL','Lee County',90.000000,147.450000,0.000000,106.160000,2341.800000,1939.000000,1.000000
+'AAAAAAAABPKCAAAA','United States','AL','Lee County',96.000000,167.270000,0.000000,70.250000,-2592.000000,1939.000000,1.000000
+'AAAAAAAACBKBAAAA','United States','AL','Lee County',64.000000,258.600000,910.270000,142.230000,2638.530000,1939.000000,1.000000
+'AAAAAAAACIICAAAA','United States','AL','Lee County',9.000000,11.290000,5.920000,5.980000,6.050000,1939.000000,1.000000
+'AAAAAAAAEDJAAAAA','United States','AL','Lee County',19.000000,121.960000,0.000000,47.560000,-26.980000,1939.000000,1.000000
+'AAAAAAAAJJDEAAAA','United States','AL','Lee County',32.000000,107.320000,0.000000,10.730000,-910.080000,1939.000000,1.000000
+'AAAAAAAAMOACAAAA','United States','AL','Lee County',51.000000,16.480000,0.000000,1.640000,-375.870000,1939.000000,1.000000
+'AAAAAAAAOEEDAAAA','United States','AL','Lee County',17.000000,74.280000,336.640000,63.880000,51.640000,1939.000000,1.000000
+'AAAAAAAAOOKDAAAA','United States','AL','Lee County',95.000000,156.530000,0.000000,95.480000,1989.300000,1939.000000,1.000000
+'AAAAAAAAPIFDAAAA','United States','AL','Lee County',42.000000,58.660000,118.980000,40.470000,-811.560000,1939.000000,1.000000
+'AAAAAAAADEGDAAAA','United States','AL','Marengo County',19.000000,36.900000,0.000000,1.470000,-551.570000,1960.000000,2.000000
+'AAAAAAAADPMBAAAA','United States','AL','Marengo County',12.000000,86.800000,0.000000,49.470000,-139.920000,1960.000000,2.000000
+'AAAAAAAAFBFCAAAA','United States','AL','Marengo County',12.000000,22.330000,48.330000,4.240000,-218.970000,1960.000000,2.000000
+'AAAAAAAAPDCCAAAA','United States','AL','Marengo County',2.000000,183.040000,0.000000,91.520000,38.340000,1960.000000,2.000000
+'AAAAAAAAAAHBAAAA','United States','AL','Marshall County',55.000000,40.870000,0.000000,20.020000,-261.250000,1978.000000,3.000000
+'AAAAAAAAAALDAAAA','United States','AL','Marshall County',80.000000,100.760000,59.200000,1.000000,-6228.000000,1978.000000,3.000000
+'AAAAAAAAADHAAAAA','United States','AL','Marshall County',7.000000,260.420000,0.000000,190.100000,710.640000,1978.000000,3.000000
+'AAAAAAAACAGCAAAA','United States','AL','Marshall County',92.000000,183.760000,0.000000,121.280000,3438.040000,1978.000000,3.000000
+'AAAAAAAADAOCAAAA','United States','AL','Marshall County',68.000000,199.290000,3428.280000,109.600000,-2717.680000,1978.000000,3.000000
+'AAAAAAAAGGPCAAAA','United States','AL','Marshall County',64.000000,263.920000,0.000000,160.990000,4313.600000,1978.000000,3.000000
+'AAAAAAAAHBEEAAAA','United States','AL','Marshall County',25.000000,46.470000,0.000000,10.220000,-209.250000,1978.000000,3.000000
+'AAAAAAAAFJODAAAA','United States','AL','Morgan County',39.000000,45.160000,0.000000,8.120000,-1377.090000,1985.000000,4.000000
+'AAAAAAAAGDCDAAAA','United States','AL','Morgan County',99.000000,8.690000,0.000000,0.000000,-354.420000,1985.000000,4.000000
+'AAAAAAAAGKDAAAAA','United States','AL','Morgan County',68.000000,135.270000,0.000000,132.560000,2840.360000,1985.000000,4.000000
+'AAAAAAAAIOFBAAAA','United States','AL','Morgan County',37.000000,64.090000,0.000000,1.920000,-827.320000,1985.000000,4.000000
+'AAAAAAAALBJBAAAA','United States','AL','Morgan County',73.000000,132.770000,0.000000,132.770000,5891.100000,1985.000000,4.000000
+'AAAAAAAAPJICAAAA','United States','AL','Morgan County',85.000000,86.390000,0.000000,60.470000,2237.200000,1985.000000,4.000000
+'AAAAAAAADOCAAAAA','United States','AL','Perry County',44.000000,132.900000,0.000000,53.160000,-584.760000,1957.000000,6.000000
+'AAAAAAAADPAEAAAA','United States','AL','Perry County',95.000000,180.400000,0.000000,151.530000,5279.150000,1957.000000,6.000000
+'AAAAAAAAEFNDAAAA','United States','AL','Perry County',25.000000,3.800000,0.000000,2.200000,6.000000,1957.000000,6.000000
+'AAAAAAAAGHGDAAAA','United States','AL','Perry County',43.000000,82.190000,0.000000,32.870000,-2051.530000,1957.000000,6.000000
+'AAAAAAAAHACEAAAA','United States','AL','Perry County',65.000000,191.050000,0.000000,32.470000,-3173.950000,1957.000000,6.000000
+'AAAAAAAAHHHBAAAA','United States','AL','Perry County',6.000000,36.220000,0.000000,7.960000,-36.480000,1957.000000,6.000000
+'AAAAAAAAIHGCAAAA','United States','AL','Perry County',12.000000,48.660000,0.000000,36.980000,-51.120000,1957.000000,6.000000
+'AAAAAAAAKAHAAAAA','United States','AL','Perry County',99.000000,7.510000,0.000000,0.450000,-292.050000,1957.000000,6.000000
+'AAAAAAAALNBAAAAA','United States','AL','Perry County',49.000000,150.130000,0.000000,123.100000,1285.760000,1957.000000,6.000000
+'AAAAAAAAMFDDAAAA','United States','AL','Perry County',87.000000,121.490000,0.000000,51.020000,-1244.100000,1957.000000,6.000000
+'AAAAAAAAOMFBAAAA','United States','AL','Perry County',55.000000,79.970000,0.000000,38.380000,-461.450000,1957.000000,6.000000
+'AAAAAAAAADFDAAAA','United States','AL','Shelby County',75.000000,231.300000,0.000000,83.260000,-1106.250000,1948.000000,3.000000
+'AAAAAAAAADKAAAAA','United States','AL','Shelby County',61.000000,67.390000,616.250000,13.470000,-3432.620000,1973.000000,1.000000
+'AAAAAAAAAOCEAAAA','United States','AL','Shelby County',67.000000,51.710000,0.000000,11.890000,-1732.620000,1948.000000,3.000000
+---- TYPES
+STRING, STRING, STRING, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q22.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q22.test
new file mode 100644
index 0000000..737180e
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q22.test
@@ -0,0 +1,125 @@
+====
+---- QUERY: TPCDS-Q22
+select i_product_name
+ ,i_brand
+ ,i_class
+ ,i_category
+ ,avg(inv_quantity_on_hand) qoh
+ from inventory
+ ,date_dim
+ ,item
+ ,warehouse
+ where inv_date_sk=d_date_sk
+ and inv_item_sk=i_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and d_month_seq between 1212 and 1212 + 11
+ group by rollup(i_product_name
+ ,i_brand
+ ,i_class
+ ,i_category)
+order by qoh, i_product_name, i_brand, i_class, i_category
+limit 100;
+---- RESULTS
+'n stbarcallyationought','amalgscholar #2','rock','Music',432.056680162
+'n stbarcallyationought','amalgscholar #2','rock','NULL',432.056680162
+'n stbarcallyationought','amalgscholar #2','NULL','NULL',432.056680162
+'n stbarcallyationought','NULL','NULL','NULL',432.056680162
+'antiablen stcally','univbrand #6','jewelry boxes','Jewelry',436.257028112
+'antiablen stcally','univbrand #6','jewelry boxes','NULL',436.257028112
+'antiablen stcally','univbrand #6','NULL','NULL',436.257028112
+'antiablen stcally','NULL','NULL','NULL',436.257028112
+'antiantiableeseought','exportiexporti #2','toddlers','Children',436.558704453
+'antiantiableeseought','exportiexporti #2','toddlers','NULL',436.558704453
+'antiantiableeseought','exportiexporti #2','NULL','NULL',436.558704453
+'antiantiableeseought','NULL','NULL','NULL',436.558704453
+'n stcallyn stationought','exportischolar #2','pop','Music',436.852589641
+'n stcallyn stationought','exportischolar #2','pop','NULL',436.852589641
+'n stcallyn stationought','exportischolar #2','NULL','NULL',436.852589641
+'n stcallyn stationought','NULL','NULL','NULL',436.852589641
+'ationantiableationought','amalgscholar #2','rock','Music',438.133064516
+'ationantiableationought','amalgscholar #2','rock','NULL',438.133064516
+'ationantiableationought','amalgscholar #2','NULL','NULL',438.133064516
+'ationantiableationought','NULL','NULL','NULL',438.133064516
+'n stcallyesepriought','edu packscholar #2','classical','Music',438.56097561
+'n stcallyesepriought','edu packscholar #2','classical','NULL',438.56097561
+'n stcallyesepriought','edu packscholar #2','NULL','NULL',438.56097561
+'n stcallyesepriought','NULL','NULL','NULL',438.56097561
+'oughteingoughtn st','importobrand #6','costume','Jewelry',441.268595041
+'oughteingoughtn st','importobrand #6','costume','NULL',441.268595041
+'oughteingoughtn st','importobrand #6','NULL','NULL',441.268595041
+'oughteingoughtn st','NULL','NULL','NULL',441.268595041
+'prieingeseantiought','amalgunivamalg #4','cooking','Books',441.76446281
+'prieingeseantiought','amalgunivamalg #4','cooking','NULL',441.76446281
+'prieingeseantiought','amalgunivamalg #4','NULL','NULL',441.76446281
+'prieingeseantiought','NULL','NULL','NULL',441.76446281
+'ationationpribarought','scholarmaxi #10','history','Books',442.041322314
+'ationationpribarought','scholarmaxi #10','history','NULL',442.041322314
+'ationationpribarought','scholarmaxi #10','NULL','NULL',442.041322314
+'ationationpribarought','NULL','NULL','NULL',442.041322314
+'n stpribarought','edu packexporti #2','school-uniforms','Children',442.215447154
+'n stpribarought','edu packexporti #2','school-uniforms','NULL',442.215447154
+'n stpribarought','edu packexporti #2','NULL','NULL',442.215447154
+'n stpribarought','NULL','NULL','NULL',442.215447154
+'pricallyantiese','amalgunivamalg #8','cameras','Electronics',442.740890688
+'pricallyantiese','amalgunivamalg #8','cameras','NULL',442.740890688
+'pricallyantiese','amalgunivamalg #8','NULL','NULL',442.740890688
+'pricallyantiese','NULL','NULL','NULL',442.740890688
+'n stoughtoughtbarought','amalgscholar #2','rock','Music',443.9625
+'n stoughtoughtbarought','amalgscholar #2','rock','NULL',443.9625
+'n stoughtoughtbarought','amalgscholar #2','NULL','NULL',443.9625
+'n stoughtoughtbarought','NULL','NULL','NULL',443.9625
+'antiableableought','importoamalgamalg #2','monitors','Electronics',444.646090535
+'antiableableought','importoamalgamalg #2','monitors','NULL',444.646090535
+'antiableableought','importoamalgamalg #2','NULL','NULL',444.646090535
+'antiableableought','NULL','NULL','NULL',444.646090535
+'prieseoughtable','exportischolar #2','pop','Music',445.270491803
+'prieseoughtable','exportischolar #2','pop','NULL',445.270491803
+'prieseoughtable','exportischolar #2','NULL','NULL',445.270491803
+'prieseoughtable','NULL','NULL','NULL',445.270491803
+'ationpribarought','amalgunivamalg #1','cameras','Electronics',445.530120482
+'ationpribarought','amalgunivamalg #1','cameras','NULL',445.530120482
+'ationpribarought','amalgunivamalg #1','NULL','NULL',445.530120482
+'ationpribarought','NULL','NULL','NULL',445.530120482
+'n stn stesebarought','importoexporti #2','infants','Children',445.907630522
+'n stn stesebarought','importoexporti #2','infants','NULL',445.907630522
+'n stn stesebarought','importoexporti #2','NULL','NULL',445.907630522
+'n stn stesebarought','NULL','NULL','NULL',445.907630522
+'prioughtanticallyought','importoexporti #2','infants','Children',445.959677419
+'prioughtanticallyought','importoexporti #2','infants','NULL',445.959677419
+'prioughtanticallyought','importoexporti #2','NULL','NULL',445.959677419
+'prioughtanticallyought','NULL','NULL','NULL',445.959677419
+'oughtationpriantiought','exportiedu pack #2','kids','Shoes',445.963855422
+'oughtationpriantiought','exportiedu pack #2','kids','NULL',445.963855422
+'oughtationpriantiought','exportiedu pack #2','NULL','NULL',445.963855422
+'oughtationpriantiought','NULL','NULL','NULL',445.963855422
+'priantioughtantiought','importomaxi #10','business','Books',446.179591837
+'priantioughtantiought','importomaxi #10','business','NULL',446.179591837
+'priantioughtantiought','importomaxi #10','NULL','NULL',446.179591837
+'priantioughtantiought','NULL','NULL','NULL',446.179591837
+'n stpribarbarought','edu packbrand #8','estate','Jewelry',446.316
+'n stpribarbarought','edu packbrand #8','estate','NULL',446.316
+'n stpribarbarought','edu packbrand #8','NULL','NULL',446.316
+'n stpribarbarought','NULL','NULL','NULL',446.316
+'ationn steingantiought','edu packcorp #2','bracelets','Jewelry',446.586065574
+'ationn steingantiought','edu packcorp #2','bracelets','NULL',446.586065574
+'ationn steingantiought','edu packcorp #2','NULL','NULL',446.586065574
+'ationn steingantiought','NULL','NULL','NULL',446.586065574
+'antioughtcallyantiought','edu packamalgamalg #10','automotive','Electronics',446.714859438
+'antioughtcallyantiought','edu packamalgamalg #10','automotive','NULL',446.714859438
+'antioughtcallyantiought','edu packamalgamalg #10','NULL','NULL',446.714859438
+'antioughtcallyantiought','NULL','NULL','NULL',446.714859438
+'antiableprin st','edu packimporto #2','sports-apparel','Men',446.796680498
+'antiableprin st','edu packimporto #2','sports-apparel','NULL',446.796680498
+'antiableprin st','edu packimporto #2','NULL','NULL',446.796680498
+'antiableprin st','NULL','NULL','NULL',446.796680498
+'antioughtantically','corpnameless #6','football','Sports',446.963562753
+'antioughtantically','corpnameless #6','football','NULL',446.963562753
+'antioughtantically','corpnameless #6','NULL','NULL',446.963562753
+'antioughtantically','NULL','NULL','NULL',446.963562753
+'antin stn stanti','amalgamalg #2','dresses','Women',447.380165289
+'antin stn stanti','amalgamalg #2','dresses','NULL',447.380165289
+'antin stn stanti','amalgamalg #2','NULL','NULL',447.380165289
+'antin stn stanti','NULL','NULL','NULL',447.380165289
+---- TYPES
+STRING, STRING, STRING, STRING, DOUBLE
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q5.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q5.test
new file mode 100644
index 0000000..fdb26fd
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q5.test
@@ -0,0 +1,231 @@
+====
+---- QUERY: TPCDS-Q5
+with ssr as
+ (select s_store_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select ss_store_sk as store_sk,
+ ss_sold_date_sk as date_sk,
+ ss_ext_sales_price as sales_price,
+ ss_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from store_sales
+ union all
+ select sr_store_sk as store_sk,
+ sr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ sr_return_amt as return_amt,
+ sr_net_loss as net_loss
+ from store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and store_sk = s_store_sk
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select cs_catalog_page_sk as page_sk,
+ cs_sold_date_sk as date_sk,
+ cs_ext_sales_price as sales_price,
+ cs_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from catalog_sales
+ union all
+ select cr_catalog_page_sk as page_sk,
+ cr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ cr_return_amount as return_amt,
+ cr_net_loss as net_loss
+ from catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and page_sk = cp_catalog_page_sk
+ group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as `returns`,
+ sum(net_loss) as profit_loss
+ from
+ ( select ws_web_site_sk as wsr_web_site_sk,
+ ws_sold_date_sk as date_sk,
+ ws_ext_sales_price as sales_price,
+ ws_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from web_sales
+ union all
+ select ws_web_site_sk as wsr_web_site_sk,
+ wr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ wr_return_amt as return_amt,
+ wr_net_loss as net_loss
+ from web_returns left outer join web_sales on
+ ( wr_item_sk = ws_item_sk
+ and wr_order_number = ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + INTERVAL 14 days)
+ and wsr_web_site_sk = web_site_sk
+ group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(`returns`) as `returns`
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || s_store_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || cp_catalog_page_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100;
+---- RESULTS
+'catalog channel','catalog_pageAAAAAAAAAABAAAAA',73724.99,0.00,-34506.26
+'catalog channel','catalog_pageAAAAAAAAAADAAAAA',73182.60,0.00,-17828.06
+'catalog channel','catalog_pageAAAAAAAAABAAAAAA',0.00,4543.46,-2785.79
+'catalog channel','catalog_pageAAAAAAAAABBAAAAA',72671.60,0.00,6590.95
+'catalog channel','catalog_pageAAAAAAAAABCAAAAA',0.00,3646.32,-514.18
+'catalog channel','catalog_pageAAAAAAAAABDAAAAA',69548.37,0.00,-6749.40
+'catalog channel','catalog_pageAAAAAAAAACAAAAAA',0.00,10499.54,-5521.37
+'catalog channel','catalog_pageAAAAAAAAACBAAAAA',153295.93,5351.63,4632.81
+'catalog channel','catalog_pageAAAAAAAAACDAAAAA',54336.42,0.00,-18212.06
+'catalog channel','catalog_pageAAAAAAAAADAAAAAA',0.00,9992.13,-6117.85
+'catalog channel','catalog_pageAAAAAAAAADBAAAAA',131954.65,50.05,5527.80
+'catalog channel','catalog_pageAAAAAAAAADDAAAAA',76814.28,0.00,-3457.30
+'catalog channel','catalog_pageAAAAAAAAAEAAAAAA',0.00,3197.25,-478.68
+'catalog channel','catalog_pageAAAAAAAAAEBAAAAA',92714.17,0.00,10984.25
+'catalog channel','catalog_pageAAAAAAAAAECAAAAA',0.00,2249.75,-1759.52
+'catalog channel','catalog_pageAAAAAAAAAEDAAAAA',74886.06,0.00,12438.42
+'catalog channel','catalog_pageAAAAAAAAAFAAAAAA',0.00,7425.07,-4953.73
+'catalog channel','catalog_pageAAAAAAAAAFDAAAAA',68271.10,0.00,-31349.03
+'catalog channel','catalog_pageAAAAAAAAAGAAAAAA',0.00,30.69,-114.52
+'catalog channel','catalog_pageAAAAAAAAAGBAAAAA',0.00,152.10,-232.54
+'catalog channel','catalog_pageAAAAAAAAAGCAAAAA',0.00,907.94,-259.69
+'catalog channel','catalog_pageAAAAAAAAAGDAAAAA',80839.26,0.00,-17596.72
+'catalog channel','catalog_pageAAAAAAAAAHAAAAAA',183580.58,3667.05,-30239.38
+'catalog channel','catalog_pageAAAAAAAAAIAAAAAA',176232.57,130.41,-14554.63
+'catalog channel','catalog_pageAAAAAAAAAIBAAAAA',0.00,267.00,-180.11
+'catalog channel','catalog_pageAAAAAAAAAJAAAAAA',200218.67,1441.64,41.78
+'catalog channel','catalog_pageAAAAAAAAAJCAAAAA',21707.46,0.00,-2111.35
+'catalog channel','catalog_pageAAAAAAAAAKAAAAAA',142031.14,0.00,-26852.79
+'catalog channel','catalog_pageAAAAAAAAAKCAAAAA',15995.65,0.00,-1934.05
+'catalog channel','catalog_pageAAAAAAAAALAAAAAA',126857.88,9.78,-44236.00
+'catalog channel','catalog_pageAAAAAAAAALBAAAAA',0.00,NULL,-508.20
+'catalog channel','catalog_pageAAAAAAAAALCAAAAA',21479.91,0.00,-8594.75
+'catalog channel','catalog_pageAAAAAAAAAMAAAAAA',169425.57,2291.31,-37705.18
+'catalog channel','catalog_pageAAAAAAAAAMCAAAAA',14310.85,0.00,-21009.49
+'catalog channel','catalog_pageAAAAAAAAANAAAAAA',198016.35,273.78,-14742.98
+'catalog channel','catalog_pageAAAAAAAAANBAAAAA',0.00,490.00,-184.86
+'catalog channel','catalog_pageAAAAAAAAANCAAAAA',29918.97,0.00,-4040.58
+'catalog channel','catalog_pageAAAAAAAAAOAAAAAA',105854.60,0.00,-8874.12
+'catalog channel','catalog_pageAAAAAAAAAOCAAAAA',14091.48,0.00,-1309.14
+'catalog channel','catalog_pageAAAAAAAAAPAAAAAA',83006.94,0.00,-5381.07
+'catalog channel','catalog_pageAAAAAAAAAPCAAAAA',37508.18,0.00,-7290.39
+'catalog channel','catalog_pageAAAAAAAABAAAAAAA',0.00,1107.33,-960.08
+'catalog channel','catalog_pageAAAAAAAABABAAAAA',95905.45,0.00,-13710.06
+'catalog channel','catalog_pageAAAAAAAABADAAAAA',47636.96,0.00,-28573.24
+'catalog channel','catalog_pageAAAAAAAABBAAAAAA',0.00,2623.04,-1015.94
+'catalog channel','catalog_pageAAAAAAAABBBAAAAA',58354.38,0.00,-10624.68
+'catalog channel','catalog_pageAAAAAAAABBDAAAAA',49780.88,0.00,-11981.01
+'catalog channel','catalog_pageAAAAAAAABCAAAAAA',0.00,4125.66,-1523.65
+'catalog channel','catalog_pageAAAAAAAABCBAAAAA',98660.80,0.00,-16289.71
+'catalog channel','catalog_pageAAAAAAAABCDAAAAA',113374.11,0.00,-19319.37
+'catalog channel','catalog_pageAAAAAAAABDAAAAAA',0.00,180.96,-303.56
+'catalog channel','catalog_pageAAAAAAAABDBAAAAA',58175.73,380.96,555.42
+'catalog channel','catalog_pageAAAAAAAABDDAAAAA',49788.84,0.00,-35531.15
+'catalog channel','catalog_pageAAAAAAAABEAAAAAA',0.00,4989.69,-1347.21
+'catalog channel','catalog_pageAAAAAAAABEBAAAAA',77142.64,0.00,2181.34
+'catalog channel','catalog_pageAAAAAAAABEDAAAAA',37722.95,0.00,-3862.61
+'catalog channel','catalog_pageAAAAAAAABFAAAAAA',0.00,1134.57,-2249.74
+'catalog channel','catalog_pageAAAAAAAABFDAAAAA',90245.96,0.00,8274.06
+'catalog channel','catalog_pageAAAAAAAABGAAAAAA',0.00,9836.43,-6219.61
+'catalog channel','catalog_pageAAAAAAAABGBAAAAA',0.00,60.03,-337.81
+'catalog channel','catalog_pageAAAAAAAABHAAAAAA',211801.76,0.00,-18883.07
+'catalog channel','catalog_pageAAAAAAAABHCAAAAA',0.00,2809.62,-969.29
+'catalog channel','catalog_pageAAAAAAAABIAAAAAA',175304.26,0.00,-5063.68
+'catalog channel','catalog_pageAAAAAAAABJAAAAAA',192257.13,2641.12,4494.09
+'catalog channel','catalog_pageAAAAAAAABJCAAAAA',21066.11,0.00,-9792.90
+'catalog channel','catalog_pageAAAAAAAABKAAAAAA',117310.09,0.00,-38423.34
+'catalog channel','catalog_pageAAAAAAAABKCAAAAA',16351.27,0.00,970.92
+'catalog channel','catalog_pageAAAAAAAABLAAAAAA',189060.69,0.00,-27541.44
+'catalog channel','catalog_pageAAAAAAAABLCAAAAA',11111.58,0.00,2786.73
+'catalog channel','catalog_pageAAAAAAAABMAAAAAA',148581.54,1047.69,-39546.35
+'catalog channel','catalog_pageAAAAAAAABMCAAAAA',5756.80,0.00,961.53
+'catalog channel','catalog_pageAAAAAAAABNAAAAAA',210427.91,404.72,-17396.64
+'catalog channel','catalog_pageAAAAAAAABNCAAAAA',11474.87,0.00,-6101.47
+'catalog channel','catalog_pageAAAAAAAABOAAAAAA',85328.52,0.00,-13566.80
+'catalog channel','catalog_pageAAAAAAAABOCAAAAA',8991.73,0.00,-9799.23
+'catalog channel','catalog_pageAAAAAAAABPAAAAAA',67031.90,228.48,-17054.88
+'catalog channel','catalog_pageAAAAAAAABPCAAAAA',3184.57,0.00,-1553.07
+'catalog channel','catalog_pageAAAAAAAACAAAAAAA',0.00,594.69,-910.16
+'catalog channel','catalog_pageAAAAAAAACABAAAAA',68609.89,0.00,-2362.92
+'catalog channel','catalog_pageAAAAAAAACADAAAAA',97819.48,0.00,3615.65
+'catalog channel','catalog_pageAAAAAAAACBAAAAAA',0.00,4531.07,-864.84
+'catalog channel','catalog_pageAAAAAAAACBBAAAAA',87577.40,5163.40,-23331.95
+'catalog channel','catalog_pageAAAAAAAACBDAAAAA',65509.70,0.00,-26490.01
+'catalog channel','catalog_pageAAAAAAAACCAAAAAA',0.00,579.28,-263.33
+'catalog channel','catalog_pageAAAAAAAACCBAAAAA',79097.22,0.00,-14175.57
+'catalog channel','catalog_pageAAAAAAAACCCAAAAA',0.00,107.64,-110.70
+'catalog channel','catalog_pageAAAAAAAACCDAAAAA',94846.27,0.00,2494.45
+'catalog channel','catalog_pageAAAAAAAACDAAAAAA',0.00,3583.50,-5705.10
+'catalog channel','catalog_pageAAAAAAAACDBAAAAA',78115.95,0.00,-10896.38
+'catalog channel','catalog_pageAAAAAAAACDCAAAAA',0.00,793.44,-78.49
+'catalog channel','catalog_pageAAAAAAAACDDAAAAA',102043.84,0.00,-3411.73
+'catalog channel','catalog_pageAAAAAAAACEBAAAAA',80630.55,0.00,-37508.78
+'catalog channel','catalog_pageAAAAAAAACECAAAAA',0.00,505.73,-398.47
+'catalog channel','catalog_pageAAAAAAAACEDAAAAA',144185.84,0.00,23612.84
+'catalog channel','catalog_pageAAAAAAAACFAAAAAA',0.00,2046.36,-1765.63
+'catalog channel','catalog_pageAAAAAAAACFDAAAAA',87318.92,0.00,22201.70
+'catalog channel','catalog_pageAAAAAAAACGAAAAAA',0.00,1401.84,-382.86
+'catalog channel','catalog_pageAAAAAAAACGCAAAAA',0.00,498.96,-311.43
+'catalog channel','catalog_pageAAAAAAAACHAAAAAA',198097.84,0.00,-171.05
+'catalog channel','catalog_pageAAAAAAAACIAAAAAA',170669.13,0.00,-28180.55
+---- TYPES
+STRING, STRING, DECIMAL, DECIMAL, DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q67.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q67.test
new file mode 100644
index 0000000..77e5b26
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q67.test
@@ -0,0 +1,147 @@
+====
+---- QUERY: TPCDS-Q67
+select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100;
+---- RESULTS
+'Books','arts','amalgmaxi #10','NULL',NULL,NULL,NULL,'NULL',771942.81,94
+'Books','arts','amalgmaxi #12','NULL',NULL,NULL,NULL,'NULL',1404915.39,28
+'Books','arts','amalgmaxi #2','NULL',NULL,NULL,NULL,'NULL',843070.19,86
+'Books','arts','NULL','NULL',NULL,NULL,NULL,'NULL',4506213.38,17
+'Books','business','importomaxi #12','NULL',NULL,NULL,NULL,'NULL',976588.70,69
+'Books','business','importomaxi #2','NULL',NULL,NULL,NULL,'NULL',1070176.27,59
+'Books','business','importomaxi #4','NULL',NULL,NULL,NULL,'NULL',985988.99,66
+'Books','business','importomaxi #6','NULL',NULL,NULL,NULL,'NULL',759660.32,95
+'Books','business','importomaxi #8','NULL',NULL,NULL,NULL,'NULL',998679.05,63
+'Books','business','NULL','NULL',NULL,NULL,NULL,'NULL',5357940.76,15
+'Books','computers','exportimaxi #10','NULL',NULL,NULL,NULL,'NULL',892700.18,77
+'Books','computers','exportimaxi #12','NULL',NULL,NULL,NULL,'NULL',1334760.61,34
+'Books','computers','exportimaxi #2','NULL',NULL,NULL,NULL,'NULL',1491893.77,26
+'Books','computers','exportimaxi #4','NULL',NULL,NULL,NULL,'NULL',1375974.23,31
+'Books','computers','exportimaxi #6','NULL',NULL,NULL,NULL,'NULL',864545.72,80
+'Books','computers','exportimaxi #8','NULL',NULL,NULL,NULL,'NULL',1227706.86,43
+'Books','computers','NULL','NULL',NULL,NULL,NULL,'NULL',7187581.37,3
+'Books','cooking','amalgunivamalg #10','NULL',NULL,NULL,NULL,'NULL',828936.66,89
+'Books','cooking','amalgunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1199051.96,49
+'Books','cooking','amalgunivamalg #4','NULL',NULL,NULL,NULL,'NULL',1215546.70,46
+'Books','cooking','amalgunivamalg #6','NULL',NULL,NULL,NULL,'NULL',978464.05,68
+'Books','cooking','amalgunivamalg #8','NULL',NULL,NULL,NULL,'NULL',893562.91,76
+'Books','cooking','NULL','NULL',NULL,NULL,NULL,'NULL',5648185.86,13
+'Books','entertainments','edu packmaxi #10','NULL',NULL,NULL,NULL,'NULL',1063523.23,60
+'Books','entertainments','edu packmaxi #12','NULL',NULL,NULL,NULL,'NULL',812807.75,91
+'Books','entertainments','edu packmaxi #2','NULL',NULL,NULL,NULL,'NULL',850707.47,83
+'Books','entertainments','edu packmaxi #4','NULL',NULL,NULL,NULL,'NULL',1132602.81,56
+'Books','entertainments','edu packmaxi #6','NULL',NULL,NULL,NULL,'NULL',871965.57,79
+'Books','entertainments','edu packmaxi #8','NULL',NULL,NULL,NULL,'NULL',894401.25,75
+'Books','entertainments','NULL','NULL',NULL,NULL,NULL,'NULL',5705551.13,11
+'Books','fiction','scholarunivamalg #10','NULL',NULL,NULL,NULL,'NULL',1080330.14,58
+'Books','fiction','scholarunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1063243.41,61
+'Books','fiction','scholarunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1931852.77,18
+'Books','fiction','scholarunivamalg #6','NULL',NULL,NULL,NULL,'NULL',990232.80,65
+'Books','fiction','scholarunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1357801.34,32
+'Books','fiction','NULL','NULL',NULL,NULL,NULL,'NULL',6758618.52,7
+'Books','history','scholarmaxi #10','NULL',NULL,NULL,NULL,'NULL',1159896.65,51
+'Books','history','scholarmaxi #12','NULL',NULL,NULL,NULL,'NULL',1377383.04,30
+'Books','history','scholarmaxi #2','NULL',NULL,NULL,NULL,'NULL',1880968.59,19
+'Books','history','scholarmaxi #6','NULL',NULL,NULL,NULL,'NULL',1321161.65,37
+'Books','history','scholarmaxi #8','NULL',NULL,NULL,NULL,'NULL',951861.54,72
+'Books','history','NULL','NULL',NULL,NULL,NULL,'NULL',7175822.76,4
+'Books','home repair','importounivamalg #10','NULL',NULL,NULL,NULL,'NULL',975176.77,71
+'Books','home repair','importounivamalg #12','NULL',NULL,NULL,NULL,'NULL',851531.36,82
+'Books','home repair','importounivamalg #2','NULL',NULL,NULL,NULL,'NULL',1553230.30,22
+'Books','home repair','importounivamalg #4','NULL',NULL,NULL,NULL,'NULL',834733.96,88
+'Books','home repair','importounivamalg #6','NULL',NULL,NULL,NULL,'NULL',672760.54,100
+'Books','home repair','importounivamalg #8','NULL',NULL,NULL,NULL,'NULL',1748378.72,21
+'Books','home repair','NULL','NULL',NULL,NULL,NULL,'NULL',6635811.65,8
+'Books','mystery','corpunivamalg #10','NULL',NULL,NULL,NULL,'NULL',862402.84,81
+'Books','mystery','corpunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1132883.60,55
+'Books','mystery','corpunivamalg #2','NULL',NULL,NULL,NULL,'NULL',843818.57,84
+'Books','mystery','corpunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1002261.69,62
+'Books','mystery','corpunivamalg #8','NULL',NULL,NULL,NULL,'NULL',727595.71,98
+'Books','mystery','NULL','NULL',NULL,NULL,NULL,'NULL',5139547.86,16
+'Books','parenting','corpmaxi #10','NULL',NULL,NULL,NULL,'NULL',1297132.15,40
+'Books','parenting','corpmaxi #12','NULL',NULL,NULL,NULL,'NULL',994252.14,64
+'Books','parenting','corpmaxi #2','NULL',NULL,NULL,NULL,'NULL',817919.30,90
+'Books','parenting','corpmaxi #4','NULL',NULL,NULL,NULL,'NULL',1137537.84,53
+'Books','parenting','corpmaxi #6','NULL',NULL,NULL,NULL,'NULL',1756990.35,20
+'Books','parenting','corpmaxi #8','NULL',NULL,NULL,NULL,'NULL',786459.87,92
+'Books','parenting','NULL','NULL',NULL,NULL,NULL,'NULL',6790291.65,5
+'Books','reference','brandmaxi #10','NULL',NULL,NULL,NULL,'NULL',901096.84,74
+'Books','reference','brandmaxi #12','NULL',NULL,NULL,NULL,'NULL',1331752.35,35
+'Books','reference','brandmaxi #2','NULL',NULL,NULL,NULL,'NULL',975383.60,70
+'Books','reference','brandmaxi #4','NULL',NULL,NULL,NULL,'NULL',1492547.50,24
+'Books','reference','brandmaxi #6','NULL',NULL,NULL,NULL,'NULL',1277662.78,41
+'Books','reference','brandmaxi #8','NULL',NULL,NULL,NULL,'NULL',1354720.24,33
+'Books','reference','NULL','NULL',NULL,NULL,NULL,'NULL',7333163.31,2
+'Books','romance','namelessmaxi #10','NULL',NULL,NULL,NULL,'NULL',749766.99,96
+'Books','romance','namelessmaxi #12','NULL',NULL,NULL,NULL,'NULL',1297611.04,39
+'Books','romance','namelessmaxi #2','NULL',NULL,NULL,NULL,'NULL',1208741.34,47
+'Books','romance','namelessmaxi #4','NULL',NULL,NULL,NULL,'NULL',1303175.14,38
+'Books','romance','namelessmaxi #6','NULL',NULL,NULL,NULL,'NULL',1329372.02,36
+'Books','romance','namelessmaxi #8','NULL',NULL,NULL,NULL,'NULL',872633.95,78
+'Books','romance','NULL','NULL',NULL,NULL,NULL,'NULL',6761300.48,6
+'Books','science','maximaxi #10','NULL',NULL,NULL,NULL,'NULL',843554.63,85
+'Books','science','maximaxi #2','NULL',NULL,NULL,NULL,'NULL',837350.77,87
+'Books','science','maximaxi #6','NULL',NULL,NULL,NULL,'NULL',1492461.17,25
+'Books','science','maximaxi #8','NULL',NULL,NULL,NULL,'NULL',1200421.62,48
+'Books','science','NULL','NULL',NULL,NULL,NULL,'NULL',5457882.47,14
+'Books','self-help','exportiunivamalg #10','NULL',NULL,NULL,NULL,'NULL',1161828.20,50
+'Books','self-help','exportiunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1094139.27,57
+'Books','self-help','exportiunivamalg #4','NULL',NULL,NULL,NULL,'NULL',777051.93,93
+'Books','self-help','exportiunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1404452.50,29
+'Books','self-help','exportiunivamalg #8','NULL',NULL,NULL,NULL,'NULL',739121.55,97
+'Books','self-help','NULL','NULL',NULL,NULL,NULL,'NULL',5652129.32,12
+'Books','sports','edu packunivamalg #10','NULL',NULL,NULL,NULL,'NULL',1240893.26,42
+'Books','sports','edu packunivamalg #12','NULL',NULL,NULL,NULL,'NULL',914992.76,73
+'Books','sports','edu packunivamalg #4','NULL',NULL,NULL,NULL,'NULL',1137120.66,54
+'Books','sports','edu packunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1503138.10,23
+'Books','sports','edu packunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1222640.97,44
+'Books','sports','NULL','NULL',NULL,NULL,NULL,'NULL',6435144.67,9
+'Books','travel','univunivamalg #10','NULL',NULL,NULL,NULL,'NULL',705516.40,99
+'Books','travel','univunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1144238.62,52
+'Books','travel','univunivamalg #2','NULL',NULL,NULL,NULL,'NULL',980611.95,67
+'Books','travel','univunivamalg #4','NULL',NULL,NULL,NULL,'NULL',1220300.37,45
+'Books','travel','univunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1459320.64,27
+'Books','travel','NULL','NULL',NULL,NULL,NULL,'NULL',6168021.72,10
+'Books','NULL','NULL','NULL',NULL,NULL,NULL,'NULL',98805619.53,1
+---- TYPES
+STRING, STRING, STRING, STRING, INT, INT, INT, STRING, DECIMAL, BIGINT
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q77.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q77.test
new file mode 100644
index 0000000..4d9a7e0
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q77.test
@@ -0,0 +1,155 @@
+====
+---- QUERY: TPCDS-Q77
+with ss as
+ (select s_store_sk,
+ sum(ss_ext_sales_price) as sales,
+ sum(ss_net_profit) as profit
+ from store_sales,
+ date_dim,
+ store
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and ss_store_sk = s_store_sk
+ group by s_store_sk)
+ ,
+ sr as
+ (select s_store_sk,
+ sum(sr_return_amt) as `returns`,
+ sum(sr_net_loss) as profit_loss
+ from store_returns,
+ date_dim,
+ store
+ where sr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and sr_store_sk = s_store_sk
+ group by s_store_sk),
+ cs as
+ (select cs_call_center_sk,
+ sum(cs_ext_sales_price) as sales,
+ sum(cs_net_profit) as profit
+ from catalog_sales,
+ date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ group by cs_call_center_sk
+ ),
+ cr as
+ (select
+ sum(cr_return_amount) as `returns`,
+ sum(cr_net_loss) as profit_loss
+ from catalog_returns,
+ date_dim
+ where cr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ ),
+ ws as
+ ( select wp_web_page_sk,
+ sum(ws_ext_sales_price) as sales,
+ sum(ws_net_profit) as profit
+ from web_sales,
+ date_dim,
+ web_page
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and ws_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk),
+ wr as
+ (select wp_web_page_sk,
+ sum(wr_return_amt) as `returns`,
+ sum(wr_net_loss) as profit_loss
+ from web_returns,
+ date_dim,
+ web_page
+ where wr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + interval 30 days)
+ and wr_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(`returns`) as `returns`
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , ss.s_store_sk as id
+ , sales
+ , coalesce(`returns`, 0) as `returns`
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ss left join sr
+ on ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+ , cs_call_center_sk as id
+ , sales
+ , `returns`
+ , (profit - profit_loss) as profit
+ from cs
+ , cr
+ union all
+ select 'web channel' as channel
+ , ws.wp_web_page_sk as id
+ , sales
+ , coalesce(`returns`, 0) `returns`
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ws left join wr
+ on ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ ,sales desc
+ limit 100;
+---- RESULTS
+'catalog channel',1,26059763.88,1676013.07,-3439678.62
+'catalog channel',2,26848946.97,1676013.07,-3818396.48
+'catalog channel',4,27291299.87,1676013.07,-4012447.66
+'catalog channel',NULL,80395563.85,6704052.28,-12276833.88
+'catalog channel',NULL,195553.13,1676013.07,-1006311.12
+'store channel',1,18173324.99,458396.78,-7884303.32
+'store channel',2,19801551.45,482036.27,-8829753.47
+'store channel',4,19389251.68,487721.54,-8683007.74
+'store channel',7,18542305.22,441352.00,-8390550.61
+'store channel',8,18949957.95,520739.84,-8609781.61
+'store channel',10,19471021.10,424474.72,-8634992.52
+'store channel',NULL,114327412.39,2814721.15,-51032389.27
+'web channel',1,1368093.56,18413.39,-109814.89
+'web channel',2,1360282.03,20851.65,-262088.83
+'web channel',4,1406457.78,23644.41,-123413.05
+'web channel',7,1500004.47,21499.66,-154155.36
+'web channel',8,1446371.50,23082.66,-94682.60
+'web channel',10,1332588.96,17483.48,-116693.82
+'web channel',13,1482737.42,22291.09,-143363.78
+'web channel',14,1284016.42,27528.32,-109703.25
+'web channel',16,1243361.71,17003.20,-177836.49
+'web channel',19,1255323.32,19886.02,-164063.62
+'web channel',20,1453667.29,26935.69,-108401.67
+'web channel',22,1387121.58,13920.95,-178199.43
+'web channel',25,1335607.81,22147.53,-244732.70
+'web channel',26,1373568.78,13978.71,-203947.57
+'web channel',28,1421052.17,20238.90,-182295.36
+'web channel',31,1359126.56,33528.64,-187639.56
+'web channel',32,1352687.30,24759.85,-136361.30
+'web channel',34,1375278.93,20797.04,-128682.38
+'web channel',37,1280097.59,22871.42,-72902.31
+'web channel',38,1479886.09,19766.90,-165440.16
+'web channel',40,1412821.75,13967.60,-129666.09
+'web channel',43,1218207.79,12950.30,-267935.28
+'web channel',44,1394049.31,9287.77,-69579.24
+'web channel',46,1307728.82,20036.96,-169346.37
+'web channel',49,1523143.08,16493.90,-187746.56
+'web channel',50,1389142.56,26725.69,-75992.98
+'web channel',52,1428230.31,21974.82,-168225.24
+'web channel',55,1494853.34,33347.97,-143467.09
+'web channel',56,1390529.31,6835.23,-126132.78
+'web channel',58,1126120.88,31121.74,-218628.31
+'web channel',NULL,41182158.42,623371.49,-4621138.07
+'NULL',NULL,235905134.66,10142144.92,-67930361.22
+---- TYPES
+STRING, INT, DECIMAL, DECIMAL, DECIMAL
+====
diff --git a/tests/query_test/test_aggregation.py b/tests/query_test/test_aggregation.py
index 8106145..a4820e7 100644
--- a/tests/query_test/test_aggregation.py
+++ b/tests/query_test/test_aggregation.py
@@ -384,6 +384,12 @@ class TestAggregationQueries(ImpalaTestSuite):
for i in xrange(14, 16):
self.appx_equals(int(sampled_ndv_vals[i]) * sample_perc, int(ndv_vals[i]), 2.0)
+ def test_grouping_sets(self, vector):
+ """Tests for ROLLUP, CUBE and GROUPING SETS."""
+ if vector.get_value('table_format').file_format == 'hbase':
+ pytest.xfail(reason="IMPALA-283 - HBase null handling is inconsistent")
+ self.run_test_case('QueryTest/grouping-sets', vector)
+
class TestDistinctAggregation(ImpalaTestSuite):
"""Run the distinct aggregation test suite, with codegen and shuffle_distinct_exprs
diff --git a/tests/query_test/test_tpcds_queries.py b/tests/query_test/test_tpcds_queries.py
index f9e911d..40d9dfe 100644
--- a/tests/query_test/test_tpcds_queries.py
+++ b/tests/query_test/test_tpcds_queries.py
@@ -351,6 +351,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q4(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q4', vector)
+ def test_tpcds_q5(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q5', vector)
+
def test_tpcds_q6(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q6', vector)
@@ -384,6 +387,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q17(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q17', vector)
+ def test_tpcds_q18(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q18', vector)
+
def test_tpcds_q18a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q18a', vector)
@@ -396,6 +402,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q21(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q21', vector)
+ def test_tpcds_q22(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q22', vector)
+
def test_tpcds_q22a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q22a', vector)
@@ -510,6 +519,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q65(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q65', vector)
+ def test_tpcds_q67(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q67', vector)
+
def test_tpcds_q67a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q67a', vector)
@@ -540,6 +552,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q76(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q76', vector)
+ def test_tpcds_q77(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q77', vector)
+
def test_tpcds_q77a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q77a', vector)
diff --git a/tests/util/parse_util.py b/tests/util/parse_util.py
index 80b1c1d..c5105ee 100644
--- a/tests/util/parse_util.py
+++ b/tests/util/parse_util.py
@@ -22,7 +22,7 @@ from datetime import datetime
# changed, and the stress test loses the ability to run the full set of queries. Set
# these constants and assert that when a workload is used, all the queries we expect to
# use are there.
-EXPECTED_TPCDS_QUERIES_COUNT = 87
+EXPECTED_TPCDS_QUERIES_COUNT = 92
EXPECTED_TPCH_NESTED_QUERIES_COUNT = 22
EXPECTED_TPCH_QUERIES_COUNT = 22
# Add the number of stress test specific queries, i.e. in files like '*-stress-*.test'