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

Oh, I can see how my description was poor.  It should have been something
more along the lines of:

The fourth column is the result of the innner CASE statement having a
compare to NULL, which should be skiipped, and then return 1, which is the
correct result.

The last column compares to a column which does have NULL and it should
also be skipped, but some how Postgres gets confused and returns NULL.  The
fifth should should also be 2.

Sorry.

Best wishes.


On Mon, Oct 10, 2016 at 12:29 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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


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