Is this example regarding aggregates sourced by subquery correct?
http://www.postgresql.org/docs/9.5/static/functions-aggregate.html
"""
...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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
On Fri, May 20, 2016 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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 portableto
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
On Saturday, May 21, 2016, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.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 toBeware 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.