Fix EquivalenceClass code to handle volatile sort expressions in a more

predictable manner; in particular that if you say ORDER BY output-column-ref,
it will in fact sort by that specific column even if there are multiple
syntactic matches.  An example is
	SELECT random() AS a, random() AS b FROM ... ORDER BY b, a;
While the use-case for this might be a bit debatable, it worked as expected
in earlier releases, so we should preserve the behavior for 8.3.  Per my
recent proposal.

While at it, fix convert_subquery_pathkeys() to handle RelabelType stripping
in both directions; it needs this for the same reasons make_sort_from_pathkeys
does.

3 files changed, 12 insertions, 5 deletions

diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 3890352022..db6eef2096 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.147 2007/10/11 18:05:27 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.148 2007/11/08 21:49:48 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -449,7 +449,10 @@ typedef struct IndexOptInfo
  * which is a case that can't arise otherwise since clauses containing
  * volatile functions are never considered mergejoinable.  We mark such
  * EquivalenceClasses specially to prevent them from being merged with
- * ordinary EquivalenceClasses.
+ * ordinary EquivalenceClasses.  Also, for volatile expressions we have
+ * to be careful to match the EquivalenceClass to the correct targetlist
+ * entry: consider SELECT random() AS a, random() AS b ... ORDER BY b,a.
+ * So we record the SortGroupRef of the originating sort clause.
  *
  * We allow equality clauses appearing below the nullable side of an outer join
  * to form EquivalenceClasses, but these have a slightly different meaning:
@@ -472,6 +475,7 @@ typedef struct EquivalenceClass
 	bool		ec_has_volatile;	/* the (sole) member is a volatile expr */
 	bool		ec_below_outer_join;	/* equivalence applies below an OJ */
 	bool		ec_broken;			/* failed to generate needed clauses? */
+	Index		ec_sortref;			/* originating sortclause label, or 0 */
 	struct EquivalenceClass *ec_merged;		/* set if merged into another EC */
 } EquivalenceClass;
 
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index cbde0c7b9a..cf589b48af 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/optimizer/paths.h,v 1.99 2007/09/26 18:51:51 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/optimizer/paths.h,v 1.100 2007/11/08 21:49:48 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -115,7 +115,8 @@ extern void reconsider_outer_join_clauses(PlannerInfo *root);
 extern EquivalenceClass *get_eclass_for_sort_expr(PlannerInfo *root,
 						 Expr *expr,
 						 Oid expr_datatype,
-						 List *opfamilies);
+						 List *opfamilies,
+						 Index sortref);
 extern void generate_base_implied_equalities(PlannerInfo *root);
 extern List *generate_join_implied_equalities(PlannerInfo *root,
 											  RelOptInfo *joinrel,
diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
index bb127cbce2..515339363a 100644
--- a/src/include/optimizer/tlist.h
+++ b/src/include/optimizer/tlist.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/optimizer/tlist.h,v 1.46 2007/11/08 19:25:37 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/optimizer/tlist.h,v 1.47 2007/11/08 21:49:48 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -23,6 +23,8 @@ extern TargetEntry *tlist_member_ignore_relabel(Node *node, List *targetlist);
 extern List *flatten_tlist(List *tlist);
 extern List *add_to_flat_tlist(List *tlist, List *vars);
 
+extern TargetEntry *get_sortgroupref_tle(Index sortref,
+						List *targetList);
 extern TargetEntry *get_sortgroupclause_tle(SortClause *sortClause,
 						List *targetList);
 extern Node *get_sortgroupclause_expr(SortClause *sortClause,