public inbox for [email protected]help / color / mirror / Atom feed
Is this example regarding aggregates sourced by subquery correct? 5+ messages / 2 participants [nested] [flat]
* Is this example regarding aggregates sourced by subquery correct? @ 2016-05-20 20:08 David G. Johnston <[email protected]> 2016-05-20 21:07 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2016-05-20 20:08 UTC (permalink / raw) To: pgsql-docs 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is this example regarding aggregates sourced by subquery correct? 2016-05-20 20:08 Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> @ 2016-05-20 21:07 ` Tom Lane <[email protected]> 2016-05-20 22:41 ` Re: Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2016-05-20 21:07 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-docs "David G. Johnston" <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is this example regarding aggregates sourced by subquery correct? 2016-05-20 20:08 Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 2016-05-20 21:07 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> @ 2016-05-20 22:41 ` David G. Johnston <[email protected]> 2016-05-21 17:02 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2016-05-20 22:41 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs On Fri, May 20, 2016 at 5:07 PM, Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected]> 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is this example regarding aggregates sourced by subquery correct? 2016-05-20 20:08 Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 2016-05-20 21:07 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> 2016-05-20 22:41 ` Re: Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> @ 2016-05-21 17:02 ` Tom Lane <[email protected]> 2016-05-21 17:13 ` Re: Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2016-05-21 17:02 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-docs "David G. Johnston" <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is this example regarding aggregates sourced by subquery correct? 2016-05-20 20:08 Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 2016-05-20 21:07 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> 2016-05-20 22:41 ` Re: Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 2016-05-21 17:02 ` Re: Is this example regarding aggregates sourced by subquery correct? Tom Lane <[email protected]> @ 2016-05-21 17:13 ` David G. Johnston <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: David G. Johnston @ 2016-05-21 17:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs On Saturday, May 21, 2016, Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected] <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. ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-05-21 17:13 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-05-20 20:08 Is this example regarding aggregates sourced by subquery correct? David G. Johnston <[email protected]> 2016-05-20 21:07 ` Tom Lane <[email protected]> 2016-05-20 22:41 ` David G. Johnston <[email protected]> 2016-05-21 17:02 ` Tom Lane <[email protected]> 2016-05-21 17:13 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox