BUG #16653: Regression in CTE evaluation

On Sun, Oct 4, 2020 at 04:18:43PM +0000, PG Bug reporting form wrote:

The following SQL worked in versions 9 through 12. It is throwing an error
in version 13.

CREATE TABLE test AS SELECT now() AS tstmp, 'value' AS val;

WITH exp_days AS (
SELECT ''::TEXT AS days WHERE '' ~ E'^[-]?\\d+$'
)
SELECT test.*
FROM test
CROSS JOIN exp_days
WHERE tstmp > date(current_date - CAST(exp_days.days || ' days' AS
interval));

I'd leave alone why this SQL looks ridiculous - it is a very, very
simplified case of more complex dynamic query. Statement "exp_days" returns
no rows and in previous versions the optimizer would not even try evaluating
WHERE in the final query. It doesn't seem to be the case starting version
13.

Uh, I am able to reproduce the error in PG _12_ as well, and I am sure
it is related to this change in PG 12:

https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.9.5

Allow common table expressions (CTEs) to be inlined into the outer query
(Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)

Specifically, CTEs are automatically inlined if they have no
side-effects, are not recursive, and are referenced only once in the
query. Inlining can be prevented by specifying MATERIALIZED, or forced
for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously,
CTEs were never inlined and were always evaluated before the rest of the
query.

In fact, running this query with MATERIALIZED works and returns no rows:

CREATE TABLE test AS SELECT now() AS tstmp, 'value' AS val;

--> WITH exp_days AS MATERIALIZED (
SELECT ''::TEXT AS days WHERE '' ~ E'^[-]?\\d+$'
)
SELECT test.*
FROM test
CROSS JOIN exp_days
WHERE tstmp > date(current_date - CAST(exp_days.days || ' days' AS interval));

I am not sure MATERIALIZED helps you, but it goes give you the pre-PG 12
behavior.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

Bruce Momjian <bruce@momjian.us> writes:

On Sun, Oct 4, 2020 at 04:18:43PM +0000, PG Bug reporting form wrote:

The following SQL worked in versions 9 through 12. It is throwing an error
in version 13.

Uh, I am able to reproduce the error in PG _12_ as well, and I am sure
it is related to this change in PG 12:

Allow common table expressions (CTEs) to be inlined into the outer query
(Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)

Indeed. The planner now inlines the WITH query, allowing
"CAST(exp_days.days || ' days' AS interval)" to be folded to a constant,
whereupon you get an error since indeed ' days' isn't valid interval
input.

Sorry, I reject the position that this is a bug. It was something of
an implementation artifact that you didn't get this error before.
As of v12, we provide explicit control over whether a WITH query can
be inlined or not, and you need to use that control if you have a
query that's dependent on inlining not happening.

regards, tom lane

I guess I was too quick to characterize it as "regression" which implies a
bug. It is certainly a behavior change that affects already written and
tested to work queries.

On Sun, Oct 4, 2020 at 1:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: