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

Stephan Szabo <sszabo@megazone.bigpanda.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);