From: Tom Lane <tgl@sss.pgh.pa.us>
To: s.p.e@gmx-topmail.de
Cc: pgsql-bugs@lists.postgresql.org, Jeff Davis <jdavis@postgresql.org>
Subject: Re: Postgress 13.x: wrong result for delete with subquery
Date: Fri, 29 Jan 2021 17:26:17 -0500
Message-ID: <2828817.1611959177@sss.pgh.pa.us> (raw)
In-Reply-To: <trinity-1c565d44-159f-488b-a518-caf13883134f-1611835701633@3c-app-gmx-bap78>
References: <trinity-1c565d44-159f-488b-a518-caf13883134f-1611835701633@3c-app-gmx-bap78>
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=853.22..1730.13 rows=0 width=0)
-> Hash Join (cost=853.22..1730.13 rows=7289 width=12)
Output: t1.ctid, t2.ctid
Inner Unique: true
Hash Cond: (((t1.id1)::text = (t2.id1)::text) AND ((t1.id2)::text = (t2.id2)::text))
-> Seq Scan on pg_temp_3.t1 (cost=0.00..634.55 rows=29155 width=74)
Output: t1.ctid, t1.id1, t1.id2
-> Hash (cost=809.48..809.48 rows=2916 width=74)
Output: t2.ctid, t2.id1, t2.id2
-> HashAggregate (cost=780.32..809.48 rows=2916 width=74)
Output: t2.ctid, t2.id1, t2.id2
Group Key: (t2.id1)::text, (t2.id2)::text
-> Seq Scan on pg_temp_3.t2 (cost=0.00..634.55 rows=29155 width=74)
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
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, s.p.e@gmx-topmail.de, jdavis@postgresql.org
Subject: Re: Postgress 13.x: wrong result for delete with subquery
In-Reply-To: <2828817.1611959177@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