findTargetlistEntrySQL92() and COLLATE clause

[Amit Langote]

Hi,

I couldn't find old discussions or source code comments about this, but
has someone encountered the following error and wondered whether it's
working that way for a reason?

select a::text, b from foo order by 1, 2 collate "C";
ERROR:  collations are not supported by type integer
LINE 1: select a::text, b from foo order by 1, 2 collate "C";
                                                 ^
I expected this to resolve the output column number (2) to actual column
(b) and apply COLLATE clause on top of it.  Attached patch makes it so by
teaching findTargetlistEntrySQL92() to recognize such ORDER BY items and
handle them likewise.  With the patch:

select a::text, b from foo order by 1, 2 collate "C";
 a  │    b
────┼──────────
 ab │ ab wins
 ab │ ab1 wins
 ab │ ab2 wins
(3 rows)

select a::text, b from foo order by 1 collate "C", 2;
 a  │    b
────┼──────────
 ab │ ab1 wins
 ab │ ab2 wins
 ab │ ab wins
(3 rows)

select a::text, b from foo order by 3 collate "C", 2;
ERROR:  ORDER BY position 3 is not in select list
LINE 1: select a::text, b from foo order by 3 collate "C", 2;

Am I missing something?

Thanks,
Amit

diff --git a/src/backend/parser/parse_clause.c 
b/src/backend/parser/parse_clause.c
index d6b93f55df..0f25159231 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -2005,6 +2005,29 @@ findTargetlistEntrySQL92(ParseState *pstate, Node *node, 
List **tlist,
                                                ParseExprKindName(exprKind), 
target_pos),
                                 parser_errposition(pstate, location)));
        }
+       /*
+        * If we've got a COLLATE clause covering up an integer referencing a
+        * targetlist expression, fix it up by replacing the integer with the
+        * referenced targetlist expression and let findTargetlistEntrySQL99
+        * finish it up by performing the remaining processing.
+        */
+       if (IsA(node, CollateClause) &&
+               IsA(((CollateClause *) node)->arg, A_Const))
+       {
+               CollateClause *collclause = (CollateClause *) node;
+               TargetEntry       *tle;
+
+               /* Recursively get the targetlist entry referenced by the 
integer. */
+               tle = findTargetlistEntrySQL92(pstate, collclause->arg, tlist,
+                                                                          
exprKind);
+
+               /*
+                * Found one; replace the integer by the targetlist expression.
+                * findTargetlistEntrySQL99 will finish up the rest.
+                */
+               Assert(tle != NULL);
+               collclause->arg = tle->expr;
+       }
 
        /*
         * Otherwise, we have an expression, so process it per SQL99 rules.