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)
