LEFT JOIN issue - Mailing list pgsql-general

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)