MySQL Bugs: #30041: CHECKSUM TABLE reports different results for tables with FLOAT/DOUBLE columns

Bug #30041	CHECKSUM TABLE reports different results for tables with FLOAT/DOUBLE columns
Submitted:	25 Jul 2007 14:16	Modified:	4 Feb 2008 19:42
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.44	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	bfsm_2007_08_02

Description:
Using CHECKSUM TABLE ... EXTENDED across replication set ups with statement based replication causes unpredictable results, with the checksum value created returning differently for otherwise exact copies of tables. 

For instance, on 'replication server one', with some debug code, we can see output such as:

OTHER DCF99F42 len=4

crc = 1694539836

And on 'replication server two' for the same row, we see:

OTHER DBF99F42 len=4

crc = 1677762620

F4FD1F42 versus F6FD1F42 corresponding to 39.998008728027344 vs 39.99800109863281 within IEEE-754 format. 

As is seen, this affects the checksum of the column (and hence row). The actual value displayed on both servers for the above row is:

mysql> select amount from t1 limit 179,1;
+--------+
| amount |
+--------+
| 39.998 |
+--------+
1 row in set (0.00 sec)

The tables also dump to a file from mysqldump that report no diff's across the replication servers, so the data is otherwise 'precise'.

How to repeat:
Only reproduced in debugging, see follow up discussion.

Suggested fix:
For FLOAT/DOUBLE columns, truncate/round the values to their default or configured precision, and then take the CRC check of the column value, instead of taking the checksum on the full float precision.

Just to comment, if CHECKSUM TABLE reports tables as different, they *are* different, and there could be queries that will return different results on these tables. This behavior is of course intentional, and it's why CHECKSUM TABLE was created in the first place - to be able to determine whether two tables are identical or different.

These tables are really different, and it's right that the checksum shows
this difference. So it's not a bug.
Though the problem is that these tables have to be equal.
Now we plan to solve it in 5.2.

I expect this to be fixed by the patch for wl#2934 (already pushed)

Fixed in 6.0 whith the patch to wl#2934.

The problem is that tables that are same from the 'SQL' point of view can differ on 'binary' level as same numbers can be differently represented in DOUBLE field. So the same tables can have different checksums. It is a bug, just not a bug in the checksum calculation but in the way we handle doubles.
That was fixed in 6.0 with the special functions to avoid it.

Noted in 6.0.5 changelog.

For tables with FLOAT or DOUBLE columns, CHECKSUM TABLE could report
different results on master and slave servers.