Bug #22271 data casting may affect data stored in the next column(s?)
Submitted: 12 Sep 2006 15:22 Modified: 6 Oct 2006 3:14
Reporter: Victor Tarhon-Onu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.22, 5.0 BK, 5.1 BK OS:Linux (Fedora Core 5 Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[12 Sep 2006 15:22] Victor Tarhon-Onu
Description:
It seems that in some situations, when the data needs to be truncated during the data cast process, the values in the next column (or columns, I'm not sure, but probably someone should test with data types needing more bytes to be stored) are also affected.

How to repeat:
I'll be short. Steps to reproduce:

Run the following statements at the mysql prompt:

1. create table t (f1 bit(1), f2 smallint unsigned);

2. insert into t (f1, f2) values ('1','2');

A warning will be issued on this insert, because for MySQL '1' is different than 1, so probably the character '1' will be casted to bit. Anyway, nothing happens.

3. insert into t (f2, f1) values ('2','1');

The same warning will be issued, just that in this case the default (NULL in this case) value will be stored in the f2 column. This is not normal at all.

I ran into that bug after upgrading from mysql 4.1 to 5.0. I wrote some PERL tools which connect to a MySQL database using DBD-MySQL CPAN module. The DBI API quotes automatically all the values of the arguments given to the execute() statements, so that's the reason why in my example a bool value (0 or 1) is quoted ('0' and '1').
[12 Sep 2006 16:27] Marius Feraru
doesn't occur for InnoDB tables, but it DOES for MyISAM tables.

(I'd attach my test case / output.... but I seem to have no permission to do that) :(
[12 Sep 2006 16:42] Marius Feraru
i have uploaded my test at:
  http://people.n0i.net/altblue/sql/mysql-myisam-bits/

(sample output given for mysql v5.0.22)
[13 Sep 2006 9:01] Sveta Smirnova
Thank you for the report and the test case.

Verified as described on Linux using test case provided by Marius Feraru and last BK sources. Version 5.1 is affected too. Version 4.1 is not affected.
[13 Sep 2006 14:43] Victor Tarhon-Onu
No, MySQL 4.1 and older are not affected because the bit type columns are created as tinyint.

I found something else which may be interesting and/or usefull in locating the bug (anyway probably somewhere a function returns earlier than it should): if there is a not null constraint on the column next to that one of bit type then the values are inserted corectly:

create table t2 (f1 bit(1), f2 smallint not null);
insert into t2 (f2, f1) values ('2','1');

now, select * from t2;  - and the f2 field will contain what it should.

Apparently only the column next to bit type column are affected. I created a table with two smallint columns and tried to insert an 'a' to force mysql to cast it to smallint and it worked fine.
[28 Sep 2006 12:02] Bugs System
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/12710

ChangeSet@1.2273, 2006-09-28 17:00:29+05:00, ramil@mysql.com +3 -0
  Fix for bug #22271: data casting may affect data stored in the next column(s?)
  
  Using wrong filling value may cause unneeded extra bit rewriting.
  
  Fix: use proper value to fill uneven bits.
[2 Oct 2006 20:36] Timothy Smith
OK to queue this patch.  Thanks, Ramil.
[3 Oct 2006 20:01] Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:13] Chad MILLER
Available in 5.1.12-beta.
[6 Oct 2006 3:14] Paul Dubois
Noted in 5.0.26, 5.1.12 changelogs.

Conversion of values inserted into a BIT column could affect
adjacent columns.