Re: Aggregate ORDER BY patch - Mailing list pgsql-hackers

>>>>> "Hitoshi" == Hitoshi Harada <umi.tanuki@gmail.com> writes:
>> What case exactly would you consider an error? When an order by>> expression references a lower (more deeply nested)
querylevel>> than any of the actual arguments?
 
Hitoshi> It's only that I felt not intuitive. To me, arguments areHitoshi> regarded as aggregate's "member" while ORDER
BYclauseHitoshi> expressions didn't hit me.  If it's only me, noHitoshi> problem. Maybe additional document in
#syntax-aggregatesHitoshi>will do.
 

How about:
   ... But an exception occurs if the aggregate's arguments   (including any <literal>ORDER BY</> clause) contain only
outer-level variables: the aggregate then belongs to the nearest   such outer level, ...
 
>> Without that flag or something like it, when you do>> >> create view foo as select count(distinct x) from table;>>
>>and then display the view definition, you would get back the query as>> "select count(distinct x order by x) from
table"which would be>> confusing and unnecessarily backwards- and forwards-incompatible.>> >> So the code sets
"implicit"for any SortGroupClause that is added for>> some internal reason rather than being present in the original
query,>>and the deparse code in ruleutils skips such clauses.
 
Hitoshi> I don't have much experiences in VIEW systems, but isn't itHitoshi> enough to let "order by x" omitted?
"selectcount(distinct xHitoshi> order by x) from table" means the same as "selectHitoshi> count(distinct x) from table"
currently.ruleutils canHitoshi> handle it if distinct expressions are equal to order byHitoshi> expressions.
 

That doesn't work in more complex cases. For example, the user might
specify aggfunc(distinct x,y order by x) (not caring about the relative
order of y) but the code will still turn that internally into
aggfunc(distinct x,y order by x,y). It's necessary to be able to recover
what the user originally entered, which means needing to be able to
distinguish both of those cases from aggfunc(distinct x,y).

Other ways this could have been done (but which I rejected) were:
 1) separate lists of SortGroupClauses for "orderings the user    explicitly required" and "ordering we're going to
use"
 2) single list of SortGroupClauses, but a count of how many of the    entries are original, rather than added
 3) deferring the addition of extra ordering elements required for    distinctness until planning time

I didn't consider (3) because I wasn't really touching the planner
for this patch, and the information needed was already available in
parse analysis as part of the error checking.

-- 
Andrew.