double precision column returning different values after upgrade to 5.5

I have several double precision columns in a table and a select on a server running 5.1.63 would return a number like 12.0. I upgraded to the latest Percona 5.5 (5.5.27-28.0 at the time), same hardware, and the select now returns 12.00000001. Why would this be the case? I understand floating point cannot represent certain numbers, but would expect the stored value to be represented the same way on both distributions.

Hi, I have tried to test the same scenario locally but I didn’t get any differences. Can you provide some more details to troubleshoot it? like, any one table description which contains double precision columns, before and after upgrade and the steps which you followed to upgrade DB from 5.1 to 5.5.

Btw: You can get more information about versions changes here. e_51_55.html#numeric-calculations

Hi Nilnandan,

It did not occur on all rows, just a handful. I’d like to isolate this to a few rows if possible, but the table is quite large. Do you have any suggestions as far as creating a table in an isolated schema with just the rows that caused me issues? Then I can upload a sample db if able to reproduce.

The upgrade was nothing more than booting the server on the latest version, no data conversion scripts were run. The underlying data did not change, and rolling back to the older version yields “correct” results.

I did see the linked guide before posting, but unless I misunderstood it, had to do with string to fp conversion. Since I was working with previously stored data, I thought it did not apply.

Just posting a followup from a year ago. This was actually a Java driver “issue”. I shouldn’t say issue, but a better understanding of what the driver did under the covers. In the later versions, it deals with strings, not raw values unless you enable that option. Since the fp lib changed in 5.5, the string representation of a double in 5.1 and 5.5 could be different.