apache / apache/druid

Null query result when using SQL CASE expression

Open
#17,768 4 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

### 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.

![Image](https://github.com/user-attachments/assets/200e9b0d-7932-4722-b29a-6719081740e4)

Contributor guide

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.