bug13.sql

application/sql

Download

-- 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);
        DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);
-- 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
--
-- maybe with different datasets also this tables produce wrong results
-- postgres 12.5 uses the same query plan without any error


--    second service ----------------------------------------------------------------------
--    different query plan after analyse works
-- 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);
        DELETE FROM t1 WHERE (id1,id2) IN (SELECT id1,id2 FROM t2);
SELECT * from t1;