From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Chris BSomething <xpusostomos@gmail.com>,
        PostgreSQL Bug List <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: 
 <CAKFQuwZ2Rk4T+NWM6uLbooEmg9ADUPb9MqBvqFceK_c2+RyKBQ@mail.gmail.com>
References: <18594-af265c6988d431d8@postgresql.org>
 <0838f16a-8b3d-4c39-9ad8-5d27791e9dec@dunslane.net>
 <CAEgp-NfU3G_9ynpY4n0hUvYjW2eLLqmGJ5_ejtS5hPi131Jg5g@mail.gmail.com>
 <3501446.1724890288@sss.pgh.pa.us>
 <CADrHaBGKd4DQZpEWsdU3msCjg+z1dy1RkFbUn39pm+QuD0QMGQ@mail.gmail.com>
 <CAKFQuwZ2Rk4T+NWM6uLbooEmg9ADUPb9MqBvqFceK_c2+RyKBQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Mon, 17 Feb 2025 15:31:22 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1149959.1739844738.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 17 Feb 2025 21:12:18 -0500
Message-ID: <1149960.1739844738@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1149960.1739844738%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Jan 22, 2025 at 3:00 AM Chris BSomething <xpusostomos@gmail.com>
> wrote:
>> ... 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?

> Do you have an example demonstrating this new rule?  I'm doubting this
> resolution logic goes into that much depth regarding the semantics of each
> type.  It is implied that even if an implicit cast exists from type A to
> type B that actual values of type A may fail during the casting process to
> make them type B.  That would be the interesting error you'd end up seeing
> - but from the type casting/input system, not the type resolution one.

Yeah.  We document somewhere that the contents of a literal string are
*not* considered while deciding what type it should be taken as; thus,
the possibility of a casting failure is not part of the rules.

I think such a rule would be pretty dangerous, even if we could
implement it easily.  As an example, if you entered something you
meant as a timestamp, but you fat-fingered the punctuation or
something, it'd likely fall back to being considered just "text",
potentially silently changing the semantics of the expression.  We had
a lot of trouble with that sort of behavior back when the system had a
lot of implicit casts to text.  We got rid of (most of) those in 8.3
or thereabouts, and it made the semantics generally a lot safer.

			regards, tom lane