Обсуждение: Is this example regarding aggregates sourced by subquery correct?
"""
...This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. For example:
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.
"""
This seems incorrect - I was expecting something like:
SELECT xmlagg((SELECT x FROM test ORDER BY y DESC))
The example seems expressly permitted by the standard and other database systems.
I'll believe that said ordering in the example is not guaranteed but that isn't what it says.
David J.
"David G. Johnston" <david.g.johnston@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". regards, tom lane
"David G. Johnston" <david.g.johnston@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.
"David G. Johnston" <david.g.johnston@gmail.com> 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.
regards, tom lane
On Saturday, May 21, 2016, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> 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.