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@dunslane.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.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.
> >
>
> 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