The following bug has been logged online:
Bug reference: 2553
Logged by: Steven Adams
Email address: swadams3@comcast.net
PostgreSQL version: 8.1.4
Operating system: Red Hat Linux 3.2.3-42
Description: Outer join bug
Details:
Every time I use an outer join as the last one in a query and there are
non-join conditions after it, those conditions are ignored. For example, if
a left outer join is the last one in the query, all rows of the left table
are returned, even if there is a condition that requires that table's
primary key column to equal a certain value. If I add an inner self join
after the outer join, the query returns only the row with the primary key
value specified in the "AND" clause after the joins, as it should.
The tables and query involved are as follows (with only the relevant columns
shown):
create table information_asset_categories(
ID integer not null,
internal boolean not null,
constraint information_asset_categories_PK primary key(ID));
create table information_assets(
ID integer not null,
name varchar not null,
category_ID integer,
constraint information_assets_PK primary key(ID),
constraint information_assets_categories_FK foreign key(category_ID)
references information_asset_categories(ID));
select ia.name, iac.internal
from information_assets as ia
left outer join information_asset_categories as iac on(ia.category_id =
iac.id)
and ia.id = 21
This causes all rows in information_assets to be returned despite the "and"
clause. Adding "join information_assets as ia2 on(ia.id = ia2.id)" after
the outer join corrects this.