Обсуждение: BUG #17637: case-when branches taken even if they dont match, raising errors

The following bug has been logged on the website:

Bug reference:      17637
Logged by:          Facundo Etchezar
Email address:      hctf90@gmail.com
PostgreSQL version: 14.5
Operating system:   Debian 11.3.0-3
Description:

Hi! I've come across some weird behavior. I'm inserting a row into a table,
and I parse/cast a text column in one way or the other depending on the
result of a join with a case-when expression. The issue is that for some
reason it seems the branches that aren't taken are evaluated anyway, which
in turn raises a cast error. Here is a tiny repro where I make two tables
and then try to insert in the test table a float8 or bool value based on the
result of the joined tmap table.

This tiny repro below raises the error SQL Error [22P02]: ERROR: invalid
input syntax for type boolean: "123.4" Like it's trying to parse the text
column as bool even if it shouldn't reach that part of the case-when.

drop table if exists test;
drop table if exists tmap;
create table test(
 id int8,
 vf float8,
 vb bool
);
create table tmap(
 id int8,
 mapped_to int8
);
insert into tmap values(1, 1);
insert into tmap values(2, 2);
insert into test 
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;

Weirdly enough if you forego the join, doing this below, it works fine:

drop table if exists test;
create table test(
 id int8,
 vf float8,
 vb bool
);
insert into test 
with tmp as (select 1::int8 id, '123.4'::text v)
select t.id,
case t.id when 1 then v::float8 else null end,
case t.id when 2 then v::bool else null end
from tmp t;

This should result in the same behavior yet it works fine, without throwing
an error.

I've also tested this in https://www.db-fiddle.com with versions 15 beta,
13, 12. All with the same error. Versions 11 and 10 seem to work fine.

Thank you for your time.

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