After doing dumps+dumps of random tables, it looks like it’s this issue.

@shlomi: thanks for a suitable work around.

I found that with CAST, when using a float precision of 30 or less on a double field, it works, but not 31 or more:

mysql> select @@version; SELECT
ROUND(IF(double_field BETWEEN -1 AND 1, CAST(double_field AS DECIMAL(65,30)), double_field), 30) as float_p30,
ROUND(IF(double_field BETWEEN -1 AND 1, CAST(double_field AS DECIMAL(65,30)), double_field), 31) as float_p31 from my_table where id IN (val1,val2,val3);
+————+
| @@version |
+————+
| 5.1.61-log |
+————+
1 row in set (0.00 sec)

+———————————-+————-+
| float_p30 | float_p31 |
+———————————-+————-+
| 0.000075488799999999981024841056 | 7.54888e-05 |
| 0.000090369599999999989390145338 | 9.03696e-05 |
| 0.000059410600000000003402360338 | 5.94106e-05 |
+———————————-+————-+
3 rows in set (0.00 sec)

This kind of truncation also happens on MariaDB, but in a different way – it truncates to 10 decimal places.

MariaDB [addons_allizom_org]> SELECT ROUND(IF(movers BETWEEN -1 AND 1, CAST(movers AS DECIMAL(65,30)), movers), 30) as float_p30,ROUND(IF(movers BETWEEN -1 AND 1, CAST(movers AS DECIMAL(65,30)), movers), 31) as float_p31 from addons_allizom_org.personas where id in (4313,33619,66706);
+———————————-+————–+
| float_p30 | float_p31 |
+———————————-+————–+
| 0.000075488800000000000000000000 | 0.0000754888 |
| 0.000090369599999999990000000000 | 0.0000903696 |
| 0.000059410600000000000000000000 | 0.0000594106 |
+———————————-+————–+
3 rows in set (0.00 sec)

MariaDB truncates without any warnings, which is odd.

What is needed is to FORMAT to some number of _significant_ digits, not some number of _decimal places_.

If the numbers are different on the two machines, they should be flagged as different. Converting to strings, then comparing the strings is the source of the problem.

Unfortunately, there is not a CAST AS BINARY to produce the internal representation. (A trick like that works to bypass character set issues.) Probably 99.99% of MySQL installations use hardware that uses IEEE 754, so I suggest that it is not reasonable to argue about differing representations.

FORMAT() seems to work well for small numbers (for larger numbers, which are of no interest in this problem, it adds commas to denote orders of magnitude).
It apparently doesn’t have a limit on the number of digits after the floating point (FLOAT and DOUBLE max at 30 digits when casted to DECIMAL) — but this is just silent: when FORMATting numbers with over 30 digits, result is rounded to 30 digits no matter what.
So in conclusion I don’t see the difference between the two.

Moreover, consider I’ve put this into the float-precision option, whose target in the first place is to do round ups… So yes, there’s round up, and it is intentional.

Right/wrong?