Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f39UL-0004y9-9O for pgsql-docs@arkaria.postgresql.org; Tue, 03 Apr 2018 00:07:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f39UK-0007WW-2a for pgsql-docs@arkaria.postgresql.org; Tue, 03 Apr 2018 00:07:48 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f39S4-0004id-Bl for pgsql-docs@lists.postgresql.org; Tue, 03 Apr 2018 00:05:28 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1f39S0-0006se-S5 for pgsql-docs@lists.postgresql.org; Tue, 03 Apr 2018 00:05:26 +0000 Received: from bruce by momjian.us with local (Exim 4.84_2) (envelope-from ) id 1f39Rx-0007h1-J6; Mon, 02 Apr 2018 20:05:21 -0400 Date: Mon, 2 Apr 2018 20:05:21 -0400 From: Bruce Momjian To: "David G. Johnston" Cc: Tom Lane , Euler Taveira , dirk.lattermann@leanix.net, pgsql-docs@lists.postgresql.org Subject: Re: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT Message-ID: <20180403000521.GA12774@momjian.us> References: <152110913499.1223.7026776990975251345@wrigleys.postgresql.org> <28115.1521177420@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="DocE+STaALJfprDB" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --DocE+STaALJfprDB Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit 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 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 + --DocE+STaALJfprDB Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="select.diff" 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 ( grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] ! [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] --- 40,46 ---- [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] ! [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] limited_select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] *************** TABLE [ ONLY ] limited_select is one or + more additional SELECT queries using only clauses + listed above this item. Clauses after this item can only be appended + to the last SELECT, unless parentheses are used. + + + + + Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT statement can *************** TABLE [ ONLY ] non_recursive_term UNION [ ALL | DISTINCT ] recursive_term where the recursive self-reference must appear on the right-hand ! side of the UNION. 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 SELECT query in --- 260,271 ---- SELECT subquery to reference itself by name. Such a subquery must have the form ! non_recursive_term { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] recursive_term where the recursive self-reference must appear on the right-hand ! side of ! UNION/INTERSECT/EXCEPT. ! 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 SELECT query in *************** SELECT DISTINCT ON (location) location, *** 1129,1144 **** The UNION clause has this general form: ! select_statement UNION [ ALL | DISTINCT ] select_statement ! select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. ! (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.) --- 1140,1154 ---- The UNION clause has this general form: ! [ limited_select UNION [ ALL | DISTINCT ] [ ... ] select ! limited_select is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. ! (ORDER BY and LIMIT can be ! attached if it is enclosed in parentheses.) The final select can contain the clauses listed ! above and those are applied to the result of the UNION. *************** SELECT DISTINCT ON (location) location, *** 1182,1189 **** The INTERSECT clause has this general form: ! select_statement INTERSECT [ ALL | DISTINCT ] select_statement ! select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. --- 1192,1199 ---- The INTERSECT clause has this general form: ! limited_select INTERSECT [ ALL | DISTINCT ] [ ... ] select_statement ! limited_select is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. *************** SELECT DISTINCT ON (location) location, *** 1230,1237 **** The EXCEPT clause has this general form: ! select_statement EXCEPT [ ALL | DISTINCT ] select_statement ! select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. --- 1240,1247 ---- The EXCEPT clause has this general form: ! limited_select EXCEPT [ ALL | DISTINCT ] [ ... ] select_statement ! limited_select is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. --DocE+STaALJfprDB--