Ingres multiplication gives wrong result

Question

I have an Ingres table with following columns

from_date ingresdate
to_date ingresdate
model_amt money

The dates can reflect a period of any number of days, and the model_amt is always a weekly figure. I need to work out the the total model_amt for the period

To do this I need to know how many days are covered by the period, and then divide model_amt by 7, and multiply it by the number of days

however, I am getting incorrect results using the code below

select model_amt, date_part('day',b.to_date - b.from_date), 
model_amt / 7 * int4( (date_part('day',b.to_date - b.from_date)) )
from table

For example, where model_amt = 88.82 and the period is for 2 weeks, I get the following output

+-------------------------------------------------------+
¦model_amt           ¦col2         ¦col3                ¦
+--------------------+-------------+--------------------¦
¦              #88.82¦           14¦             #177.66¦
+-------------------------------------------------------+

But 88.82 / 7 * 14 = 177.64, not 177.66?

Any ideas what is going on? The same issue happens regardless of whether I include the int4 function around the date_part.

* Update 15:28 *

The solution was to add a float8 function around the model_amt

float8(model_amt)/ 7 * interval('days', to_date - from_date)

Thanks for the responses.

I know nothing about Ingres SQL, but my guess is that you are just seeing the effects of floating point rounding error. — Tim Biegeleisen, Aug 29 '18 at 14:12
I get the same problem if I use the interval function instead of the date_part function. How do I get around this? — Ben Hamilton, Aug 29 '18 at 14:16
I think this is what's happening: The actual `model_amt` value is not `88.82`, but rather there are more digits past the hundredth place `88.82xxxx`, such that if you were to actually include them in your calculation, you would end up getting `177.66`. So, nothing is wrong, but your interpretation/expectation is incorrect. — Tim Biegeleisen, Aug 29 '18 at 14:20

score 1 · Accepted Answer · answered Aug 29 '18 at 14:15

In computers, floating point numbers are notoriously inaccurate. You can multiply do all kinds of basic mathematics calculations on floating point numbers and they'll be off by a few decimals.

Some information can be found here; but its very googleable :). http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm

Generally to avoid inaccuracies, you need to use a language specific feature (e.g. BigDecimal in Java) to "perfectly" store the decimals. Alternatively, you can represent decimals as separate integers (e.g. main number is one integer and the decimal is another integer) and combine them later.

So, I suspect this is just ingres showing the normal floating point inaccuracies and that there are known workarounds for it in that database.

Update

Here's a support article from Actian specifically about ingres floating point issues which seems useful: https://communities.actian.com/s/article/Floating-Point-Numbers-Causes-of-Imprecision.

Ingres multiplication gives wrong result

1 Answers1