Re: BUG #2553: Outer join bug

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 03:54:43
Message-ID: 16007.1154058883@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Steven Adams" <swadams3(at)comcast(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Fuhr 2006-07-28 04:07:13 Re: BUG #2553: Outer join bug
Previous Message Tom Lane 2006-07-28 03:48:16 Re: server stopped running abnormally