Обсуждение: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

Hi,

I have a query that throws error "RIGHT JOIN is only supported with
merge-joinable join conditions".
This should allow it to be reproduce.

create table table1 (t1id integer not null, extension integer not null);
create table table2 (t1id integer not null, t3id integer not null, original
integer not null, replacement integer not null);
create table table3 (t3id integer not null);
create unique index table3ix1 on table3 (t3id);
insert into table3 select * from generate_series(1,10000);

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 am on PostgreSQL 8.1 beta3 under Windows XP Service Pack 2.

Thanks
Jean-Pierre Pelletier
e-djuster

"Jean-Pierre Pelletier" <pelletier_32@sympatico.ca> writes:
> I have a query that throws error "RIGHT JOIN is only supported with
> merge-joinable join conditions".

Wow, that's a goodie ... seems to fail all the way back to 7.2 ...
thanks for the report.

            regards, tom lane

"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;
  }

Thanks for the speedy fix.

I agree that this is not a typical query, in it Table2.t3id and Table3.t3id
would always join
(a foreing key constraint ensure that) but columns from Table3 should
sometimes be excluded which is taken care by "table1.extension in
(table2.replacement)".

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, October 25, 2005 4:34 PM
Subject: Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join
conditions, PostgreSQL 8.1 beta3


> "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;
>  }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match