Re: BUG #17225: Query with CTE failed after upgrade Postgres from 11 to 12/13

PG Bug reporting form <noreply(at)postgresql(dot)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