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

[11 May 2005 10:57] Disha
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>
[11 May 2005 18:07] MySQL Verification Team
I was able for to repeat this on Windows server. I will test again with
same changeset as Linux server.
[12 May 2005 0:14] MySQL Verification Team
I was able to repeat only with Windows server.
[13 May 2005 7:38] Sergei Golubchik
I also repeated on freebsd
[18 May 2005 12:28] 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/internals/25035
[6 Jun 2005 8:42] Ramil Kalimullin
Fixed in 5.0.7
[23 Jun 2005 13:32] Jon Stephens
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.
[24 Jun 2005 11:38] Jon Stephens
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