MySQL Bugs: #51304: checksum table gives different results for same data when using bit fields

Bug #51304	checksum table gives different results for same data when using bit fields
Submitted:	19 Feb 2010 2:22	Modified:	18 Jun 2010 1:50
Reporter:	Ben Krug	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0, 5.1.44	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	checksum, valgrind

Description:
checksum table gives different results for tables with bit fields, even when the tables contain the same data.  

How to repeat:
create table bit_test (a int, b bit(1)) engine=myisam;

insert into bit_test values (1,0), (2,1);

create table bit_test2 as select * from bit_test;

also copy bit_test.* files to bit_test3.* in the OS, eg, for linux,

cp bit_test.frm bit_test3.frm
cp bit_test.MYD bit_test3.MYD
cp bit_test.MYI bit_test3.MYI
chown mysql:mysql bit_test3.*

then use

checksum table bit_test extended;
checksum table bit_test2 extended;
checksum table bit_test3 extended;

here are the results for me, all different:

mysql> checksum table bit_test extended;
+---------------+-----------+
| Table         | Checksum  |
+---------------+-----------+
| test.bit_test | 872151869 | 
+---------------+-----------+
1 row in set (0.00 sec)

mysql> checksum table bit_test2 extended;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| test.bit_test2 | 3422813889 | 
+----------------+------------+
1 row in set (0.00 sec)

mysql> checksum table bit_test3 extended;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| test.bit_test3 | 1275854017 | 
+----------------+------------+
1 row in set (0.00 sec)

Suggested fix:
fix checksum for bit fields

the cause of the problem is using uninitialized variables. see valgrind output.

Attachment: bug51304_5.1.44_valgrind_errors.txt (text/plain), 2.24 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101346

3354 Ramil Kalimullin	2010-02-24
      Fix for bug#51304: checksum table gives different results 
      for same data when using bit fields
      
      Problem: checksum for BIT fields may be computed incorrectly 
      in some cases due to its storage peculiarity.
      
      Fix: convert a BIT field to a string then calculate its checksum.
     @ mysql-test/r/myisam.result
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - test result.
     @ mysql-test/t/myisam.test
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - test case.
     @ sql/sql_table.cc
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - convert BIT fields to strings calculating its checksums
        as some bits may be saved among NULL bits in the record buffer.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101772

3369 Ramil Kalimullin	2010-02-28
      Fix for bug#51304: checksum table gives different results 
      for same data when using bit fields
      
      Problem: checksum for BIT fields may be computed incorrectly 
      in some cases due to its storage peculiarity.
      
      Fix: convert a BIT field to a string then calculate its checksum.
     @ mysql-test/r/myisam.result
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - test result.
     @ mysql-test/t/myisam.test
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - test case.
     @ sql/sql_table.cc
        Fix for bug#51304: checksum table gives different results 
        for same data when using bit fields
          - convert BIT fields to strings calculating its checksums
        as some bits may be saved among NULL bits in the record buffer.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301190251-iema5x6db433m99z) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

CHECKSUM TABLE could compute the checksum for BIT columns
incorrectly. 

Setting report to Need Merge pending push into 5.1.x, Celosia.

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Noted in 5.5.4 changelog.

Setting report to Need Merge pending push into 5.1.x.

5.5.4 changelog entry was moved to 5.5.5.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:joro@sun.com-20100301084434-ytctk3ceebjvqo7a) (merge vers: 5.1.45) (pib:16)

Noted in 5.1.46 changelog.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)