pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (3+ messages)  latest in thread

Message-ID: <2828817.1611959177@sss.pgh.pa.us>
Permalink:  ../2828817.1611959177@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2828817.1611959177@sss.pgh.pa.us

 · 

reply

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