Thread: BUG #2553: Outer join bug

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.

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@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.

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.

"Steven Adams" <swadams3@comcast.net> writes:
> 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.

AFAICS that's correct behavior.  I think you're confused about SQL
syntax: the way you've written it, the "ia.id = 21" condition is
part of the left join's ON clause, and therefore it cannot cause any
rows from the left side of the join to be omitted.  (Hint: those
parentheses are just noise.)  Perhaps you meant to write WHERE ia.id = 21?

            regards, tom lane

On Thu, Jul 27, 2006 at 07:30:01PM +0000, Steven Adams wrote:
> 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.

http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN

LEFT OUTER JOIN

    First, an inner join is performed.  Then, for each row in T1
    that does not satisfy the join condition with any row in T2, a
    joined row is added with null values in columns of T2.  Thus,
    the joined table unconditionally has at least one row for each
    row in T1.

The "and ia.id = 21" expression is part of the outer join condition
that restricts rows from information_asset_categories (T2); it
doesn't restrict rows from information_assets (T1).  If you don't
want all rows from information_assets then why are you using an
outer join?

--
Michael Fuhr

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Fri, Jul 28, 2006 at 09:54:42AM -0500, Steven Adams wrote:
> I wanted the row to show whether or not there was a matching row in the
> other table, but I wanted to return exactly 1 row.

As Tom Lane already pointed out, you're probably needing a WHERE
clause.  Does this do what you want?

SELECT ia.name, iac.internal
FROM information_assets AS ia
LEFT OUTER JOIN information_asset_categories AS iac ON ia.category_id = iac.id
WHERE ia.id = 21;

--
Michael Fuhr