When you use a "select" from the shell to output a value converted to text, it outputs the value "doctored up" (coddled) for display to humans rather than display the true (as in actual) floating point value. This is because "most people" do not understand how computers (binary floating point in particular) work and want "pretty" output that looks like what they typed in rather than the "true and accurate" representation.
This behaviour (described above) is a bug (in my opinion) because it encourages people to not understand what it is they are doing, and lay blame for their misunderstanding at some door other than their own. However, it would seem that many (most) user interfaces are "buggy" in that they prefer to molly-coddle rather than be honest and truthful, thus encouraging by its own (rather unfortunate) feedback loop perpetual ignorance by those using the bug ridden products (which is almost everything that uses binary floating point). Those interested in truth and accuracy realize that the entire binary floating point system is an approximation of base-10 and that (like everything else) should only be coddled at first input and last output, and not coddled anywhere in between. Intermediate coddling should never be used for any purpose. That said, however, the purpose of .DUMP is not to present information for the coddling of the user. It is for the purpose of generating SQL which, when imported back in to an "empty" database, will result in *exactly* what was in the database which was dumped. This means that the EXACT binary floating point value must be dumped and reloaded, not the coddled user ignorantificated version presented by other interfaces, since it is possible for the coddling to display values which, when reloaded, do *not* result in the same binary value as that which existed before the coddling operation. That is, depending on the method used to coddle <X>, the uncoddling my produce a different value of <X> that that which originally existed, even though it is possible that the new value of <X> may just happen to also coddle to the same coddled display as the actual true value of <X> before the "round tripping" through the coddling functions. This "coddling error" may multiply each time the values are successively coddled and uncoddled until the resulting value of <X> no longer coddles to the same "apparent value" as the original <X>. The "standards" try to minimize the possibility of such errors, however, they still occur and cannot be prevented except by careful use of correctly designed numerical methods. This is the nature of binary floating point and there have been many pages (probably in the hundreds of thousands or more) written over the last couple of millenia describing the difficulties of, and how to deal with, the problems created by the limited human cognition of arithmetic quantities in various bases, and more lately, on the peculiarities in particular of "binary floating point" representation of base-10 numbers and calculations with them. Most of these issues had been addressed by the last quarter of the last century, however, there continues to be new people suddenly rediscovering that which was old and ancient news and making much ado out of it (take for example the latest so called meltdown and spectre CPU flaws that were recently "rediscovered" but were known since the 1950's and simply ignored by those hardware manufacturers and software developers affected by them in the here and now). The long and the short of it is that the output of .DUMP is correct. It contains the true and accurate binary floating point representation into base-10 and that particular representation does in fact exactly "round trip" through SQLite3 accurately, maintaining 100% true internal binary representation of the values that are stored in the database. In fact, those representations round trip through almost all other binary-floating point representation converters with 100% binary accuracy. And this is the goal of the .dump command -- it is for preservation of exact values, not for human consumption. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Iulian Onofrei >Sent: Monday, 22 January, 2018 05:01 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] .DUMP displays floats differently from SELECT > >Hi, > >This is clearly a bug, as it outputs incorrect and different output >from the >previous versions. > >I have a "REAL" column with float values having up to 2 decimals, and >using >".dump" with the latest version incorrectly converts them like this: > >"0.0" -> "0.0" >"0.05" -> "0.050000000000000002775" >"0.06" -> "0.059999999999999997779" >"0.07" -> "0.070000000000000006661" >"0.08" -> "0.080000000000000001665" >"0.09" -> "0.089999999999999996669" >"0.1" -> "0.10000000000000000555" >"0.11" -> "0.11000000000000000055" >"0.12" -> "0.11999999999999999555" >"0.13" -> "0.13000000000000000444" >"0.15" -> "0.14999999999999999444" >"0.16" -> "0.16000000000000000333" >"0.17" -> "0.17000000000000001221" >"0.18" -> "0.17999999999999999333" >"0.19" -> "0.19000000000000000222" >"0.21" -> "0.20999999999999999222" >"0.22" -> "0.22000000000000000111" >"0.23" -> "0.23000000000000000999" >"0.24" -> "0.23999999999999999111" >"0.25" -> "0.25" >"0.26" -> "0.26000000000000000888" >"0.27" -> "0.27000000000000001776" >"0.28" -> "0.28000000000000002664" >"0.29" -> "0.28999999999999998001" >"0.3" -> "0.29999999999999998889" >"0.32" -> "0.32000000000000000666" >"0.33" -> "0.33000000000000001554" >"0.37" -> "0.36999999999999999555" >"0.38" -> "0.38000000000000000444" >"0.4" -> "0.4000000000000000222" >"0.41" -> "0.40999999999999997557" >"0.43" -> "0.42999999999999999333" >"0.44" -> "0.44000000000000000222" >"0.45" -> "0.4500000000000000111" >"0.46" -> "0.46000000000000001998" >"0.49" -> "0.48999999999999999111" >"0.5" -> "0.5" >"0.51" -> "0.51000000000000000888" >"0.52" -> "0.52000000000000001776" >"0.54" -> "0.54000000000000003552" >"0.56" -> "0.56000000000000005329" >"0.57" -> "0.56999999999999995115" >"0.58" -> "0.57999999999999996003" >"0.6" -> "0.59999999999999997779" >"0.61" -> "0.60999999999999998667" >"0.65" -> "0.6500000000000000222" >"0.67" -> "0.67000000000000003996" >"0.7" -> "0.69999999999999995559" >"0.73" -> "0.72999999999999998223" >"0.75" -> "0.75" > >I rely on dumps to track changes to some databases, so this breaks it >completely, and I currently had to pin sqlite to an older version >which >isn't desired. > >Thank you, >iulianOnofrei > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
