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/11/19 19:45:37 UTC
[14/16] hive git commit: HIVE-20920: Use SQL constraints to improve
join reordering algorithm (II) (Jesus Camacho Rodriguez,
reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query64.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query64.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query64.q.out
index 103b671..4261d06 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query64.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query64.q.out
@@ -267,124 +267,122 @@ HiveProject(product_name=[$0], store_name=[$1], store_zip=[$2], b_street_number=
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$18], dir0=[ASC], dir1=[ASC], dir2=[ASC])
HiveProject(product_name=[$0], store_name=[$2], store_zip=[$3], b_street_number=[$4], b_streen_name=[$5], b_city=[$6], b_zip=[$7], c_street_number=[$8], c_street_name=[$9], c_city=[$10], c_zip=[$11], cnt=[$12], s1=[$13], s2=[$14], s3=[$15], s11=[$20], s21=[$21], s31=[$22], cnt1=[$19])
HiveJoin(condition=[AND(AND(AND(=($1, $16), <=($19, $12)), =($2, $17)), =($3, $18))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject($f0=[$13], $f1=[$12], $f2=[$4], $f3=[$5], $f4=[$0], $f5=[$1], $f6=[$2], $f7=[$3], $f8=[$6], $f9=[$7], $f10=[$8], $f11=[$9], $f15=[$14], $f16=[$15], $f17=[$16], $f18=[$17])
- HiveAggregate(group=[{3, 4, 5, 6, 8, 9, 20, 21, 22, 23, 26, 28, 42, 43}], agg#0=[count()], agg#1=[sum($39)], agg#2=[sum($40)], agg#3=[sum($41)])
- HiveJoin(condition=[AND(=($32, $0), =($38, $1))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
- HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveJoin(condition=[=($34, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$1], $f1=[$0], $f2=[$2], $f3=[$3], $f4=[$6], $f5=[$7], $f6=[$8], $f7=[$9], $f8=[$10], $f9=[$11], $f10=[$12], $f11=[$13], $f15=[$14], $f16=[$15], $f17=[$16], $f18=[$17])
+ HiveAggregate(group=[{19, 20, 24, 25, 29, 31, 37, 38, 39, 40, 42, 43, 44, 45}], agg#0=[count()], agg#1=[sum($16)], agg#2=[sum($17)], agg#3=[sum($18)])
+ HiveJoin(condition=[=($5, $41)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($13, $36)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($3, $34), <>($33, $35))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($11, $32)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $30)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($7, $28)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($4, $27)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($12, $26)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($14, $23)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($9, $0), =($15, $1))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
+ HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
+ HiveJoin(condition=[=($7, $20)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_shipto_date_sk=[$5], c_first_sales_date_sk=[$6])
+ HiveFilter(condition=[AND(IS NOT NULL($6), IS NOT NULL($5), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveJoin(condition=[=($0, $13)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $11)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_wholesale_cost=[$11], ss_list_price=[$12], ss_coupon_amt=[$19])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3), IS NOT NULL($4), IS NOT NULL($8), IS NOT NULL($5), IS NOT NULL($6))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(i_item_sk=[$0], i_product_name=[$21])
+ HiveFilter(condition=[AND(IN($17, _UTF-16LE'maroon', _UTF-16LE'burnished', _UTF-16LE'dim', _UTF-16LE'steel', _UTF-16LE'navajo', _UTF-16LE'chocolate'), BETWEEN(false, $5, 35, 45), BETWEEN(false, $5, 36, 50))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[=($6, 2000)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(cs_item_sk=[$0])
+ HiveFilter(condition=[>($1, *(2, $2))])
+ HiveAggregate(group=[{0}], agg#0=[sum($2)], agg#1=[sum($5)])
+ HiveJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_item_sk=[$15], cs_order_number=[$17], cs_ext_list_price=[$25])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(cr_item_sk=[$2], cr_order_number=[$16], +=[+(+($23, $24), $25)])
+ HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
+ HiveProject(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25])
+ HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($25))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[hd1])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[hd2])
+ HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
HiveTableScan(table=[[default, customer_address]], table:alias=[ad1])
- HiveJoin(condition=[=($30, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25])
- HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($25))])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveJoin(condition=[=($25, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[IS NOT NULL($1)])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[hd1])
- HiveJoin(condition=[=($21, $34)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($3, $0), <>($19, $1))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
- HiveJoin(condition=[=($20, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($4, $14)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($5, $12)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_shipto_date_sk=[$5], c_first_sales_date_sk=[$6])
- HiveFilter(condition=[AND(IS NOT NULL($6), IS NOT NULL($5), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($4))])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
- HiveTableScan(table=[[default, customer_address]], table:alias=[ad2])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[IS NOT NULL($1)])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[hd2])
- HiveProject(d_date_sk=[$0], d_year=[$6])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
- HiveProject(d_date_sk=[$0], d_year=[$6])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$1], ss_sold_date_sk=[$2], ss_item_sk=[$3], ss_customer_sk=[$4], ss_cdemo_sk=[$5], ss_hdemo_sk=[$6], ss_addr_sk=[$7], ss_store_sk=[$8], ss_ticket_number=[$9], ss_wholesale_cost=[$10], ss_list_price=[$11], ss_coupon_amt=[$12], i_item_sk=[$13], i_product_name=[$14], d_date_sk=[$15])
- HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
- HiveJoin(condition=[=($0, $13)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_wholesale_cost=[$11], ss_list_price=[$12], ss_coupon_amt=[$19])
- HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3), IS NOT NULL($4), IS NOT NULL($8), IS NOT NULL($5), IS NOT NULL($6))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(i_item_sk=[$0], i_product_name=[$21])
- HiveFilter(condition=[AND(IN($17, _UTF-16LE'maroon', _UTF-16LE'burnished', _UTF-16LE'dim', _UTF-16LE'steel', _UTF-16LE'navajo', _UTF-16LE'chocolate'), BETWEEN(false, $5, 35, 45), BETWEEN(false, $5, 36, 50))])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[=($6, 2000)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
- HiveProject(cs_item_sk=[$0])
- HiveFilter(condition=[>($1, *(2, $2))])
- HiveAggregate(group=[{0}], agg#0=[sum($2)], agg#1=[sum($5)])
- HiveJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_item_sk=[$15], cs_order_number=[$17], cs_ext_list_price=[$25])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(cr_item_sk=[$2], cr_order_number=[$16], +=[+(+($23, $24), $25)])
- HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
- HiveProject($f1=[$12], $f2=[$4], $f3=[$5], $f15=[$14], $f16=[$15], $f17=[$16], $f18=[$17])
- HiveAggregate(group=[{3, 4, 5, 6, 8, 9, 20, 21, 22, 23, 26, 28, 42, 43}], agg#0=[count()], agg#1=[sum($39)], agg#2=[sum($40)], agg#3=[sum($41)])
- HiveJoin(condition=[AND(=($32, $0), =($38, $1))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
- HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveJoin(condition=[=($34, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[ad2])
+ HiveProject($f1=[$0], $f2=[$2], $f3=[$3], $f15=[$14], $f16=[$15], $f17=[$16], $f18=[$17])
+ HiveAggregate(group=[{19, 20, 24, 25, 29, 31, 37, 38, 39, 40, 42, 43, 44, 45}], agg#0=[count()], agg#1=[sum($16)], agg#2=[sum($17)], agg#3=[sum($18)])
+ HiveJoin(condition=[=($5, $41)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($13, $36)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($3, $34), <>($33, $35))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($11, $32)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $30)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($7, $28)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($4, $27)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($12, $26)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($14, $23)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($9, $0), =($15, $1))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
+ HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
+ HiveJoin(condition=[=($7, $20)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($8, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_shipto_date_sk=[$5], c_first_sales_date_sk=[$6])
+ HiveFilter(condition=[AND(IS NOT NULL($6), IS NOT NULL($5), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveJoin(condition=[=($0, $13)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $11)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_wholesale_cost=[$11], ss_list_price=[$12], ss_coupon_amt=[$19])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3), IS NOT NULL($4), IS NOT NULL($8), IS NOT NULL($5), IS NOT NULL($6))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(i_item_sk=[$0], i_product_name=[$21])
+ HiveFilter(condition=[AND(IN($17, _UTF-16LE'maroon', _UTF-16LE'burnished', _UTF-16LE'dim', _UTF-16LE'steel', _UTF-16LE'navajo', _UTF-16LE'chocolate'), BETWEEN(false, $5, 35, 45), BETWEEN(false, $5, 36, 50))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[=($6, 2001)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(cs_item_sk=[$0])
+ HiveFilter(condition=[>($1, *(2, $2))])
+ HiveAggregate(group=[{0}], agg#0=[sum($2)], agg#1=[sum($5)])
+ HiveJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_item_sk=[$15], cs_order_number=[$17], cs_ext_list_price=[$25])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(cr_item_sk=[$2], cr_order_number=[$16], +=[+(+($23, $24), $25)])
+ HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
+ HiveProject(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25])
+ HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($25))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[hd1])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[hd2])
+ HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(d_date_sk=[$0], d_year=[$6])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
HiveTableScan(table=[[default, customer_address]], table:alias=[ad1])
- HiveJoin(condition=[=($30, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25])
- HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($25))])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveJoin(condition=[=($25, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[IS NOT NULL($1)])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[hd1])
- HiveJoin(condition=[=($21, $34)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($3, $0), <>($19, $1))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd2])
- HiveJoin(condition=[=($20, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($4, $14)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($5, $12)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($3, $6)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_shipto_date_sk=[$5], c_first_sales_date_sk=[$6])
- HiveFilter(condition=[AND(IS NOT NULL($6), IS NOT NULL($5), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($4))])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
- HiveTableScan(table=[[default, customer_address]], table:alias=[ad2])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[IS NOT NULL($1)])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[hd2])
- HiveProject(d_date_sk=[$0], d_year=[$6])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
- HiveProject(d_date_sk=[$0], d_year=[$6])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$1], ss_sold_date_sk=[$2], ss_item_sk=[$3], ss_customer_sk=[$4], ss_cdemo_sk=[$5], ss_hdemo_sk=[$6], ss_addr_sk=[$7], ss_store_sk=[$8], ss_ticket_number=[$9], ss_wholesale_cost=[$10], ss_list_price=[$11], ss_coupon_amt=[$12], i_item_sk=[$13], i_product_name=[$14], d_date_sk=[$15])
- HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
- HiveJoin(condition=[=($0, $13)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_wholesale_cost=[$11], ss_list_price=[$12], ss_coupon_amt=[$19])
- HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3), IS NOT NULL($4), IS NOT NULL($8), IS NOT NULL($5), IS NOT NULL($6))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(i_item_sk=[$0], i_product_name=[$21])
- HiveFilter(condition=[AND(IN($17, _UTF-16LE'maroon', _UTF-16LE'burnished', _UTF-16LE'dim', _UTF-16LE'steel', _UTF-16LE'navajo', _UTF-16LE'chocolate'), BETWEEN(false, $5, 35, 45), BETWEEN(false, $5, 36, 50))])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[=($6, 2001)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
- HiveProject(cs_item_sk=[$0])
- HiveFilter(condition=[>($1, *(2, $2))])
- HiveAggregate(group=[{0}], agg#0=[sum($2)], agg#1=[sum($5)])
- HiveJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_item_sk=[$15], cs_order_number=[$17], cs_ext_list_price=[$25])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(cr_item_sk=[$2], cr_order_number=[$16], +=[+(+($23, $24), $25)])
- HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
+ HiveProject(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[ad2])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query68.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query68.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query68.q.out
index e5c1822..1b25235 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query68.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query68.q.out
@@ -96,32 +96,32 @@ POSTHOOK: Input: default@store_sales
POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$4], dir0=[ASC], dir1=[ASC], fetch=[100])
- HiveProject(c_last_name=[$3], c_first_name=[$2], ca_city=[$5], bought_city=[$8], ss_ticket_number=[$6], extended_price=[$9], extended_tax=[$11], list_price=[$10])
- HiveJoin(condition=[AND(<>($5, $8), =($7, $0))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_last_name=[$5], c_first_name=[$4], ca_city=[$1], bought_city=[$8], ss_ticket_number=[$6], extended_price=[$9], extended_tax=[$11], list_price=[$10])
+ HiveJoin(condition=[AND(=($3, $0), <>($1, $8))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_city=[$6])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[current_addr])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4], c_first_name=[$8], c_last_name=[$9])
HiveFilter(condition=[IS NOT NULL($4)])
HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_city=[$6])
- HiveTableScan(table=[[default, customer_address]], table:alias=[current_addr])
- HiveProject(ss_ticket_number=[$3], ss_customer_sk=[$1], bought_city=[$0], extended_price=[$4], list_price=[$5], extended_tax=[$6])
- HiveAggregate(group=[{1, 3, 5, 7}], agg#0=[sum($8)], agg#1=[sum($9)], agg#2=[sum($10)])
- HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0], ca_city=[$6])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($4, $10)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_ext_sales_price=[$15], ss_ext_list_price=[$17], ss_ext_tax=[$18])
- HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($5), IS NOT NULL($6), IS NOT NULL($3))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(IN($6, 1998, 1999, 2000), BETWEEN(false, $9, 1, 2))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(s_store_sk=[$0])
- HiveFilter(condition=[IN($22, _UTF-16LE'Cedar Grove', _UTF-16LE'Wildwood')])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[OR(=($3, 2), =($4, 1))])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
+ HiveProject(ss_ticket_number=[$3], ss_customer_sk=[$1], bought_city=[$0], extended_price=[$4], list_price=[$5], extended_tax=[$6])
+ HiveAggregate(group=[{1, 3, 5, 7}], agg#0=[sum($8)], agg#1=[sum($9)], agg#2=[sum($10)])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_city=[$6])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($4, $10)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_ticket_number=[$9], ss_ext_sales_price=[$15], ss_ext_list_price=[$17], ss_ext_tax=[$18])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($5), IS NOT NULL($6), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(IN($6, 1998, 1999, 2000), BETWEEN(false, $9, 1, 2))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(s_store_sk=[$0])
+ HiveFilter(condition=[IN($22, _UTF-16LE'Cedar Grove', _UTF-16LE'Wildwood')])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[OR(=($3, 2), =($4, 1))])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query72.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query72.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query72.q.out
index c28c941..206b0f1 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query72.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query72.q.out
@@ -82,44 +82,44 @@ CBO PLAN:
HiveSortLimit(sort0=[$5], sort1=[$0], sort2=[$1], sort3=[$2], dir0=[DESC-nulls-last], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5])
HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)], agg#1=[count($4)], agg#2=[count()])
- HiveProject($f0=[$17], $f1=[$15], $f2=[$21], $f3=[CASE(IS NULL($27), 1, 0)], $f4=[CASE(IS NOT NULL($27), 1, 0)])
- HiveJoin(condition=[AND(=($0, $6), =($1, $8))], joinType=[right], algorithm=[none], cost=[not available])
- HiveProject(cr_item_sk=[$2], cr_order_number=[$16])
- HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
- HiveProject(cs_sold_date_sk=[$10], cs_ship_date_sk=[$11], cs_bill_cdemo_sk=[$12], cs_bill_hdemo_sk=[$13], cs_item_sk=[$14], cs_promo_sk=[$15], cs_order_number=[$16], cs_quantity=[$17], inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3], w_warehouse_sk=[$4], w_warehouse_name=[$5], i_item_sk=[$6], i_item_desc=[$7], cd_demo_sk=[$21], hd_demo_sk=[$22], d_date_sk=[$18], d_week_seq=[$19], +=[$20], d_date_sk0=[$24], d_week_seq0=[$25], d_date_sk1=[$8], CAST=[$9], p_promo_sk=[$23])
- HiveJoin(condition=[AND(=($0, $24), =($19, $25))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($14, $1), <($3, $17))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($4, $2)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3])
- HiveTableScan(table=[[default, inventory]], table:alias=[inventory])
- HiveProject(w_warehouse_sk=[$0], w_warehouse_name=[$2])
- HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse])
- HiveProject(i_item_sk=[$0], i_item_desc=[$1], d_date_sk=[$2], CAST=[$3], cs_sold_date_sk=[$4], cs_ship_date_sk=[$5], cs_bill_cdemo_sk=[$6], cs_bill_hdemo_sk=[$7], cs_item_sk=[$8], cs_promo_sk=[$9], cs_order_number=[$10], cs_quantity=[$11], d_date_sk0=[$12], d_week_seq=[$13], +=[$14], cd_demo_sk=[$15], hd_demo_sk=[$16], p_promo_sk=[$17])
- HiveJoin(condition=[=($0, $8)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(i_item_sk=[$0], i_item_desc=[$4])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveJoin(condition=[=($7, $15)], joinType=[left], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($3, $0), >($1, $12))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(d_date_sk=[$0], CAST=[CAST($2):DOUBLE])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
- HiveJoin(condition=[=($3, $12)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($2, $11)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $8)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_ship_date_sk=[$2], cs_bill_cdemo_sk=[$4], cs_bill_hdemo_sk=[$5], cs_item_sk=[$15], cs_promo_sk=[$16], cs_order_number=[$17], cs_quantity=[$18])
- HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($5), IS NOT NULL($0), IS NOT NULL($2))])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0], d_week_seq=[$4], +=[+(CAST($2):DOUBLE, 5)])
- HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($4))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject($f0=[$15], $f1=[$13], $f2=[$19], $f3=[CASE(IS NULL($25), 1, 0)], $f4=[CASE(IS NOT NULL($25), 1, 0)])
+ HiveJoin(condition=[AND(=($26, $4), =($27, $6))], joinType=[left], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$4], cs_ship_date_sk=[$5], cs_bill_cdemo_sk=[$6], cs_bill_hdemo_sk=[$7], cs_item_sk=[$8], cs_promo_sk=[$9], cs_order_number=[$10], cs_quantity=[$11], inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3], w_warehouse_sk=[$19], w_warehouse_name=[$20], i_item_sk=[$23], i_item_desc=[$24], cd_demo_sk=[$12], hd_demo_sk=[$13], d_date_sk=[$16], d_week_seq=[$17], +=[$18], d_date_sk0=[$14], d_week_seq0=[$15], d_date_sk1=[$21], CAST=[$22], p_promo_sk=[$25])
+ HiveJoin(condition=[=($9, $25)], joinType=[left], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($23, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($5, $21), >($22, $18))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($19, $2)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(AND(=($0, $14), =($8, $1)), <($3, $11))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(inv_date_sk=[$0], inv_item_sk=[$1], inv_warehouse_sk=[$2], inv_quantity_on_hand=[$3])
+ HiveTableScan(table=[[default, inventory]], table:alias=[inventory])
+ HiveJoin(condition=[=($0, $12)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($3, $9)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $8)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_ship_date_sk=[$2], cs_bill_cdemo_sk=[$4], cs_bill_hdemo_sk=[$5], cs_item_sk=[$15], cs_promo_sk=[$16], cs_order_number=[$17], cs_quantity=[$18])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($5), IS NOT NULL($0), IS NOT NULL($2))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
HiveProject(cd_demo_sk=[$0])
HiveFilter(condition=[=($2, _UTF-16LE'M')])
HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics])
HiveProject(hd_demo_sk=[$0])
HiveFilter(condition=[=($2, _UTF-16LE'1001-5000')])
HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
- HiveProject(p_promo_sk=[$0])
- HiveTableScan(table=[[default, promotion]], table:alias=[promotion])
- HiveProject(d_date_sk=[$0], d_week_seq=[$4])
- HiveFilter(condition=[IS NOT NULL($4)])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$1], d_date_sk0=[$2], d_week_seq0=[$3], +=[$4])
+ HiveJoin(condition=[=($3, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(d_date_sk=[$0], d_week_seq=[$4], +=[+(CAST($2):DOUBLE, 5)])
+ HiveFilter(condition=[AND(=($6, 2001), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(w_warehouse_sk=[$0], w_warehouse_name=[$2])
+ HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse])
+ HiveProject(d_date_sk=[$0], CAST=[CAST($2):DOUBLE])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_item_desc=[$4])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(p_promo_sk=[$0])
+ HiveTableScan(table=[[default, promotion]], table:alias=[promotion])
+ HiveProject(cr_item_sk=[$2], cr_order_number=[$16])
+ HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query74.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query74.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query74.q.out
index 74a3a3f..4a8f0b8 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query74.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query74.q.out
@@ -131,7 +131,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject(customer_id=[$0], customer_first_name=[$1], customer_last_name=[$2])
- HiveJoin(condition=[AND(=($0, $6), CASE(CAST(IS NOT NULL($7)):BOOLEAN, CASE($10, >(/($5, $9), /($3, $7)), >(null, /($3, $7))), CASE($10, >(/($5, $9), null), null)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($0, $9), CASE(CAST(IS NOT NULL($10)):BOOLEAN, CASE($8, >(/($5, $7), /($3, $10)), >(null, /($3, $10))), CASE($8, >(/($5, $7), null), null)))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_customer_id=[$0], c_first_name=[$1], c_last_name=[$2], $f3=[$3])
HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
@@ -144,20 +144,33 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2002))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject($f0=[$0], $f4=[$3])
- HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29])
- HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2002))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f4=[$3])
+ HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2002))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(customer_id=[$0], year_total=[$3], CAST=[CAST(IS NOT NULL($3)):BOOLEAN])
+ HiveFilter(condition=[>($3, 0)])
+ HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2001))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject($f0=[$0], $f4=[$3])
HiveFilter(condition=[>($3, 0)])
HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
@@ -171,17 +184,4 @@ HiveSortLimit(sort0=[$1], sort1=[$0], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2001))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(customer_id=[$0], year_total=[$3], CAST=[CAST(IS NOT NULL($3)):BOOLEAN])
- HiveFilter(condition=[>($3, 0)])
- HiveAggregate(group=[{1, 2, 3}], agg#0=[max($6)])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], ws_net_paid=[$29])
- HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(IN($6, 2001, 2002), =($6, 2001))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query76.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query76.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query76.q.out
index 1f8c55a..a51a05a 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query76.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query76.q.out
@@ -62,34 +62,34 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], dir0=[
HiveAggregate(group=[{0, 1, 2, 3, 4}], agg#0=[count()], agg#1=[sum($5)])
HiveProject(channel=[$0], col_name=[$1], d_year=[$2], d_qoy=[$3], i_category=[$4], ext_sales_price=[$5])
HiveUnion(all=[true])
- HiveProject(channel=[_UTF-16LE'store'], col_name=[_UTF-16LE'ss_addr_sk'], d_year=[$1], d_qoy=[$2], i_category=[$4], ext_sales_price=[$7])
- HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(channel=[_UTF-16LE'store'], col_name=[_UTF-16LE'ss_addr_sk'], d_year=[$1], d_qoy=[$2], i_category=[$7], ext_sales_price=[$5])
+ HiveJoin(condition=[=($4, $6)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(i_item_sk=[$0], i_category=[$12])
- HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_ext_sales_price=[$15])
HiveFilter(condition=[AND(IS NULL($6), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(channel=[_UTF-16LE'web'], col_name=[_UTF-16LE'ws_web_page_sk'], d_year=[$6], d_qoy=[$7], i_category=[$4], ext_sales_price=[$2])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_category=[$12])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(channel=[_UTF-16LE'web'], col_name=[_UTF-16LE'ws_web_page_sk'], d_year=[$4], d_qoy=[$5], i_category=[$7], ext_sales_price=[$2])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_ext_sales_price=[$23])
HiveFilter(condition=[AND(IS NULL($12), IS NOT NULL($0))])
HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(i_item_sk=[$0], i_category=[$12])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(channel=[_UTF-16LE'catalog'], col_name=[_UTF-16LE'cs_warehouse_sk'], d_year=[$6], d_qoy=[$7], i_category=[$4], ext_sales_price=[$2])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_category=[$12])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(channel=[_UTF-16LE'catalog'], col_name=[_UTF-16LE'cs_warehouse_sk'], d_year=[$4], d_qoy=[$5], i_category=[$7], ext_sales_price=[$2])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_ext_sales_price=[$23])
HiveFilter(condition=[AND(IS NULL($14), IS NOT NULL($0))])
HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(i_item_sk=[$0], i_category=[$12])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_date_sk=[$0], d_year=[$6], d_qoy=[$10])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_category=[$12])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query83.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query83.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query83.q.out
index 2a51015..ef313ad 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query83.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query83.q.out
@@ -148,15 +148,12 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_id=[$0], $f1=[$1])
- HiveAggregate(group=[{4}], agg#0=[sum($2)])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{7}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cr_returned_date_sk=[$0], cr_item_sk=[$2], cr_return_quantity=[$17])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, catalog_returns]], table:alias=[catalog_returns])
- HiveProject(i_item_sk=[$0], i_item_id=[$1])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(d_date_sk=[$0], d_date=[$2])
HiveFilter(condition=[IS NOT NULL($2)])
@@ -170,16 +167,15 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(d_date_sk=[$0], d_date_id=[$1], d_date=[$2], d_month_seq=[$3], d_week_seq=[$4], d_quarter_seq=[$5], d_year=[$6], d_dow=[$7], d_moy=[$8], d_dom=[$9], d_qoy=[$10], d_fy_year=[$11], d_fy_quarter_seq=[$12], d_fy_week_seq=[$13], d_day_name=[$14], d_quarter_name=[$15], d_holiday=[$16], d_weekend=[$17], d_following_holiday=[$18], d_first_dom=[$19], d_last_dom=[$20], d_same_day_ly=[$21], d_same_day_lq=[$22], d_current_day=[$23], d_current_week=[$24], d_current_month=[$25], d_current_quarter=[$26], d_current_year=[$27], BLOCK__OFFSET__INSIDE__FILE=[$28], INPUT__FILE__NAME=[$29], ROW__ID=[$30])
HiveFilter(condition=[AND(IN($2, _UTF-16LE'1998-01-02', _UTF-16LE'1998-10-15', _UTF-16LE'1998-11-10'), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
HiveProject(i_item_id=[$0], $f1=[$1])
- HiveAggregate(group=[{4}], agg#0=[sum($2)])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{7}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_return_quantity=[$10])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveProject(i_item_sk=[$0], i_item_id=[$1])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(d_date_sk=[$0], d_date=[$2])
HiveFilter(condition=[IS NOT NULL($2)])
@@ -193,16 +189,15 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(d_date_sk=[$0], d_date_id=[$1], d_date=[$2], d_month_seq=[$3], d_week_seq=[$4], d_quarter_seq=[$5], d_year=[$6], d_dow=[$7], d_moy=[$8], d_dom=[$9], d_qoy=[$10], d_fy_year=[$11], d_fy_quarter_seq=[$12], d_fy_week_seq=[$13], d_day_name=[$14], d_quarter_name=[$15], d_holiday=[$16], d_weekend=[$17], d_following_holiday=[$18], d_first_dom=[$19], d_last_dom=[$20], d_same_day_ly=[$21], d_same_day_lq=[$22], d_current_day=[$23], d_current_week=[$24], d_current_month=[$25], d_current_quarter=[$26], d_current_year=[$27], BLOCK__OFFSET__INSIDE__FILE=[$28], INPUT__FILE__NAME=[$29], ROW__ID=[$30])
HiveFilter(condition=[AND(IN($2, _UTF-16LE'1998-01-02', _UTF-16LE'1998-10-15', _UTF-16LE'1998-11-10'), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
HiveProject(item_id=[$0], wr_item_qty=[$1], CAST=[CAST($1):DOUBLE])
- HiveAggregate(group=[{4}], agg#0=[sum($2)])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{7}], agg#0=[sum($2)])
+ HiveJoin(condition=[=($1, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(wr_returned_date_sk=[$0], wr_item_sk=[$2], wr_return_quantity=[$14])
HiveFilter(condition=[IS NOT NULL($0)])
HiveTableScan(table=[[default, web_returns]], table:alias=[web_returns])
- HiveProject(i_item_sk=[$0], i_item_id=[$1])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(d_date_sk=[$0], d_date=[$1], d_date0=[$2])
HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(d_date_sk=[$0], d_date=[$2])
HiveFilter(condition=[IS NOT NULL($2)])
@@ -216,4 +211,6 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveProject(d_date_sk=[$0], d_date_id=[$1], d_date=[$2], d_month_seq=[$3], d_week_seq=[$4], d_quarter_seq=[$5], d_year=[$6], d_dow=[$7], d_moy=[$8], d_dom=[$9], d_qoy=[$10], d_fy_year=[$11], d_fy_quarter_seq=[$12], d_fy_week_seq=[$13], d_day_name=[$14], d_quarter_name=[$15], d_holiday=[$16], d_weekend=[$17], d_following_holiday=[$18], d_first_dom=[$19], d_last_dom=[$20], d_same_day_ly=[$21], d_same_day_lq=[$22], d_current_day=[$23], d_current_week=[$24], d_current_month=[$25], d_current_quarter=[$26], d_current_year=[$27], BLOCK__OFFSET__INSIDE__FILE=[$28], INPUT__FILE__NAME=[$29], ROW__ID=[$30])
HiveFilter(condition=[AND(IN($2, _UTF-16LE'1998-01-02', _UTF-16LE'1998-10-15', _UTF-16LE'1998-11-10'), IS NOT NULL($4))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query85.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query85.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query85.q.out
index 1876936..6471345 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query85.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query85.q.out
@@ -184,18 +184,18 @@ CBO PLAN:
HiveProject(_o__c0=[$0], _o__c1=[$1], _o__c2=[$2], _o__c3=[$3])
HiveSortLimit(sort0=[$7], sort1=[$4], sort2=[$5], sort3=[$6], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
HiveProject(_o__c0=[substr($0, 1, 20)], _o__c1=[/(CAST($1):DOUBLE, $2)], _o__c2=[/($3, $4)], _o__c3=[/($5, $6)], (tok_function avg (tok_table_or_col ws_quantity))=[/(CAST($1):DOUBLE, $2)], (tok_function avg (tok_table_or_col wr_refunded_cash))=[/($3, $4)], (tok_function avg (tok_table_or_col wr_fee))=[/($5, $6)], (tok_function substr (tok_table_or_col r_reason_desc) 1 20)=[substr($0, 1, 20)])
- HiveAggregate(group=[{14}], agg#0=[sum($30)], agg#1=[count($30)], agg#2=[sum($26)], agg#3=[count($26)], agg#4=[sum($25)], agg#5=[count($25)])
- HiveJoin(condition=[AND(AND(AND(=($1, $17), =($2, $18)), =($0, $20)), OR(AND($3, $4, $34), AND($5, $6, $35), AND($7, $8, $36)))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2], cd_education_status=[$3], ==[=($2, _UTF-16LE'M')], =4=[=($3, _UTF-16LE'4 yr Degree')], =5=[=($2, _UTF-16LE'D')], =6=[=($3, _UTF-16LE'Primary')], =7=[=($2, _UTF-16LE'U')], =8=[=($3, _UTF-16LE'Advanced Degree')])
- HiveFilter(condition=[AND(IN($3, _UTF-16LE'4 yr Degree', _UTF-16LE'Primary', _UTF-16LE'Advanced Degree'), IN($2, _UTF-16LE'M', _UTF-16LE'D', _UTF-16LE'U'))])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
- HiveJoin(condition=[AND(=($0, $12), OR(AND($1, $22), AND($2, $23), AND($3, $24)))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0], IN=[IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM')], IN2=[IN($8, _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN')], IN3=[IN($8, _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV')])
- HiveFilter(condition=[AND(IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM', _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN', _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV'), =($10, _UTF-16LE'United States'))])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveJoin(condition=[=($0, $10)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(r_reason_sk=[$0], r_reason_desc=[$2])
- HiveTableScan(table=[[default, reason]], table:alias=[reason])
+ HiveAggregate(group=[{1}], agg#0=[sum($30)], agg#1=[count($30)], agg#2=[sum($26)], agg#3=[count($26)], agg#4=[sum($25)], agg#5=[count($25)])
+ HiveJoin(condition=[=($0, $23)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(r_reason_sk=[$0], r_reason_desc=[$2])
+ HiveTableScan(table=[[default, reason]], table:alias=[reason])
+ HiveJoin(condition=[AND(AND(AND(=($1, $15), =($2, $16)), =($0, $18)), OR(AND($3, $4, $32), AND($5, $6, $33), AND($7, $8, $34)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2], cd_education_status=[$3], ==[=($2, _UTF-16LE'M')], =4=[=($3, _UTF-16LE'4 yr Degree')], =5=[=($2, _UTF-16LE'D')], =6=[=($3, _UTF-16LE'Primary')], =7=[=($2, _UTF-16LE'U')], =8=[=($3, _UTF-16LE'Advanced Degree')])
+ HiveFilter(condition=[AND(IN($3, _UTF-16LE'4 yr Degree', _UTF-16LE'Primary', _UTF-16LE'Advanced Degree'), IN($2, _UTF-16LE'M', _UTF-16LE'D', _UTF-16LE'U'))])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[cd1])
+ HiveJoin(condition=[AND(=($0, $10), OR(AND($1, $20), AND($2, $21), AND($3, $22)))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], IN=[IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM')], IN2=[IN($8, _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN')], IN3=[IN($8, _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV')])
+ HiveFilter(condition=[AND(IN($8, _UTF-16LE'KY', _UTF-16LE'GA', _UTF-16LE'NM', _UTF-16LE'MT', _UTF-16LE'OR', _UTF-16LE'IN', _UTF-16LE'WI', _UTF-16LE'MO', _UTF-16LE'WV'), =($10, _UTF-16LE'United States'))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
HiveJoin(condition=[=($12, $0)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[=($6, 1998)])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query91.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query91.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query91.q.out
index 046a374..0017776 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query91.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query91.q.out
@@ -78,22 +78,22 @@ CBO PLAN:
HiveProject(call_center=[$0], call_center_name=[$1], manager=[$2], returns_loss=[$3])
HiveSortLimit(sort0=[$4], dir0=[DESC-nulls-last])
HiveProject(call_center=[$2], call_center_name=[$3], manager=[$4], returns_loss=[$5], (tok_function sum (tok_table_or_col cr_net_loss))=[$5])
- HiveAggregate(group=[{6, 7, 14, 15, 16}], agg#0=[sum($11)])
- HiveJoin(condition=[=($17, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($9, $1)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0])
- HiveFilter(condition=[=($11, -7)])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveJoin(condition=[=($4, $1)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4])
- HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2), IS NOT NULL($3))])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2], cd_education_status=[$3])
- HiveFilter(condition=[AND(IN($3, _UTF-16LE'Unknown', _UTF-16LE'Advanced Degree'), IN($2, _UTF-16LE'M', _UTF-16LE'W'), IN(ROW($2, $3), ROW(_UTF-16LE'M', _UTF-16LE'Unknown'), ROW(_UTF-16LE'W', _UTF-16LE'Advanced Degree')))])
- HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics])
- HiveProject(cr_returned_date_sk=[$0], cr_returning_customer_sk=[$1], cr_call_center_sk=[$2], cr_net_loss=[$3], d_date_sk=[$4], cc_call_center_sk=[$5], cc_call_center_id=[$6], cc_name=[$7], cc_manager=[$8])
- HiveJoin(condition=[=($2, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{6, 7, 15, 16, 17}], agg#0=[sum($11)])
+ HiveJoin(condition=[=($10, $14)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($13, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($9, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0])
+ HiveFilter(condition=[=($11, -7)])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveJoin(condition=[=($4, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4])
+ HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(cd_demo_sk=[$0], cd_marital_status=[$2], cd_education_status=[$3])
+ HiveFilter(condition=[AND(IN($3, _UTF-16LE'Unknown', _UTF-16LE'Advanced Degree'), IN($2, _UTF-16LE'M', _UTF-16LE'W'), IN(ROW($2, $3), ROW(_UTF-16LE'M', _UTF-16LE'Unknown'), ROW(_UTF-16LE'W', _UTF-16LE'Advanced Degree')))])
+ HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics])
+ HiveProject(cr_returned_date_sk=[$0], cr_returning_customer_sk=[$1], cr_call_center_sk=[$2], cr_net_loss=[$3], d_date_sk=[$4])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cr_returned_date_sk=[$0], cr_returning_customer_sk=[$7], cr_call_center_sk=[$11], cr_net_loss=[$26])
HiveFilter(condition=[AND(IS NOT NULL($11), IS NOT NULL($0), IS NOT NULL($7))])
@@ -101,9 +101,9 @@ HiveProject(call_center=[$0], call_center_name=[$1], manager=[$2], returns_loss=
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(=($6, 1999), =($8, 11))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(cc_call_center_sk=[$0], cc_call_center_id=[$1], cc_name=[$6], cc_manager=[$11])
- HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
- HiveProject(hd_demo_sk=[$0])
- HiveFilter(condition=[LIKE($2, _UTF-16LE'0-500%')])
- HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
+ HiveProject(hd_demo_sk=[$0])
+ HiveFilter(condition=[LIKE($2, _UTF-16LE'0-500%')])
+ HiveTableScan(table=[[default, household_demographics]], table:alias=[household_demographics])
+ HiveProject(cc_call_center_sk=[$0], cc_call_center_id=[$1], cc_name=[$6], cc_manager=[$11])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query99.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query99.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query99.q.out
index 2dccdf2..1cc459c 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query99.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query99.q.out
@@ -81,10 +81,10 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveProject(_o__c0=[$0], sm_type=[$1], cc_name=[$2], 30 days=[$3], 31-60 days=[$4], 61-90 days=[$5], 91-120 days=[$6], >120 days=[$7])
HiveSortLimit(sort0=[$8], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
- HiveProject(_o__c0=[$2], sm_type=[$0], cc_name=[$1], 30 days=[$3], 31-60 days=[$4], 61-90 days=[$5], 91-120 days=[$6], >120 days=[$7], (tok_function substr (tok_table_or_col w_warehouse_name) 1 20)=[$2])
+ HiveProject(_o__c0=[$1], sm_type=[$0], cc_name=[$2], 30 days=[$3], 31-60 days=[$4], 61-90 days=[$5], 91-120 days=[$6], >120 days=[$7], (tok_function substr (tok_table_or_col w_warehouse_name) 1 20)=[$1])
HiveAggregate(group=[{11, 13, 15}], agg#0=[sum($4)], agg#1=[sum($5)], agg#2=[sum($6)], agg#3=[sum($7)], agg#4=[sum($8)])
- HiveJoin(condition=[=($3, $14)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $12)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($1, $14)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($3, $12)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($2, $10)], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(cs_ship_date_sk=[$2], cs_call_center_sk=[$11], cs_ship_mode_sk=[$13], cs_warehouse_sk=[$14], CASE=[CASE(<=(-($2, $0), 30), 1, 0)], CASE5=[CASE(AND(>(-($2, $0), 30), <=(-($2, $0), 60)), 1, 0)], CASE6=[CASE(AND(>(-($2, $0), 60), <=(-($2, $0), 90)), 1, 0)], CASE7=[CASE(AND(>(-($2, $0), 90), <=(-($2, $0), 120)), 1, 0)], CASE8=[CASE(>(-($2, $0), 120), 1, 0)])
@@ -95,8 +95,8 @@ HiveProject(_o__c0=[$0], sm_type=[$1], cc_name=[$2], 30 days=[$3], 31-60 days=[$
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
HiveProject(sm_ship_mode_sk=[$0], sm_type=[$2])
HiveTableScan(table=[[default, ship_mode]], table:alias=[ship_mode])
- HiveProject(cc_call_center_sk=[$0], cc_name=[$6])
- HiveTableScan(table=[[default, call_center]], table:alias=[call_center])
- HiveProject(w_warehouse_sk=[$0], substr=[substr($2, 1, 20)])
- HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse])
+ HiveProject(w_warehouse_sk=[$0], substr=[substr($2, 1, 20)])
+ HiveTableScan(table=[[default, warehouse]], table:alias=[warehouse])
+ HiveProject(cc_call_center_sk=[$0], cc_name=[$6])
+ HiveTableScan(table=[[default, call_center]], table:alias=[call_center])