Floating point values are represented as <n bits of mantissa> * 2 ^^ <m bits of exponent>
The egde cases are inserting in sorted order. Descending: The first row is tagged with 1.0 Each new first row is tagged with 1/2 the previous. This will either lose 1 bit of mantissa or decrement the exponent. This means you will run out of values before reaching n + 2 ^^ (m-1) Ascending: The rows are assigned consecutive integers. This means you will run out of values before reaching 2 ^^ n (because then the smallest increment becomes 2) With n=53 and m=11 this gives you about 1000 inserts in descending order, which at a rate of 3/sec gives you all of 333 seconds (about 5 1/2 Minutes) between renumbering runs. Of course you could implement a special number format with n=m=32 for 2^^31 inserts, which at a rate of 3/sec gives you a mere 22 years between renumbering runs. You would need to write a user defined functions to manipulate and compare the values though. -----Ursprüngliche Nachricht----- Von: Scott Robison [mailto:sc...@casaderobison.com] Gesendet: Mittwoch, 24. September 2014 18:58 An: rsm...@rsweb.co.za; General Discussion of SQLite Database Betreff: Re: [sqlite] Division accuracy On Wed, Sep 24, 2014 at 10:49 AM, RSmith <rsm...@rsweb.co.za> wrote: > I'm trying to find what the limit is for dividing in terms of accuracy. > > Basically I have one program that inserts values to a table and > determine sort order using one standard trick that has a REAL column > named "SortOrder" which gets the value Highest_previous_value+1 if an > insert happens with something that needs to be sorted at the end of the table. > > For any other position, the SortOrder gets assigned the value: > ((Prev.Sortorder + Next.Sortorder) / 2) > {snipped} A quick bit of test code shows me that after 53 iterations you'll run out of precision, which makes sense because there are 53 mantissa bits in a normalized double including the implicit leading 1 bit). My quick & dirty test code which may be useful. #include <stdio.h> int main() { double lo = 1.0; double hi = 2.0; int count = 0; while (lo != hi) { double mid = (lo + hi) / 2.0; printf("%d %f\n", ++count, mid); lo = mid; } return 0; } -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
