apache / apache/druid

incorrect druid SQL results using GROUP BY and COUNT

Open
#11,197 0 comments 0 reactions 0 assignees View on GitHub
Area - SQL
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.