Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1b3t7K-0007EV-GD for pgsql-docs@arkaria.postgresql.org; Fri, 20 May 2016 22:42:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1b3t7K-00065v-0t for pgsql-docs@arkaria.postgresql.org; Fri, 20 May 2016 22:42:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1b3t6x-0005ha-Rt for pgsql-docs@postgresql.org; Fri, 20 May 2016 22:41:39 +0000 Received: from mail-oi0-x233.google.com ([2607:f8b0:4003:c06::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1b3t6t-0002mY-AE for pgsql-docs@postgresql.org; Fri, 20 May 2016 22:41:38 +0000 Received: by mail-oi0-x233.google.com with SMTP id b65so56753665oia.1 for ; Fri, 20 May 2016 15:41:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=8ZSV5LlgpZqvCtnTrOM+7Lcl2+VmucyNyJZV+R0vS4A=; b=tcFbpFuMTVqZ4oL18Po2GsZiI/Eb0iDHhVl4NK+NV0hdbt5/T2EOuQodxXIEBOB8X3 d1uUKwfrj4sxlBejC3FSrjJEy1QtYfiSh+7KLlAc8+hc+aLPYkzioNELg7xgj1ZYqnC1 vUjGAmxFFLjACkakxNLVi+krA4yLFkaqYG7Bck42QOVebWc/qT/yMG36t5zdnR0Gc3/0 IZW3KNMjs48D8k/CubsjozMrfH3736IaCGsp1MPCKkgbTUG6tnB9Rd+WWm+UVe6c/ns6 Ayj2BdBqQjXaL+xyfBzZ4qkdbhRA6Xr8mPxjV4QPIdGWKvmD4o13e2SuywrKwWK2BEkP qT9A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=8ZSV5LlgpZqvCtnTrOM+7Lcl2+VmucyNyJZV+R0vS4A=; b=b+YwLifYNJTM4h20UXm9+C75vwjGJBPTHL7GiQVoekMWGl5wT5Y8HU7NpBTreu2YDS SJqOphLv2im6equxy0QPwsr/tRY++IAC9TP1Qhl+jUuKluRFjyo6oFXm9k+BcUvxQMT2 mqmX8fPsA7qw6spgW5Je4wAUgw6B8TAdcnqUxMi6/iKEPYeIHA/sNcZleUzQgA2X/wOG fNw4HprpaCs3pvgw/K+EJ1i/995Zd75W6CXyV+uMvOG29WjFQP3A2XYL7Ai5kGrRcWi5 QrH0Jrk35dk94kIov/uDeV4/ALlHJe+mW3vRu2HbXwjynfFlVwk8SSl8PrffOGkwlpL1 87uA== X-Gm-Message-State: AOPr4FW7KyIbsOv5pCxIjJ6H0iKueIta0T0v0PDXdqTJl0Nmyc/6BcOegqaLpdn8/sruYBQtNAfDs2bhdZV0dQ== MIME-Version: 1.0 X-Received: by 10.202.96.68 with SMTP id u65mr3135928oib.83.1463784093405; Fri, 20 May 2016 15:41:33 -0700 (PDT) Received: by 10.157.37.93 with HTTP; Fri, 20 May 2016 15:41:33 -0700 (PDT) In-Reply-To: <55062.1463778448@sss.pgh.pa.us> References: <55062.1463778448@sss.pgh.pa.us> Date: Fri, 20 May 2016 18:41:33 -0400 Message-ID: Subject: Re: Is this example regarding aggregates sourced by subquery correct? From: "David G. Johnston" To: Tom Lane Cc: "pgsql-docs@postgresql.org" Content-Type: multipart/alternative; boundary=001a113d5d26ea5b6f05334dca6b X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --001a113d5d26ea5b6f05334dca6b Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Fri, May 20, 2016 at 5:07 PM, Tom Lane wrote: > "David G. Johnston" 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 databas= e > > systems. > > The example is illegal in SQL:2003 and before; they did not allow ORDER B= Y > in a 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 shoul= d > 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". > =E2=80=8BI guess that is the only portion that would make sense to be illeg= al... 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 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. --001a113d5d26ea5b6f05334dca6b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Fri, Ma= y 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/doc= s/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 portabl= e to
> other database systems.
> """

> The example seems expressly permitted by the s= tandard and other database
> systems.

The example is illegal in SQL:2003 and before; they did not allow OR= DER BY
in a <query expression> until SQL:2008.=C2=A0 Even in newer spec vers= ions, it's
considered an optional feature (cf F850-F855).=C2=A0 Given that, I would be=
pretty leery of claims that it's supported in all other DBMSes.=C2=A0 W= e should
perhaps back off the wording to something like "is not allowed in olde= r
versions of the SQL standard, and may not be portable to other database
systems".

=E2=80=8BI guess that is t= he 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 expre= ssion> construct as a whole.

Based upon what you= 9;ve said I would soften it a bit.=C2=A0 Given my own experience I'd pr= obably point out what is now obvious to me - that the allowance of the ORDE= R BY clause is implementation specific.=C2=A0 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 h= azard."

David J.

--001a113d5d26ea5b6f05334dca6b--