On 5/27/19 11:36 AM, Jose Isaias Cabrera wrote: > James K. Lowden, on Sunday, May 26, 2019 04:51 PM, wrote... >> On Fri, 24 May 2019 13:10:49 +0000 >> Jose Isaias Cabrera <jic...@outlook.com> wrote: >> >>>> Consider these two queries: >>>> >>>> SELECT round(3.255,2); >>>> SELECT round(3.2549999999999998,2); >>>> >>>> Do you expect them to give different answers? >>> >>> 3.26 >>> 3.25 >> >> sqlite> SELECT cast(100 * (0.005 + 3.2549999999999998) as >> ...> integer)/100.0; >> 3.26 > > Ok, I think it happens even before the casting. This should be, > 3.2599999999999998, and yet, it's 3.26. > > 11:23:07.55>sqlite3 > SQLite version 3.28.0 2019-04-16 19:49:53 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> SELECT 0.005 + 3.2549999999999998; > 3.26 > > I even tried casting it as real, > > sqlite> SELECT cast(0.005 + 3.2549999999999998 as real); > 3.26 > sqlite> SELECT cast(3.2549999999999998 + 0.005 as real); > 3.26 > sqlite> SELECT cast((3.2549999999999998 + 0.005) as real); > 3.26 > sqlite>
One thing to watch out when using floating point numbers is that none of the mentioned numbers in this example are exactly what was typed, so everything has internal rounding adjustments to them, and even the output has been rounded and doesn't express the exact number that was the result (There is no value 3.26 as an IEEE floating point number). IEEE floating point can only represent numbers that can we written as a integer times a integer power of 2. Most decimal numbers can not be, due to the factor of 5 in the decimal base of 10. Only if the decimal part of the number is a multiple of 5 to the power of the number of decimal digits is the number possibly representable (there is still a precision limit). A quick test for this is that all floating point values that are exactly representable are either: Integer, or numbers that there decimal ends if 5 If they have more than 1 decimal digit, then they end in 25 or 75 (or 50) if they have more than 2 decimal digits, then they end in 125, 375, 626, 875 (or 500, 250, 750) You can extend these patterns to more digits, but these flag over 99% of decimals as not having an exact representation, so tend to be good enough. The ultimate answer is that when dealing with floating point, you REALLY need to KNOW what you are doing or you can easily get answer that seem wrong (but actually follow the rules). This is why many business application and the supporting languages for them adopted a 'Decimal' number that stores numbers with decimal fractions (base 10), to get around the fact that as people we are used to thinking in numbers base 10. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
