Re: [BUGS] BUG #2553: Outer join bug
[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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2553: Outer join bug
On Thu, Jul 27, 2006 at 07:30:01PM +, 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2553: Outer join bug
"Steven Adams" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] 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: [EMAIL PROTECTED] > 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] BUG #2553: Outer join bug
The following bug has been logged online: Bug reference: 2553 Logged by: Steven Adams Email address: [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
