0

How are floating point values in JSON data columns rounded in MySQL (5.7)?

I am having trouble finding a good resource to know how to solve my issue.

Here's what happens:

CREATE TABLE someTable (jdoc JSON);
INSERT INTO someTable VALUES('{"data":14970.911769838869}');

Then select the rows:

SELECT * from someTable;

I get data back with a different final digit: '{"data": 14970.911769838867}'

Any idea why this happens? Can I adjust the data in a way to prevent this or is there a rounding precision issue?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Cameron
  • 1,524
  • 11
  • 21

1 Answers1

2

Double precision floating point has about 16 decimal digits of precision. Your number has 17 digits, so it can't be represented exactly in floating point, and you get round-off error in the last digit.

See How many significant digits have floats and doubles in java?

The question is about Java, but just about everything uses the same IEEE 754 floating point format, so the answer applies pretty generally.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ah well that explains it... seeing a floating point number and storing a floating point number don't mean the same thing when the precision limit is hit. – Cameron Mar 05 '18 at 21:48