Ensure that EquivalenceClasses generated from ORDER BY keys contain proper

RelabelType nodes when the sort key is binary-compatible with the sort
operator rather than having exactly its input type.  We did this correctly
for index columns but not sort keys, leading to failure to notice that
a varchar index matches an ORDER BY request.  This requires a bit more work
in make_sort_from_pathkeys, but not anyplace else that I can find.
Per bug report and subsequent discussion.

2 files changed, 44 insertions, 23 deletions

diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index b48bd953894..2ad31203697 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -11,7 +11,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.86 2007/10/27 05:45:43 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.87 2007/11/02 18:54:15 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -277,6 +277,30 @@ make_pathkey_from_sortinfo(PlannerInfo *root,
 		elog(ERROR, "could not find opfamilies for ordering operator %u",
 			 ordering_op);
 
+	/*
+	 * When dealing with binary-compatible opclasses, we have to ensure that
+	 * the exposed type of the expression tree matches the declared input
+	 * type of the opclass, except when that is a polymorphic type
+	 * (compare the behavior of parse_coerce.c).  This ensures that we can
+	 * correctly match the indexkey or sortclause expression to other
+	 * expressions we find in the query, because arguments of ordinary
+	 * operator expressions will be cast that way.  (We have to do this
+	 * for indexkeys because they are represented without any explicit
+	 * relabel in pg_index, and for sort clauses because the parser is
+	 * likewise cavalier about putting relabels on them.)
+	 */
+	if (exprType((Node *) expr) != opcintype &&
+		!IsPolymorphicType(opcintype))
+	{
+		/* Strip any existing RelabelType, and add a new one */
+		while (expr && IsA(expr, RelabelType))
+			expr = (Expr *) ((RelabelType *) expr)->arg;
+		expr = (Expr *) makeRelabelType(expr,
+										opcintype,
+										-1,
+										COERCE_DONTCARE);
+	}
+
 	/* Now find or create a matching EquivalenceClass */
 	eclass = get_eclass_for_sort_expr(root, expr, opcintype, opfamilies);
 
@@ -495,27 +519,6 @@ build_index_pathkeys(PlannerInfo *root,
 			indexprs_item = lnext(indexprs_item);
 		}
 
-		/*
-		 * When dealing with binary-compatible indexes, we have to ensure that
-		 * the exposed type of the expression tree matches the declared input
-		 * type of the opclass, except when that is a polymorphic type
-		 * (compare the behavior of parse_coerce.c).  This ensures that we can
-		 * correctly match the indexkey expression to expressions we find in
-		 * the query, because arguments of operators that could match the
-		 * index will be cast likewise.
-		 */
-		if (exprType((Node *) indexkey) != index->opcintype[i] &&
-			!IsPolymorphicType(index->opcintype[i]))
-		{
-			/* Strip any existing RelabelType, and add a new one */
-			while (indexkey && IsA(indexkey, RelabelType))
-				indexkey = (Expr *) ((RelabelType *) indexkey)->arg;
-			indexkey = (Expr *) makeRelabelType(indexkey,
-												index->opcintype[i],
-												-1,
-												COERCE_DONTCARE);
-		}
-
 		/* OK, make a canonical pathkey for this sort key */
 		cpathkey = make_pathkey_from_sortinfo(root,
 											  indexkey,
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 23099df6dc3..fb9b437174f 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.231 2007/05/21 17:57:34 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.232 2007/11/02 18:54:15 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2756,12 +2756,30 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 
 			if (em->em_is_const || em->em_is_child)
 				continue;
+
 			tle = tlist_member((Node *) em->em_expr, tlist);
 			if (tle)
 			{
 				pk_datatype = em->em_datatype;
 				break;			/* found expr already in tlist */
 			}
+
+			/*
+			 * We can also use it if the pathkey expression is a relabel
+			 * of the tlist entry.  This is needed for binary-compatible
+			 * cases (cf. make_pathkey_from_sortinfo).
+			 */
+			if (IsA(em->em_expr, RelabelType))
+			{
+				Expr	   *rtarg = ((RelabelType *) em->em_expr)->arg;
+
+				tle = tlist_member((Node *) rtarg, tlist);
+				if (tle)
+				{
+					pk_datatype = em->em_datatype;
+					break;			/* found expr already in tlist */
+				}
+			}
 		}
 		if (!tle)
 		{