Calcite is too aggressive in simplifying a having filter on multi-value dimensions
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Affected Version
Druid 0.16.0 (and prob earlier)
### Description
With this dataset (as `fs`):
```json
{"time":"2019-08-14T00:00:00.000Z","srcGroups":["x","y","z"],"dstGroups":["a","b","c","d"]}
{"time":"2019-08-14T00:00:00.000Z","srcGroups":["x","y","z"],"dstGroups":["a","c","d"]}
{"time":"2019-08-14T00:00:00.000Z","srcGroups":["x","y","z"],"dstGroups":["a","g"]}
```
Doing this query:
```sql
SELECT
"dstGroups",
COUNT(*) AS "Count"
FROM "fs"
GROUP BY 1
HAVING "dstGroups" = 'a'
ORDER BY "Count" DESC
```
Yields:

Which is not what is expected.
The plan is:
```json
{
"queryType": "topN",
"dataSource": {
"type": "table",
"name": "fs"
},
"virtualColumns": [],
"dimension": {
"type": "default",
"dimension": "dstGroups",
"outputName": "d0",
"outputType": "STRING"
},
"metric": {
"type": "numeric",
"metric": "a0"
},
"threshold": 100,
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"filter": {
"type": "selector",
"dimension": "dstGroups",
"value": "a",
"extractionFn": null
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "count",
"name": "a0"
}
],
"postAggregations": [],
"descending": false
}
```
This is because Calcite converted the HAVING on a grouping key into a WHERE, which given Druid multi-value semantics is not correct to do.
A workaround is to prevent Calcite from simplifying the HAVING filter by complicating the HAVING into something that can not be simplified as easily.
```sql
SELECT
"dstGroups",
COUNT(*) AS "Count"
FROM "fs"
GROUP BY 1
HAVING "dstGroups" = 'a' OR "Count" < 0
ORDER BY "Count" DESC
```
Which yields:

Contributor guide
Research direction
Reproduce the SQL query with the supplied multi-value dataset and inspect Calcite's HAVING-to-WHERE planning path. Done means the HAVING predicate preserves the expected multi-value dimension semantics rather than producing the shown simplified topN filter, with the affected query behavior verified.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100