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)