[KYLIN-4582] Query with count distinct and grouping sets get wrong result

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: v2.5.2
• Fix Version/s: v3.1.4
• Component/s: Query Engine
• Environment: hdp 2.6
• Assignee: Unassigned
• Reporter: YaoChen
• Created: 19/Jun/20 03:52
• Updated: 04/Nov/21 06:27

Description

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:

group by grouping sets(
(a.period_id,a.company_name,a.province,a.new_flag),
(a.period_id,a.company_name,a.new_flag))

full 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))

But if we add one or more grouping the query result (count distinct(user_id)) is wrong.

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))

full 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)
)

 

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.

Issue Links

• is related to: KYLIN-5001 When using the "group by cube(...)" query statement, the query result is abnormal