Re: BUG #17637: case-when branches taken even if they dont match, raising errors

On Thu, Oct 13, 2022 at 6:08 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> 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;

I think this has something to do with the CTE used here. In
preprocess_expression, we do not know the value of m.mapped_to, so we
cannot tell the test condition is constant FALSE. Thus we need go on
processing the result. But thanks to the CTE, we know t.v is const
'123.4'::text, and we want to convert it to boolean, which triggers the
error.

I'm not sure about this being a bug.

Thanks
Richard