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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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