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])
