zhang-arvin opened a new pull request, #20093: URL: https://github.com/apache/druid/pull/20093
## Description Fixes #18058 and #17768. The `DruidAggregateCaseToFilterRule` D1 rewrite converts `SUM(CASE WHEN COND THEN COL1 ELSE 0 END)` to `SUM(COL1) FILTER(WHERE COND)`. However, `SUM` returns NULL when the filter never matches, while the original CASE expression would return 0. This causes incorrect NULL results for queries with aggregations on empty windows. ### Root Cause The D1 rewrite in `DruidAggregateCaseToFilterRule.transform()` uses `call.getAggregation()` (which is `SUM`) when creating the filtered aggregate call. `SUM` returns NULL for empty groups, but the original `SUM(CASE WHEN ... ELSE 0 END)` returns 0 when no rows match the condition. ### Fix Changed the D1 rewrite to use `SUM0` (via `SqlStdOperatorTable.SUM0`) instead of `SUM`. `SUM0` returns 0 for empty/null input, which matches the expected behavior of the original CASE expression. ### Changes - **`DruidAggregateCaseToFilterRule.java`**: Changed the D1 case in `transform()` to use `SUM0` instead of `SUM` for the filtered aggregate call, with non-nullable result type. - **`filtered_sum.iq`**: Updated expected test results to reflect the corrected behavior (0 instead of NULL for empty window and no-match cases). ### Behavior Changes | Scenario | Before | After | |----------|--------|-------| | Empty input (no rows) | NULL | 0 | | Rows exist, none match filter | NULL | 0 | | Rows exist, some match, non-null | N | N (unchanged) | | Rows exist, all null values | NULL | 0 | ### Key Features - Aggregations on empty windows now return 0 instead of NULL - Consistent with the documented behavior table in the class javadoc - Backward compatible for most use cases ### Testing Updated the `filtered_sum.iq` quidem test to verify the corrected behavior for all four scenarios (empty input, no match, some match, all null values). -- 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]
