2

I am trying to use CAST AS INTEGER in WHERE clause. Since I have a column as TEXT and I want to apply <= and >= on that column. It is working well (fine records as excpetectd i,e 12 records) on SqlBrowser but not returning accurate resutls (cast as integer seems like not working more than 1000 records) on emulator or device (its SqlLite Database ). Here is my query

SELECT  l.* FROM leads AS l   
WHERE cast ( l.lead_budget  as INTEGER)  >=  1200
AND  cast ( l.lead_budget as INTEGER ) <=  15000

I m wondering what Am I missing. Any guess

Junaid Hafeez
  • 1,618
  • 1
  • 16
  • 25

1 Answers1

3

This is an issue with kitkat. I had such issue in past. I tried same cast query on 6.0 and it was working as expected. Try it on 6.0 . But sqllite supports casting and here is it's documentation about cast as integer etc. But after a little more digging I found that most kitkat uses SQLite 3.7.11: .Here is a link. I found this page about versions of sqllite. They describe the issue they fixed in each version.

This is what they changed in 3.8.2

Changed the defined behavior for the CAST expression when floating point values greater than +9223372036854775807 are cast into into integers so that the result is the largest possible integer, +9223372036854775807, instead of the smallest possible integer, -9223372036854775808. After this change, CAST(9223372036854775809.0 as INT) yields +9223372036854775807 instead of -9223372036854775808. ← Potentially Incompatible Change!

And then in 3.8.7 They improve optimization of CAST operators. And then I found this ticket which explain alot of what's really wrong. Now it's fixed might work or new device but with older devices you have to find some other solution. With this os issue, you cannot rely on cast query. Since you are not sure about which os version uses which SQL version. Hope it helps.

Community
  • 1
  • 1
Nouman Ghaffar
  • 3,780
  • 1
  • 29
  • 37