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

On Fri, May 20, 2016 at 5:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > http://www.postgresql.org/docs/9.5/static/functions-aggregate.html
> > """
> > SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
> > But this syntax is not allowed in the SQL standard, and is not portable
> to
> > other database systems.
> > """
>
> > The example seems expressly permitted by the standard and other database
> > systems.
>
> The example is illegal in SQL:2003 and before; they did not allow ORDER BY
> in a <query expression> until SQL:2008. Even in newer spec versions, it's
> considered an optional feature (cf F850-F855). Given that, I would be
> pretty leery of claims that it's supported in all other DBMSes. We should
> perhaps back off the wording to something like "is not allowed in older
> versions of the SQL standard, and may not be portable to other database
> systems".
>

​I guess that is the only portion that would make sense to be illegal...

I won't claim to know the behavior other databases with respect to the
allow-ability of an ORDER BY clause - I was looking at the <query
expression> construct as a whole.

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."

David J.