http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query35.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query35.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query35.q.out index e79c6b7..e434696 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query35.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query35.q.out @@ -135,15 +135,16 @@ HiveProject(ca_state=[$0], cd_gender=[$1], cd_marital_status=[$2], cnt1=[$3], _o HiveJoin(condition=[=($0, $13)], joinType=[left], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $11)], joinType=[left], algorithm=[none], cost=[not available]) HiveSemiJoin(condition=[=($0, $11)], joinType=[inner]) - HiveJoin(condition=[=($5, $1)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($2, $3)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_addr_sk=[$4]) - HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2))]) - HiveTableScan(table=[[default, customer]], table:alias=[c]) + HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$1], c_current_addr_sk=[$2], ca_address_sk=[$9], ca_state=[$10], cd_demo_sk=[$3], cd_gender=[$4], cd_marital_status=[$5], cd_dep_count=[$6], cd_dep_employed_count=[$7], cd_dep_college_count=[$8]) + HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_addr_sk=[$4]) + HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($2))]) + HiveTableScan(table=[[default, customer]], table:alias=[c]) + HiveProject(cd_demo_sk=[$0], cd_gender=[$1], cd_marital_status=[$2], cd_dep_count=[$6], cd_dep_employed_count=[$7], cd_dep_college_count=[$8]) + HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics]) HiveProject(ca_address_sk=[$0], ca_state=[$8]) HiveTableScan(table=[[default, customer_address]], table:alias=[ca]) - HiveProject(cd_demo_sk=[$0], cd_gender=[$1], cd_marital_status=[$2], cd_dep_count=[$6], cd_dep_employed_count=[$7], cd_dep_college_count=[$8]) - HiveTableScan(table=[[default, customer_demographics]], table:alias=[customer_demographics]) HiveProject(ss_customer_sk0=[$1]) HiveJoin(condition=[=($0, $2)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3])
http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query4.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query4.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query4.q.out index 9fb918e..9668e0f 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query4.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query4.q.out @@ -229,7 +229,7 @@ POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(customer_preferred_cust_flag=[$1]) - HiveJoin(condition=[AND(=($0, $7), CASE(CAST(IS NOT NULL($8)):BOOLEAN, CASE($14, >(/($4, $13), /($2, $8)), >(null, /($2, $8))), CASE($14, >(/($4, $13), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($0, $10), CASE(CAST(IS NOT NULL($11)):BOOLEAN, CASE($14, >(/($6, $13), /($2, $11)), >(null, /($2, $11))), CASE($14, >(/($6, $13), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject($f0=[$0], $f3=[$3], $f8=[$7]) HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -242,34 +242,47 @@ HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(d_date_sk=[$0]) HiveFilter(condition=[=($6, 2002)]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveJoin(condition=[AND(=($4, $0), CASE($8, CASE($11, >(/($1, $10), /($3, $7)), >(null, /($3, $7))), CASE($11, >(/($1, $10), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($7, $0), CASE($6, CASE($11, >(/($3, $10), /($1, $5)), >(null, /($1, $5))), CASE($11, >(/($3, $10), null), null)))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject($f0=[$0], $f8=[$7]) HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) HiveTableScan(table=[[default, customer]], table:alias=[customer]) HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) - HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) - HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + HiveProject(ws_sold_date_sk=[$0], ws_bill_customer_sk=[$4], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) + 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=[=($6, 2002)]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveJoin(condition=[=($2, $7)], joinType=[inner], algorithm=[none], cost=[not available]) - 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], $f8=[$7]) - HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) - HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) - 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], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) - 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=[=($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], $f8=[$7]) + HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) + HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) + HiveTableScan(table=[[default, customer]], table:alias=[customer]) + HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) + HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) + HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + HiveProject(d_date_sk=[$0]) + HiveFilter(condition=[=($6, 2002)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveJoin(condition=[=($3, $5)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(customer_id=[$0], year_total=[$7], CAST=[CAST(IS NOT NULL($7)):BOOLEAN]) + HiveFilter(condition=[>($7, 0)]) + HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) + HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) + 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], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) + 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=[=($6, 2001)]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject($f0=[$0], $f8=[$7]) HiveFilter(condition=[>($7, 0)]) HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) @@ -290,23 +303,10 @@ HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) 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], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) - HiveFilter(condition=[AND(IS NOT NULL($4), IS NOT NULL($0))]) - HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) + HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) + HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) + HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) HiveProject(d_date_sk=[$0]) HiveFilter(condition=[=($6, 2001)]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(customer_id=[$0], year_total=[$7], CAST=[CAST(IS NOT NULL($7)):BOOLEAN]) - HiveFilter(condition=[>($7, 0)]) - HiveAggregate(group=[{1, 2, 3, 4, 5, 6, 7}], agg#0=[sum($10)]) - HiveJoin(condition=[=($0, $9)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(c_customer_sk=[$0], c_customer_id=[$1], c_first_name=[$8], c_last_name=[$9], c_preferred_cust_flag=[$10], c_birth_country=[$14], c_login=[$15], c_email_address=[$16]) - HiveTableScan(table=[[default, customer]], table:alias=[customer]) - HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], /=[/(+(-(-($25, $24), $22), $23), CAST(2):DECIMAL(10, 0))]) - HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) - HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) - HiveProject(d_date_sk=[$0]) - HiveFilter(condition=[=($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_query46.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query46.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query46.q.out index df36f9b..9d21449 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query46.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query46.q.out @@ -82,32 +82,32 @@ POSTHOOK: Input: default@store_sales POSTHOOK: Output: hdfs://### HDFS PATH ### CBO PLAN: HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], sort4=[$4], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100]) - HiveProject(c_last_name=[$3], c_first_name=[$2], ca_city=[$5], bought_city=[$8], ss_ticket_number=[$6], amt=[$9], profit=[$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], amt=[$9], profit=[$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], amt=[$4], profit=[$5]) - HiveAggregate(group=[{1, 3, 5, 7}], agg#0=[sum($8)], agg#1=[sum($9)]) - 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, $10)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $9)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($0, $8)], 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_coupon_amt=[$19], ss_net_profit=[$22]) - 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($7, 6, 0), IN($6, 1998, 1999, 2000))]) - 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', _UTF-16LE'Union', _UTF-16LE'Salem', _UTF-16LE'Highland Park')]) - 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], amt=[$4], profit=[$5]) + HiveAggregate(group=[{1, 3, 5, 7}], agg#0=[sum($8)], agg#1=[sum($9)]) + 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, $10)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($4, $9)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($0, $8)], 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_coupon_amt=[$19], ss_net_profit=[$22]) + 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($7, 6, 0), IN($6, 1998, 1999, 2000))]) + 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', _UTF-16LE'Union', _UTF-16LE'Salem', _UTF-16LE'Highland Park')]) + 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_query47.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out index 3c90232..8aa0871 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query47.q.out @@ -115,14 +115,11 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su HiveJoin(condition=[AND(AND(AND(AND(=($12, $0), =($13, $1)), =($14, $2)), =($15, $3)), =($20, $5))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], -=[-($5, 1)]) HiveFilter(condition=[IS NOT NULL($5)]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6]) - HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) - HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) - HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6]) + HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)]) + HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $7)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_store_sk=[$7], ss_sales_price=[$13]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) @@ -130,20 +127,20 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) - HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) - HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) + HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) + HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) + HiveTableScan(table=[[default, item]], table:alias=[item]) HiveJoin(condition=[AND(AND(AND(AND(=($6, $0), =($7, $1)), =($8, $2)), =($9, $3)), =($14, $5))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$4], +=[+($5, 1)]) HiveFilter(condition=[IS NOT NULL($5)]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6]) - HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) - HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) - HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6]) + HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)]) + HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $7)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_store_sk=[$7], ss_sales_price=[$13]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) @@ -151,19 +148,19 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) - HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) - HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) + HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) + HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) + HiveTableScan(table=[[default, item]], table:alias=[item]) HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$4], (tok_table_or_col d_moy)=[$5], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[$7], rank_window_1=[$8]) HiveFilter(condition=[AND(=($4, 2000), >($7, 0), CASE(>($7, 0), >(/(ABS(-($6, $7)), $7), 0.1), null), IS NOT NULL($8))]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col s_store_name)=[$4], (tok_table_or_col s_company_name)=[$5], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $1, $0, $4, $5, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $5 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4, $5 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], s_store_name=[$4], s_company_name=[$5], $f6=[$6]) - HiveAggregate(group=[{1, 2, 8, 9, 11, 12}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $10)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($4, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) - HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) - HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$5], (tok_table_or_col i_brand)=[$4], (tok_table_or_col s_store_name)=[$2], (tok_table_or_col s_company_name)=[$3], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col ss_sales_price))=[$6], avg_window_0=[avg($6) OVER (PARTITION BY $5, $4, $2, $3, $0 ORDER BY $5 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST, $3 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $5, $4, $2, $3 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], s_store_name=[$2], s_company_name=[$3], i_brand=[$4], i_category=[$5], $f6=[$6]) + HiveAggregate(group=[{5, 6, 8, 9, 11, 12}], agg#0=[sum($3)]) + HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $7)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_store_sk=[$7], ss_sales_price=[$13]) HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) @@ -171,7 +168,10 @@ HiveProject(i_category=[$0], d_year=[$1], d_moy=[$2], avg_monthly_sales=[$3], su HiveProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) HiveFilter(condition=[AND(IN($6, 2000, 1999, 2001), OR(=($6, 2000), IN(ROW($6, $8), ROW(1999, 12), ROW(2001, 1))))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) - HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) - HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(s_store_sk=[$0], s_store_name=[$5], s_company_name=[$17]) + HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($17))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) + HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) + 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_query54.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out index 5d9dd17..6af3162 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out @@ -1,7 +1,7 @@ -Warning: Shuffle Join MERGEJOIN[264][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product -Warning: Shuffle Join MERGEJOIN[273][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product -Warning: Shuffle Join MERGEJOIN[272][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 14' is a cross product -Warning: Shuffle Join MERGEJOIN[274][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product +Warning: Shuffle Join MERGEJOIN[269][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product +Warning: Shuffle Join MERGEJOIN[278][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4, $hdt$_5]] in Stage 'Reducer 6' is a cross product +Warning: Shuffle Join MERGEJOIN[277][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 15' is a cross product +Warning: Shuffle Join MERGEJOIN[279][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 7' is a cross product PREHOOK: query: explain cbo with my_customers as ( select distinct c_customer_sk @@ -139,34 +139,24 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveFilter(condition=[BETWEEN(false, $2, $3, $4)]) HiveProject(c_customer_sk=[$0], ss_ext_sales_price=[$4], d_month_seq=[$11], _o__c0=[$13], $f0=[$14]) HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject($f0=[$11], $f1=[$12], ss_sold_date_sk=[$0], ss_customer_sk=[$1], ss_ext_sales_price=[$2], ca_address_sk=[$6], ca_county=[$7], ca_state=[$8], s_county=[$9], s_state=[$10], d_date_sk=[$4], d_month_seq=[$5], cnt=[$3], $f00=[$13]) + HiveProject($f0=[$4], $f1=[$5], ss_sold_date_sk=[$0], ss_customer_sk=[$1], ss_ext_sales_price=[$2], ca_address_sk=[$8], ca_county=[$9], ca_state=[$10], s_county=[$11], s_state=[$12], d_date_sk=[$6], d_month_seq=[$7], cnt=[$3], $f00=[$13]) HiveJoin(condition=[true], joinType=[left], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($11, $1)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_ext_sales_price=[$15]) - HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) - HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - HiveProject(cnt=[$0]) - HiveFilter(condition=[<=(sq_count_check($0), 1)]) - HiveProject(cnt=[$0]) - HiveAggregate(group=[{}], cnt=[COUNT()]) - HiveProject($f0=[$0]) - HiveAggregate(group=[{0}]) - HiveProject($f0=[+($3, 1)]) - HiveFilter(condition=[AND(=($6, 1999), =($8, 3))]) - HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(d_date_sk=[$0], d_month_seq=[$3]) - HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(ca_address_sk=[$0], ca_county=[$1], ca_state=[$2], s_county=[$3], s_state=[$4], c_customer_sk=[$5], c_current_addr_sk=[$6]) - HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8]) - HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($8))]) - HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) - HiveProject(s_county=[$23], s_state=[$24]) - HiveFilter(condition=[AND(IS NOT NULL($23), IS NOT NULL($24))]) - HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveJoin(condition=[=($5, $8)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($4, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_ext_sales_price=[$15]) + HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))]) + HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + HiveProject(cnt=[$0]) + HiveFilter(condition=[<=(sq_count_check($0), 1)]) + HiveProject(cnt=[$0]) + HiveAggregate(group=[{}], cnt=[COUNT()]) + HiveProject($f0=[$0]) + HiveAggregate(group=[{0}]) + HiveProject($f0=[+($3, 1)]) + HiveFilter(condition=[AND(=($6, 1999), =($8, 3))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1]) HiveAggregate(group=[{0, 1}]) HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) @@ -189,6 +179,16 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(i_item_sk=[$0]) HiveFilter(condition=[AND(=($12, _UTF-16LE'Jewelry'), =($10, _UTF-16LE'consignment'))]) HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject(d_date_sk=[$0], d_month_seq=[$3]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(ca_address_sk=[$0], ca_county=[$1], ca_state=[$2], s_county=[$3], s_state=[$4]) + HiveJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8]) + HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($8))]) + HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address]) + HiveProject(s_county=[$23], s_state=[$24]) + HiveFilter(condition=[AND(IS NOT NULL($23), IS NOT NULL($24))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) HiveProject($f0=[$0]) HiveAggregate(group=[{0}]) HiveProject($f0=[+($3, 1)]) http://git-wip-us.apache.org/repos/asf/hive/blob/5553c59e/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out index 440a4e9..cb8f67b 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query57.q.out @@ -109,13 +109,10 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_ HiveJoin(condition=[AND(AND(AND(=($10, $0), =($11, $1)), =($12, $2)), =($17, $4))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], -=[-($4, 1)]) HiveFilter(condition=[IS NOT NULL($4)]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5]) - HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) - HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) - HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5]) + HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)]) + HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21]) @@ -127,16 +124,16 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_ HiveProject(cc_call_center_sk=[$0], cc_name=[$6]) HiveFilter(condition=[IS NOT NULL($6)]) HiveTableScan(table=[[default, call_center]], table:alias=[call_center]) + HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) + HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) + HiveTableScan(table=[[default, item]], table:alias=[item]) HiveJoin(condition=[AND(AND(AND(=($5, $0), =($6, $1)), =($7, $2)), =($12, $4))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$3], +=[+($4, 1)]) HiveFilter(condition=[IS NOT NULL($4)]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5]) - HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) - HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) - HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5]) + HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)]) + HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21]) @@ -148,15 +145,15 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_ HiveProject(cc_call_center_sk=[$0], cc_name=[$6]) HiveFilter(condition=[IS NOT NULL($6)]) HiveTableScan(table=[[default, call_center]], table:alias=[call_center]) - HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7]) - HiveFilter(condition=[AND(=($3, 2000), >($6, 0), CASE(>($6, 0), >(/(ABS(-($5, $6)), $6), 0.1), null), IS NOT NULL($7))]) - HiveProject((tok_table_or_col i_category)=[$1], (tok_table_or_col i_brand)=[$0], (tok_table_or_col cc_name)=[$4], (tok_table_or_col d_year)=[$2], (tok_table_or_col d_moy)=[$3], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $1, $0, $4, $2 ORDER BY $1 NULLS FIRST, $0 NULLS FIRST, $4 NULLS FIRST, $2 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $1, $0, $4 ORDER BY $2 NULLS LAST, $3 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) - HiveProject(i_brand=[$0], i_category=[$1], d_year=[$2], d_moy=[$3], cc_name=[$4], $f5=[$5]) - HiveAggregate(group=[{1, 2, 8, 9, 11}], agg#0=[sum($6)]) - HiveJoin(condition=[=($5, $0)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) HiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7]) + HiveFilter(condition=[AND(=($3, 2000), >($6, 0), CASE(>($6, 0), >(/(ABS(-($5, $6)), $6), 0.1), null), IS NOT NULL($7))]) + HiveProject((tok_table_or_col i_category)=[$4], (tok_table_or_col i_brand)=[$3], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$0], (tok_table_or_col d_moy)=[$1], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $4, $3, $2, $0 ORDER BY $4 NULLS FIRST, $3 NULLS FIRST, $2 NULLS FIRST, $0 NULLS FIRST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)], rank_window_1=[rank() OVER (PARTITION BY $4, $3, $2 ORDER BY $0 NULLS LAST, $1 NULLS LAST ROWS BETWEEN 2147483647 FOLLOWING AND 2147483647 PRECEDING)]) + HiveProject(d_year=[$0], d_moy=[$1], cc_name=[$2], i_brand=[$3], i_category=[$4], $f5=[$5]) + HiveAggregate(group=[{5, 6, 8, 10, 11}], agg#0=[sum($3)]) + HiveJoin(condition=[=($2, $9)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(cs_sold_date_sk=[$0], cs_call_center_sk=[$11], cs_item_sk=[$15], cs_sales_price=[$21]) @@ -168,4 +165,7 @@ HiveProject(i_category=[$0], i_brand=[$1], d_year=[$2], d_moy=[$3], avg_monthly_ HiveProject(cc_call_center_sk=[$0], cc_name=[$6]) HiveFilter(condition=[IS NOT NULL($6)]) HiveTableScan(table=[[default, call_center]], table:alias=[call_center]) + HiveProject(i_item_sk=[$0], i_brand=[$8], i_category=[$12]) + HiveFilter(condition=[AND(IS NOT NULL($12), IS NOT NULL($8))]) + 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_query58.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query58.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query58.q.out index a362c45..3e89c2d 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query58.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query58.q.out @@ -1,4 +1,4 @@ -Warning: Shuffle Join MERGEJOIN[401][tables = [$hdt$_4, $hdt$_5]] in Stage 'Reducer 22' is a cross product +Warning: Shuffle Join MERGEJOIN[406][tables = [$hdt$_3, $hdt$_4]] in Stage 'Reducer 20' is a cross product PREHOOK: query: explain cbo with ss_items as (select i_item_id item_id @@ -145,15 +145,12 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveJoin(condition=[AND(AND(AND(AND(=($0, $4), BETWEEN(false, $3, $6, $7)), BETWEEN(false, $1, $6, $7)), BETWEEN(false, $5, *(0.9, $3), *(1.1, $3))), BETWEEN(false, $5, *(0.9, $1), *(1.1, $1)))], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[AND(AND(=($2, $0), BETWEEN(false, $3, *(0.9, $1), *(1.1, $1))), BETWEEN(false, $1, *(0.9, $3), *(1.1, $3)))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(i_item_id=[$0], $f1=[$1]) - HiveAggregate(group=[{4}], agg#0=[sum($2)]) - HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveJoin(condition=[=($1, $3)], joinType=[inner], algorithm=[none], cost=[not available]) + 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(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_ext_sales_price=[$23]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) - 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)]) @@ -174,16 +171,15 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(d_week_seq=[$4]) HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(i_item_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(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_ext_sales_price=[$15]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) - 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)]) @@ -204,16 +200,15 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(d_week_seq=[$4]) HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(i_item_sk=[$0], i_item_id=[$1]) + HiveTableScan(table=[[default, item]], table:alias=[item]) HiveProject(item_id=[$0], ws_item_rev=[$1], *=[*(0.9, $1)], *3=[*(1.1, $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(ws_sold_date_sk=[$0], ws_item_sk=[$3], ws_ext_sales_price=[$23]) HiveFilter(condition=[IS NOT NULL($0)]) HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) - 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)]) @@ -234,4 +229,6 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) HiveProject(d_week_seq=[$4]) HiveFilter(condition=[AND(=($2, _UTF-16LE'1998-02-19'), IS NOT NULL($4))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(i_item_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_query6.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query6.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query6.q.out index cbf372a..959ddd0 100644 --- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query6.q.out +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query6.q.out @@ -1,4 +1,4 @@ -Warning: Map Join MAPJOIN[170][bigTable=?] in task 'Reducer 15' is a cross product +Warning: Map Join MAPJOIN[170][bigTable=?] in task 'Map 8' is a cross product PREHOOK: query: explain cbo select a.ca_state state, count(*) cnt from customer_address a @@ -65,8 +65,8 @@ CBO PLAN: HiveSortLimit(sort0=[$1], dir0=[ASC], fetch=[100]) HiveProject(ca_state=[$0], $f1=[$1]) HiveFilter(condition=[>=($1, 10)]) - HiveAggregate(group=[{9}], agg#0=[count()]) - HiveJoin(condition=[=($1, $10)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveAggregate(group=[{10}], agg#0=[count()]) + HiveJoin(condition=[=($1, $11)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($6, $2)], joinType=[inner], algorithm=[none], cost=[not available]) HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3]) @@ -80,29 +80,29 @@ HiveSortLimit(sort0=[$1], dir0=[ASC], fetch=[100]) HiveAggregate(group=[{3}]) HiveFilter(condition=[AND(=($6, 2000), =($8, 2), IS NOT NULL($3))]) HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) - HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1], ca_address_sk=[$2], ca_state=[$3]) - HiveJoin(condition=[=($2, $1)], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4]) - HiveFilter(condition=[IS NOT NULL($4)]) - HiveTableScan(table=[[default, customer]], table:alias=[c]) + HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1], cnt=[$2], ca_address_sk=[$3], ca_state=[$4]) + HiveJoin(condition=[=($3, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4]) + HiveFilter(condition=[IS NOT NULL($4)]) + HiveTableScan(table=[[default, customer]], table:alias=[c]) + HiveProject(cnt=[$0]) + HiveFilter(condition=[<=(sq_count_check($0), 1)]) + HiveProject(cnt=[$0]) + HiveAggregate(group=[{}], cnt=[COUNT()]) + HiveProject(d_month_seq=[$0]) + HiveAggregate(group=[{3}]) + HiveFilter(condition=[AND(=($6, 2000), =($8, 2))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) HiveProject(ca_address_sk=[$0], ca_state=[$8]) HiveTableScan(table=[[default, customer_address]], table:alias=[a]) - HiveProject(i_item_sk=[$0], i_current_price=[$1], i_category=[$2], i_category0=[$3], *=[$4], cnt=[$5]) + HiveProject(i_item_sk=[$0], i_current_price=[$1], i_category=[$2], i_category0=[$3], *=[$4]) HiveJoin(condition=[AND(=($3, $2), >($1, $4))], joinType=[inner], algorithm=[none], cost=[not available]) HiveProject(i_item_sk=[$0], i_current_price=[$5], i_category=[$12]) HiveFilter(condition=[IS NOT NULL($12)]) HiveTableScan(table=[[default, item]], table:alias=[i]) - HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) - HiveProject(i_category=[$0], *=[*(1.2, CAST(/($1, $2)):DECIMAL(16, 6))]) - HiveAggregate(group=[{12}], agg#0=[sum($5)], agg#1=[count($5)]) - HiveFilter(condition=[IS NOT NULL($12)]) - HiveTableScan(table=[[default, item]], table:alias=[j]) - HiveProject(cnt=[$0]) - HiveFilter(condition=[<=(sq_count_check($0), 1)]) - HiveProject(cnt=[$0]) - HiveAggregate(group=[{}], cnt=[COUNT()]) - HiveProject(d_month_seq=[$0]) - HiveAggregate(group=[{3}]) - HiveFilter(condition=[AND(=($6, 2000), =($8, 2))]) - HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(i_category=[$0], *=[*(1.2, CAST(/($1, $2)):DECIMAL(16, 6))]) + HiveAggregate(group=[{12}], agg#0=[sum($5)], agg#1=[count($5)]) + HiveFilter(condition=[IS NOT NULL($12)]) + HiveTableScan(table=[[default, item]], table:alias=[j])
