From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?= <fco.j.ossandon@gmail.com>
cc: Andrew Dunstan <andrew@dunslane.net>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18594: CASE WHEN ELSE failing to return the expected output
 when the same colum is used in WHEN and ELSE
In-reply-to: 
 <CAEgp-NfU3G_9ynpY4n0hUvYjW2eLLqmGJ5_ejtS5hPi131Jg5g@mail.gmail.com>
References: <18594-af265c6988d431d8@postgresql.org>
 <0838f16a-8b3d-4c39-9ad8-5d27791e9dec@dunslane.net>
 <CAEgp-NfU3G_9ynpY4n0hUvYjW2eLLqmGJ5_ejtS5hPi131Jg5g@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?=
 <fco.j.ossandon@gmail.com>
	message dated "Wed, 28 Aug 2024 20:02:50 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3501445.1724890288.1@sss.pgh.pa.us>
Date: Wed, 28 Aug 2024 20:11:28 -0400
Message-ID: <3501446.1724890288@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3501446.1724890288%40sss.pgh.pa.us>
Precedence: bulk

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