SQLite Forum

Complex view breaking SQLite on Android < v11
Login

Complex view breaking SQLite on Android < v11

(1) By PierreKnx (pierreknx) on 2021-06-01 16:02:23 [link] [source]

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, [NoApp] INTEGER);

CREATE TABLE Destinataire);

CREATE TABLE Coureurs, [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] [source]

Your view uses a window function `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() to find out what version your target Android version has installed, and consult the release history to find out if that version is supposed to support window functions.

(3) By PierreKnx (pierreknx) on 2021-06-01 17:38:16 in reply to 2 [link] [source]

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 [source]

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] [source]

Thanks for letting us know you were able to solve it.

I assume you mean the following pages: