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

Re: BUG #1409: A good and a bad news: Crazy SQL JOIN?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Lutischn Ferenc <yoursoft(at)freemail(dot)hu>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1409: A good and a bad news: Crazy SQL JOIN?
Date: 2005-01-23 02:27:50
Message-ID: 26090.1106447270@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> It looks like it thinks that the output is already sorted by b.col2 which
> would appear to be untrue if rows are being extended from a so I think
> this is a bug optimizing the query.

Yup.  Looks like this bug has been there since day one (ever since we
supported outer joins, that is).  I've patched it back as far as 7.2.

			regards, tom lane

*** src/backend/optimizer/path/joinpath.c.orig	Fri Dec 31 17:45:50 2004
--- src/backend/optimizer/path/joinpath.c	Sat Jan 22 20:44:49 2005
***************
*** 271,277 ****
  												   cur_mergeclauses,
  												   innerrel);
  		/* Build pathkeys representing output sort order. */
! 		merge_pathkeys = build_join_pathkeys(root, joinrel, outerkeys);
  
  		/*
  		 * And now we can make the path.
--- 271,278 ----
  												   cur_mergeclauses,
  												   innerrel);
  		/* Build pathkeys representing output sort order. */
! 		merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
! 											 outerkeys);
  
  		/*
  		 * And now we can make the path.
***************
*** 431,437 ****
  		 * as a nestloop, and even if some of the mergeclauses are
  		 * implemented by qpquals rather than as true mergeclauses):
  		 */
! 		merge_pathkeys = build_join_pathkeys(root, joinrel,
  											 outerpath->pathkeys);
  
  		if (nestjoinOK)
--- 432,438 ----
  		 * as a nestloop, and even if some of the mergeclauses are
  		 * implemented by qpquals rather than as true mergeclauses):
  		 */
! 		merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
  											 outerpath->pathkeys);
  
  		if (nestjoinOK)
*** src/backend/optimizer/path/pathkeys.c.orig	Fri Dec 31 17:45:50 2004
--- src/backend/optimizer/path/pathkeys.c	Sat Jan 22 20:44:50 2005
***************
*** 858,864 ****
--- 858,869 ----
   *	  vars they were joined with; furthermore, it doesn't matter what kind
   *	  of join algorithm is actually used.
   *
+  *	  EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
+  *	  having the outer path's path keys, because null lefthand rows may be
+  *	  inserted at random points.  It must be treated as unsorted.
+  *
   * 'joinrel' is the join relation that paths are being formed for
+  * 'jointype' is the join type (inner, left, full, etc)
   * 'outer_pathkeys' is the list of the current outer path's path keys
   *
   * Returns the list of new path keys.
***************
*** 866,873 ****
--- 871,882 ----
  List *
  build_join_pathkeys(Query *root,
  					RelOptInfo *joinrel,
+ 					JoinType jointype,
  					List *outer_pathkeys)
  {
+ 	if (jointype == JOIN_FULL || jointype == JOIN_RIGHT)
+ 		return NIL;
+ 
  	/*
  	 * This used to be quite a complex bit of code, but now that all
  	 * pathkey sublists start out life canonicalized, we don't have to do
*** src/include/optimizer/paths.h.orig	Fri Dec 31 17:46:56 2004
--- src/include/optimizer/paths.h	Sat Jan 22 20:44:43 2005
***************
*** 114,119 ****
--- 114,120 ----
  						Query *subquery);
  extern List *build_join_pathkeys(Query *root,
  					RelOptInfo *joinrel,
+ 					JoinType jointype,
  					List *outer_pathkeys);
  extern List *make_pathkeys_for_sortclauses(List *sortclauses,
  							  List *tlist);

In response to

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-01-23 03:54:12
Subject: Re: 8.0.0 pg_restore -L doesn't restore ACLs
Previous:From: Bruce MomjianDate: 2005-01-23 00:41:49
Subject: Re: BUG #1414: DOC - pl/Perl hash tags missing

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