SQLite: OVER () and OVER (ORDER BY ...) rejected — PARTITION BY treated as required

Version

node-sql-parser@5.4.0

Summary

In the SQLite dialect, PARTITION BY is required inside OVER (...). SQLite makes every clause of a window definition optional, so OVER () and OVER (ORDER BY ...) are valid SQLite that the parser rejects.

Every other dialect I tried parses the bare form, so this looks like an oversight in the SQLite grammar rather than an intentional restriction.

Reproduction

const { Parser } = require('node-sql-parser')
const p = new Parser()

p.astify('SELECT SUM(b) OVER () FROM t', { database: 'SQLite' })
// Error: Expected "#", "--", "/*", "PARTITION", or [ \t\n\r] but ")" found.

p.astify('SELECT SUM(b) OVER (ORDER BY d) FROM t', { database: 'SQLite' })
// Error: Expected "#", "--", "/*", "PARTITION", or [ \t\n\r] but "O" found.

p.astify('SELECT SUM(b) OVER (PARTITION BY c) FROM t', { database: 'SQLite' })
// OK

The function does not matter — ROW_NUMBER(), RANK(), LAG() and COUNT(*) all behave the same way: fine with PARTITION BY, rejected without it.

Expected

All three should parse. Per the SQLite window-function grammar, window-defn is:

( [base-window-name] [PARTITION BY expr, ...] [ORDER BY term, ...] [frame-spec] )

— every clause optional, including an entirely empty ().

Confirmed against the engine: SQLite 3.53.4 (via better-sqlite3 13.x) executes all three forms correctly.

Dialect comparison

Same two queries, partition = OVER (PARTITION BY c), bare = OVER ():

SQLite        partition:OK  bare:REJECT
MySQL         partition:OK  bare:OK
PostgreSQL    partition:OK  bare:OK
BigQuery      partition:OK  bare:OK
TransactSQL   partition:OK  bare:OK
MariaDB       partition:OK  bare:OK
Hive          partition:OK  bare:OK
Snowflake     partition:OK  bare:OK

Impact

OVER () is how you get a grand total alongside a grouped aggregate in one pass:

SELECT c, COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM t GROUP BY c

Without it, callers issue a second query for the total. There is a workaround — OVER (PARTITION BY NULL) parses and is semantically identical — but it is obscure enough that anyone hitting this will more likely conclude window functions are unsupported in the SQLite dialect and stop using them.

Related SQLite gaps found alongside this

Same pattern — valid SQLite that 5.4.0's SQLite dialect rejects, all verified as executing correctly on SQLite 3.53.4. Listing them here in case they share a cause; happy to split them into separate issues if you would prefer:

• SELECT COUNT(*) FILTER (WHERE b > 1) FROM t — aggregate FILTER clause
• SELECT a FROM t ORDER BY b NULLS LAST
• SELECT SUM(b) OVER (PARTITION BY a ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t — frame spec
• SELECT SUM(b) OVER w FROM t WINDOW w AS (PARTITION BY a) — named window

For contrast, these all parse fine in the SQLite dialect: CTEs, CASE WHEN, UNION, subqueries in FROM, EXISTS, CAST, COALESCE, strftime, date(), GROUP_CONCAT, IIF, LIMIT/OFFSET, HAVING, and joins.

I am happy to put together a PR for the PARTITION BY fix if that would help — it looks like making the clause optional in the SQLite window rule.

Start at the SQLite window-function grammar entry point and run the three SQL reproductions from the issue. Done means the parser accepts OVER (), OVER (ORDER BY d), and OVER (PARTITION BY c) for SQLite without regressing the existing partitioned form.