Bug? OFFSET doesn't work anymore with SQLCipher 1.50 ?

[jmon]

Hello,

I recently recompiled my app with SQLCipher 1.50 (Last time I used it I was using 1.30 I think). One of my query doesn't work anymore. You can see the attached example, the "OFFSET" returns the wrong result.
Previously, it would work like: rows 1-10, 11-20, 21-30 .... 991-1000.
But now I get: 1-10, 1-20, 1-30, .... 1-1000.


I have tried with different queries:

SELECT bar FROM foo ORDER BY bar ASC LIMIT ${limit} OFFSET ${offset};

SELECT bar FROM foo ORDER BY bar ASC LIMIT ${offset},${limit};

(http://www.sqlitetutorial.net/sqlite-limit/)
But both fail.


Using "LIMIT" only works as expected:

SELECT bar FROM foo ORDER BY bar ASC LIMIT ${limit};

1-10
1-10
...
1-10


I haven't kept the old SQLCipher version, so I can't confirm that it was working properly before. But it did for the past 5 years? I Haven't changed the code apart from that. It's not only me, my users also noticed the problem. :-S

Thank you for your help.
Jmon

Note: The example creates a DB with 1000 records in it. Pressing the button, should write selected records in the label, each time the button is pressed.
Expected result is:
OFFSET IS AT= 30
rowCount= 10 (Should be : 10)
row0030
row0031
....
row0039


but I get:
OFFSET IS AT= 30
rowCount= 30 (Should be : 10)
row0001
row0002
....
row0039

 

[Erel]

The SQLCipher wrapper doesn't do anything with the query itself. It is passed as-is to the underlying SQLCipher SDK. If there is a bug then it is in the SQLCipher engine.

 

[jmon]

Yes probably. I see that on v 1.50 :

• Based on SQLCipher v3.59

Would you know where I can find the previous "sqlcipher_native.jar" version? I could give it a try and see if the bug happens with the new one?

 

[jmon]

I found the link:
https://search.maven.org/#search|gav|1|g:"net.zetetic" AND a:"android-database-sqlcipher"
I'll do a test

[edit] swapping the jar doesn't work, I get an error

 

[jmon]

I reinstalled the previous version of my app, with the previous SQLcipher. It was working with no problem.
The reason I upgraded SQLCipher was because of the issues with Android 6&7, which are fixed now with the new version. The problem is that I also increased the target SDK version to 26, so I think I can't revert back to the previous version?

Arg... I didn't even realize the bug until several people reported it. My limit and offset numbers are quite high, so I didn't scroll the results that much to see the bug.. I think I'll have to un-publish my app until the problem is solved.

 

[jmon]

Attached to this post, you will find the same code, but with SQLite instead of SQLCipher.
It works correctly as expected.

Couldn't find the jar file of sqlcipher_native.jar. But I found the Maven directory. How can I download to a jar?

 

[Erel]

Which version of SQLCipher do you want to use?

 

[jmon]

Would you still have version 1.42 (I mean the B4A library version)?

 

[Erel]

I need to search for it, however if I remember correctly it will not work on new versions of Android any way.

 

[jmon]

Thank you. I just wanted to test if it works with older versions. I know it doesn't work with new Android versions.
I will write a bug report to the sql cipher team.

Thank you

 

[Cassie]

Hi Guys,

I have same issues here for SQLCipher 1.5 which limit and offset does not work correctly. Any solution would be appreciated.

 

[Erel]

The issue is discussed here: https://github.com/sqlcipher/android-database-sqlcipher/issues/374#issuecomment-377319166

Deleting the jni\armeabi folder from the AAR might fix it. It might cause issues with low-end devices.