Request a Demo
Home   >   mailing lists

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

From Chris BSomething
Subject Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
Date
Msg-id CADrHaBGKd4DQZpEWsdU3msCjg+z1dy1RkFbUn39pm+QuD0QMGQ@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Tree view
In the 6 rules for type resolution, rule 5 says "select the first non-unknown input type as candidate, and consider if other non-unknown types can be implicitly converted." ... should there not be a new rule, something like considering if the unknown types can be converted, then notice that a long string can't be converted to a char, because it won't fit? And then fail with an interesting error?


On Thu, 29 Aug 2024 at 08:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Francisco J. Ossandón <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


pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: Combining array slicing and indexing causes incorrect/confusing results
Next
From: "David G. Johnston"
Date:
Subject: Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4