apache / apache/druid

Exact distinct-COUNT with complex expression (CASE, IN) throws NullPointerException

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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.