incorrect druid SQL results using GROUP BY and COUNT
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
Some druid SQL queries including GROUP BY and COUNT return incorrect results. I'm including an example with the wikipedia dataset.
### Affected Version
Seen in 0.20.0 and 0.21.0.
### Description
Particular GROUP BY queries are returning 0 counts when they should return non-zero numbers. Providing two examples, one with wikipedia, and one with a custom data set (attached).
With wikipedia, we can see the following, to zero in on the issue:
This query returns various non-zero numbers for the counts:
```
select comment, count(*) FROM wikipedia
WHERE channel = '#ar.wikipedia'
GROUP BY comment
```
This query will return 1's, which makes sense:
```
select comment, count(distinct(channel)) FROM wikipedia
WHERE channel = '#ar.wikipedia'
GROUP BY comment
```
So, we have data for the channel field (which makes sense, since we filter for a channel), but the following query returns 0's for all the counts, which is not only incorrect, but logically impossible given the query:
```
select comment, count(channel) FROM wikipedia
WHERE channel = '#ar.wikipedia'
GROUP BY comment
```
Contributor guide
Research direction
Reproduce the three GROUP BY queries against the Wikipedia dataset on the affected Druid versions, 0.20.0 and 0.21.0, and compare COUNT(*), COUNT(DISTINCT channel), and COUNT(channel). Trace the SQL aggregation path responsible for the results; done means COUNT(channel) returns the expected non-zero counts for the filtered 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
- 35/100