On 2015-05-19 07:39 PM, 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 > ); > > 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
This is not right either... The returned astart value of 7169780000 is not less than the 7169319380 specified in the where-clause. > > 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. None of these meet the constraints, not even the first one. > Why is this happening? Hard to say without the full schema. .dump it from the command line and paste in a reply please.
