apache / apache/druid

Druid SQL Differing Results When Grouping By 1 Column vs 1 Col With Dummy Column

Open
#10,011 11 comments 0 reactions 0 assignees View on GitHub
Area - Querying Bug
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

### Affected Version
Build From Source as of Fri Jun 5

### Description
I loaded test data using this spec:

```
{
"type": "index_parallel",
"spec": {
"ioConfig": {
"type": "index_parallel",
"inputSource": {
"type": "inline",
"data": "a|b"
},
"inputFormat": {
"type": "csv",
"columns": [
"col1"
],
"findColumnsFromHeader": false,
"listDelimiter": "|"
}
},
"tuningConfig": {
"type": "index_parallel",
"partitionsSpec": {
"type": "dynamic"
}
},
"dataSchema": {
"dataSource": "inline_data",
"granularitySpec": {
"type": "uniform",
"queryGranularity": "NONE",
"rollup": false,
"segmentGranularity": "HOUR"
},
"timestampSpec": {
"column": "!!!_no_such_column_!!!",
"missingValue": "2010-01-01T00:00:00Z"
},
"dimensionsSpec": {
"dimensions": []
}
}
}
}
```

Then tried these two queries:

```
SELECT col1, count(*) from inline_data where col1='a' GROUP BY col1
SELECT col1, count(*) from inline_data where col1='a' GROUP BY col1, 'IGNORED'
```

The first query returns two rows, one for a and the other for b.
The second query returns only a row for 'a' - this is what I expected the first query to return, based on previous 'group by' queries that I had run and the description that 'group by' was supposed to act similar to an unnest/explode for multi-value dimensions

I'm fairly certain that the issue is the difference between a 'TopN' query and a 'groupBy' query. If I just swap queryType 'topN' for 'groupBy', I get only one record back instead of two.

Perhaps we need to use groupBy instead of topN if there is a where clause?

Also, "useApproximateTopN": false seems to be ingored. From the documentation, it sounded like this should replace topN with groupBy, but it doesn't seem to have that effect.

Contributor guide

Open the contributing guide

Research direction

Start with the supplied ingestion specification and run the two SQL queries against the resulting inline_data datasource. Compare the query types and the effect of the WHERE clause and useApproximateTopN setting; done means grouping produces consistent, documented results for both queries and the configuration behaves as described.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
data, 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.