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 1iWojW-0006Jo-VU for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 21:38:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iWojV-00082H-SD for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 21:38:53 +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 1iWohw-0005fQ-Ep for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 21:37:16 +0000 Received: from mail-qv1-xf42.google.com ([2607:f8b0:4864:20::f42]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iWohr-0006A4-B3 for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 21:37:15 +0000 Received: by mail-qv1-xf42.google.com with SMTP id n4so4116799qvq.9 for ; Mon, 18 Nov 2019 13:37:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:subject:date:message-id:in-reply-to:references :mime-version:content-transfer-encoding; bh=tr2M3bVJWIhJQG9Rv1twyZWbAWPn6Q5MPEz+6YaQpCU=; b=mcpGhxfT+UdTmdJSmgp7znjgZ64ogZxbUuVrwmYi7vIKqHYLNPD0dqMjYV7m8glO+J I6SbQYGqCvrNiLXQltbcpydTYgtHbmq/u6RAiEjkGWXSlPVxHq8DcKdxbe63s7ygWOFm ssUSl9j4n9kQVgb6y8SdBk9LP5ysHr2O/LR1Blgzesz7mnO2sB60VDwvGeOyFdCebK1Q RFxaKTbbuwa6XHJEYU310TzpBooeXCzhnCqUeZTMrMC4qnyejcii6CeG3P9GcuY797sY 9IeuhbuTGhlUC92HSGO6JE+Dcbl/A9P4ZMJX8MiAhAQkpnCA/T2bikL2JLxQ5bRTco+0 MGFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:date:message-id:in-reply-to :references:mime-version:content-transfer-encoding; bh=tr2M3bVJWIhJQG9Rv1twyZWbAWPn6Q5MPEz+6YaQpCU=; b=e0XghMCd/rXmELbSNjDAaJ1imUnsdhT4hdad90otU4G+EKC+0XNVqN/na6uQ1pOPIJ cRaQoGDps2aktqmtwHJozDWtPQjxWOtfbVnwdozQGBAhHjBIiNiPyoYkAZXIPPG+pyWe YZAu7zrPy2VTXaXmyZZoYBpM6ayS+33qQOTnf3bF5xQb50l3mpq7RZ58hy3rQndw+o9r S7ltA/DC1O08QVCCvlRy3DUTCxs05A17Z558PqyAoXd7HGPPBJHuKNtHzSr6DDN8g1s3 Wik9lDqTMUAtpFyMaQ4jeh+jpBwgdXxV9K3SmIXdsS29TYyYwpT0jI2K3eoTODFBSgoP LOFQ== X-Gm-Message-State: APjAAAWZhQL96g8dBN9wZeE3MwkivED6USMHu0dYvHsEHK5KHNM62lSq vGIgVVjbq0KgN2H3Qzn0fh8uDoPmAdc= X-Google-Smtp-Source: APXvYqwYLIl60VmwOgK0OrHlJT4ebdDXLL0AO/Fvrl3/l6gkAEJX5EEuJ4rQbCDld0f8epLB2otlMw== X-Received: by 2002:ad4:4310:: with SMTP id c16mr28335316qvs.238.1574113030178; Mon, 18 Nov 2019 13:37:10 -0800 (PST) Received: from hammer.magicstack.net (toroon0954w-lp140-04-76-64-166-31.dsl.bell.ca. [76.64.166.31]) by smtp.gmail.com with ESMTPSA id 132sm9090549qki.114.2019.11.18.13.37.09 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 18 Nov 2019 13:37:09 -0800 (PST) From: Elvis Pranskevichus To: Tom Lane 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 16:37:08 -0500 Message-ID: <17891385.j2xyqUmsBK@hammer.magicstack.net> In-Reply-To: <18925.1574095675@sss.pgh.pa.us> References: <16121-b8e8dc82e608b89d@postgresql.org> <18925.1574095675@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Monday, November 18, 2019 11:47:55 A.M. EST Tom Lane wrote: > 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: > >=20 > > volatility=3D# SELECT > >=20 > > v.i, > > (SELECT random() from (values (v.i)) as q) AS v1 > >=20 > > FROM > >=20 > > generate_series(1, 2) as v(i); > > =20 > > i =E2=94=82 v1 > >=20 > > =E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80 > >=20 > > 1 =E2=94=82 0.8303615124282295 > > 2 =E2=94=82 0.8303615124282295 > >=20 > > (2 rows) >=20 > 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. >=20 > 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.) >=20 > 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. >=20 > 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. Hmm. We're generating those queries, so hoisting to the outer query is=20 not always possible, especially when there's cardinality mismatch and=20 the subquery is aggregated somehow. I guess we would need to find=20 another way to convince the optimizer to leave the evaluation of the=20 volatile call in place. Thanks, Elvis