Обсуждение: RIGHT JOIN is only supported with mergejoinable join conditions
Hi-
I'm on version 7.1, and I'm getting this error when attempting to select
from a view:
RIGHT JOIN is only supported with mergejoinable join conditions
I don't understand what this error is telling me...
The script I have used to create the view is below pasted in below.
Essentially, I have a main table which I want to see every row from. I also
have two separate lookup tables that I want to get a description field from
*if* there is a matching code in a corresponding nullable field in the main
table. I tried pasting this into MSAccess, and it works fine there. (I know
this doesn't necessarily mean it is valid SQL <grin>.)
My questions are:
1)Have I done something wrong here, or am I hitting a limitation of
PostgreSQL?
2)In either case, how could I re-write this to make it work with PostgreSQL?
Thanks!
-Nick
create view demo as select case_data.case_id, case_disposition_code.case_disp_global_desc,
local_case_type.global_case_type_desc from local_case_type right join ( case_disposition_code
right join case_data on case_disposition_code.case_disp_local_code =
case_data.case_disp_local_code ) on ( local_case_type.court_id = case_data.court_id )
and ( local_case_type.local_case_subtype_code = case_data.local_case_type_code ) and
( local_case_type.local_case_subtype_code = case_data.local_case_subtype_code );
--------------------------------------------------------------------------
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/
Nick, > RIGHT JOIN is only supported with mergejoinable join conditions Woof! Talk about destruction testing. You have ... let's see ... a three-column right join on two right-joined tables. If you ahve uncovered a bug, I wouldn't be surprised. However, are you sure you want RIGHT OUTER JOINS and not LEFT? Try re-organizing the query as LEFT JOINS, and see if it works. create view demo as select case_data.case_id, case_disposition_code.case_disp_global_desc, local_case_type.global_case_type_desc from ( case_data left join case_disposition_code on case_data.case_disp_local_code = case_disposition_code.case_disp_local_code ) LEFT JOIN local_case_type ON (( local_case_type.court_id = case_data.court_id ) and ( local_case_type.local_case_subtype_code = case_data.local_case_type_code ) and ( local_case_type.local_case_subtype_code = case_data.local_case_subtype_code )); If that doesn't work, try making the case_data and case_disposition_code join into a subselect. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Nick Fankhauser" <nickf@ontko.com> writes:
> I'm on version 7.1, and I'm getting this error when attempting to select
> from a view:
> RIGHT JOIN is only supported with mergejoinable join conditions
What are the datatypes of the columns you're using?
regards, tom lane
They are all varchar. -Nick > > RIGHT JOIN is only supported with mergejoinable join conditions > > What are the datatypes of the columns you're using? > > regards, tom lane >
"Nick Fankhauser" <nickf@ontko.com> 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
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" <nickf@ontko.com> 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) >
"Nick Fankhauser" <nickf@ontko.com> writes:
> I agree that this still may be a bug.
It definitely is a bug --- we fixed a similar problem around 7.1.1 or
so, but this test case appears to expose a different variant of the
mistake. (The planner is generating a plan that the executor can't
handle; it's supposed to know not to do that.)
I think I know where to fix it, but am not confident enough in
my powers of analysis today to want to actually commit anything.
(I've had a bad head-cold all week and am still unable to do anything
that requires more than a few minutes of sustained thought :-()
Will get back on it as soon as I feel better...
regards, tom lane