From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | hctf90(at)gmail(dot)com |
Subject: | BUG #17637: case-when branches taken even if they dont match, raising errors |
Date: | 2022-10-13 02:30:00 |
Message-ID: | 17637-5904e3fdee533c7f@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17637
Logged by: Facundo Etchezar
Email address: hctf90(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Guo | 2022-10-13 11:30:53 | Re: BUG #17637: case-when branches taken even if they dont match, raising errors |
Previous Message | PG Bug reporting form | 2022-10-12 11:22:26 | BUG #17636: terminating connection because of crash of another server process |