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 2018/11/12 18:04:42 UTC
[22/51] [partial] hive git commit: HIVE-20850: Push case conditional
from projections to dimension tables if possible (Zoltan Haindrich via Jesus
Camacho Rodriguez)
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query13.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query13.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query13.q.out
index ccad088..19f3039 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query13.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query13.q.out
@@ -114,28 +114,28 @@ POSTHOOK: Input: default@store_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject($f0=[/(CAST($0):DOUBLE, $1)], $f1=[/($2, $3)], $f2=[/($4, $5)], $f3=[CAST($4):DECIMAL(17, 2)])
- HiveAggregate(group=[{}], agg#0=[sum($16)], agg#1=[count($16)], agg#2=[sum($18)], agg#3=[count($18)], agg#4=[sum($19)], agg#5=[count($19)])
- HiveJoin(condition=[AND(=($0, $12), OR(AND(=($1, _UTF-16LE'M'), =($2, _UTF-16LE'4 yr Degree'), BETWEEN(false, $17, 100, 150), =($7, 3)), AND(=($1, _UTF-16LE'D'), =($2, _UTF-16LE'Primary'), BETWEEN(false, $17, 50, 100), =($7, 1)), AND(=($1, _UTF-16LE'U'), =($2, _UTF-16LE'Advanced Degree'), BETWEEN(false, $17, 150, 200), =($7, 1))))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2], cd_education_status=[$3])
+ HiveAggregate(group=[{}], agg#0=[sum($21)], agg#1=[count($21)], agg#2=[sum($22)], agg#3=[count($22)], agg#4=[sum($23)], agg#5=[count($23)])
+ HiveJoin(condition=[AND(=($0, $17), OR(AND($1, $2, $27, $12), AND($3, $4, $28, $13), AND($5, $6, $29, $13)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cd_demo_sk=[$0], ==[=($2, _UTF-16LE'M')], =2=[=($3, _UTF-16LE'4 yr Degree')], =3=[=($2, _UTF-16LE'D')], =4=[=($3, _UTF-16LE'Primary')], =5=[=($2, _UTF-16LE'U')], =6=[=($3, _UTF-16LE'Advanced Degree')])
HiveFilter(condition=[AND(IN($2, _UTF-16LE'M', _UTF-16LE'D', _UTF-16LE'U'), IN($3, _UTF-16LE'4 yr Degree', _UTF-16LE'Primary', _UTF-16LE'Advanced Degree'), IS NOT NULL($0))])
HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics])
- HiveJoin(condition=[AND(=($11, $0), OR(AND(IN($1, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM'), BETWEEN(false, $17, 100, 200)), AND(IN($1, _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN'), BETWEEN(false, $17, 150, 300)), AND(IN($1, _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV'), BETWEEN(false, $17, 50, 250))))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0], ca_state=[$8], ca_country=[CAST(_UTF-16LE'United States'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"])
+ HiveJoin(condition=[AND(=($12, $0), OR(AND($1, $17), AND($2, $18), AND($3, $19)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], IN=[IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM')], IN2=[IN($8, _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN')], IN3=[IN($8, _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV')])
HiveFilter(condition=[AND(IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM', _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN', _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV'), =($10, _UTF-16LE'United States'), IS NOT NULL($0))])
HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(hd_demo_sk=[$0], hd_dep_count=[$3])
+ HiveProject(hd_demo_sk=[$0], ==[=($3, 3)], =2=[=($3, 1)])
HiveFilter(condition=[AND(IN($3, 3, 1), IS NOT NULL($0))])
HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
- HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2001):INTEGER])
- HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(s_store_sk=[$0])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveProject(ss_sold_date_sk=[$0], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_quantity=[$10], ss_sales_price=[$13], ss_ext_sales_price=[$15], ss_ext_wholesale_cost=[$16], ss_net_profit=[$22])
+ HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(s_store_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveJoin(condition=[=($1, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(ss_sold_date_sk=[$0], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_quantity=[$10], ss_ext_sales_price=[$15], ss_ext_wholesale_cost=[$16], BETWEEN=[BETWEEN(false, $22, 100, 200)], BETWEEN9=[BETWEEN(false, $22, 150, 300)], BETWEEN10=[BETWEEN(false, $22, 50, 250)], BETWEEN11=[BETWEEN(false, $13, 100, 150)], BETWEEN12=[BETWEEN(false, $13, 50, 100)], BETWEEN13=[BETWEEN(false, $13, 150, 200)])
HiveFilter(condition=[AND(OR(BETWEEN(false, $13, 100, 150), BETWEEN(false, $13, 50, 100), BETWEEN(false, $13, 150, 200)), OR(BETWEEN(false, $22, 100, 200), BETWEEN(false, $22, 150, 300), BETWEEN(false, $22, 50, 250)), IS NOT NULL($7), IS NOT NULL($4), IS NOT NULL($5), IS NOT NULL($6), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/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 43a4fab..e5a0be1 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
@@ -233,7 +233,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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)])
- HiveSemiJoin(condition=[=($5, $11)], joinType=[inner])
+ HiveSemiJoin(condition=[=($5, $9)], joinType=[inner])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[IS NOT NULL($0)])
@@ -242,7 +242,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12])
HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f0=[$4], $f1=[$5], $f2=[$6])
@@ -256,39 +256,39 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
HiveUnion(all=[true])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
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=[iss])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
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=[ics])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
@@ -309,7 +309,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -317,7 +317,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -325,7 +325,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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)])
@@ -337,7 +337,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -345,7 +345,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -353,7 +353,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -361,7 +361,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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)])
- HiveSemiJoin(condition=[=($5, $11)], joinType=[inner])
+ HiveSemiJoin(condition=[=($5, $9)], joinType=[inner])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[IS NOT NULL($0)])
@@ -370,7 +370,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_quantity=[$18], cs_list_price=[$20])
HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f0=[$4], $f1=[$5], $f2=[$6])
@@ -384,39 +384,39 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
HiveUnion(all=[true])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
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=[iss])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
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=[ics])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
@@ -437,7 +437,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -445,7 +445,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -453,7 +453,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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)])
@@ -465,7 +465,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -473,7 +473,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -481,7 +481,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -489,7 +489,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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)])
- HiveSemiJoin(condition=[=($5, $11)], joinType=[inner])
+ HiveSemiJoin(condition=[=($5, $9)], joinType=[inner])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
HiveFilter(condition=[IS NOT NULL($0)])
@@ -498,7 +498,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_quantity=[$18], ws_list_price=[$20])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 2000), =($8, 11), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f0=[$4], $f1=[$5], $f2=[$6])
@@ -512,39 +512,39 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
HiveUnion(all=[true])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
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=[iss])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
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=[ics])
HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
- HiveAggregate(group=[{5, 6, 7}], agg#0=[count()])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $6, 1999, 2001), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
@@ -565,7 +565,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -573,7 +573,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -581,7 +581,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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)])
@@ -593,7 +593,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -601,7 +601,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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])
@@ -609,7 +609,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
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], d_year=[$6])
+ 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/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query15.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query15.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query15.q.out
index 522bbb0..c00a741 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query15.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query15.q.out
@@ -49,21 +49,21 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100])
HiveProject(ca_zip=[$0], $f1=[$1])
- HiveAggregate(group=[{4}], agg#0=[sum($7)])
- HiveJoin(condition=[AND(=($6, $0), OR(>($7, 500), IN(substr($4, 1, 5), _UTF-16LE'85669', _UTF-16LE'86197', _UTF-16LE'88274', _UTF-16LE'83405', _UTF-16LE'86475', _UTF-16LE'85392', _UTF-16LE'85460', _UTF-16LE'80348', _UTF-16LE'81792'), IN($3, _UTF-16LE'CA', _UTF-16LE'WA', _UTF-16LE'GA')))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{3}], agg#0=[sum($8)])
+ HiveJoin(condition=[AND(=($7, $0), OR($9, $4, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_state=[$8], ca_zip=[$9])
+ HiveProject(ca_address_sk=[$0], ca_zip=[$9], IN=[IN(substr($9, 1, 5), _UTF-16LE'85669', _UTF-16LE'86197', _UTF-16LE'88274', _UTF-16LE'83405', _UTF-16LE'86475', _UTF-16LE'85392', _UTF-16LE'85460', _UTF-16LE'80348', _UTF-16LE'81792')], IN3=[IN($8, _UTF-16LE'CA', _UTF-16LE'WA', _UTF-16LE'GA')])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_sales_price=[$2], d_date_sk=[$3], d_year=[$4], d_qoy=[$5])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_sales_price=[$21])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_sales_price=[$2], >=[$3], d_date_sk=[$4])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_sales_price=[$21], >=[>($21, 500)])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER], d_qoy=[CAST(2):INTEGER])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($10, 2), =($6, 2000), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query17.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query17.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query17.q.out
index 6c5b480..84832a4 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query17.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query17.q.out
@@ -104,35 +104,35 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject(i_item_id=[$0], i_item_desc=[$1], s_state=[$2], store_sales_quantitycount=[$3], store_sales_quantityave=[/(CAST($4):DOUBLE, $3)], store_sales_quantitystdev=[POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5)], store_sales_quantitycov=[/(POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5), /(CAST($4):DOUBLE, $3))], as_store_returns_quantitycount=[$7], as_store_returns_quantityave=[/(CAST($8):DOUBLE, $7)], as_store_returns_quantitystdev=[POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5)], store_returns_quantitycov=[/(POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5), /(CAST($8):DOUBLE, $7))], catalog_sales_quantitycount=[$11], catalog_sales_quantityave=[/(CAST($12):DOUBLE, $11)], catalog_sales_quantitystdev=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))], catalog_sales_quantitycov=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($
11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))])
HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)], agg#1=[sum($3)], agg#2=[sum($7)], agg#3=[sum($6)], agg#4=[count($4)], agg#5=[sum($4)], agg#6=[sum($9)], agg#7=[sum($8)], agg#8=[count($5)], agg#9=[sum($5)], agg#10=[sum($11)], agg#11=[sum($10)])
- HiveProject($f0=[$9], $f1=[$10], $f2=[$25], $f3=[$5], $f4=[$21], $f5=[$14], $f30=[CAST($5):DOUBLE], $f7=[*(CAST($5):DOUBLE, CAST($5):DOUBLE)], $f40=[CAST($21):DOUBLE], $f9=[*(CAST($21):DOUBLE, CAST($21):DOUBLE)], $f50=[CAST($14):DOUBLE], $f11=[*(CAST($14):DOUBLE, CAST($14):DOUBLE)])
- HiveJoin(condition=[=($24, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(AND(=($2, $19), =($1, $18)), =($4, $20))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($8, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$8], $f1=[$9], $f2=[$22], $f3=[$5], $f4=[$19], $f5=[$13], $f30=[CAST($5):DOUBLE], $f7=[*(CAST($5):DOUBLE, CAST($5):DOUBLE)], $f40=[CAST($19):DOUBLE], $f9=[*(CAST($19):DOUBLE, CAST($19):DOUBLE)], $f50=[CAST($13):DOUBLE], $f11=[*(CAST($13):DOUBLE, CAST($13):DOUBLE)])
+ HiveJoin(condition=[=($21, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(AND(=($2, $17), =($1, $16)), =($4, $18))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_quantity=[$10])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($2), IS NOT NULL($9), IS NOT NULL($0), IS NOT NULL($7))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_quarter_name=[CAST(_UTF-16LE'2000Q1'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($15, _UTF-16LE'2000Q1'), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_quantity=[$3], d_date_sk=[$4], d_quarter_name=[$5], sr_returned_date_sk=[$6], sr_item_sk=[$7], sr_customer_sk=[$8], sr_ticket_number=[$9], sr_return_quantity=[$10], d_date_sk0=[$11], d_quarter_name0=[$12])
- HiveJoin(condition=[AND(=($8, $1), =($7, $2))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_quantity=[$3], d_date_sk=[$4], sr_returned_date_sk=[$5], sr_item_sk=[$6], sr_customer_sk=[$7], sr_ticket_number=[$8], sr_return_quantity=[$9], d_date_sk0=[$10])
+ HiveJoin(condition=[AND(=($7, $1), =($6, $2))], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_quantity=[$18])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_quarter_name=[$15])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3'), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
- HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_return_quantity=[$4], d_date_sk=[$5], d_quarter_name=[$6])
+ HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_return_quantity=[$4], d_date_sk=[$5])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_customer_sk=[$3], sr_ticket_number=[$9], sr_return_quantity=[$10])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($2), IS NOT NULL($9), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveProject(d_date_sk=[$0], d_quarter_name=[$15])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3'), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
HiveProject(s_store_sk=[$0], s_state=[$24])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query18.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query18.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query18.q.out
index 7e931c5..ebeb13b 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query18.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query18.q.out
@@ -80,35 +80,34 @@ POSTHOOK: Input: default@item
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$3], sort3=[$0], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
- HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[/($4, $5)], $f5=[/($6, $7)], $f6=[/($8, $9)], $f7=[/($10, $11)], $f8=[/($12, $13)], $f9=[/($14, $15)], $f10=[/($16, $17)])
- HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($4)], agg#1=[count($4)], agg#2=[sum($5)], agg#3=[count($5)], agg#4=[sum($6)], agg#5=[count($6)], agg#6=[sum($7)], agg#7=[count($7)], agg#8=[sum($8)], agg#9=[count($8)], agg#10=[sum($9)], agg#11=[count($9)], agg#12=[sum($10)], agg#13=[count($10)])
- HiveProject($f0=[$11], $f1=[$8], $f2=[$7], $f3=[$6], $f4=[CAST($16):DECIMAL(12, 2)], $f5=[CAST($17):DECIMAL(12, 2)], $f6=[CAST($19):DECIMAL(12, 2)], $f7=[CAST($18):DECIMAL(12, 2)], $f8=[CAST($20):DECIMAL(12, 2)], $f9=[CAST($4):DECIMAL(12, 2)], $f10=[CAST($26):DECIMAL(12, 2)])
- HiveJoin(condition=[=($13, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $9)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($2, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_addr_sk=[$4], c_birth_month=[$12], c_birth_year=[$13])
- HiveFilter(condition=[AND(IN($12, 9, 5, 12, 4, 1, 10), IS NOT NULL($0), IS NOT NULL($2), IS NOT NULL($4))])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8], ca_country=[$10])
- HiveFilter(condition=[AND(IN($8, _UTF-16LE'ND', _UTF-16LE'WI', _UTF-16LE'AL', _UTF-16LE'NC', _UTF-16LE'OK', _UTF-16LE'MS', _UTF-16LE'TN'), IS NOT NULL($0))])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveProject(cd_demo_sk=[$0])
+ HiveProject($f0=[$3], $f1=[$2], $f2=[$1], $f3=[$0], $f4=[/($4, $5)], $f5=[/($6, $7)], $f6=[/($8, $9)], $f7=[/($10, $11)], $f8=[/($12, $13)], $f9=[/($14, $15)], $f10=[/($16, $17)])
+ HiveAggregate(group=[{5, 6, 7, 10}], groups=[[{5, 6, 7, 10}, {6, 7, 10}, {7, 10}, {10}, {}]], agg#0=[sum($15)], agg#1=[count($15)], agg#2=[sum($16)], agg#3=[count($16)], agg#4=[sum($17)], agg#5=[count($17)], agg#6=[sum($18)], agg#7=[count($18)], agg#8=[sum($19)], agg#9=[count($19)], agg#10=[sum($3)], agg#11=[count($3)], agg#12=[sum($22)], agg#13=[count($22)])
+ HiveJoin(condition=[=($12, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_addr_sk=[$4], CAST=[CAST($13):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(IN($12, 9, 5, 12, 4, 1, 10), IS NOT NULL($0), IS NOT NULL($2), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8], ca_country=[$10])
+ HiveFilter(condition=[AND(IN($8, _UTF-16LE'ND', _UTF-16LE'WI', _UTF-16LE'AL', _UTF-16LE'NC', _UTF-16LE'OK', _UTF-16LE'MS', _UTF-16LE'TN'), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(cd_demo_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], cs_sold_date_sk=[$2], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$5], CAST=[$6], CAST5=[$7], CAST6=[$8], CAST7=[$9], CAST8=[$10], d_date_sk=[$11], cd_demo_sk=[$12], CAST0=[$13])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
- HiveProject(i_item_sk=[$0], i_item_id=[$1], cs_sold_date_sk=[$2], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$5], cs_quantity=[$6], cs_list_price=[$7], cs_sales_price=[$8], cs_coupon_amt=[$9], cs_net_profit=[$10], d_date_sk=[$11], d_year=[$12], cd_demo_sk=[$13], cd_gender=[$14], cd_education_status=[$15], cd_dep_count=[$16])
- HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(i_item_sk=[$0], i_item_id=[$1])
- HiveFilter(condition=[IS NOT NULL($0)])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$15], cs_quantity=[$18], cs_list_price=[$20], cs_sales_price=[$21], cs_coupon_amt=[$27], cs_net_profit=[$33])
- HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($15))])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(2001):INTEGER])
- HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(cd_demo_sk=[$0], cd_gender=[CAST(_UTF-16LE'M'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], cd_education_status=[CAST(_UTF-16LE'College'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], cd_dep_count=[$6])
- HiveFilter(condition=[AND(=($1, _UTF-16LE'M'), =($3, _UTF-16LE'College'), IS NOT NULL($0))])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($2, $10)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$15], CAST=[CAST($18):DECIMAL(12, 2)], CAST5=[CAST($20):DECIMAL(12, 2)], CAST6=[CAST($27):DECIMAL(12, 2)], CAST7=[CAST($21):DECIMAL(12, 2)], CAST8=[CAST($33):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($15))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cd_demo_sk=[$0], CAST=[CAST($6):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(=($1, _UTF-16LE'M'), =($3, _UTF-16LE'College'), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query19.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query19.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query19.q.out
index c24d76f..be19bab 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query19.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query19.q.out
@@ -64,29 +64,29 @@ CBO PLAN:
HiveProject(brand_id=[$0], brand=[$1], i_manufact_id=[$2], i_manufact=[$3], ext_price=[$4])
HiveSortLimit(sort0=[$4], sort1=[$5], sort2=[$6], sort3=[$2], sort4=[$3], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
HiveProject(brand_id=[$0], brand=[$1], i_manufact_id=[$2], i_manufact=[$3], ext_price=[$4], (tok_table_or_col i_brand)=[$1], (tok_table_or_col i_brand_id)=[$0])
- HiveAggregate(group=[{13, 14, 15, 16}], agg#0=[sum($8)])
- HiveJoin(condition=[AND(<>(substr($3, 1, 5), substr($19, 1, 5)), =($7, $18))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{11, 12, 13, 14}], agg#0=[sum($8)])
+ HiveJoin(condition=[AND(<>($3, $16), =($7, $15))], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_zip=[$9])
+ HiveProject(ca_address_sk=[$0], substr=[substr($9, 1, 5)])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_store_sk=[$3], ss_ext_sales_price=[$4], d_date_sk=[$5], d_year=[$6], d_moy=[$7], i_item_sk=[$8], i_brand_id=[$9], i_brand=[$10], i_manufact_id=[$11], i_manufact=[$12], i_manager_id=[$13])
- HiveJoin(condition=[=($1, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_store_sk=[$3], ss_ext_sales_price=[$4], d_date_sk=[$5], i_item_sk=[$6], i_brand_id=[$7], i_brand=[$8], i_manufact_id=[$9], i_manufact=[$10])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ext_sales_price=[$15])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($7))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($8, 11), =($6, 1999), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_brand=[$8], i_manufact_id=[$13], i_manufact=[$14], i_manager_id=[CAST(7):INTEGER])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_brand=[$8], i_manufact_id=[$13], i_manufact=[$14])
HiveFilter(condition=[AND(=($20, 7), IS NOT NULL($0))])
HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(s_store_sk=[$0], s_zip=[$25])
+ HiveProject(s_store_sk=[$0], substr=[substr($25, 1, 5)])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, store]], table:alias=[store])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query2.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query2.q.out
index cca252e..7b14ad3 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query2.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query2.q.out
@@ -126,12 +126,12 @@ POSTHOOK: Input: default@web_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], dir0=[ASC])
- HiveProject(d_week_seq1=[$0], _o__c1=[round(/($1, $11), 2)], _o__c2=[round(/($2, $12), 2)], _o__c3=[round(/($3, $13), 2)], _o__c4=[round(/($4, $14), 2)], _o__c5=[round(/($5, $15), 2)], _o__c6=[round(/($6, $16), 2)], _o__c7=[round(/($7, $17), 2)])
- HiveJoin(condition=[=($0, -($10, 53))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_week_seq1=[$0], _o__c1=[round(/($1, $10), 2)], _o__c2=[round(/($2, $11), 2)], _o__c3=[round(/($3, $12), 2)], _o__c4=[round(/($4, $13), 2)], _o__c5=[round(/($5, $14), 2)], _o__c6=[round(/($6, $15), 2)], _o__c7=[round(/($7, $16), 2)])
+ HiveJoin(condition=[=($0, -($9, 53))], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)], agg#4=[sum($5)], agg#5=[sum($6)], agg#6=[sum($7)])
- HiveProject($f0=[$3], $f1=[CASE(=($4, _UTF-16LE'Sunday'), $1, null)], $f2=[CASE(=($4, _UTF-16LE'Monday'), $1, null)], $f3=[CASE(=($4, _UTF-16LE'Tuesday'), $1, null)], $f4=[CASE(=($4, _UTF-16LE'Wednesday'), $1, null)], $f5=[CASE(=($4, _UTF-16LE'Thursday'), $1, null)], $f6=[CASE(=($4, _UTF-16LE'Friday'), $1, null)], $f7=[CASE(=($4, _UTF-16LE'Saturday'), $1, null)])
+ HiveProject($f0=[$3], $f1=[CASE($4, $1, null)], $f2=[CASE($5, $1, null)], $f3=[CASE($6, $1, null)], $f4=[CASE($7, $1, null)], $f5=[CASE($8, $1, null)], $f6=[CASE($9, $1, null)], $f7=[CASE($10, $1, null)])
HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$1])
HiveUnion(all=[true])
@@ -141,17 +141,17 @@ HiveSortLimit(sort0=[$0], dir0=[ASC])
HiveProject(cs_sold_date_sk=[$0], cs_ext_sales_price=[$23])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_week_seq=[$4], d_day_name=[$14])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], ==[=($14, _UTF-16LE'Sunday')], =3=[=($14, _UTF-16LE'Monday')], =4=[=($14, _UTF-16LE'Tuesday')], =5=[=($14, _UTF-16LE'Wednesday')], =6=[=($14, _UTF-16LE'Thursday')], =7=[=($14, _UTF-16LE'Friday')], =8=[=($14, _UTF-16LE'Saturday')])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(d_week_seq=[$4], d_year=[CAST(2001):INTEGER])
+ HiveProject(d_week_seq=[$4])
HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(d_week_seq2=[$0], sun_sales2=[$1], mon_sales2=[$2], tue_sales2=[$3], wed_sales2=[$4], thu_sales2=[$5], fri_sales2=[$6], sat_sales2=[$7])
HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)], agg#4=[sum($5)], agg#5=[sum($6)], agg#6=[sum($7)])
- HiveProject($f0=[$3], $f1=[CASE(=($4, _UTF-16LE'Sunday'), $1, null)], $f2=[CASE(=($4, _UTF-16LE'Monday'), $1, null)], $f3=[CASE(=($4, _UTF-16LE'Tuesday'), $1, null)], $f4=[CASE(=($4, _UTF-16LE'Wednesday'), $1, null)], $f5=[CASE(=($4, _UTF-16LE'Thursday'), $1, null)], $f6=[CASE(=($4, _UTF-16LE'Friday'), $1, null)], $f7=[CASE(=($4, _UTF-16LE'Saturday'), $1, null)])
+ HiveProject($f0=[$3], $f1=[CASE($4, $1, null)], $f2=[CASE($5, $1, null)], $f3=[CASE($6, $1, null)], $f4=[CASE($7, $1, null)], $f5=[CASE($8, $1, null)], $f6=[CASE($9, $1, null)], $f7=[CASE($10, $1, null)])
HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$1])
HiveUnion(all=[true])
@@ -161,10 +161,10 @@ HiveSortLimit(sort0=[$0], dir0=[ASC])
HiveProject(cs_sold_date_sk=[$0], cs_ext_sales_price=[$23])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_week_seq=[$4], d_day_name=[$14])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], ==[=($14, _UTF-16LE'Sunday')], =3=[=($14, _UTF-16LE'Monday')], =4=[=($14, _UTF-16LE'Tuesday')], =5=[=($14, _UTF-16LE'Wednesday')], =6=[=($14, _UTF-16LE'Thursday')], =7=[=($14, _UTF-16LE'Friday')], =8=[=($14, _UTF-16LE'Saturday')])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(d_week_seq=[$4], d_year=[CAST(2002):INTEGER])
+ HiveProject(d_week_seq=[$4])
HiveFilter(condition=[AND(=($6, 2002), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query20.q.out
----------------------------------------------------------------------
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 834c804..ba354b6 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
@@ -75,7 +75,7 @@ HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_ext_sales_price=[$23])
HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 2001-01-12 00:00:00, 2001-02-11 00:00:00), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query21.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query21.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query21.q.out
index a54a085..3a67503 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query21.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query21.q.out
@@ -71,20 +71,20 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3])
HiveFilter(condition=[CASE(>($2, 0), BETWEEN(false, /(CAST($3):DOUBLE, CAST($2):DOUBLE), 6.66667E-1, 1.5E0), null)])
HiveAggregate(group=[{0, 1}], agg#0=[sum($2)], agg#1=[sum($3)])
- HiveProject($f0=[$1], $f1=[$9], $f2=[CASE(<(CAST($7):DATE, 1998-04-08), $5, 0)], $f3=[CASE(>=(CAST($7):DATE, 1998-04-08), $5, 0)])
+ HiveProject($f0=[$1], $f1=[$10], $f2=[CASE($7, $5, 0)], $f3=[CASE($8, $5, 0)])
HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(w_warehouse_sk=[$0], w_warehouse_name=[$2])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse])
- HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3])
HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($1), IS NOT NULL($0))])
HiveTableScan(table=[[default, inventory]], table:alias=[inventory])
- HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveProject(d_date_sk=[$0], <=[<(CAST($2):DATE, 1998-04-08)], >==[>=(CAST($2):DATE, 1998-04-08)])
HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-03-09 00:00:00, 1998-05-08 00:00:00), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(i_item_sk=[$0], i_item_id=[$1], i_current_price=[$5])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
HiveFilter(condition=[AND(BETWEEN(false, $5, 0.99, 1.49), IS NOT NULL($0))])
HiveTableScan(table=[[default, item]], table:alias=[item])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query22.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query22.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query22.q.out
index c5118ee..7231d6e 100644
--- a/ql/src/test/results/clientpositive/perf/tez/cbo_query22.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query22.q.out
@@ -58,12 +58,12 @@ HiveSortLimit(sort0=[$4], sort1=[$0], sort2=[$1], sort3=[$2], sort4=[$3], dir0=[
HiveProject(i_item_sk=[$0], i_brand=[$8], i_class=[$10], i_category=[$12], i_product_name=[$21])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveJoin(condition=[=($2, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3])
HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2))])
HiveTableScan(table=[[default, inventory]], table:alias=[inventory])
- HiveProject(d_date_sk=[$0], d_month_seq=[$3])
+ HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $3, 1212, 1223), IS NOT NULL($0))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(w_warehouse_sk=[$0])