Re: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

"Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca> writes:
> select
> count(table3.*)
> from
> table1
> inner join table2
> on table1.t1id = table2.t1id
> and table1.extension in (table2.original, table2.replacement)
> left outer join table3
> on table2.t3id = table3.t3id
> and table1.extension in (table2.replacement);

I've applied the attached patch (for 8.1, variants as needed for back
branches) to fix this failure.

BTW, I think the reason nobody saw this before is that using a condition
on table1 vs table2 in the outer-join condition for table3 is a bit, er,
weird. Are you sure that the original query will do what you really
wanted?

But anyway, many thanks for the test case!

regards, tom lane

Index: joinpath.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.96
diff -c -r1.96 joinpath.c
*** joinpath.c 15 Oct 2005 02:49:20 -0000 1.96
--- joinpath.c 25 Oct 2005 19:52:54 -0000
***************
*** 795,800 ****
--- 795,801 ----
{
List *result_list = NIL;
bool isouterjoin = IS_OUTER_JOIN(jointype);
+ bool have_nonmergeable_joinclause = false;
ListCell *l;

foreach(l, restrictlist)
***************
*** 803,844 ****

/*
* If processing an outer join, only use its own join clauses in the
! * merge. For inner joins we need not be so picky.
! *
! * Furthermore, if it is a right/full join then *all* the explicit join
! * clauses must be mergejoinable, else the executor will fail. If we
! * are asked for a right join then just return NIL to indicate no
! * mergejoin is possible (we can handle it as a left join instead). If
! * we are asked for a full join then emit an error, because there is
! * no fallback.
*/
! if (isouterjoin)
! {
! if (restrictinfo->is_pushed_down)
! continue;
! switch (jointype)
! {
! case JOIN_RIGHT:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! return NIL; /* not mergejoinable */
! break;
! case JOIN_FULL:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("FULL JOIN is only supported with merge-joinable join conditions")));
! break;
! default:
! /* otherwise, it's OK to have nonmergeable join quals */
! break;
! }
! }

if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
continue; /* not mergejoinable */

/*
* Check if clause is usable with these input rels. All the vars
--- 804,822 ----

/*
* If processing an outer join, only use its own join clauses in the
! * merge. For inner joins we can use pushed-down clauses too.
! * (Note: we don't set have_nonmergeable_joinclause here because
! * pushed-down clauses will become otherquals not joinquals.)
*/
! if (isouterjoin && restrictinfo->is_pushed_down)
! continue;

if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
+ {
+ have_nonmergeable_joinclause = true;
continue; /* not mergejoinable */
+ }

/*
* Check if clause is usable with these input rels. All the vars
***************
*** 856,865 ****
--- 834,870 ----
/* lefthand side is inner */
}
else
+ {
+ have_nonmergeable_joinclause = true;
continue; /* no good for these input relations */
+ }

result_list = lcons(restrictinfo, result_list);
}

+ /*
+ * If it is a right/full join then *all* the explicit join clauses must be
+ * mergejoinable, else the executor will fail. If we are asked for a right
+ * join then just return NIL to indicate no mergejoin is possible (we can
+ * handle it as a left join instead). If we are asked for a full join then
+ * emit an error, because there is no fallback.
+ */
+ if (have_nonmergeable_joinclause)
+ {
+ switch (jointype)
+ {
+ case JOIN_RIGHT:
+ return NIL; /* not mergejoinable */
+ case JOIN_FULL:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FULL JOIN is only supported with merge-joinable join conditions")));
+ break;
+ default:
+ /* otherwise, it's OK to have nonmergeable join quals */
+ break;
+ }
+ }
+
return result_list;
}