JRobTS opened a new issue, #18559:
URL: https://github.com/apache/druid/issues/18559
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 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
----+---------------------+--------------------
3 | N/A | 2025-09-22T13:00:00
1 | 2025-09-22T01:00:00 | 2025-09-22T11:00:00
2 | 2025-09-22T02:00:00 | N/A
```
Actual Result is:
```
Key | EXPR$1 | EXPR$2
----+---------------------+--------------------
3 | N/A | 2025-09-22T13:00:00
1 | 2025-09-22T01:00:00 | 2025-09-22T11:00:00
2 | 2025-09-22T02:00:00 | null
```
The same holds true for NVL. For example: `SELECT Key, NVL(ds1.LastUsed,
CURRENT_TIMESTAMP), NVL(ds2.LastUsed, CURRENT_TIMESTAMP)` returns a null entry
in EXPR$2
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
