http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query77.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query77.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query77.q.out new file mode 100644 index 0000000..0ac295b --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query77.q.out @@ -0,0 +1,304 @@ +Warning: Shuffle Join MERGEJOIN[225][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 14' is a cross product +PREHOOK: query: explain cbo +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + group by cs_call_center_sk + ), + cr as + (select + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(returns, 0) as returns + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , returns + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(returns, 0) returns + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_page +PREHOOK: Input: default@web_returns +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + group by cs_call_center_sk + ), + cr as + (select + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(returns, 0) as returns + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , returns + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(returns, 0) returns + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_page +POSTHOOK: Input: default@web_returns +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) + HiveProject(channel=[$0], id=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($4)]) + HiveProject(channel=[$0], id=[$1], sales=[$2], returns=[$3], profit=[$4]) + HiveUnion(all=[true]) + 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(ss_store_sk=[$0], $f1=[$1], $f2=[$2]) + HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)]) + HiveProject(ss_store_sk=[CAST($1):INTEGER NOT NULL], ss_ext_sales_price=[$2], ss_net_profit=[$3]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(sr_store_sk=[$0], $f1=[$1], $f2=[$2]) + HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)]) + HiveProject(sr_store_sk=[CAST($1):INTEGER NOT NULL], sr_return_amt=[$2], sr_net_loss=[$3]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(channel=[_UTF-16LE'catalog channel'], id=[$0], sales=[$1], returns=[$3], profit=[-($2, $4)]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cs_call_center_sk=[$0], $f1=[$1], $f2=[$2]) + HiveAggregate(group=[{1}], agg#0=[sum($2)], agg#1=[sum($3)]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject($f0=[$0], $f1=[$1]) + HiveAggregate(group=[{}], agg#0=[sum($1)], agg#1=[sum($2)]) + HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + 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))]) + HiveJoin(condition=[=($0, $3)], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(ws_web_page_sk=[$0], $f1=[$1], $f2=[$2]) + HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)]) + HiveProject(ws_web_page_sk=[CAST($1):INTEGER NOT NULL], ws_ext_sales_price=[$2], ws_net_profit=[$3]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(wr_web_page_sk=[$0], $f1=[$1], $f2=[$2]) + HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)]) + HiveProject(wr_web_page_sk=[CAST($1):INTEGER NOT NULL], wr_return_amt=[$2], wr_net_loss=[$3]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) +
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query78.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query78.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query78.q.out new file mode 100644 index 0000000..de1b133 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query78.q.out @@ -0,0 +1,183 @@ +PREHOOK: query: explain cbo +with ws as + (select d_year AS ws_sold_year, ws_item_sk, + ws_bill_customer_sk ws_customer_sk, + sum(ws_quantity) ws_qty, + sum(ws_wholesale_cost) ws_wc, + sum(ws_sales_price) ws_sp + from web_sales + left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk + join date_dim on ws_sold_date_sk = d_date_sk + where wr_order_number is null + group by d_year, ws_item_sk, ws_bill_customer_sk + ), +cs as + (select d_year AS cs_sold_year, cs_item_sk, + cs_bill_customer_sk cs_customer_sk, + sum(cs_quantity) cs_qty, + sum(cs_wholesale_cost) cs_wc, + sum(cs_sales_price) cs_sp + from catalog_sales + left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk + join date_dim on cs_sold_date_sk = d_date_sk + where cr_order_number is null + group by d_year, cs_item_sk, cs_bill_customer_sk + ), +ss as + (select d_year AS ss_sold_year, ss_item_sk, + ss_customer_sk, + sum(ss_quantity) ss_qty, + sum(ss_wholesale_cost) ss_wc, + sum(ss_sales_price) ss_sp + from store_sales + left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk + join date_dim on ss_sold_date_sk = d_date_sk + where sr_ticket_number is null + group by d_year, ss_item_sk, ss_customer_sk + ) + select +ss_sold_year, ss_item_sk, ss_customer_sk, +round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio, +ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, +coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty, +coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost, +coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price +from ss +left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) +left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk) +where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000 +order by + ss_sold_year, ss_item_sk, ss_customer_sk, + ss_qty desc, ss_wc desc, ss_sp desc, + other_chan_qty, + other_chan_wholesale_cost, + other_chan_sales_price, + round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_returns +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +with ws as + (select d_year AS ws_sold_year, ws_item_sk, + ws_bill_customer_sk ws_customer_sk, + sum(ws_quantity) ws_qty, + sum(ws_wholesale_cost) ws_wc, + sum(ws_sales_price) ws_sp + from web_sales + left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk + join date_dim on ws_sold_date_sk = d_date_sk + where wr_order_number is null + group by d_year, ws_item_sk, ws_bill_customer_sk + ), +cs as + (select d_year AS cs_sold_year, cs_item_sk, + cs_bill_customer_sk cs_customer_sk, + sum(cs_quantity) cs_qty, + sum(cs_wholesale_cost) cs_wc, + sum(cs_sales_price) cs_sp + from catalog_sales + left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk + join date_dim on cs_sold_date_sk = d_date_sk + where cr_order_number is null + group by d_year, cs_item_sk, cs_bill_customer_sk + ), +ss as + (select d_year AS ss_sold_year, ss_item_sk, + ss_customer_sk, + sum(ss_quantity) ss_qty, + sum(ss_wholesale_cost) ss_wc, + sum(ss_sales_price) ss_sp + from store_sales + left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk + join date_dim on ss_sold_date_sk = d_date_sk + where sr_ticket_number is null + group by d_year, ss_item_sk, ss_customer_sk + ) + select +ss_sold_year, ss_item_sk, ss_customer_sk, +round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio, +ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, +coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty, +coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost, +coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price +from ss +left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) +left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk) +where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000 +order by + ss_sold_year, ss_item_sk, ss_customer_sk, + ss_qty desc, ss_wc desc, ss_sp desc, + other_chan_qty, + other_chan_wholesale_cost, + other_chan_sales_price, + round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_returns +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveSortLimit(fetch=[100]) + HiveProject(ss_sold_year=[CAST(2000):INTEGER], ss_item_sk=[$0], ss_customer_sk=[$1], ratio=[$2], store_qty=[$3], store_wholesale_cost=[$4], store_sales_price=[$5], other_chan_qty=[$6], other_chan_wholesale_cost=[$7], other_chan_sales_price=[$8]) + HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$9], sort3=[$10], sort4=[$11], sort5=[$6], sort6=[$7], sort7=[$8], sort8=[$12], dir0=[ASC], dir1=[ASC], dir2=[DESC-nulls-last], dir3=[DESC-nulls-last], dir4=[DESC-nulls-last], dir5=[ASC], dir6=[ASC], dir7=[ASC], dir8=[ASC]) + HiveProject(ss_item_sk=[$0], ss_customer_sk=[$1], ratio=[round(/(CAST($2):DOUBLE, CAST(CASE(AND(IS NOT NULL($7), IS NOT NULL($11)), +($7, $11), 1)):DOUBLE), 2)], store_qty=[$2], store_wholesale_cost=[$3], store_sales_price=[$4], other_chan_qty=[+(CASE(IS NOT NULL($7), $7, 0), CASE(IS NOT NULL($11), $11, 0))], other_chan_wholesale_cost=[+(CASE(IS NOT NULL($8), $8, 0), CASE(IS NOT NULL($12), $12, 0))], other_chan_sales_price=[+(CASE(IS NOT NULL($9), $9, 0), CASE(IS NOT NULL($13), $13, 0))], ss_qty=[$2], ss_wc=[$3], ss_sp=[$4], (tok_function round (/ (tok_table_or_col ss_qty) (tok_function coalesce (+ (tok_table_or_col ws_qty) (tok_table_or_col cs_qty)) 1)) 2)=[round(/(CAST($2):DOUBLE, CAST(CASE(AND(IS NOT NULL($7), IS NOT NULL($11)), +($7, $11), 1)):DOUBLE), 2)]) + HiveFilter(condition=[CASE(IS NOT NULL($11), >($11, 0), false)]) + HiveJoin(condition=[=($10, $1)], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(ss_item_sk=[$0], ss_customer_sk=[$1], $f2=[$2], $f3=[$3], $f4=[$4], ws_item_sk=[$5], ws_bill_customer_sk=[$6], $f20=[$7], $f30=[$8], $f40=[$9]) + 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=[{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=[=($6, 2000)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + 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]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + 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=[{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=[=($6, 2000)]) + 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_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]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) + 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=[{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=[=($6, 2000)]) + 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_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]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + HiveProject(cr_item_sk=[$2], cr_order_number=[$16]) + HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query79.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query79.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query79.q.out new file mode 100644 index 0000000..552f96b --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query79.q.out @@ -0,0 +1,81 @@ +PREHOOK: query: explain cbo +select + c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit + from + (select ss_ticket_number + ,ss_customer_sk + ,store.s_city + ,sum(ss_coupon_amt) amt + ,sum(ss_net_profit) profit + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0) + and date_dim.d_dow = 1 + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_number_employees between 200 and 295 + group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer + where ss_customer_sk = c_customer_sk + order by c_last_name,c_first_name,substr(s_city,1,30), profit +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@household_demographics +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +select + c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit + from + (select ss_ticket_number + ,ss_customer_sk + ,store.s_city + ,sum(ss_coupon_amt) amt + ,sum(ss_net_profit) profit + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0) + and date_dim.d_dow = 1 + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_number_employees between 200 and 295 + group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer + where ss_customer_sk = c_customer_sk + order by c_last_name,c_first_name,substr(s_city,1,30), profit +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@household_demographics +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +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=[$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]) + HiveTableScan(table=[[default, customer]], table:alias=[customer]) + 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]) + HiveFilter(condition=[AND(IN($6, 1998, 1999, 2000), =($7, 1))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(s_store_sk=[$0], s_city=[$22]) + HiveFilter(condition=[BETWEEN(false, $6, 200, 295)]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(hd_demo_sk=[$0]) + HiveFilter(condition=[OR(=($3, 8), >($4, 0))]) + HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics]) + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query8.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query8.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query8.q.out new file mode 100644 index 0000000..fe14ea3 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query8.q.out @@ -0,0 +1,266 @@ +PREHOOK: query: explain cbo +select s_store_name + ,sum(ss_net_profit) + from store_sales + ,date_dim + ,store, + (select ca_zip + from ( + (SELECT substr(ca_zip,1,5) ca_zip + FROM customer_address + WHERE substr(ca_zip,1,5) IN ( + '89436','30868','65085','22977','83927','77557', + '58429','40697','80614','10502','32779', + '91137','61265','98294','17921','18427', + '21203','59362','87291','84093','21505', + '17184','10866','67898','25797','28055', + '18377','80332','74535','21757','29742', + '90885','29898','17819','40811','25990', + '47513','89531','91068','10391','18846', + '99223','82637','41368','83658','86199', + '81625','26696','89338','88425','32200', + '81427','19053','77471','36610','99823', + '43276','41249','48584','83550','82276', + '18842','78890','14090','38123','40936', + '34425','19850','43286','80072','79188', + '54191','11395','50497','84861','90733', + '21068','57666','37119','25004','57835', + '70067','62878','95806','19303','18840', + '19124','29785','16737','16022','49613', + '89977','68310','60069','98360','48649', + '39050','41793','25002','27413','39736', + '47208','16515','94808','57648','15009', + '80015','42961','63982','21744','71853', + '81087','67468','34175','64008','20261', + '11201','51799','48043','45645','61163', + '48375','36447','57042','21218','41100', + '89951','22745','35851','83326','61125', + '78298','80752','49858','52940','96976', + '63792','11376','53582','18717','90226', + '50530','94203','99447','27670','96577', + '57856','56372','16165','23427','54561', + '28806','44439','22926','30123','61451', + '92397','56979','92309','70873','13355', + '21801','46346','37562','56458','28286', + '47306','99555','69399','26234','47546', + '49661','88601','35943','39936','25632', + '24611','44166','56648','30379','59785', + '11110','14329','93815','52226','71381', + '13842','25612','63294','14664','21077', + '82626','18799','60915','81020','56447', + '76619','11433','13414','42548','92713', + '70467','30884','47484','16072','38936', + '13036','88376','45539','35901','19506', + '65690','73957','71850','49231','14276', + '20005','18384','76615','11635','38177', + '55607','41369','95447','58581','58149', + '91946','33790','76232','75692','95464', + '22246','51061','56692','53121','77209', + '15482','10688','14868','45907','73520', + '72666','25734','17959','24677','66446', + '94627','53535','15560','41967','69297', + '11929','59403','33283','52232','57350', + '43933','40921','36635','10827','71286', + '19736','80619','25251','95042','15526', + '36496','55854','49124','81980','35375', + '49157','63512','28944','14946','36503', + '54010','18767','23969','43905','66979', + '33113','21286','58471','59080','13395', + '79144','70373','67031','38360','26705', + '50906','52406','26066','73146','15884', + '31897','30045','61068','45550','92454', + '13376','14354','19770','22928','97790', + '50723','46081','30202','14410','20223', + '88500','67298','13261','14172','81410', + '93578','83583','46047','94167','82564', + '21156','15799','86709','37931','74703', + '83103','23054','70470','72008','49247', + '91911','69998','20961','70070','63197', + '54853','88191','91830','49521','19454', + '81450','89091','62378','25683','61869', + '51744','36580','85778','36871','48121', + '28810','83712','45486','67393','26935', + '42393','20132','55349','86057','21309', + '80218','10094','11357','48819','39734', + '40758','30432','21204','29467','30214', + '61024','55307','74621','11622','68908', + '33032','52868','99194','99900','84936', + '69036','99149','45013','32895','59004', + '32322','14933','32936','33562','72550', + '27385','58049','58200','16808','21360', + '32961','18586','79307','15492')) + intersect + (select ca_zip + from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + FROM customer_address, customer + WHERE ca_address_sk = c_current_addr_sk and + c_preferred_cust_flag='Y' + group by ca_zip + having count(*) > 10)A1))A2) V1 + where ss_store_sk = s_store_sk + and ss_sold_date_sk = d_date_sk + and d_qoy = 1 and d_year = 2002 + and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + group by s_store_name + order by s_store_name + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@customer_address +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +select s_store_name + ,sum(ss_net_profit) + from store_sales + ,date_dim + ,store, + (select ca_zip + from ( + (SELECT substr(ca_zip,1,5) ca_zip + FROM customer_address + WHERE substr(ca_zip,1,5) IN ( + '89436','30868','65085','22977','83927','77557', + '58429','40697','80614','10502','32779', + '91137','61265','98294','17921','18427', + '21203','59362','87291','84093','21505', + '17184','10866','67898','25797','28055', + '18377','80332','74535','21757','29742', + '90885','29898','17819','40811','25990', + '47513','89531','91068','10391','18846', + '99223','82637','41368','83658','86199', + '81625','26696','89338','88425','32200', + '81427','19053','77471','36610','99823', + '43276','41249','48584','83550','82276', + '18842','78890','14090','38123','40936', + '34425','19850','43286','80072','79188', + '54191','11395','50497','84861','90733', + '21068','57666','37119','25004','57835', + '70067','62878','95806','19303','18840', + '19124','29785','16737','16022','49613', + '89977','68310','60069','98360','48649', + '39050','41793','25002','27413','39736', + '47208','16515','94808','57648','15009', + '80015','42961','63982','21744','71853', + '81087','67468','34175','64008','20261', + '11201','51799','48043','45645','61163', + '48375','36447','57042','21218','41100', + '89951','22745','35851','83326','61125', + '78298','80752','49858','52940','96976', + '63792','11376','53582','18717','90226', + '50530','94203','99447','27670','96577', + '57856','56372','16165','23427','54561', + '28806','44439','22926','30123','61451', + '92397','56979','92309','70873','13355', + '21801','46346','37562','56458','28286', + '47306','99555','69399','26234','47546', + '49661','88601','35943','39936','25632', + '24611','44166','56648','30379','59785', + '11110','14329','93815','52226','71381', + '13842','25612','63294','14664','21077', + '82626','18799','60915','81020','56447', + '76619','11433','13414','42548','92713', + '70467','30884','47484','16072','38936', + '13036','88376','45539','35901','19506', + '65690','73957','71850','49231','14276', + '20005','18384','76615','11635','38177', + '55607','41369','95447','58581','58149', + '91946','33790','76232','75692','95464', + '22246','51061','56692','53121','77209', + '15482','10688','14868','45907','73520', + '72666','25734','17959','24677','66446', + '94627','53535','15560','41967','69297', + '11929','59403','33283','52232','57350', + '43933','40921','36635','10827','71286', + '19736','80619','25251','95042','15526', + '36496','55854','49124','81980','35375', + '49157','63512','28944','14946','36503', + '54010','18767','23969','43905','66979', + '33113','21286','58471','59080','13395', + '79144','70373','67031','38360','26705', + '50906','52406','26066','73146','15884', + '31897','30045','61068','45550','92454', + '13376','14354','19770','22928','97790', + '50723','46081','30202','14410','20223', + '88500','67298','13261','14172','81410', + '93578','83583','46047','94167','82564', + '21156','15799','86709','37931','74703', + '83103','23054','70470','72008','49247', + '91911','69998','20961','70070','63197', + '54853','88191','91830','49521','19454', + '81450','89091','62378','25683','61869', + '51744','36580','85778','36871','48121', + '28810','83712','45486','67393','26935', + '42393','20132','55349','86057','21309', + '80218','10094','11357','48819','39734', + '40758','30432','21204','29467','30214', + '61024','55307','74621','11622','68908', + '33032','52868','99194','99900','84936', + '69036','99149','45013','32895','59004', + '32322','14933','32936','33562','72550', + '27385','58049','58200','16808','21360', + '32961','18586','79307','15492')) + intersect + (select ca_zip + from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + FROM customer_address, customer + WHERE ca_address_sk = c_current_addr_sk and + c_preferred_cust_flag='Y' + group by ca_zip + having count(*) > 10)A1))A2) V1 + where ss_store_sk = s_store_sk + and ss_sold_date_sk = d_date_sk + and d_qoy = 1 and d_year = 2002 + and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + group by s_store_name + order by s_store_name + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@customer_address +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) + HiveProject(s_store_name=[$0], $f1=[$1]) + 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]) + HiveFilter(condition=[AND(=($10, 1), =($6, 2002))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + 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]) + HiveUnion(all=[true]) + HiveProject(ca_zip=[$0], $f1=[$1]) + HiveAggregate(group=[{0}], agg#0=[count()]) + HiveProject(ca_zip=[substr($9, 1, 5)]) + HiveFilter(condition=[AND(IN(substr($9, 1, 5), _UTF-16LE'89436', _UTF-16LE'30868', _UTF-16LE'65085', _UTF-16LE'22977', _UTF-16LE'83927', _UTF-16LE'77557', _UTF-16LE'58429', _UTF-16LE'40697', _UTF-16LE'80614', _UTF-16LE'10502', _UTF-16LE'32779', _UTF-16LE'91137', _UTF-16LE'61265', _UTF-16LE'98294', _UTF-16LE'17921', _UTF-16LE'18427', _UTF-16LE'21203', _UTF-16LE'59362', _UTF-16LE'87291', _UTF-16LE'84093', _UTF-16LE'21505', _UTF-16LE'17184', _UTF-16LE'10866', _UTF-16LE'67898', _UTF-16LE'25797', _UTF-16LE'28055', _UTF-16LE'18377', _UTF-16LE'80332', _UTF-16LE'74535', _UTF-16LE'21757', _UTF-16LE'29742', _UTF-16LE'90885', _UTF-16LE'29898', _UTF-16LE'17819', _UTF-16LE'40811', _UTF-16LE'25990', _UTF-16LE'47513', _UTF-16LE'89531', _UTF-16LE'91068', _UTF-16LE'10391', _UTF-16LE'18846', _UTF-16LE'99223', _UTF-16LE'82637', _UTF-16LE'41368', _UTF-16LE'83658', _UTF-16LE'86199', _UTF-16LE'81625', _UTF-16LE'26696', _UTF-16LE'89338', _UTF-16LE'88425', _UTF-16LE'32200', _UTF -16LE'81427', _UTF-16LE'19053', _UTF-16LE'77471', _UTF-16LE'36610', _UTF-16LE'99823', _UTF-16LE'43276', _UTF-16LE'41249', _UTF-16LE'48584', _UTF-16LE'83550', _UTF-16LE'82276', _UTF-16LE'18842', _UTF-16LE'78890', _UTF-16LE'14090', _UTF-16LE'38123', _UTF-16LE'40936', _UTF-16LE'34425', _UTF-16LE'19850', _UTF-16LE'43286', _UTF-16LE'80072', _UTF-16LE'79188', _UTF-16LE'54191', _UTF-16LE'11395', _UTF-16LE'50497', _UTF-16LE'84861', _UTF-16LE'90733', _UTF-16LE'21068', _UTF-16LE'57666', _UTF-16LE'37119', _UTF-16LE'25004', _UTF-16LE'57835', _UTF-16LE'70067', _UTF-16LE'62878', _UTF-16LE'95806', _UTF-16LE'19303', _UTF-16LE'18840', _UTF-16LE'19124', _UTF-16LE'29785', _UTF-16LE'16737', _UTF-16LE'16022', _UTF-16LE'49613', _UTF-16LE'89977', _UTF-16LE'68310', _UTF-16LE'60069', _UTF-16LE'98360', _UTF-16LE'48649', _UTF-16LE'39050', _UTF-16LE'41793', _UTF-16LE'25002', _UTF-16LE'27413', _UTF-16LE'39736', _UTF-16LE'47208', _UTF-16LE'16515', _UTF-16LE'94808', _UTF-16LE'57648', _UTF-16LE'15009', _UTF-16LE'8 0015', _UTF-16LE'42961', _UTF-16LE'63982', _UTF-16LE'21744', _UTF-16LE'71853', _UTF-16LE'81087', _UTF-16LE'67468', _UTF-16LE'34175', _UTF-16LE'64008', _UTF-16LE'20261', _UTF-16LE'11201', _UTF-16LE'51799', _UTF-16LE'48043', _UTF-16LE'45645', _UTF-16LE'61163', _UTF-16LE'48375', _UTF-16LE'36447', _UTF-16LE'57042', _UTF-16LE'21218', _UTF-16LE'41100', _UTF-16LE'89951', _UTF-16LE'22745', _UTF-16LE'35851', _UTF-16LE'83326', _UTF-16LE'61125', _UTF-16LE'78298', _UTF-16LE'80752', _UTF-16LE'49858', _UTF-16LE'52940', _UTF-16LE'96976', _UTF-16LE'63792', _UTF-16LE'11376', _UTF-16LE'53582', _UTF-16LE'18717', _UTF-16LE'90226', _UTF-16LE'50530', _UTF-16LE'94203', _UTF-16LE'99447', _UTF-16LE'27670', _UTF-16LE'96577', _UTF-16LE'57856', _UTF-16LE'56372', _UTF-16LE'16165', _UTF-16LE'23427', _UTF-16LE'54561', _UTF-16LE'28806', _UTF-16LE'44439', _UTF-16LE'22926', _UTF-16LE'30123', _UTF-16LE'61451', _UTF-16LE'92397', _UTF-16LE'56979', _UTF-16LE'92309', _UTF-16LE'70873', _UTF-16LE'13355', _UTF-16LE'21801', _UTF-16LE'46346', _UTF-16LE'37562', _UTF-16LE'56458', _UTF-16LE'28286', _UTF-16LE'47306', _UTF-16LE'99555', _UTF-16LE'69399', _UTF-16LE'26234', _UTF-16LE'47546', _UTF-16LE'49661', _UTF-16LE'88601', _UTF-16LE'35943', _UTF-16LE'39936', _UTF-16LE'25632', _UTF-16LE'24611', _UTF-16LE'44166', _UTF-16LE'56648', _UTF-16LE'30379', _UTF-16LE'59785', _UTF-16LE'11110', _UTF-16LE'14329', _UTF-16LE'93815', _UTF-16LE'52226', _UTF-16LE'71381', _UTF-16LE'13842', _UTF-16LE'25612', _UTF-16LE'63294', _UTF-16LE'14664', _UTF-16LE'21077', _UTF-16LE'82626', _UTF-16LE'18799', _UTF-16LE'60915', _UTF-16LE'81020', _UTF-16LE'56447', _UTF-16LE'76619', _UTF-16LE'11433', _UTF-16LE'13414', _UTF-16LE'42548', _UTF-16LE'92713', _UTF-16LE'70467', _UTF-16LE'30884', _UTF-16LE'47484', _UTF-16LE'16072', _UTF-16LE'38936', _UTF-16LE'13036', _UTF-16LE'88376', _UTF-16LE'45539', _UTF-16LE'35901', _UTF-16LE'19506', _UTF-16LE'65690', _UTF-16LE'73957', _UTF-16LE'71850', _UTF-16LE'49231', _UTF-16LE'14276', _UTF-16LE'20005', _UTF-16 LE'18384', _UTF-16LE'76615', _UTF-16LE'11635', _UTF-16LE'38177', _UTF-16LE'55607', _UTF-16LE'41369', _UTF-16LE'95447', _UTF-16LE'58581', _UTF-16LE'58149', _UTF-16LE'91946', _UTF-16LE'33790', _UTF-16LE'76232', _UTF-16LE'75692', _UTF-16LE'95464', _UTF-16LE'22246', _UTF-16LE'51061', _UTF-16LE'56692', _UTF-16LE'53121', _UTF-16LE'77209', _UTF-16LE'15482', _UTF-16LE'10688', _UTF-16LE'14868', _UTF-16LE'45907', _UTF-16LE'73520', _UTF-16LE'72666', _UTF-16LE'25734', _UTF-16LE'17959', _UTF-16LE'24677', _UTF-16LE'66446', _UTF-16LE'94627', _UTF-16LE'53535', _UTF-16LE'15560', _UTF-16LE'41967', _UTF-16LE'69297', _UTF-16LE'11929', _UTF-16LE'59403', _UTF-16LE'33283', _UTF-16LE'52232', _UTF-16LE'57350', _UTF-16LE'43933', _UTF-16LE'40921', _UTF-16LE'36635', _UTF-16LE'10827', _UTF-16LE'71286', _UTF-16LE'19736', _UTF-16LE'80619', _UTF-16LE'25251', _UTF-16LE'95042', _UTF-16LE'15526', _UTF-16LE'36496', _UTF-16LE'55854', _UTF-16LE'49124', _UTF-16LE'81980', _UTF-16LE'35375', _UTF-16LE'49157', _UTF-16LE'6351 2', _UTF-16LE'28944', _UTF-16LE'14946', _UTF-16LE'36503', _UTF-16LE'54010', _UTF-16LE'18767', _UTF-16LE'23969', _UTF-16LE'43905', _UTF-16LE'66979', _UTF-16LE'33113', _UTF-16LE'21286', _UTF-16LE'58471', _UTF-16LE'59080', _UTF-16LE'13395', _UTF-16LE'79144', _UTF-16LE'70373', _UTF-16LE'67031', _UTF-16LE'38360', _UTF-16LE'26705', _UTF-16LE'50906', _UTF-16LE'52406', _UTF-16LE'26066', _UTF-16LE'73146', _UTF-16LE'15884', _UTF-16LE'31897', _UTF-16LE'30045', _UTF-16LE'61068', _UTF-16LE'45550', _UTF-16LE'92454', _UTF-16LE'13376', _UTF-16LE'14354', _UTF-16LE'19770', _UTF-16LE'22928', _UTF-16LE'97790', _UTF-16LE'50723', _UTF-16LE'46081', _UTF-16LE'30202', _UTF-16LE'14410', _UTF-16LE'20223', _UTF-16LE'88500', _UTF-16LE'67298', _UTF-16LE'13261', _UTF-16LE'14172', _UTF-16LE'81410', _UTF-16LE'93578', _UTF-16LE'83583', _UTF-16LE'46047', _UTF-16LE'94167', _UTF-16LE'82564', _UTF-16LE'21156', _UTF-16LE'15799', _UTF-16LE'86709', _UTF-16LE'37931', _UTF-16LE'74703', _UTF-16LE'83103', _UTF-16LE'23054', _UT F-16LE'70470', _UTF-16LE'72008', _UTF-16LE'49247', _UTF-16LE'91911', _UTF-16LE'69998', _UTF-16LE'20961', _UTF-16LE'70070', _UTF-16LE'63197', _UTF-16LE'54853', _UTF-16LE'88191', _UTF-16LE'91830', _UTF-16LE'49521', _UTF-16LE'19454', _UTF-16LE'81450', _UTF-16LE'89091', _UTF-16LE'62378', _UTF-16LE'25683', _UTF-16LE'61869', _UTF-16LE'51744', _UTF-16LE'36580', _UTF-16LE'85778', _UTF-16LE'36871', _UTF-16LE'48121', _UTF-16LE'28810', _UTF-16LE'83712', _UTF-16LE'45486', _UTF-16LE'67393', _UTF-16LE'26935', _UTF-16LE'42393', _UTF-16LE'20132', _UTF-16LE'55349', _UTF-16LE'86057', _UTF-16LE'21309', _UTF-16LE'80218', _UTF-16LE'10094', _UTF-16LE'11357', _UTF-16LE'48819', _UTF-16LE'39734', _UTF-16LE'40758', _UTF-16LE'30432', _UTF-16LE'21204', _UTF-16LE'29467', _UTF-16LE'30214', _UTF-16LE'61024', _UTF-16LE'55307', _UTF-16LE'74621', _UTF-16LE'11622', _UTF-16LE'68908', _UTF-16LE'33032', _UTF-16LE'52868', _UTF-16LE'99194', _UTF-16LE'99900', _UTF-16LE'84936', _UTF-16LE'69036', _UTF-16LE'99149', _UTF-16LE' 45013', _UTF-16LE'32895', _UTF-16LE'59004', _UTF-16LE'32322', _UTF-16LE'14933', _UTF-16LE'32936', _UTF-16LE'33562', _UTF-16LE'72550', _UTF-16LE'27385', _UTF-16LE'58049', _UTF-16LE'58200', _UTF-16LE'16808', _UTF-16LE'21360', _UTF-16LE'32961', _UTF-16LE'18586', _UTF-16LE'79307', _UTF-16LE'15492'), IS NOT NULL(substr(substr($9, 1, 5), 1, 2)))]) + HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) + HiveProject(ca_zip=[$0], $f1=[$1]) + HiveAggregate(group=[{0}], agg#0=[count()]) + HiveProject(ca_zip=[substr($0, 1, 5)]) + HiveFilter(condition=[>($1, 10)]) + HiveAggregate(group=[{1}], agg#0=[count()]) + HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ca_address_sk=[$0], ca_zip=[$9]) + HiveFilter(condition=[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]) + 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], substr=[substr($25, 1, 2)]) + HiveFilter(condition=[IS NOT NULL(substr($25, 1, 2))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query80.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query80.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query80.q.out new file mode 100644 index 0000000..a4fac0c --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query80.q.out @@ -0,0 +1,295 @@ +PREHOOK: query: explain cbo +with ssr as + (select s_store_id as store_id, + sum(ss_ext_sales_price) as sales, + sum(coalesce(sr_return_amt, 0)) as returns, + sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit + from store_sales left outer join store_returns on + (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number), + date_dim, + store, + item, + promotion + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + and ss_item_sk = i_item_sk + and i_current_price > 50 + and ss_promo_sk = p_promo_sk + and p_channel_tv = 'N' + group by s_store_id) + , + csr as + (select cp_catalog_page_id as catalog_page_id, + sum(cs_ext_sales_price) as sales, + sum(coalesce(cr_return_amount, 0)) as returns, + sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit + from catalog_sales left outer join catalog_returns on + (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number), + date_dim, + catalog_page, + item, + promotion + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and cs_catalog_page_sk = cp_catalog_page_sk + and cs_item_sk = i_item_sk + and i_current_price > 50 + and cs_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by cp_catalog_page_id) + , + wsr as + (select web_site_id, + sum(ws_ext_sales_price) as sales, + sum(coalesce(wr_return_amt, 0)) as returns, + sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit + from web_sales left outer join web_returns on + (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number), + date_dim, + web_site, + item, + promotion + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_site_sk = web_site_sk + and ws_item_sk = i_item_sk + and i_current_price > 50 + and ws_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by web_site_id) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , 'store' || store_id as id + , sales + , returns + , profit + from ssr + union all + select 'catalog channel' as channel + , 'catalog_page' || catalog_page_id as id + , sales + , returns + , profit + from csr + union all + select 'web channel' as channel + , 'web_site' || web_site_id as id + , sales + , returns + , profit + from wsr + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_page +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@promotion +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_returns +PREHOOK: Input: default@web_sales +PREHOOK: Input: default@web_site +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +with ssr as + (select s_store_id as store_id, + sum(ss_ext_sales_price) as sales, + sum(coalesce(sr_return_amt, 0)) as returns, + sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit + from store_sales left outer join store_returns on + (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number), + date_dim, + store, + item, + promotion + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + and ss_item_sk = i_item_sk + and i_current_price > 50 + and ss_promo_sk = p_promo_sk + and p_channel_tv = 'N' + group by s_store_id) + , + csr as + (select cp_catalog_page_id as catalog_page_id, + sum(cs_ext_sales_price) as sales, + sum(coalesce(cr_return_amount, 0)) as returns, + sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit + from catalog_sales left outer join catalog_returns on + (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number), + date_dim, + catalog_page, + item, + promotion + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and cs_catalog_page_sk = cp_catalog_page_sk + and cs_item_sk = i_item_sk + and i_current_price > 50 + and cs_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by cp_catalog_page_id) + , + wsr as + (select web_site_id, + sum(ws_ext_sales_price) as sales, + sum(coalesce(wr_return_amt, 0)) as returns, + sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit + from web_sales left outer join web_returns on + (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number), + date_dim, + web_site, + item, + promotion + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_site_sk = web_site_sk + and ws_item_sk = i_item_sk + and i_current_price > 50 + and ws_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by web_site_id) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , 'store' || store_id as id + , sales + , returns + , profit + from ssr + union all + select 'catalog channel' as channel + , 'catalog_page' || catalog_page_id as id + , sales + , returns + , profit + from csr + union all + select 'web channel' as channel + , 'web_site' || web_site_id as id + , sales + , returns + , profit + from wsr + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_page +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@promotion +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_returns +POSTHOOK: Input: default@web_sales +POSTHOOK: Input: default@web_site +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) + HiveProject(channel=[$0], id=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($4)]) + HiveProject(channel=[$0], id=[$1], sales=[$2], returns=[$3], profit=[$4]) + 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=[$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]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + 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=[>($5, 50)]) + HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($1, $7), =($4, $8))], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_store_sk=[$7], ss_promo_sk=[$8], ss_ticket_number=[$9], ss_ext_sales_price=[$15], ss_net_profit=[$22]) + HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($8))]) + HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9], sr_return_amt=[$11], sr_net_loss=[$19]) + HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) + HiveProject(d_date_sk=[$0]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(p_promo_sk=[$0]) + HiveFilter(condition=[=($11, _UTF-16LE'N')]) + 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=[$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]) + HiveTableScan(table=[[default, catalog_page]], table:alias=[catalog_page]) + 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=[>($5, 50)]) + HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($2, $7), =($4, $8))], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(cs_sold_date_sk=[$0], cs_catalog_page_sk=[$12], cs_item_sk=[$15], cs_promo_sk=[$16], cs_order_number=[$17], cs_ext_sales_price=[$23], cs_net_profit=[$33]) + HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12), IS NOT NULL($16))]) + HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + HiveProject(cr_item_sk=[$2], cr_order_number=[$16], cr_return_amount=[$18], cr_net_loss=[$26]) + HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns]) + HiveProject(d_date_sk=[$0]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(p_promo_sk=[$0]) + HiveFilter(condition=[=($11, _UTF-16LE'N')]) + 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=[$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=[=($11, _UTF-16LE'N')]) + HiveTableScan(table=[[default, promotion]], table:alias=[promotion]) + HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0]) + HiveFilter(condition=[>($5, 50)]) + HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveJoin(condition=[=($0, $11)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($1, $7), =($4, $8))], joinType=[left], algorithm=[none], cost=[not available]) + HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_web_site_sk=[$13], ws_promo_sk=[$16], ws_order_number=[$17], ws_ext_sales_price=[$23], ws_net_profit=[$33]) + HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($13), IS NOT NULL($16))]) + HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) + HiveProject(wr_item_sk=[$2], wr_order_number=[$13], wr_return_amt=[$15], wr_net_loss=[$23]) + HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns]) + HiveProject(d_date_sk=[$0]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-08-04 00:00:00, 1998-09-03 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(web_site_sk=[$0], web_site_id=[$1]) + HiveTableScan(table=[[default, web_site]], table:alias=[web_site]) + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query81.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query81.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query81.q.out new file mode 100644 index 0000000..9806664 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query81.q.out @@ -0,0 +1,113 @@ +PREHOOK: query: explain cbo +with customer_total_return as + (select cr_returning_customer_sk as ctr_customer_sk + ,ca_state as ctr_state, + sum(cr_return_amt_inc_tax) as ctr_total_return + from catalog_returns + ,date_dim + ,customer_address + where cr_returned_date_sk = d_date_sk + and d_year =1998 + and cr_returning_addr_sk = ca_address_sk + group by cr_returning_customer_sk + ,ca_state ) + select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset + ,ca_location_type,ctr_total_return + from customer_total_return ctr1 + ,customer_address + ,customer + where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 + from customer_total_return ctr2 + where ctr1.ctr_state = ctr2.ctr_state) + and ca_address_sk = c_current_addr_sk + and ca_state = 'IL' + and ctr1.ctr_customer_sk = c_customer_sk + order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset + ,ca_location_type,ctr_total_return + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@customer +PREHOOK: Input: default@customer_address +PREHOOK: Input: default@date_dim +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +with customer_total_return as + (select cr_returning_customer_sk as ctr_customer_sk + ,ca_state as ctr_state, + sum(cr_return_amt_inc_tax) as ctr_total_return + from catalog_returns + ,date_dim + ,customer_address + where cr_returned_date_sk = d_date_sk + and d_year =1998 + and cr_returning_addr_sk = ca_address_sk + group by cr_returning_customer_sk + ,ca_state ) + select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset + ,ca_location_type,ctr_total_return + from customer_total_return ctr1 + ,customer_address + ,customer + where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 + from customer_total_return ctr2 + where ctr1.ctr_state = ctr2.ctr_state) + and ca_address_sk = c_current_addr_sk + and ca_state = 'IL' + and ctr1.ctr_customer_sk = c_customer_sk + order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset + ,ca_location_type,ctr_total_return + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@customer_address +POSTHOOK: Input: default@date_dim +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=[$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=[IS NOT NULL($4)]) + 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_zip=[$9], ca_country=[$10], ca_gmt_offset=[$11], ca_location_type=[$12]) + HiveFilter(condition=[=($8, _UTF-16LE'IL')]) + 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]) + HiveJoin(condition=[AND(=($1, $4), >($2, $3))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cr_returning_customer_sk=[$1], ca_state=[$0], $f2=[$2]) + HiveAggregate(group=[{1, 3}], agg#0=[sum($5)]) + HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ca_address_sk=[$0], ca_state=[$8]) + HiveFilter(condition=[IS NOT NULL($8)]) + HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[=($6, 1998)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(_o__c0=[*(/($1, $2), 1.2)], ctr_state=[$0]) + HiveAggregate(group=[{0}], agg#0=[sum($2)], agg#1=[count($2)]) + HiveProject(ca_state=[$0], cr_returning_customer_sk=[$1], $f2=[$2]) + HiveAggregate(group=[{1, 3}], agg#0=[sum($5)]) + HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ca_address_sk=[$0], ca_state=[$8]) + HiveFilter(condition=[IS NOT NULL($8)]) + HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + 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]) + HiveFilter(condition=[=($6, 1998)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query82.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query82.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query82.q.out new file mode 100644 index 0000000..54c4a99 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query82.q.out @@ -0,0 +1,62 @@ +PREHOOK: query: explain cbo +select i_item_id + ,i_item_desc + ,i_current_price + from item, inventory, date_dim, store_sales + where i_current_price between 30 and 30+30 + and inv_item_sk = i_item_sk + and d_date_sk=inv_date_sk + and d_date between cast('2002-05-30' as date) and (cast('2002-05-30' as date) + 60 days) + and i_manufact_id in (437,129,727,663) + and inv_quantity_on_hand between 100 and 500 + and ss_item_sk = i_item_sk + group by i_item_id,i_item_desc,i_current_price + order by i_item_id + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@inventory +PREHOOK: Input: default@item +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +select i_item_id + ,i_item_desc + ,i_current_price + from item, inventory, date_dim, store_sales + where i_current_price between 30 and 30+30 + and inv_item_sk = i_item_sk + and d_date_sk=inv_date_sk + and d_date between cast('2002-05-30' as date) and (cast('2002-05-30' as date) + 60 days) + and i_manufact_id in (437,129,727,663) + and inv_quantity_on_hand between 100 and 500 + and ss_item_sk = i_item_sk + group by i_item_id,i_item_desc,i_current_price + order by i_item_id + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@inventory +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +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=[=($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]) + 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]) + HiveFilter(condition=[AND(IN($13, 437, 129, 727, 663), BETWEEN(false, $5, 30, 60))]) + HiveTableScan(table=[[default, item]], table:alias=[item]) + 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=[BETWEEN(false, $3, 100, 500)]) + HiveTableScan(table=[[default, inventory]], table:alias=[inventory]) + HiveProject(d_date_sk=[$0]) + HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 2002-05-30 00:00:00, 2002-07-29 00:00:00)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) +
