FILTER over window functions is not supported

[sivaraam]

Describe the bug

It is possible to use a FILTER clause before the OVER clause in some databases (PostgreSQL, SQLite; as a side note, it is actually part of the SQL standard). It is not possible to parse queries with the FILTER clause using the library.

Reference

• The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions

To Reproduce

Steps to reproduce the behavior:

1. Example SQL

SELECT COUNT(*) FILTER (WHERE name = 'Raj') OVER (PARTITION BY name) FROM table

2. Parsing this SQL using JSqlParser with this statements

        try {
            Statement st = CCJSqlParserUtil.parse(
                    "SELECT COUNT(*) FILTER (WHERE name = 'Raj') OVER (PARTITION BY name) FROM table"
            );
            System.out.println(st);
        } catch (JSQLParserException e) {
            e.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: "(" "("
    at line 1, column 24.

Was expecting one of:

    ","
    ";"
    "CONNECT"
    "EXCEPT"
    "FOR"
    "FROM"
    "GROUP"
    "HAVING"
    "INTERSECT"
    "INTO"
    "MINUS"
    "ORDER"
    "START"
    "UNION"
    "WHERE"
    "WINDOW"
    <EOF>

Expected behavior
It should possible to successfully parse queries with FILTER clause.

System

• Database you are using: PostgreSQL, SQLite.
• Java Version: 11.0.3
• JSqlParser version: 3.0

[localdevjs]

Wow, I just literally ran into this problem tonight myself. It would be great if window functions could be supported.

[wumpz]

Window functions are supported. This filter option is not. PRs are welcome.