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}

Actinomycin

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.

Ophthalmologist

Thanks Mike for the explanation, it make sense. – Actinomycin

@JeromeDupont What's the solution? – Bilander

Is the latest version contains the use of lag() and over() window function now? Or still we are struggling? – Bilander

API 30+ comes with SQLite 3.28.0 so window functions are available for such devices/emulators. – Ophthalmologist

@Ophthalmologist have you got this working? I'm using targetSDK 31, minSDK 30, for room the latest 2.5 alpha, and the 7.1.2 AGP. I get issues when compiling, and also when pasting a sample query on App Inspection – Polariscope

Answer 2

Indeed the SQLite version embedded in Android does not provide window functions yet. But I want to post a workaround inspired by this question: How to use ROW_NUMBER in sqlite, if anyone else is struggling with this on Android.

For example in this relation:

| id | value | updated_at                     |
-----------------------------------------------
| 1  | yes   | 2020-11-26 11:27:45.662 +00:00 |
| 3  | yes   | 2020-11-27 17:19:45.662 +00:00 |
| 4  | yes   | 2020-11-26 11:21:45.662 +00:00 |
| 6  | no    | 2020-11-26 14:42:45.662 +00:00 |
| 9  | yes   | 2020-11-27 15:08:45.662 +00:00 |
-----------------------------------------------

You can use this query to only get the 'yes' rows ordered by the updated_at column associated to a row number:

SELECT
    id,
    value,
    (
        SELECT
            count(*)
        FROM
            tbl b
        WHERE
            a.updated_at >= b.updated_at
            AND value = 'yes'
    ) AS row_num,
    updated_at
FROM
    tbl a
WHERE
    value = 'yes'
ORDER BY updated_at

It produces the following results:

| id | value | row_num | updated_at                     |
---------------------------------------------------------
|  4 |   yes |   1     | 2020-11-26 11:21:45.662 +00:00 |
|  1 |   yes |   2     | 2020-11-26 11:27:45.662 +00:00 |
|  9 |   yes |   3     | 2020-11-27 15:08:45.662 +00:00 |
|  3 |   yes |   4     | 2020-11-27 17:19:45.662 +00:00 |
---------------------------------------------------------

Beware that the OVER (ORDER BY updated_at) clause normally present in the window function ROW_NUMBER is replaced here by the WHERE a.updated_at >= b.updated_at clause of the sub-query which produces the row_num column. Also you need to filter the rows of this sub-query as you do it in the main query, otherwise, the row number will take into account all the rows of the table and you won't get the expected result. That's the AND value = 'yes' part of the query.

Isometry

Answer 3

It is a Syntax error in your SQL Query.

Make sure that when you are forming your Query,put a space at the end of each line.

Dewayne