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:[email protected]] Im Auftrag von Jose Isaias Cabrera Gesendet: Dienstag, 28. Mai 2019 05:38 An: SQLite mailing list <[email protected]> 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 <[email protected]> >wrote: >> Ok, I think it happens even before the casting. This should be, >>3.2599999999999998, and yet, it's 3.26. >> sqlite> SELECT 0.005 + 3.2549999999999998; >> 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.2549999999999998, 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 [email protected] 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. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
