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/


Wed, 7 Nov 2001 11:16:38 -0500
"Nick Fankhauser" <nickf@ontko.com>
RIGHT JOIN is only supported with mergejoinable join conditions
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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Wed, 07 Nov 2001 08:49:10 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: RIGHT JOIN is only supported with mergejoinable join
"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

Wed, 07 Nov 2001 12:38:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RIGHT JOIN is only supported with mergejoinable join conditions 
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
> 

Wed, 7 Nov 2001 13:29:44 -0500
"Nick Fankhauser" <nickf@ontko.com>
Re: 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

Wed, 07 Nov 2001 14:06:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RIGHT JOIN is only supported with mergejoinable join conditions 
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)
>


Wed, 7 Nov 2001 17:22:13 -0500
"Nick Fankhauser" <nickf@ontko.com>
Re: RIGHT JOIN is only supported with mergejoinable join conditions 
"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

Wed, 07 Nov 2001 17:48:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RIGHT JOIN is only supported with mergejoinable join conditions 
"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

I have committed a fix for this problem --- of the three routines that
can generate mergejoin plans, only two were checking to ensure they'd
generated a valid join plan in RIGHT/FULL join cases.  I seem to recall
having deliberately decided that sort_inner_and_outer didn't need to
check, but your example proves that it does.

There is still a related problem with FULL JOIN, which is that *all*
the possible join plans may get rejected:

regression=# create table aa (v1 varchar, v2 varchar);
CREATE
regression=# create table bb (v1 varchar, v2 varchar, v3 varchar);
CREATE
regression=# select * from aa a full join bb b on
regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1 and a.v2 = b.v1;
ERROR:  Unable to devise a query plan for the given query
regression=#

This is not exactly fatal, since you can work around it by pushing
down the redundant join condition to one of the input relations:

regression=# select * from aa a full join bb b on
regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1
regression-# where a.v2 = a.v1;
[ okay ]

But it's pretty annoying anyway.  I'm trying to figure out how we could
implement the query as given...

			regards, tom lane

Sun, 11 Nov 2001 14:37:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] RIGHT JOIN is only supported with mergejoinable join
	conditions