[ https://issues.apache.org/jira/browse/KYLIN-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326309#comment-17326309 ]
Xiaoxiang Yu commented on KYLIN-4984: ------------------------------------- Could you please provided a sample query on kylin_sales to reproduce this issue? thx! 你能尝试在 Kylin 官方样例数据集 KylinSales上复现该问题嘛? > KYLIN3.1.1 left join时sum结果为NULL > ------------------------------- > > Key: KYLIN-4984 > URL: https://issues.apache.org/jira/browse/KYLIN-4984 > Project: Kylin > Issue Type: Bug > Components: Query Engine > Affects Versions: v3.1.1 > Reporter: yaping > Priority: Major > > 包含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 -- This message was sent by Atlassian Jira (v8.3.4#803005)