Bug #5253 hex() return type depends on input, should be string/varchar
Submitted: 27 Aug 2004 14:10 Modified: 30 Dec 2005 12:35
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:MySQL 4.0.20a-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[27 Aug 2004 14:10] Olaf van der Spek
Description:
The return type of the hex() function depends on the input. If the input is a blob field, the output is considered binary and a case sensitive compare is used.
In my opinion the output should always be a string/varchar, such that a case insensitive compare is used.
Apache/2.0.50
MySQL 4.0.20a-nt 
PHP Version 4.3.8

mysql> select hex(a), hex(a) = '2f' from hex_test;
+--------+---------------+
| hex(a) | hex(a) = '2f' |
+--------+---------------+
| 2F     |             0 |
+--------+---------------+
1 row in set (0.00 sec)

How to repeat:
create table hex_test
(
  a blob
);
insert into hex_test values ('/');
select hex(a), hex(a) = '2f' from hex_test;
drop table hex_test;
[27 Aug 2004 14:25] Olaf van der Spek
Changed category to MySQL Server.
[29 Dec 2005 16:00] Valeriy Kravchuk
Thank you for a feature request. It works for me just as you wanted, on newer versions:

mysql> create table hex_test
    -> (
    ->   a blob
    -> );
Query OK, 0 rows affected (1.39 sec)

mysql> insert into hex_test values ('/');
Query OK, 1 row affected (0.13 sec)

mysql> select hex(a), hex(a) = '2f' from hex_test;
+--------+---------------+
| hex(a) | hex(a) = '2f' |
+--------+---------------+
| 2F     |             1 |
+--------+---------------+
1 row in set (0.09 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.02 sec)

So, please, check. Looks like we can close this request.
[30 Dec 2005 12:35] Olaf van der Spek
Yes, this appears to be fixed.
Thanks.