0

As we all know some rational numbers have errors when represented as floating point as witnessed here :

SELECT SUM( TEST ) 
FROM (

SELECT @N := @N +1 AS rownumber, 1/3 AS TEST
FROM INFORMATION_SCHEMA.COLUMNS, (

SELECT @N :=0
)dummyRowNums
LIMIT 3000
) AS test

I have extensive use of rational numbers arithmetics in my system and im looking for an approach to make it as precise as possible, is there any out of the box solution to this?

Its not a duplicate of this SO question as I'm asking how to do rational arithmetic right on mysql, while there they discuss why rational arithmetic is broken.

Nate-Wilkins
  • 5,364
  • 4
  • 46
  • 61
Ofek Ron
  • 8,354
  • 13
  • 55
  • 103
  • You might find [this answer](https://stackoverflow.com/a/12977067) of mine helpful in understanding why it's impossible for *any* method of calculation that's guaranteed to terminate to "do rational arithmetic right" for arbitrary values: values must be rounded to something finite, which depends on the numeral system used (something that is usually a pretty arbitrary choice). Note that this even applies to performing calculations in your head. – eggyal Jan 02 '18 at 09:10
  • You can use normal integer values if you multiply all your values by a given factor. As an example you can store the "cent" value `456` as an integer instead of the "dollar" value `4.56` in a float type column. Or you can use [Fixed-Point types](https://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html) to store your rational numbers. And at last resort, you can save the rational number as two integer values for `p/q` and do all the mathematical calculations by yourself. – Progman Jan 02 '18 at 11:10

0 Answers0