Re: BUG #14363: CASE against a literal value produces correct against, same expression against a column null doesn't

Hi

2016-10-10 20:56 GMT+02:00 <hcate3(at)gmail(dot)com>:

> The following bug has been logged on the website:
>
> Bug reference: 14363
> Logged by: Henry Cate
> Email address: hcate3(at)gmail(dot)com
> PostgreSQL version: 9.5.2
> Operating system: x86_64-pc-linux-gnu
> Description:
>
> With this setup:
>
> drop table if exists t2;
> create table t2 (
> order_key int,
> decimal_9_1 decimal(9,1),
> decimal_18_1 decimal(18,1));
>
> insert into t2 values (3, 901.8, null);
>
>
> This query:
>
> select decimal_9_1, decimal_18_1, (
> case decimal_9_1
> when null then 0
> when decimal_9_1 then 1 end),
> case (
> case decimal_9_1
> when null then 0
> when decimal_9_1 then 1 end)
> when 1 then 2
> when 0 then 3
> end,
> case (
> case decimal_9_1
> when decimal_18_1 then 0
> when decimal_9_1 then 1 end)
> when 1 then 2
> when 0 then 3
> end
> from t2 ;
>
>
> produces these results:
>
> decimal_9_1 | decimal_18_1 | case | case | case
> -------------+--------------+------+------+--------
> 901.8 | <null> | 1 | 2 | <null>
> (1 row)
>
>
> I expect the last two columns to both have a value of 2.
>
> The fourth column compares the result of the inner case statement to NULL
> and produces the correct result. The last column compares to a column
> which
> does have NULL, but some how Postgres gets confused and returns NULL. It
> should also be returning 2.
>
>
>
> Here is the version information:
>
> ybd_test=# select version();
> version
>
> ------------------------------------------------------------
> --------------------------------------------------------
> PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by clang version
> 3.8.0-2ubuntu3 (tags/RELEASE_380/final), 64-bit
> (1 row)
>
>

You cannot to compare NULL with NULL in Postgres.

postgres=# select case null when null then 1 else 0 end;
+------+
| case |
+------+
| 0 |
+------+
(1 row)

Time: 0.764 ms

This result is correct

you can use another form of CASE

postgres=# select case when null is null then 1 else 0 end;
+------+
| case |
+------+
| 1 |
+------+
(1 row)

Regards

Pavel

>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>