MySQL does not detect pattern when using UNHEX function
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
High
|
Oleg Smirnov | |||
5.6 |
Triaged
|
High
|
Oleg Smirnov | |||
5.7 |
Triaged
|
High
|
Oleg Smirnov |
Bug Description
Hi,
When using rlike operator to detect characters with hex values between 80 and FF, it does not work when converting a binary string that starts with hexadecimal '00' using unhex function. Here is an example:
-- First query returns 1 because expresion matches with pattern.
mysql> set @text := unhex('149D5554');
Query OK, 0 rows affected (0.00 sec)
mysql> select @text, hex(@text);
+------
| @text | hex(@text) |
+------
| ?UT | 149D5554 |
+------
1 row in set (0.00 sec)
mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
-- Second query expresion starts with 00 and does not return a match.
mysql> set @text := unhex('
Query OK, 0 rows affected (0.00 sec)
mysql> select @text, hex(@text);
+------
| @text | hex(@text) |
+------
| ?UT | 00149D5554 |
+------
1 row in set (0.00 sec)
mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
Although '00' hex string is out of range from pattern 80 and FF, following strings of the expression should match with this pattern. For example, It works well with '01' hex string:
mysql> set @text := unhex('
Query OK, 0 rows affected (0.00 sec)
mysql> select @text, hex(@text);
+------
| @text | hex(@text) |
+------
| ?UT | 01149D5554 |
+------
1 row in set (0.00 sec)
mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
Thank you for the report.
Verified as described.