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.