MySQL Bugs: #35144: Update fails if bit field is compared to '\0'

Bug #35144	Update fails if bit field is compared to '\0'
Submitted:	7 Mar 2008 12:10	Modified:	7 Mar 2008 19:08
Reporter:	Lawrenty Novitsky	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.0.56	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bit UPDATE

Description:
If bit column is tested to be equal '\0' in WHERE clause of UPDATE statement, it fails with:
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

But it works for SELECT statement. As well as w/ numeric fields.

Occurs on 5.0.45, and works on 5.1.x

How to repeat:
mysql> CREATE TABLE bug30349 (C1 INT PRIMARY KEY, C2 CHAR(20), C3 BIT(1));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into bug30349 values (1,'FOO',0), (2,'BAR', 1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from bug30349 where c3='\0';
+----+------+------+
| C1 | C2   | C3   |
+----+------+------+
|  1 | FOO  |      |
+----+------+------+
1 row in set, 2 warnings (0.00 sec)

mysql> update bug30349 set C2='test' where c3='\0';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

mysql> update bug30349 set C2='test' where c3='0';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show variables like "version";
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 5.0.45-community-nt |
+---------------+---------------------+
1 row in set (0.00 sec)

Thank you for a problem report. 5.0.56 demonstrates the same behaviour:

mysql> select * from bug30349 where c3='\0';
+----+------+------+
| C1 | C2   | C3   |
+----+------+------+
|  1 | FOO  |      |
+----+------+------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: ''
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: ''
2 rows in set (0.00 sec)

mysql> update bug30349 set C2='test' where c3='\0';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''
mysql> update bug30349 set C2='test' where c3='0';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

You should NOT use '\0', it seems, to work with bit values (use b'0' or just 0  instead), but UNPDATE results are inconsistent with SELECT results for the same WHERE clause, and this is a bug.

I'm not sure if following adds something to report, or either it's bug or related to  original report. But could be :) Sort of fyi.
So, if we have table:

CREATE TABLE `test` (
  `pk` int(11) NOT NULL DEFAULT '0',
  `c1` bit(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and trying to insert a row like:
mysql> insert into test values(2,'1');

we are getting error
ERROR 1406 (22001): Data too long for column 'c1' at row 1

I can imagine that string '1' is casted to int 1, and its 32 bits are too long to fit our bit field.
The only thing is that it fails in 5.0, but works in 5.1 server. On other hand 5.1 bites everything and sets (all bits) to 1 for all strings except '\0' (if any non-null byte?).