[jira] [Updated] (HIVE-22051) Hiveserver2 OOM when many subqueries using join in select clause

["steve, Oh (JIRA)" <ji...@apache.org>]

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