Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA7MONm27437 for ; Wed, 7 Nov 2001 17:24:23 -0500 (EST) (envelope-from nickf@ontko.com) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id RAA30679; Wed, 7 Nov 2001 17:24:06 -0500 X-Authentication-Warning: shire.ontko.com: Host bilbo.ontko.com [199.164.165.101] claimed to be nick Reply-To: From: "Nick Fankhauser" To: "Josh Berkus" , "Tom Lane" Cc: "PGSQL-SQL" Subject: Re: RIGHT JOIN is only supported with mergejoinable join conditions Date: Wed, 7 Nov 2001 17:22:13 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-reply-to: <5788.1005159968@sss.pgh.pa.us> X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 Importance: Normal X-Archive-Number: 200111/115 X-Sequence-Number: 5499 Tom, Josh: Thanks for the ideas! Tom's idea was the easiest to test, so I tried it first, and it worked! As you surmised, there was a typo, so I removed the extra "sub". I agree that this still may be a bug. These tables have been migrated forward from an older postgresql version & hence have no primary or foreign key constraints that might tip off the planner about my typo - as far as the database knows, these are just two varchar fields in separate tables. Your thought about the overlap causing the problem seems likely since this seems to be a valid query, even with the typo. At any rate, my immediate problem is solved & I'm a happy camper! Thanks. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Wednesday, November 07, 2001 2:06 PM > To: nickf@ontko.com > Cc: PGSQL-SQL > Subject: Re: [SQL] RIGHT JOIN is only supported with mergejoinable join > conditions > > > "Nick Fankhauser" writes: > > and > > ( > > local_case_type.local_case_subtype_code = > > case_data.local_case_type_code > > ) > > Did you actually mean to match local_case_subtype_code against > local_case_type_code, or is that a typo? > > I believe you have uncovered a planner bug, but the bug may be triggered > by the partial overlap of this join condition with the next one. > Assuming that it's a typo, you may find that you avoid the problem by > fixing the typo. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >