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:41 UTC
[36/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_query14.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
new file mode 100644
index 0000000..457cdce
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query14.q.out
@@ -0,0 +1,612 @@
+Warning: Shuffle Join MERGEJOIN[1431][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 5' is a cross product
+Warning: Shuffle Join MERGEJOIN[1443][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
+Warning: Shuffle Join MERGEJOIN[1433][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 13' is a cross product
+Warning: Shuffle Join MERGEJOIN[1456][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 14' is a cross product
+Warning: Shuffle Join MERGEJOIN[1435][tables = [$hdt$_2, $hdt$_3]] in Stage 'Reducer 18' is a cross product
+Warning: Shuffle Join MERGEJOIN[1469][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 19' is a cross product
+PREHOOK: query: explain cbo
+with cross_items as
+ (select i_item_sk ss_item_sk
+ from item,
+ (select iss.i_brand_id brand_id
+ ,iss.i_class_id class_id
+ ,iss.i_category_id category_id
+ from store_sales
+ ,item iss
+ ,date_dim d1
+ where ss_item_sk = iss.i_item_sk
+ and ss_sold_date_sk = d1.d_date_sk
+ and d1.d_year between 1999 AND 1999 + 2
+ intersect
+ select ics.i_brand_id
+ ,ics.i_class_id
+ ,ics.i_category_id
+ from catalog_sales
+ ,item ics
+ ,date_dim d2
+ where cs_item_sk = ics.i_item_sk
+ and cs_sold_date_sk = d2.d_date_sk
+ and d2.d_year between 1999 AND 1999 + 2
+ intersect
+ select iws.i_brand_id
+ ,iws.i_class_id
+ ,iws.i_category_id
+ from web_sales
+ ,item iws
+ ,date_dim d3
+ where ws_item_sk = iws.i_item_sk
+ and ws_sold_date_sk = d3.d_date_sk
+ and d3.d_year between 1999 AND 1999 + 2) x
+ where i_brand_id = brand_id
+ and i_class_id = class_id
+ and i_category_id = category_id
+),
+ avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001
+ union all
+ select cs_quantity quantity
+ ,cs_list_price list_price
+ from catalog_sales
+ ,date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_year between 1998 and 1998 + 2
+ union all
+ select ws_quantity quantity
+ ,ws_list_price list_price
+ from web_sales
+ ,date_dim
+ where ws_sold_date_sk = d_date_sk
+ and d_year between 1998 and 1998 + 2) x)
+ select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales)
+ from(
+ select 'store' channel, i_brand_id,i_class_id
+ ,i_category_id,sum(ss_quantity*ss_list_price) sales
+ , count(*) number_sales
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk in (select ss_item_sk from cross_items)
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)
+ union all
+ select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk in (select ss_item_sk from cross_items)
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
+ union all
+ select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk in (select ss_item_sk from cross_items)
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)
+ ) y
+ group by rollup (channel, i_brand_id,i_class_id,i_category_id)
+ order by channel,i_brand_id,i_class_id,i_category_id
+ 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 cross_items as
+ (select i_item_sk ss_item_sk
+ from item,
+ (select iss.i_brand_id brand_id
+ ,iss.i_class_id class_id
+ ,iss.i_category_id category_id
+ from store_sales
+ ,item iss
+ ,date_dim d1
+ where ss_item_sk = iss.i_item_sk
+ and ss_sold_date_sk = d1.d_date_sk
+ and d1.d_year between 1999 AND 1999 + 2
+ intersect
+ select ics.i_brand_id
+ ,ics.i_class_id
+ ,ics.i_category_id
+ from catalog_sales
+ ,item ics
+ ,date_dim d2
+ where cs_item_sk = ics.i_item_sk
+ and cs_sold_date_sk = d2.d_date_sk
+ and d2.d_year between 1999 AND 1999 + 2
+ intersect
+ select iws.i_brand_id
+ ,iws.i_class_id
+ ,iws.i_category_id
+ from web_sales
+ ,item iws
+ ,date_dim d3
+ where ws_item_sk = iws.i_item_sk
+ and ws_sold_date_sk = d3.d_date_sk
+ and d3.d_year between 1999 AND 1999 + 2) x
+ where i_brand_id = brand_id
+ and i_class_id = class_id
+ and i_category_id = category_id
+),
+ avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 1999 and 2001
+ union all
+ select cs_quantity quantity
+ ,cs_list_price list_price
+ from catalog_sales
+ ,date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_year between 1998 and 1998 + 2
+ union all
+ select ws_quantity quantity
+ ,ws_list_price list_price
+ from web_sales
+ ,date_dim
+ where ws_sold_date_sk = d_date_sk
+ and d_year between 1998 and 1998 + 2) x)
+ select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales)
+ from(
+ select 'store' channel, i_brand_id,i_class_id
+ ,i_category_id,sum(ss_quantity*ss_list_price) sales
+ , count(*) number_sales
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk in (select ss_item_sk from cross_items)
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)
+ union all
+ select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk in (select ss_item_sk from cross_items)
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
+ union all
+ select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk in (select ss_item_sk from cross_items)
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 1998+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)
+ ) y
+ group by rollup (channel, i_brand_id,i_class_id,i_category_id)
+ order by channel,i_brand_id,i_class_id,i_category_id
+ 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], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
+ HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], $f4=[$4], $f5=[$5])
+ HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($4)], agg#1=[sum($5)])
+ HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5])
+ HiveUnion(all=[true])
+ HiveProject(channel=[_UTF-16LE'store'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
+ HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
+ HiveProject($f0=[$2], $f1=[$3], $f2=[$4], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_item_sk=[$0])
+ HiveJoin(condition=[AND(AND(=($1, $4), =($2, $5)), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2])
+ HiveFilter(condition=[=($3, 3)])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveUnion(all=[true])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iss])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[ics])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iws])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
+ 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()])
+ HiveProject
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{}], agg#0=[count($0)])
+ HiveProject(quantity=[$0], list_price=[$1])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(channel=[_UTF-16LE'catalog'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
+ HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
+ HiveProject($f0=[$2], $f1=[$3], $f2=[$4], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_item_sk=[$0])
+ HiveJoin(condition=[AND(AND(=($1, $4), =($2, $5)), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2])
+ HiveFilter(condition=[=($3, 3)])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveUnion(all=[true])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iss])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[ics])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iws])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
+ 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()])
+ HiveProject
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{}], agg#0=[count($0)])
+ HiveProject(quantity=[$0], list_price=[$1])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(channel=[_UTF-16LE'web'], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4])
+ HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()])
+ HiveProject($f0=[$2], $f1=[$3], $f2=[$4], $f3=[*(CAST($7):DECIMAL(10, 0), $8)])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_item_sk=[$0])
+ HiveJoin(condition=[AND(AND(=($1, $4), =($2, $5)), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2])
+ HiveFilter(condition=[=($3, 3)])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveUnion(all=[true])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iss])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[ics])
+ HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveAggregate(group=[{4, 5, 6}], agg#0=[count()])
+ HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($9), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, item]], table:alias=[iws])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($6, 2000), =($8, 11))])
+ 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()])
+ HiveProject
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{}], agg#0=[count($0)])
+ HiveProject(quantity=[$0], list_price=[$1])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject($f0=[/($0, $1)])
+ HiveAggregate(group=[{}], agg#0=[sum($0)], agg#1=[count($0)])
+ HiveProject($f0=[*(CAST($0):DECIMAL(10, 0), $1)])
+ HiveUnion(all=[true])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_quantity=[$10], ss_list_price=[$12])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1999, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_quantity=[$18], cs_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(quantity=[$1], list_price=[$2])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_quantity=[$18], ws_list_price=[$20])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[BETWEEN(false, $6, 1998, 2000)])
+ 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_query15.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query15.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query15.q.out
new file mode 100644
index 0000000..b260731
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query15.q.out
@@ -0,0 +1,68 @@
+PREHOOK: query: explain cbo
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip
+ order by ca_zip
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip
+ order by ca_zip
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100])
+ HiveProject(ca_zip=[$0], $f1=[$1])
+ HiveAggregate(group=[{3}], agg#0=[sum($8)])
+ HiveJoin(condition=[AND(=($7, $0), OR($4, $9, $5))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_zip=[$9], IN=[IN(substr($9, 1, 5), _UTF-16LE'85669', _UTF-16LE'86197', _UTF-16LE'88274', _UTF-16LE'83405', _UTF-16LE'86475', _UTF-16LE'85392', _UTF-16LE'85460', _UTF-16LE'80348', _UTF-16LE'81792')], IN3=[IN($8, _UTF-16LE'CA', _UTF-16LE'WA', _UTF-16LE'GA')])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_sales_price=[$2], >=[$3], d_date_sk=[$4])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_sales_price=[$21], >=[>($21, 500)])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($10, 2), =($6, 2000))])
+ 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_query16.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query16.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query16.q.out
new file mode 100644
index 0000000..ddbf80a
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query16.q.out
@@ -0,0 +1,102 @@
+PREHOOK: query: explain cbo
+select
+ count(distinct cs_order_number) as `order count`
+ ,sum(cs_ext_ship_cost) as `total shipping cost`
+ ,sum(cs_net_profit) as `total net profit`
+from
+ catalog_sales cs1
+ ,date_dim
+ ,customer_address
+ ,call_center
+where
+ d_date between '2001-4-01' and
+ (cast('2001-4-01' as date) + 60 days)
+and cs1.cs_ship_date_sk = d_date_sk
+and cs1.cs_ship_addr_sk = ca_address_sk
+and ca_state = 'NY'
+and cs1.cs_call_center_sk = cc_call_center_sk
+and cc_county in ('Ziebach County','Levy County','Huron County','Franklin Parish',
+ 'Daviess County'
+)
+and exists (select *
+ from catalog_sales cs2
+ where cs1.cs_order_number = cs2.cs_order_number
+ and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
+and not exists(select *
+ from catalog_returns cr1
+ where cs1.cs_order_number = cr1.cr_order_number)
+order by count(distinct cs_order_number)
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@call_center
+PREHOOK: Input: default@catalog_returns
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select
+ count(distinct cs_order_number) as `order count`
+ ,sum(cs_ext_ship_cost) as `total shipping cost`
+ ,sum(cs_net_profit) as `total net profit`
+from
+ catalog_sales cs1
+ ,date_dim
+ ,customer_address
+ ,call_center
+where
+ d_date between '2001-4-01' and
+ (cast('2001-4-01' as date) + 60 days)
+and cs1.cs_ship_date_sk = d_date_sk
+and cs1.cs_ship_addr_sk = ca_address_sk
+and ca_state = 'NY'
+and cs1.cs_call_center_sk = cc_call_center_sk
+and cc_county in ('Ziebach County','Levy County','Huron County','Franklin Parish',
+ 'Daviess County'
+)
+and exists (select *
+ from catalog_sales cs2
+ where cs1.cs_order_number = cs2.cs_order_number
+ and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
+and not exists(select *
+ from catalog_returns cr1
+ where cs1.cs_order_number = cr1.cr_order_number)
+order by count(distinct cs_order_number)
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@call_center
+POSTHOOK: Input: default@catalog_returns
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveProject(order count=[$0], total shipping cost=[$1], total net profit=[$2])
+ HiveSortLimit(sort0=[$3], dir0=[ASC], fetch=[100])
+ HiveProject(order count=[$0], total shipping cost=[$1], total net profit=[$2], (tok_functiondi count (tok_table_or_col cs_order_number))=[$0])
+ HiveAggregate(group=[{}], agg#0=[count(DISTINCT $4)], agg#1=[sum($5)], agg#2=[sum($6)])
+ HiveFilter(condition=[IS NULL($14)])
+ HiveJoin(condition=[=($4, $13)], joinType=[left], algorithm=[none], cost=[not available])
+ HiveSemiJoin(condition=[AND(<>($3, $13), =($4, $14))], joinType=[inner])
+ HiveProject(cs_ship_date_sk=[$2], cs_ship_addr_sk=[$3], cs_call_center_sk=[$4], cs_warehouse_sk=[$5], cs_order_number=[$6], cs_ext_ship_cost=[$7], cs_net_profit=[$8], d_date_sk=[$9], d_date=[$10], ca_address_sk=[$0], ca_state=[$1], cc_call_center_sk=[$11], cc_county=[$12])
+ HiveJoin(condition=[=($4, $11)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_state=[CAST(_UTF-16LE'NY'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"])
+ HiveFilter(condition=[=($8, _UTF-16LE'NY')])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($0, $7)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_ship_date_sk=[$2], cs_ship_addr_sk=[$10], cs_call_center_sk=[$11], cs_warehouse_sk=[$14], cs_order_number=[$17], cs_ext_ship_cost=[$28], cs_net_profit=[$33])
+ HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($10), IS NOT NULL($11))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[cs1])
+ HiveProject(d_date_sk=[$0], d_date=[$2])
+ HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 2001-04-01 00:00:00, 2001-05-31 00:00:00)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cc_call_center_sk=[$0], cc_county=[$25])
+ HiveFilter(condition=[IN($25, _UTF-16LE'Ziebach County', _UTF-16LE'Levy County', _UTF-16LE'Huron County', _UTF-16LE'Franklin Parish', _UTF-16LE'Daviess County')])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
+ HiveProject(cs_warehouse_sk=[$14], cs_order_number=[$17])
+ HiveFilter(condition=[IS NOT NULL($14)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[cs2])
+ HiveProject(cr_order_number0=[$16], $f1=[true])
+ HiveTableScan(table=[[default, catalog_returns]], table:alias=[cr1])
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
new file mode 100644
index 0000000..d100578
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
@@ -0,0 +1,139 @@
+PREHOOK: query: explain cbo
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as_store_returns_quantitycount
+ ,avg(sr_return_quantity) as_store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2000Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_returns
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as_store_returns_quantitycount
+ ,avg(sr_return_quantity) as_store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2000Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_returns
+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(i_item_id=[$0], i_item_desc=[$1], s_state=[$2], store_sales_quantitycount=[$3], store_sales_quantityave=[/(CAST($4):DOUBLE, $3)], store_sales_quantitystdev=[POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5)], store_sales_quantitycov=[/(POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5), /(CAST($4):DOUBLE, $3))], as_store_returns_quantitycount=[$7], as_store_returns_quantityave=[/(CAST($8):DOUBLE, $7)], as_store_returns_quantitystdev=[POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5)], store_returns_quantitycov=[/(POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5), /(CAST($8):DOUBLE, $7))], catalog_sales_quantitycount=[$11], catalog_sales_quantityave=[/(CAST($12):DOUBLE, $11)], catalog_sales_quantitystdev=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))], catalog_sales_quantitycov=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($
11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)], agg#1=[sum($3)], agg#2=[sum($7)], agg#3=[sum($6)], agg#4=[count($4)], agg#5=[sum($4)], agg#6=[sum($9)], agg#7=[sum($8)], agg#8=[count($5)], agg#9=[sum($5)], agg#10=[sum($11)], agg#11=[sum($10)])
+ HiveProject($f0=[$8], $f1=[$9], $f2=[$22], $f3=[$5], $f4=[$19], $f5=[$13], $f30=[CAST($5):DOUBLE], $f7=[*(CAST($5):DOUBLE, CAST($5):DOUBLE)], $f40=[CAST($19):DOUBLE], $f9=[*(CAST($19):DOUBLE, CAST($19):DOUBLE)], $f50=[CAST($13):DOUBLE], $f11=[*(CAST($13):DOUBLE, CAST($13):DOUBLE)])
+ HiveJoin(condition=[=($21, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(AND(=($2, $17), =($1, $16)), =($4, $18))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_quantity=[$10])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[=($15, _UTF-16LE'2000Q1')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_quantity=[$3], d_date_sk=[$4], sr_returned_date_sk=[$5], sr_item_sk=[$6], sr_customer_sk=[$7], sr_ticket_number=[$8], sr_return_quantity=[$9], d_date_sk0=[$10])
+ HiveJoin(condition=[AND(=($7, $1), =($6, $2))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_quantity=[$18])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_return_quantity=[$4], d_date_sk=[$5])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_customer_sk=[$3], sr_ticket_number=[$9], sr_return_quantity=[$10])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(s_store_sk=[$0], s_state=[$24])
+ 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_query18.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query18.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query18.q.out
new file mode 100644
index 0000000..72c7628
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query18.q.out
@@ -0,0 +1,111 @@
+PREHOOK: query: explain cbo
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as numeric(12,2))) agg1,
+ avg( cast(cs_list_price as numeric(12,2))) agg2,
+ avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
+ avg( cast(cs_sales_price as numeric(12,2))) agg4,
+ avg( cast(cs_net_profit as numeric(12,2))) agg5,
+ avg( cast(c_birth_year as numeric(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@customer_demographics
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as numeric(12,2))) agg1,
+ avg( cast(cs_list_price as numeric(12,2))) agg2,
+ avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
+ avg( cast(cs_sales_price as numeric(12,2))) agg4,
+ avg( cast(cs_net_profit as numeric(12,2))) agg5,
+ avg( cast(c_birth_year as numeric(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@customer_demographics
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$3], sort3=[$0], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
+ HiveProject($f0=[$3], $f1=[$2], $f2=[$1], $f3=[$0], $f4=[/($4, $5)], $f5=[/($6, $7)], $f6=[/($8, $9)], $f7=[/($10, $11)], $f8=[/($12, $13)], $f9=[/($14, $15)], $f10=[/($16, $17)])
+ HiveAggregate(group=[{5, 6, 7, 10}], groups=[[{5, 6, 7, 10}, {6, 7, 10}, {7, 10}, {10}, {}]], agg#0=[sum($15)], agg#1=[count($15)], agg#2=[sum($16)], agg#3=[count($16)], agg#4=[sum($17)], agg#5=[count($17)], agg#6=[sum($18)], agg#7=[count($18)], agg#8=[sum($19)], agg#9=[count($19)], agg#10=[sum($3)], agg#11=[count($3)], agg#12=[sum($22)], agg#13=[count($22)])
+ HiveJoin(condition=[=($12, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_addr_sk=[$4], CAST=[CAST($13):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(IN($12, 9, 5, 12, 4, 1, 10), IS NOT NULL($2), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8], ca_country=[$10])
+ HiveFilter(condition=[IN($8, _UTF-16LE'ND', _UTF-16LE'WI', _UTF-16LE'AL', _UTF-16LE'NC', _UTF-16LE'OK', _UTF-16LE'MS', _UTF-16LE'TN')])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(cd_demo_sk=[$0])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], cs_sold_date_sk=[$2], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$5], CAST=[$6], CAST5=[$7], CAST6=[$8], CAST7=[$9], CAST8=[$10], d_date_sk=[$11], cd_demo_sk=[$12], CAST0=[$13])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[=($2, $10)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_bill_cdemo_sk=[$4], cs_item_sk=[$15], CAST=[CAST($18):DECIMAL(12, 2)], CAST5=[CAST($20):DECIMAL(12, 2)], CAST6=[CAST($27):DECIMAL(12, 2)], CAST7=[CAST($21):DECIMAL(12, 2)], CAST8=[CAST($33):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[=($6, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(cd_demo_sk=[$0], CAST=[CAST($6):DECIMAL(12, 2)])
+ HiveFilter(condition=[AND(=($1, _UTF-16LE'M'), =($3, _UTF-16LE'College'))])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query19.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query19.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query19.q.out
new file mode 100644
index 0000000..0027cf4
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query19.q.out
@@ -0,0 +1,90 @@
+PREHOOK: query: explain cbo
+select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item,customer,customer_address,store
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=7
+ and d_moy=11
+ and d_year=1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip,1,5) <> substr(s_zip,1,5)
+ and ss_store_sk = s_store_sk
+ group by i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ order by ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+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
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item,customer,customer_address,store
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=7
+ and d_moy=11
+ and d_year=1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip,1,5) <> substr(s_zip,1,5)
+ and ss_store_sk = s_store_sk
+ group by i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ order by ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+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
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveProject(brand_id=[$0], brand=[$1], i_manufact_id=[$2], i_manufact=[$3], ext_price=[$4])
+ HiveSortLimit(sort0=[$4], sort1=[$5], sort2=[$6], sort3=[$2], sort4=[$3], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100])
+ HiveProject(brand_id=[$0], brand=[$1], i_manufact_id=[$2], i_manufact=[$3], ext_price=[$4], (tok_table_or_col i_brand)=[$1], (tok_table_or_col i_brand_id)=[$0])
+ HiveAggregate(group=[{11, 12, 13, 14}], agg#0=[sum($8)])
+ HiveJoin(condition=[AND(<>($3, $16), =($7, $15))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], substr=[substr($9, 1, 5)])
+ 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], i_item_sk=[$6], i_brand_id=[$7], i_brand=[$8], i_manufact_id=[$9], i_manufact=[$10])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ext_sales_price=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($3), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($8, 11), =($6, 1999))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$7], i_brand=[$8], i_manufact_id=[$13], i_manufact=[$14])
+ HiveFilter(condition=[=($20, 7)])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(s_store_sk=[$0], substr=[substr($25, 1, 5)])
+ 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_query2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query2.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query2.q.out
new file mode 100644
index 0000000..c245b9b
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query2.q.out
@@ -0,0 +1,170 @@
+PREHOOK: query: explain cbo
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales) x
+ union all
+ (select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales)),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,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 wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,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 wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain cbo
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales) x
+ union all
+ (select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales)),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,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 wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,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 wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+CBO PLAN:
+HiveSortLimit(sort0=[$0], dir0=[ASC])
+ HiveProject(d_week_seq1=[$0], _o__c1=[round(/($1, $9), 2)], _o__c2=[round(/($2, $10), 2)], _o__c3=[round(/($3, $11), 2)], _o__c4=[round(/($4, $12), 2)], _o__c5=[round(/($5, $13), 2)], _o__c6=[round(/($6, $14), 2)], _o__c7=[round(/($7, $15), 2)])
+ HiveJoin(condition=[=($0, $16)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7])
+ HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)], agg#4=[sum($5)], agg#5=[sum($6)], agg#6=[sum($7)])
+ HiveProject($f0=[$3], $f1=[CASE($4, $1, null)], $f2=[CASE($5, $1, null)], $f3=[CASE($6, $1, null)], $f4=[CASE($7, $1, null)], $f5=[CASE($8, $1, null)], $f6=[CASE($9, $1, null)], $f7=[CASE($10, $1, null)])
+ HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$1])
+ HiveUnion(all=[true])
+ HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$23])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(cs_sold_date_sk=[$0], cs_ext_sales_price=[$23])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], ==[=($14, _UTF-16LE'Sunday')], =3=[=($14, _UTF-16LE'Monday')], =4=[=($14, _UTF-16LE'Tuesday')], =5=[=($14, _UTF-16LE'Wednesday')], =6=[=($14, _UTF-16LE'Thursday')], =7=[=($14, _UTF-16LE'Friday')], =8=[=($14, _UTF-16LE'Saturday')])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_week_seq=[$4])
+ HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(sun_sales2=[$1], mon_sales2=[$2], tue_sales2=[$3], wed_sales2=[$4], thu_sales2=[$5], fri_sales2=[$6], sat_sales2=[$7], -=[-($0, 53)])
+ HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7])
+ HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)], agg#3=[sum($4)], agg#4=[sum($5)], agg#5=[sum($6)], agg#6=[sum($7)])
+ HiveProject($f0=[$3], $f1=[CASE($4, $1, null)], $f2=[CASE($5, $1, null)], $f3=[CASE($6, $1, null)], $f4=[CASE($7, $1, null)], $f5=[CASE($8, $1, null)], $f6=[CASE($9, $1, null)], $f7=[CASE($10, $1, null)])
+ HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$1])
+ HiveUnion(all=[true])
+ HiveProject(ws_sold_date_sk=[$0], ws_ext_sales_price=[$23])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(cs_sold_date_sk=[$0], cs_ext_sales_price=[$23])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], ==[=($14, _UTF-16LE'Sunday')], =3=[=($14, _UTF-16LE'Monday')], =4=[=($14, _UTF-16LE'Tuesday')], =5=[=($14, _UTF-16LE'Wednesday')], =6=[=($14, _UTF-16LE'Thursday')], =7=[=($14, _UTF-16LE'Friday')], =8=[=($14, _UTF-16LE'Saturday')])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_week_seq=[$4])
+ HiveFilter(condition=[AND(=($6, 2002), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+