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])
