Support ORDER BY ... NULLS FIRST/LAST, and add ASC/DESC/NULLS FIRST/NULLS LAST

per-column options for btree indexes.  The planner's support for this is still
pretty rudimentary; it does not yet know how to plan mergejoins with
nondefault ordering options.  The documentation is pretty rudimentary, too.
I'll work on improving that stuff later.

Note incompatible change from prior behavior: ORDER BY ... USING will now be
rejected if the operator is not a less-than or greater-than member of some
btree opclass.  This prevents less-than-sane behavior if an operator that
doesn't actually define a proper sort ordering is selected.

2 files changed, 42 insertions, 20 deletions

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index d1ac0740c64..3250beafb67 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.227 2007/01/05 22:19:32 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.228 2007/01/09 02:14:13 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -1147,7 +1147,7 @@ has_distinct_on_clause(Query *query)
 				continue;		/* we can ignore unsorted junk cols */
 			return true;		/* definitely not in DISTINCT list */
 		}
-		if (targetIsInSortList(tle, query->distinctClause))
+		if (targetIsInSortList(tle, InvalidOid, query->distinctClause))
 		{
 			if (tle->resjunk)
 				return true;	/* junk TLE in DISTINCT means DISTINCT ON */
@@ -1158,7 +1158,7 @@ has_distinct_on_clause(Query *query)
 			/* This TLE is not in DISTINCT list */
 			if (!tle->resjunk)
 				return true;	/* non-junk, non-DISTINCT, so DISTINCT ON */
-			if (targetIsInSortList(tle, query->sortClause))
+			if (targetIsInSortList(tle, InvalidOid, query->sortClause))
 				return true;	/* sorted, non-distinct junk */
 			/* unsorted junk is okay, keep looking */
 		}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 353f2debe4c..9150f1d936f 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.130 2007/01/05 22:19:33 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.131 2007/01/09 02:14:13 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -142,7 +142,6 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			IndexOptInfo *info;
 			int			ncolumns;
 			int			i;
-			int16		amorderstrategy;
 
 			/*
 			 * Extract info from the relation descriptor for the index.
@@ -169,12 +168,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			info->ncolumns = ncolumns = index->indnatts;
 
 			/*
-			 * Need to make opfamily and ordering arrays large enough to put
-			 * a terminating 0 at the end of each one.
+			 * Need to make opfamily array large enough to put a terminating
+			 * zero at the end.
 			 */
 			info->indexkeys = (int *) palloc(sizeof(int) * ncolumns);
 			info->opfamily = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1));
-			info->ordering = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1));
+			/* initialize these to zeroes in case index is unordered */
+			info->fwdsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns);
+			info->revsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns);
+			info->nulls_first = (bool *) palloc0(sizeof(bool) * ncolumns);
 
 			for (i = 0; i < ncolumns; i++)
 			{
@@ -189,22 +191,42 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			/*
 			 * Fetch the ordering operators associated with the index, if any.
 			 */
-			amorderstrategy = indexRelation->rd_am->amorderstrategy;
-			if (amorderstrategy > 0)
+			if (indexRelation->rd_am->amorderstrategy > 0)
 			{
-				int			oprindex = amorderstrategy - 1;
-
-				/*
-				 * Index AM must have a fixed set of strategies for it to
-				 * make sense to specify amorderstrategy, so we need not
-				 * allow the case amstrategies == 0.
-				 */
-				Assert(oprindex < indexRelation->rd_am->amstrategies);
+				int			nstrat = indexRelation->rd_am->amstrategies;
 
 				for (i = 0; i < ncolumns; i++)
 				{
-					info->ordering[i] = indexRelation->rd_operator[oprindex];
-					oprindex += indexRelation->rd_am->amstrategies;
+					int16	opt = indexRelation->rd_indoption[i];
+					int		fwdstrat;
+					int		revstrat;
+
+					if (opt & INDOPTION_DESC)
+					{
+						fwdstrat = indexRelation->rd_am->amdescorder;
+						revstrat = indexRelation->rd_am->amorderstrategy;
+					}
+					else
+					{
+						fwdstrat = indexRelation->rd_am->amorderstrategy;
+						revstrat = indexRelation->rd_am->amdescorder;
+					}
+					/*
+					 * Index AM must have a fixed set of strategies for it
+					 * to make sense to specify amorderstrategy, so we
+					 * need not allow the case amstrategies == 0.
+					 */
+					if (fwdstrat > 0)
+					{
+						Assert(fwdstrat <= nstrat);
+						info->fwdsortop[i] = indexRelation->rd_operator[i * nstrat + fwdstrat - 1];
+					}
+					if (revstrat > 0)
+					{
+						Assert(revstrat <= nstrat);
+						info->revsortop[i] = indexRelation->rd_operator[i * nstrat + revstrat - 1];
+					}
+					info->nulls_first[i] = (opt & INDOPTION_NULLS_FIRST) != 0;
 				}
 			}