On Thu, Nov 12, 2020 at 7:34 AM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:
Bug reference: 16712
Logged by: SP
Email address: [hidden email]
PostgreSQL version: 13.0
Operating system: seems any (tested on Ubuntu 18.04 and macOS 10.15)
An unexpected result returns when running this query:
WITH data_cte(str) AS (
FROM (VALUES ('a')) data_tmp(str)
While empty result is expected, we get this:
ERROR: invalid input syntax for type integer: "a"
Because of these type casting error, it seems that the outer query has
access to the data_cte record!
It does - right there in the "FROM data_cte" clause. The planner has chosen to optimize this query by taking the two parts and making them one, reducing a level of execution indirection. If you don't wish for this to happen you need to add MATERIALIZED to the WITH clause element. This is one of the more impactful changes with v13, in prior versions materialized was the default (and optimization was not possible).