LEFT JOIN issue
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)
On Tuesday 09 September 2008, "David Jaquay" <djaquay@gmail.com> wrote:
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.
explain doesn't execute the query. Show the output of the actual select - it
doesn't return any rows on my 8.3.3 database.
--
Alan
Just got an email saying that this has been fixed in 8.3.4, so I
suppose I'll watch and wait for that. Thanks for looking, Alan (and
anyone else who was watching this...)
Dave
Show quoted text
On Tue, Sep 9, 2008 at 4:57 PM, Alan Hodgson <ahodgson@simkin.ca> wrote:
On Tuesday 09 September 2008, "David Jaquay" <djaquay@gmail.com> wrote:
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.explain doesn't execute the query. Show the output of the actual select - it
doesn't return any rows on my 8.3.3 database.--
Alan--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general