SQLite Forum

ORDER BY not working for a specific DB/table
> the nearest representable value of 31.0 is 30.999999999999999999.

This is pretty misleading. 31.0 can be trivially represented with a binary decimal like:


ie. 2⁴ + 2³ + 2² + 2¹ + 2⁰

Which has a clear and precise encoding under IEEE754. At double precision, that is 0x403f000000000000.

Of course that is the same encoding that you would get for 
30.999999999999999999, and to understand why I find it instructive to look at how neighbouring bit patterns are interpreted (decimal approximations courtesy of java.lang.Double):

0x403effffffffffff = 30.999999999999996
0x403f000000000000 = 31.0
0x403f000000000001 = 31.000000000000004

ie. each distinct floating point value can be thought of as representing a region on the line of real numbers, and any real number is indistingiushable from other numbers in the same region.

I'd wager the majority of floating point engines display 0x403f000000000000 as 31.0 so it's surprising that SQLite generates 30.999999999999999999, but it's not incorrect.