From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: hctf90@gmail.com
Subject: BUG #17637: case-when branches taken even if they dont match, raising errors
Date: Thu, 13 Oct 2022 02:30:00 +0000
Message-ID: <17637-5904e3fdee533c7f@postgresql.org> (raw)
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.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, hctf90@gmail.com
Subject: Re: BUG #17637: case-when branches taken even if they dont match, raising errors
In-Reply-To: <17637-5904e3fdee533c7f@postgresql.org>
* 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