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@sss.pgh.pa.us> wrote:
Richard Guo <guofenglinux@gmail.com> writes: > On Thu, Oct 13, 2022 at 6:08 PM PG Bug reporting form < > noreply@postgresql.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.
From:
PG Bug reporting form Date: Subject:
BUG #17639: Failed to download packages: python3-humanize-3.13.1-42.rhel8.noarch: Cannot download, all mirrors w
