MySQL does not detect pattern when using UNHEX function

[Juan Pablo Arruti]

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('00149D5554');
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('01149D5554');
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)

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described.

[Oleg Smirnov (olernov)]

Suggested fix: https://github.com/percona/percona-server/pull/1770

[Oleg Smirnov (olernov)]

Suggested fix for 5.6: https://github.com/percona/percona-server/pull/1778

[Oleg Smirnov (olernov)]

Suggested fix for 5.5: https://github.com/percona/percona-server/pull/1787

[Oleg Smirnov (olernov)]

They reported "Won't fix" in upstream because they are switching to a new regexp library in upcoming release of MySQL where this bug is not present. Should we change our status to "Won't fix" accordingly?

[Laurynas Biveinis (laurynas-biveinis)]

Not necessarily - we'd still like to review these patches for 5.5-5.7 merge even given the upstream decision.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1079