Hi Dan, Thank you for responding.
The integrity check result is OK: sqlite> pragma integrity_check; integrity_check ------------------------------ ok Here is the session copied. I had to obfuscate some values though? I don?t think this should prevent investigating this. $ sqlite3 myDatabase -- Loading resources from /Users/adamp/.sqliterc SQLite version 3.8.7.1 2014-10-29 13:59:56 Enter ".help" for usage hints. sqlite> select * from list_of_numbers where astart < "7169319380" and aend > "7169319380"; nn astart aend alength usesflag blength coolflag alpaid ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------------ 1 7169780000 7169839999 3 Y 10 0 1b3603393975150ni sqlite> CREATE INDEX startingnumber ON list_of_numbers(astart); sqlite> CREATE INDEX endingnumber ON list_of_numbers(aend); sqlite> select * from list_of_numbers where astart < "7169319380" and aend > "7169319380"; nn astart aend alength usesflag blength coolflag alpaid ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------------ 1 7169780000 7169839999 3 Y 10 0 1b3603393975150ni 1 7014660000 7014669999 3 Y 10 0 1b3603393975150ni 1 7015470000 7015479999 3 Y 10 0 1b3603393975187ni 1 7019710000 7019719999 3 Y 10 0 1b3603396077307ni 1 7038330000 7038339999 3 Y 10 0 1b3963340750704ni 1 7057930000 7057939999 3 Y 10 0 1b3603393975173ni 1 7054040000 7054049999 3 Y 10 0 1b3603393975171ni 1 7054070000 7054079999 3 Y 10 0 1b3603393975171ni 1 7075550000 7075559999 3 Y 10 0 1b3963340145811ni 1 7078140000 7078149999 3 Y 10 0 1b3963340145811ni Some more info about the data: ?astart? has some repeated values, but the combination of nn + astart is unique across the table. Total ~200K records. Thank you for helping investigate. -- adam > On 19 May 2015, at 20:00, Dan Kennedy <danielk1977 at gmail.com> wrote: > > On 05/20/2015 12:39 AM, Adam Podstawczy?ski wrote: >> Hi all, >> >> I have this schema: >> >> >> CREATE TABLE list_of_numbers ( >> nn TEXT, >> astart INT, >> aend INT, >> alength INT, >> usesflag TEXT, >> blength INT, >> coolflag NUM, >> alphaid >> ); > > Thanks for the report. Are you able to cut and paste a shell tool session > showing the query working without the index, creating the index, and then the > query failing? > > Also run "PRAGMA integrity_check" in the same session if possible. > > Posting an unedited cut'n'paste from the shell tool causes bug reports to be > treated more urgently - as everybody can proceed without worrying that the OP > may have made a typo. > > Thanks, > Dan. > > > > >> >> Some example data: >> >> nn astart aend alength usesflag >> blength coolflag alpaid >> ------------------------------ ---------- ---------- ---------- >> ---------- ---------- ---------- ------------------ >> 1 7017000000 7017009999 9 Y >> 10 0 1b9633407507819ni >> 1 7017070000 7017039999 9 Y >> 10 0 1b6033960773078ni >> 1 7017040000 7017059999 9 Y >> 10 0 1b9633407507819ni >> 1 7017060000 7017069999 9 Y >> 10 0 1b6033960773078ni >> 1 7017070000 7017079999 9 Y >> 10 0 1b6033939751871ni >> 1 7017080000 7017099999 9 Y >> 10 0 1b9633407507819ni >> >> And this query: >> >> sqlite> select * from list_of_numbers where astart < 7169319380 and aend > >> 7169319380; >> >> Now, the above query is expected to return one record only ? and it does: >> >> 1nn astart aend alength >> usesflag blength coolflag alpaid >> ------------------------------ ---------- ---------- ---------- >> ---------- ---------- ---------- ------------------ >> 1 7169780000 7169839999 3 Y >> 10 0 1b3603393975150ni >> >> But when I add index to the very same table: >> >> CREATE INDEX startingnumber ON list_of_numbers(astart); >> CREATE INDEX endingnumber ON list_of_numbers(aend); >> >> The behavior of the same query becomes unpredictable: >> >> nn astart aend alength usesflag >> blength coolflag alpaid >> 1 7169780000 7169839999 3 Y >> 10 0 1b3603393975150ni >> 1 7014660000 7014669999 3 Y >> 10 0 1b3603396077307ni >> 1 7015470000 7015479999 3 Y >> 10 0 1b3603393975187ni >> 1 7019710000 7019719999 3 Y >> 10 0 1b3603396077307ni >> 1 7038330000 7038339999 3 Y >> 10 0 1b3963340750704ni >> 1 7057930000 7057939999 3 Y >> 10 0 1b3603393975173ni >> 1 7054040000 7054049999 3 Y >> 10 0 1b3603393975171ni >> 1 7054070000 7054079999 3 Y >> 10 0 1b3603393975171ni >> >> >> ... and a few dozens more results. Only the first one meets the query >> constraints. >> >> Why is this happening? >> >> Thanks, >> >> Adam >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org <mailto:sqlite-users at >> mailinglists.sqlite.org> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org <mailto:sqlite-users at > mailinglists.sqlite.org> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
