The following SQL statement works fine on my Android test devices (Android 13):
SELECT *
FROM portfolio
JOIN holdingCalculations
ON portfolio.uid = holdingCalculations.uid
ORDER BY calc_num_transactions = 0, CASE use_local_currency_for_totals WHEN TRUE THEN calc_total_change_percent_local ELSE calc_total_change_percent END DESC, portfolio.name COLLATE NOCASE
However after I've released my Android app to production, I'm seeing exceptions on some customer devices (Such as POCO F1 running Android 10, Galaxy S8+ running Android 9):
Fatal Exception: android.database.sqlite.SQLiteException: no such column: TRUE (code 1 SQLITE_ERROR[1]): , while compiling: SELECT *
FROM portfolio
JOIN holdingCalculations
ON portfolio.uid = holdingCalculations.uid
ORDER BY calc_num_transactions = 0, CASE use_local_currency_for_totals WHEN TRUE THEN calc_total_change_percent_local ELSE calc_total_change_percent END DESC, portfolio.name COLLATE NOCASE
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(SQLiteConnection.java)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1229)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:703)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1865)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1840)
at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.kt:156)
at com.squareup.sqldelight.android.AndroidQuery.executeQuery(AndroidSqliteDriver.kt:253)
at com.squareup.sqldelight.android.AndroidQuery.executeQuery(AndroidSqliteDriver.kt:228)
at com.squareup.sqldelight.android.AndroidSqliteDriver$executeQuery$2.invoke(AndroidSqliteDriver.kt:149)
at com.squareup.sqldelight.android.AndroidSqliteDriver$executeQuery$2.invoke(AndroidSqliteDriver.kt:149)
at com.squareup.sqldelight.android.AndroidSqliteDriver.execute(AndroidSqliteDriver.kt:127)
at com.squareup.sqldelight.android.AndroidSqliteDriver.executeQuery(AndroidSqliteDriver.kt:149)
at com.squareup.sqldelight.db.SqlDriver$DefaultImpls.executeQuery$default(SqlDriver.kt:35)
My guess is that this sqlite syntax is not supported on some devices. Could this be an issue with the version of sqlite installed on those devices, and is there a backwards compatible way of writing this query?
The use of true/false requires SQLite 3.23.0 or higher. Only since API 30 (Android 11) has the SQLite version been high enough (3.28.0).
Unless you are limiting the distribution to 11+, then you should use 0 for false and 1 for true.
You may wish to refer to:-
https://www.sqlite.org/lang_expr.html#boolean_expressions
and