Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
Date: 2005-10-25 20:34:15
Message-ID: 28846.1130272455@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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;
  }

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-10-25 20:39:45
Subject: Re: Variable not found in subplan target lists, PostgreSQL 8.1 beta3
Previous:From: Jean-Pierre PelletierDate: 2005-10-25 19:58:39
Subject: Variable not found in subplan target lists, PostgreSQL 8.1 beta3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group