apache / apache/druid

NVL and COALESCE does not work on RIGHT JOIN

Open
#18,559 2 comments 0 reactions 0 assignees View on GitHub
Uncategorized problem report
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

When using NVL or COALESCE in a SQL query involving a NULL value from the right-side table, the function returns null instead of the expected value.

### Affected Version

Druid 33.0.0

### Description

Using this sample query:
```
SELECT ds1.Key, COALESCE(ds1.LastUsed, 'N/A'), COALESCE(ds2.LastUsed, 'N/A')
FROM (
SELECT Key, MAX(__time) AS LastUsed
FROM datasource1
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY 1
) ds1
FULL OUTER JOIN (
SELECT Key, MAX(__time) AS LastUsed
FROM datasource2
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY 1
) ds2 ON ds1.Key = ds2.Key
ORDER BY 1, COALESCE(ds2.LastUsed, ds1.LastUsed) DESC
```

Given data like:
```
datasource1

__time | Key
--------------------+----
2025-09-22T01:00:00 | 1
2025-09-22T02:00:00 | 2

datasource2

__time | Key
--------------------+----
2025-09-22T11:00:00 | 1
2025-09-22T13:00:00 | 3
```

Expected Result would be:
```
Key | EXPR$1 | EXPR$2
----+---------------------+--------------------
1 | 2025-09-22T01:00:00 | 2025-09-22T11:00:00
2 | 2025-09-22T02:00:00 | N/A
3 | N/A | 2025-09-22T13:00:00
```

Actual Result is:
```
Key | EXPR$1 | EXPR$2
----+---------------------+--------------------
1 | 2025-09-22T01:00:00 | 2025-09-22T11:00:00
2 | 2025-09-22T02:00:00 | null
3 | N/A | 2025-09-22T13:00:00
```

The same holds true for NVL. For example: `SELECT ds1.Key, NVL(ds1.LastUsed, CURRENT_TIMESTAMP), NVL(ds2.LastUsed, CURRENT_TIMESTAMP)` returns a null entry in EXPR$2

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the sample FULL OUTER JOIN query on Druid 33.0.0 and compare COALESCE and NVL results for NULL values from the right-side table. Trace the join and expression handling that produces the null result; done when both functions return their fallback values for the missing right-side row.

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
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.