On 2017/11/21 7:35 AM, Jens Alfke wrote:
On Nov 20, 2017, at 2:05 PM, Simon Slavin <slav...@bigfraud.org> wrote:
INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana')
This gives you a value of 1.5, and puts the new entry in the right place.
This solution (which comes up every time this problem is discussed, it seems)
is attractive but not very scaleable. All you have to do is add 60 records one
at a time after record 1, and you’ll exceed the precision of double-precision
floating point and get duplicate values that don’t have a stable sort order.
It doesn't really matter....
That assumes you are not starting from an integer part (like 4000) and
hitting the exact same relative insert spot every time, which /can/
happen, but is hugely unlikely.
In the very unlikely event that you /are/ inserting at the same spot
(let's ignore for a moment that the chosen design is flawed if this is
the case) , you definitely can run into the limit of division precision.
However, the solution is pretty simple:
The moment you assign a Sort Index value that differs from its neighbour
by less than, say, 1x10^-8 (that's still many bits away from the
limit), then run (or at least flag/schedule for) your Sort-Index
re-balancing operation.
The fact that a normal double precision float is only 64 bits long is
never a reason to panic and doesn't invalidate a solution, though it
does mean you need to pay attention.
Also worthy to note, this solution is only really great if you have an
insanely big dataset or insert loads of entries at a time and so want to
defer a more expensive sort re-jig till later. If you only insert one
new thing now and again on a medium sized db, then just rejig the Sort
indexer immediately.
What Jens' point does illustrate is: This solution *must* be accompanied
by some Sort-Index re-jigging algorithm.
You have however a lot of freedom in choosing the frequency and scope of it.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
