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(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> 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
>>>
>>
>>
>
>
> --
> * * * * Henry Cate III <hcate3(at)gmail(dot)com> * * * *
> Silent gratitude isn't much use to anyone.
> -Gladys Bronwyn Stern, writer (1890-1973)
> Our blog: http://whyhomeschool.blogspot.com/
>