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 > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
