Room Databse - Query Error as no viable alternative at input

Question

I am using Room in Android and done as below in my dao file :

@Query("select DISTINCT timeStamp, batteryId, status " +
            "from (select batteryDetails.*, " +
            "lag(status) over (partition by batteryId order by timeStamp) as oldStatus " +
            "from batteryDetails) batteryDetails " +
            "where status is null or oldStatus <> status")
    suspend fun getHistoryEventsData(): List<BatteryDetailsHistory>?

While executing above query, Getting below error :

error: no viable alternative at input '(select batteryDetails.*, lag(status) over (' public abstract java.lang.Object getHistoryEventsData(@org.jetbrains.annotations.NotNull() ^ extraneous input '(' expecting {, ';', 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_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}

There might be some issue in query syntax. Please guide.

This query I got from backend. Using it with Android Room database. It's fine? — Jaimin Modi, Jun 09 '21 at 05:00
@eshirvana Can you please guide me to remove this window functions from the above query and make it simple.. — Jaimin Modi, Jun 09 '21 at 07:34

score 1 · Answer 1 · answered Jun 09 '21 at 11:28

If your version of SQLite does not support window functions, you can replace LAG() with a correlated subquery:

SELECT DISTINCT timeStamp, batteryId, status  
FROM (
  SELECT b1.*,  
    (
      SELECT b2.status 
      FROM batteryDetails b2 
      WHERE b2.batteryId = b1.batteryId AND b2.timeStamp < b1.timeStamp 
      ORDER BY b2.timeStamp DESC LIMIT 1
    ) AS oldStatus  
  FROM batteryDetails b1
) 
WHERE status IS NULL OR oldStatus <> status

Finally I have deal it with sub query. – Jaimin Modi Aug 05 '21 at 06:29 — Jaimin Modi, Aug 05 '21 at 06:29

score 0 · Answer 2 · answered Jun 08 '21 at 17:04

Looks like Your version of sqlite doesn't support window functions including LAG because android app use build-in version on sqlite library to android OS. That's why version of sqlite depends on android's api level version (where app running). According to sqlite docs(paragraph 6) the window functions was added in version 3.25.0 and according to google docs (and other answer on stackoverflow)the sqlite's window functions supports on android since android api level 30.

To solve the problem of fragmentation of slqlite library you can use android-requery which allows to use last version of sqlile in all android versions(since API level 14). It's easy to use this library with room.

Finally I have deal it with sub query. – Jaimin Modi Aug 05 '21 at 06:30 — Jaimin Modi, Aug 05 '21 at 06:30

Room Databse - Query Error as no viable alternative at input

2 Answers2