The following bug has been logged online:
Bug reference: 4413
Logged by: David Jaquay
Email address: djaquay@gmail.com
PostgreSQL version: 8.3.3
Operating system: Ubuntu Heron
Description: LEFT JOIN not working as expected
Details:
I'm seeing a problem with a LEFT JOIN. 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)
