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

2018-03-16 2:17 GMT-03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Euler Taveira <euler(at)timbira(dot)com(dot)br> writes:
>> 2018-03-15 7:18 GMT-03:00 PG Doc comments form <noreply(at)postgresql(dot)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