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

Hello Andrew,
I thought about that before writing and tried casting to TEXT in the WHEN
comparison bits but it still failed.
CASE
WHEN typcategory::TEXT = 'N' THEN 'Numeric types'
WHEN typcategory::TEXT = 'S' THEN 'String types'
WHEN typcategory::TEXT = 'E' THEN 'Enum types'
ELSE typcategory
END AS test_case_fails,

After your answer, I tried again but this time adding the casting to the
ELSE too, and then it worked:
CASE
WHEN typcategory::TEXT = 'N' THEN 'Numeric types'
WHEN typcategory::TEXT = 'S' THEN 'String types'
WHEN typcategory::TEXT = 'E' THEN 'Enum types'
ELSE typcategory::TEXT

So is the ELSE column hijacking the data type of the whole expression?

Cheers,

Francisco

El mié, 28 ago 2024 a las 13:57, Andrew Dunstan (<andrew(at)dunslane(dot)net>)
escribió:

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

--
Francisco J. Ossandon
Bioinformatician
Ph.D. in Biotechnology