"Office Space"... I'm losing all those pennies
(1) By rbucker on 2021-05-02 01:31:15 [link]
I'm building a financial application and for some reason I'm losing pennies. I can remember the days when I wrote a lot of C code and had to give up using floating point because of the conversion(s). But I would expect different from a DB. SAMPLE: ``` sqlite> select sum(cast('16.15' as numeric))*100; 1615.0 sqlite> select cast(sum(cast('16.15' as numeric))*100 as int); 1614 sqlite> select sum(cast(cast('16.15' as numeric)*100 as int)); 1614 sqlite> select printf('%d', sum(cast('16.15' as numeric))*100); 1614 sqlite> select printf('%.0f', sum(cast('16.15' as numeric))*100); 1615 ```
(2) By Richard Damon (RichardDamon) on 2021-05-02 02:00:14 in reply to 1 [link]
Why would you expect differently from a Database being told to use floating-point numbers to be different than a C program using floating-point numbers. SQLite numeric is still floating-point, not some fixed point decimal notation.
(3) By Keith Medcalf (kmedcalf) on 2021-05-02 02:19:23 in reply to 1 [link]
The IEEE-754 floating point value 16.15 is 16.149999999999998578914528. When you "cast to int" you discard the fraction. 16.15*100 is 1614.9999999999997726263245 When you cast this value to integer (discard the fraction) the result is 1614 The %d format specifier to printf formats a float as an integer by casting to integer. The %.0f format specifier "pretty prints for hooman consumption" the value to 0 decimal places which then prints 1615. This is simply how floating point arithmetic works. If you wish to ROUND the value to the nearest integer, then you should simply do so: ``` sqlite> select round(sum(cast('16.15' as numeric))*100, 0); ┌─────────────────────────────────────────────┐ │ round(sum(cast('16.15' as numeric))*100, 0) │ ├─────────────────────────────────────────────┤ │ 1615.0 │ └─────────────────────────────────────────────┘ ```
(4) By Keith Medcalf (kmedcalf) on 2021-05-02 02:27:37 in reply to 1
An IEEE double on a computer than performs IEEE compliant arithmetic can handle numbers with an absolute dollars and cents value up to about 9,999,999,999.99 with no loss of precision (that is one penny shy of 10 american billions or 10 thousand millions for every one else) -- with 2.5 guard digits -- scale appropriately for your guard requirements.
(5) By rbucker on 2021-05-02 02:43:49 in reply to 4 [link]
Kieth, your previous post about using `round()` makes perfect sense even though I've never had to do that sort of gymnastics before with other DBs. Sadly I'm not able to correlate your second note. But thanks.
(6) By Keith Medcalf (kmedcalf) on 2021-05-02 04:37:35 in reply to 5 [link]
> though I've never had to do that sort of gymnastics before with other DBs Then those other DBs are not doing IEEE compliant computations using IEEE-754 Floating Point numbers. You can do decimal arithmetic using the SQLite3 decimal extension <https://sqlite.org/floatingpoint.html> Section 2.2 ``` sqlite> select decimal_mul('16.15', '100'); ┌─────────────────────────────┐ │ decimal_mul('16.15', '100') │ ├─────────────────────────────┤ │ 1615 │ └─────────────────────────────┘ ``` or by using a decimal extension (such as LifePillar's decimal extension) ``` sqlite> .load decimal sqlite> select decStr(decMul(dec('16.15') , dec('100'))); ┌───────────────────────────────────────────┐ │ decStr(decMul(dec('16.15'), dec('100'))) │ ├───────────────────────────────────────────┤ │ 1615 │ └───────────────────────────────────────────┘ ```
(7) By anonymous on 2021-05-02 11:40:30 in reply to 6 [link]
>or by using a decimal extension (such as LifePillar's decimal extension) Could you provide a link?
(8) By Simon Slavin (slavin) on 2021-05-02 13:34:47 in reply to 1 [link]
It is impossible to store the number 16.15 precisely in binary. However you handle it, you're going to get truncation and/or rounding problems. If you don't know why that is, take a course in computer science. If you're building a financial application, amounts of money should be stored as integers. Your value of $16.15 (or Euros, or Pounds, or whatever it is) should be stored as 1615 in column with INTEGER affinity, and your C code should handle all those values as integer values. This will force you to handle truncation and rounding in your C code, without any ambiguity ("losing pennies").
(9) By Warren Young (wyoung) on 2021-05-02 18:44:41 in reply to 7 [link]
(10) By Holger J (holgerj) on 2021-05-04 06:37:28 in reply to 2 [link]
That's exactly the point. The SQL standard is very clear about the data types NUMERIC and DECIMAL (which are identical) to do decimal arithmetic and are painfully missed in SQLite.
(11.1) By Scott Robison (casaderobison) on 2021-05-04 10:09:07 edited from 11.0 in reply to 10 [link]
That is part of the "lite" in SQLite. In the beginning was the C provided floating-point functionality that was the first-order approximation for decimal mode. It was lite to use existing functionality rather than implement its own decimal numeric processing. SQLite has never claimed to be conformant to any particular SQL standard. Few database systems fully conform to any standard. Fortunately, there are solutions to the missing functionality for those who require it, though it is still in a non-standard format (using functions instead of operators, for example).
(12) By rbucker on 2021-05-04 10:51:14 in reply to 8 [link]
I thought I knew how the data was stored.... as text with some affinity.... at least in my actual use-case everything is text on purpose. The closest thing to the real solution would have been the decimal math, however, in the actual project code I'm writing it in golang and the decimal extensions are not immediately available. thanks.
(13) By Warren Young (wyoung) on 2021-05-04 10:59:38 in reply to 12 [link]
[Run-time loadable extensions are a thing.](https://www.sqlite.org/loadext.html)
(14) By Andreas Kupries (andreas-kupries) on 2021-05-04 11:53:24 in reply to 1 [link]
Is it possible to store the values as pennies ? That would be integer numbers. The math in your system would need to be adapted, i.e fixed point math, instead of floating point. Maybe store even integers representing 1/1000 of a penny or some such. Still fixed point math, with some guard digits before you have to round. I.e. 1.23 [USD] would be 123 [Penny]. Or 123000 [1/1000th penny].
(15.1) By rbucker on 2021-05-05 00:55:22 edited from 15.0 in reply to 14 [link]
correct. The issue was that converting it to pennies was the problem... SOLVED. thansks UPDATE: I ended up going to github and pulling the decimal.c file then adding it as an extension in my golang project.
(16) By tom (younique) on 2021-05-06 20:52:58 in reply to 2 [link]
> Why would you expect differently from a Database being told to use floating-point numbers to be different than a C program using floating-point numbers. Ok, but shouldn't other RDBMSs do the same? However, in MariaDB 10.5.8, I get: `SELECT SUM(CAST('16.15' AS FLOAT))*100; // 1614.9999618530273` `SELECT CAST(SUM(CAST('16.15' AS FLOAT))*100 AS INT); // 1615` `SELECT SUM(CAST(CAST('16.15' AS FLOAT)*100 AS INT)); // 1615` I clearly understand the pros and cons of floating-point arithmetic. But what puzzle me each time is that different software returns different results for the same thing??? My confidence in floating-point arithmetic would be much greater if it was consistent everywhere.
(17) By Scott Robison (casaderobison) on 2021-05-06 23:25:40 in reply to 16 [link]
I suspect the discrepancy has to do with the digits of precision that float has. Float has 24 bits of precision (23 for subnormal) which amounts to 7.22 decimal digits of precision. MariaDB is undoubtedly going the extra mile and performing a rounding step internally before the final conversion to integer. This might be done via a IEEE 754 rounding mode, or it could be something they do in a final analysis. Where MariaDB is a dedicated process that knows its only reason for existence is to work with a database, it is free to make all the decisions about how to utilize the floating point environment. SQLite is but one part of a larger process, and defers those decisions to the process. Depending on what platform you are on, it might be possible to set an IEEE 754 rounding mode that would give you the same rounding mode. Alternatively, you could do something like: SELECT CAST(CAST('16.15' AS FLOAT)*100+0.5 AS INT) Or use ROUND as was suggested previously. The long story short is: If all variables are the same, you will get the same answer on both platforms. If anything varies, then you will not. What platform are you using? Operating system, compiler, runtime library, and versions.