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 +
>> Jose Isaias Cabrera wrote:
>>
Consider these two queries:
SELECT round(3.255,2);
SELECT round(3.2548,2);
Do you expect them to give different answers?
>>>
>>> 3.26
>>> 3.25
>>
>> sqlite> SELECT cast(100 * (0.005 + 3.2548) as
>> ...> integer)/100.0;
>> 3.26
>
> Ok, I think it happens even before the casting. This should be,
> 3.2598, 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.2548;
> 3.26
>
> I even tried casting it as real,
>
> sqlite> SELECT cast(0.005 + 3.2548 as real);
> 3.26
> sqlite> SELECT cast(3.2548 + 0.005 as real);
> 3.26
> sqlite> SELECT cast((3.2548 + 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
