NVL and COALESCE does not work on RIGHT JOIN
- 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
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