apache / apache/druid

Grouping Sets Behaviors Strangely On Druid with Filter conditions

Open
#9,966 1 comment 0 reactions 0 assignees View on GitHub
Area - SQL Bug
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

### Affected Version
0.18.1

### Description
We run a test on wikipedia datasource to test grouping sets SQL function.
When the SQL is like this, the result come as expected.
`
select
countryName,
cityName,
sum(sum_deleted)
from
wikipedia
where
"__time" between TIMESTAMP '2016-01-01 00:00:00' and TIMESTAMP '2017-05-11 00:00:00'
group by GROUPING SETS ((countryName, cityName), (countryName), ())
`
However when we add some conditions in the where clause, the result doesn't show grouping set results as expected
`
select
countryName,
cityName,
sum(sum_deleted)
from
wikipedia
where
"__time" between TIMESTAMP '2016-01-01 00:00:00' and TIMESTAMP '2017-05-11 00:00:00'
and countryName in ('Argentina', 'Australia', 'Belgium')
and cityName in ('Buenos Aires', 'Canberra', 'Seventeen Mile Rocks', 'Antwerp')
group by GROUPING SETS ((countryName, cityName), (countryName), ())
`
Result:
Argentina Buenos Aires 238
Australia Canberra 342
Australia Seventeen Mile Rocks 79
Belgium Antwerp 66

which looks like only calculate group by countryName, cityName.

I run the data on Hive with conditions in the where clause and it turns out to have the right results
Result:
all all 725
Argentina all 238
Argentina Buenos Aires 238
Australia all 421
Australia Canberra 342
Australia Seventeen Mile Rocks 79
Belgium all 66
Belgium Antwerp 66

Contributor guide

Open the contributing guide

Research direction

Reproduce both GROUPING SETS queries from the issue against the Wikipedia datasource on Druid 0.18.1, first without and then with the countryName and cityName filters. Compare the output with the Hive results; the fix is done when the filtered query includes the country-level and grand-total grouping rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.