Subject: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT

From: PG Doc comments form <[email-masked]>
Date: Thu, 15 Mar 2018 10:18:54 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/sql-select.html
Description:

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". Further down the page, in the "UNION clause" section (and also
INTERSECTION or EXCEPT), it is written:

select_statement UNION [ ALL | DISTINCT ] select_statement

which uses boldface "select_statement" instead of boldface "select" as in
the synopsis. This is confusing.

It would be great if the boldface "select" in the synopsis could be better
defined.

---

From: Euler Taveira <[email-masked]>
Date: Fri, 16 Mar 2018 01:02:13 -0300

> INTERSECTION or EXCEPT), it is written:
"select" is defined as a sub-select that can appear in the FROM clause
(see the From Clause section).

> the synopsis. This is confusing.
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").

-- 
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index b5d3d3a..5acd749 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -40,7 +40,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
     [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
     [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
-    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
+    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable> ]
     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
     [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]

---

From: Tom Lane <[email-masked]>
Date: Fri, 16 Mar 2018 01:17:00 -0400

Euler Taveira <[email-masked]> writes:
>> "select".

> 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

---

From: Euler Taveira <[email-masked]>
Date: Fri, 16 Mar 2018 15:57:09 -0300

> We do define it implicitly in the UNION/INTERSECT/EXCEPT subsections,
Is it worth inflate the synopsis with each element that appears in it?
"join_type" and "window_definition" are not defined in the synopsis
either.

>     taken to apply to the result of the UNION, not to its right-hand input
Yup. It does not inform that the ORDER BY should be at the end of the UNION.

> just doubling down on a bad documentation design.
I don't see it as a problem since that part will not (never | rarely) change.

> confusing.  A pedantically correct syntax synopsis would look something
The problem with adding "select_statement" to the synopsis is that it
is not self-explainable...

> "SELECT expression FROM ..." part.  Nor has the use of parentheses been
... I mean it is really difficult to present this part in a synopsis.
I tend to agree that avoid defining some elements was a clever
decision (because we have the opportunity to explain it in detail a
few paragraphs below). Although, I don't see the "window_definition"
in the synopsis I can explore its syntax a few lines above. IMO if
users have a hard time finding the "select_statement" element, maybe
we can add a link to each of those elements that have additional
syntax and does not appear in the synopsis.

> syntax altogether?  How exactly?
It seems a radical change. I wouldn't certainly suggest unless chapter
and/or section is a complete mess. I don't think it is the case. If a
few more users also complain about the set-operators confusion, we
should improve tutorial and add a few use-cases for it.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 18 Mar 2018 14:28:26 -0700

As a first step we could do something like:

​basic_select_statement is:
​

>     [ WINDOW window_name AS ( window_definition ) [, ...] ]

​full_select_statement is basic_select_statement with the following
possible additional clauses tacked onto the end:

    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST |
> LAST } ] [, ...] ]
>     [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name
> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
>

​Then we can define the set clauses in terms of basic_select_stmt and
parentheses-surrounded full_select_stmt. The result of the set clause is
itself a type of basic_select_statement which can be made full by adding
one or more of the additional clauses, including ORDER BY.

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 2 Apr 2018 20:05:21 -0400

On Sun, Mar 18, 2018 at 02:28:26PM -0700, David G. Johnston wrote:
> ​full_select_statement is basic_select_statement with the following possible
> additional clauses tacked onto the end:
> 
> 
> parentheses-surrounded full_select_stmt. The result of the set clause is itself
> a type of basic_select_statement which can be made full by adding one or more
> of the additional clauses, including ORDER BY.

Based on this discussion, I have developed the attached patch which
tries to clarify the behavior without adding complexity.

If this is applied, should it be backpatched as a fix?

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
new file mode 100644
index b5d3d3a..2c6c49c
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** SELECT [ ALL | DISTINCT [ ON ( <replacea
*** 40,46 ****
      [ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
      [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
      [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
      [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
      [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
--- 40,46 ----
      [ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
      [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
      [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">limited_select</replaceable> ]
      [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
      [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
*************** TABLE [ ONLY ] <replaceable class="param
*** 152,157 ****
--- 152,166 ----
  
      <listitem>
       <para>
+       <replaceable class="parameter">limited_select</replaceable> is one or
+       more additional <command>SELECT</command> queries using only clauses
+       listed above this item.  Clauses after this item can only be appended
+       to the last <command>SELECT</command>, unless parentheses are used.
+      </para>
+     </listitem>
+ 
+     <listitem>
+      <para>
        Using the operators <literal>UNION</literal>,
        <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
        output of more than one <command>SELECT</command> statement can
*************** TABLE [ ONLY ] <replaceable class="param
*** 251,260 ****
      <command>SELECT</command> subquery to reference itself by name.  Such a
      subquery must have the form
  <synopsis>
! <replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
  </synopsis>
      where the recursive self-reference must appear on the right-hand
!     side of the <literal>UNION</literal>.  Only one recursive self-reference
      is permitted per query.  Recursive data-modifying statements are not
      supported, but you can use the results of a recursive
      <command>SELECT</command> query in
--- 260,271 ----
      <command>SELECT</command> subquery to reference itself by name.  Such a
      subquery must have the form
  <synopsis>
! <replaceable class="parameter">non_recursive_term</replaceable> { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
  </synopsis>
      where the recursive self-reference must appear on the right-hand
!     side of
!     <literal>UNION</literal>/<literal>INTERSECT</literal>/<literal>EXCEPT</literal>.
!     Only one recursive self-reference
      is permitted per query.  Recursive data-modifying statements are not
      supported, but you can use the results of a recursive
      <command>SELECT</command> query in
*************** SELECT DISTINCT ON (location) location,
*** 1129,1144 ****
     <para>
      The <literal>UNION</literal> clause has this general form:
  <synopsis>
! <replaceable class="parameter">select_statement</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
! </synopsis><replaceable class="parameter">select_statement</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
!     (<literal>ORDER BY</literal> and <literal>LIMIT</literal> 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 <literal>UNION</literal>, not to its right-hand input
!     expression.)
     </para>
  
     <para>
--- 1140,1154 ----
     <para>
      The <literal>UNION</literal> clause has this general form:
  <synopsis>
! [ <replaceable class="parameter">limited_select</replaceable> UNION [ ALL | DISTINCT ] [ ... ] <replaceable class="parameter">select</replaceable>
! </synopsis><replaceable class="parameter">limited_select</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
!     (<literal>ORDER BY</literal> and <literal>LIMIT</literal> can be
!     attached if it is enclosed in parentheses.)  The final <replaceable
!     class="parameter">select</replaceable> can contain the clauses listed
!     above and those are applied to the result of the <literal>UNION</literal>.
     </para>
  
     <para>
*************** SELECT DISTINCT ON (location) location,
*** 1182,1189 ****
     <para>
      The <literal>INTERSECT</literal> clause has this general form:
  <synopsis>
! <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
! </synopsis><replaceable class="parameter">select_statement</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
--- 1192,1199 ----
     <para>
      The <literal>INTERSECT</literal> clause has this general form:
  <synopsis>
! <replaceable class="parameter">limited_select</replaceable> INTERSECT [ ALL | DISTINCT ] [ ... ] <replaceable class="parameter">select_statement</replaceable>
! </synopsis><replaceable class="parameter">limited_select</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
*************** SELECT DISTINCT ON (location) location,
*** 1230,1237 ****
     <para>
      The <literal>EXCEPT</literal> clause has this general form:
  <synopsis>
! <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
! </synopsis><replaceable class="parameter">select_statement</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
--- 1240,1247 ----
     <para>
      The <literal>EXCEPT</literal> clause has this general form:
  <synopsis>
! <replaceable class="parameter">limited_select</replaceable> EXCEPT [ ALL | DISTINCT ] [ ... ] <replaceable class="parameter">select_statement</replaceable>
! </synopsis><replaceable class="parameter">limited_select</replaceable> is
      any <command>SELECT</command> statement without an <literal>ORDER
      BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
      <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.

---

From: Tom Lane <[email-masked]>
Date: Mon, 02 Apr 2018 20:21:45 -0400

> tries to clarify the behavior without adding complexity.

I don't think this is an improvement, really ... in particular, it
makes the <synopsis> not self-contained, which is pretty horrible
for psql's help.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 3 Apr 2018 09:24:39 -0400

> makes the <synopsis> not self-contained, which is pretty horrible

Well, we know we are limited in how much precision we can add without
losing clarity.  I don't have any new ideas so we will just have to keep
what we have, though I do think mine was an improvement in enough areas
to warrant it.