Search code examples
sqlsqlitekotlinsubquerywindow-functions

Window functions in android's SQLite

I am struggling with android trying to use window functions. I have a database in which I have entries with a created_at field and a test field and when I try to get the max of created at for each test with that code (my code is in Kotlin but I guess the java code would be the same)

db.rawQuery("SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;", arrayOf()).close()

my app crashes, but with that code

db.rawQuery("SELECT MAX(created_at) FROM test_result;", arrayOf()).close()

everything works fine

Obviously in that use case, I could do a subquery and join with the table of the maxes, but in fact what I am trying to do requires getting the mean value over the last 4 entries for each test, so I really need window functions and I am puzzled that they don't work in android because they work in normal SQLite

Also, the error is completely useless

09-03 07:58:28.627 10723 10723 E AndroidRuntime: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.memorizer/com.example.memorizer.TestActivity}: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Error Code : 1 (SQLITE_ERROR)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused By : SQL(query) error or missing database.
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    (near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2957)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3032)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.-wrap11(Unknown Source:0)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1696)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.os.Handler.dispatchMessage(Handler.java:105)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.os.Looper.loop(Looper.java:164)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.main(ActivityThread.java:6944)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at java.lang.reflect.Method.invoke(Native Method)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:327)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1374)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused by: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Error Code : 1 (SQLITE_ERROR)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused By : SQL(query) error or missing database.
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    (near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1096)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:661)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1746)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1685)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.example.memorizer.TestActivity.getData(TestActivity.kt:202)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.example.memorizer.TestActivity.onCreate(TestActivity.kt:184)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.Activity.performCreate(Activity.java:7183)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1220)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2910)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    ... 9 more

my sqlite version is 3.18.2

Thanks for your time

EDIT: the issue is the version of sqlite on my phone, 3.25.0 was the first version to introduce window functions and my version is older than that

Solution

  • Window functions were added in SQLite 3.25, while you are running version 3.18.

    what I am trying to do requires getting the mean value over the last 4 entries for each test,

    An alternative is a correlated subquery:

    SELECT 
    created_at, 
    (
        SELECT AVG(value) 
        FROM test_result t1 
        WHERE t1.test = t.test and t1.created_at <= t.created_at
        ORDER BY t1.created_at DESC LIMIT 4
    ) running_avg_value
FROM test_result t

    This gives you a running average of the current row and the last 3 records of the same test, as ordered by created_at.

    For performance, consider an index on (test, created_at, value). Even so, this will probably not scale well against a large dateset. If you were to upgrade to a recent version (which should really consider), you would replace the subquery with:

    AVG(value) OVER(PARTITION BY test ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)