[KYLIN-4984] KYLIN3.1.1 left join时sum结果为NULL

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: v3.1.1
• Component/s: Query Engine
• Assignee: Unassigned
• Reporter: yaping
• Created: 21/Apr/21 06:39
• Updated: 21/Apr/21 10:19

Description

包含left join后sum的语句，在版本2.5.2上执行sum结果是正常的，但该语句在3.1.1版本上sum结果为NULL，现象跟 https://issues.apache.org/jira/browse/KYLIN-3712很像。

 

sum(salescount)， sum(sales_amount)返回结果为NULL。

--------------------

SELECT '全部' zpjt,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' manu,'全部' pinpai,'全部' level,'全部' pack_level,SELECT '全部' zpjt,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' manu,'全部' pinpai,'全部' level,'全部' pack_level,sum(salescount), sum(sales_amount), a.sku_storenum, a.a_sku_number, a.a_cuxiao, a.a_dis, a.a_on_sale, b.lastsaleroom, b.lastsales_numbers, b.sku_number, b.sku_stores,  b.cuxiao, b.dis, b.on_sale, c.ko_amount, c.ko_count, d.lastko_amount, d.lastko_count, store.store_num, store.store_orders, laststore.laststore_num, laststore.laststore_orders, allstore.allstores, lastoff.lastoff,    a.per_ucFROM sku_all_2021_q1_hive_56city LEFT JOIN ( SELECT count AS a_sku_number, count( DISTINCT storeid_copy ) as sku_storenum, sum(cuxiao) as a_cuxiao, sum(dis) as a_dis, sum(uc) as per_uc, sum(on_sale) as a_on_sale,'全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' manu,'全部' pinpai,'全部' level,'全部' pack_level  FROM sku_all_2021_q1_hive_56city  GROUP BY zpjt, city_level, platform, channel, level, manu, pinpai, pack_level, type ) AS a ON(1=1)  LEFT JOIN ( SELECT sum( sales_amount ) AS lastsaleroom, sum(salescount) as lastsales_numbers, count AS sku_number, count( DISTINCT storeid_copy ) as sku_stores, sum(cuxiao) cuxiao, sum(dis) as dis, sum(on_sale) as on_sale,'全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' manu,'全部' pinpai,'全部' level,'全部' pack_level  FROM sku_all_2020_q4_hive_56city  GROUP BY zpjt, city_level, platform, channel, level, manu, pinpai, pack_level, type  ) AS b ON(1=1)  LEFT JOIN (SELECT   '全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' pinpai,'全部' level,'全部' pack_level, sum(sales_amount) ko_amount, sum(salescount) ko_count FROM sku_all_2021_q1_hive_56city   GROUP BY zpjt, city_level, platform, channel, type, pinpai, level, pack_level ) AS c ON (1=1) LEFT JOIN ( SELECT  '全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,'全部' type,'全部' pinpai,'全部' level,'全部' pack_level,  sum(sales_amount) lastko_amount, sum(salescount) lastko_count FROM sku_all_2020_q4_hive_56city   GROUP BY zpjt, city_level, platform, channel, type, pinpai, level, pack_level ) AS d ON (1=1) LEFT JOIN ( SELECT  '全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,COUNT(DISTINCT ID) store_num,     sum(salecount) store_orders FROM stores_all_2021_q1_hive_56city  GROUP BY zpjt, city_level, platform, channel ) AS store ON (1=1) LEFT JOIN ( SELECT  '全部' zpjt ,'全部' city_level,'全部' channel,'全部' platform,COUNT(DISTINCT ID) laststore_num,     sum(salecount) laststore_orders FROM stores_all_2020_q4_hive_56city GROUP BY zpjt, city_level, platform, channel ) AS laststore ON (1=1) LEFT JOIN ( SELECT  '全部' zpjt ,'全部' city_level,'全部' channel,sum(xxmd)*3 allstores FROM xxmd_2021_q1  GROUP BY zpjt, city_level, channel ) AS allstore ON (1=1) LEFT JOIN ( SELECT  '全部' zpjt ,'全部' city_level,'全部' channel,sum(xxmd)*3 lastoff FROM xxmd_2020_q4   GROUP BY zpjt, city_level, channel ) AS lastoff ON (1=1) GROUP BY level, pack_level, zpjt, city_level, channel, platform, type , manu, pinpai, a.a_sku_number, a.a_cuxiao, a.a_dis, a.a_on_sale,    b.lastsaleroom, b.lastsales_numbers, b.sku_number, b.cuxiao, b.sku_stores, b.dis, c.ko_amount, c.ko_count, d.lastko_amount, d.lastko_count, store.store_num, store.store_orders, laststore.laststore_num, laststore.laststore_orders, allstore.allstores, lastoff.lastoff, a.per_uc, a.sku_storenum, b.on_sale