You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by jc...@apache.org on 2018/10/22 02:10:34 UTC
[29/51] [partial] hive git commit: HIVE-20718: Add perf cli driver
with constraints (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
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])
+