MySQL Bugs: #22271: data casting may affect data stored in the next column(s?)

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

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').

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) :(

i have uploaded my test at:
  http://people.n0i.net/altblue/sql/mysql-myisam-bits/

(sample output given for mysql v5.0.22)

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.

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.

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.

OK to queue this patch.  Thanks, Ramil.

Available in 5.0.26.

Available in 5.1.12-beta.

Noted in 5.0.26, 5.1.12 changelogs.

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