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:47 UTC
[42/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/cbo_query57.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out
new file mode 100644
index 0000000..e0480e4
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query57.q.out
@@ -0,0 +1,171 @@
+PREHOOK: query: explain cbo
+with v1 as(
+ select i_category, i_brand,
+ cc_name,
+ d_year, d_moy,
+ sum(cs_sales_price) sum_sales,
+ avg(sum(cs_sales_price)) over
+ (partition by i_category, i_brand,
+ cc_name, d_year)
+ avg_monthly_sales,
+ rank() over
+ (partition by i_category, i_brand,
+ cc_name
+ order by d_year, d_moy) rn
+ from item, catalog_sales, date_dim, call_center
+ where cs_item_sk = i_item_sk and
+ cs_sold_date_sk = d_date_sk and
+ cc_call_center_sk= cs_call_center_sk and
+ (
+ d_year = 2000 or
+ ( d_year = 2000-1 and d_moy =12) or
+ ( d_year = 2000+1 and d_moy =1)
+ )
+ group by i_category, i_brand,
+ cc_name , d_year, d_moy),
+ v2 as(
+ select v1.i_category, v1.i_brand
+ ,v1.d_year, v1.d_moy
+ ,v1.avg_monthly_sales
+ ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
+ from v1, v1 v1_lag, v1 v1_lead
+ where v1.i_category = v1_lag.i_category and
+ v1.i_category = v1_lead.i_category and
+ v1.i_brand = v1_lag.i_brand and
+ v1.i_brand = v1_lead.i_brand and
+ v1. cc_name = v1_lag. cc_name and
+ v1. cc_name = v1_lead. cc_name and
+ v1.rn = v1_lag.rn + 1 and
+ v1.rn = v1_lead.rn - 1)
+ select *
+ from v2
+ where d_year = 2000 and
+ avg_monthly_sales > 0 and
+ case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+ order by sum_sales - avg_monthly_sales, 3
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@call_center
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+with v1 as(
+ select i_category, i_brand,
+ cc_name,
+ d_year, d_moy,
+ sum(cs_sales_price) sum_sales,
+ avg(sum(cs_sales_price)) over
+ (partition by i_category, i_brand,
+ cc_name, d_year)
+ avg_monthly_sales,
+ rank() over
+ (partition by i_category, i_brand,
+ cc_name
+ order by d_year, d_moy) rn
+ from item, catalog_sales, date_dim, call_center
+ where cs_item_sk = i_item_sk and
+ cs_sold_date_sk = d_date_sk and
+ cc_call_center_sk= cs_call_center_sk and
+ (
+ d_year = 2000 or
+ ( d_year = 2000-1 and d_moy =12) or
+ ( d_year = 2000+1 and d_moy =1)
+ )
+ group by i_category, i_brand,
+ cc_name , d_year, d_moy),
+ v2 as(
+ select v1.i_category, v1.i_brand
+ ,v1.d_year, v1.d_moy
+ ,v1.avg_monthly_sales
+ ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
+ from v1, v1 v1_lag, v1 v1_lead
+ where v1.i_category = v1_lag.i_category and
+ v1.i_category = v1_lead.i_category and
+ v1.i_brand = v1_lag.i_brand and
+ v1.i_brand = v1_lead.i_brand and
+ v1. cc_name = v1_lag. cc_name and
+ v1. cc_name = v1_lead. cc_name and
+ v1.rn = v1_lag.rn + 1 and
+ v1.rn = v1_lead.rn - 1)
+ select *
+ from v2
+ where d_year = 2000 and
+ avg_monthly_sales > 0 and
+ case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+ order by sum_sales - avg_monthly_sales, 3
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@call_center
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_sales=[$4], sum_sales=[$5], psum=[$6], nsum=[$7])
+ HiveSortLimit(sort0=[$8], sort1=[$2], dir0=[ASC], dir1=[ASC], fetch=[100])
+ HiveProject(i_category=[$10], i_brand=[$11], d_year=[$13], d_moy=[$14], avg_monthly_sales=[$16], sum_sales=[$15], psum=[$8], nsum=[$3], (- (tok_table_or_col sum_sales) (tok_table_or_col avg_monthly_sales))=[-($15, $16)])
+ HiveJoin(condition=[AND(AND(AND(=($10, $0), =($11, $1)), =($12, $2)), =($17, -($4, 1)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], rank_window_1=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
+ HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12), IS NOT NULL($8))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21])
+ HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8])
+ HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cc_call_center_sk=[$0], cc_name=[$6])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($6))])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
+ HiveJoin(condition=[AND(AND(AND(=($5, $0), =($6, $1)), =($7, $2)), =($12, +($4, 1)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], rank_window_1=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
+ HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12), IS NOT NULL($8))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21])
+ HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8])
+ HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cc_call_center_sk=[$0], cc_name=[$6])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($6))])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
+ HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7])
+ HiveFilter(condition=[AND(=($3, 2000), >($6, 0), CASE(>($6, 0), >(/(ABS(-($5, $6)), $6), 0.1), null), IS NOT NULL($7))])
+ HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $1, $0, $4, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5])
+ HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12), IS NOT NULL($8))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21])
+ HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8])
+ HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cc_call_center_sk=[$0], cc_name=[$6])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($6))])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/cbo_query58.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query58.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query58.q.out
new file mode 100644
index 0000000..2504d78
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query58.q.out
@@ -0,0 +1,240 @@
+Warning: Shuffle Join MERGEJOIN[404][tables = [$hdt$_4, $hdt$_5]] in Stage 'Reducer 22' is a cross product
+PREHOOK: query: explain cbo
+with ss_items as
+ (select i_item_id item_id
+ ,sum(ss_ext_sales_price) ss_item_rev
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ss_sold_date_sk = d_date_sk
+ group by i_item_id),
+ cs_items as
+ (select i_item_id item_id
+ ,sum(cs_ext_sales_price) cs_item_rev
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and cs_sold_date_sk = d_date_sk
+ group by i_item_id),
+ ws_items as
+ (select i_item_id item_id
+ ,sum(ws_ext_sales_price) ws_item_rev
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq =(select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ws_sold_date_sk = d_date_sk
+ group by i_item_id)
+ select ss_items.item_id
+ ,ss_item_rev
+ ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev
+ ,cs_item_rev
+ ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev
+ ,ws_item_rev
+ ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev
+ ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
+ from ss_items,cs_items,ws_items
+ where ss_items.item_id=cs_items.item_id
+ and ss_items.item_id=ws_items.item_id
+ and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
+ and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
+ and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
+ and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
+ and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
+ and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
+ order by item_id
+ ,ss_item_rev
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+with ss_items as
+ (select i_item_id item_id
+ ,sum(ss_ext_sales_price) ss_item_rev
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ss_sold_date_sk = d_date_sk
+ group by i_item_id),
+ cs_items as
+ (select i_item_id item_id
+ ,sum(cs_ext_sales_price) cs_item_rev
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and cs_sold_date_sk = d_date_sk
+ group by i_item_id),
+ ws_items as
+ (select i_item_id item_id
+ ,sum(ws_ext_sales_price) ws_item_rev
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq =(select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ws_sold_date_sk = d_date_sk
+ group by i_item_id)
+ select ss_items.item_id
+ ,ss_item_rev
+ ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev
+ ,cs_item_rev
+ ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev
+ ,ws_item_rev
+ ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev
+ ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
+ from ss_items,cs_items,ws_items
+ where ss_items.item_id=cs_items.item_id
+ and ss_items.item_id=ws_items.item_id
+ and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
+ and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
+ and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
+ and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
+ and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
+ and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
+ order by item_id
+ ,ss_item_rev
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
+ HiveProject(item_id=[$0], ss_item_rev=[$3], ss_dev=[*(/(/($3, +(+($3, $1), $5)), CAST(3):DECIMAL(10, 0)), CAST(100):DECIMAL(10, 0))], cs_item_rev=[$1], cs_dev=[*(/(/($1, +(+($3, $1), $5)), CAST(3):DECIMAL(10, 0)), CAST(100):DECIMAL(10, 0))], ws_item_rev=[$5], ws_dev=[*(/(/($5, +(+($3, $1), $5)), CAST(3):DECIMAL(10, 0)), CAST(100):DECIMAL(10, 0))], average=[/(+(+($3, $1), $5), CAST(3):DECIMAL(10, 0))])
+ HiveJoin(condition=[AND(AND(AND(AND(=($0, $4), BETWEEN(false, $3, *(0.9, $5), *(1.1, $5))), BETWEEN(false, $1, *(0.9, $5), *(1.1, $5))), BETWEEN(false, $5, *(0.9, $3), *(1.1, $3))), BETWEEN(false, $5, *(0.9, $1), *(1.1, $1)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(AND(=($2, $0), BETWEEN(false, $3, *(0.9, $1), *(1.1, $1))), BETWEEN(false, $1, *(0.9, $3), *(1.1, $3)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{4}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_ext_sales_price=[$23])
+ HiveFilter(condition=[AND(IS NOT NULL($15), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_date=[$0])
+ HiveAggregate(group=[{0}])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date=[$2], d_week_seq=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveFilter(condition=[=($2, _UTF-16LE'1998-02-19')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_week_seq=[$4])
+ HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{4}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_ext_sales_price=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_date=[$0])
+ HiveAggregate(group=[{0}])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date=[$2], d_week_seq=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveFilter(condition=[=($2, _UTF-16LE'1998-02-19')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_week_seq=[$4])
+ HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{4}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_ext_sales_price=[$23])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_date=[$0])
+ HiveAggregate(group=[{0}])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date=[$2], d_week_seq=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveFilter(condition=[=($2, _UTF-16LE'1998-02-19')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_week_seq=[$4])
+ HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))])
+ 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/cbo_query59.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query59.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query59.q.out
new file mode 100644
index 0000000..bb92a1f
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query59.q.out
@@ -0,0 +1,136 @@
+PREHOOK: query: explain cbo
+with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales1,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185 and 1185 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185+ 12 and 1185 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales1,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185 and 1185 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185+ 12 and 1185 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
+ HiveProject(s_store_name1=[$2], s_store_id1=[$1], d_week_seq1=[$3], _o__c3=[/($5, $16)], _o__c4=[/($6, $17)], _o__c5=[/($7, $7)], _o__c6=[/($8, $18)], _o__c7=[/($9, $19)], _o__c8=[/($10, $20)], _o__c9=[/($11, $21)])
+ HiveJoin(condition=[AND(=($1, $15), =($3, -($14, 52)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(s_store_sk=[$0], s_store_id=[$1], s_store_name=[$5])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveJoin(condition=[=($10, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8])
+ HiveAggregate(group=[{0, 1}], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($4)], agg#3=[sum($5)], agg#4=[sum($6)], agg#5=[sum($7)], agg#6=[sum($8)])
+ HiveProject($f0=[$4], $f1=[$1], $f2=[CASE(=($5, _UTF-16LE'Sunday'), $2, null)], $f3=[CASE(=($5, _UTF-16LE'Monday'), $2, null)], $f4=[CASE(=($5, _UTF-16LE'Tuesday'), $2, null)], $f5=[CASE(=($5, _UTF-16LE'Wednesday'), $2, null)], $f6=[CASE(=($5, _UTF-16LE'Thursday'), $2, null)], $f7=[CASE(=($5, _UTF-16LE'Friday'), $2, null)], $f8=[CASE(=($5, _UTF-16LE'Saturday'), $2, null)])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_store_sk=[$7], ss_sales_price=[$13])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], d_day_name=[$14])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_month_seq=[$3], d_week_seq=[$4])
+ HiveFilter(condition=[AND(BETWEEN(false, $3, 1185, 1196), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d])
+ HiveProject(d_week_seq2=[$2], s_store_id2=[$1], sun_sales2=[$4], mon_sales2=[$5], wed_sales2=[$6], thu_sales2=[$7], fri_sales2=[$8], sat_sales2=[$9])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(s_store_sk=[$0], s_store_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveJoin(condition=[=($9, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7])
+ HiveAggregate(group=[{0, 1}], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($5)], agg#3=[sum($6)], agg#4=[sum($7)], agg#5=[sum($8)])
+ HiveProject($f0=[$4], $f1=[$1], $f2=[CASE(=($5, _UTF-16LE'Sunday'), $2, null)], $f3=[CASE(=($5, _UTF-16LE'Monday'), $2, null)], $f4=[CASE(=($5, _UTF-16LE'Tuesday'), $2, null)], $f5=[CASE(=($5, _UTF-16LE'Wednesday'), $2, null)], $f6=[CASE(=($5, _UTF-16LE'Thursday'), $2, null)], $f7=[CASE(=($5, _UTF-16LE'Friday'), $2, null)], $f8=[CASE(=($5, _UTF-16LE'Saturday'), $2, null)])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_store_sk=[$7], ss_sales_price=[$13])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], d_day_name=[$14])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_month_seq=[$3], d_week_seq=[$4])
+ HiveFilter(condition=[AND(BETWEEN(false, $3, 1197, 1208), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d])
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/cbo_query6.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query6.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query6.q.out
new file mode 100644
index 0000000..02149e7
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query6.q.out
@@ -0,0 +1,109 @@
+Warning: Map Join MAPJOIN[171][bigTable=?] in task 'Reducer 15' is a cross product
+PREHOOK: query: explain cbo
+select a.ca_state state, count(*) cnt
+ from customer_address a
+ ,customer c
+ ,store_sales s
+ ,date_dim d
+ ,item i
+ where a.ca_address_sk = c.c_current_addr_sk
+ and c.c_customer_sk = s.ss_customer_sk
+ and s.ss_sold_date_sk = d.d_date_sk
+ and s.ss_item_sk = i.i_item_sk
+ and d.d_month_seq =
+ (select distinct (d_month_seq)
+ from date_dim
+ where d_year = 2000
+ and d_moy = 2 )
+ and i.i_current_price > 1.2 *
+ (select avg(j.i_current_price)
+ from item j
+ where j.i_category = i.i_category)
+ group by a.ca_state
+ having count(*) >= 10
+ order by cnt
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select a.ca_state state, count(*) cnt
+ from customer_address a
+ ,customer c
+ ,store_sales s
+ ,date_dim d
+ ,item i
+ where a.ca_address_sk = c.c_current_addr_sk
+ and c.c_customer_sk = s.ss_customer_sk
+ and s.ss_sold_date_sk = d.d_date_sk
+ and s.ss_item_sk = i.i_item_sk
+ and d.d_month_seq =
+ (select distinct (d_month_seq)
+ from date_dim
+ where d_year = 2000
+ and d_moy = 2 )
+ and i.i_current_price > 1.2 *
+ (select avg(j.i_current_price)
+ from item j
+ where j.i_category = i.i_category)
+ group by a.ca_state
+ having count(*) >= 10
+ order by cnt
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$1], dir0=[ASC], fetch=[100])
+ HiveProject(ca_state=[$0], $f1=[$1])
+ HiveFilter(condition=[>=($1, 10)])
+ HiveAggregate(group=[{9}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $2)], 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_item_sk=[$2], ss_customer_sk=[$3])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[s])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_month_seq=[$3])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d])
+ HiveProject(d_month_seq=[$0])
+ HiveAggregate(group=[{3}])
+ HiveFilter(condition=[AND(=($6, 2000), =($8, 2), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1], ca_address_sk=[$2], ca_state=[$3])
+ HiveJoin(condition=[=($2, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer]], table:alias=[c])
+ HiveProject(ca_address_sk=[$0], ca_state=[$8])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[a])
+ HiveProject(i_item_sk=[$0], i_current_price=[$1], i_category=[$2], _o__c0=[$3], i_category0=[$4], cnt=[$5])
+ HiveJoin(condition=[AND(=($4, $2), >($1, *(1.2, CAST($3):DECIMAL(16, 6))))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_current_price=[$5], i_category=[$12])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($12))])
+ HiveTableScan(table=[[default, item]], table:alias=[i])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(_o__c0=[/($1, $2)], i_category=[$0])
+ HiveAggregate(group=[{12}], agg#0=[sum($5)], agg#1=[count($5)])
+ HiveFilter(condition=[IS NOT NULL($12)])
+ HiveTableScan(table=[[default, item]], table:alias=[j])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveProject(d_month_seq=[$0])
+ HiveAggregate(group=[{3}])
+ HiveFilter(condition=[AND(=($6, 2000), =($8, 2))])
+ 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/cbo_query60.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query60.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query60.q.out
new file mode 100644
index 0000000..81dfec7
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query60.q.out
@@ -0,0 +1,244 @@
+PREHOOK: query: explain cbo
+with ss as (
+ select
+ i_item_id,sum(ss_ext_sales_price) total_sales
+ from
+ store_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and ss_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id),
+ cs as (
+ select
+ i_item_id,sum(cs_ext_sales_price) total_sales
+ from
+ catalog_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and cs_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id),
+ ws as (
+ select
+ i_item_id,sum(ws_ext_sales_price) total_sales
+ from
+ web_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and ws_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id)
+ select
+ i_item_id
+,sum(total_sales) total_sales
+ from (select * from ss
+ union all
+ select * from cs
+ union all
+ select * from ws) tmp1
+ group by i_item_id
+ order by i_item_id
+ ,total_sales
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+with ss as (
+ select
+ i_item_id,sum(ss_ext_sales_price) total_sales
+ from
+ store_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and ss_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id),
+ cs as (
+ select
+ i_item_id,sum(cs_ext_sales_price) total_sales
+ from
+ catalog_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and cs_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id),
+ ws as (
+ select
+ i_item_id,sum(ws_ext_sales_price) total_sales
+ from
+ web_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Children'))
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 1999
+ and d_moy = 9
+ and ws_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -6
+ group by i_item_id)
+ select
+ i_item_id
+,sum(total_sales) total_sales
+ from (select * from ss
+ union all
+ select * from cs
+ union all
+ select * from ws) tmp1
+ group by i_item_id
+ order by i_item_id
+ ,total_sales
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{0}], agg#0=[sum($1)])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveUnion(all=[true])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{1}], agg#0=[sum($8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(i_item_id=[$0])
+ HiveAggregate(group=[{1}])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Children'), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[$1], ss_sold_date_sk=[$2], ss_item_sk=[$3], ss_addr_sk=[$4], ss_ext_sales_price=[$5], d_date_sk=[$6], d_year=[$7], d_moy=[$8])
+ HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[CAST(-6):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($11, -6), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_addr_sk=[$6], ss_ext_sales_price=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($6), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(9):INTEGER])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 9), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{1}], agg#0=[sum($8)])
+ HiveJoin(condition=[=($7, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(i_item_id=[$0])
+ HiveAggregate(group=[{1}])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Children'), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[$1], cs_sold_date_sk=[$2], cs_bill_addr_sk=[$3], cs_item_sk=[$4], cs_ext_sales_price=[$5], d_date_sk=[$6], d_year=[$7], d_moy=[$8])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[CAST(-6):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($11, -6), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_addr_sk=[$6], cs_item_sk=[$15], cs_ext_sales_price=[$23])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($6), IS NOT NULL($15))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(9):INTEGER])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 9), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_id=[$0], $f1=[$1])
+ HiveAggregate(group=[{1}], agg#0=[sum($8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(i_item_id=[$0])
+ HiveAggregate(group=[{1}])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Children'), IS NOT NULL($1))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[$1], ws_sold_date_sk=[$2], ws_item_sk=[$3], ws_bill_addr_sk=[$4], ws_ext_sales_price=[$5], d_date_sk=[$6], d_year=[$7], d_moy=[$8])
+ HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[CAST(-6):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($11, -6), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_bill_addr_sk=[$7], ws_ext_sales_price=[$23])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(9):INTEGER])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 9), IS NOT NULL($0))])
+ 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/cbo_query61.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query61.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query61.q.out
new file mode 100644
index 0000000..c5356df
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query61.q.out
@@ -0,0 +1,164 @@
+Warning: Shuffle Join MERGEJOIN[266][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 5' is a cross product
+PREHOOK: query: explain cbo
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@promotion
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@promotion
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
+ HiveProject(promotions=[$0], total=[$1], _o__c2=[*(/(CAST($0):DECIMAL(15, 4), CAST($1):DECIMAL(15, 4)), CAST(100):DECIMAL(10, 0))])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{}], agg#0=[sum($9)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[CAST(-7):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($11, -7), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_store_sk=[$3], ss_promo_sk=[$4], ss_ext_sales_price=[$5], d_date_sk=[$6], d_year=[$7], d_moy=[$8], i_item_sk=[$9], i_category=[$10], s_store_sk=[$11], s_gmt_offset=[$12], p_promo_sk=[$13], p_channel_dmail=[$14], p_channel_email=[$15], p_channel_tv=[$16])
+ HiveJoin(condition=[=($4, $13)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($3, $11)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_promo_sk=[$8], ss_ext_sales_price=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($8), IS NOT NULL($0), IS NOT NULL($3), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 11), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_category=[CAST(_UTF-16LE'Electronics'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Electronics'), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(s_store_sk=[$0], s_gmt_offset=[CAST(-7):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($27, -7), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(p_promo_sk=[$0], p_channel_dmail=[$8], p_channel_email=[$9], p_channel_tv=[$11])
+ HiveFilter(condition=[AND(OR(=($8, _UTF-16LE'Y'), =($9, _UTF-16LE'Y'), =($11, _UTF-16LE'Y')), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, promotion]], table:alias=[promotion])
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{}], agg#0=[sum($8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_gmt_offset=[CAST(-7):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($11, -7), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_store_sk=[$3], ss_ext_sales_price=[$4], d_date_sk=[$5], d_year=[$6], d_moy=[$7], i_item_sk=[$8], i_category=[$9], s_store_sk=[$10], s_gmt_offset=[$11])
+ HiveJoin(condition=[=($3, $10)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ext_sales_price=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($0), IS NOT NULL($3), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0], d_year=[CAST(1999):INTEGER], d_moy=[CAST(11):INTEGER])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 11), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_category=[CAST(_UTF-16LE'Electronics'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Electronics'), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(s_store_sk=[$0], s_gmt_offset=[CAST(-7):DECIMAL(5, 2)])
+ HiveFilter(condition=[AND(=($27, -7), IS NOT NULL($0))])
+ 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/cbo_query63.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out
new file mode 100644
index 0000000..a080989
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query63.q.out
@@ -0,0 +1,89 @@
+PREHOOK: query: explain cbo
+select *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
+ from item
+ ,store_sales
+ ,date_dim
+ ,store
+ where ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','refernece','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, d_moy) tmp1
+where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
+ from item
+ ,store_sales
+ ,date_dim
+ ,store
+ where ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','refernece','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, d_moy) tmp1
+where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$2], sort2=[$1], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
+ HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$1], avg_window_0=[$2])
+ HiveFilter(condition=[CASE(>($2, 0), >(/(ABS(-($1, $2)), $2), 0.1), null)])
+ HiveProject((tok_table_or_col i_manager_id)=[$0], (tok_function sum (tok_table_or_col ss_sales_price))=[$2], avg_window_0=[avg($2) OVER (PARTITION BY $0 ORDER BY $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)])
+ HiveProject(i_manager_id=[$0], d_moy=[$1], $f2=[$2])
+ HiveAggregate(group=[{9, 12}], agg#0=[sum($4)])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(s_store_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_store_sk=[$7], ss_sales_price=[$13])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(i_item_sk=[$0], i_brand=[$8], i_class=[$10], i_category=[$12], i_manager_id=[$20])
+ HiveFilter(condition=[AND(IN($10, _UTF-16LE'personal', _UTF-16LE'portable', _UTF-16LE'refernece', _UTF-16LE'self-help', _UTF-16LE'accessories', _UTF-16LE'classical', _UTF-16LE'fragrances', _UTF-16LE'pants'), IN($8, _UTF-16LE'scholaramalgamalg #14', _UTF-16LE'scholaramalgamalg #7', _UTF-16LE'exportiunivamalg #9', _UTF-16LE'scholaramalgamalg #9', _UTF-16LE'amalgimporto #1', _UTF-16LE'edu packscholar #1', _UTF-16LE'exportiimporto #1', _UTF-16LE'importoamalg #1'), IN($12, _UTF-16LE'Books', _UTF-16LE'Children', _UTF-16LE'Electronics', _UTF-16LE'Women', _UTF-16LE'Music', _UTF-16LE'Men'), OR(AND(IN($12, _UTF-16LE'Books', _UTF-16LE'Children', _UTF-16LE'Electronics'), IN($10, _UTF-16LE'personal', _UTF-16LE'portable', _UTF-16LE'refernece', _UTF-16LE'self-help'), IN($8, _UTF-16LE'scholaramalgamalg #14', _UTF-16LE'scholaramalgamalg #7', _UTF-16LE'exportiunivamalg #9', _UTF-16LE'scholaramalgamalg #9')), AND(IN($12, _UTF-16LE'Women', _UTF-16LE'Music', _UTF-16LE'Men'), IN($10,
_UTF-16LE'accessories', _UTF-16LE'classical', _UTF-16LE'fragrances', _UTF-16LE'pants'), IN($8, _UTF-16LE'amalgimporto #1', _UTF-16LE'edu packscholar #1', _UTF-16LE'exportiimporto #1', _UTF-16LE'importoamalg #1'))), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0], d_month_seq=[$3], d_moy=[$8])
+ HiveFilter(condition=[AND(IN($3, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+