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 ); 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
