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

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.

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/