pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16653: Regression in CTE evaluation
4+ messages / 4 participants
[nested] [flat]

* BUG #16653: Regression in CTE evaluation
@ 2020-10-04 16:18  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2020-10-04 16:18 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: cherio@gmail.com

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.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16653: Regression in CTE evaluation
@ 2020-10-04 17:13  Bruce Momjian <bruce@momjian.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: Bruce Momjian @ 2020-10-04 17:13 UTC (permalink / raw)
  To: cherio@gmail.com; pgsql-bugs@lists.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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16653: Regression in CTE evaluation
@ 2020-10-04 17:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <bruce@momjian.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2020-10-04 17:36 UTC (permalink / raw)
  To: Bruce Momjian <bruce@momjian.us>; +Cc: cherio@gmail.com; pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16653: Regression in CTE evaluation
@ 2020-10-04 22:19  Cherio <cherio@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Cherio @ 2020-10-04 22:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-bugs@lists.postgresql.org

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:

> 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
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2020-10-04 22:19 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-04 16:18 BUG #16653: Regression in CTE evaluation PG Bug reporting form <noreply@postgresql.org>
2020-10-04 17:13 ` Bruce Momjian <bruce@momjian.us>
2020-10-04 17:36   ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-04 22:19     ` Cherio <cherio@gmail.com>

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