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

The following bug has been logged on the website:

Bug reference:      17225
Logged by:          Karol K
Email address:      karllos88@gmail.com
PostgreSQL version: 12.0
Operating system:   Debian 11.1
Description:

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:

WITH ...(),
last_inspection_info AS (
    SELECT 
        sort_column || '<span style="' || color || '" ' || on_click || '>'
|| task_icon || '(' || days_to_next_inspection || ') ' || li.start_::date ||
'</span>' AS last_inspection_info,
        devcid, li.evntid
    FROM last_inspection AS li 
    INNER JOIN next_inspection AS ni USING (devcid), icon
)
SELECT * FROM devices AS d
LEFT JOIN last_inspection_info AS lii
USING (devcid)



Info: devices is a Table, the rest (last_inspection, next_inspection,
last_inspection_info, icon) are CTE which use other CTE of the remaining
8.

What's interesting if the last one CTE (last_inspection_info) will be
replaced with FROM, query runs:

WITH ... ()
SELECT * FROM devices AS d 
LEFT JOIN (
    SELECT 
        sort_column || '<span style="' || color || '" ' || on_click || '>'
|| task_icon || '(' || days_to_next_inspection || ') ' || li.start_::date ||
'</span>' AS last_inspection_info,
        devcid, li.evntid
    FROM last_inspection AS li 
    INNER JOIN next_inspection AS ni USING (devcid), icon
) AS lii
USING (devcid)

I don't assume that the query is incorrect, precisely because of this. Two:
that the message points to data that is not even used in the CTE
'last_inspection_info', because if I try SELECT * FROM last_inspection_info
I get one record, and none of the columns contains "36-004" also there is no
such data in devices. In fact that data is in first CTE which use table
where the data is in, but there is no casting and the data is in text column
and is filtered in such a way that it is no longer present in the CTE.

There was two ways of postgres upgrade:

    1. pg_upgradecluster
    2. Instalation Postgres 13, run postgresql13 service, and pg_dump from
11 and restore on 13.

In both ways there was everything fine but query runs with the above error.

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