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

Le mar. 12 oct. 2021 à 22:07, PG Bug reporting form <noreply(at)postgresql(dot)org>
a écrit :

> The following bug has been logged on the website:
>
> Bug reference: 17225
> Logged by: Karol K
> Email address: karllos88(at)gmail(dot)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.
>
>
If you think your query isn't at fault, you should probably try your query
on the latest minor release of the v12 branch (12.8, released in august).

--
Guillaume.