You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "JRobTS (via GitHub)" <gi...@apache.org> on 2025/09/22 16:33:39 UTC

[I] NVL and COALESCE does not work on RIGHT JOIN (druid)

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: commits-unsubscribe@druid.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org

Re: [I] NVL and COALESCE does not work on RIGHT JOIN (druid)

Posted by "JRobTS (via GitHub)" <gi...@apache.org>.
JRobTS commented on issue #18559:
URL: https://github.com/apache/druid/issues/18559#issuecomment-4846020111

   This issue is still relevant.


-- 
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: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org