Re: Is this example regarding aggregates sourced by subquery correct?

On Saturday, May 21, 2016, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com <javascript:;>> writes:
> > Based upon what you've said I would soften it a bit. Given my own
> > experience I'd probably point out what is now obvious to me - that the
> > allowance of the ORDER BY clause is implementation specific. But I'd be
> > fine chalking that up to an anomalous reading.
>
> > Something like:
>
> > "But permitting the sub-query's ORDER BY was only upgraded to optional in
> > SQL:2008 and thus this syntax poses a portability hazard."
>
> After further thought I realized that this gripe applies just as much
> to the alternative we're comparing this to, ie, putting ORDER BY into
> the aggregate call. (I've not looked up whether the two features were
> introduced in exactly the same SQL version, but I am pretty sure they
> are both post-SQL99.) So we might as well just take it out. What we
> could usefully do instead is explain exactly what's dangerous about
> using a subquery ORDER BY in this way. So I changed it to
>
> Beware that this approach can fail if the outer query level contains
> additional processing, such as a join, because that might cause the
> subquery's output to be reordered before the aggregate is computed.
>
>
That works. There's only so much portability warning that is useful and we
should focus on better informing how postgreSQL functions.

Thanks.

David J.