Re: Optimizer problem with subselect.c? - Mailing list pgsql-bugs

"Daniel O'Neill" <daniel@fatport.com> writes:
> [ complex test case ]

Got it.  The patch against 7.4.* is attached and will appear in 7.4.2.

            regards, tom lane

Index: createplan.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.157.2.2
diff -c -r1.157.2.2 createplan.c
*** createplan.c    18 Jan 2004 00:31:53 -0000    1.157.2.2
--- createplan.c    29 Feb 2004 17:29:48 -0000
***************
*** 101,107 ****
  static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
            AttrNumber *sortColIdx, Oid *sortOperators);
  static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         Relids relids, List *pathkeys);


  /*
--- 101,107 ----
  static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
            AttrNumber *sortColIdx, Oid *sortOperators);
  static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         List *pathkeys);


  /*
***************
*** 1015,1021 ****
          outer_plan = (Plan *)
              make_sort_from_pathkeys(root,
                                      outer_plan,
-                           best_path->jpath.outerjoinpath->parent->relids,
                                      best_path->outersortkeys);
      }

--- 1015,1020 ----
***************
*** 1025,1031 ****
          inner_plan = (Plan *)
              make_sort_from_pathkeys(root,
                                      inner_plan,
-                           best_path->jpath.innerjoinpath->parent->relids,
                                      best_path->innersortkeys);
      }

--- 1024,1029 ----
***************
*** 1793,1799 ****
   *      Create sort plan to sort according to given pathkeys
   *
   *      'lefttree' is the node which yields input tuples
-  *      'relids' is the set of relids represented by the input node
   *      'pathkeys' is the list of pathkeys by which the result is to be sorted
   *
   * We must convert the pathkey information into arrays of sort key column
--- 1791,1796 ----
***************
*** 1806,1813 ****
   * adding a Result node just to do the projection.
   */
  static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         Relids relids, List *pathkeys)
  {
      List       *tlist = lefttree->targetlist;
      List       *sort_tlist;
--- 1803,1809 ----
   * adding a Result node just to do the projection.
   */
  static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree, List *pathkeys)
  {
      List       *tlist = lefttree->targetlist;
      List       *sort_tlist;
***************
*** 1852,1863 ****
          }
          if (!resdom)
          {
!             /* No matching Var; look for an expression */
              foreach(j, keysublist)
              {
                  pathkey = lfirst(j);
!                 if (bms_is_subset(pull_varnos(pathkey->key), relids))
!                     break;
              }
              if (!j)
                  elog(ERROR, "could not find pathkey item to sort");
--- 1848,1869 ----
          }
          if (!resdom)
          {
!             /* No matching Var; look for a computable expression */
              foreach(j, keysublist)
              {
+                 List   *exprvars;
+                 List   *k;
+
                  pathkey = lfirst(j);
!                 exprvars = pull_var_clause(pathkey->key, false);
!                 foreach(k, exprvars)
!                 {
!                     if (!tlist_member(lfirst(k), tlist))
!                         break;
!                 }
!                 freeList(exprvars);
!                 if (!k)
!                     break;        /* found usable expression */
              }
              if (!j)
                  elog(ERROR, "could not find pathkey item to sort");