Re: BUG #17225: Query with CTE failed after upgrade Postgres from 11 to 12/13 - Mailing list pgsql-bugs

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.