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

On 10/10/16, hcate3@gmail.com <hcate3@gmail.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      14363
> Logged by:          Henry Cate
> Email address:      hcate3@gmail.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)

Upgrade to the newest version of 9.5. It works as expected as of 9.5.4
and 9.6.0:

psql (9.5.4)
Type "help" for help.

postgres=# create table t2 (
postgres(#   order_key int,
postgres(#   decimal_9_1     decimal(9,1),
postgres(#   decimal_18_1    decimal(18,1));
CREATE TABLE
postgres=#
postgres=# insert into t2 values (3, 901.8, null);
INSERT 0 1
postgres=# select decimal_9_1, decimal_18_1, (
postgres(#   case decimal_9_1
postgres(#      when null then 0
postgres(#      when decimal_9_1  then 1 end),
postgres-#   case (
postgres(#     case decimal_9_1
postgres(#       when null then 0
postgres(#       when decimal_9_1  then 1 end)
postgres-#     when 1 then 2
postgres-#     when 0 then 3
postgres-#   end,
postgres-#   case (
postgres(#     case decimal_9_1
postgres(#       when decimal_18_1 then 0
postgres(#       when decimal_9_1  then 1 end)
postgres-#     when 1 then 2
postgres-#     when 0 then 3
postgres-#   end
postgres-# from t2 ;
 decimal_9_1 | decimal_18_1 | case | case | case
-------------+--------------+------+------+------
       901.8 |              |    1 |    2 |    2
(1 row)

postgres=# select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Gentoo
4.7.3-r1 p1.4, pie-0.5.5) 4.7.3, 64-bit
(1 row)

--
Best regards,
Vitaly Burovoy