On Fri, May 20, 2016 at 5:07 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johns...@gmail.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.
