pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <bruce@momjian.us>
To: cherio@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16653: Regression in CTE evaluation
Date: Sun, 4 Oct 2020 13:13:59 -0400
Message-ID: <20201004171359.GA10396@momjian.us> (raw)
In-Reply-To: <16653-3154c42c219f8693@postgresql.org>
References: <16653-3154c42c219f8693@postgresql.org>

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






view thread (4+ messages)  latest in thread

Message-ID: <20201004171359.GA10396@momjian.us>
Permalink:  ../20201004171359.GA10396@momjian.us/
Also on:    postgresql.org/message-id/20201004171359.GA10396@momjian.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: bruce@momjian.us, cherio@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16653: Regression in CTE evaluation
  In-Reply-To: <20201004171359.GA10396@momjian.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox