http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query72.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query72.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query72.q.out index fca31ef..e49b44b 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query72.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query72.q.out @@ -82,10 +82,10 @@ CBO PLAN: HiveSortLimit(sort0=[$5], sort1=[$0], sort2=[$1], sort3=[$2], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5]) HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)], agg#1=[count($4)], agg#2=[count()]) - HiveProject($f0=[$15], $f1=[$13], $f2=[$22], $f3=[CASE(IS NULL($28), 1, 0)], $f4=[CASE(IS NOT NULL($28), 1, 0)]) - HiveJoin(condition=[AND(=($29, $4), =($30, $6))], joinType=[left], algorithm=[none], cost=[not available]) - HiveProject(cs_sold_date_sk=[$10], cs_ship_date_sk=[$11], cs_bill_cdemo_sk=[$12], cs_bill_hdemo_sk=[$13], cs_item_sk=[$14], cs_promo_sk=[$15], cs_order_number=[$16], cs_quantity=[$17], inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3], w_warehouse_sk=[$4], w_warehouse_name=[$5], i_item_sk=[$8], i_item_desc=[$9], cd_demo_sk=[$22], cd_marital_status=[$23], hd_demo_sk=[$24], hd_buy_potential=[$25], d_date_sk=[$18], d_date=[$19], d_week_seq=[$20], d_year=[$21], d_date_sk0=[$27], d_week_seq0=[$28], d_date_sk1=[$6], d_date0=[$7], p_promo_sk=[$26]) - HiveJoin(condition=[AND(=($0, $27), =($20, $28))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$15], $f1=[$13], $f2=[$19], $f3=[CASE(IS NULL($25), 1, 0)], $f4=[CASE(IS NOT NULL($25), 1, 0)]) + HiveJoin(condition=[AND(=($26, $4), =($27, $6))], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(cs_sold_date_sk=[$10], cs_ship_date_sk=[$11], cs_bill_cdemo_sk=[$12], cs_bill_hdemo_sk=[$13], cs_item_sk=[$14], cs_promo_sk=[$15], cs_order_number=[$16], cs_quantity=[$17], inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3], w_warehouse_sk=[$4], w_warehouse_name=[$5], i_item_sk=[$8], i_item_desc=[$9], cd_demo_sk=[$21], hd_demo_sk=[$22], d_date_sk=[$18], d_week_seq=[$19], +=[$20], d_date_sk0=[$24], d_week_seq0=[$25], d_date_sk1=[$6], CAST=[$7], p_promo_sk=[$23]) + HiveJoin(condition=[AND(=($0, $24), =($19, $25))], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[AND(=($14, $1), <($3, $17))], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($4, $2)], 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]) @@ -94,29 +94,29 @@ HiveSortLimit(sort0=[$5], sort1=[$0], sort2=[$1], sort3=[$2], dir0=[DESC-nulls-l HiveProject(w_warehouse_sk=[$0], w_warehouse_name=[$2]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse]) - HiveProject(d_date_sk=[$0], d_date=[$1], i_item_sk=[$2], i_item_desc=[$3], cs_sold_date_sk=[$4], cs_ship_date_sk=[$5], cs_bill_cdemo_sk=[$6], cs_bill_hdemo_sk=[$7], cs_item_sk=[$8], cs_promo_sk=[$9], cs_order_number=[$10], cs_quantity=[$11], d_date_sk0=[$12], d_date0=[$13], d_week_seq=[$14], d_year=[$15], cd_demo_sk=[$16], cd_marital_status=[$17], hd_demo_sk=[$18], hd_buy_potential=[$19], p_promo_sk=[$20]) - HiveJoin(condition=[AND(=($5, $0), >(CAST($1):DOUBLE, +(CAST($13):DOUBLE, 5)))], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0], CAST=[$1], i_item_sk=[$2], i_item_desc=[$3], cs_sold_date_sk=[$4], cs_ship_date_sk=[$5], cs_bill_cdemo_sk=[$6], cs_bill_hdemo_sk=[$7], cs_item_sk=[$8], cs_promo_sk=[$9], cs_order_number=[$10], cs_quantity=[$11], d_date_sk0=[$12], d_week_seq=[$13], +=[$14], cd_demo_sk=[$15], hd_demo_sk=[$16], p_promo_sk=[$17]) + HiveJoin(condition=[AND(=($5, $0), >($1, $14))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(d_date_sk=[$0], CAST=[CAST($2):DOUBLE]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, date_dim]], table:alias=[d3]) HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(i_item_sk=[$0], i_item_desc=[$4]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveJoin(condition=[=($5, $16)], joinType=[left], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($3, $14)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($2, $12)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($5, $13)], joinType=[left], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $12)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $8)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_ship_date_sk=[$2], cs_bill_cdemo_sk=[$4], cs_bill_hdemo_sk=[$5], cs_item_sk=[$15], cs_promo_sk=[$16], cs_order_number=[$17], cs_quantity=[$18]) HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($4), IS NOT NULL($5), IS NOT NULL($0), IS NOT NULL($2))]) HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) - HiveProject(d_date_sk=[$0], d_date=[$2], d_week_seq=[$4], d_year=[CAST(2001):INTEGER]) + HiveProject(d_date_sk=[$0], d_week_seq=[$4], +=[+(CAST($2):DOUBLE, 5)]) HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[d1]) - HiveProject(cd_demo_sk=[$0], cd_marital_status=[CAST(_UTF-16LE'M'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(cd_demo_sk=[$0]) HiveFilter(condition=[AND(=($2, _UTF-16LE'M'), IS NOT NULL($0))]) HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics]) - HiveProject(hd_demo_sk=[$0], hd_buy_potential=[CAST(_UTF-16LE'1001-5000'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(hd_demo_sk=[$0]) HiveFilter(condition=[AND(=($2, _UTF-16LE'1001-5000'), IS NOT NULL($0))]) HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics]) HiveProject(p_promo_sk=[$0])
http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query73.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query73.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query73.q.out index d28a896..2639cf2 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query73.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query73.q.out @@ -75,19 +75,19 @@ HiveSortLimit(sort0=[$5], dir0=[DESC-nulls-last]) HiveFilter(condition=[BETWEEN(false, $2, 1, 5)]) HiveProject(ss_ticket_number=[$1], ss_customer_sk=[$0], $f2=[$2]) HiveAggregate(group=[{1, 4}], agg#0=[count()]) - HiveJoin(condition=[=($3, $12)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($2, $8)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $7)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $6)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_hdemo_sk=[$5], ss_store_sk=[$7], ss_ticket_number=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($5), IS NOT NULL($3))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_year=[$6], d_dom=[$9]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 2000, 2001, 2002), BETWEEN(false, $9, 1, 2), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(hd_demo_sk=[$0], hd_buy_potential=[$2], hd_dep_count=[$3], hd_vehicle_count=[$4]) + HiveProject(hd_demo_sk=[$0]) HiveFilter(condition=[AND(IN($2, _UTF-16LE'>10000', _UTF-16LE'unknown'), >($4, 0), CASE(>($4, 0), >(/(CAST($3):DOUBLE, CAST($4):DOUBLE), 1), null), IS NOT NULL($0))]) HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics]) - HiveProject(s_store_sk=[$0], s_county=[$23]) + HiveProject(s_store_sk=[$0]) HiveFilter(condition=[AND(IN($23, _UTF-16LE'Mobile County', _UTF-16LE'Maverick County', _UTF-16LE'Huron County', _UTF-16LE'Kittitas County'), 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_query74.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query74.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query74.q.out index 32d6e03..4a9a112 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query74.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query74.q.out @@ -131,9 +131,9 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100]) HiveProject(customer_id=[$0], customer_first_name=[$1], customer_last_name=[$2]) - HiveJoin(condition=[AND(=($0, $6), CASE(CAST(IS NOT NULL($7)):BOOLEAN, CASE(CAST(IS NOT NULL($9)):BOOLEAN, >(/($5, $9), /($3, $7)), >(null, /($3, $7))), CASE(CAST(IS NOT NULL($9)):BOOLEAN, >(/($5, $9), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(customer_id=[$0], customer_first_name=[$1], customer_last_name=[$2], year_total=[$4]) - HiveAggregate(group=[{1, 2, 3, 8}], agg#0=[max($6)]) + HiveJoin(condition=[AND(=($0, $6), CASE(CAST(IS NOT NULL($7)):BOOLEAN, CASE($10, >(/($5, $9), /($3, $7)), >(null, /($3, $7))), CASE($10, >(/($5, $9), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_id=[$0], c_first_name=[$1], c_last_name=[$2], $f3=[$3]) + HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) @@ -142,13 +142,13 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_net_paid=[$20]) HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2002):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(customer_id=[$0], year_total=[$4]) - HiveAggregate(group=[{1, 2, 3, 8}], agg#0=[max($6)]) + HiveProject($f0=[$0], $f4=[$3]) + HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) @@ -157,12 +157,12 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29]) HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))]) HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2002):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(customer_id=[$0], year_total=[$4]) - HiveFilter(condition=[>($4, 0)]) - HiveAggregate(group=[{1, 2, 3, 8}], agg#0=[max($6)]) + HiveProject($f0=[$0], $f4=[$3]) + HiveFilter(condition=[>($3, 0)]) + HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) @@ -171,12 +171,12 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_net_paid=[$20]) HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2001):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2001), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(customer_id=[$0], year_total=[$4]) - HiveFilter(condition=[>($4, 0)]) - HiveAggregate(group=[{1, 2, 3, 8}], agg#0=[max($6)]) + HiveProject(customer_id=[$0], year_total=[$3], CAST=[CAST(IS NOT NULL($3)):BOOLEAN]) + HiveFilter(condition=[>($3, 0)]) + HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) @@ -185,7 +185,7 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29]) HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))]) HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2001):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2001), 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_query75.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query75.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query75.q.out index 3d87d1b..8c445d9 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query75.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query75.q.out @@ -169,52 +169,52 @@ HiveProject(prev_year=[CAST(2001):INTEGER], year=[CAST(2002):INTEGER], i_brand_i HiveAggregate(group=[{0, 1, 2, 3, 4, 5}]) HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], i_manufact_id=[$3], sales_cnt=[$4], sales_amt=[$5]) HiveUnion(all=[true]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(cr_item_sk=[$2], cr_order_number=[$16], cr_return_quantity=[$17], cr_return_amount=[$18]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_order_number=[$17], cs_quantity=[$18], 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_year=[CAST(2001):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9], sr_return_quantity=[$10], sr_return_amt=[$11]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], 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_ticket_number=[$9], ss_quantity=[$10], ss_ext_sales_price=[$15]) 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(2001):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(wr_item_sk=[$2], wr_order_number=[$13], wr_return_quantity=[$14], wr_return_amt=[$15]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_order_number=[$17], ws_quantity=[$18], ws_ext_sales_price=[$23]) 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(2001):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) HiveTableScan(table=[[default, item]], table:alias=[item]) HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], i_manufact_id=[$3], $f4=[$4], $f5=[$5]) @@ -227,52 +227,52 @@ HiveProject(prev_year=[CAST(2001):INTEGER], year=[CAST(2002):INTEGER], i_brand_i HiveAggregate(group=[{0, 1, 2, 3, 4, 5}]) HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], i_manufact_id=[$3], sales_cnt=[$4], sales_amt=[$5]) HiveUnion(all=[true]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(cr_item_sk=[$2], cr_order_number=[$16], cr_return_quantity=[$17], cr_return_amount=[$18]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_order_number=[$17], cs_quantity=[$18], 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_year=[CAST(2002):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9], sr_return_quantity=[$10], sr_return_amt=[$11]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], 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_ticket_number=[$9], ss_quantity=[$10], ss_ext_sales_price=[$15]) 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(2002):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(i_brand_id=[$12], i_class_id=[$13], i_category_id=[$14], i_manufact_id=[$16], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) + HiveProject(i_brand_id=[$11], i_class_id=[$12], i_category_id=[$13], i_manufact_id=[$14], sales_cnt=[-($7, CASE(IS NOT NULL($2), $2, 0))], sales_amt=[-($8, CASE(IS NOT NULL($3), $3, 0))]) HiveJoin(condition=[AND(=($6, $1), =($5, $0))], joinType=[right], algorithm=[none], cost=[not available]) HiveProject(wr_item_sk=[$2], wr_order_number=[$13], wr_return_quantity=[$14], wr_return_amt=[$15]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_order_number=[$17], ws_quantity=[$18], ws_ext_sales_price=[$23]) 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(2002):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_category=[CAST(_UTF-16LE'Sports'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11], i_manufact_id=[$13]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Sports'), IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11), IS NOT NULL($13))]) 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_query76.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query76.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query76.q.out index 74f888c..740d038 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query76.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query76.q.out @@ -62,7 +62,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], dir0=[ HiveAggregate(group=[{0, 1, 2, 3, 4}], agg#0=[count()], agg#1=[sum($5)]) HiveProject(channel=[$0], col_name=[$1], d_year=[$2], d_qoy=[$3], i_category=[$4], ext_sales_price=[$5]) HiveUnion(all=[true]) - HiveProject(channel=[_UTF-16LE'store'], col_name=[_UTF-16LE'ss_addr_sk'], d_year=[$1], d_qoy=[$2], i_category=[$4], ext_sales_price=[$8]) + HiveProject(channel=[_UTF-16LE'store'], col_name=[_UTF-16LE'ss_addr_sk'], d_year=[$1], d_qoy=[$2], i_category=[$4], ext_sales_price=[$7]) HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10]) HiveFilter(condition=[IS NOT NULL($0)]) @@ -71,13 +71,13 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], dir0=[ HiveProject(i_item_sk=[$0], i_category=[$12]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_addr_sk=[null], ss_ext_sales_price=[$15]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_ext_sales_price=[$15]) HiveFilter(condition=[AND(IS NULL($6), IS NOT NULL($2), IS NOT NULL($0))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(channel=[_UTF-16LE'web'], col_name=[_UTF-16LE'ws_web_page_sk'], d_year=[$7], d_qoy=[$8], i_category=[$5], ext_sales_price=[$3]) - HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_web_page_sk=[null], ws_ext_sales_price=[$23]) + HiveProject(channel=[_UTF-16LE'web'], col_name=[_UTF-16LE'ws_web_page_sk'], d_year=[$6], d_qoy=[$7], i_category=[$4], ext_sales_price=[$2]) + HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_ext_sales_price=[$23]) HiveFilter(condition=[AND(IS NULL($12), IS NOT NULL($3), IS NOT NULL($0))]) HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) HiveProject(i_item_sk=[$0], i_category=[$12]) @@ -86,10 +86,10 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], dir0=[ HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(channel=[_UTF-16LE'catalog'], col_name=[_UTF-16LE'cs_warehouse_sk'], d_year=[$7], d_qoy=[$8], i_category=[$5], ext_sales_price=[$3]) - HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(cs_sold_date_sk=[$0], cs_warehouse_sk=[null], cs_item_sk=[$15], cs_ext_sales_price=[$23]) + HiveProject(channel=[_UTF-16LE'catalog'], col_name=[_UTF-16LE'cs_warehouse_sk'], d_year=[$6], d_qoy=[$7], i_category=[$4], ext_sales_price=[$2]) + HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_ext_sales_price=[$23]) HiveFilter(condition=[AND(IS NULL($14), IS NOT NULL($15), IS NOT NULL($0))]) HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) HiveProject(i_item_sk=[$0], i_category=[$12]) http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query77.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query77.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query77.q.out index 2c42995..91ad054 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query77.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query77.q.out @@ -240,13 +240,13 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(channel=[_UTF-16LE'store channel'], id=[$0], sales=[$1], returns=[CASE(IS NOT NULL($4), $4, 0)], profit=[-($2, CASE(IS NOT NULL($5), $5, 0))]) HiveJoin(condition=[=($0, $3)], joinType=[left], algorithm=[none], cost=[not available]) HiveProject(s_store_sk=[$0], $f1=[$1], $f2=[$2]) - HiveAggregate(group=[{6}], agg#0=[sum($2)], agg#1=[sum($3)]) - HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveAggregate(group=[{5}], agg#0=[sum($2)], agg#1=[sum($3)]) + HiveJoin(condition=[=($1, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_store_sk=[$7], ss_ext_sales_price=[$15], ss_net_profit=[$22]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(s_store_sk=[$0]) @@ -262,7 +262,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(sr_returned_date_sk=[$0], sr_store_sk=[$7], sr_return_amt=[$11], sr_net_loss=[$19]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(channel=[_UTF-16LE'catalog channel'], id=[$0], sales=[$1], returns=[$3], profit=[-($2, $4)]) @@ -273,7 +273,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_ext_sales_price=[$23], cs_net_profit=[$33]) HiveFilter(condition=[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), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject($f0=[$0], $f1=[$1]) @@ -282,7 +282,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(cr_returned_date_sk=[$0], cr_return_amount=[$18], cr_net_loss=[$26]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(channel=[_UTF-16LE'web channel'], id=[$0], sales=[$1], returns=[CASE(IS NOT NULL($4), $4, 0)], profit=[-($2, CASE(IS NOT NULL($5), $5, 0))]) @@ -297,7 +297,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(ws_sold_date_sk=[$0], ws_web_page_sk=[$12], ws_ext_sales_price=[$23], ws_net_profit=[$33]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12))]) HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(wp_web_page_sk=[$0], $f1=[$1], $f2=[$2]) @@ -310,7 +310,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(wr_returned_date_sk=[$0], wr_web_page_sk=[$11], wr_return_amt=[$15], wr_net_loss=[$23]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($11))]) HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 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_query78.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query78.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query78.q.out index 66b345c..0263582 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query78.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query78.q.out @@ -139,12 +139,12 @@ HiveSortLimit(fetch=[100]) HiveFilter(condition=[CASE(IS NOT NULL($7), >($7, 0), false)]) HiveJoin(condition=[AND(=($5, $0), =($6, $1))], joinType=[left], algorithm=[none], cost=[not available]) HiveProject(ss_item_sk=[$0], ss_customer_sk=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) - HiveAggregate(group=[{3, 4}], agg#0=[sum($6)], agg#1=[sum($7)], agg#2=[sum($8)]) - HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER]) + HiveAggregate(group=[{2, 3}], agg#0=[sum($4)], agg#1=[sum($5)], agg#2=[sum($6)]) + HiveJoin(condition=[=($1, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2000), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_ticket_number=[$3], ss_quantity=[$4], ss_wholesale_cost=[$5], ss_sales_price=[$6], sr_item_sk=[$7], sr_ticket_number=[$8]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_quantity=[$4], ss_wholesale_cost=[$5], ss_sales_price=[$6]) HiveFilter(condition=[IS NULL($8)]) HiveJoin(condition=[AND(=($8, $3), =($1, $7))], joinType=[left], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_ticket_number=[$9], ss_quantity=[$10], ss_wholesale_cost=[$11], ss_sales_price=[$13]) @@ -153,12 +153,12 @@ HiveSortLimit(fetch=[100]) HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9]) HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) HiveProject(ws_item_sk=[$0], ws_bill_customer_sk=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) - HiveAggregate(group=[{3, 4}], agg#0=[sum($6)], agg#1=[sum($7)], agg#2=[sum($8)]) - HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER]) + HiveAggregate(group=[{2, 3}], agg#0=[sum($4)], agg#1=[sum($5)], agg#2=[sum($6)]) + HiveJoin(condition=[=($1, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2000), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$1], ws_bill_customer_sk=[$2], ws_order_number=[$3], ws_quantity=[$4], ws_wholesale_cost=[$5], ws_sales_price=[$6], wr_item_sk=[$7], wr_order_number=[$8]) + HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$1], ws_bill_customer_sk=[$2], ws_quantity=[$4], ws_wholesale_cost=[$5], ws_sales_price=[$6]) HiveFilter(condition=[IS NULL($8)]) HiveJoin(condition=[AND(=($8, $3), =($1, $7))], joinType=[left], algorithm=[none], cost=[not available]) HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_bill_customer_sk=[$4], ws_order_number=[$17], ws_quantity=[$18], ws_wholesale_cost=[$19], ws_sales_price=[$21]) @@ -167,12 +167,12 @@ HiveSortLimit(fetch=[100]) HiveProject(wr_item_sk=[$2], wr_order_number=[$13]) HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) HiveProject($f2=[$0], $f3=[$2], $f4=[$3], $f5=[$4]) - HiveAggregate(group=[{3, 4}], agg#0=[sum($6)], agg#1=[sum($7)], agg#2=[sum($8)]) - HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER]) + HiveAggregate(group=[{2, 3}], agg#0=[sum($4)], agg#1=[sum($5)], agg#2=[sum($6)]) + HiveJoin(condition=[=($1, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 2000), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_order_number=[$3], cs_quantity=[$4], cs_wholesale_cost=[$5], cs_sales_price=[$6], cr_item_sk=[$7], cr_order_number=[$8]) + HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_quantity=[$4], cs_wholesale_cost=[$5], cs_sales_price=[$6]) HiveFilter(condition=[IS NULL($8)]) HiveJoin(condition=[AND(=($8, $3), =($2, $7))], joinType=[left], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_order_number=[$17], cs_quantity=[$18], cs_wholesale_cost=[$19], cs_sales_price=[$21]) http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query79.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query79.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query79.q.out index 6da8ac2..4317c1b 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query79.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query79.q.out @@ -57,26 +57,26 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveProject(c_last_name=[$0], c_first_name=[$1], _o__c2=[$2], ss_ticket_number=[$3], amt=[$4], profit=[$5]) HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$6], sort3=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) - HiveProject(c_last_name=[$2], c_first_name=[$1], _o__c2=[substr($5, 1, 30)], ss_ticket_number=[$3], amt=[$6], profit=[$7], (tok_function substr (tok_table_or_col s_city) 1 30)=[substr($5, 1, 30)]) + HiveProject(c_last_name=[$2], c_first_name=[$1], _o__c2=[$8], ss_ticket_number=[$3], amt=[$6], profit=[$7], (tok_function substr (tok_table_or_col s_city) 1 30)=[substr($5, 1, 30)]) HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, customer]], table:alias=[customer]) - HiveProject(ss_ticket_number=[$2], ss_customer_sk=[$0], s_city=[$3], amt=[$4], profit=[$5]) - HiveAggregate(group=[{1, 3, 5, 13}], agg#0=[sum($6)], agg#1=[sum($7)]) - HiveJoin(condition=[=($2, $14)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $11)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ss_ticket_number=[$2], ss_customer_sk=[$0], s_city=[$3], amt=[$4], profit=[$5], substr=[substr($3, 1, 30)]) + HiveAggregate(group=[{1, 3, 5, 10}], agg#0=[sum($6)], agg#1=[sum($7)]) + HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($4, $9)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $8)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_coupon_amt=[$19], ss_net_profit=[$22]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($5), IS NOT NULL($3))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_year=[$6], d_dow=[CAST(1):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(IN($6, 1998, 1999, 2000), =($7, 1), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(s_store_sk=[$0], s_number_employees=[$6], s_city=[$22]) + HiveProject(s_store_sk=[$0], s_city=[$22]) HiveFilter(condition=[AND(BETWEEN(false, $6, 200, 295), IS NOT NULL($0))]) HiveTableScan(table=[[default, store]], table:alias=[store]) - HiveProject(hd_demo_sk=[$0], hd_dep_count=[$3], hd_vehicle_count=[$4]) + HiveProject(hd_demo_sk=[$0]) HiveFilter(condition=[AND(OR(=($3, 8), >($4, 0)), IS NOT NULL($0))]) HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics]) http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query8.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query8.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query8.q.out index 5c4f7a2..d80d336 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query8.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query8.q.out @@ -227,18 +227,18 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(s_store_name=[$0], $f1=[$1]) - HiveAggregate(group=[{8}], agg#0=[sum($2)]) - HiveJoin(condition=[=($1, $7)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveAggregate(group=[{6}], agg#0=[sum($2)]) + HiveJoin(condition=[=($1, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_store_sk=[$7], ss_net_profit=[$22]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(d_date_sk=[$0], d_year=[CAST(2002):INTEGER], d_qoy=[CAST(1):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($10, 1), =($6, 2002), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject($f0=[$0], s_store_sk=[$1], s_store_name=[$2], s_zip=[$3]) - HiveJoin(condition=[=(substr($3, 1, 2), substr($0, 1, 2))], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject($f0=[$0]) + HiveProject(substr=[$0], s_store_sk=[$1], s_store_name=[$2], substr0=[$3]) + HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(substr=[substr($0, 1, 2)]) HiveFilter(condition=[=($1, 2)]) HiveAggregate(group=[{0}], agg#0=[count($1)]) HiveProject(ca_zip=[$0], $f1=[$1]) @@ -257,10 +257,10 @@ HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(ca_address_sk=[$0], ca_zip=[$9]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL(substr(substr($9, 1, 5), 1, 2)))]) HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) - HiveProject(c_current_addr_sk=[$4], c_preferred_cust_flag=[CAST(_UTF-16LE'Y'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(c_current_addr_sk=[$4]) HiveFilter(condition=[AND(=($10, _UTF-16LE'Y'), IS NOT NULL($4))]) HiveTableScan(table=[[default, customer]], table:alias=[customer]) - HiveProject(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25]) + HiveProject(s_store_sk=[$0], s_store_name=[$5], substr=[substr($25, 1, 2)]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL(substr($25, 1, 2)))]) 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_query80.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query80.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query80.q.out index 9c43294..334f09c 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query80.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query80.q.out @@ -222,14 +222,14 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveUnion(all=[true]) HiveProject(channel=[_UTF-16LE'store channel'], id=[||(_UTF-16LE'store', $0)], sales=[$1], returns=[$2], profit=[$3]) HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)]) - HiveProject($f0=[$1], $f1=[$9], $f2=[CASE(IS NOT NULL($13), $13, 0)], $f3=[-($10, CASE(IS NOT NULL($14), $14, 0))]) - HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$1], $f1=[$8], $f2=[CASE(IS NOT NULL($12), $12, 0)], $f3=[-($9, CASE(IS NOT NULL($13), $13, 0))]) + HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(s_store_sk=[$0], s_store_id=[$1]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, store]], table:alias=[store]) - HiveJoin(condition=[=($5, $15)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_current_price=[$5]) + HiveJoin(condition=[=($4, $13)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0]) HiveFilter(condition=[AND(>($5, 50), IS NOT NULL($0))]) HiveTableScan(table=[[default, item]], table:alias=[item]) HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -240,22 +240,22 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9], sr_return_amt=[$11], sr_net_loss=[$19]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(p_promo_sk=[$0], p_channel_tv=[CAST(_UTF-16LE'N'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(p_promo_sk=[$0]) HiveFilter(condition=[AND(=($11, _UTF-16LE'N'), IS NOT NULL($0))]) HiveTableScan(table=[[default, promotion]], table:alias=[promotion]) HiveProject(channel=[_UTF-16LE'catalog channel'], id=[||(_UTF-16LE'catalog_page', $0)], sales=[$1], returns=[$2], profit=[$3]) HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)]) - HiveProject($f0=[$1], $f1=[$9], $f2=[CASE(IS NOT NULL($13), $13, 0)], $f3=[-($10, CASE(IS NOT NULL($14), $14, 0))]) - HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$1], $f1=[$8], $f2=[CASE(IS NOT NULL($12), $12, 0)], $f3=[-($9, CASE(IS NOT NULL($13), $13, 0))]) + HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cp_catalog_page_sk=[$0], cp_catalog_page_id=[$1]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, catalog_page]], table:alias=[catalog_page]) - HiveJoin(condition=[=($5, $15)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_current_price=[$5]) + HiveJoin(condition=[=($4, $13)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0]) HiveFilter(condition=[AND(>($5, 50), IS NOT NULL($0))]) HiveTableScan(table=[[default, item]], table:alias=[item]) HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -266,22 +266,22 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(cr_item_sk=[$2], cr_order_number=[$16], cr_return_amount=[$18], cr_net_loss=[$26]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(p_promo_sk=[$0], p_channel_tv=[CAST(_UTF-16LE'N'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(p_promo_sk=[$0]) HiveFilter(condition=[AND(=($11, _UTF-16LE'N'), IS NOT NULL($0))]) HiveTableScan(table=[[default, promotion]], table:alias=[promotion]) HiveProject(channel=[_UTF-16LE'web channel'], id=[||(_UTF-16LE'web_site', $0)], sales=[$1], returns=[$2], profit=[$3]) HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)]) - HiveProject($f0=[$18], $f1=[$9], $f2=[CASE(IS NOT NULL($13), $13, 0)], $f3=[-($10, CASE(IS NOT NULL($14), $14, 0))]) - HiveJoin(condition=[=($6, $17)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(p_promo_sk=[$0], p_channel_tv=[CAST(_UTF-16LE'N'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject($f0=[$15], $f1=[$7], $f2=[CASE(IS NOT NULL($11), $11, 0)], $f3=[-($8, CASE(IS NOT NULL($12), $12, 0))]) + HiveJoin(condition=[=($4, $14)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(p_promo_sk=[$0]) HiveFilter(condition=[AND(=($11, _UTF-16LE'N'), IS NOT NULL($0))]) HiveTableScan(table=[[default, promotion]], table:alias=[promotion]) - HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_current_price=[$5]) + HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0]) HiveFilter(condition=[AND(>($5, 50), IS NOT NULL($0))]) HiveTableScan(table=[[default, item]], table:alias=[item]) HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -292,7 +292,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(wr_item_sk=[$2], wr_order_number=[$13], wr_return_amt=[$15], wr_net_loss=[$23]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) - HiveProject(d_date_sk=[$0], d_date=[$2]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(web_site_sk=[$0], web_site_id=[$1]) http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query81.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query81.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query81.q.out index e13017f..0adb555 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query81.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query81.q.out @@ -71,13 +71,13 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveProject(c_customer_id=[$0], c_salutation=[$1], c_first_name=[$2], c_last_name=[$3], ca_street_number=[$4], ca_street_name=[$5], ca_street_type=[$6], ca_suite_number=[$7], ca_city=[$8], ca_county=[$9], ca_state=[CAST(_UTF-16LE'IL'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], ca_zip=[$10], ca_country=[$11], ca_gmt_offset=[$12], ca_location_type=[$13], ctr_total_return=[$14]) HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], sort5=[$5], sort6=[$6], sort7=[$7], sort8=[$8], sort9=[$9], sort10=[$10], sort11=[$11], sort12=[$12], sort13=[$13], sort14=[$14], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC], dir9=[ASC], dir10=[ASC], dir11=[ASC], dir12=[ASC], dir13=[ASC], dir14=[ASC], fetch=[100]) - HiveProject(c_customer_id=[$1], c_salutation=[$3], c_first_name=[$4], c_last_name=[$5], ca_street_number=[$7], ca_street_name=[$8], ca_street_type=[$9], ca_suite_number=[$10], ca_city=[$11], ca_county=[$12], ca_zip=[$14], ca_country=[$15], ca_gmt_offset=[$16], ca_location_type=[$17], ctr_total_return=[$20]) - HiveJoin(condition=[=($18, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_id=[$1], c_salutation=[$3], c_first_name=[$4], c_last_name=[$5], ca_street_number=[$7], ca_street_name=[$8], ca_street_type=[$9], ca_suite_number=[$10], ca_city=[$11], ca_county=[$12], ca_zip=[$13], ca_country=[$14], ca_gmt_offset=[$15], ca_location_type=[$16], ctr_total_return=[$19]) + HiveJoin(condition=[=($17, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($6, $2)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_current_addr_sk=[$4], c_salutation=[$7], c_first_name=[$8], c_last_name=[$9]) HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))]) HiveTableScan(table=[[default, customer]], table:alias=[customer]) - HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_street_type=[$4], ca_suite_number=[$5], ca_city=[$6], ca_county=[$7], ca_state=[CAST(_UTF-16LE'IL'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"], ca_zip=[$9], ca_country=[$10], ca_gmt_offset=[$11], ca_location_type=[$12]) + HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_street_type=[$4], ca_suite_number=[$5], ca_city=[$6], ca_county=[$7], ca_zip=[$9], ca_country=[$10], ca_gmt_offset=[$11], ca_location_type=[$12]) HiveFilter(condition=[AND(=($8, _UTF-16LE'IL'), IS NOT NULL($0))]) HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) HiveProject(cr_returning_customer_sk=[$0], ca_state=[$1], $f2=[$2], _o__c0=[$3], ctr_state=[$4]) @@ -92,7 +92,7 @@ HiveProject(c_customer_id=[$0], c_salutation=[$1], c_first_name=[$2], c_last_nam HiveProject(cr_returned_date_sk=[$0], cr_returning_customer_sk=[$7], cr_returning_addr_sk=[$10], cr_return_amt_inc_tax=[$20]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($10), IS NOT NULL($7))]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveProject(d_date_sk=[$0], d_year=[CAST(1998):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 1998), IS NOT NULL($0))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(_o__c0=[*(/($1, $2), 1.2)], ctr_state=[$0]) @@ -107,7 +107,7 @@ HiveProject(c_customer_id=[$0], c_salutation=[$1], c_first_name=[$2], c_last_nam HiveProject(cr_returned_date_sk=[$0], cr_returning_customer_sk=[$7], cr_returning_addr_sk=[$10], cr_return_amt_inc_tax=[$20]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($10))]) HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) - HiveProject(d_date_sk=[$0], d_year=[CAST(1998):INTEGER]) + HiveProject(d_date_sk=[$0]) HiveFilter(condition=[AND(=($6, 1998), 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_query82.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query82.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query82.q.out index 08d9992..a60312f 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query82.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query82.q.out @@ -44,20 +44,20 @@ CBO PLAN: HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(i_item_id=[$0], i_item_desc=[$1], i_current_price=[$2]) HiveAggregate(group=[{2, 3, 4}]) - HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($6, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_item_sk=[$2]) HiveFilter(condition=[IS NOT NULL($2)]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5], i_manufact_id=[$13]) + HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4], i_current_price=[$5]) HiveFilter(condition=[AND(IN($13, 437, 129, 727, 663), BETWEEN(false, $5, 30, 60), IS NOT NULL($0))]) HiveTableScan(table=[[default, item]], table:alias=[item]) - HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_quantity_on_hand=[$2], d_date_sk=[$3], d_date=[$4]) - HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_quantity_on_hand=[$3]) + HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], d_date_sk=[$2]) + HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(inv_date_sk=[$0], inv_item_sk=[$1]) HiveFilter(condition=[AND(BETWEEN(false, $3, 100, 500), 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]) HiveFilter(condition=[AND(BETWEEN(false, CAST($2):TIMESTAMP(9), 2002-05-30 00:00:00, 2002-07-29 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_query83.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query83.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query83.q.out index d5a3d66..0e61e45 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query83.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query83.q.out @@ -144,10 +144,10 @@ POSTHOOK: Input: default@web_returns POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) - HiveProject(item_id=[$0], sr_item_qty=[$3], sr_dev=[*(/(/(CAST($3):DOUBLE, CAST(+(+($3, $1), $5)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], cr_item_qty=[$1], cr_dev=[*(/(/(CAST($1):DOUBLE, CAST(+(+($3, $1), $5)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], wr_item_qty=[$5], wr_dev=[*(/(/(CAST($5):DOUBLE, CAST(+(+($3, $1), $5)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], average=[/(CAST(+(+($3, $1), $5)):DECIMAL(19, 0), 3)]) - HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_id=[$0], $f1=[$1]) + HiveProject(item_id=[$0], sr_item_qty=[$4], sr_dev=[*(/(/($5, CAST(+(+($4, $1), $7)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], cr_item_qty=[$1], cr_dev=[*(/(/($2, CAST(+(+($4, $1), $7)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], wr_item_qty=[$7], wr_dev=[*(/(/($8, CAST(+(+($4, $1), $7)):DOUBLE), CAST(3):DOUBLE), CAST(100):DOUBLE)], average=[/(CAST(+(+($4, $1), $7)):DECIMAL(19, 0), 3)]) + HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$0], $f1=[$1], CAST=[CAST($1):DOUBLE]) HiveAggregate(group=[{4}], agg#0=[sum($2)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -171,7 +171,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(d_date_sk=[$0], d_date_id=[$1], d_date=[$2], d_month_seq=[$3], d_week_seq=[$4], d_quarter_seq=[$5], d_year=[$6], d_dow=[$7], d_moy=[$8], d_dom=[$9], d_qoy=[$10], d_fy_year=[$11], d_fy_quarter_seq=[$12], d_fy_week_seq=[$13], d_day_name=[$14], d_quarter_name=[$15], d_holiday=[$16], d_weekend=[$17], d_following_holiday=[$18], d_first_dom=[$19], d_last_dom=[$20], d_same_day_ly=[$21], d_same_day_lq=[$22], d_current_day=[$23], d_current_week=[$24], d_current_month=[$25], d_current_quarter=[$26], d_current_year=[$27], BLOCK__OFFSET__INSIDE__FILE=[$28], INPUT__FILE__NAME=[$29], ROW__ID=[$30]) HiveFilter(condition=[AND(IN($2, _UTF-16LE'1998-01-02', _UTF-16LE'1998-10-15', _UTF-16LE'1998-11-10'), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_id=[$0], $f1=[$1]) + HiveProject($f0=[$0], $f1=[$1], CAST=[CAST($1):DOUBLE]) HiveAggregate(group=[{4}], agg#0=[sum($2)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -195,7 +195,7 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(d_date_sk=[$0], d_date_id=[$1], d_date=[$2], d_month_seq=[$3], d_week_seq=[$4], d_quarter_seq=[$5], d_year=[$6], d_dow=[$7], d_moy=[$8], d_dom=[$9], d_qoy=[$10], d_fy_year=[$11], d_fy_quarter_seq=[$12], d_fy_week_seq=[$13], d_day_name=[$14], d_quarter_name=[$15], d_holiday=[$16], d_weekend=[$17], d_following_holiday=[$18], d_first_dom=[$19], d_last_dom=[$20], d_same_day_ly=[$21], d_same_day_lq=[$22], d_current_day=[$23], d_current_week=[$24], d_current_month=[$25], d_current_quarter=[$26], d_current_year=[$27], BLOCK__OFFSET__INSIDE__FILE=[$28], INPUT__FILE__NAME=[$29], ROW__ID=[$30]) HiveFilter(condition=[AND(IN($2, _UTF-16LE'1998-01-02', _UTF-16LE'1998-10-15', _UTF-16LE'1998-11-10'), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(i_item_id=[$0], $f1=[$1]) + HiveProject($f0=[$0], $f1=[$1], CAST=[CAST($1):DOUBLE]) HiveAggregate(group=[{4}], agg#0=[sum($2)]) HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) http://git-wip-us.apache.org/repos/asf/hive/blob/55887646/ql/src/test/results/clientpositive/perf/tez/cbo_query84.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query84.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query84.q.out index de765ab..006f703 100644 --- a/ql/src/test/results/clientpositive/perf/tez/cbo_query84.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query84.q.out @@ -55,7 +55,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveProject(customer_id=[$0], customername=[$1]) HiveSortLimit(sort0=[$2], dir0=[ASC], fetch=[100]) - HiveProject(customer_id=[$2], customername=[||(||($7, _UTF-16LE', '), $6)], c_customer_id=[$2]) + HiveProject(customer_id=[$2], customername=[$6], c_customer_id=[$2]) HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(sr_cdemo_sk=[$4]) @@ -64,21 +64,21 @@ HiveProject(customer_id=[$0], customername=[$1]) HiveProject(cd_demo_sk=[$0]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics]) - HiveProject(c_customer_id=[$0], c_current_cdemo_sk=[$1], c_current_hdemo_sk=[$2], c_current_addr_sk=[$3], c_first_name=[$4], c_last_name=[$5], ca_address_sk=[$6], ca_city=[$7], hd_demo_sk=[$8], hd_income_band_sk=[$9], ib_income_band_sk=[$10], ib_lower_bound=[$11], ib_upper_bound=[$12]) - HiveJoin(condition=[=($8, $2)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($3, $6)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(c_customer_id=[$1], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_name=[$8], c_last_name=[$9]) + HiveProject(c_customer_id=[$0], c_current_cdemo_sk=[$1], c_current_hdemo_sk=[$2], c_current_addr_sk=[$3], ||=[$4], ca_address_sk=[$5], hd_demo_sk=[$6], hd_income_band_sk=[$7], ib_income_band_sk=[$8]) + HiveJoin(condition=[=($6, $2)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $5)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_id=[$1], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], ||=[||(||($9, _UTF-16LE', '), $8)]) HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2), IS NOT NULL($3))]) HiveTableScan(table=[[default, customer]], table:alias=[customer]) - HiveProject(ca_address_sk=[$0], ca_city=[CAST(_UTF-16LE'Hopewell'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveProject(ca_address_sk=[$0]) HiveFilter(condition=[AND(=($6, _UTF-16LE'Hopewell'), IS NOT NULL($0))]) HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) - HiveProject(hd_demo_sk=[$0], hd_income_band_sk=[$1], ib_income_band_sk=[$2], ib_lower_bound=[$3], ib_upper_bound=[$4]) + HiveProject(hd_demo_sk=[$0], hd_income_band_sk=[$1], ib_income_band_sk=[$2]) HiveJoin(condition=[=($2, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(hd_demo_sk=[$0], hd_income_band_sk=[$1]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics]) - HiveProject(ib_income_band_sk=[$0], ib_lower_bound=[$1], ib_upper_bound=[$2]) + HiveProject(ib_income_band_sk=[$0]) HiveFilter(condition=[AND(>=($1, 32287), <=($2, 82287), IS NOT NULL($0))]) HiveTableScan(table=[[default, income_band]], table:alias=[income_band])
