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 2020/04/26 17:26:31 UTC
[hive] branch master updated: HIVE-23275: Represent UNBOUNDED in
window functions in CBO correctly (Jesus Camacho Rodriguez,
reviewed by Zoltan Haindrich)
This is an automated email from the ASF dual-hosted git repository.
jcamacho pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 62ed1aa HIVE-23275: Represent UNBOUNDED in window functions in CBO correctly (Jesus Camacho Rodriguez, reviewed by Zoltan Haindrich)
62ed1aa is described below
commit 62ed1aa091f951cc54a4a70e82c71225e370bcd4
Author: Jesus Camacho Rodriguez <jc...@apache.org>
AuthorDate: Wed Apr 22 21:40:12 2020 -0700
HIVE-23275: Represent UNBOUNDED in window functions in CBO correctly (Jesus Camacho Rodriguez, reviewed by Zoltan Haindrich)
Close apache/hive#993
---
.../hive/ql/optimizer/calcite/translator/ASTConverter.java | 12 ++++++------
.../ql/optimizer/calcite/translator/ExprNodeConverter.java | 12 ++++++------
.../org/apache/hadoop/hive/ql/parse/CalcitePlanner.java | 9 +++++----
ql/src/test/results/clientpositive/llap/join46.q.out | 2 +-
ql/src/test/results/clientpositive/llap/lineage2.q.out | 2 +-
ql/src/test/results/clientpositive/llap/lineage3.q.out | 6 +++---
.../llap/materialized_view_rewrite_window.q.out | 2 +-
.../test/results/clientpositive/llap/subquery_scalar.q.out | 2 +-
.../results/clientpositive/llap/topnkey_windowing.q.out | 4 ++--
.../test/results/clientpositive/perf/tez/cbo_query12.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query20.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query36.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query47.q.out | 6 +++---
.../test/results/clientpositive/perf/tez/cbo_query49.q.out | 6 +++---
.../test/results/clientpositive/perf/tez/cbo_query51.q.out | 6 +++---
.../test/results/clientpositive/perf/tez/cbo_query53.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query57.q.out | 6 +++---
.../test/results/clientpositive/perf/tez/cbo_query63.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query89.q.out | 2 +-
.../test/results/clientpositive/perf/tez/cbo_query98.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query12.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query20.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query36.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query47.q.out | 6 +++---
.../clientpositive/perf/tez/constraints/cbo_query49.q.out | 6 +++---
.../clientpositive/perf/tez/constraints/cbo_query51.q.out | 6 +++---
.../clientpositive/perf/tez/constraints/cbo_query53.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query57.q.out | 6 +++---
.../clientpositive/perf/tez/constraints/cbo_query63.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query89.q.out | 2 +-
.../clientpositive/perf/tez/constraints/cbo_query98.q.out | 2 +-
.../clientpositive/perf/tez/constraints/mv_query67.q.out | 14 +++++++-------
32 files changed, 71 insertions(+), 70 deletions(-)
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ASTConverter.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ASTConverter.java
index ee21a1e..14dbb5f 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ASTConverter.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ASTConverter.java
@@ -665,15 +665,15 @@ public class ASTConverter {
ASTNode wRangeAst = null;
ASTNode startAST = null;
- RexWindowBound ub = window.getUpperBound();
- if (ub != null) {
- startAST = getWindowBound(ub);
+ RexWindowBound lb = window.getLowerBound();
+ if (lb != null) {
+ startAST = getWindowBound(lb);
}
ASTNode endAST = null;
- RexWindowBound lb = window.getLowerBound();
- if (lb != null) {
- endAST = getWindowBound(lb);
+ RexWindowBound ub = window.getUpperBound();
+ if (ub != null) {
+ endAST = getWindowBound(ub);
}
if (startAST != null || endAST != null) {
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
index 658d2cc..5587e99 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
@@ -475,15 +475,15 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
private WindowFrameSpec getWindowRange(RexWindow window) {
// NOTE: in Hive AST Rows->Range(Physical) & Range -> Values (logical)
BoundarySpec start = null;
- RexWindowBound ub = window.getUpperBound();
- if (ub != null) {
- start = getWindowBound(ub);
+ RexWindowBound lb = window.getLowerBound();
+ if (lb != null) {
+ start = getWindowBound(lb);
}
BoundarySpec end = null;
- RexWindowBound lb = window.getLowerBound();
- if (lb != null) {
- end = getWindowBound(lb);
+ RexWindowBound ub = window.getUpperBound();
+ if (ub != null) {
+ end = getWindowBound(ub);
}
return new WindowFrameSpec(window.isRows() ? WindowType.ROWS : WindowType.RANGE, start, end);
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
index 7b2e201..f94a9f9 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
@@ -4270,8 +4270,9 @@ public class CalcitePlanner extends SemanticAnalyzer {
if (bs != null) {
SqlParserPos pos = new SqlParserPos(1, 1);
- SqlNode amt = bs.getAmt() == 0 ? null : SqlLiteral.createExactNumeric(
- String.valueOf(bs.getAmt()), new SqlParserPos(2, 2));
+ SqlNode amt = bs.getAmt() == 0 || bs.getAmt() == BoundarySpec.UNBOUNDED_AMOUNT
+ ? null
+ : SqlLiteral.createExactNumeric(String.valueOf(bs.getAmt()), new SqlParserPos(2, 2));
RexNode amtLiteral = null;
SqlCall sc = null;
@@ -4356,8 +4357,8 @@ public class CalcitePlanner extends SemanticAnalyzer {
WindowSpec wndSpec = ((WindowFunctionSpec) wExpSpec).getWindowSpec();
List<RexNode> partitionKeys = getPartitionKeys(wndSpec.getPartition(), inputRR);
List<RexFieldCollation> orderKeys = getOrderKeys(wndSpec.getOrder(), inputRR);
- RexWindowBound upperBound = getBound(wndSpec.getWindowFrame().getStart());
- RexWindowBound lowerBound = getBound(wndSpec.getWindowFrame().getEnd());
+ RexWindowBound lowerBound = getBound(wndSpec.getWindowFrame().getStart());
+ RexWindowBound upperBound = getBound(wndSpec.getWindowFrame().getEnd());
boolean isRows = wndSpec.getWindowFrame().getWindowType() == WindowType.ROWS;
w = cluster.getRexBuilder().makeOver(calciteAggFnRetType, calciteAggFn, calciteAggFnArgs,
diff --git a/ql/src/test/results/clientpositive/llap/join46.q.out b/ql/src/test/results/clientpositive/llap/join46.q.out
index 16b86b5..baaa2a6 100644
--- a/ql/src/test/results/clientpositive/llap/join46.q.out
+++ b/ql/src/test/results/clientpositive/llap/join46.q.out
@@ -2519,7 +2519,7 @@ POSTHOOK: Input: default@table1
CBO PLAN:
HiveProject(r=[$0])
HiveJoin(condition=[=($0, $1)], joinType=[left], algorithm=[none], cost=[not available])
- HiveProject(_o__col11=[RANK() OVER (PARTITION BY 0 ORDER BY $1 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(_o__col11=[RANK() OVER (PARTITION BY 0 ORDER BY $1 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(a=[$0], b=[$1])
HiveFilter(condition=[IS NOT NULL($0)])
diff --git a/ql/src/test/results/clientpositive/llap/lineage2.q.out b/ql/src/test/results/clientpositive/llap/lineage2.q.out
index 7f7df56..6576a84 100644
--- a/ql/src/test/results/clientpositive/llap/lineage2.q.out
+++ b/ql/src/test/results/clientpositive/llap/lineage2.q.out
@@ -634,7 +634,7 @@ having count(a.c2) > 0
PREHOOK: type: QUERY
PREHOOK: Input: default@dest_l2
#### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"4e60ca1e72d985639b2027021a199297","queryText":"select sum(a.c1) over (partition by a.c1 order by a.id)\nfrom dest_l2 a\nwhere a.c2 != 10\ngroup by a.c1, a.c2, a.id\nhaving count(a.c2) > 0","edges":[{"sources":[1,2,3],"targets":[0],"expression":"(tok_function sum (. (tok_table_or_col $hdt$_0) c1) (tok_windowspec (tok_partitioningspec (tok_distributeby (. (tok_table_or_col $hdt$_0) c1)) (tok_orderby (tok_tabsortcolnameasc (tok_nu [...]
+{"version":"1.0","engine":"tez","database":"default","hash":"4e60ca1e72d985639b2027021a199297","queryText":"select sum(a.c1) over (partition by a.c1 order by a.id)\nfrom dest_l2 a\nwhere a.c2 != 10\ngroup by a.c1, a.c2, a.id\nhaving count(a.c2) > 0","edges":[{"sources":[1,2,3],"targets":[0],"expression":"(tok_function sum (. (tok_table_or_col $hdt$_0) c1) (tok_windowspec (tok_partitioningspec (tok_distributeby (. (tok_table_or_col $hdt$_0) c1)) (tok_orderby (tok_tabsortcolnameasc (tok_nu [...]
1
PREHOOK: query: select sum(a.c1), count(b.c1), b.c2, b.c3
from dest_l2 a join dest_l3 b on (a.id = b.id)
diff --git a/ql/src/test/results/clientpositive/llap/lineage3.q.out b/ql/src/test/results/clientpositive/llap/lineage3.q.out
index a4f6e61..d762fbc 100644
--- a/ql/src/test/results/clientpositive/llap/lineage3.q.out
+++ b/ql/src/test/results/clientpositive/llap/lineage3.q.out
@@ -67,7 +67,7 @@ where cint > 10 and cint < 10000 limit 10
PREHOOK: type: QUERY
PREHOOK: Input: default@alltypesorc
#### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"af879e003bd60eb1f8ff064bd3f362ac","queryText":"select cint, rank() over(order by cint) from alltypesorc\nwhere cint > 10 and cint < 10000 limit 10","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3,4,2,5,6,7,8,9,10,11,12,13],"targets":[1],"expression":"(tok_function rank (tok_windowspec (tok_partitioningspec (tok_distributeby 0) (tok_orderby (tok_tabsortcolnameasc (tok_nulls_last (. (tok_table_or_col [...]
+{"version":"1.0","engine":"tez","database":"default","hash":"af879e003bd60eb1f8ff064bd3f362ac","queryText":"select cint, rank() over(order by cint) from alltypesorc\nwhere cint > 10 and cint < 10000 limit 10","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3,4,2,5,6,7,8,9,10,11,12,13],"targets":[1],"expression":"(tok_function rank (tok_windowspec (tok_partitioningspec (tok_distributeby 0) (tok_orderby (tok_tabsortcolnameasc (tok_nulls_last (. (tok_table_or_col [...]
762 1
762 1
762 1
@@ -97,7 +97,7 @@ order by cdouble, a, b limit 5
PREHOOK: type: QUERY
PREHOOK: Input: default@alltypesorc
#### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"4ab227ced0fc6679614c949ac8a56ccc","queryText":"with v2 as\n (select cdouble, count(cint) over() a,\n sum(cint + cbigint) over(partition by cboolean1) b\n from (select * from alltypesorc) v1)\nselect cdouble, a, b, a + b, cdouble + a from v2\nwhere cdouble is not null\norder by cdouble, a, b limit 5","edges":[{"sources":[5],"targets":[0],"edgeType":"PROJECTION"},{"sources":[6,7,8,9,10,5,11,12,13,14,15,16],"targets":[1],"e [...]
+{"version":"1.0","engine":"tez","database":"default","hash":"4ab227ced0fc6679614c949ac8a56ccc","queryText":"with v2 as\n (select cdouble, count(cint) over() a,\n sum(cint + cbigint) over(partition by cboolean1) b\n from (select * from alltypesorc) v1)\nselect cdouble, a, b, a + b, cdouble + a from v2\nwhere cdouble is not null\norder by cdouble, a, b limit 5","edges":[{"sources":[5],"targets":[0],"edgeType":"PROJECTION"},{"sources":[6,7,8,9,10,5,11,12,13,14,15,16],"targets":[1],"e [...]
-16379.0 9173 -919551973060 -919551963887 -7206.0
-16373.0 9173 -919551973060 -919551963887 -7200.0
-16372.0 9173 -919551973060 -919551963887 -7199.0
@@ -317,7 +317,7 @@ PREHOOK: type: QUERY
PREHOOK: Input: default@alltypesorc
PREHOOK: Input: default@dest_v3
#### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"fd4e0dd59f42b53fc07125817451df49","queryText":"select * from dest_v3 limit 2","edges":[{"sources":[3,4,5,6,7],"targets":[0],"expression":"(tok_function sum (. (tok_table_or_col $hdt$_0) ctinyint) (tok_windowspec (tok_partitioningspec (tok_distributeby (. (tok_table_or_col $hdt$_0) csmallint)) (tok_orderby (tok_tabsortcolnameasc (tok_nulls_last (. (tok_table_or_col $hdt$_0) csmallint))))) (tok_windowvalues (preceding 2147483647) [...]
+{"version":"1.0","engine":"tez","database":"default","hash":"fd4e0dd59f42b53fc07125817451df49","queryText":"select * from dest_v3 limit 2","edges":[{"sources":[3,4,5,6,7],"targets":[0],"expression":"(tok_function sum (. (tok_table_or_col $hdt$_0) ctinyint) (tok_windowspec (tok_partitioningspec (tok_distributeby (. (tok_table_or_col $hdt$_0) csmallint)) (tok_orderby (tok_tabsortcolnameasc (tok_nulls_last (. (tok_table_or_col $hdt$_0) csmallint))))) (tok_windowvalues (preceding unbounded) [...]
38 216 false
38 229 true
PREHOOK: query: drop table if exists src_dp
diff --git a/ql/src/test/results/clientpositive/llap/materialized_view_rewrite_window.q.out b/ql/src/test/results/clientpositive/llap/materialized_view_rewrite_window.q.out
index 4e35bba..26e3856 100644
--- a/ql/src/test/results/clientpositive/llap/materialized_view_rewrite_window.q.out
+++ b/ql/src/test/results/clientpositive/llap/materialized_view_rewrite_window.q.out
@@ -91,7 +91,7 @@ HiveAggregate(group=[{2}], agg#0=[max($1)])
HiveTableScan(table=[[arc_view, wealth]], table:alias=[t2])
HiveProject(total_views=[$1], quartile=[$2], program=[$0])
HiveFilter(condition=[IS NOT NULL($0)])
- HiveProject(program=[$0], total_views=[$1], _o__col6=[sum(CAST(1.5:DECIMAL(2, 1)):DECIMAL(9, 4)) OVER (PARTITION BY 0 ORDER BY $1 NULLS LAST RANGE BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject(program=[$0], total_views=[$1], _o__col6=[sum(CAST(1.5:DECIMAL(2, 1)):DECIMAL(9, 4)) OVER (PARTITION BY 0 ORDER BY $1 NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveTableScan(table=[[arc_view, tv_view_data]], table:alias=[tv_view_data])
PREHOOK: query: create materialized view mv_tv_view_data_av1 stored as orc TBLPROPERTIES ('transactional'='true') as
diff --git a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out b/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
index ad5c9f0..8fab167 100644
--- a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
+++ b/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
@@ -7551,7 +7551,7 @@ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$1], ss_list_price=[$2])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
HiveProject(avg_window_0=[$0])
HiveFilter(condition=[IS NOT NULL($0)])
- HiveProject(avg_window_0=[avg(*(CAST($1):DECIMAL(10, 0), $2)) OVER (PARTITION BY $2 ORDER BY $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(avg_window_0=[avg(*(CAST($1):DECIMAL(10, 0), $2)) OVER (PARTITION BY $2 ORDER BY $2 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$1], ss_list_price=[$2])
HiveFilter(condition=[IS NOT NULL($0)])
diff --git a/ql/src/test/results/clientpositive/llap/topnkey_windowing.q.out b/ql/src/test/results/clientpositive/llap/topnkey_windowing.q.out
index 64772ea..fe4d31d 100644
--- a/ql/src/test/results/clientpositive/llap/topnkey_windowing.q.out
+++ b/ql/src/test/results/clientpositive/llap/topnkey_windowing.q.out
@@ -377,7 +377,7 @@ POSTHOOK: type: QUERY
POSTHOOK: Input: default@topnkey_windowing
#### A masked pattern was here ####
OPTIMIZED SQL: SELECT *
-FROM (SELECT `tw_code`, RANK() OVER (PARTITION BY 0 ORDER BY `tw_value` ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING) AS `rank_window_0`
+FROM (SELECT `tw_code`, RANK() OVER (PARTITION BY 0 ORDER BY `tw_value` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `rank_window_0`
FROM `default`.`topnkey_windowing`) AS `t`
WHERE `rank_window_0` <= 3
STAGE DEPENDENCIES:
@@ -578,7 +578,7 @@ POSTHOOK: type: QUERY
POSTHOOK: Input: default@topnkey_windowing
#### A masked pattern was here ####
OPTIMIZED SQL: SELECT *
-FROM (SELECT `tw_code`, RANK() OVER (PARTITION BY 0 ORDER BY `tw_value` ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING) AS `rank_window_0`
+FROM (SELECT `tw_code`, RANK() OVER (PARTITION BY 0 ORDER BY `tw_value` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `rank_window_0`
FROM `default`.`topnkey_windowing`) AS `t`
WHERE `rank_window_0` <= 3
STAGE DEPENDENCIES:
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query12.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query12.q.out
index 21bd652..78401f5 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query12.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query12.q.out
@@ -73,7 +73,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query20.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query20.q.out
index 2700e6a..b141b50 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query20.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query20.q.out
@@ -65,7 +65,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query36.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query36.q.out
index 3f152fd..f5b6d1c 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query36.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query36.q.out
@@ -69,7 +69,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(gross_margin=[$0], i_category=[$1], i_class=[$2], lochierarchy=[$3], rank_within_parent=[$4])
HiveSortLimit(sort0=[$3], sort1=[$5], sort2=[$4], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], fetch=[100])
- HiveProject(gross_margin=[/($2, $3)], i_category=[$0], i_class=[$1], lochierarchy=[+(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT))], rank_within_parent=[rank() OVER (PARTITION BY +(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT)), CASE(=(grouping($4, 0:BIGINT), CAST(0):BIGINT), $0, null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE") ORDER BY /($2, $3) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], (tok_function when (= (tok_table_or_col lochierarchy) 0) (tok [...]
+ HiveProject(gross_margin=[/($2, $3)], i_category=[$0], i_class=[$1], lochierarchy=[+(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT))], rank_within_parent=[rank() OVER (PARTITION BY +(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT)), CASE(=(grouping($4, 0:BIGINT), CAST(0):BIGINT), $0, null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE") ORDER BY /($2, $3) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], (tok_function when (= (tok_table_or_col lochierarchy) 0) (tok_t [...]
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], GROUPING__ID=[$4])
HiveAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[sum($2)], agg#1=[sum($3)], GROUPING__ID=[GROUPING__ID()])
HiveProject($f0=[$9], $f1=[$8], $f2=[$4], $f3=[$3])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query47.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query47.q.out
index c706a7d..0e6795d 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query47.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query47.q.out
@@ -115,7 +115,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveJoin(condition=[AND(=($12, $0), =($13, $1), =($14, $2), =($15, $3), =($20, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], -=[-($5, 1)])
HiveFilter(condition=[IS NOT NULL($5)])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6])
HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -136,7 +136,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveJoin(condition=[AND(=($6, $0), =($7, $1), =($8, $2), =($9, $3), =($14, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], +=[+($5, 1)])
HiveFilter(condition=[IS NOT NULL($5)])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6])
HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -156,7 +156,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveTableScan(table=[[default, store]], table:alias=[store])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$4], (tok_table_or_col d_moy)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[$7], rank_window_1=[$8])
HiveFilter(condition=[AND(=($4, 2000), >($7, 0), CASE(>($7, 0), >(/(ABS(-($6, $7)), $7), 0.1:DECIMAL(1, 1)), false), IS NOT NULL($8))])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $1, $0, $4, $5, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 [...]
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $1, $0, $4, $5, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FO [...]
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6])
HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query49.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query49.q.out
index 7de6c1f..3a02606 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query49.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query49.q.out
@@ -278,7 +278,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveUnion(all=[true])
HiveProject(channel=[_UTF-16LE'web':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(ws_item_sk=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{5}], agg#0=[sum($2)], agg#1=[sum($7)], agg#2=[sum($3)], agg#3=[sum($8)])
HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[inner], algorithm=[none], cost=[not available])
@@ -294,7 +294,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'catalog':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(cs_item_sk=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{5}], agg#0=[sum($2)], agg#1=[sum($7)], agg#2=[sum($3)], agg#3=[sum($8)])
HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[inner], algorithm=[none], cost=[not available])
@@ -310,7 +310,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(ss_item_sk=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{5}], agg#0=[sum($2)], agg#1=[sum($7)], agg#2=[sum($3)], agg#3=[sum($8)])
HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query51.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query51.q.out
index e3184ec..8eb4770 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query51.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query51.q.out
@@ -98,9 +98,9 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(item_sk=[$0], d_date=[$1], web_sales=[$2], store_sales=[$3], max_window_0=[$4], max_window_1=[$5])
HiveFilter(condition=[>($4, $5)])
- HiveProject(item_sk=[CASE(IS NOT NULL($3), $3, $0)], d_date=[CASE(IS NOT NULL($4), $4, $1)], web_sales=[$5], store_sales=[$2], max_window_0=[max($5) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)], max_window_1=[max($2) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject(item_sk=[CASE(IS NOT NULL($3), $3, $0)], d_date=[CASE(IS NOT NULL($4), $4, $1)], web_sales=[$5], store_sales=[$2], max_window_0=[max($5) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], max_window_1=[max($2) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveJoin(condition=[AND(=($3, $0), =($4, $1))], joinType=[full], algorithm=[none], cost=[not available])
- HiveProject((tok_table_or_col ss_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col ss_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveProject(ss_item_sk=[$0], d_date=[$1], $f2=[$2])
HiveAggregate(group=[{1, 4}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -110,7 +110,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(d_date_sk=[$0], d_date=[$2])
HiveFilter(condition=[AND(BETWEEN(false, $3, 1212, 1223), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject((tok_table_or_col ws_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col ws_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveProject(ws_item_sk=[$0], d_date=[$1], $f2=[$2])
HiveAggregate(group=[{1, 4}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query53.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query53.q.out
index 6e2aecf..58554dd 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query53.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query53.q.out
@@ -66,7 +66,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$2], sort1=[$1], sort2=[$0], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$1], avg_window_0=[$2])
HiveFilter(condition=[CASE(>($2, 0), >(/(ABS(-($1, $2)), $2), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_manufact_id=[$0], d_qoy=[$1], $f2=[$2])
HiveAggregate(group=[{6, 8}], agg#0=[sum($4)])
HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out
index 1504116..442a191 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out
@@ -109,7 +109,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveJoin(condition=[AND(=($10, $0), =($11, $1), =($12, $2), =($17, $4))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], -=[-($4, 1)])
HiveFilter(condition=[IS NOT NULL($4)])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -130,7 +130,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveJoin(condition=[AND(=($5, $0), =($6, $1), =($7, $2), =($12, $4))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], +=[+($4, 1)])
HiveFilter(condition=[IS NOT NULL($4)])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -150,7 +150,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7])
HiveFilter(condition=[AND(=($3, 2000), >($6, 0), CASE(>($6, 0), >(/(ABS(-($5, $6)), $6), 0.1:DECIMAL(1, 1)), false), IS NOT NULL($7))])
- HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $1, $0, $4, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 [...]
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $1, $0, $4, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 OR [...]
HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out
index ca3fe80..47a57d2 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out
@@ -68,7 +68,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$2], sort2=[$1], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$1], avg_window_0=[$2])
HiveFilter(condition=[CASE(>($2, 0), >(/(ABS(-($1, $2)), $2), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_manager_id=[$0], d_moy=[$1], $f2=[$2])
HiveAggregate(group=[{6, 8}], agg#0=[sum($4)])
HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query89.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query89.q.out
index 667c16e..101a757 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query89.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query89.q.out
@@ -67,7 +67,7 @@ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], s_store_name=[$3], s_co
HiveSortLimit(sort0=[$8], sort1=[$3], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], s_store_name=[$3], s_company_name=[$4], d_moy=[$5], sum_sales=[$6], avg_monthly_sales=[$7], (- (tok_table_or_col sum_sales) (tok_table_or_col avg_monthly_sales))=[-($6, $7)])
HiveFilter(condition=[CASE(<>($7, 0), >(/(ABS(-($6, $7)), $7), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_category)=[$2], (tok_table_or_col i_class)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $2, $0, $4, $5 ORDER BY $2 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$2], (tok_table_or_col i_class)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $2, $0, $4, $5 ORDER BY $2 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_class=[$1], i_category=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6])
HiveAggregate(group=[{5, 6, 7, 9, 11, 12}], agg#0=[sum($3)])
HiveJoin(condition=[=($2, $10)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query98.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query98.q.out
index e2a14a6..5a5596d 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query98.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query98.q.out
@@ -71,7 +71,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query12.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query12.q.out
index 6737448..dec6a4d 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query12.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query12.q.out
@@ -73,7 +73,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query20.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query20.q.out
index f82af06..136eab6 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query20.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query20.q.out
@@ -65,7 +65,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query36.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query36.q.out
index 358ace4..1ec1faf 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query36.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query36.q.out
@@ -69,7 +69,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(gross_margin=[$0], i_category=[$1], i_class=[$2], lochierarchy=[$3], rank_within_parent=[$4])
HiveSortLimit(sort0=[$3], sort1=[$5], sort2=[$4], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], fetch=[100])
- HiveProject(gross_margin=[/($2, $3)], i_category=[$0], i_class=[$1], lochierarchy=[+(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT))], rank_within_parent=[rank() OVER (PARTITION BY +(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT)), CASE(=(grouping($4, 0:BIGINT), CAST(0):BIGINT), $0, null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE") ORDER BY /($2, $3) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], (tok_function when (= (tok_table_or_col lochierarchy) 0) (tok [...]
+ HiveProject(gross_margin=[/($2, $3)], i_category=[$0], i_class=[$1], lochierarchy=[+(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT))], rank_within_parent=[rank() OVER (PARTITION BY +(grouping($4, 1:BIGINT), grouping($4, 0:BIGINT)), CASE(=(grouping($4, 0:BIGINT), CAST(0):BIGINT), $0, null:VARCHAR(2147483647) CHARACTER SET "UTF-16LE") ORDER BY /($2, $3) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], (tok_function when (= (tok_table_or_col lochierarchy) 0) (tok_t [...]
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], GROUPING__ID=[$4])
HiveAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[sum($2)], agg#1=[sum($3)], GROUPING__ID=[GROUPING__ID()])
HiveProject($f0=[$9], $f1=[$8], $f2=[$4], $f3=[$3])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out
index 94bfb65..8863cae 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out
@@ -115,7 +115,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveJoin(condition=[AND(=($12, $0), =($13, $1), =($14, $2), =($15, $3), =($20, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], -=[-($5, 1)])
HiveFilter(condition=[IS NOT NULL($5)])
- HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6])
HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)])
HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -136,7 +136,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveJoin(condition=[AND(=($6, $0), =($7, $1), =($8, $2), =($9, $3), =($14, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], +=[+($5, 1)])
HiveFilter(condition=[IS NOT NULL($5)])
- HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6])
HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)])
HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -156,7 +156,7 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su
HiveTableScan(table=[[default, item]], table:alias=[item])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$4], (tok_table_or_col d_moy)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[$7], rank_window_1=[$8])
HiveFilter(condition=[AND(=($4, 2000), >($7, 0), CASE(>($7, 0), >(/(ABS(-($6, $7)), $7), 0.1:DECIMAL(1, 1)), false), IS NOT NULL($8))])
- HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $5, $4, $2, $3, $0 ORDER BY $5 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST, $3 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 [...]
+ HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $5, $4, $2, $3, $0 ORDER BY $5 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST, $3 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FO [...]
HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6])
HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)])
HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query49.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query49.q.out
index 7e5e8fa..d0d759b 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query49.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query49.q.out
@@ -278,7 +278,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveUnion(all=[true])
HiveProject(channel=[_UTF-16LE'web':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)])
HiveProject($f0=[$5], $f1=[CASE(IS NOT NULL($2), $2, 0)], $f2=[CASE(IS NOT NULL($7), $7, 0)], $f3=[CASE(IS NOT NULL($3), $3, 0:DECIMAL(12, 2))], $f4=[CASE(IS NOT NULL($8), $8, 0:DECIMAL(12, 2))])
@@ -295,7 +295,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'catalog':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)])
HiveProject($f0=[$5], $f1=[CASE(IS NOT NULL($2), $2, 0)], $f2=[CASE(IS NOT NULL($7), $7, 0)], $f3=[CASE(IS NOT NULL($3), $3, 0:DECIMAL(12, 2))], $f4=[CASE(IS NOT NULL($8), $8, 0:DECIMAL(12, 2))])
@@ -312,7 +312,7 @@ HiveSortLimit(sort0=[$0], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC], dir2=[
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], item=[$0], return_ratio=[$1], return_rank=[$2], currency_rank=[$3])
HiveFilter(condition=[OR(<=($2, 10), <=($3, 10))])
- HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(item=[$0], return_ratio=[/(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4))], rank_window_0=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($1):DECIMAL(15, 4), CAST($2):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY 0 ORDER BY /(CAST($3):DECIMAL(15, 4), CAST($4):DECIMAL(15, 4)) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)])
HiveProject($f0=[$5], $f1=[CASE(IS NOT NULL($2), $2, 0)], $f2=[CASE(IS NOT NULL($7), $7, 0)], $f3=[CASE(IS NOT NULL($3), $3, 0:DECIMAL(12, 2))], $f4=[CASE(IS NOT NULL($8), $8, 0:DECIMAL(12, 2))])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query51.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query51.q.out
index fbb5b45..eca3e3a 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query51.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query51.q.out
@@ -98,9 +98,9 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(item_sk=[$0], d_date=[$1], web_sales=[$2], store_sales=[$3], max_window_0=[$4], max_window_1=[$5])
HiveFilter(condition=[>($4, $5)])
- HiveProject(item_sk=[CASE(IS NOT NULL($3), $3, $0)], d_date=[CASE(IS NOT NULL($4), $4, $1)], web_sales=[$5], store_sales=[$2], max_window_0=[max($5) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)], max_window_1=[max($2) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject(item_sk=[CASE(IS NOT NULL($3), $3, $0)], d_date=[CASE(IS NOT NULL($4), $4, $1)], web_sales=[$5], store_sales=[$2], max_window_0=[max($5) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], max_window_1=[max($2) OVER (PARTITION BY CASE(IS NOT NULL($3), $3, $0) ORDER BY CASE(IS NOT NULL($4), $4, $1) NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveJoin(condition=[AND(=($3, $0), =($4, $1))], joinType=[full], algorithm=[none], cost=[not available])
- HiveProject((tok_table_or_col ss_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col ss_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveProject(ss_item_sk=[$0], d_date=[$1], $f2=[$2])
HiveAggregate(group=[{1, 4}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -110,7 +110,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(d_date_sk=[$0], d_date=[$2])
HiveFilter(condition=[BETWEEN(false, $3, 1212, 1223)])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject((tok_table_or_col ws_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN CURRENT ROW AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col ws_item_sk)=[$0], (tok_table_or_col d_date)=[$1], sum_window_0=[sum($2) OVER (PARTITION BY $0 ORDER BY $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
HiveProject(ws_item_sk=[$0], d_date=[$1], $f2=[$2])
HiveAggregate(group=[{1, 4}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query53.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query53.q.out
index f8993fd..9e059fe 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query53.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query53.q.out
@@ -66,7 +66,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$2], sort1=[$1], sort2=[$0], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$1], avg_window_0=[$2])
HiveFilter(condition=[CASE(>($2, 0), >(/(ABS(-($1, $2)), $2), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_manufact_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_manufact_id=[$0], d_qoy=[$1], $f2=[$2])
HiveAggregate(group=[{4, 6}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out
index ede1ffa..1d7363a 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out
@@ -109,7 +109,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveJoin(condition=[AND(=($10, $0), =($11, $1), =($12, $2), =($17, $4))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], -=[-($4, 1)])
HiveFilter(condition=[IS NOT NULL($4)])
- HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5])
HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)])
HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -130,7 +130,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveJoin(condition=[AND(=($5, $0), =($6, $1), =($7, $2), =($12, $4))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], +=[+($4, 1)])
HiveFilter(condition=[IS NOT NULL($4)])
- HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5])
HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)])
HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -150,7 +150,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_
HiveTableScan(table=[[default, item]], table:alias=[item])
HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7])
HiveFilter(condition=[AND(=($3, 2000), >($6, 0), CASE(>($6, 0), >(/(ABS(-($5, $6)), $6), 0.1:DECIMAL(1, 1)), false), IS NOT NULL($7))])
- HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $4, $3, $2, $0 ORDER BY $4 NULLS FIRST, $3 NULLS FIRST, $2 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 [...]
+ HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $4, $3, $2, $0 ORDER BY $4 NULLS FIRST, $3 NULLS FIRST, $2 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 OR [...]
HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5])
HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)])
HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query63.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query63.q.out
index 4dddf0b..50f94b9 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query63.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query63.q.out
@@ -68,7 +68,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$2], sort2=[$1], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$1], avg_window_0=[$2])
HiveFilter(condition=[CASE(>($2, 0), >(/(ABS(-($1, $2)), $2), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_manager_id=[$0], d_moy=[$1], $f2=[$2])
HiveAggregate(group=[{4, 6}], agg#0=[sum($2)])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query89.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query89.q.out
index 64726a2..2f59e0c 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query89.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query89.q.out
@@ -67,7 +67,7 @@ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], s_store_name=[$3], s_co
HiveSortLimit(sort0=[$8], sort1=[$3], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], s_store_name=[$3], s_company_name=[$4], d_moy=[$5], sum_sales=[$6], avg_monthly_sales=[$7], (- (tok_table_or_col sum_sales) (tok_table_or_col avg_monthly_sales))=[-($6, $7)])
HiveFilter(condition=[CASE(<>($7, 0), >(/(ABS(-($6, $7)), $7), 0.1:DECIMAL(1, 1)), false)])
- HiveProject((tok_table_or_col i_category)=[$2], (tok_table_or_col i_class)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $2, $0, $4, $5 ORDER BY $2 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject((tok_table_or_col i_category)=[$2], (tok_table_or_col i_class)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $2, $0, $4, $5 ORDER BY $2 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_brand=[$0], i_class=[$1], i_category=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6])
HiveAggregate(group=[{5, 6, 7, 9, 11, 12}], agg#0=[sum($3)])
HiveJoin(condition=[=($2, $10)], joinType=[inner], algorithm=[none], cost=[not available])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query98.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query98.q.out
index 42db0d6..7df2be8 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query98.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query98.q.out
@@ -71,7 +71,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5])
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC])
- HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING))], (tok_table_or_col i_item_id)=[$0])
+ HiveProject(i_item_desc=[$1], i_category=[$4], i_class=[$3], i_current_price=[$2], itemrevenue=[$5], revenueratio=[/(*($5, 100:DECIMAL(10, 0)), sum($5) OVER (PARTITION BY $3 ORDER BY $3 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))], (tok_table_or_col i_item_id)=[$0])
HiveAggregate(group=[{1, 2, 3, 4, 5}], agg#0=[sum($8)])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_class=[$10], i_category=[$12])
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/mv_query67.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/mv_query67.q.out
index fe66be5..b92bdf2 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/mv_query67.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/mv_query67.q.out
@@ -130,7 +130,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], $f9=[$9])
HiveFilter(condition=[<=($9, 100)])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveTableScan(table=[[default, my_materialized_view_n100]], table:alias=[default.my_materialized_view_n100])
PREHOOK: query: explain cbo
@@ -257,7 +257,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[$9])
HiveFilter(condition=[<=($9, 100)])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], $f8=[$8])
HiveAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7}], groups=[[{0, 1, 2, 3, 4, 5, 6, 7}, {0, 1, 2, 3, 4, 5, 6}, {0, 1, 2, 3, 4, 5}, {0, 1, 2, 3, 4}, {0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($8)])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8])
@@ -365,7 +365,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[$9])
HiveFilter(condition=[<=($9, 100)])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], $f8=[$8])
HiveAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7}], groups=[[{0, 1, 2, 3, 4, 5, 6, 7}, {0, 1, 2, 3, 4, 5, 6}, {0, 1, 2, 3, 4, 5}, {0, 1, 2, 3, 4}, {0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($8)])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8])
@@ -471,7 +471,7 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[$7], sumsales=[$8], rank_window_0=[$9])
HiveFilter(condition=[<=($9, 100)])
- HiveProject(i_category=[$6], i_class=[$5], i_brand=[$4], i_product_name=[$7], d_year=[$0], d_qoy=[$2], d_moy=[$1], s_store_id=[$3], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $6 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$6], i_class=[$5], i_brand=[$4], i_product_name=[$7], d_year=[$0], d_qoy=[$2], d_moy=[$1], s_store_id=[$3], sumsales=[$8], rank_window_0=[rank() OVER (PARTITION BY $6 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveProject(d_year=[$0], d_moy=[$1], d_qoy=[$2], s_store_id=[$3], i_brand=[$4], i_class=[$5], i_category=[$6], i_product_name=[$7], $f8=[$8])
HiveAggregate(group=[{5, 6, 7, 9, 11, 12, 13, 14}], groups=[[{5, 6, 7, 9, 11, 12, 13, 14}, {5, 6, 7, 11, 12, 13, 14}, {5, 7, 11, 12, 13, 14}, {5, 11, 12, 13, 14}, {11, 12, 13, 14}, {11, 12, 13}, {12, 13}, {13}, {}]], agg#0=[sum(DISTINCT $3)])
HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -624,7 +624,7 @@ POSTHOOK: Input: default@store_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveTableScan(table=[[default, my_materialized_view_n101]], table:alias=[default.my_materialized_view_n101])
PREHOOK: query: DROP MATERIALIZED VIEW `my_materialized_view_n101`
@@ -805,7 +805,7 @@ POSTHOOK: Input: default@store_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveTableScan(table=[[default, my_materialized_view_n102]], table:alias=[default.my_materialized_view_n102])
PREHOOK: query: DROP MATERIALIZED VIEW `my_materialized_view_n102`
@@ -988,6 +988,6 @@ POSTHOOK: Input: default@store_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], fetch=[100])
- HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_category=[$0], i_class=[$1], i_brand=[$2], i_product_name=[$3], d_year=[$4], d_qoy=[$5], d_moy=[$6], s_store_id=[CAST($7):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL], sumsales=[$8], $f9=[rank() OVER (PARTITION BY $0 ORDER BY $8 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
HiveTableScan(table=[[default, my_materialized_view_n103]], table:alias=[default.my_materialized_view_n103])