| 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: | |
| 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: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.
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>