Bug #29084 Incorrect data conversion by unhex
Submitted: 13 Jun 2007 18:03 Modified: 1 Aug 2007 20:36
Reporter: Sergey Serov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.19-beta-log OS:Linux (Debian 2.6.18-3-amd64)
Assigned to: CPU Architecture:Any
Tags: BINARY, hex, UNHEX

[13 Jun 2007 18:03] Sergey Serov
Description:
If a binary column is large enough (20 in this case) then equal operator incorrectly returns false if the right part is a result of unhex() and the last characters of the right part is 20 (space).

However it was working fine with 5.1.11.

How to repeat:
mysql> create table tt(bin_col binary(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', unique key(bin_col));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into tt set bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087575');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into tt set bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087520');
Query OK, 1 row affected (0.00 sec)

mysql> select hex(bin_col) from tt where bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087520');
Empty set (0.00 sec)

mysql> select hex(bin_col) from tt where bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087575');
+------------------------------------------+
| hex(bin_col)                             |
+------------------------------------------+
| 1F9480179366F2BF567E1C4B964C1EF029087575 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>
[13 Jun 2007 18:04] Sergey Serov
Forget to mention that if the col is binary(2) then there is no problem.
[13 Jun 2007 18:13] Sergey Serov
The bug is not amd64 related. Under Debian 2.6.18.5 #1 SMP its the same.

Also using x'' convetions the problem still remains

mysql> select hex(bin_col) from tt where bin_col = x'1F9480179366F2BF567E1C4B964C1EF029087575';
+------------------------------------------+
| hex(bin_col)                             |
+------------------------------------------+
| 1F9480179366F2BF567E1C4B964C1EF029087575 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select hex(bin_col) from tt where bin_col = x'1F9480179366F2BF567E1C4B964C1EF029087520';
Empty set (0.01 sec)

mysql>
[13 Jun 2007 18:20] Sergey Serov
I have just found a workaround.
There is no bug if using 'binary' before the col name in select query.
Its clear to use 'binary' with varchars but not with true binary colums.

mysql> select hex(bin_col) from tt where binary bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087520');
+------------------------------------------+
| hex(bin_col)                             |
+------------------------------------------+
| 1F9480179366F2BF567E1C4B964C1EF029087520 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>
[13 Jun 2007 18:49] Sergey Serov
In the workaround above indexes are not working.
if replace unhex('') with x'' indexes will work.
[1 Aug 2007 19:07] MySQL Verification Team
Thank you for the bug report. I am not able to repeat with current source
server:

mysql> create table tt(bin_col binary(20) NOT NULL DEFAULT
    -> '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', unique key(bin_col));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tt set bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087575');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt set bin_col = unhex('1F9480179366F2BF567E1C4B964C1EF029087520');
Query OK, 1 row affected (0.00 sec)

mysql> select hex(bin_col) from tt where bin_col =
    -> unhex('1F9480179366F2BF567E1C4B964C1EF029087520');
+------------------------------------------+
| hex(bin_col)                             |
+------------------------------------------+
| 1F9480179366F2BF567E1C4B964C1EF029087520 |
+------------------------------------------+
1 row in set (0.03 sec)

mysql> select hex(bin_col) from tt where bin_col =
    -> unhex('1F9480179366F2BF567E1C4B964C1EF029087575');
+------------------------------------------+
| hex(bin_col)                             |
+------------------------------------------+
| 1F9480179366F2BF567E1C4B964C1EF029087575 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.21-beta-debug   |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | suse-linux-gnu      |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>
[1 Aug 2007 20:36] Sergey Serov
Thank you for reviewing the bug report.
I have downgraded the server version (because of suspected performance issues with 5.1.19) to the stable version that is available in Debian (5.0.32) there is still a bug.
I have just tried to install the latest "recommended" version (5.0.45) and the results were the same.
I may guess the bug is fixed in the latest version you tried but not in the stable production version.