Per-column collation support

This adds collation support for columns and domains, a COLLATE clause
to override it per expression, and B-tree index support.

Peter Eisentraut
reviewed by Pavel Stehule, Itagaki Takahiro, Robert Haas, Noah Misch

1 files changed, 49 insertions, 15 deletions

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index c74125f1f75..f01114c673a 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -105,7 +105,7 @@ static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
 			 List *tidquals);
 static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
 				  Index scanrelid, Node *funcexpr, List *funccolnames,
-				  List *funccoltypes, List *funccoltypmods);
+				  List *funccoltypes, List *funccoltypmods, List *funccolcollations);
 static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
 				Index scanrelid, List *values_lists);
 static CteScan *make_ctescan(List *qptlist, List *qpqual,
@@ -133,12 +133,13 @@ static MergeJoin *make_mergejoin(List *tlist,
 			   List *joinclauses, List *otherclauses,
 			   List *mergeclauses,
 			   Oid *mergefamilies,
+			   Oid *mergecollations,
 			   int *mergestrategies,
 			   bool *mergenullsfirst,
 			   Plan *lefttree, Plan *righttree,
 			   JoinType jointype);
 static Sort *make_sort(PlannerInfo *root, Plan *lefttree, int numCols,
-		  AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst,
+		  AttrNumber *sortColIdx, Oid *sortOperators, Oid *collations, bool *nullsFirst,
 		  double limit_tuples);
 static Plan *prepare_sort_from_pathkeys(PlannerInfo *root,
 										Plan *lefttree, List *pathkeys,
@@ -146,6 +147,7 @@ static Plan *prepare_sort_from_pathkeys(PlannerInfo *root,
 										int *p_numsortkeys,
 										AttrNumber **p_sortColIdx,
 										Oid **p_sortOperators,
+										Oid **p_collations,
 										bool **p_nullsFirst);
 static Material *make_material(Plan *lefttree);
 
@@ -671,6 +673,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path)
 									  &node->numCols,
 									  &node->sortColIdx,
 									  &node->sortOperators,
+									  &node->collations,
 									  &node->nullsFirst);
 
 	/*
@@ -685,6 +688,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path)
 		int			numsortkeys;
 		AttrNumber *sortColIdx;
 		Oid		   *sortOperators;
+		Oid		   *collations;
 		bool	   *nullsFirst;
 
 		/* Build the child plan */
@@ -696,6 +700,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path)
 											 &numsortkeys,
 											 &sortColIdx,
 											 &sortOperators,
+											 &collations,
 											 &nullsFirst);
 
 		/*
@@ -710,13 +715,15 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path)
 			elog(ERROR, "MergeAppend child's targetlist doesn't match MergeAppend");
 		Assert(memcmp(sortOperators, node->sortOperators,
 					  numsortkeys * sizeof(Oid)) == 0);
+		Assert(memcmp(collations, node->collations,
+					  numsortkeys * sizeof(Oid)) == 0);
 		Assert(memcmp(nullsFirst, node->nullsFirst,
 					  numsortkeys * sizeof(bool)) == 0);
 
 		/* Now, insert a Sort node if subplan isn't sufficiently ordered */
 		if (!pathkeys_contained_in(pathkeys, subpath->pathkeys))
 			subplan = (Plan *) make_sort(root, subplan, numsortkeys,
-										 sortColIdx, sortOperators, nullsFirst,
+										 sortColIdx, sortOperators, collations, nullsFirst,
 										 best_path->limit_tuples);
 
 		subplans = lappend(subplans, subplan);
@@ -1569,7 +1576,8 @@ create_functionscan_plan(PlannerInfo *root, Path *best_path,
 								  rte->funcexpr,
 								  rte->eref->colnames,
 								  rte->funccoltypes,
-								  rte->funccoltypmods);
+								  rte->funccoltypmods,
+								  rte->funccolcollations);
 
 	copy_path_costsize(&scan_plan->scan.plan, best_path);
 
@@ -1847,6 +1855,7 @@ create_mergejoin_plan(PlannerInfo *root,
 	List	   *innerpathkeys;
 	int			nClauses;
 	Oid		   *mergefamilies;
+	Oid		   *mergecollations;
 	int		   *mergestrategies;
 	bool	   *mergenullsfirst;
 	MergeJoin  *join_plan;
@@ -1946,6 +1955,7 @@ create_mergejoin_plan(PlannerInfo *root,
 	nClauses = list_length(mergeclauses);
 	Assert(nClauses == list_length(best_path->path_mergeclauses));
 	mergefamilies = (Oid *) palloc(nClauses * sizeof(Oid));
+	mergecollations = (Oid *) palloc(nClauses * sizeof(Oid));
 	mergestrategies = (int *) palloc(nClauses * sizeof(int));
 	mergenullsfirst = (bool *) palloc(nClauses * sizeof(bool));
 
@@ -2074,12 +2084,14 @@ create_mergejoin_plan(PlannerInfo *root,
 
 		/* pathkeys should match each other too (more debugging) */
 		if (opathkey->pk_opfamily != ipathkey->pk_opfamily ||
+			opathkey->pk_collation != ipathkey->pk_collation ||
 			opathkey->pk_strategy != ipathkey->pk_strategy ||
 			opathkey->pk_nulls_first != ipathkey->pk_nulls_first)
 			elog(ERROR, "left and right pathkeys do not match in mergejoin");
 
 		/* OK, save info for executor */
 		mergefamilies[i] = opathkey->pk_opfamily;
+		mergecollations[i] = opathkey->pk_collation;
 		mergestrategies[i] = opathkey->pk_strategy;
 		mergenullsfirst[i] = opathkey->pk_nulls_first;
 		i++;
@@ -2099,6 +2111,7 @@ create_mergejoin_plan(PlannerInfo *root,
 							   otherclauses,
 							   mergeclauses,
 							   mergefamilies,
+							   mergecollations,
 							   mergestrategies,
 							   mergenullsfirst,
 							   outer_plan,
@@ -2528,6 +2541,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index)
 				/* Found a match */
 				result = makeVar(index->rel->relid, pos + 1,
 								 exprType(lfirst(indexpr_item)), -1,
+								 exprCollation(lfirst(indexpr_item)),
 								 0);
 				return (Node *) result;
 			}
@@ -2881,7 +2895,8 @@ make_functionscan(List *qptlist,
 				  Node *funcexpr,
 				  List *funccolnames,
 				  List *funccoltypes,
-				  List *funccoltypmods)
+				  List *funccoltypmods,
+				  List *funccolcollations)
 {
 	FunctionScan *node = makeNode(FunctionScan);
 	Plan	   *plan = &node->scan.plan;
@@ -2896,6 +2911,7 @@ make_functionscan(List *qptlist,
 	node->funccolnames = funccolnames;
 	node->funccoltypes = funccoltypes;
 	node->funccoltypmods = funccoltypmods;
+	node->funccolcollations = funccolcollations;
 
 	return node;
 }
@@ -3181,6 +3197,7 @@ make_mergejoin(List *tlist,
 			   List *otherclauses,
 			   List *mergeclauses,
 			   Oid *mergefamilies,
+			   Oid *mergecollations,
 			   int *mergestrategies,
 			   bool *mergenullsfirst,
 			   Plan *lefttree,
@@ -3197,6 +3214,7 @@ make_mergejoin(List *tlist,
 	plan->righttree = righttree;
 	node->mergeclauses = mergeclauses;
 	node->mergeFamilies = mergefamilies;
+	node->mergeCollations = mergecollations;
 	node->mergeStrategies = mergestrategies;
 	node->mergeNullsFirst = mergenullsfirst;
 	node->join.jointype = jointype;
@@ -3214,7 +3232,7 @@ make_mergejoin(List *tlist,
  */
 static Sort *
 make_sort(PlannerInfo *root, Plan *lefttree, int numCols,
-		  AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst,
+		  AttrNumber *sortColIdx, Oid *sortOperators, Oid *collations, bool *nullsFirst,
 		  double limit_tuples)
 {
 	Sort	   *node = makeNode(Sort);
@@ -3238,6 +3256,7 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols,
 	node->numCols = numCols;
 	node->sortColIdx = sortColIdx;
 	node->sortOperators = sortOperators;
+	node->collations = collations;
 	node->nullsFirst = nullsFirst;
 
 	return node;
@@ -3253,9 +3272,9 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols,
  * max possible number of columns.	Return value is the new column count.
  */
 static int
-add_sort_column(AttrNumber colIdx, Oid sortOp, bool nulls_first,
+add_sort_column(AttrNumber colIdx, Oid sortOp, Oid coll, bool nulls_first,
 				int numCols, AttrNumber *sortColIdx,
-				Oid *sortOperators, bool *nullsFirst)
+				Oid *sortOperators, Oid *collations, bool *nullsFirst)
 {
 	int			i;
 
@@ -3271,7 +3290,8 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, bool nulls_first,
 		 * opposite nulls direction is redundant.
 		 */
 		if (sortColIdx[i] == colIdx &&
-			sortOperators[numCols] == sortOp)
+			sortOperators[numCols] == sortOp &&
+			collations[numCols] == coll)
 		{
 			/* Already sorting by this col, so extra sort key is useless */
 			return numCols;
@@ -3281,6 +3301,7 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, bool nulls_first,
 	/* Add the column */
 	sortColIdx[numCols] = colIdx;
 	sortOperators[numCols] = sortOp;
+	collations[numCols] = coll;
 	nullsFirst[numCols] = nulls_first;
 	return numCols + 1;
 }
@@ -3320,6 +3341,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 						   int *p_numsortkeys,
 						   AttrNumber **p_sortColIdx,
 						   Oid **p_sortOperators,
+						   Oid **p_collations,
 						   bool **p_nullsFirst)
 {
 	List	   *tlist = lefttree->targetlist;
@@ -3327,6 +3349,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
 	Oid		   *sortOperators;
+	Oid		   *collations;
 	bool	   *nullsFirst;
 
 	/*
@@ -3335,6 +3358,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 	numsortkeys = list_length(pathkeys);
 	sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
 	sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
+	collations = (Oid *) palloc(numsortkeys * sizeof(Oid));
 	nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
 
 	numsortkeys = 0;
@@ -3493,9 +3517,10 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 		 */
 		numsortkeys = add_sort_column(tle->resno,
 									  sortop,
+									  pathkey->pk_collation,
 									  pathkey->pk_nulls_first,
 									  numsortkeys,
-									  sortColIdx, sortOperators, nullsFirst);
+									  sortColIdx, sortOperators, collations, nullsFirst);
 	}
 
 	Assert(numsortkeys > 0);
@@ -3504,6 +3529,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 	*p_numsortkeys = numsortkeys;
 	*p_sortColIdx = sortColIdx;
 	*p_sortOperators = sortOperators;
+	*p_collations = collations;
 	*p_nullsFirst = nullsFirst;
 
 	return lefttree;
@@ -3525,6 +3551,7 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
 	Oid		   *sortOperators;
+	Oid		   *collations;
 	bool	   *nullsFirst;
 
 	/* Compute sort column info, and adjust lefttree as needed */
@@ -3533,11 +3560,12 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 										  &numsortkeys,
 										  &sortColIdx,
 										  &sortOperators,
+										  &collations,
 										  &nullsFirst);
 
 	/* Now build the Sort node */
 	return make_sort(root, lefttree, numsortkeys,
-					 sortColIdx, sortOperators, nullsFirst, limit_tuples);
+					 sortColIdx, sortOperators, collations, nullsFirst, limit_tuples);
 }
 
 /*
@@ -3555,6 +3583,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree)
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
 	Oid		   *sortOperators;
+	Oid		   *collations;
 	bool	   *nullsFirst;
 
 	/*
@@ -3563,6 +3592,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree)
 	numsortkeys = list_length(sortcls);
 	sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
 	sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
+	collations = (Oid *) palloc(numsortkeys * sizeof(Oid));
 	nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
 
 	numsortkeys = 0;
@@ -3578,15 +3608,16 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree)
 		 * redundantly.
 		 */
 		numsortkeys = add_sort_column(tle->resno, sortcl->sortop,
+									  exprCollation((Node *) tle->expr),
 									  sortcl->nulls_first,
 									  numsortkeys,
-									  sortColIdx, sortOperators, nullsFirst);
+									  sortColIdx, sortOperators, collations, nullsFirst);
 	}
 
 	Assert(numsortkeys > 0);
 
 	return make_sort(root, lefttree, numsortkeys,
-					 sortColIdx, sortOperators, nullsFirst, -1.0);
+					 sortColIdx, sortOperators, collations, nullsFirst, -1.0);
 }
 
 /*
@@ -3614,6 +3645,7 @@ make_sort_from_groupcols(PlannerInfo *root,
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
 	Oid		   *sortOperators;
+	Oid		   *collations;
 	bool	   *nullsFirst;
 
 	/*
@@ -3622,6 +3654,7 @@ make_sort_from_groupcols(PlannerInfo *root,
 	numsortkeys = list_length(groupcls);
 	sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
 	sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
+	collations = (Oid *) palloc(numsortkeys * sizeof(Oid));
 	nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
 
 	numsortkeys = 0;
@@ -3637,16 +3670,17 @@ make_sort_from_groupcols(PlannerInfo *root,
 		 * redundantly.
 		 */
 		numsortkeys = add_sort_column(tle->resno, grpcl->sortop,
+									  exprCollation((Node *) tle->expr),
 									  grpcl->nulls_first,
 									  numsortkeys,
-									  sortColIdx, sortOperators, nullsFirst);
+									  sortColIdx, sortOperators, collations, nullsFirst);
 		grpno++;
 	}
 
 	Assert(numsortkeys > 0);
 
 	return make_sort(root, lefttree, numsortkeys,
-					 sortColIdx, sortOperators, nullsFirst, -1.0);
+					 sortColIdx, sortOperators, collations, nullsFirst, -1.0);
 }
 
 static Material *