[
https://issues.apache.org/jira/browse/HIVE-23493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17113020#comment-17113020
]
Hive QA commented on HIVE-23493:
--------------------------------
| (x) *{color:red}-1 overall{color}* |
\\
\\
|| Vote || Subsystem || Runtime || Comment ||
|| || || || {color:brown} Prechecks {color} ||
| {color:green}+1{color} | {color:green} @author {color} | {color:green} 0m
0s{color} | {color:green} The patch does not contain any @author tags. {color} |
|| || || || {color:brown} master Compile Tests {color} ||
| {color:blue}0{color} | {color:blue} mvndep {color} | {color:blue} 1m
8s{color} | {color:blue} Maven dependency ordering for branch {color} |
| {color:green}+1{color} | {color:green} mvninstall {color} | {color:green} 10m
27s{color} | {color:green} master passed {color} |
| {color:green}+1{color} | {color:green} compile {color} | {color:green} 8m
28s{color} | {color:green} master passed {color} |
| {color:green}+1{color} | {color:green} checkstyle {color} | {color:green} 3m
28s{color} | {color:green} master passed {color} |
| {color:blue}0{color} | {color:blue} findbugs {color} | {color:blue} 0m
40s{color} | {color:blue} common in master has 63 extant Findbugs warnings.
{color} |
| {color:blue}0{color} | {color:blue} findbugs {color} | {color:blue} 4m
35s{color} | {color:blue} ql in master has 1525 extant Findbugs warnings.
{color} |
| {color:green}+1{color} | {color:green} javadoc {color} | {color:green} 8m
1s{color} | {color:green} master passed {color} |
|| || || || {color:brown} Patch Compile Tests {color} ||
| {color:blue}0{color} | {color:blue} mvndep {color} | {color:blue} 0m
14s{color} | {color:blue} Maven dependency ordering for patch {color} |
| {color:green}+1{color} | {color:green} mvninstall {color} | {color:green} 11m
26s{color} | {color:green} the patch passed {color} |
| {color:green}+1{color} | {color:green} compile {color} | {color:green} 8m
25s{color} | {color:green} the patch passed {color} |
| {color:green}+1{color} | {color:green} javac {color} | {color:green} 8m
25s{color} | {color:green} the patch passed {color} |
| {color:red}-1{color} | {color:red} checkstyle {color} | {color:red} 0m
19s{color} | {color:red} common: The patch generated 1 new + 377 unchanged - 0
fixed = 378 total (was 377) {color} |
| {color:red}-1{color} | {color:red} checkstyle {color} | {color:red} 1m
1s{color} | {color:red} ql: The patch generated 9 new + 106 unchanged - 1 fixed
= 115 total (was 107) {color} |
| {color:red}-1{color} | {color:red} checkstyle {color} | {color:red} 2m
19s{color} | {color:red} root: The patch generated 10 new + 483 unchanged - 1
fixed = 493 total (was 484) {color} |
| {color:green}+1{color} | {color:green} whitespace {color} | {color:green} 0m
0s{color} | {color:green} The patch has no whitespace issues. {color} |
| {color:red}-1{color} | {color:red} findbugs {color} | {color:red} 1m
16s{color} | {color:red} patch/common cannot run setBugDatabaseInfo from
findbugs {color} |
| {color:red}-1{color} | {color:red} findbugs {color} | {color:red} 8m
8s{color} | {color:red} patch/ql cannot run setBugDatabaseInfo from findbugs
{color} |
| {color:green}+1{color} | {color:green} javadoc {color} | {color:green} 15m
33s{color} | {color:green} the patch passed {color} |
|| || || || {color:brown} Other Tests {color} ||
| {color:green}+1{color} | {color:green} asflicense {color} | {color:green} 0m
20s{color} | {color:green} The patch does not generate ASF License warnings.
{color} |
| {color:black}{color} | {color:black} {color} | {color:black} 86m 40s{color} |
{color:black} {color} |
\\
\\
|| Subsystem || Report/Notes ||
| Optional Tests | asflicense javac javadoc findbugs checkstyle compile |
| uname | Linux hiveptest-server-upstream 3.16.0-4-amd64 #1 SMP Debian
3.16.43-2+deb8u5 (2017-09-19) x86_64 GNU/Linux |
| Build tool | maven |
| Personality |
/data/hiveptest/working/yetus_PreCommit-HIVE-Build-22515/dev-support/hive-personality.sh
|
| git revision | master / 29db9e6 |
| Default Java | 1.8.0_111 |
| findbugs | v3.0.1 |
| checkstyle |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus/diff-checkstyle-common.txt
|
| checkstyle |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus/diff-checkstyle-ql.txt
|
| checkstyle |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus/diff-checkstyle-root.txt
|
| findbugs |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus/patch-findbugs-common.txt
|
| findbugs |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus/patch-findbugs-ql.txt
|
| modules | C: common ql . U: . |
| Console output |
http://104.198.109.242/logs//PreCommit-HIVE-Build-22515/yetus.txt |
| Powered by | Apache Yetus http://yetus.apache.org |
This message was automatically generated.
> 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)
