[
https://issues.apache.org/jira/browse/CALCITE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028274#comment-17028274
]
Rui Wang commented on CALCITE-3764:
-----------------------------------
I tried to verify the behaviour of cases in this Jira on Postgresql.
First;y I created a test table and note that the third row's name column is
"NULL".
postgres=# select * from filter_test;
sal | name
-----+------
10 | n1
20 | n2
30 |
(3 rows)
Then here are three queries' results:
postgres=# select count(case when name='n2' then null else 1 end) from
filter_test;
count
-------
2
(1 row)
postgres=# select count(*) FILTER (WHERE name='n2' IS FALSE) FROM filter_test;
count
-------
1
(1 row)
postgres=# select count(*) FILTER (WHERE name='n2' IS NOT TRUE) FROM
filter_test;
count
-------
2
(1 row)
> 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)
