Repository navigation
Druid v33 gives null value for each column in aggregation that does not have a value in the query time range #18058
Description
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.
Activity
gianm commentedon Jun 3, 2025
The first one,
sum(pe1_0.payment_amount), is nowNULLdue to SQL compliance (this is how SQL expectsSUMto 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
NULLdue to an incorrect planner rule.carltal commentedon Jun 5, 2025
@gianm Thank you for confirming! In this case we will have to wrap them all in coalesce function.
carltal commentedon Jun 5, 2025
@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.
gianm commentedon Jun 5, 2025
@carltal It depends on how the SQL query is translated. The planner makes some effort to translate
coalescein 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 checkEXPLAIN PLAN FORto see how the query is being translated.carltal commentedon Jun 5, 2025
@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.
zhang-arvin commentedon Aug 20, 2026
I'd like to work on this issue. Could you please assign it to me?
fix: use SUM0 in AggregateCaseToFilter D1 rewrite to avoid NULL on em…