Component used: Room
Version used: 2.6.1
Devices/Android versions reproduced on: 7.6 Fold-in with outer display. Android API 34.

Issue Description

Since SQLite version 3.25.0, SQLite supports window functions. This includes queries such as ROW_NUMBER() OVER (PARTITION BY ...). However, after the OVER and on the ( parenthesis, it gives a style-check erorr: <compound operator>, FROM, GROUP, LIMIT, ORDER, WHERE or comma expected, got '('.

There are many Stackoverflow issues describing this issue:

But all these issues date back from years ago, when Room was still using SQLite versions below 3.25.0.

Currently, according to your own API-SQLite conversion table, Room uses SQLite 3.39-3.42.0 as of API 34. So it does support window functions. The page also says that SQLite versions may differ per device, but I already confirmed that on my own device, I use SQLite version 3.39.2.

So even though my SQLite version is high enough to actually support window functions, Room doesn't allow me to run the query because the query cannot be parsed. At least, I think it's a parsing problem because when I use a raw query to run the query (so basically just running a query without the style checks of Room), my query just runs perfectly and actually queries the data like it should. So that signals no problems of SQLite itself.

I'll give a coding example, so you can reproduce it. Create a databate and tables as usual. Then, in your DAO class, you write a query for the database:

@Query("<My Query>")
Foo queryMethod()

Where <My Query> is a query that uses a window function (like ROW_NUMBER() OVER (...) for example). You should then see an error at the first ( parenthesis after OVER.

However, when just using a raw query:

SupportSQLiteQuery ssq = new SimpleSQLiteQuery("<My Query>")

@RawQuery
Foo queryMethod(SupportSQLiteQuery query)

gives no problems. Here, you would pass ssq to queryMethod, and you would see that executing it does not give errors and returns your queried data.

To be fixed

Allow the use of windows functions in @Query("...") annotations, without causing errors. Again, this is probably a parsing error in Room, not an error caused by SQLite.

Issue 378896402

Links (7)

Issue metadata