MySQL Bugs: #10539: When inserting out of range value in BIT, different engines behaves differently

Bug #10539	When inserting out of range value in BIT, different engines behaves differently
Submitted:	11 May 2005 10:57	Modified:	24 Jun 2005 11:38
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.5 Beta	OS:	Windows (Windows 2003)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
If we try to insert out of range value in to a bit field then for myisam all bits are inserted as 1 and for memory and innodb 0 is inserted.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create three tables with bit data type each table should use different database engine as follows:
   create table t1(f1 bit(8)) engine=innodb//
   create table t2(f1 bit(8)) engine=myisam//
   create table t3(f1 bit(8)) engine=memory//
   
4. Now insert a bit value having length more than 8 in all three tables as follows:
   Insert into t1 values(b'1111100000')//
   Insert into t2 values(b'1111100000')//
   Insert into t3 values(b'1111100000')//

Expected Results: 
1. The value should get inserted by adjusting the length.
2. The behavior should be consistent across different engines.

Actual Results: 
1. For each insert statement a warming is displayed as follows:
Warning | 1264 | Out of range value adjusted for column 'f1' at row 1
2. The value that is inserted differs for different engines
   a. For myisam a value with all bits set to 1 is inserted
   b. For innodb and memory, a zero is inserted.

The following is the output of select statement for each table

   mysql>
   mysql> select bin(f1+0) from t1//
   +-----------+
   | bin(f1+0) |
   +-----------+
   | 0         |
   +-----------+
   1 row in set (0.00 sec)
   
   mysql> select bin(f1+0) from t2//
   +-----------+
   | bin(f1+0) |
   +-----------+
   | 11111111  |
   +-----------+
   1 row in set (0.02 sec)
   
   mysql> select bin(f1+0) from t3//
   +-----------+
   | bin(f1+0) |
   +-----------+
   | 0         |
   +-----------+
   1 row in set (0.00 sec)
   
   mysql>

I was able for to repeat this on Windows server. I will test again with
same changeset as Linux server.

I was able to repeat only with Windows server.

I also repeated on freebsd

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/internals/25035

Fixed in 5.0.7

I tested this on 5.0.7-beta-nt-max (Windows 2000 Server). With SQL_MODE='mysql40', the out-of-range value was truncated and inserted as inspected, and generated an approipriate warning.

However, with SQL_MODE='traditional', I obtained the following result:

mysql> create table b1 (c bit(8));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into b1 values (b'100');
Query OK, 1 row affected (0.06 sec)

mysql> insert into b1 values (b'1100');
Query OK, 1 row affected (0.02 sec)

mysql> insert into b1 values (b'100000000');
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1
mysql> SELECT bin(c+0) FROM b1;
+----------+
| bin(c+0) |
+----------+
| 100      |
| 1100     |
+----------+
2 rows in set (0.00 sec)

The result was identical using either the MyISAM or the InnoDB storage engine.

Whilst the behaviour itself is correct in that the value is not inserted, the error message needs to be modified accordingly, as the value is not 'adjusted'; rather it is not used at all.

Opened new bug at developer request, as the problem with error messages which I noted previously constitutes a separate issue.

See http://bugs.mysql.com/11546