I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I
believe to be an Ubuntu Heron server. The sql below demonstrates the
issue.
What I expect to see is no rows in the output, i.e. the LEFT JOIN
should pair the two rows together, and the WHERE clause should decide
that the joined row doesn't match, and should yield no output.
What happens is that the planner appears to apply the WHERE clause
early, the left table doesn't yield any rows, and the row from the
right table is output by itself. This only appears to happen when
both sides of the OR are present, and the idx_beta_datereceived index
is present. Remove any one, and it works like I expect.
Dave
create table alpha (
alphaid bigint not null,
betaid bigint null,
itemcode char(1) not null
);
ALTER TABLE ONLY alpha
ADD CONSTRAINT pk_alpha PRIMARY KEY (alphaid);
create table beta (
betaid bigint not null,
datereceived date null
);
ALTER TABLE ONLY beta
ADD CONSTRAINT pk_beta PRIMARY KEY (betaid);
create index idx_alpha_betaid on alpha(betaid);
insert into alpha values (22044, 92359002, 'U');
insert into beta values (92359002, '2008-08-11');
CREATE INDEX idx_beta_datereceived ON beta USING btree (datereceived);
analyze alpha;
analyze beta;
explain select alpha.alphaid as aid
, alpha.betaid as alphabetaid
, beta.betaid as betaid
, beta.datereceived
, alpha.itemcode
from alpha
left join beta on beta.betaid = alpha.betaid
where alpha.alphaid = 22044
and ( beta.datereceived IS NULL
OR
( beta.betaid IS NULL
AND alpha.itemcode='U'
)
);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..2.04 rows=1 width=30)
Join Filter: (beta.betaid = alpha.betaid)
Filter: ((beta.datereceived IS NULL) OR ((beta.betaid IS NULL) AND
(alpha.itemcode = 'U'::bpchar)))
-> Seq Scan on alpha (cost=0.00..1.01 rows=1 width=18)
Filter: (alphaid = 22044)
-> Seq Scan on beta (cost=0.00..1.01 rows=1 width=12)
Filter: ((beta.datereceived IS NULL) OR (beta.betaid IS NULL))
(7 rows)
