Android Room + Window Functions

Question

I try to use a Window Functions in a Room Query. The parser is complaining about my query. I simply try to add a "ROW_NUMBER() OVER (ORDER BY column)" expression in my select statement. Without this expression, the query is running correctly.

Parser error:

extraneous input '(' expecting {<EOF>, ';', ',', K_ALTER, K_ANALYZE, 
K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH, K_DROP, 
K_END, K_EXCEPT, K_EXPLAIN, K_FROM, K_GROUP, K_INSERT, K_INTERSECT, 
K_LIMIT, K_ORDER, K_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, 
K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UNION, K_UPDATE, K_VACUUM, 
K_VALUES, K_WHERE, K_WITH, UNEXPECTED_CHAR}
mismatched input ')' expecting {<EOF>, ';', ',', '=', '*', '+', '-', 
'||', '/', '%', '<<', '>>', '&', '|', '<', '<=', '>', '>=', '==', '!=', 
'<>', K_ALTER, K_ANALYZE, K_AND, K_ASC, K_ATTACH, K_BEGIN, K_BETWEEN, 
K_COLLATE, K_COMMIT, K_CREATE, K_DELETE, K_DESC, K_DETACH, K_DROP, 
K_END, K_EXPLAIN, K_GLOB, K_IN, K_INSERT, K_IS, K_ISNULL, K_LIKE, 
K_LIMIT, K_MATCH, K_NOT, K_NOTNULL, K_OR, K_PRAGMA, K_REGEXP, 
K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, 
K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}
extraneous input ')' expecting {<EOF>, ';', ',', K_ALTER, K_ANALYZE, 
K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH, K_DROP, 
K_END, K_EXPLAIN, K_INSERT, K_LIMIT, K_PRAGMA, K_REINDEX, K_RELEASE, 
K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, K_VACUUM, 
K_VALUES, K_WITH, UNEXPECTED_CHAR} extraneous input 'add_permutation' 
expecting {<EOF>, ';', ',', '=', '*', '+', '-', '||', '/', '%', '<<', 
'>>', '&', '|', '<', '<=', '>', '>=', '==', '!=', '<>', K_ALTER, 
K_ANALYZE, K_AND, K_ASC, K_ATTACH, K_BEGIN, K_BETWEEN, K_COLLATE, 
K_COMMIT, K_CREATE, K_DELETE, K_DESC, K_DETACH, K_DROP, K_END, 
K_EXPLAIN, K_GLOB, K_IN, K_INSERT, K_IS, K_ISNULL, K_LIKE, K_LIMIT, 
K_MATCH, K_NOT, K_NOTNULL, K_OR, K_PRAGMA, K_REGEXP, K_REINDEX, 
K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, 
K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}

Answer 1

Android Room will not be using a version of SQLite that includes the Windows Functions which requires SQLite 3.25.0 or greater, as per :-

Window function support was added to SQLite with release version 3.25.0 (2018-09-15). The SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave. Many test cases have been run against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL. Window Functions

The latest Android API 28 appears to use 3.19 as it is not listed at android.database.sqlite, so it would use the same as API 27.

As such the SQLite sees the unknown clause as a syntax error.