From: Tom Lane <tgl@sss.pgh.pa.us>
To: karllos88@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17225: Query with CTE failed after upgrade Postgres from 11 to 12/13
Date: Tue, 12 Oct 2021 16:40:25 -0400
Message-ID: <593010.1634071225@sss.pgh.pa.us> (raw)
In-Reply-To: <17225-15393593a2d11003@postgresql.org>
References: <17225-15393593a2d11003@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> PostgreSQL version: 12.0
If the server is really 12.0, you'd be well advised to update
to 12.latest (currently 12.8). We made some changes to CTE
support in v12, and I recall that there were some early bugs.
> After upgrading Postgres I have a weird error with SELECT query.
> ERROR: invalid input syntax for type integer: "36-004"
> Query works on previous DB version and now doesn't. Query use a lot of CTE
> (To be more specific - 8) and looks like:
> ...
> What's interesting if the last one CTE (last_inspection_info) will be
> replaced with FROM, query runs:
Hm. My first thought about this is that this is a silent behavior change
resulting from the fact that we no longer materialize CTEs by default, but
inline them into the calling query if that wouldn't result in multiple
evaluations. That change could translate into the observed error if some
cast-to-integer is being applied on a row where it wasn't before.
Now, the query change you describe here amounts to manually inlining that
one CTE, so that particular CTE doesn't seem to be the cause of the
problem. But one of the other CTEs you didn't show us might be. I'd
suggest seeing whether the behavior changes when you attach MATERIALIZED
keywords to the other CTEs. That'd at least help localize the issue.
regards, tom lane
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: tgl@sss.pgh.pa.us, karllos88@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17225: Query with CTE failed after upgrade Postgres from 11 to 12/13
In-Reply-To: <593010.1634071225@sss.pgh.pa.us>
* 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