[
https://issues.apache.org/jira/browse/HIVE-23493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17112996#comment-17112996
]
Hive QA commented on HIVE-23493:
--------------------------------
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/13003577/HIVE-23493.1.patch
{color:red}ERROR:{color} -1 due to no test(s) being added or modified.
{color:red}ERROR:{color} -1 due to 38 failed/errored test(s), 17277 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestBeeLineDriver.testCliDriver[materialized_view_create_rewrite]
(batchId=233)
org.apache.hadoop.hive.cli.TestMiniDruidCliDriver.testCliDriver[druid_timestamptz2]
(batchId=131)
org.apache.hadoop.hive.cli.TestMiniDruidCliDriver.testCliDriver[druidmini_test_insert]
(batchId=131)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[constraints_optimization]
(batchId=81)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[join_constraints_optimization]
(batchId=41)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[masking_mv]
(batchId=114)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_create_rewrite]
(batchId=33)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_create_rewrite_dummy]
(batchId=84)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_create_rewrite_multi_db]
(batchId=100)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_1]
(batchId=94)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_2]
(batchId=53)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_3]
(batchId=120)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_4]
(batchId=36)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_5]
(batchId=54)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_6]
(batchId=102)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_7]
(batchId=37)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_in_between]
(batchId=76)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_no_join_opt]
(batchId=123)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_no_join_opt_2]
(batchId=112)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_part_1]
(batchId=49)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[materialized_view_rewrite_part_2]
(batchId=31)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[nullability_transitive_inference]
(batchId=64)
org.apache.hadoop.hive.cli.TestTezPerfCliDriver.testCliDriver[cbo_query3]
(batchId=231)
org.apache.hadoop.hive.cli.TestTezPerfCliDriver.testCliDriver[cbo_query64]
(batchId=231)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_ext_query1]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_query11]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_query1]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_query3]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_query4]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[cbo_query74]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[query11]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[query1]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[query1b]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[query4]
(batchId=230)
org.apache.hadoop.hive.cli.TestTezPerfConstraintsCliDriver.testCliDriver[query74]
(batchId=230)
org.apache.hadoop.hive.ql.TestAcidOnTez.testGetSplitsLocksWithMaterializedView
(batchId=179)
org.apache.hadoop.hive.ql.parse.TestScheduledReplicationScenarios.testAcidTablesReplLoadBootstrapIncr
(batchId=208)
org.apache.hive.hcatalog.mapreduce.TestHCatPartitioned.testHCatPartitionedTable[6]
(batchId=145)
{noformat}
Test results:
https://builds.apache.org/job/PreCommit-HIVE-Build/22515/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/22515/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-22515/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.YetusPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 38 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 13003577 - PreCommit-HIVE-Build
> Rewrite plan to join back tables with many projected columns joined multiple
> times
> ----------------------------------------------------------------------------------
>
> Key: HIVE-23493
> URL: https://issues.apache.org/jira/browse/HIVE-23493
> Project: Hive
> Issue Type: New Feature
> Components: CBO
> Reporter: Krisztian Kasa
> Assignee: Krisztian Kasa
> Priority: Major
> Attachments: HIVE-23493.1.patch
>
>
> Queries with a pattern where one or more tables joins with a fact table in a
> CTE. Many columns are projected out those tables and then grouped in the CTE.
> The main query joins multiple instances of the CTE and may project a subset
> of these.
> The optimization is to rewrite the CTE to include only key (PK, non null
> Unique Key) columns and join the tables back to the resultset of the main
> query to fetch the rest of the wide columns. This reduces the datasize of the
> joined back tables that is broadcast/shuffled throughout the DAG processing.
> Example query, tpc-ds query4
> {code}
> with year_total as (
> select c_customer_id customer_id
> ,c_first_name customer_first_name
> ,c_last_name customer_last_name
> ,c_preferred_cust_flag customer_preferred_cust_flag
> ,c_birth_country customer_birth_country
> ,c_login customer_login
> ,c_email_address customer_email_address
> ,d_year dyear
>
> ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2)
> year_total
> ,'s' sale_type
> from customer
> ,store_sales
> ,date_dim
> where c_customer_sk = ss_customer_sk
> and ss_sold_date_sk = d_date_sk
> group by c_customer_id
> ,c_first_name
> ,c_last_name
> ,c_preferred_cust_flag
> ,c_birth_country
> ,c_login
> ,c_email_address
> ,d_year
> union all
> select c_customer_id customer_id
> ,c_first_name customer_first_name
> ,c_last_name customer_last_name
> ,c_preferred_cust_flag customer_preferred_cust_flag
> ,c_birth_country customer_birth_country
> ,c_login customer_login
> ,c_email_address customer_email_address
> ,d_year dyear
>
> ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2)
> ) year_total
> ,'c' sale_type
> from customer
> ,catalog_sales
> ,date_dim
> where c_customer_sk = cs_bill_customer_sk
> and cs_sold_date_sk = d_date_sk
> group by c_customer_id
> ,c_first_name
> ,c_last_name
> ,c_preferred_cust_flag
> ,c_birth_country
> ,c_login
> ,c_email_address
> ,d_year
> union all
> select c_customer_id customer_id
> ,c_first_name customer_first_name
> ,c_last_name customer_last_name
> ,c_preferred_cust_flag customer_preferred_cust_flag
> ,c_birth_country customer_birth_country
> ,c_login customer_login
> ,c_email_address customer_email_address
> ,d_year dyear
>
> ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2)
> ) year_total
> ,'w' sale_type
> from customer
> ,web_sales
> ,date_dim
> where c_customer_sk = ws_bill_customer_sk
> and ws_sold_date_sk = d_date_sk
> group by c_customer_id
> ,c_first_name
> ,c_last_name
> ,c_preferred_cust_flag
> ,c_birth_country
> ,c_login
> ,c_email_address
> ,d_year
> )
> select
> t_s_secyear.customer_id
> ,t_s_secyear.customer_first_name
> ,t_s_secyear.customer_last_name
> ,t_s_secyear.customer_birth_country
> from year_total t_s_firstyear
> ,year_total t_s_secyear
> ,year_total t_c_firstyear
> ,year_total t_c_secyear
> ,year_total t_w_firstyear
> ,year_total t_w_secyear
> where t_s_secyear.customer_id = t_s_firstyear.customer_id
> and t_s_firstyear.customer_id = t_c_secyear.customer_id
> and t_s_firstyear.customer_id = t_c_firstyear.customer_id
> and t_s_firstyear.customer_id = t_w_firstyear.customer_id
> and t_s_firstyear.customer_id = t_w_secyear.customer_id
> and t_s_firstyear.sale_type = 's'
> and t_c_firstyear.sale_type = 'c'
> and t_w_firstyear.sale_type = 'w'
> and t_s_secyear.sale_type = 's'
> and t_c_secyear.sale_type = 'c'
> and t_w_secyear.sale_type = 'w'
> and t_s_firstyear.dyear = 1999
> and t_s_secyear.dyear = 1999+1
> and t_c_firstyear.dyear = 1999
> and t_c_secyear.dyear = 1999+1
> and t_w_firstyear.dyear = 1999
> and t_w_secyear.dyear = 1999+1
> and t_s_firstyear.year_total > 0
> and t_c_firstyear.year_total > 0
> and t_w_firstyear.year_total > 0
> and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total /
> t_c_firstyear.year_total else null end
> > case when t_s_firstyear.year_total > 0 then
> t_s_secyear.year_total / t_s_firstyear.year_total else null end
> and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total /
> t_c_firstyear.year_total else null end
> > case when t_w_firstyear.year_total > 0 then
> t_w_secyear.year_total / t_w_firstyear.year_total else null end
> order by t_s_secyear.customer_id
> ,t_s_secyear.customer_first_name
> ,t_s_secyear.customer_last_name
> ,t_s_secyear.customer_birth_country
> limit 100;
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
