hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "steve, Oh (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-22051) Hiveserver2 OOM when many subqueries using join in select clause
Date Fri, 26 Jul 2019 02:42:00 GMT

     [ https://issues.apache.org/jira/browse/HIVE-22051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

steve, Oh updated HIVE-22051:
-----------------------------
    Attachment: stack1.log

> Hiveserver2 OOM when many subqueries using join in select clause
> ----------------------------------------------------------------
>
>                 Key: HIVE-22051
>                 URL: https://issues.apache.org/jira/browse/HIVE-22051
>             Project: Hive
>          Issue Type: Bug
>          Components: CBO
>    Affects Versions: 3.1.0
>            Reporter: steve, Oh
>            Priority: Critical
>         Attachments: stack1.log
>
>
> If you perform a lot of the same join on the subquery in the select clause, hiveserver2
out of memory occur.
> Below is a reproducible query. 
> (It is not for real use query, but for reproduction.)
> data : tpcds 10GB
> [https://github.com/hortonworks/hive-testbench]
>  
> {code:java}
> explain
> select
>  ss.ss_ticket_number
>  , ss.ss_quantity
>  , ss.ss_wholesale_cost
>  , ss.ss_list_price
>  , c.c_first_name
>  , c.c_last_name
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 1) as promo_1
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 2) as promo_2
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 3) as promo_3
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 4) as promo_4
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 5) as promo_5
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 6) as promo_6
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 7) as promo_7
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 8) as promo_8
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 9) as promo_9
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 10) as promo_10
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 11) as promo_11
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 12) as promo_12
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 13) as promo_13
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 14) as promo_14
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 15) as promo_15
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 16) as promo_16
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 17) as promo_17
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 18) as promo_18
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 19) as promo_19
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 20) as promo_20
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 21) as promo_21
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 22) as promo_22
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 23) as promo_23
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 24) as promo_24
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 25) as promo_25
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 26) as promo_26
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 27) as promo_27
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 28) as promo_28
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 29) as promo_29
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 30) as promo_30
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 31) as promo_31
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 32) as promo_32
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 33) as promo_33
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 34) as promo_34
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 35) as promo_35
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 36) as promo_36
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 37) as promo_37
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 38) as promo_38
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 39) as promo_39
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 40) as promo_40
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 41) as promo_41
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 42) as promo_42
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 43) as promo_43
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 44) as promo_44
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 45) as promo_45
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 46) as promo_46
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 47) as promo_47
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 48) as promo_48
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 49) as promo_49
> ,(select p_promo_name from promotion p where ss.ss_item_sk != p.p_promo_sk and p.p_promo_sk
= 50) as promo_50
> from store_sales ss
> inner join customer c
> on ss.ss_customer_sk = c.c_customer_sk
> ;{code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

Mime
View raw message