YaoChen created KYLIN-4582:
------------------------------
Summary: 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
Attachments: count_distinct_grouping_sets_wrong_result.png,
right_result.png
query result is correct when grouping sets is
{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 '咪咕数媒' when '2' then '咪咕动漫' when '3' then
'咪咕视讯' when '4' then '咪咕音乐' when '5' then '咪咕互娱' when '7' then '咪咕+'
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!
*{color:red}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}
!count_distinct_grouping_sets_wrong_result.png!
*result of '新增‘ = all*
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
