On Sun, Mar 18, 2018 at 02:28:26PM -0700, David G. Johnston wrote:
> As a first step we could do something like:
>
> basic_select_statement 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 ) [, ...] ]
>
>
> 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 } ] [, ...] ]
> [ 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.
>
>
> 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.
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 protected]> 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.
