Skip site navigation (1) Skip section navigation (2)

Re: BUG #2553: Outer join bug

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Steven Adams <swadams3(at)comcast(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2553: Outer join bug
Date: 2006-07-28 04:07:13
Message-ID: 20060728040713.GA80083@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Richard HuxtonDate: 2006-07-28 13:34:40
Subject: Re: Query returned unhandled type 16411
Previous:From: Tom LaneDate: 2006-07-28 03:54:43
Subject: Re: BUG #2553: Outer join bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group