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

jmon

Well-Known Member
Licensed User
Longtime User
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
 

Attachments

  • SqliteCipherOffset.zip
    8.3 KB · Views: 221

jmon

Well-Known Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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?
 

Attachments

  • SqliteBehavior.zip
    8.3 KB · Views: 216

jmon

Well-Known Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
Hi Guys,

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