Re: Aggregate ORDER BY patch

>>>>> "Hitoshi" == Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:

>> What case exactly would you consider an error? When an order by
>> expression references a lower (more deeply nested) query level
>> than any of the actual arguments?

Hitoshi> It's only that I felt not intuitive. To me, arguments are
Hitoshi> regarded as aggregate's "member" while ORDER BY clause
Hitoshi> expressions didn't hit me. If it's only me, no
Hitoshi> problem. Maybe additional document in #syntax-aggregates
Hitoshi> 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 it
Hitoshi> enough to let "order by x" omitted? "select count(distinct x
Hitoshi> order by x) from table" means the same as "select
Hitoshi> count(distinct x) from table" currently. ruleutils can
Hitoshi> handle it if distinct expressions are equal to order by
Hitoshi> 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.