Re: [SQL] RIGHT JOIN is only supported with mergejoinable join conditions

"Nick Fankhauser" <nickf(at)ontko(dot)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