Horton

Thread

Commits

  1. Fix bug in HashAgg's selective-column-spilling logic.

  2. HashAgg: before spilling tuples, set unneeded columns to NULL.

  1. Postgress 13.x: wrong result for delete with subquery

    s.p.e@gmx-topmail.de — 2021-01-28T12:08:21Z

      
     
  
  
    Hallo PSQL-Team,
  
  
     
  
  
    I found a dataset (attachment bug13.csv)  which produces a wrong result on postgreSQL 13 for a special delete command:
  
  
     
  
  
    Table t1 and t2 contain the same dataset, every row of the data (id1, id2) is unique
  
  
    So the command
  
  
      DELETE FROM t1 WHERE (id1,id2) IN (SELECT t2.id1, t2.id2 FROM t2);
  
  
    should remove all lines. So it does in postgres 10 and 12.5. In Version 13.0 and 13.1 there is on line left after the command.
  
  
     
  
  
     
  
  
    In detail:
  
  
     
  
  
    I have tested on PostgreSQL 10 (local instalaltion on SuSE-Linux) and three docker-Images (12.5, 13.0, 13.1).
  
  
    The docker container are installed from scratch
  
  
     
  
  
    docker run --name psqlXX    \

               --shm-size=880MB \

               -v $PWD/dXX:/var/lib/postgresql/data \

               -v $PWD/extra:/var/extra       \

               -e POSTGRES_PASSWORD=comirnaty \

               -d postgres:XX.X
  
  
     
  
  
    docker exec -it psqlXX bash

    psql -U postgres < /var/extra/bug13.sql > /var/extra/XXX.out
  
  
     
  
  
    bug13.sql does the following:
  
  
     
  
  
    
      -- wrong result on postgres 13.1 (docker)

      -- also tested postgres 13.0 (docker): same bug

      -- also tested postgres 12.5 (docker): no bug
    
    
       
    
    
      \set VERBOSITY verbose

      SELECT version();
    
    
      

      -- prepare the test-data-set, all rows are unique

      DROP TABLE IF EXISTS  tstdata;
    
    
      CREATE TABLE tstdata (

       id1           VARCHAR(  3),

       id2           VARCHAR( 25)

      );

      CREATE UNIQUE INDEX tst_idx ON tstdata (id1, id2);
    
    
      COPY tstdata (id1, id2) FROM '/var/extra/bug13.csv';
    
    
      

      -- tables for the test

      DROP TABLE IF EXISTS  t1, t2;
    
    
      CREATE TEMPORARY TABLE t1 (

         id1 VARCHAR( 32),

         id2 VARCHAR( 32) 

      );

      CREATE UNIQUE INDEX t1_idx ON t1 (id1,id2);
    
    
      CREATE TEMPORARY TABLE t2 (

         id1 VARCHAR( 32),

         id2 VARCHAR( 32) 

      );
    
    
      

      -- fill the tst tables with data

      INSERT INTO t1 SELECT id1,id2 from tstdata;

      INSERT INTO t2 SELECT id1,id2 from tstdata;
    
    
       
    
    
       
    
    
      -- here ist the test for the bug with wrong result

      -- t1 and t2 contain the identical set

      -- so this command should remove all entries in t1
    
    
      EXPLAIN DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);
    
    
      --                                           QUERY PLAN                                          

      -- ----------------------------------------------------------------------------------------------

      --  Delete on t1  (cost=595.12..1201.93 rows=3602 width=12)

      --    ->  Hash Join  (cost=595.12..1201.93 rows=3602 width=12)

      --          Hash Cond: (((t1.id1)::text = (t2.id1)::text) AND ((t1.id2)::text = (t2.id2)::text))

      --          ->  Seq Scan on t1  (cost=0.00..487.06 rows=14406 width=170)

      --          ->  Hash  (cost=573.50..573.50 rows=1441 width=170)

      --                ->  HashAggregate  (cost=559.09..573.50 rows=1441 width=170)

      --                      Group Key: (t2.id1)::text, (t2.id2)::text

      --                      ->  Seq Scan on t2  (cost=0.00..487.06 rows=14406 width=170)

      

      DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);
    
    
      

      -- on version 13.x there is still one entry left

      --  MED | 0000000000000010035567472 

       
    
    
      SELECT * from t1;
    
    
       
    
    
      -- sometimes after multiple tries: the bug disappears, after recreating t2 the bug recurs

      --   - creating an index on t2 -> correct result 

      --   - define t2 non temporary -> correct result

      --   - analyse t2              -> correct result

      -- postgres 12.5 uses the same query plan without any error
    
    
       
    
    
       
    
    
      

       
    
    
      --    second service ----------------------------------------------------------------------

      --    different query plan after analyse and the bug disappears

      -- refill t1

      DELETE FROM t1;

      INSERT INTO t1 SELECT id1,id2 from tstdata;

      ANALYSE t2;

      EXPLAIN DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);
    
    
      --                                           QUERY PLAN                                          

      -- ----------------------------------------------------------------------------------------------

      --  Delete on t1  (cost=1506.92..2718.77 rows=7192 width=12)

      --    ->  Hash Semi Join  (cost=1506.92..2718.77 rows=7192 width=12)

      --          Hash Cond: (((t1.id1)::text = (t2.id1)::text) AND ((t1.id2)::text = (t2.id2)::text))

      --          ->  Seq Scan on t1  (cost=0.00..972.70 rows=28770 width=170)

      --          ->  Hash  (cost=808.57..808.57 rows=46557 width=36)

      --                ->  Seq Scan on t2  (cost=0.00..808.57 rows=46557 width=36)

      

      DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);

      SELECT * from t1;

       
    
  
  
     
  
  
    Data, Command and Output  are attached to this mail.
  
  
    I reduced the bug-inducing datsaset from initial 8 Mio entries to this example file. The bug is very sensitive to the exact data and disappears on the most changes.
  
  
     
  
  
    I hope, that a added everything necessary for further analysis.
  
  
    Best regards
  
  
     
  
  
    Stephan Endres
  
  
     

  2. Re: Postgress 13.x: wrong result for delete with subquery

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-01-29T22:26:17Z

    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

  3. Re: Postgress 13.x: wrong result for delete with subquery

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-02-05T04:02:45Z

    I wrote:
> 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.

Hearing nothing from Jeff, I went ahead and pushed a fix along that line.

			regards, tom lane