[GitHub] [spark] turboFei removed a comment on issue #24992: [SPARK-28194][SQL] Judge whether to reorder joinKeys to prevent None.get in EnsureRequirements

Tue, 02 Jul 2019 02:02:11 -0700 

turboFei removed a comment on issue #24992:  [SPARK-28194][SQL] Judge whether 
to reorder joinKeys to prevent None.get in EnsureRequirements
URL: https://github.com/apache/spark/pull/24992#issuecomment-507145891
 
 
   > I just want to know an example user's query to throw the exception. What 
was your query to hit the issue?
   @maropu 
   My query:
   ```
   SELECT *
   FROM (
     SELECT
         TO_DATE(goodsid2buname.day) day,
         add_cart.goods_id,
         goods_info.product_name,
         goodsid2buname.bu_name,
         goods_cat_brand.brand_name,
         goods_cat_brand.cat1_name, goods_cat_brand.cat2_name, 
goods_cat_brand.cat3_name, goods_cat_brand.cat4_name, goods_cat_brand.cat5_name,
         goods_info.cat_leaf_name,
         add_cart.add_cart_uv,
         goods_info.online_status,
         goods_info.suggest_price,
         analysis.pv,
         analysis.uv,
         analysis.num_items_sold,
         analysis.inctax_sale_amt,
    analysis.conversion_rate
     FROM (
         SELECT
             DISTINCT
             day,
             CAST(goods_id AS STRING) goods_id,
             bu_name
         FROM haitao_dev_log.dim_kl_itm_goods_bu_df
         WHERE day = DATE_ADD(CURRENT_DATE(), -1)
     ) goodsid2buname
     JOIN (
         SELECT
             day,
             TO_DATE(cart_datetime) add_cart_day, 
             goods_id,
             COUNT(DISTINCT account_id) add_cart_uv
         FROM haitao_dev_log.dwd_kl_prm_preheat_cart_goods_df  
         WHERE day = DATE_ADD(CURRENT_DATE(), -1)
         GROUP BY day, goods_id, TO_DATE(cart_datetime)
     ) add_cart
     ON goodsid2buname.day = add_cart.day
         AND goodsid2buname.goods_id = add_cart.goods_id
         AND goodsid2buname.day = add_cart.add_cart_day
     JOIN (
         SELECT
             day, goods_id, product_name, cat_leaf_name,
             AVG(suggest_price) suggest_price,
             MAX(online_status) online_status 
         FROM haitao_dev_log.dim_kl_itm_product
    WHERE day = DATE_ADD(CURRENT_DATE(), -1)
         GROUP BY day, goods_id, product_name, cat_leaf_name
     ) goods_info
     ON goodsid2buname.day = goods_info.day
         AND goodsid2buname.goods_id = goods_info.goods_id
     JOIN (
         SELECT
             day,
             page_id goods_id,
             COUNT(IF(page_type = 'productPage' AND log_type = 'pv', 
deviceudid, NULL)) pv, 
             COUNT(DISTINCT IF(page_type = 'productPage' AND log_type = 'pv', 
deviceudid, NULL)) uv,  
             SUM(IF(page_type = 'productPage' AND log_type = 'trade', item_cnt, 
0)) num_items_sold, 
             SUM(IF(page_type = 'productPage' AND log_type = 'trade', 
inctax_sale_amt, 0)) inctax_sale_amt, 
             COUNT(DISTINCT IF(page_type = 'productPage' AND log_type = 
'trade', buy_account, NULL)) num_buyers,
             COUNT(DISTINCT IF(page_type = 'productPage' AND log_type = 
'trade', buy_account, NULL)) / COUNT(DISTINCT IF(page_type = 'productPage' AND 
log_type = 'pv', deviceudid, NULL))  conversion_rate 
         FROM haitao_dev_log.dwd_kl_flw_analysis_1d
         WHERE day = DATE_ADD(CURRENT_DATE(), -1)
    AND page_type = 'productPage'
         GROUP BY day, page_id
     ) analysis
     ON goodsid2buname.day = analysis.day
         AND goodsid2buname.goods_id = analysis.goods_id
     JOIN (
         SELECT DISTINCT
             goods_id,
             brand_name,
             cat1_name, cat2_name, cat3_name, cat4_name, cat5_name
         FROM haitao_dev_log.dim_kl_itm_bg_sku_wh_f
     ) goods_cat_brand
     ON goodsid2buname.goods_id = goods_cat_brand.goods_id
   ) `t`;
   ```
   And the debug info is：
   
![image](https://user-images.githubusercontent.com/6757692/60417518-34767180-9c13-11e9-8975-387fd98bc5cf.png)
   
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
[email protected]


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to