[ https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028277#comment-17028277 ]
Rui Wang commented on CALCITE-3764: ----------------------------------- However, I am not sure if the proposed solution is correct. Converting to "WHERE b IS NOT TRUE" is correct only for the special case "THEN NULL ELSE 1 END". Think about a general case: {code:sql} SELECT AGG_FUNCTION(CASE WHEN b THEN result_1 ELSE result_2 END) FROM t {code} basically for an arbitrary aggregation function, FILTER clause shouldn't be used and CASE should be preserved. Because we cannot always ignore result_1. For example, {code:sql} SELECT SUM(CASE WHEN b THEN col_1 ELSE col_2 END) FROM t {code} cannot be converted to {code:sql} SELECT SUM(col_2) FILTER (WHERE b IS NOT TRUE) FROM t {code} Unless this jira tries to address "COUNT(CASE WHEN b THEN NULL ELSE 1 END)" as a special case, otherwise the right resolution is to not convert to FILTER if there is more than one THEN. I can try to open a PR for it. > AggregateCaseToFilterRule handles NULL values correctly > ------------------------------------------------------- > > Key: CALCITE-3764 > URL: https://issues.apache.org/jira/browse/CALCITE-3764 > Project: Calcite > Issue Type: Bug > Reporter: Julian Hyde > Priority: Major > > {{AggregateCaseToFilterRule}} handles NULL values correctly. It converts > {code:sql} > SELECT COUNT(CASE WHEN b THEN NULL ELSE 1 END) FROM t > {code} > to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS FALSE) FROM t > {code} > which fails to count rows where {{b}} is UNKNOWN, so it should convert to > {code:sql} > SELECT COUNT(*) FILTER (WHERE b IS NOT TRUE) FROM t > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)