You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "carltal (via GitHub)" <gi...@apache.org> on 2025/05/30 20:45:30 UTC
[I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
carltal opened a new issue, #18058:
URL: https://github.com/apache/druid/issues/18058
### Affected Version
v33
### Description
Hi, this may be intentional from Druid, but we are just trying to see if there are any settings we can turn on to avoid this behavior. We have gone through https://druid.apache.org/docs/latest/tutorials/tutorial-sql-null/ but don't see much about this behavior.
Specifically: when there is completely no data in a query time range, and when applying aggregation such as SUM(payment_amount), the column for that aggregation will be NULL. Please see a screenshot attached.
Now we can add NVL to every aggregated column, but we are wondering if there is an easier way to do this (as adding NVL requires code change for our API). Thanks.
<img width="1271" alt="Image" src="https://github.com/user-attachments/assets/982d0621-6485-4c47-a891-82f007d8162d" />
--
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] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-2945833234
@carltal It depends on how the SQL query is translated. The planner makes some effort to translate `coalesce` in a low-impact way. Ideally for a situation like this, it will be translated to a post-aggregator, where it's applied just once on the result of the aggregation. You can check `EXPLAIN PLAN FOR` to see how the query is being translated.
--
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
Re: [I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "carltal (via GitHub)" <gi...@apache.org>.
carltal commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-2945849089
@gianm Ah I see. OK I will check it. May I know how I can tell if it is a post-aggregator from the plan? Thank you.
--
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
Re: [I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "carltal (via GitHub)" <gi...@apache.org>.
carltal commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-2945099416
@gianm Hi, a side question: how does coalesce affect performance in Druid? Does it have a big impact? Reason is that given this change of null behavior, we will have to wrap everything in a coalesce function. Thanks.
--
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
Re: [I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "zhang-arvin (via GitHub)" <gi...@apache.org>.
zhang-arvin commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-5353978644
I'd like to work on this issue. Could you please assign it to me?
--
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
Re: [I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-2936956612
The first one, `sum(pe1_0.payment_amount)`, is now `NULL` due to SQL compliance (this is how SQL expects `SUM` to behave). The behavior is no longer configurable, since Druid is now always in SQL compliant null-handling mode.
The second one is a bug, #17768. It should be returning 0 but it returns `NULL` due to an incorrect planner rule.
--
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
Re: [I] Druid v33 gives null value for each column in aggregation that does not have a value in the query time range (druid)
Posted by "carltal (via GitHub)" <gi...@apache.org>.
carltal commented on issue #18058:
URL: https://github.com/apache/druid/issues/18058#issuecomment-2942062377
@gianm Thank you for confirming! In this case we will have to wrap them all in coalesce function.
--
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