From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: hctf90@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17637: case-when branches taken even if they dont match,
 raising errors
In-reply-to: 
 <CAMbWs48yWBw6JRGck7qB3Eyvkz3xzRYqmHNvB=DZeNrpgQF3RQ@mail.gmail.com>
References: <17637-5904e3fdee533c7f@postgresql.org>
 <CAMbWs48yWBw6JRGck7qB3Eyvkz3xzRYqmHNvB=DZeNrpgQF3RQ@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Thu, 13 Oct 2022 19:30:53 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <955801.1665671189.1@sss.pgh.pa.us>
Date: Thu, 13 Oct 2022 10:26:29 -0400
Message-ID: <955802.1665671189@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/955802.1665671189%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> On Thu, Oct 13, 2022 at 6:08 PM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> with tmp as (select 1::int8 id, '123.4'::text v)
>> select t.id,
>> case m.mapped_to when 1 then v::float8 else null end,
>> case m.mapped_to when 2 then v::bool else null end
>> from tmp t
>> join tmap m on m.id = t.id;

> I'm not sure about this being a bug.

I'm not buying it either.  The point is that the constants from the
WITH clause get pulled up into the outer query, whereupon you have

    case m.mapped_to when 2 then '123.4'::text::bool else null end

and then we apply constant-folding which tries to perform the bool
conversion.  (There are some folding rules whereby if a WHEN condition
reduces to constant true or constant false, we drop all the
therefore-unreachable THEN/ELSE arms without folding them --- but
that doesn't help here since m.mapped_to isn't a constant.)

I'm not especially eager to lobotomize the const-folding rules
in order to make toy examples like this one work.  I don't think
it's representative of real queries; but we *would* be penalizing
real queries if we didn't perform such folding.

I believe you could dodge the issue in this particular case
by marking the WITH query as MATERIALIZED, which'll serve as
an optimization fence to prevent the constants from being
hoisted into the outer query.

			regards, tom lane