Обсуждение: BUG #16653: Regression in CTE evaluation

The following bug has been logged on the website:

Bug reference:      16653
Logged by:          Yuri Cherio
Email address:      cherio@gmail.com
PostgreSQL version: 13.0
Operating system:   Ubuntu Linux 20.04
Description:

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.

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