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:
None 
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
Description:
When a MyISAM table is created with ROW_FORMAT=FIXED CHECKSUM=1,
CHECKSUM TABLE QUICK and CHECKSUM TABLE EXTENDED return different values,
which shouldn't be (normally the difference between QUICK and EXTENDED is just that QUICK is faster, but same value).

How to repeat:
Testcase:
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
Guilhem,

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