pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RIGHT JOIN is only supported with mergejoinable join conditions
8+ messages / 3 participants
[nested] [flat]

* RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 16:16  Nick Fankhauser <nickf@ontko.com>
  0 siblings, 4 replies; 8+ messages in thread

From: Nick Fankhauser @ 2001-11-07 16:16 UTC (permalink / raw)
  To: pgsql-sql

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/




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join
@ 2001-11-07 16:49  Josh Berkus <josh@agliodbs.com>
  parent: Nick Fankhauser <nickf@ontko.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Josh Berkus @ 2001-11-07 16:49 UTC (permalink / raw)
  To: nickf@ontko.com; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 17:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nick Fankhauser <nickf@ontko.com>
  3 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2001-11-07 17:38 UTC (permalink / raw)
  To: nickf@ontko.com; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 18:29  Nick Fankhauser <nickf@ontko.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Nick Fankhauser @ 2001-11-07 18:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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
> 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 19:06  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nick Fankhauser <nickf@ontko.com>
  3 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2001-11-07 19:06 UTC (permalink / raw)
  To: nickf@ontko.com; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 22:22  Nick Fankhauser <nickf@ontko.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Nick Fankhauser @ 2001-11-07 22:22 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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)
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 22:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nick Fankhauser <nickf@ontko.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-11-07 22:48 UTC (permalink / raw)
  To: nickf@ontko.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-11 19:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nick Fankhauser <nickf@ontko.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-11-11 19:37 UTC (permalink / raw)
  To: nickf@ontko.com; +Cc: pgsql-hackers@postgreSQL.org

"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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-11-11 19:37 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
2001-11-07 16:49 ` Re: RIGHT JOIN is only supported with mergejoinable join Josh Berkus <josh@agliodbs.com>
2001-11-07 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 18:29   ` Nick Fankhauser <nickf@ontko.com>
2001-11-07 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 22:22   ` Nick Fankhauser <nickf@ontko.com>
2001-11-07 22:48     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-11 19:37 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox