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

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,

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

=?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?= <fco.j.ossandon@gmail.com> writes:
> So is the ELSE column hijacking the data type of the whole expression?

It's the only CASE result that is supplying a definite type at all.
But see

https://www.postgresql.org/docs/current/typeconv-union-case.html

particularly the footnote to the bit about "Select the first
non-unknown input type as the candidate type, then consider
each other non-unknown input type, left to right."

The WHEN clauses have exactly nothing to do with the result type
of the CASE: it's the THEN and ELSE clauses that supply the result.

            regards, tom lane