pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedunclear syntax explanation for multiple window definitions in a SELECT query
4+ messages / 3 participants
[nested] [flat]
* unclear syntax explanation for multiple window definitions in a SELECT query
@ 2023-02-16 09:54 PG Doc comments form <noreply@postgresql.org>
2023-02-16 16:13 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2023-02-16 09:54 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: postgresql@mackler.email
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-select.html
Description:
The syntax documentation for SELECT in SQL COMMANDS section is unclear in
explaining how to use multiple window definitions. It's easy to read it and
believe that multiple WINDOW keywords, one for each definition, are correct,
which is not the case.
See this stackoverflow question and answer on point:
https://stackoverflow.com/questions/59651529/multiple-named-windows-in-a-postgres-query
Thanks.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unclear syntax explanation for multiple window definitions in a SELECT query
2023-02-16 09:54 unclear syntax explanation for multiple window definitions in a SELECT query PG Doc comments form <noreply@postgresql.org>
@ 2023-02-16 16:13 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-16 17:18 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query Adam Mackler <adam@mackler.email>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-02-16 16:13 UTC (permalink / raw)
To: postgresql@mackler.email; pgsql-docs@lists.postgresql.org
On Thu, Feb 16, 2023 at 5:13 AM PG Doc comments form <noreply@postgresql.org>
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/sql-select.html
> Description:
>
> The syntax documentation for SELECT in SQL COMMANDS section is unclear in
> explaining how to use multiple window definitions. It's easy to read it
> and
> believe that multiple WINDOW keywords, one for each definition, are
> correct,
> which is not the case.
>
> See this stackoverflow question and answer on point:
>
> https://stackoverflow.com/questions/59651529/multiple-named-windows-in-a-postgres-query
>
>
This is how we've chosen to document this kind of grammar and I don't see a
compelling reason to revisit that decision on the basis of this report. I
could consider some additional wording regarding "each clause keyword only
ever appears once in a given (sub)query" though that seems almost
self-evident as part of the learning of SQL. The fact that you write
WINDOW and then a comma-separated listing of names plus definitions is how
that syntax diagram is read, just like with WITH, FROM, and GROUP BY
clauses in the same diagram.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unclear syntax explanation for multiple window definitions in a SELECT query
2023-02-16 09:54 unclear syntax explanation for multiple window definitions in a SELECT query PG Doc comments form <noreply@postgresql.org>
2023-02-16 16:13 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query David G. Johnston <david.g.johnston@gmail.com>
@ 2023-02-16 17:18 ` Adam Mackler <adam@mackler.email>
2023-02-17 17:09 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adam Mackler @ 2023-02-16 17:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: postgresql@mackler.email; pgsql-docs@lists.postgresql.org
On Thursday, February 16th, 2023 at 5:13 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
> This is how we've chosen to document this kind of grammar and I don't see a compelling reason to revisit that decision on the basis of this report. I could consider some additional wording regarding "each clause keyword only ever appears once in a given (sub)query" though that seems almost self-evident as part of the learning of SQL. The fact that you write WINDOW and then a comma-separated listing of names plus definitions is how that syntax diagram is read, just like with WITH, FROM, and GROUP BY clauses in the same diagram.
Yes, such additional wording would be helpful, thanks, as well as would be an example. Perhaps not in the SELECT reference section, but in section 4.2.8, which has a lot of detail about using window functions. Section 4.2.8 does have a single mention of existing_window_name in bold italics, as if it would be described in more detail lower on the page, but that's the only mention of it there.
The only instruction for how to have multiple window definitions is then in the SELECT reference, where it is represented only by [, ... ] indicating repetition of something, so that's good the reader at least knows it's possible, even if it's unclear exactly how. Inferring wrongly what the correct form is tripped me up, and it tripped up the guy who posted the question on SO, and at least six others upvoted the answer there, so at least a few people find value in being explicit about this.
Incidentally the other clauses you mention are all supported with examples in the manual showing how to include multiple elements without repeating the keyword: FROM in 2.6, WITH in 7.8.1, GROUP BY in 7.2.3. Besides the examples, that section 7.2.3 provides the syntax for GROUP BY as:
GROUP BY
grouping_column_reference
[
,
grouping_column_reference
]...
with grouping_column_reference repeated to indicate clearly that there is no keyword necessary before the second and subsequent elements, so at least in some contexts the clarity provided by such repetition is appropriate. Inconsistently the syntax given for WINDOW is:
WINDOW
window_name
AS (
window_definition
) [, ...]
rather than something like
WINDOW
window_name
AS (
window_definition
) [,
window_name
AS (
window_definition
)]...
Maybe the reference section for SELECT is the wrong place for that longer form, but section 4.2.8 (or some other referenced from there) could be.
Cheers,
--
Adam Mackler
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unclear syntax explanation for multiple window definitions in a SELECT query
2023-02-16 09:54 unclear syntax explanation for multiple window definitions in a SELECT query PG Doc comments form <noreply@postgresql.org>
2023-02-16 16:13 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query David G. Johnston <david.g.johnston@gmail.com>
2023-02-16 17:18 ` Re: unclear syntax explanation for multiple window definitions in a SELECT query Adam Mackler <adam@mackler.email>
@ 2023-02-17 17:09 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-02-17 17:09 UTC (permalink / raw)
To: Adam Mackler <adam@mackler.email>; +Cc: postgresql@mackler.email; pgsql-docs@lists.postgresql.org
On Thu, Feb 16, 2023 at 10:18 AM Adam Mackler <adam@mackler.email> wrote:
>
> Maybe the reference section for SELECT is the wrong place for that longer
> form, but section 4.2.8 (or some other referenced from there) could be.
>
>
Expanding Section 7.2.5 to be more consistent in level-of-detail with the
rest of that page (syntax and examples in particular) seems like an easy
sell.
https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WINDOW
I'm not convinced that modifying the SELECT page to make all of the
relevant [, ...] items [, blah-blah-blah ] instead is going to be a net
improvement.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-02-17 17:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-16 09:54 unclear syntax explanation for multiple window definitions in a SELECT query PG Doc comments form <noreply@postgresql.org>
2023-02-16 16:13 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-16 17:18 ` Adam Mackler <adam@mackler.email>
2023-02-17 17:09 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox