[
https://issues.apache.org/jira/browse/KYLIN-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326421#comment-17326421
]
Xiaoxiang Yu commented on KYLIN-4984:
-------------------------------------
Is there more SQL that goes wrong like this? Can you explore more and see what
more features there are in the wrong query? The features provided at present,
including left join and sum measure, are relatively difficult for me to
reproduce this phenomenon.
这样出错的SQL 比较多吗？你能不能多探究一下，看看出错的查询有什么更多的特征，目前提供的特点，包括left join和sum度量，相对不容易让我来复现该现象。
> 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)
