Bug #40187 bit comparison against string literal is inconsistent
Submitted: 20 Oct 2008 19:43 Modified: 21 Oct 2008 4:08
Reporter: Jess Balint Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.62, 5.1.28 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[20 Oct 2008 19:43] Jess Balint
Description:
bit comparison of a field against a string literal returns a different result than a comparison between a bit literal and a string literal

How to repeat:
create table bitx (d bit(30));
insert into bitx values(b'01000001');
select bin(d),d,d='A' as 'cmp bit field',d=b'01000001',b'01000001'='A' as 'cmp bit literal' from bitx;

+---------+------+---------------+---------------+-----------------+
| bin(d)  | d    | cmp bit field | d=b'01000001' | cmp bit literal |
+---------+------+---------------+---------------+-----------------+
| 1000001 |    A |             0 |             1 |               1 | 
+---------+------+---------------+---------------+-----------------+
[21 Oct 2008 4:08] Valeriy Kravchuk
I've got the same result with 5.1.28 also:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bitx (d bit(30));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into bitx values(b'01000001');
Query OK, 1 row affected (0.17 sec)

mysql> select bin(d),d,d='A' as 'cmp bit field',d=b'01000001',b'01000001'='A'
    -> as 'cmp bit literal' from bitx;
+---------+------+---------------+---------------+-----------------+
| bin(d)  | d    | cmp bit field | d=b'01000001' | cmp bit literal |
+---------+------+---------------+---------------+-----------------+
| 1000001 |    A |             0 |             1 |               1 |
+---------+------+---------------+---------------+-----------------+
1 row in set, 1 warning (0.73 sec)

But note the warning:

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

So, looks like bit column and character literal were compared as DOUBLE, as manual says (http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html):

"In all other cases, the arguments are compared as floating-point (real) numbers."

But I'd expect values to be compared as bits or as strings in this case, so I still consider this a bug.

Workaround is: hex(d) = hex('A') ...