Merge join bug?

Поиск
Список
Период
Сортировка
От Bruce Momjian
Тема Merge join bug?
Дата
Msg-id 200603170518.k2H5IHA12945@candle.pha.pa.us
обсуждение исходный текст
Ответы Re: Merge join bug?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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;


В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Dan B."
Дата:
Сообщение: BUG #2326: Problems Upgrading from 8.0.2
Следующее
От: tomas@tuxteam.de (Tomas Zerolo)
Дата:
Сообщение: Re: BUG #2318: language