You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by vg...@apache.org on 2018/12/14 17:44:52 UTC
[4/4] hive git commit: HIVE-21021: Scalar subquery with only
aggregate in subquery (no group by) has unnecessary sq_count_check branch
(Vineet Garg, reviewed by Ashutosh Chauhan)
HIVE-21021: Scalar subquery with only aggregate in subquery (no group by) has unnecessary sq_count_check branch (Vineet Garg, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/c7b5454a
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/c7b5454a
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/c7b5454a
Branch: refs/heads/master
Commit: c7b5454aa57edec171f7f254b6e342bb4b9cbb82
Parents: 64930f8
Author: Vineet Garg <vg...@apache.org>
Authored: Fri Dec 14 09:43:54 2018 -0800
Committer: Vineet Garg <vg...@apache.org>
Committed: Fri Dec 14 09:43:54 2018 -0800
----------------------------------------------------------------------
.../calcite/rules/HiveRemoveSqCountCheck.java | 45 +-
.../queries/clientpositive/subquery_scalar.q | 35 +
.../clientpositive/llap/subquery_scalar.q.out | 138 ++
.../clientpositive/perf/tez/cbo_query14.q.out | 285 +--
.../perf/tez/constraints/cbo_query14.q.out | 285 +--
.../perf/tez/constraints/query14.q.out | 1589 +++++++----------
.../clientpositive/perf/tez/query14.q.out | 1623 ++++++++----------
.../clientpositive/spark/subquery_scalar.q.out | 138 ++
8 files changed, 1845 insertions(+), 2293 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/c7b5454a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveRemoveSqCountCheck.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveRemoveSqCountCheck.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveRemoveSqCountCheck.java
index 0100395..f0f7094 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveRemoveSqCountCheck.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveRemoveSqCountCheck.java
@@ -40,9 +40,11 @@ import java.util.NavigableMap;
import java.util.TreeMap;
/**
- * Planner rule that removes UDF sq_count_check from a
- * plan if group by keys in a subquery are constant
- * and there is no windowing or grouping sets
+ * Planner rule that removes UDF sq_count_check from a plan if
+ * 1) either group by keys in a subquery are constant and there is no windowing or grouping sets
+ * 2) OR there are no group by keys but only aggregate
+ * Both of the above case will produce at most one row, therefore it is safe to remove sq_count_check
+ * which was introduced earlier in the plan to ensure that this condition is met at run time
*/
public class HiveRemoveSqCountCheck extends RelOptRule {
@@ -97,25 +99,17 @@ public class HiveRemoveSqCountCheck extends RelOptRule {
return false;
}
+ private boolean isAggregateWithoutGbyKeys(final Aggregate agg) {
+ return agg.getGroupCount() == 0 ? true : false;
+ }
- @Override public void onMatch(RelOptRuleCall call) {
- final Join topJoin= call.rel(0);
- final Join join = call.rel(2);
- final Aggregate aggregate = call.rel(6);
-
- // in presence of grouping sets we can't remove sq_count_check
- if(aggregate.indicator) {
- return;
- }
-
- final int groupCount = aggregate.getGroupCount();
-
+ private boolean isAggWithConstantGbyKeys(final Aggregate aggregate, RelOptRuleCall call) {
final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder();
final RelMetadataQuery mq = call.getMetadataQuery();
final RelOptPredicateList predicates =
mq.getPulledUpPredicates(aggregate.getInput());
if (predicates == null) {
- return;
+ return false;
}
final NavigableMap<Integer, RexNode> map = new TreeMap<>();
for (int key : aggregate.getGroupSet()) {
@@ -128,15 +122,30 @@ public class HiveRemoveSqCountCheck extends RelOptRule {
// None of the group expressions are constant. Nothing to do.
if (map.isEmpty()) {
- return;
+ return false;
}
+ final int groupCount = aggregate.getGroupCount();
if (groupCount == map.size()) {
+ return true;
+ }
+ return false;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Join topJoin= call.rel(0);
+ final Join join = call.rel(2);
+ final Aggregate aggregate = call.rel(6);
+
+ // in presence of grouping sets we can't remove sq_count_check
+ if(aggregate.indicator) {
+ return;
+ }
+ if(isAggregateWithoutGbyKeys(aggregate) || isAggWithConstantGbyKeys(aggregate, call)) {
// join(left, join.getRight)
RelNode newJoin = HiveJoin.getJoin(topJoin.getCluster(), join.getLeft(), topJoin.getRight(),
topJoin.getCondition(), topJoin.getJoinType());
call.transformTo(newJoin);
}
}
-
}
http://git-wip-us.apache.org/repos/asf/hive/blob/c7b5454a/ql/src/test/queries/clientpositive/subquery_scalar.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/subquery_scalar.q b/ql/src/test/queries/clientpositive/subquery_scalar.q
index a7322f5..64973a1 100644
--- a/ql/src/test/queries/clientpositive/subquery_scalar.q
+++ b/ql/src/test/queries/clientpositive/subquery_scalar.q
@@ -258,3 +258,38 @@ explain select key, count(*) from src group by key having count(*) >
explain select key, count(*) from src group by key having count(*) >
(select count(*) from src s1 where s1.key = '90' group by s1.key );
+
+CREATE TABLE `store_sales`(
+ `ss_sold_date_sk` int,
+ `ss_quantity` int,
+ `ss_list_price` decimal(7,2));
+
+CREATE TABLE `date_dim`(
+ `d_date_sk` int,
+ `d_year` int);
+
+explain cbo with avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales);
+
+-- this one should have sq_count_check branch because it contains windowing function
+explain cbo with avg_sales as
+ (select avg(quantity*list_price) over( partition by list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales);
+
+
+DROP TABLE store_sales;
+DROP TABLE date_dim;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/c7b5454a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
----------------------------------------------------------------------
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 c43ad91..e830835 100644
--- a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
+++ b/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
@@ -7155,3 +7155,141 @@ STAGE PLANS:
Processor Tree:
ListSink
+PREHOOK: query: CREATE TABLE `store_sales`(
+ `ss_sold_date_sk` int,
+ `ss_quantity` int,
+ `ss_list_price` decimal(7,2))
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@store_sales
+POSTHOOK: query: CREATE TABLE `store_sales`(
+ `ss_sold_date_sk` int,
+ `ss_quantity` int,
+ `ss_list_price` decimal(7,2))
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@store_sales
+PREHOOK: query: CREATE TABLE `date_dim`(
+ `d_date_sk` int,
+ `d_year` int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@date_dim
+POSTHOOK: query: CREATE TABLE `date_dim`(
+ `d_date_sk` int,
+ `d_year` int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@date_dim
+Warning: Shuffle Join MERGEJOIN[37][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product
+PREHOOK: query: explain cbo with avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store_sales
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo with avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store_sales
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$1], ss_list_price=[$2])
+ HiveJoin(condition=[>($2, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$1], ss_list_price=[$2])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($1):DECIMAL(10, 0), $2)])
+ 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)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+
+Warning: Shuffle Join MERGEJOIN[68][tables = [$hdt$_0, $hdt$_1, $hdt$_2]] in Stage 'Reducer 2' is a cross product
+PREHOOK: query: explain cbo with avg_sales as
+ (select avg(quantity*list_price) over( partition by list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store_sales
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo with avg_sales as
+ (select avg(quantity*list_price) over( partition by list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001 ) x)
+select * from store_sales where ss_list_price > (select average_sales from avg_sales)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store_sales
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$1], ss_list_price=[$2])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($2, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ 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=[avg(*(CAST($1):DECIMAL(10, 0), $2)) OVER (PARTITION BY $2 ORDER BY $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ 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)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveJoin(condition=[=($0, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+
+PREHOOK: query: DROP TABLE store_sales
+PREHOOK: type: DROPTABLE
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: default@store_sales
+POSTHOOK: query: DROP TABLE store_sales
+POSTHOOK: type: DROPTABLE
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: default@store_sales
+PREHOOK: query: DROP TABLE date_dim
+PREHOOK: type: DROPTABLE
+PREHOOK: Input: default@date_dim
+PREHOOK: Output: default@date_dim
+POSTHOOK: query: DROP TABLE date_dim
+POSTHOOK: type: DROPTABLE
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Output: default@date_dim
http://git-wip-us.apache.org/repos/asf/hive/blob/c7b5454a/ql/src/test/results/clientpositive/perf/tez/cbo_query14.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query14.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query14.q.out
index e5a0be1..c09da86 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query14.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query14.q.out
@@ -1,9 +1,6 @@
-Warning: Shuffle Join MERGEJOIN[1440][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 5' is a cross product
-Warning: Shuffle Join MERGEJOIN[1452][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
-Warning: Shuffle Join MERGEJOIN[1442][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 13' is a cross product
-Warning: Shuffle Join MERGEJOIN[1465][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 14' is a cross product
-Warning: Shuffle Join MERGEJOIN[1444][tables = [$hdt$_2, $hdt$_3]] in Stage 'Reducer 18' is a cross product
-Warning: Shuffle Join MERGEJOIN[1478][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 19' is a cross product
+Warning: Shuffle Join MERGEJOIN[1164][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 6' is a cross product
+Warning: Shuffle Join MERGEJOIN[1171][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 16' is a cross product
+Warning: Shuffle Join MERGEJOIN[1178][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 22' is a cross product
PREHOOK: query: explain cbo
with cross_items as
(select i_item_sk ss_item_sk
@@ -229,7 +226,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5])
HiveUnion(all=[true])
HiveProject(channel=[_UTF-16LE'store'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject(i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f3=[*(CAST($6):DECIMAL(10, 0), $7)])
@@ -294,70 +291,36 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
HiveTableScan(table=[[default, item]], table:alias=[iws])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'catalog'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject(i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f3=[*(CAST($6):DECIMAL(10, 0), $7)])
@@ -422,70 +385,36 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
HiveTableScan(table=[[default, item]], table:alias=[iws])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'web'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject(i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f3=[*(CAST($6):DECIMAL(10, 0), $7)])
@@ -550,66 +479,32 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
HiveTableScan(table=[[default, item]], table:alias=[iws])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $6, 1998, 2000), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
http://git-wip-us.apache.org/repos/asf/hive/blob/c7b5454a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
index 9abcb05..118d23b 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
@@ -1,9 +1,6 @@
-Warning: Shuffle Join MERGEJOIN[1458][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 5' is a cross product
-Warning: Shuffle Join MERGEJOIN[1470][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
-Warning: Shuffle Join MERGEJOIN[1460][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 13' is a cross product
-Warning: Shuffle Join MERGEJOIN[1483][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 14' is a cross product
-Warning: Shuffle Join MERGEJOIN[1462][tables = [$hdt$_2, $hdt$_3]] in Stage 'Reducer 18' is a cross product
-Warning: Shuffle Join MERGEJOIN[1496][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 19' is a cross product
+Warning: Shuffle Join MERGEJOIN[1182][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 6' is a cross product
+Warning: Shuffle Join MERGEJOIN[1189][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 16' is a cross product
+Warning: Shuffle Join MERGEJOIN[1196][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 22' is a cross product
PREHOOK: query: explain cbo
with cross_items as
(select i_item_sk ss_item_sk
@@ -229,7 +226,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5])
HiveUnion(all=[true])
HiveProject(channel=[_UTF-16LE'store'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
@@ -293,70 +290,36 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'catalog'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
@@ -420,70 +383,36 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(channel=[_UTF-16LE'web'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
- HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[>($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
@@ -547,66 +476,32 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
- HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject
- HiveProject($f0=[$0])
- HiveAggregate(group=[{}], agg#0=[count($0)])
- HiveProject(quantity=[$0], list_price=[$1])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject($f0=[/($0, $1)])
- HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
- HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
- HiveUnion(all=[true])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(quantity=[$1], list_price=[$2])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])