Not possible to specify window specification in the absence of ORDER BY clause

[sivaraam]

Describe the bug

In Postgres and MySQL, it is possible to specify a window specification (ROWS/RANGE ...) without an ORDER BY clause for window functions. But the parser requires the ORDER BY clause to be present before the window specification.

Reference

• SQLite documentation about window function
• PostgreSQL documentation about window function syntax:

   [ existing_window_name ]
   [ PARTITION BY expression [, ...] ]
   [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
   [ frame_clause ]
  
   frame_clause can be one of
   	{ RANGE | ROWS } frame_start
   	{ RANGE | ROWS } BETWEEN frame_start AND frame_end
  

• MySQL documentation about window function syntax:

  window_spec:
      [window_name] [partition_clause] [order_clause] [frame_clause]
  

To Reproduce

Steps to reproduce the behavior:

1. Example SQL

select subject_id, student_id, mark, sum(mark) over (partition by (subject_id) rows between unbounded preceding and current row) from marks

2. Parsing this SQL using JSqlParser with this statements

        try {
            Statement st = CCJSqlParserUtil.parse("select subject_id, student_id, mark, sum(mark) over (partition by (subject_id) rows between unbounded preceding and current row) from marks");
        }
        catch (JSQLParserException jsqle) {
            jsqle.printStackTrace();

        }

3. Exception

net.sf.jsqlparser.JSQLParserException
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:51)
...
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "rows" "ROWS"
    at line 1, column 80.

Was expecting one of:

    "&"
    ")"
    ","
    "::"
    "<<"
    ">>"
    "COLLATE"
    "ORDER"
    "["
    "^"
    "|"

Expected behavior

It should be possible to specify window without the ORDER BY clause.

System

• Database you are using: Postgres, MySQL
• Java Version: 11.0.3
• JSqlParser version: 3.0

[sivaraam]

I was just wondering, is there any specific reason behind requiring ORDER BY to be present before the window specification (a.k.a frame clause)?