pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: elprans@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16121: 12 regression: Volatile function in target list subquery behave as stable
Date: Mon, 18 Nov 2019 11:47:55 -0500
Message-ID: <18925.1574095675@sss.pgh.pa.us> (raw)
In-Reply-To: <16121-b8e8dc82e608b89d@postgresql.org>
References: <16121-b8e8dc82e608b89d@postgresql.org>

PG Bug reporting form <noreply@postgresql.org> writes:
> There seems to be a regression in Postgres 12 related to how volatile
> functions are handled. If put inside a subquery in the target list, the
> volatile function seems to behave like a stable one:

> volatility=# SELECT
>    v.i,
>    (SELECT random() from (values (v.i)) as q) AS v1
> FROM
>    generate_series(1, 2) as v(i);
>  i │         v1
> ───┼────────────────────
>  1 │ 0.8303615124282295
>  2 │ 0.8303615124282295
> (2 rows)

While there's certainly been a change in behavior here, it's hard
to call it a bug, because I do not think we promise anything much
about cases like this.  The reason why v12 is no longer evaluating
random() more than once is that it's successfully simplified the
sub-select to just "(SELECT random())", which is supposed to be
evaluated only once, because it's not correlated to the outer query.

Previous versions failed to do that, or at least failed to do it
early enough to remove the v.i reference before making the decision
as to whether the sub-select is correlated or not.  (Just to check,
I bisected to see where the behavior changed, and found that it was
at commit 4be058fe9, "In the planner, replace an empty FROM clause
with a dummy RTE".  Smarter handling of trivial VALUES items was an
explicit goal of that commit.)

So basically, you're depending on an assumption that the system
won't optimize away the unused reference to v.i, and now it's
started to do so.  I do not wish to regard that as a bug; for
most people affected by this change, it's surely an improvement.

In the given example, you'd be a lot better off skipping the
sub-select altogether and just writing "random() AS v1".
I suppose this is an oversimplified form of some production query,
but we'd need to see something closer to the real query to have
an intelligent discussion about what to do instead.

			regards, tom lane





view thread (3+ messages)  latest in thread

Message-ID: <18925.1574095675@sss.pgh.pa.us>
Permalink:  ../18925.1574095675@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18925.1574095675@sss.pgh.pa.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, elprans@gmail.com
  Subject: Re: BUG #16121: 12 regression: Volatile function in target list subquery behave as stable
  In-Reply-To: <18925.1574095675@sss.pgh.pa.us>
  References: <16121-b8e8dc82e608b89d@postgresql.org> <18925.1574095675@sss.pgh.pa.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