[jira] [Updated] (KYLIN-4582) Query with count distinct and grouping sets get wrong result

["Yaqian Zhang (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/KYLIN-4582?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yaqian Zhang updated KYLIN-4582:
--------------------------------
    Fix Version/s: v3.1.4

> Query with count distinct and grouping sets get wrong result
> ------------------------------------------------------------
>
>                 Key: KYLIN-4582
>                 URL: https://issues.apache.org/jira/browse/KYLIN-4582
>             Project: Kylin
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: v2.5.2
>         Environment: hdp 2.6
>            Reporter: YaoChen
>            Priority: Critical
>             Fix For: v3.1.4
>
>         Attachments: count_distinct_grouping_sets_wrong_result.png, right_result.png
>
>
> We find that query with count distinct and grouping sets get wrong result when number of  grouping sets combination > 2;
> if we change count distinct to count or sum the result is correct.
> Query result is correct when grouping have two sets as below:
> {code:sql}
> group by grouping sets(
> (a.period_id,a.company_name,a.province,a.new_flag),
> (a.period_id,a.company_name,a.new_flag))
> {code}
> full sql:
> {code:sql}
> select period_id,
>        case grouping(a.company_name) when 1 then 'ALL' else a.company_name end company_name,
>        case grouping(a.province) when 1 then 'ALL' else a.province end province,
>        case grouping(a.new_flag) when 1 then 'ALL' else a.new_flag end new_flag,
>        count(distinct user_id) as index2  --'users'
> from (
> select monthid as period_id,
>        case company_id when '1' then 'xx1' when '2' then 'xx2'
>        end as company_name,
>        province_name as province,
>        case new_user_flag when '1' then '新增' when '2' then '存量' when '3' then '存量' end as new_flag,
>        user_id
> from rpt_culture_order_m
> where monthid between '202005' and '{202005}' and order_flag=2 and company_id=4
> ) a 
> group by grouping sets(
> (a.period_id,a.company_name,a.province,a.new_flag),
> (a.period_id,a.company_name,a.new_flag))
> {code}
> !right_result.png|width=843,height=547!
> *{color:#ff0000}But if we add one or more grouping the query result (count distinct(user_id)) is wrong.{color}*
> {code:sql}
> group by grouping sets(
> (a.period_id,a.company_name,a.province,a.new_flag),
> (a.period_id,a.company_name,a.new_flag),
> (a.period_id,a.company_name))
> {code}
> full sql:
> {code:sql}
> select period_id,
>        case grouping(a.company_name) when 1 then 'ALL' else a.company_name end company_name,
>        case grouping(a.province) when 1 then 'ALL' else a.province end province,
>        case grouping(a.new_flag) when 1 then 'ALL' else a.new_flag end new_flag,
>        count(distinct user_id) as index2  --'users'
> from (
> select monthid as period_id,
>        case company_id when '1' then 'xx1' when '2' then 'xx2'
>        end as company_name,
>        province_name as province,
>        case new_user_flag when '1' then '新增' when '2' then '存量' when '3' then '存量' end as new_flag,
>        user_id
> from rpt_culture_order_m
> where monthid between '202005' and '{202005}' and order_flag=2 and company_id=4
> ) a 
> group by grouping sets(
> (a.period_id,a.company_name,a.province,a.new_flag),
> (a.period_id,a.company_name,a.new_flag),
> (a.period_id,a.company_name)
> )
> {code}
>  
>  !count_distinct_grouping_sets_wrong_result.png!
> The result of '新增‘ （column ：INDEX2） should be 3170752 instead of 34808840 which is equal with the result of 'all'.
> count distinct result of  "All" dimension =  roll up dimension.
> Further, we have test that when change the aggregation from count distinct to count or sum the result is right.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)