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

Vitaly, thanks for reporting that this is fixed in a more recent version of
Postgres.

And thanks to everyone else for their help.

Best wishes.


On Mon, Oct 10, 2016 at 1:09 PM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
wrote:

> 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
>



--
*  *  *  *  Henry Cate III  <hcate3@gmail.com>  *  *  *  *
Silent gratitude isn't much use to anyone.
-Gladys Bronwyn Stern, writer (1890-1973)
Our blog: http://whyhomeschool.blogspot.com/