Complex view breaking SQLite on Android < v11
(1) By PierreKnx (pierreknx) on 2021-06-01 16:02:23
Hello, this is strange one. Using SQlite3 3.35 on my pc, I create a database, with three tables and two views. Then, I copy it over to my phone (Android 11) and open it with aSQLiteManager to test: everything works great including both views. I copy the exact same database file to an Android 10 or below phone. When I try to open it with aSQLiteManager, it says "Race.db is not a database" and then overwrites it with a blank database. I tried other SQLite database utilities with the same result. They all work OK with my database on Android 11 but not on phones running previous versions. It really looks like the view "r1_Trois" is the cause of the problem. If I re-create the database without that view, the new database can be opened without any issues on all Android versions and the other view is working just fine. Thanks in advance for your help! Here's the SQl code: CREATE TABLE [Station] ( [Station] INTEGER, [DescStation] NVARCHAR(25), [NoApp] INTEGER); CREATE TABLE [Destinataire] ( [Courriel] NVARCHAR(60)); CREATE TABLE [Coureurs] ( [Dossard] INTEGER PRIMARY KEY, [Prenom] NVARCHAR(25), [Nom] NVARCHAR(25), [Epreuve] NVARCHAR(10), [Sexe] NVARCHAR(1), [TempsPassage] DATETIME, [Statut] NVARCHAR(9)); CREATE VIEW r1_Trois AS SELECT Epreuve, rn AS "#", Sexe as "S", Dossard AS "3_Premiers", STRFTIME('%H:%M %d',TempsPassage) AS "Temps/Jour" FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Epreuve, Sexe ORDER BY TempsPassage) rn FROM Coureurs WHERE TempsPassage <> '' AND Statut IS "En_Course") WHERE rn <= 3 ORDER BY SUBSTR(Epreuve, 5) + 0 DESC, Sexe DESC, rn ASC; CREATE VIEW r2_DNFs AS SELECT Epreuve, Dossard as "Liste_DNFs", STRFTIME('%H:%M %d',TempsPassage) AS "Temps/jour" FROM Coureurs WHERE TempsPassage <> '' AND Statut is "DNF" ORDER BY SUBSTR(Epreuve, 5) + 0 DESC, Dossard; .quit
(2) By Kees Nuyt (knu) on 2021-06-01 16:23:32 in reply to 1 [link]
Your view uses a [window function][1] `OVER (PARTITION BY', which may not be supported by the SQLite version on Android < v11, or by aSQLiteManager on those versions. Use [SELECT sqlite_version()][2] to find out what version your target Android version has installed, and consult the [release history][3] to find out if that version is supposed to support window functions. [1]: https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions [2]: https://www.sqlite.org/lang_corefunc.html#sqlite_version [3]: https://www.sqlite.org/changes.html
(3) By PierreKnx (pierreknx) on 2021-06-01 17:38:16 in reply to 2 [link]
Thank you so much for the explanation. Is there any way that this view could be simplified to avoid using the window function? I don't mind if I have to do separate views by distance if needed. I was handled over this code and my knowledge of SQLite is very limited. The view is giving the top three racers (their bib no.) by time, for men and women, and for each of the races. Epreuve # S 3_Premiers Temps/Jour ---------- - - ---------- ---------- UTHC 125km 1 M 107 02:00 17 UTHC 125km 2 M 133 02:14 17 UTHC 125km 3 M 118 02:29 17 UTHC 125km 1 F 157 02:19 17 UTHC 125km 2 F 48 02:40 17 UTHC 125km 3 F 136 04:14 17 UTHC 80km 1 M 8116 02:12 17 UTHC 80km 2 M 8010 02:59 17 UTHC 80km 3 M 8124 03:10 17 UTHC 80km 1 F 8062 02:03 17 UTHC 80km 2 F 8034 02:15 17 UTHC 80km 3 F 8139 02:35 17 UTHC 65km 1 M 6073 02:09 17 UTHC 65km 2 M 6230 02:33 17 UTHC 65km 3 M 6178 02:36 17 UTHC 65km 1 F 6121 02:26 17 UTHC 65km 2 F 6204 02:42 17 UTHC 65km 3 F 6276 02:54 17
(4) By PierreKnx (pierreknx) on 2021-06-02 12:21:00 in reply to 3 [link]
Got an amazing answer from @forpas on StackOverflow using a correlated subquery instead of the window function. The View now works without a glitch on Android7, 10 and 11 !
(5) By Kees Nuyt (knu) on 2021-06-02 13:39:59 in reply to 4 [link]
Thanks for letting us know you were able to solve it. I assume you mean the following pages: - [The stack overflow entry](https://stackoverflow.com/questions/66717812/sqlite-best-three-times-for-men-and-women-for-each-race-distance/66718392#66718392) - [A solution](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=483122f01f2a809c0db8a09e82cabd5c)