[KYLIN-5570] Incorrect result produced by the query with grouping sets

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.0-alpha
• Fix Version/s: 5.0-beta
• Component/s: Query Engine
• Assignee: Guangyuan Feng
• Reporter: Guangyuan Feng
• Created: 08/Jun/23 07:59
• Updated: 14/Jun/23 02:24
• Resolved: 14/Jun/23 02:24

Description

The following SQL pattern will produce the wrong result when hitting a model. In such a case, assuming each group contains 2 rows, this query will return 10 rows by KE, rather than 18 rows actually.

//代码占位符
select
GROUPING(gr1) as gr1,
GROUPING(gr2) as gr2,
GROUPING(gr3) as gr3,
GROUPING(gr4) as gr4,
GROUPING(gr5) as gr5,
GROUPING(gr6) as gr6,
GROUPING(gr7) as gr7,
GROUPING(gr8) as gr8,
GROUPING(gr9) as gr9,
count(distinct case when 1=1 then LO_ORDERKEY else null end) as goal_group
from(
select
case when LO_ORDERPRIOTITY = '1-URGENT' then '立刻发出' else '延后发出' end gr1,
case when LO_SHIPMODE = 'AIR' then '空运' else '海运' end gr2,
case when LO_LINENUMBER = 1 then '1' else '0' end gr3,
case when LO_CUSTKEY = 1 then '1' else '0' end gr4,
case when LO_PARTKEY = 1 then '1' else '0' end gr5,
case when LO_SUPPKEY = 1 then '1' else '0' end gr6,
case when LO_QUANTITY = 1 then '1' else '0' end gr7,
case when LO_EXTENDEDPRICE = 90400 then '1' else '0' end gr8,
case when LO_TAX = 0 then '1' else '0' end gr9,
LO_ORDERKEY
from
SSB.LINEORDER
)
group by
GROUPING SETS
(
(gr1),
(gr2),
(gr3),
(gr4),
(gr5),
(gr6),
(gr7),
(gr8),
(gr9)
)
order by 1,2,3,4,5,6,7,8,9
LIMIT 500

 

A simple resolution is to add groupByColumns ids into OLAPProjectRel node's digest field.