MySQL Bugs: #37892: REGEXP error when escaping period

Bug #37892	REGEXP error when escaping period
Submitted:	5 Jul 2008 8:26	Modified:	7 Jul 2008 11:10
Reporter:	Matt Wegrzyn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51b-community-nt	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
MySQL throws an error when escaping the "." character in a regular expression match. Happens only if the regular expression is in a certain order. See "how to repeat" for more info.

How to repeat:
On an INNODB table:

SELECT domain_name FROM domain_info where domain_name regexp '^[^0-9\-\.]*';

This throws the following error:
ERROR 1139 (42000): Got error 'invalid character range' from regexp

Although, if you reverse the order in the regular expression bracket, it returns the results:

SELECT domain_name FROM domain_info where domain_name regexp '^[^\.0-9\-]*';

Both should do exactly the same thing, but the first one fails for some odd reason.

Suggested fix:
Both should return the same results.

An easier example:

Throws error:
SELECT concat('hello') REGEXP '^[^0-9\-\.]*';

Does not throw error:
SELECT concat('hello') REGEXP '^[^\.0-9\-]*';

A double-escaping is the issue here.

MySQL decodes escapes in a character literal, so regexp library sees '^[^0-9-.]*' - it complains because 9-. is an invalid range, as ASCII('.') < ASCII('9').

Either disable backslash escapes or double-escape. See the manual for details.