From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>
Subject: Re: Is this example regarding aggregates sourced by subquery correct?
In-reply-to: 
 <CAKFQuwbsUgMVnWXObyjvZsY46pkzVF3Wm2Z7j678eA8vNJHbaQ@mail.gmail.com>
References: 
 <CAKFQuwbAX=iO9QbpN7_jr+BnUWm9FYX8WbEPUvG0p+nZhp6TZg@mail.gmail.com>
 <55062.1463778448@sss.pgh.pa.us>
 <CAKFQuwbsUgMVnWXObyjvZsY46pkzVF3Wm2Z7j678eA8vNJHbaQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 20 May 2016 18:41:33 -0400"
Date: Sat, 21 May 2016 13:02:43 -0400
Message-ID: <55820.1463850163@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

"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