MIME-Version: 1.0
In-Reply-To: <55820.1463850163@sss.pgh.pa.us>
References: 
 <CAKFQuwbAX=iO9QbpN7_jr+BnUWm9FYX8WbEPUvG0p+nZhp6TZg@mail.gmail.com>
	<55062.1463778448@sss.pgh.pa.us>
	<CAKFQuwbsUgMVnWXObyjvZsY46pkzVF3Wm2Z7j678eA8vNJHbaQ@mail.gmail.com>
	<55820.1463850163@sss.pgh.pa.us>
Date: Sat, 21 May 2016 13:13:38 -0400
Message-ID: 
 <CAKFQuwYD49-=7XL=i-F7VMP3o5Kjwo2a9v6LQVn_5RV4DDzN-A@mail.gmail.com>
Subject: Re: Is this example regarding aggregates sourced by subquery correct?
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>
Content-Type: multipart/alternative; boundary=94eb2c114d1e0afbc905335d5481
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

--94eb2c114d1e0afbc905335d5481
Content-Type: text/plain; charset=UTF-8

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 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.

--94eb2c114d1e0afbc905335d5481
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<br><br>On Saturday, May 21, 2016, Tom Lane &lt;<a href=3D"mailto:tgl@sss.p=
gh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">&quot;David G. Johnston&quot; &lt;<a href=3D"javascript:;" onclick=3D"_e(=
event, &#39;cvml&#39;, &#39;david.g.johnston@gmail.com&#39;)">david.g.johns=
ton@gmail.com</a>&gt; writes:<br>
&gt; Based upon what you&#39;ve said I would soften it a bit.=C2=A0 Given m=
y own<br>
&gt; experience I&#39;d probably point out what is now obvious to me - that=
 the<br>
&gt; allowance of the ORDER BY clause is implementation specific.=C2=A0 But=
 I&#39;d be<br>
&gt; fine chalking that up to an anomalous reading.<br>
<br>
&gt; Something like:<br>
<br>
&gt; &quot;But permitting the sub-query&#39;s ORDER BY was only upgraded to=
 optional in<br>
&gt; SQL:2008 and thus this syntax poses a portability hazard.&quot;<br>
<br>
After further thought I realized that this gripe applies just as much<br>
to the alternative we&#39;re comparing this to, ie, putting ORDER BY into<b=
r>
the aggregate call.=C2=A0 (I&#39;ve not looked up whether the two features =
were<br>
introduced in exactly the same SQL version, but I am pretty sure they<br>
are both post-SQL99.)=C2=A0 So we might as well just take it out.=C2=A0 Wha=
t we<br>
could usefully do instead is explain exactly what&#39;s dangerous about<br>
using a subquery ORDER BY in this way.=C2=A0 So I changed it to<br>
<br>
=C2=A0 =C2=A0Beware that this approach can fail if the outer query level co=
ntains<br>
=C2=A0 =C2=A0additional processing, such as a join, because that might caus=
e the<br>
=C2=A0 =C2=A0subquery&#39;s output to be reordered before the aggregate is =
computed.<br><br>
</blockquote><div><br></div><div>That works.=C2=A0 There&#39;s only so much=
 portability warning that is useful and we should focus on better informing=
 how postgreSQL functions.</div><br><div>Thanks.</div><div><br></div>David =
J.

--94eb2c114d1e0afbc905335d5481--