Обсуждение: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
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.
2018-03-15 7:18 GMT-03:00 PG Doc comments form <noreply@postgresql.org>: > 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" is defined as a sub-select that can appear in the FROM clause (see the From Clause section). > select_statement UNION [ ALL | DISTINCT ] select_statement > > which uses boldface "select_statement" instead of boldface "select" as in > 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
Вложения
Euler Taveira <euler@timbira.com.br> writes:
> 2018-03-15 7:18 GMT-03:00 PG Doc comments form <noreply@postgresql.org>:
>> 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
2018-03-16 2:17 GMT-03:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Euler Taveira <euler@timbira.com.br> writes:
>> 2018-03-15 7:18 GMT-03:00 PG Doc comments form <noreply@postgresql.org>:
>>> 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?
>
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.
> 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.)
>
Yup. It does not inform that the ORDER BY should be at the end of the UNION.
> 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.
>
I don't see it as a problem since that part will not (never | rarely) change.
> 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
>
The problem with adding "select_statement" to the synopsis is that it
is not self-explainable...
> 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.
>
... 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.
> 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?
>
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.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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.
David J.
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 <bruce@momjian.us> 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 +
Вложения
Bruce Momjian <bruce@momjian.us> writes:
> Based on this discussion, I have developed the attached patch which
> 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
On Mon, Apr 2, 2018 at 08:21:45PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Based on this discussion, I have developed the attached patch which > > 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. 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. -- Bruce Momjian <bruce@momjian.us> 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 +