From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: hctf90@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17637: case-when branches taken even if they dont match, raising errors
Date: Thu, 13 Oct 2022 10:26:29 -0400
Message-ID: <955802.1665671189@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMbWs48yWBw6JRGck7qB3Eyvkz3xzRYqmHNvB=DZeNrpgQF3RQ@mail.gmail.com>
References: <17637-5904e3fdee533c7f@postgresql.org>
<CAMbWs48yWBw6JRGck7qB3Eyvkz3xzRYqmHNvB=DZeNrpgQF3RQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, guofenglinux@gmail.com, 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: <955802.1665671189@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox