Think of floating point as a knapsack problem. The decimal number you want to
represent is the height of the (cylindrical) knapsack. You have a set of
cylinders you want to stack into the knapsack. The IEEE set of cylinders has
the essential property that each cylinder is larger then the sum of all smaller
cylinders, which makes the "greedy algorithm" very efficient: select the
largest cylinder that will fit into the remaining space. The second attribute
if the IEEE set of cynlinders is that you can use only a contigous subset of at
most 48 cylinders. The third attribute of the set is that the cylinder sizes
are powers of 2, i.e. 1,2,4,8,16,... and 1/2, 1/4, 1/8, 1/16.
Lets assume that your knapsack has a height of 3.255, so the first cylinder is
height 2, leaving 1.255 to fill.
Second cylinder is 1, leaving 0.255 to fill.
Third cylinder is 1/4, leaving 0.005 to fill.
Unfortunately, the cylinders you have cannot exactly fill your specific
knapsack; the smallest cylinder of the allowed 48 cylinder subset just cannot
fit. You now have to choose to either slightly expand (round up), or slightly
compress (round down) your knapsack. This is where errors start to add up or
even multiply, because only the contents of the knapsacks involved are
considered, not the specified knapsack sizes.
Let's try another set of cylinders: the basic set is 1 2, 3 ... 9 and you are
allowed a limited number (usually 8) of expansion sets with cylinders exactly
10 times the size of the previous set. Again, the greedy algorithm work well,
as long as you remember to switch to the next smaller subset of cylinders at
each step. This is called the "decimal integer set"
With your knapsack of heigth 3.255 the algorithm terminates after the first
cylinder of height 3, leaving a lot of "compressed headroom", which is clearly
unsatisfactory.
So why not discard the top three subsets in favor of the "tenths", "hundredths"
and "thousandths" subsets? Somewhat magically, the knapsack of height 3.255 can
be filled in precisely with exactly 4 cylinders.
So instead of complaining that the IEEE cylinder set causes undue stress on
your knapsacks, please choose a more suitable set of cylinders for your
specific application.
BTW: There are still quite some knapsacks that cannot be precisely filled with
any set of cylinders whose sizes are whole numbers and/or fractions, the most
prominent being named "pi" and "e".
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jose Isaias Cabrera
Gesendet: Dienstag, 28. Mai 2019 05:38
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] round function inconsistent
Rowan Worth, on Monday, May 27, 2019 11:07 PM, wrote...
>On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera
>wrote:
>> Ok, I think it happens even before the casting. This should be,
>>3.2598, and yet, it's 3.26.
>> sqlite> SELECT 0.005 + 3.2548;
>> 3.26
>>
>
>Note that no arithmetic is required to see these symptoms:
Yes. It has definitely been a learning experience. I actually thought that
using floating point was more accurate in every way possible. :-) I am also
understood why some of the dumps that I get from a reporting system have
numbers such as 3.2548, etc., when someone actually entered a
different and shorter number all together. I am the least in knowledge of this
group, and I appreciate the lovely responses from everyone. But, I have been
wondering in the past few days: why haven't more people complaint about it?
Math is math and decimal number is what most business run? Yes, there are many
other floating point application for engineers and such, but the amount of work
in daily business activity relies on decimal numbering system. Anyway, I
understand it now.
>This is all very subtle which is why some languages/software offer
>actual decimal arithmetic. sqlite does not, but there's also nothing
>to stop you from storing eg. strings in the DB and converting to/from
>decimal representations in your application.
Thanks Rowan for the reply. Yes, I will have to manage it this way from now on.
Back to creating a few arithmetic functions to manage these types of decimal
processing. It is sad, though, because it would be easier to be able to do
this right from SQLite, instead of outside. Now, I will have to bring the
records out, do the sum of the records, round, etc., etc. Thanks. josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
