MySQL Bugs: #68153: REGEXP does not seem to match the NUL character

Bug #68153	REGEXP does not seem to match the NUL character
Submitted:	23 Jan 2013 11:40	Modified:	6 Jul 2017 14:30
Reporter:	Georg Bauhaus	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.29, 5.0.97, 5.1.69, 5.5.31, 5.7.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
According to the docs, the RE language includes notation for characters in classes, viz [.NUL.] for '\0' (ASCII 0), or [.tilde.] for '~' (ASCII 126). Trying this notation (when finding rows affected by a software error), it does not seem to work for ASCII NUL:

mysql> SELECT CHAR(0) REGEXP '[[.NUL.]]';
+----------------------------+
| CHAR(0) REGEXP '[[.NUL.]]' |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(126) REGEXP '[[.tilde.]]';
+--------------------------------+
| CHAR(126) REGEXP '[[.tilde.]]' |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(CHAR(0));
+-----------------+
| LENGTH(CHAR(0)) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

So I take it there is a string of length 1 consisting of the \0 character, but it does not match [[.NUL.]].

How to repeat:
SELECT CHAR(0) REGEXP '[[.NUL.]]';

Suggested fix:
(Is this a string termination issue in the RE engine?)

Thank you for the report.

Verified as described.

Yes, it seems like a string termination issue in RE engine. This issue caused  https://bugs.mysql.com/bug.php?id=85588, so I guess suggested patch for that case should fix this bug too.

Posted by developer:
 
Thank you for the bug report. We are switching the regular expression library in the next version, and the new library doesn't support collation classes at all. You will be able to use '\0' to match NUL, however.