Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE

On 2024-08-28 We 12:17 PM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18594
> Logged by: Francisco Javier Ossandon
> Email address: fco(dot)j(dot)ossandon(at)gmail(dot)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.
>

The bug is in the query, not in Postgres. Try casting typcategory to
text in your failing case to see the difference. As it is the literals
are being cast to "char" because that's what typcategory is.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com