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

I see. So is this behavior expected? The two snippets should work the same
when you look at them but one errors out and the other doesn't. I'm
thinking either both should work or both should error out.

I had this issue on an insert from a NiFi instance that received JSON data
(field sensor measurements from edge devices) and inserted it into a
PostgreSQL instance. The snippet is a short "toy example" simply because I
managed to make an easy reproduction case for your convenience to test it
easily, that's all.

That being said, using the materialized CTE makes it work just fine, so
I'll have this in mind if it happens again. Thank you.

On Thu, Oct 13, 2022 at 11:26 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > On Thu, Oct 13, 2022 at 6:08 PM PG Bug reporting form <
> > noreply(at)postgresql(dot)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
>