Null query result when using SQL CASE expression
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Affected Version
Druid 32.0.0
### Description
After upgrading to version 32, where the `druid.generic.useDefaultValueForNull=true` feature is deprecated, we’re getting incorrect results when executing SQL queries using `CASE`.
The query is supposed to return a result based on a given condition, but when a type value doesn’t match any condition, it returns NULL instead of the expected ELSE value or COALESCE result.
```
SELECT
t.id,
SUM(CASE WHEN type = 'X'
THEN NVL(amount, 0)
ELSE 0
END) AS total_amount
FROM
example t
GROUP BY t.id
```
We followed all the steps in the [migration guide](https://druid.apache.org/docs/latest/release-info/migr-ansi-sql-null/), and no null values were inserted during ingestion. All raw data contains non-null values.

Contributor guide
Research direction
Start by reproducing the SQL query against Druid 32.0.0 with druid.generic.useDefaultValueForNull=true deprecated, using non-null ingested data and a type value that matches no condition. Compare the CASE ELSE and COALESCE results; done when the query returns the expected non-NULL total.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100