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

"Jean-Pierre Pelletier" <pelletier_32@sympatico.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;
  }