Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5cDj-0001s3-Gt for pgsql-bugs@arkaria.postgresql.org; Fri, 29 Jan 2021 22:26:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l5cDh-0007fX-Cq for pgsql-bugs@arkaria.postgresql.org; Fri, 29 Jan 2021 22:26:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5cDh-0007fP-6q for pgsql-bugs@lists.postgresql.org; Fri, 29 Jan 2021 22:26:25 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5cDf-0000wJ-0u; Fri, 29 Jan 2021 22:26:24 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 10TMQHH62828818; Fri, 29 Jan 2021 17:26:17 -0500 From: Tom Lane To: s.p.e@gmx-topmail.de cc: pgsql-bugs@lists.postgresql.org, Jeff Davis Subject: Re: Postgress 13.x: wrong result for delete with subquery In-reply-to: References: Comments: In-reply-to s.p.e@gmx-topmail.de message dated "Thu, 28 Jan 2021 13:08:21 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2828816.1611959177.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 29 Jan 2021 17:26:17 -0500 Message-ID: <2828817.1611959177@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk s.p.e@gmx-topmail.de writes: > I found a dataset (attachment bug13.csv); which produces a wrong result = on postgreSQL 13 for a special delete command: Yeah. "git bisect" pins this on Jeff's commit 230230223, and with a bit of digging it's not hard to see what the problem is. The plan ends up as a hash join on the varchar columns: Delete on pg_temp_3.t1 (cost=3D853.22..1730.13 rows=3D0 width=3D0) -> Hash Join (cost=3D853.22..1730.13 rows=3D7289 width=3D12) Output: t1.ctid, t2.ctid Inner Unique: true Hash Cond: (((t1.id1)::text =3D (t2.id1)::text) AND ((t1.id2)::te= xt =3D (t2.id2)::text)) -> Seq Scan on pg_temp_3.t1 (cost=3D0.00..634.55 rows=3D29155 w= idth=3D74) Output: t1.ctid, t1.id1, t1.id2 -> Hash (cost=3D809.48..809.48 rows=3D2916 width=3D74) Output: t2.ctid, t2.id1, t2.id2 -> HashAggregate (cost=3D780.32..809.48 rows=3D2916 width= =3D74) Output: t2.ctid, t2.id1, t2.id2 Group Key: (t2.id1)::text, (t2.id2)::text -> Seq Scan on pg_temp_3.t2 (cost=3D0.00..634.55 ro= ws=3D29155 width=3D74) Output: t2.ctid, t2.id1, t2.id2, t2.id1, t2.id2 Notice that the hashagg's input relation produces five columns, ctid, id1, id2, id1::text, id2::text (EXPLAIN doesn't show the implicit casts on the last two, which are the hashing columns). find_hash_columns decides that only the first three of these need be spilled, which ends up making the hash keys NULL in reloaded tuples. The only astonishing thing about this test case is that just one tuple fails to be joined. So fundamentally, this patch confused "Vars in the qual trees" with "input columns that we might access", which is just wrong. In some cases, the input columns represent expressions not plain Vars. We might be able to salvage this by having find_hash_columns examine the Agg node's grpColIdx list and assume that columns listed there need to be preserved. But frankly, now that I've seen this case, I'm not sure that there's anything correct about the approach being used. We might be well advised to just revert 230230223 and think harder. regards, tom lane