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

2016-10-10 21:26 GMT+02:00 Henry Cate <hcate3@gmail.com>:

>
> I'm confused.
>
> The value which is being compared in the inner CASE statement is 901.8,
> not NULL.
>
> In both the fourth and fifth column shouldn't the test in the inner CASE
> statement skip pass the NULL check (either explicit or via the column
> value) and return 1, so then the outer CASE statement should get the value
> of 1 and return 2?
>
> It seems like both columns should be returning the same value.
>
> Or maybe I am misunderstanding your example.
>

I didn't checked your example in detail - just I see a one issue there.

Pavel


>
> Thanks.
>
> Best wishes.
>
>
> On Mon, Oct 10, 2016 at 12:15 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> 2016-10-10 20:56 GMT+02:00 <hcate3@gmail.com>:
>>
>>> 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)
>>>
>>>
>>
>> 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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>
>>
>
>
> --
> *  *  *  *  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/
>