hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ser...@apache.org
Subject [36/75] [abbrv] [partial] hive git commit: HIVE-20718: Add perf cli driver with constraints (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
Date Fri, 26 Oct 2018 21:11:46 GMT
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])
+


Mime
View raw message