I looked into the problem reported here:
http://archives.postgresql.org/pgsql-bugs/2010-07/msg00119.php
The reason it's failing is that when parse_agg.c calls
transformSortClause() to process the ORDER BY, the latter function
fails to match the "t" in ORDER BY to the one in the function's input
argument list. And the reason it fails is that parse_func.c already
coerced the arguments to be what the function expects. So rather than
a plain Var for the varchar column "t", the argument list contains
"t::text", which isn't equal() to "t". The same type of thing would
happen in any case where implicit coercion of the arguments was needed
to produce the exact data type expected by the aggregate.
I thought of a few ways to attack this, most of which don't look very
workable:
1. Postpone coercion of the function inputs till after processing of
the ORDER BY/DISTINCT decoration. This isn't too good because then
we'll be using the "wrong" data type for deciding the semantics of
ORDER BY/DISTINCT. That could lead to bizarre behavior or even
crashes, eg if we try to use numeric sort operators on a value that
actually has been coerced to float8. We could possibly go back and
re-do the decisions about data types but it'd be a mess.
2. Split the processing of aggregates with ORDER BY/DISTINCT so that the
sorting/uniqueifying is done in a separate expression node that can work
with the "native" types of the given columns, and only after that do we
perform coercion to the aggregate function's input types. This would be
logically the cleanest thing, perhaps, but it'd represent a very major
rework of the patch, with really no hope of getting it done for 9.0.
3. Do something so that we can still match "t::text" to "t". This
seems pretty awful on first glance but it's not actually that bad,
because in the case we care about the cast will be marked as having
been applied implicitly. Basically, instead of just equal() comparisons
in findTargetlistEntrySQL99(), we'd strip off any implicit cast at the
top of either expression, and only then do equal(). Since the implicit
casts are, by definition, things the user didn't write, this would still
have the expected behavior of matching expressions that were identical
when the user wrote them.
#3 seems the sanest fix, but I wonder if anyone has an objection or
better idea.
regards, tom lane