Subtraction of two equal values is not zero in mysql

Question

Why Subtraction of two equal values in mysql does not equal zero?

both field type are double. See image below enter image description here

Obligatory link: [What Every Computer Scientist Should Know About Floating-Point Arithmetic](http://download.oracle.com/docs/cd/E19957-01/806-3568/ncg_goldberg.html) — Ken White, Jan 20 '14 at 05:21
@KenWhite, Obligatory upvote to obligatory link :). OP: TL;DR: You're going to have errors if you use floating point numbers, in this case the value is tiny, so it won't make much of a difference if you're using it in further arithmetic operations — Pranav Hosangadi, Jan 20 '14 at 05:35

score 3 · Answer 1 · answered Jan 20 '14 at 05:36

3

That's known as a approximate precision . This isn't an error, floating point data types are intended to work such way. They can not store data precisely. So if that matters, you should use fixed-point data types, such as DECIMAL in MySQL.

On the other hand, you can always use precision delta for comparisons for floating point, like:

SELECT 
  `foo`,
  `bar`,
  IF(ABS(`foo`-`bar`)<1E-13, 0, `foo`-`bar`) AS zero_compared
FROM
 t

as you can see, here delta is 1E-13 (normally, that will be enough)

answered Jan 20 '14 at 05:36

Alma Do

37,009
9
76
105

Actually I have to develop a inventory management software. where inventory quantities might be in 5 decimal place so that I use double. lets I have a product with 19.87 quantity in a store. when i try to transfer 19.87 quantity to another store it show not enough quantity to transfer. Now I how can I manage it. – Md. Sahadat Hossain Jan 20 '14 at 06:11
2

Use `DECIMAL` for that. Your values will be stored precisely – Alma Do Jan 20 '14 at 06:12
How can there be "a product with 19.87 quantity in a store? – Mitch Wheat Jan 20 '14 at 09:14
@MitchWheat product unit is kg or mg. So it could be 19.87 kg – Md. Sahadat Hossain Jul 23 '18 at 07:13
@ Md. Sahadat Hossain: that seems extremely unlikely – Mitch Wheat Jul 23 '18 at 07:58

score 1 · Answer 2 · edited May 23 '17 at 11:47

1

This problem is due to floating point precision and calculations on them. You can also refer this Issue for clarity on your problem:

MySQL floating point comparison issues

edited May 23 '17 at 11:47

Community

1
1

answered Jan 20 '14 at 05:45

Aman Aggarwal

17,619
9
53
81

Subtraction of two equal values is not zero in mysql

2 Answers2

Linked