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

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

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

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

Pavel Stehule <pavel.stehule@gmail.com> writes:

2016-10-10 20:56 GMT+02:00 <hcate3@gmail.com>:

case decimal_9_1
when null then 0
when decimal_9_1 then 1 end),

You cannot to compare NULL with NULL in Postgres.

More specifically, you can, but you get a NULL result. "NULL = NULL"
yields NULL, not true. This is per SQL standard.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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/

On Mon, Oct 10, 2016 at 11:56 AM, <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:

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

​Same result in 9.3.12

​It seems like "case" is too complex an expression to be embedded into the
<..."simple" form of CASE expression...>

SELECT
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

https://www.postgresql.org/docs/9.5/static/functions-conditional.html

I suspect this is some kind of long-standing bug that hasn't been seen due
to this being a unusual way to write a case expression - mainly since the
embedded case evaluates correctly if it is done as a top-level expression.

David J.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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/