MySQL Bugs: #40187: bit comparison against string literal is inconsistent

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

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 | 
+---------+------+---------------+---------------+-----------------+

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