public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Euler Taveira <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
Date: Fri, 16 Mar 2018 01:17:00 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHE3wgiaV1YyQ6-v12R4bzmQoUc_5E3hBupKxHTzLXg2TOsh-g@mail.gmail.com>
References: <[email protected]>
	<CAHE3wgiaV1YyQ6-v12R4bzmQoUc_5E3hBupKxHTzLXg2TOsh-g@mail.gmail.com>

Euler Taveira <[email protected]> writes:
> 2018-03-15 7:18 GMT-03:00 PG Doc comments form <[email protected]>:
>> The SYNOPSIS section of the "SELECT" SQL command contains the line
>> [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
>> (with a boldface "select"), but it is not clear what is meant by that
>> "select".

> It is a bug in the synopsis. UNION et al cannot contain some elements
> (such as ORDER BY) that is allowed for a sub-select. The attached
> patch replace "select" with the correct element ("select_statement").

Well, "select_statement" isn't defined in the synopsis either.
We do define it implicitly in the UNION/INTERSECT/EXCEPT subsections,
but is that enough?

I think the parenthetical remark in the "UNION Clause" section is a bit
shaky too:

    (ORDER BY and LIMIT can be attached to a subexpression if it is
    enclosed in parentheses. Without parentheses, these clauses will be
    taken to apply to the result of the UNION, not to its right-hand input
    expression.)

This would seem to imply that

SELECT * FROM foo ORDER BY x UNION SELECT * FROM bar

is legal and means the same as

(SELECT * FROM foo UNION SELECT * FROM bar) ORDER BY x

which is wrong.

Also, whether or not that wording is right, it's not duplicated in the
INTERSECT or EXCEPT headings, where logically it ought to appear too.
We could duplicate it there maybe, but I'm starting to feel like this is
just doubling down on a bad documentation design.

The long and short of it is that UNION et al were wedged into the initial
synopsis in a way that's at best misleading and at worst a lie.  But
I'm not sure how to make that better without also making it a lot more
confusing.  A pedantically correct syntax synopsis would look something
like

[ WITH [ RECURSIVE ] with_query [, ...] ]
select_stmt [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_stmt ...]

where select_stmt is

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

and that's still not right because ORDER BY et al can't be attached to a
select_stmt that's the argument of a set operation, so really we'd need
a couple of levels of nonterminals before we get down to the basic
"SELECT expression FROM ..." part.  Nor has the use of parentheses been
mentioned yet.

If you look at either the SQL standard's syntax diagrams or our actual
bison grammar, they're both unreasonably complicated.  Novices would
not thank us for reproducing that in full detail in the basic SELECT
syntax summary, and I'm not sure experts would either.

So, surely there's room for improvement here, but I'm not certain what
it'd look like.  Maybe we should split out the discussion of set-operation
syntax altogether?  How exactly?

			regards, tom lane

view thread (8+ 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], [email protected], [email protected]
  Subject: Re: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
  In-Reply-To: <[email protected]>

* 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