Bug #46493 CHECKSUM TABLE EXTENDED returns different value from QUICK if ROW_FORMAT=FIXED
Submitted: 31 Jul 2009 13:06 Modified: 12 Mar 2010 12:33
Reporter: Guilhem Bichot Email Updates:
Status: In progress Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1-bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[31 Jul 2009 13:06] Guilhem Bichot
When a MyISAM table is created with ROW_FORMAT=FIXED CHECKSUM=1,
which shouldn't be (normally the difference between QUICK and EXTENDED is just that QUICK is faster, but same value).

How to repeat:
create table t1 (a int null, v varchar(100)) engine=myisam checksum=1 row_format=fixed;
insert into t1 values(null, null), (1, "hello");
checksum table t1;
checksum table t1 quick;
checksum table t1 extended;

the last checksum value is different from the two first ones.
[31 Jul 2009 13:11] Guilhem Bichot
likely the bug depends on NULL fields.
[3 Aug 2009 16:26] Sinisa Milivojevic

I suggest that QUICK skips all float and double values, as those differ from platform to platform. Then, it makes sense to have different checksums with different methods.
[6 Aug 2009 12:32] Guilhem Bichot
Hello Sinisa,
thanks for the suggestion. Though you know, this would be a behaviour change: QUICK is currently not expected to skip platform-dependent data like floats, it's expected to return the same as EXTENDED but in a quicker manner.
If I had to change CHECKSUM TABLE, I'd modify it to use a platform-independent way of incorporating floats. Like, the computation would round floats to a precision gross enough that the rounded value is platform-independent; if this is technically possible. For example if I store 1.1 in a float, it may be 1.1000000001 on some machine, 1.09999999321 on some other, but, both values rounded to, say, 3 decimals, are the same (1.1000) and could be used to compute the checksum. Just a wild idea. Anyway anything like this should be put into WL for design first, there already exists WL#5020 "improve checksum returned by CHECKSUM TABLE" which lists other known problems. Is there a bug report for the float/double checksum problem?
[6 Aug 2009 13:06] Guilhem Bichot
I don't even know if the float/double problems still exist, reading http://bugs.mysql.com/bug.php?id=30041
[15 Aug 2009 8:54] Sergei Golubchik
the "double/float problem" can not be "solved", just consider it part of http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html