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

"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

"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/

"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

"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