Thread: Merge join bug?

Merge join bug?

From
Bruce Momjian
Date:
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;


Re: Merge join bug?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Someone has reported to me that VACUUM ANALYZE is causing different
> results for the same query.  They believe it is caused by merge join.

Fixed; bug was introduced here:

2005-05-13 17:20  tgl

    * src/: backend/executor/nodeMergejoin.c,
    include/executor/execdebug.h, include/executor/execdefs.h,
    include/nodes/execnodes.h: Revise nodeMergejoin in light of example
    provided by Guillaume Smet.  When one side of the join has a NULL,
    we don't want to uselessly try to match it against every remaining
    tuple of the other side.  While at it, rewrite the comparison
    machinery to avoid multiple evaluations of the left and right input
    expressions and to use a btree comparator where available, instead
    of double operator calls.  Also revise the state machine to
    eliminate redundant comparisons and hopefully make it more readable
    too.

            regards, tom lane