Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMSoy-000000022Ws-3xO2 for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 16:17:50 +0000 Received: from localhost ([127.0.0.1]:54936 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMSoy-00000005IQu-1vN7 for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 16:17:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:54230) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMSoy-00000005IQm-0jVa for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 16:17:44 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMSow-00000001YPn-25h4 for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 16:17:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 69TGHaCa1974819; Thu, 29 Oct 2026 12:17:36 -0400 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 69TGHaCa1974819 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1793290656; bh=Lbzi+0x6hI0sq5xEwtEzTa68aXooVFnm28mUlJ6jnTc=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=a84u6e9utyYQZXwAiS0HQ/nZraAmVQsW1dV20vMRnammm3fZLh4KPQf6o7R0YOmlT KPZudJMeyHoAa7HeTZLXgmr0j6Um8mvhPB9LjzGejLz7dFx5pyS+mzO72C8OJxSak0 i+29/z/VU1sP0jX8bq3pI/aRFpGjpQYrJ/F0kOGc= From: Tom Lane To: Tender Wang cc: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather` In-reply-to: References: <19804-594c35d8647fd7ef@postgresql.org> Comments: In-reply-to Tender Wang message dated "Thu, 29 Oct 2026 23:39:23 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <1974817.1793290656.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 29 Oct 2026 12:17:36 -0400 Message-ID: <1974818.1793290656@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Tender Wang writes: > PG Bug reporting form =E4=BA=8E2026=E5=B9=B410=E6= =9C=8829=E6=97=A5=E5=91=A8=E5=9B=9B 18:43=E5=86=99=E9=81=93=EF=BC=9A >> SELECT count(DISTINCT a.x + 1) >> FROM a JOIN b TABLESAMPLE BERNOULLI (100) ON b.y =3D a.x + 1 >> GROUP BY b.y * 2; > I investigated this with gdb. The failure appears to involve the > computability check introduced by 9f954177. > The Gather and its NestLoop subplan initially both have the targetlist: > [b.y * 2, a.x] > When preparing the sort for the presorted aggregate, the first pathkey > matches the existing expression b.y * 2. The second pathkey refers to > the join EC {b.y, a.x + 1}, but neither member matches an existing > targetlist entry. Yeah. So, looking back at the reasoning for 9f954177: ... We will not compute the EC expression in a plan node atop the one returning the passed-in targetlist; rather, the EC expression will be computed as an additional column of that targetlist. So any Var or quasi-Var used in the given tlist is also available to the EC expression. In simple cases this makes no difference because the given tlist is just a list of Vars or quasi-Vars --- but if we are considering an appendrel member produced by flattening a UNION ALL, the tlist may contain expressions, resulting in failure to match and a "could not find pathkey item to sort" error. The hidden assumption in that is that the node just below this one outputs a tlist that is just Vars and quasi-Vars (ie PHVs), so that pull_var_clause() will produce an accurate representation of what is available as input. Clearly, that fails here. It looks to me like prepare_sort_from_pathkeys() could look at the subplan(s) of the given Plan node to see what they are actually returning, but I wonder whether it'd be better to change its API to have the caller specify what the input is. We're going to have to modify the way that create_append_plan does this in any case, since right now it does prepare_sort_from_pathkeys() on the Append plan before it's attached any child plans. The comment for relation_can_be_sorted_early looks like that function might need rejiggering too, if prepare_sort_from_pathkeys starts to behave differently. I didn't look closely though. regards, tom lane