--ELM1142572697-22411-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

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. +

--ELM1142572697-22411-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/rtmp/diff"

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;


--ELM1142572697-22411-0_
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--ELM1142572697-22411-0_--

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings