Re: unclear syntax explanation for multiple window definitions in a SELECT query

On Thursday, February 16th, 2023 at 5:13 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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