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: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
On Thu, Jul 27, 2006 at 07:30:01PM +0000, Steven Adams wrote:
> select, iac.internal
> from information_assets as ia
> left outer join information_asset_categories as iac on(ia.category_id =
> and = 21
> This causes all rows in information_assets to be returned despite the "and"
> clause.  Adding "join information_assets as ia2 on( =" after
> the outer join corrects this.


    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 = 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


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-2017 The PostgreSQL Global Development Group