help! decimal field multiplication error?
(1) By eiping on 2021-08-12 09:03:56 [link] [source]
CREATE TABLE test (id INTEGER,qu DECIMAL(10, 2),price DECIMAL(5, 2),total DECIMAL (10, 2)); data:3 27.1 56.7 || 2 23.7 22.8 ||1 12.5 25.6 update test set total = price * qu; id 3 total display 1536.5700000000002, sql error? field definition error? new one,pls give me a hand, 3Q!
(2) By Warren Young (wyoung) on 2021-08-12 09:12:19 in reply to 1 [link] [source]
(3) By J.M. Aranda (JMAranda) on 2021-08-12 10:21:06 in reply to 1 [link] [source]
Use fixed point, using integers or strings. And you won't see the typical floating point arithmetic stuff.
(4) By Gunter Hick (gunter_hick) on 2021-08-12 10:58:56 in reply to 1 [source]
There is no DECIMAL type in SQLite, thus you are using float arithmetic operations to process "money" (which is never a good idea) instead of integers with appropriate rounding rules.
(5) By Warren Young (wyoung) on 2021-08-12 11:51:31 in reply to 4 [link] [source]
Technically true, but see section 2.2 of the doc I linked.
(6) By Keith Medcalf (kmedcalf) on 2021-08-12 22:08:55 in reply to 1 [link] [source]
The floating point value 27.1 is 27.100000000000001421085471
The floating point value 56.7 is 56.700000000000002842170943
Multiplying them gives the result: 1536.5700000000001637090463
This is the result that you obtained. It is merely "rounded" for your viewing pleasure.
SQLite3 does not have a DECIMAL datatype. IEEE compliant floating point is "computed exactly then rounded half-even in the least significant place".
DECIMAL with a specified precision, however, must be "computed exactly then rounded half-even to the specified precision".
Assuming that the IEEE specified rounding rounds beyond the first guard digit then rounding half-even to the lesser precision will obtain the correct result.
In the "olden days" DECIMAL usually meant to store the number in BCD or Packed Decimal format (in the 1940's). Nowadays, DECIMAL is usually emulated by judicious application of half-even rounding on floating point numbers.
Since computers are advanced technology and methods of computation are complicated beyond the understanding of most "computer users", there have been a lot of "old wives tales" that have popped up amongst the proletariat regarding "the evil of floating point numbers" and that "one cannot calculate accurately using floating point" to "you cannot use floating point for money". These are all untrue. Floating point works exactly as designed and it is merely lack of understanding that starts these sorts of rumours.
Using standard IEEE floating point to "compute money" by "emulating" the Packed Decimal type (DECIMAL) is a problem that has been solved for at least half-a-century.