MySQL floating point rounding error

Internally, what is going on with the floating point representation on the right to get false?

`mysql> SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0; +-----------------------+-----------------------------+ | 3.1415 + 0.9585 = 4.1 | 3.1415E0 + 0.9585E0 = 4.1E0 | +-----------------------+-----------------------------+ | 1 | 0 | +-----------------------+-----------------------------+ `

edit Conclusion: Don't use Float or Double for precision math. Use Decimal.

First of all, you should never use = with floating points, as you simply cannot predict if no rounding errors occur. Always use < or >, dependent on what you want to achieve.

Second: Nothing is wrong with either formula. Internally, both variants get encoded in their variables to the nearest possible binary representation, which slightly differs from the value you set.

One digit before the decimal point is standard, so all following digits are used. On the right side, you specifically define where the comma occurs, thus "wasting" a certain part at the end of the binary representation of your variable - which leads to rounding errors (in this specific case).

floating points like `FLOAT` or `DOUBLE` are always inprecise because they're an approximity, not an exact value, you should not use them for calculations. Use the `DECIMAL` data type instead.

MySQL, just as almost any computer implementation that uses floating point arithmetic, employs the IEEE-754 standard. This is binary floating point.

Just as it is impossible to write 1/3 in decimal, it's impossible to write infinitely many digits in binary floating point (given that computers are restricted in time and space). A float uses 32 bits, a double uses 64 bits, mathematically, that's very little space for infinite many fractions.

The Wikipedia article above is a good read about floating point. In general, when dealing with computer match, it is imperative to know a bit how computers use limited storage for unlimited fractions.

Exact decimals also exist, but are limited to a fixed range (i.e. no high exponent). In MySQL you can use `DECIMAL` for fixed point numbers.

In this, it was lucky that the first one compared exactly:

```SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0; ```

Test floating points for near equality in this way:

```mysql> SELECT abs(3.1415 + 0.9585 - 4.1) < 0.0001, abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001; +-------------------------------------+-------------------------------------------+ | abs(3.1415 + 0.9585 - 4.1) < 0.0001 | abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001 | +-------------------------------------+-------------------------------------------+ | 1 | 1 | +-------------------------------------+-------------------------------------------+ 1 row in set (0.00 sec) ```

Choose the difference (where I've used 0.0001) appropriately to the values involved.

Category:mysql Views:0 Time:2009-11-19
Tags: mysql

Related post

• What is a simple example of floating point/rounding error? 2008-10-30

I've heard of "error" when using floating point variables. Now I'm trying to solve this puzzle and I think I'm getting some rounding/floating point error. So I'm finally going to figure out the basics of floating point error. What is a simple example

• Reduce the effect of float number (rounding) error 2011-06-20

We are implementing some geometric algorithms but found the effect of float number calculation error is big. Is there any guide lines to reduce this kind of effects? The algorithms contains many parts and is very complex. One of the example is to cal

• Floating point rounding error php -how can I make sure it works correctly? 2014-04-23

I have the following function that determines if I sale is fully paid for. I don't remember why I did it this way, but it has been working so far and I don't remember why I had to do it this way. function _payments_cover_total() { //get_payments is a

• floating point rounding errors 2011-02-21

The output is x=1000300 y=1000000, z=1000300 I can understand how I got x and z but c's y's output makes no sense. #include <stdio.h> int main() { int i=0; float a = 100; a = a*a*a*a*a; float c = 3; float x = 1000000*c + a; float y = a; float z

• OpenGL: How do I avoid rounding errors when specifying UV co-ordinates 2010-02-27

I'm writing a 2D game using OpenGL. When I want to blit part of a texture as a sprite I use glTexCoord2f(u, v) to specify the UV co-ordinates, with u and v calculated like this: GLfloat u = (GLfloat)xpos_in_texture/(GLfloat)width_of_texture; GLfloat

• How do you handle floating point rounding problems? 2010-05-03

Seeing this post about floating point errors on slashdot, I got curious what other kind of solutions exist to deal with such kind of floating point rounding errors. So what was the floating point bug you learned most from, and what did you learn from

• Solving floating-point rounding issues C++ 2011-04-11

I develop a scientific application (simulation of chromosomes moving in a cell nucleus). The chromosomes are divided in small fragments that rotate around a random axis using 4x4 rotation matrices. The problem is that the simulation performs hundreds

• Does this === not work b/c of floating point rounding inaccuracies? 2012-04-25

Let's say I have this snippet. var age = prompt('what is your age?'); if (age === 30) { alert('your age is 30'); } When I entered 30 in the prompt, the if statement doesn't trigger. I suspect it has to do with floating point rounding errors, but don'

• Python rounding error with float numbers 2011-05-13

I don't know if this is an obvious bug, but while running a Python script for varying the parameters of a simulation, I realized the results with delta = 0.29 and delta = 0.58 were missing. On investigation, I noticed that the following Python code:

• Ruby Float Round Error Bug? 2011-10-13

I am getting the following rounding error when I try to Unit test the class below: class TypeTotal attr_reader :cr_amount, :dr_amount, :cr_count, :dr_count def initialize() @cr_amount=Float(0); @dr_amount=Float(0) @cr_count=0; @dr_count= 0 end def in

• IEEE 754 floating point arithmetic rounding error in c# and javascript 2011-12-14

I just read a book about javascript. The author mentioned a floating point arithmetic rounding error in the IEEE 754 standard. For example adding 0.1 and 0.2 yields 0.30000000000000004 instead of 0.3. so (0.1 + 0.2) == 0.3 returns false. I also repro

• How do I do floating point rounding with a bias (always round up or down)? 2009-03-26

I want to round floats with a bias, either always down or always up. There is a specific point in the code where I need this, the rest of the program should round to the nearest value as usual. For example, I want to round to the nearest multiple of

• .NET rounding error in ToString("f2") 2010-01-18

Hello I have this code in C#: float n = 2.99499989f; MessageBox.Show("n = " + n.ToString("f2", CultureInfo.InvariantCulture)); And this code in C++: float n = 2.99499989f; printf("n = %.2f", n); First one outputs 3.00. Second one outputs 2.99. I have

• Resolve Rounding Errors while Resizing Controls on Winform 2010-02-06

I have a requirement to resize all the controls on a form when my form resizes. Unfortunately, due to some issues I can not use any container control or make use of anchor/dock property. I need to do everything using code. I am able to resize the con

• Common strategies to deal with rounding errors in currency-intensive soft? 2010-05-18

What is your advice on: compensation of accumulated error in bulk math operations on collections of Money objects. How is this implemented in your production code for your locale? theory behind rounding in accountancy. any literature on topic. I curr

• Multiplying a double value by 100.0 introduces rounding errors? 2010-07-08

This is what I am doing, which works 99.999% of the time: ((int)(customerBatch.Amount * 100.0)).ToString() The Amount value is a double. I am trying to write the value out in pennies to a text file for transport to a server for processing. The Amount

• How to avoid rounding errors in Android app devlopment? 2010-07-12

I'm doing what seems like simple math in an android app, however I keep getting massive rounding errors. For example. Float.toString(0.4*0.4*12.5); I would expect to get 2 as the result of the above equation. However I get 2.0000002 --------------Sol

• Color picking on android - glReadPixels rounding errors 2010-09-28

I am using color picking in opengl es on android and i am calculating a color key to compare it to the values i get from glReadPixels: ByteBuffer PixelBuffer = ByteBuffer.allocateDirect(4); PixelBuffer.order(ByteOrder.nativeOrder()); gl.glReadPixels(

• MATLAB script to generate reports of rounding errors in algorithms 2010-11-16

I am interested in use or created an script to get error rounding reports in algorithms. I hope the script or something similar is already done... I think this would be usefull for digital electronic system design because sometimes it´s neccesary to