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

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

steve, Oh created HIVE-22051:
--------------------------------

             Summary: 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
         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)