window functions: document "workaround" to ORDER BY frame caveat

[RaduBerinde]

Radu Berinde (RaduBerinde) commented:

The window functions documentation has the caveat about how ORDER BY changes the default frame: https://www.cockroachlabs.com/docs/v19.2/window-functions.html#caveats

What is missing from this is a workaround - what do I need to do to keep the frame as it would be if there was no ORDER BY? (this came up with a user who wanted the ORDER BY just to control the order of elements in a json_agg).

I believe the correct thing to do would be to add RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING but I might be wrong - @justinj or @yuzefovich should confirm.

Jira Issue: DOC-396

[yuzefovich]

Radu is correct - if we want to have an ORDER BY clause but also to have the same window frame for every row, then RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING will do the job.