public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Is this example regarding aggregates sourced by subquery correct?
Date: Fri, 20 May 2016 18:41:33 -0400
Message-ID: <CAKFQuwbsUgMVnWXObyjvZsY46pkzVF3Wm2Z7j678eA8vNJHbaQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKFQuwbAX=iO9QbpN7_jr+BnUWm9FYX8WbEPUvG0p+nZhp6TZg@mail.gmail.com>
	<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-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.

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Is this example regarding aggregates sourced by subquery correct?
  In-Reply-To: <CAKFQuwbsUgMVnWXObyjvZsY46pkzVF3Wm2Z7j678eA8vNJHbaQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox