You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "yaping (Jira)" <ji...@apache.org> on 2021/04/21 06:40:00 UTC
[jira] [Created] (KYLIN-4984) KYLIN3.1.1 left join时sum结果为NULL
yaping created KYLIN-4984:
-----------------------------
Summary: 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
包含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)