You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by jc...@apache.org on 2016/12/15 18:36:07 UTC
[2/4] hive git commit: HIVE-15409: Add support for GROUPING function
with grouping sets (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out
index f4b0c91..e2d9d96 100644
--- a/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out
+++ b/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out
@@ -37,21 +37,21 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count()
- keys: a (type: string), b (type: string), '0' (type: string)
+ keys: a (type: string), b (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE
@@ -118,21 +118,21 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count()
- keys: a (type: string), b (type: string), '0' (type: string)
+ keys: a (type: string), b (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE
@@ -199,21 +199,21 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count()
- keys: a (type: string), b (type: string), '0' (type: string)
+ keys: a (type: string), b (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
@@ -279,19 +279,19 @@ STAGE PLANS:
outputColumnNames: a, b, c
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
- keys: a (type: string), b (type: string), c (type: string), '0' (type: string)
+ keys: a (type: string), b (type: string), c (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 108 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: int)
sort order: ++++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: int)
Statistics: Num rows: 3 Data size: 108 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
Reduce Operator Tree:
Group By Operator
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
@@ -357,19 +357,19 @@ STAGE PLANS:
outputColumnNames: a
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
- keys: a (type: string), '0' (type: string)
+ keys: a (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
Reduce Operator Tree:
Group By Operator
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
@@ -427,21 +427,21 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count()
- keys: _col0 (type: double), '0' (type: string)
+ keys: _col0 (type: double), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: double), _col1 (type: string)
+ key expressions: _col0 (type: double), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: double), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: double), _col1 (type: int)
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
TopN Hash Memory Usage: 0.1
value expressions: _col2 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: double), KEY._col1 (type: string)
+ keys: KEY._col0 (type: double), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col2
Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/groupby_grouping_window.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/groupby_grouping_window.q.out b/ql/src/test/results/clientpositive/groupby_grouping_window.q.out
index 5cd9737..251f4f7 100644
--- a/ql/src/test/results/clientpositive/groupby_grouping_window.q.out
+++ b/ql/src/test/results/clientpositive/groupby_grouping_window.q.out
@@ -49,20 +49,20 @@ STAGE PLANS:
Statistics: Num rows: 3 Data size: 20 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: max(live), max(comments)
- keys: category (type: int), '0' (type: string)
+ keys: category (type: int), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 6 Data size: 40 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: int), _col1 (type: string)
+ key expressions: _col0 (type: int), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: int), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
Statistics: Num rows: 6 Data size: 40 Basic stats: COMPLETE Column stats: NONE
value expressions: _col2 (type: int), _col3 (type: int)
Reduce Operator Tree:
Group By Operator
aggregations: max(VALUE._col0), max(VALUE._col1)
- keys: KEY._col0 (type: int), KEY._col1 (type: string)
+ keys: KEY._col0 (type: int), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col2, _col3
Statistics: Num rows: 3 Data size: 20 Basic stats: COMPLETE Column stats: NONE
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/groupby_rollup1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/groupby_rollup1.q.out b/ql/src/test/results/clientpositive/groupby_rollup1.q.out
index 19dccc8..5437315 100644
--- a/ql/src/test/results/clientpositive/groupby_rollup1.q.out
+++ b/ql/src/test/results/clientpositive/groupby_rollup1.q.out
@@ -41,20 +41,20 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
@@ -120,19 +120,19 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(DISTINCT val)
- keys: key (type: string), '0' (type: string), val (type: string)
+ keys: key (type: string), 0 (type: int), val (type: string)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int), _col2 (type: string)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
Reduce Operator Tree:
Group By Operator
aggregations: count(DISTINCT KEY._col2:0._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col2
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
@@ -193,12 +193,12 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: rand() (type: double)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -206,7 +206,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: partials
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -222,7 +222,7 @@ STAGE PLANS:
Map Operator Tree:
TableScan
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -230,7 +230,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: final
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
@@ -297,19 +297,19 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(DISTINCT val)
- keys: key (type: string), '0' (type: string), val (type: string)
+ keys: key (type: string), 0 (type: int), val (type: string)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int), _col2 (type: string)
sort order: +++
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
Reduce Operator Tree:
Group By Operator
aggregations: count(DISTINCT KEY._col2:0._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: partials
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
@@ -325,7 +325,7 @@ STAGE PLANS:
Map Operator Tree:
TableScan
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 2 Data size: 60 Basic stats: COMPLETE Column stats: NONE
@@ -333,7 +333,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: final
outputColumnNames: _col0, _col2
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
@@ -419,12 +419,12 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: key (type: string), val (type: string), '0' (type: string)
+ keys: key (type: string), val (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: rand() (type: double)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -435,7 +435,7 @@ STAGE PLANS:
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: sum(1)
- keys: key (type: string), val (type: string), '0' (type: string)
+ keys: key (type: string), val (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -448,7 +448,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: partials
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -464,7 +464,7 @@ STAGE PLANS:
Map Operator Tree:
TableScan
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -472,7 +472,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: final
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
@@ -508,7 +508,7 @@ STAGE PLANS:
Map Operator Tree:
TableScan
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: rand() (type: double)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -516,7 +516,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: partials
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -532,7 +532,7 @@ STAGE PLANS:
Map Operator Tree:
TableScan
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
Statistics: Num rows: 3 Data size: 90 Basic stats: COMPLETE Column stats: NONE
@@ -540,7 +540,7 @@ STAGE PLANS:
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: final
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/infer_bucket_sort_grouping_operators.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/infer_bucket_sort_grouping_operators.q.out b/ql/src/test/results/clientpositive/infer_bucket_sort_grouping_operators.q.out
index 573469b..84021a3 100644
--- a/ql/src/test/results/clientpositive/infer_bucket_sort_grouping_operators.q.out
+++ b/ql/src/test/results/clientpositive/infer_bucket_sort_grouping_operators.q.out
@@ -48,20 +48,20 @@ STAGE PLANS:
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 750 Data size: 7968 Basic stats: COMPLETE Column stats: NONE
@@ -1486,20 +1486,20 @@ STAGE PLANS:
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 2000 Data size: 21248 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 2000 Data size: 21248 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 1000 Data size: 10624 Basic stats: COMPLETE Column stats: NONE
@@ -1670,20 +1670,20 @@ STAGE PLANS:
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(1)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 1000 Data size: 10624 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 1000 Data size: 10624 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/limit_pushdown2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/limit_pushdown2.q.out b/ql/src/test/results/clientpositive/limit_pushdown2.q.out
index 316d8e8..b44b529 100644
--- a/ql/src/test/results/clientpositive/limit_pushdown2.q.out
+++ b/ql/src/test/results/clientpositive/limit_pushdown2.q.out
@@ -709,20 +709,20 @@ STAGE PLANS:
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: avg(_col2)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: struct<count:bigint,sum:double,input:double>)
Reduce Operator Tree:
Group By Operator
aggregations: avg(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 750 Data size: 7968 Basic stats: COMPLETE Column stats: NONE
@@ -798,20 +798,20 @@ STAGE PLANS:
Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: avg(_col2)
- keys: _col0 (type: string), _col1 (type: string), '0' (type: string)
+ keys: _col0 (type: string), _col1 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
sort order: +++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int)
Statistics: Num rows: 1500 Data size: 15936 Basic stats: COMPLETE Column stats: NONE
value expressions: _col3 (type: struct<count:bigint,sum:double,input:double>)
Reduce Operator Tree:
Group By Operator
aggregations: avg(VALUE._col0)
- keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col3
Statistics: Num rows: 750 Data size: 7968 Basic stats: COMPLETE Column stats: NONE
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/llap/vector_grouping_sets.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/llap/vector_grouping_sets.q.out b/ql/src/test/results/clientpositive/llap/vector_grouping_sets.q.out
index 127478e..2fb434c 100644
--- a/ql/src/test/results/clientpositive/llap/vector_grouping_sets.q.out
+++ b/ql/src/test/results/clientpositive/llap/vector_grouping_sets.q.out
@@ -159,14 +159,14 @@ STAGE PLANS:
outputColumnNames: s_store_id
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
Group By Operator
- keys: s_store_id (type: string), '0' (type: string)
+ keys: s_store_id (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Execution mode: llap
LLAP IO: all inputs
@@ -174,7 +174,7 @@ STAGE PLANS:
Execution mode: llap
Reduce Operator Tree:
Group By Operator
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
@@ -244,14 +244,14 @@ STAGE PLANS:
outputColumnNames: _col0
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
Group By Operator
- keys: _col0 (type: string), '0' (type: string)
+ keys: _col0 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Execution mode: llap
LLAP IO: all inputs
@@ -259,12 +259,12 @@ STAGE PLANS:
Execution mode: vectorized, llap
Reduce Operator Tree:
Group By Operator
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
Select Operator
- expressions: _col0 (type: string), _col1 (type: string)
+ expressions: _col0 (type: string), _col1 (type: int)
outputColumnNames: _col0, _col1
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
File Output Operator
@@ -332,14 +332,14 @@ STAGE PLANS:
outputColumnNames: _col0
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
Group By Operator
- keys: _col0 (type: string), '0' (type: string)
+ keys: _col0 (type: string), 0 (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
- key expressions: _col0 (type: string), _col1 (type: string)
+ key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
- Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
+ Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 24 Data size: 51264 Basic stats: COMPLETE Column stats: NONE
Execution mode: llap
LLAP IO: all inputs
@@ -347,12 +347,12 @@ STAGE PLANS:
Execution mode: vectorized, llap
Reduce Operator Tree:
Group By Operator
- keys: KEY._col0 (type: string), KEY._col1 (type: string)
+ keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
Select Operator
- expressions: _col0 (type: string), _col1 (type: string)
+ expressions: _col0 (type: string), _col1 (type: int)
outputColumnNames: _col0, _col1
Statistics: Num rows: 12 Data size: 25632 Basic stats: COMPLETE Column stats: NONE
File Output Operator
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query18.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query18.q.out b/ql/src/test/results/clientpositive/perf/query18.q.out
index 1f4cfdb..cf11954 100644
--- a/ql/src/test/results/clientpositive/perf/query18.q.out
+++ b/ql/src/test/results/clientpositive/perf/query18.q.out
@@ -34,7 +34,7 @@ Stage-0
SHUFFLE [RS_43]
PartitionCols:_col0, _col1, _col2, _col3, _col4
Group By Operator [GBY_42] (rows=2108229765 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11"],aggregations:["avg(_col4)","avg(_col5)","avg(_col6)","avg(_col7)","avg(_col8)","avg(_col9)","avg(_col10)"],keys:_col0, _col1, _col2, _col3, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11"],aggregations:["avg(_col4)","avg(_col5)","avg(_col6)","avg(_col7)","avg(_col8)","avg(_col9)","avg(_col10)"],keys:_col0, _col1, _col2, _col3, 0
Select Operator [SEL_40] (rows=421645953 width=135)
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
Merge Join Operator [MERGEJOIN_83] (rows=421645953 width=135)
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query22.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query22.q.out b/ql/src/test/results/clientpositive/perf/query22.q.out
index 52fc566..6ff8896 100644
--- a/ql/src/test/results/clientpositive/perf/query22.q.out
+++ b/ql/src/test/results/clientpositive/perf/query22.q.out
@@ -1,6 +1,44 @@
-PREHOOK: query: explain select i_product_name ,i_brand ,i_class ,i_category ,avg(inv_quantity_on_hand) qoh from inventory ,date_dim ,item ,warehouse where inventory.inv_date_sk=date_dim.d_date_sk and inventory.inv_item_sk=item.i_item_sk and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk and date_dim.d_month_seq between 1193 and 1193 + 11 group by i_product_name ,i_brand ,i_class ,i_category with rollup order by qoh, i_product_name, i_brand, i_class, i_category limit 100
+PREHOOK: query: explain
+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
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_product_name ,i_brand ,i_class ,i_category ,avg(inv_quantity_on_hand) qoh from inventory ,date_dim ,item ,warehouse where inventory.inv_date_sk=date_dim.d_date_sk and inventory.inv_item_sk=item.i_item_sk and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk and date_dim.d_month_seq between 1193 and 1193 + 11 group by i_product_name ,i_brand ,i_class ,i_category with rollup order by qoh, i_product_name, i_brand, i_class, i_category limit 100
+POSTHOOK: query: explain
+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
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -31,7 +69,7 @@ Stage-0
SHUFFLE [RS_23]
PartitionCols:_col0, _col1, _col2, _col3, _col4
Group By Operator [GBY_22] (rows=250121525 width=15)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["avg(_col3)"],keys:_col8, _col9, _col10, _col11, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["avg(_col3)"],keys:_col8, _col9, _col10, _col11, 0
Select Operator [SEL_21] (rows=50024305 width=15)
Output:["_col8","_col9","_col10","_col11","_col3"]
Merge Join Operator [MERGEJOIN_46] (rows=50024305 width=15)
@@ -79,7 +117,7 @@ Stage-0
Select Operator [SEL_5] (rows=8116 width=1119)
Output:["_col0"]
Filter Operator [FIL_41] (rows=8116 width=1119)
- predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null)
+ predicate:(d_month_seq BETWEEN 1212 AND 1223 and d_date_sk is not null)
TableScan [TS_3] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"]
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query27.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query27.q.out b/ql/src/test/results/clientpositive/perf/query27.q.out
index 40ce084..cbd7d29 100644
--- a/ql/src/test/results/clientpositive/perf/query27.q.out
+++ b/ql/src/test/results/clientpositive/perf/query27.q.out
@@ -1,6 +1,46 @@
-PREHOOK: query: explain select i_item_id, s_state, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 from store_sales, customer_demographics, date_dim, store, item where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_item_sk = item.i_item_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk and customer_demographics.cd_gender = 'F' and customer_demographics.cd_marital_status = 'D' and customer_demographics.cd_education_status = 'Unknown' and date_dim.d_year = 1998 and store.s_state in ('KS','AL', 'MN', 'AL', 'SC', 'VT') group by i_item_id, s_state order by i_item_id ,s_state limit 100
+PREHOOK: query: explain
+select i_item_id,
+ s_state, grouping(s_state) g_state,
+ avg(ss_quantity) agg1,
+ avg(ss_list_price) agg2,
+ avg(ss_coupon_amt) agg3,
+ avg(ss_sales_price) agg4
+ from store_sales, customer_demographics, 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
+ ss_cdemo_sk = cd_demo_sk and
+ cd_gender = 'M' and
+ cd_marital_status = 'U' and
+ cd_education_status = '2 yr Degree' and
+ d_year = 2001 and
+ s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
+ group by rollup (i_item_id, s_state)
+ order by i_item_id
+ ,s_state
+ limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_id, s_state, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 from store_sales, customer_demographics, date_dim, store, item where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_item_sk = item.i_item_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk and customer_demographics.cd_gender = 'F' and customer_demographics.cd_marital_status = 'D' and customer_demographics.cd_education_status = 'Unknown' and date_dim.d_year = 1998 and store.s_state in ('KS','AL', 'MN', 'AL', 'SC', 'VT') group by i_item_id, s_state order by i_item_id ,s_state limit 100
+POSTHOOK: query: explain
+select i_item_id,
+ s_state, grouping(s_state) g_state,
+ avg(ss_quantity) agg1,
+ avg(ss_list_price) agg2,
+ avg(ss_coupon_amt) agg3,
+ avg(ss_sales_price) agg4
+ from store_sales, customer_demographics, 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
+ ss_cdemo_sk = cd_demo_sk and
+ cd_gender = 'M' and
+ cd_marital_status = 'U' and
+ cd_education_status = '2 yr Degree' and
+ d_year = 2001 and
+ s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
+ group by rollup (i_item_id, s_state)
+ order by i_item_id
+ ,s_state
+ limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -17,82 +57,84 @@ Stage-0
limit:100
Stage-1
Reducer 7
- File Output Operator [FS_35]
- Limit [LIM_34] (rows=100 width=88)
+ File Output Operator [FS_36]
+ Limit [LIM_35] (rows=100 width=88)
Number of rows:100
- Select Operator [SEL_33] (rows=421657640 width=88)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Select Operator [SEL_34] (rows=1264972921 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
<-Reducer 6 [SIMPLE_EDGE]
- SHUFFLE [RS_32]
- Group By Operator [GBY_30] (rows=421657640 width=88)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","avg(VALUE._col3)"],keys:KEY._col0, KEY._col1
- <-Reducer 5 [SIMPLE_EDGE]
- SHUFFLE [RS_29]
- PartitionCols:_col0, _col1
- Group By Operator [GBY_28] (rows=843315281 width=88)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["avg(_col4)","avg(_col5)","avg(_col7)","avg(_col6)"],keys:_col17, _col15
- Select Operator [SEL_27] (rows=843315281 width=88)
- Output:["_col17","_col15","_col4","_col5","_col7","_col6"]
- Merge Join Operator [MERGEJOIN_58] (rows=843315281 width=88)
- Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col15","_col17"]
- <-Map 11 [SIMPLE_EDGE]
- SHUFFLE [RS_25]
- PartitionCols:_col0
- Select Operator [SEL_14] (rows=462000 width=1436)
- Output:["_col0","_col1"]
- Filter Operator [FIL_54] (rows=462000 width=1436)
- predicate:i_item_sk is not null
- TableScan [TS_12] (rows=462000 width=1436)
- default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"]
- <-Reducer 4 [SIMPLE_EDGE]
- SHUFFLE [RS_24]
- PartitionCols:_col1
- Merge Join Operator [MERGEJOIN_57] (rows=766650239 width=88)
- Conds:RS_21._col3=RS_22._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col15"]
- <-Map 10 [SIMPLE_EDGE]
- SHUFFLE [RS_22]
- PartitionCols:_col0
- Select Operator [SEL_11] (rows=852 width=1910)
- Output:["_col0","_col1"]
- Filter Operator [FIL_53] (rows=852 width=1910)
- predicate:((s_state) IN ('KS', 'AL', 'MN', 'SC', 'VT') and s_store_sk is not null)
- TableScan [TS_9] (rows=1704 width=1910)
- default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"]
- <-Reducer 3 [SIMPLE_EDGE]
- SHUFFLE [RS_21]
- PartitionCols:_col3
- Merge Join Operator [MERGEJOIN_56] (rows=696954748 width=88)
- Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"]
- <-Map 9 [SIMPLE_EDGE]
- SHUFFLE [RS_19]
- PartitionCols:_col0
- Select Operator [SEL_8] (rows=36524 width=1119)
- Output:["_col0"]
- Filter Operator [FIL_52] (rows=36524 width=1119)
- predicate:((d_year = 1998) and d_date_sk is not null)
- TableScan [TS_6] (rows=73049 width=1119)
- default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
- <-Reducer 2 [SIMPLE_EDGE]
- SHUFFLE [RS_18]
- PartitionCols:_col0
- Merge Join Operator [MERGEJOIN_55] (rows=633595212 width=88)
- Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"]
- <-Map 1 [SIMPLE_EDGE]
- SHUFFLE [RS_15]
- PartitionCols:_col2
- Select Operator [SEL_2] (rows=575995635 width=88)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
- Filter Operator [FIL_50] (rows=575995635 width=88)
- predicate:(ss_cdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null and ss_item_sk is not null)
- TableScan [TS_0] (rows=575995635 width=88)
- default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_store_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"]
- <-Map 8 [SIMPLE_EDGE]
- SHUFFLE [RS_16]
- PartitionCols:_col0
- Select Operator [SEL_5] (rows=232725 width=385)
- Output:["_col0"]
- Filter Operator [FIL_51] (rows=232725 width=385)
- predicate:((cd_gender = 'F') and (cd_marital_status = 'D') and (cd_education_status = 'Unknown') and cd_demo_sk is not null)
- TableScan [TS_3] (rows=1861800 width=385)
- default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"]
+ SHUFFLE [RS_33]
+ Select Operator [SEL_32] (rows=1264972921 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Group By Operator [GBY_31] (rows=1264972921 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","avg(VALUE._col3)"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_30]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_29] (rows=2529945843 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["avg(_col2)","avg(_col3)","avg(_col4)","avg(_col5)"],keys:_col0, _col1, 0
+ Select Operator [SEL_27] (rows=843315281 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Merge Join Operator [MERGEJOIN_59] (rows=843315281 width=88)
+ Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col15","_col17"]
+ <-Map 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_25]
+ PartitionCols:_col0
+ Select Operator [SEL_14] (rows=462000 width=1436)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_55] (rows=462000 width=1436)
+ predicate:i_item_sk is not null
+ TableScan [TS_12] (rows=462000 width=1436)
+ default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_24]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_58] (rows=766650239 width=88)
+ Conds:RS_21._col3=RS_22._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col15"]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_22]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=852 width=1910)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_54] (rows=852 width=1910)
+ predicate:((s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC') and s_store_sk is not null)
+ TableScan [TS_9] (rows=1704 width=1910)
+ default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_21]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_57] (rows=696954748 width=88)
+ Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_19]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_53] (rows=36524 width=1119)
+ predicate:((d_year = 2001) and d_date_sk is not null)
+ TableScan [TS_6] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_56] (rows=633595212 width=88)
+ Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_15]
+ PartitionCols:_col2
+ Select Operator [SEL_2] (rows=575995635 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Filter Operator [FIL_51] (rows=575995635 width=88)
+ predicate:(ss_cdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null and ss_item_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=88)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_store_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_16]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=232725 width=385)
+ Output:["_col0"]
+ Filter Operator [FIL_52] (rows=232725 width=385)
+ predicate:((cd_gender = 'M') and (cd_marital_status = 'U') and (cd_education_status = '2 yr Degree') and cd_demo_sk is not null)
+ TableScan [TS_3] (rows=1861800 width=385)
+ default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"]
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query36.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query36.q.out b/ql/src/test/results/clientpositive/perf/query36.q.out
new file mode 100644
index 0000000..f5ec0f9
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query36.q.out
@@ -0,0 +1,149 @@
+PREHOOK: query: explain
+select
+ sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
+ ,i_category
+ ,i_class
+ ,grouping(i_category)+grouping(i_class) as lochierarchy
+ ,rank() over (
+ partition by grouping(i_category)+grouping(i_class),
+ case when grouping(i_class) = 0 then i_category end
+ order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
+ from
+ store_sales
+ ,date_dim d1
+ ,item
+ ,store
+ where
+ d1.d_year = 1999
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and s_state in ('SD','FL','MI','LA',
+ 'MO','SC','AL','GA')
+ group by rollup(i_category,i_class)
+ order by
+ lochierarchy desc
+ ,case when lochierarchy = 0 then i_category end
+ ,rank_within_parent
+ limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select
+ sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
+ ,i_category
+ ,i_class
+ ,grouping(i_category)+grouping(i_class) as lochierarchy
+ ,rank() over (
+ partition by grouping(i_category)+grouping(i_class),
+ case when grouping(i_class) = 0 then i_category end
+ order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
+ from
+ store_sales
+ ,date_dim d1
+ ,item
+ ,store
+ where
+ d1.d_year = 1999
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and s_state in ('SD','FL','MI','LA',
+ 'MO','SC','AL','GA')
+ group by rollup(i_category,i_class)
+ order by
+ lochierarchy desc
+ ,case when lochierarchy = 0 then i_category end
+ ,rank_within_parent
+ limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_35]
+ Limit [LIM_34] (rows=100 width=88)
+ Number of rows:100
+ Select Operator [SEL_33] (rows=1149975358 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_32]
+ Select Operator [SEL_30] (rows=1149975358 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ PTF Operator [PTF_29] (rows=1149975358 width=88)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"(_col4 / _col5) ASC NULLS FIRST","partition by:":"(grouping(_col6, 0) + grouping(_col6, 1)), CASE WHEN ((UDFToInteger(grouping(_col6, 1)) = 0)) THEN (_col0) ELSE (null) END"}]
+ Select Operator [SEL_28] (rows=1149975358 width=88)
+ Output:["_col0","_col1","_col4","_col5","_col6"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ PartitionCols:(grouping(_col6, 0) + grouping(_col6, 1)), CASE WHEN ((UDFToInteger(grouping(_col6, 1)) = 0)) THEN (_col0) ELSE (null) END
+ Select Operator [SEL_26] (rows=1149975358 width=88)
+ Output:["_col0","_col1","_col4","_col5","_col6"]
+ Group By Operator [GBY_25] (rows=1149975358 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_24]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_23] (rows=2299950717 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col3)"],keys:_col0, _col1, 0
+ Select Operator [SEL_21] (rows=766650239 width=88)
+ Output:["_col0","_col1","_col2","_col3"]
+ Merge Join Operator [MERGEJOIN_51] (rows=766650239 width=88)
+ Conds:RS_18._col1=RS_19._col0(Inner),Output:["_col3","_col4","_col10","_col11"]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_19]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=462000 width=1436)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_48] (rows=462000 width=1436)
+ predicate:i_item_sk is not null
+ TableScan [TS_9] (rows=462000 width=1436)
+ default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_class","i_category"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_50] (rows=696954748 width=88)
+ Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col1","_col3","_col4"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_16]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=852 width=1910)
+ Output:["_col0"]
+ Filter Operator [FIL_47] (rows=852 width=1910)
+ predicate:((s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC', 'AL', 'GA') and s_store_sk is not null)
+ TableScan [TS_6] (rows=1704 width=1910)
+ default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_15]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_49] (rows=633595212 width=88)
+ Conds:RS_12._col0=RS_13._col0(Inner),Output:["_col1","_col2","_col3","_col4"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=575995635 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_45] (rows=575995635 width=88)
+ predicate:(ss_sold_date_sk is not null and ss_item_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=88)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_13]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_46] (rows=36524 width=1119)
+ predicate:((d_year = 1999) and d_date_sk is not null)
+ TableScan [TS_3] (rows=73049 width=1119)
+ default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query67.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query67.q.out b/ql/src/test/results/clientpositive/perf/query67.q.out
index 41cd274..1f38027 100644
--- a/ql/src/test/results/clientpositive/perf/query67.q.out
+++ b/ql/src/test/results/clientpositive/perf/query67.q.out
@@ -1,4 +1,4 @@
-PREHOOK: query: explain
+PREHOOK: query: explain
select *
from (select i_category
,i_class
@@ -23,11 +23,11 @@ from (select i_category
,date_dim
,store
,item
- where store_sales.ss_sold_date_sk=date_dim.d_date_sk
- and store_sales.ss_item_sk=item.i_item_sk
- and store_sales.ss_store_sk = store.s_store_sk
- and d_month_seq between 1193 and 1193+11
- group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id with rollup)dw1) dw2
+ 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
@@ -41,7 +41,7 @@ order by i_category
,rk
limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: explain
select *
from (select i_category
,i_class
@@ -66,11 +66,11 @@ from (select i_category
,date_dim
,store
,item
- where store_sales.ss_sold_date_sk=date_dim.d_date_sk
- and store_sales.ss_item_sk=item.i_item_sk
- and store_sales.ss_store_sk = store.s_store_sk
- and d_month_seq between 1193 and 1193+11
- group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id with rollup)dw1) dw2
+ 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
@@ -125,7 +125,7 @@ Stage-0
SHUFFLE [RS_24]
PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8
Group By Operator [GBY_23] (rows=6899852151 width=88)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(_col8)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(_col8)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, 0
Select Operator [SEL_21] (rows=766650239 width=88)
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
Merge Join Operator [MERGEJOIN_54] (rows=766650239 width=88)
@@ -173,7 +173,7 @@ Stage-0
Select Operator [SEL_5] (rows=8116 width=1119)
Output:["_col0","_col2","_col3","_col4"]
Filter Operator [FIL_49] (rows=8116 width=1119)
- predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null)
+ predicate:(d_month_seq BETWEEN 1212 AND 1223 and d_date_sk is not null)
TableScan [TS_3] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq","d_year","d_moy","d_qoy"]
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query70.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query70.q.out b/ql/src/test/results/clientpositive/perf/query70.q.out
index 611af74..581fd9b 100644
--- a/ql/src/test/results/clientpositive/perf/query70.q.out
+++ b/ql/src/test/results/clientpositive/perf/query70.q.out
@@ -1,6 +1,76 @@
-PREHOOK: query: explain select sum(ss_net_profit) as total_sum ,s_state ,s_county ,grouping__id as lochierarchy , rank() over(partition by grouping__id, case when grouping__id == 2 then s_state end order by sum(ss_net_profit)) as rank_within_parent from store_sales ss join date_dim d1 on d1.d_date_sk = ss.ss_sold_date_sk join store s on s.s_store_sk = ss.ss_store_sk where d1.d_month_seq between 1193 and 1193+11 and s.s_state in ( select s_state from (select s_state as s_state, sum(ss_net_profit), rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking from store_sales, store, date_dim where d_month_seq between 1193 and 1193+11 and date_dim.d_date_sk = store_sales.ss_sold_date_sk and store.s_store_sk = store_sales.ss_store_sk group by s_state ) tmp1 where ranking <= 5 ) group by s_state,s_county with rollup order by lochierarchy desc ,case when lochierarchy = 0 then s_state end ,rank_within_parent limit 100
+PREHOOK: query: explain
+select
+ sum(ss_net_profit) as total_sum
+ ,s_state
+ ,s_county
+ ,grouping(s_state)+grouping(s_county) as lochierarchy
+ ,rank() over (
+ partition by grouping(s_state)+grouping(s_county),
+ case when grouping(s_county) = 0 then s_state end
+ order by sum(ss_net_profit) desc) as rank_within_parent
+ from
+ store_sales
+ ,date_dim d1
+ ,store s
+ where
+ d1.d_month_seq between 1212 and 1212+11
+ and d1.d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ and s.s_state in
+ ( select s_state
+ from (select s_state as s_state,
+ rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
+ from store_sales, store, date_dim
+ where d_month_seq between 1212 and 1212+11
+ and d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ group by s_state
+ ) tmp1
+ where ranking <= 5
+ )
+ group by rollup(s_state,s_county)
+ order by
+ lochierarchy desc
+ ,case when lochierarchy = 0 then s_state end
+ ,rank_within_parent
+ limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select sum(ss_net_profit) as total_sum ,s_state ,s_county ,grouping__id as lochierarchy , rank() over(partition by grouping__id, case when grouping__id == 2 then s_state end order by sum(ss_net_profit)) as rank_within_parent from store_sales ss join date_dim d1 on d1.d_date_sk = ss.ss_sold_date_sk join store s on s.s_store_sk = ss.ss_store_sk where d1.d_month_seq between 1193 and 1193+11 and s.s_state in ( select s_state from (select s_state as s_state, sum(ss_net_profit), rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking from store_sales, store, date_dim where d_month_seq between 1193 and 1193+11 and date_dim.d_date_sk = store_sales.ss_sold_date_sk and store.s_store_sk = store_sales.ss_store_sk group by s_state ) tmp1 where ranking <= 5 ) group by s_state,s_county with rollup order by lochierarchy desc ,case when lochierarchy = 0 then s_state end ,rank_within_parent limit 100
+POSTHOOK: query: explain
+select
+ sum(ss_net_profit) as total_sum
+ ,s_state
+ ,s_county
+ ,grouping(s_state)+grouping(s_county) as lochierarchy
+ ,rank() over (
+ partition by grouping(s_state)+grouping(s_county),
+ case when grouping(s_county) = 0 then s_state end
+ order by sum(ss_net_profit) desc) as rank_within_parent
+ from
+ store_sales
+ ,date_dim d1
+ ,store s
+ where
+ d1.d_month_seq between 1212 and 1212+11
+ and d1.d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ and s.s_state in
+ ( select s_state
+ from (select s_state as s_state,
+ rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
+ from store_sales, store, date_dim
+ where d_month_seq between 1212 and 1212+11
+ and d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ group by s_state
+ ) tmp1
+ where ranking <= 5
+ )
+ group by rollup(s_state,s_county)
+ order by
+ lochierarchy desc
+ ,case when lochierarchy = 0 then s_state end
+ ,rank_within_parent
+ limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -31,12 +101,12 @@ Stage-0
Select Operator [SEL_58] (rows=1045432122 width=88)
Output:["_col0","_col1","_col2","_col3","_col4"]
PTF Operator [PTF_57] (rows=1045432122 width=88)
- Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col4 ASC NULLS FIRST","partition by:":"_col5, CASE WHEN ((_col5 = 2)) THEN (_col0) ELSE (null) END"}]
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col4 DESC NULLS LAST","partition by:":"(grouping(_col5, 0) + grouping(_col5, 1)), CASE WHEN ((UDFToInteger(grouping(_col5, 1)) = 0)) THEN (_col0) ELSE (null) END"}]
Select Operator [SEL_56] (rows=1045432122 width=88)
Output:["_col0","_col1","_col4","_col5"]
<-Reducer 4 [SIMPLE_EDGE]
SHUFFLE [RS_55]
- PartitionCols:_col5, CASE WHEN ((_col5 = 2)) THEN (_col0) ELSE (null) END
+ PartitionCols:(grouping(_col5, 0) + grouping(_col5, 1)), CASE WHEN ((UDFToInteger(grouping(_col5, 1)) = 0)) THEN (_col0) ELSE (null) END
Select Operator [SEL_54] (rows=1045432122 width=88)
Output:["_col0","_col1","_col4","_col5"]
Group By Operator [GBY_53] (rows=1045432122 width=88)
@@ -45,7 +115,7 @@ Stage-0
SHUFFLE [RS_52]
PartitionCols:_col0, _col1, _col2
Group By Operator [GBY_51] (rows=2090864244 width=88)
- Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col2)"],keys:_col0, _col1, '0'
+ Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col2)"],keys:_col0, _col1, 0
Select Operator [SEL_49] (rows=696954748 width=88)
Output:["_col0","_col1","_col2"]
Merge Join Operator [MERGEJOIN_92] (rows=696954748 width=88)
@@ -63,14 +133,14 @@ Stage-0
Filter Operator [FIL_81] (rows=575995635 width=88)
predicate:(ss_sold_date_sk is not null and ss_store_sk is not null)
TableScan [TS_0] (rows=575995635 width=88)
- default@store_sales,ss,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_net_profit"]
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_net_profit"]
<-Map 7 [SIMPLE_EDGE]
SHUFFLE [RS_44]
PartitionCols:_col0
Select Operator [SEL_5] (rows=8116 width=1119)
Output:["_col0"]
Filter Operator [FIL_82] (rows=8116 width=1119)
- predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null)
+ predicate:(d_month_seq BETWEEN 1212 AND 1223 and d_date_sk is not null)
TableScan [TS_3] (rows=73049 width=1119)
default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"]
<-Reducer 9 [SIMPLE_EDGE]
@@ -84,7 +154,7 @@ Stage-0
Select Operator [SEL_8] (rows=1704 width=1910)
Output:["_col0","_col1","_col2"]
Filter Operator [FIL_83] (rows=1704 width=1910)
- predicate:(s_store_sk is not null and s_state is not null)
+ predicate:(s_state is not null and s_store_sk is not null)
TableScan [TS_6] (rows=1704 width=1910)
default@store,s,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_county","s_state"]
<-Reducer 14 [SIMPLE_EDGE]
@@ -143,7 +213,7 @@ Stage-0
Select Operator [SEL_14] (rows=8116 width=1119)
Output:["_col0"]
Filter Operator [FIL_86] (rows=8116 width=1119)
- predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null)
+ predicate:(d_month_seq BETWEEN 1212 AND 1223 and d_date_sk is not null)
TableScan [TS_12] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"]
http://git-wip-us.apache.org/repos/asf/hive/blob/89362a14/ql/src/test/results/clientpositive/perf/query80.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query80.q.out b/ql/src/test/results/clientpositive/perf/query80.q.out
index 6595e89..10ec36a 100644
--- a/ql/src/test/results/clientpositive/perf/query80.q.out
+++ b/ql/src/test/results/clientpositive/perf/query80.q.out
@@ -47,7 +47,7 @@ Stage-0
Reduce Output Operator [RS_122]
PartitionCols:_col0, _col1, _col2
Group By Operator [GBY_121] (rows=2435062716 width=108)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
Select Operator [SEL_77] (rows=231905279 width=135)
Output:["_col0","_col1","_col2","_col3","_col4"]
Group By Operator [GBY_76] (rows=231905279 width=135)
@@ -139,7 +139,7 @@ Stage-0
Reduce Output Operator [RS_122]
PartitionCols:_col0, _col1, _col2
Group By Operator [GBY_121] (rows=2435062716 width=108)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
Select Operator [SEL_118] (rows=115958879 width=135)
Output:["_col0","_col1","_col2","_col3","_col4"]
Group By Operator [GBY_117] (rows=115958879 width=135)
@@ -231,7 +231,7 @@ Stage-0
Reduce Output Operator [RS_122]
PartitionCols:_col0, _col1, _col2
Group By Operator [GBY_121] (rows=2435062716 width=108)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, '0'
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
Select Operator [SEL_38] (rows=463823414 width=88)
Output:["_col0","_col1","_col2","_col3","_col4"]
Group By Operator [GBY_37] (rows=463823414 width=88)