Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWkC8-00045N-C7 for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 16:48:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iWkC6-0004MU-TC for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 16:48:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWkC6-0004MN-D3 for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 16:48:06 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iWkBz-0003Hr-BE for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 16:48:04 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xAIGltXZ018926; Mon, 18 Nov 2019 11:47:55 -0500 From: Tom Lane 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 In-reply-to: <16121-b8e8dc82e608b89d@postgresql.org> References: <16121-b8e8dc82e608b89d@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Mon, 18 Nov 2019 01:26:30 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <18924.1574095675.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Mon, 18 Nov 2019 11:47:55 -0500 Message-ID: <18925.1574095675@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form 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