Re: BUG #2553: Outer join bug

On Thu, 27 Jul 2006, Steven Adams wrote:

>
> The following bug has been logged online:
>
> Bug reference: 2553
> Logged by: Steven Adams
> Email address: swadams3(at)comcast(dot)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.

AFAICT that's correct behavior. The ON condition in the LEFT JOIN case
affects which rows are joined to actual rows on the right and which rows
are extended with NULLs but does not filter the rows on left.