Search code examples
sqlingres

Ingres multiplication gives wrong result

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.

Solution

  • 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.