Merge join bug? - Mailing list pgsql-bugs

Someone has reported to me that VACUUM ANALYZE is causing different
results for the same query.  They believe it is caused by merge join.

I tested in both 8.1.X and CVS HEAD and both appear to be affected. SQL
test attached.

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +
DROP TABLE t1;
DROP TABLE t3;
DROP TABLE t2;

CREATE TABLE t2
(
  t2_id int4 NOT NULL,
  CONSTRAINT t2_pkey PRIMARY KEY (t2_id)
)
WITHOUT OIDS;

CREATE TABLE t1
(
  t1_id int4 NOT NULL,
  t2_id int4,
  CONSTRAINT t1_pkey PRIMARY KEY (t1_id),
  CONSTRAINT fk_t2 FOREIGN KEY (t2_id)
      REFERENCES t2 (t2_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

CREATE TABLE t3
(
  t2_id int4 NOT NULL,
  t3_id int4 NOT NULL,
  CONSTRAINT t3_pkey PRIMARY KEY (t2_id, t3_id),
  CONSTRAINT fk_t2 FOREIGN KEY (t2_id)
      REFERENCES t2 (t2_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

INSERT INTO t2 (t2_id) VALUES (21);
INSERT INTO t3 (t2_id, t3_id) VALUES (21, 31);
INSERT INTO t3 (t2_id, t3_id) VALUES (21, 32);
INSERT INTO t1 (t1_id, t2_id) VALUES (2, NULL);
INSERT INTO t1 (t1_id, t2_id) VALUES (1, 21);

set enable_hashjoin to off;

select *
from t1
  left outer join t2
    on t1.t2_id = t2.t2_id
  left outer join t3
    on t2.t2_id = t3.t2_id;

VACUUM ANALYZE;

set enable_hashjoin to on;

select *
from t1
  left outer join t2
    on t1.t2_id = t2.t2_id
  left outer join t3
    on t2.t2_id = t3.t2_id;