[
https://issues.apache.org/jira/browse/KYLIN-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326362#comment-17326362
]
yaping commented on KYLIN-4984:
-------------------------------
select KYLIN_SALES.PART_DT, sum(KYLIN_SALES.PRICE) from KYLIN_SALES inner join
KYLIN_ACCOUNT as TT on KYLIN_SALES.BUYER_ID = TT.ACCOUNT_ID where
TT.ACCOUNT_COUNTRY = 'US' group by KYLIN_SALES.PART_DT
这个语句查询，sum是有值的。
我的那条语句比这个复杂。有9个left join，这些left join中的数据是group by 统计，最外层包又包了个group by
，现在最外层的sum值为NULL。在2.5.2版本中是有值的，这个问题导致没法把在2.5.2上的数据迁到3.1.1版本上跑。
> 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)
