Обсуждение: BUG #14363: CASE against a literal value produces correct against, same expression against a column null doesn't
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM2MwpMb2dnZWQgYnk6ICAg ICAgICAgIEhlbnJ5IENhdGUKRW1haWwgYWRkcmVzczogICAgICBoY2F0ZTNA Z21haWwuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjIKT3BlcmF0aW5n IHN5c3RlbTogICB4ODZfNjQtcGMtbGludXgtZ251CkRlc2NyaXB0aW9uOiAg ICAgICAgCgpXaXRoIHRoaXMgc2V0dXA6DQoNCmRyb3AgdGFibGUgaWYgZXhp c3RzIHQyOw0KY3JlYXRlIHRhYmxlIHQyICgNCiAgb3JkZXJfa2V5IGludCwN CiAgZGVjaW1hbF85XzEgICAgIGRlY2ltYWwoOSwxKSwNCiAgZGVjaW1hbF8x OF8xICAgIGRlY2ltYWwoMTgsMSkpOw0KDQppbnNlcnQgaW50byB0MiB2YWx1 ZXMgKDMsIDkwMS44LCBudWxsKTsNCg0KDQpUaGlzIHF1ZXJ5Og0KDQpzZWxl Y3QgZGVjaW1hbF85XzEsIGRlY2ltYWxfMThfMSwgKCANCiAgY2FzZSBkZWNp bWFsXzlfMSANCiAgICAgd2hlbiBudWxsIHRoZW4gMCANCiAgICAgd2hlbiBk ZWNpbWFsXzlfMSAgdGhlbiAxIGVuZCksICANCiAgY2FzZSAoIA0KICAgIGNh c2UgZGVjaW1hbF85XzEgDQogICAgICB3aGVuIG51bGwgdGhlbiAwIA0KICAg ICAgd2hlbiBkZWNpbWFsXzlfMSAgdGhlbiAxIGVuZCkNCiAgICB3aGVuIDEg dGhlbiAyDQogICAgd2hlbiAwIHRoZW4gMw0KICBlbmQsICANCiAgY2FzZSAo IA0KICAgIGNhc2UgZGVjaW1hbF85XzEgDQogICAgICB3aGVuIGRlY2ltYWxf MThfMSB0aGVuIDAgDQogICAgICB3aGVuIGRlY2ltYWxfOV8xICB0aGVuIDEg ZW5kKQ0KICAgIHdoZW4gMSB0aGVuIDINCiAgICB3aGVuIDAgdGhlbiAzDQog IGVuZA0KZnJvbSB0MiA7DQoNCg0KcHJvZHVjZXMgdGhlc2UgcmVzdWx0czoN Cg0KIGRlY2ltYWxfOV8xIHwgZGVjaW1hbF8xOF8xIHwgY2FzZSB8IGNhc2Ug fCAgY2FzZSAgDQotLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tKy0tLS0t LSstLS0tLS0rLS0tLS0tLS0NCiAgICAgICA5MDEuOCB8ICAgICAgIDxudWxs PiB8ICAgIDEgfCAgICAyIHwgPG51bGw+DQooMSByb3cpDQoNCg0KSSBleHBl Y3QgdGhlIGxhc3QgdHdvIGNvbHVtbnMgdG8gYm90aCBoYXZlIGEgdmFsdWUg b2YgMi4gIA0KDQpUaGUgZm91cnRoIGNvbHVtbiBjb21wYXJlcyB0aGUgcmVz dWx0IG9mIHRoZSBpbm5lciBjYXNlIHN0YXRlbWVudCB0byBOVUxMCmFuZCBw cm9kdWNlcyB0aGUgY29ycmVjdCByZXN1bHQuICBUaGUgbGFzdCBjb2x1bW4g Y29tcGFyZXMgdG8gYSBjb2x1bW4gd2hpY2gKZG9lcyBoYXZlIE5VTEwsIGJ1 dCBzb21lIGhvdyBQb3N0Z3JlcyBnZXRzIGNvbmZ1c2VkIGFuZCByZXR1cm5z IE5VTEwuICBJdApzaG91bGQgYWxzbyBiZSByZXR1cm5pbmcgMi4NCg0KDQoN CkhlcmUgaXMgdGhlIHZlcnNpb24gaW5mb3JtYXRpb246DQoNCnliZF90ZXN0 PSMgc2VsZWN0IHZlcnNpb24oKTsNCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHZlcnNpb24gICAgICAg ICAgICAgICAKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIFBvc3RncmVTUUwg OS41LjIgb24geDg2XzY0LXBjLWxpbnV4LWdudSwgY29tcGlsZWQgYnkgY2xh bmcgdmVyc2lvbgozLjguMC0ydWJ1bnR1MyAodGFncy9SRUxFQVNFXzM4MC9m aW5hbCksIDY0LWJpdA0KKDEgcm93KQ0KCgo=
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 >
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/ >
Re: BUG #14363: CASE against a literal value produces correct against, same expression against a column null doesn't
От
"David G. Johnston"
Дата:
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=3D# 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
>
=E2=80=8BSame result in 9.3.12
=E2=80=8BIt seems like "case" is too complex an expression to be embedded i=
nto 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
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
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/
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/
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/