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)
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: MySQL 4.0.20a-nt
• OS: Windows (Windows XP)
• 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.