BUG #4413: LEFT JOIN not working as expected

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)