apache
Druid v33 gives null value for each column in aggregation that does not have a value in the query time range
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.
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.
@gianm Thank you for confirming! In this case we will have to wrap them all in coalesce function.
@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.
@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.
@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.