Exact distinct-COUNT with complex expression (CASE, IN) throws NullPointerException
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
Exact distinct-COUNT fails at planning time if the expression is complex. In this case the expression involved CASE and IN. Here's the query:
```
SELECT COUNT(DISTINCT
CASE
WHEN ((
(CASE
WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
THEN REPLACE('Yes', 'Yes', 'Yes')
ELSE REPLACE('No', 'No', 'No')
END) = 'No'))
AND (wikipedia.isRobot = 'true')
THEN (wikipedia."user")
ELSE NULL END)
- (MAX(CASE WHEN ((
(CASE
WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
THEN REPLACE('Yes', 'Yes', 'Yes')
ELSE REPLACE('No', 'No', 'No') END) = 'No'))
AND (wikipedia.isRobot = 'true')
THEN NULL
ELSE -9223372036854775807 END)
+ 9223372036854775807 + 1) AS "wikipedia.count_distinct_filters_that_dont_work"
FROM druid.wikipedia AS wikipedia
LIMIT 500
```
### Affected Version
I don't know. I assume a fairly recent version.
### Description
Query was generated by Looker.
Error stack:
> Error -1 (00000) : Error while executing SQL "-- Looker Query Context '{"user_id":311,"history_id":134834,"instance_slug":"f9d334b901ca021473c05727fa02d92c"}' SELECT COUNT(DISTINCT CASE WHEN (((CASE WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) THEN REPLACE('Yes', 'Yes', 'Yes') ELSE REPLACE('No', 'No', 'No') END) = 'No')) AND (wikipedia.isRobot = 'true') THEN (wikipedia."user") ELSE NULL END) - (MAX(CASE WHEN (((CASE WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) THEN REPLACE('Yes', 'Yes', 'Yes') ELSE REPLACE('No', 'No', 'No') END) = 'No')) AND (wikipedia.isRobot = 'true') THEN NULL ELSE -9223372036854775807 END) + 9223372036854775807 + 1) AS "wikipedia.count_distinct_filters_that_dont_work" FROM druid.wikipedia AS wikipedia LIMIT 500": Remote driver error: RuntimeException: Error while applying rule AggregateExpandDistinctAggregatesRule, args [rel#7148:LogicalAggregate.NONE.[](input=rel#7147:Subset#7.NONE.[],group={},agg#0=COUNT(DISTINCT $2) FILTER $3,agg#1=MAX($4) FILTER $5)] -> NullPointerException: (null exception message)
```
Contributor guide
Research direction
Start by reproducing the supplied SQL query and inspect the AggregateExpandDistinctAggregatesRule entry point named in the planner error. Trace how the complex CASE and IN expressions reach that rule. Done means the query completes planning without a NullPointerException and its result remains correct.
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