Re: 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(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

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