From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: fco.j.ossandon@gmail.com
Subject: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
Date: Wed, 28 Aug 2024 16:17:03 +0000
Message-ID: <18594-af265c6988d431d8@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18594
Logged by: Francisco Javier Ossandon
Email address: fco.j.ossandon@gmail.com
PostgreSQL version: 15.5
Operating system: Linux
Description:
Dear developers:
I have been using Postgres for some years now, and I just found what looks
like a bug, or at least I did not see anything in the documentation that
could explain it.
* version():
PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712
(Red Hat 7.3.1-12), 64-bit
The main issue is that "CASE WHEN THEN ELSE" is giving an expected result in
what seems like simple logic. I have a series of WHENs with conditions over
the value of a column and an ELSE where I return the same column value if
the CASEs above don't apply, and the query always returns the value of the
ELSE condition like if the WHENs above would not exist. The following query
exemplifies this, where the first CASE column gives an unexpected result
when using the same column in the ELSE, while the other CASE columns provide
the expected result when removing the ELSE or using a different column in
the ELSE or a constant string:
```
SELECT oid,
typname,
typcategory,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE typcategory
END AS test_case_fails,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
END AS test_case_works_no_else,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE typname
END AS test_case_works_other_col,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE 'ELSE'
END AS test_case_works_constant
FROM pg_type
ORDER BY oid
LIMIT 10
;
```
This returns the following:
```
oid|typname
|typcategory|test_case_fails|test_case_works_no_else|test_case_works_other_col|test_case_works_constant|
---+----------+-----------+---------------+-----------------------+-------------------------+------------------------+
16|bool |B |B | |bool
|ELSE |
17|bytea |U |U | |bytea
|ELSE |
18|char |Z |Z | |char
|ELSE |
19|name |S |S |String types |String
types |String types |
20|int8 |N |N |Numeric types |Numeric
types |Numeric types |
21|int2 |N |N |Numeric types |Numeric
types |Numeric types |
22|int2vector|A |A |
|int2vector |ELSE |
23|int4 |N |N |Numeric types |Numeric
types |Numeric types |
24|regproc |N |N |Numeric types |Numeric
types |Numeric types |
25|text |S |S |String types |String
types |String types |
```
It looks weird, so I'm reporting this to you for review.
Best regards,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, fco.j.ossandon@gmail.com
Subject: Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
In-Reply-To: <18594-af265c6988d431d8@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox