MySQL Bugs: #106504: A regular expression is not supported in MySQL8, but is supported in MySQL5.7

Bug #106504	A regular expression is not supported in MySQL8, but is supported in MySQL5.7
Submitted:	18 Feb 2022 8:57	Modified:	18 Mar 2022 10:14
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (x86-64)
Tags:	regression

Description:
Hi,
  There is a regular expression, which is supported in MySQL5.7.22, but is not supported in MySQL8.0.27. The regular expression is like this:

  select 'a word a' REGEXP 'word[[:>:]]';

How to repeat:
Execute the SQL statement in MySQL5.7.22:

mysql> select 'a word a' REGEXP 'word[[:>:]]';
+---------------------------------+
| 'a word a' REGEXP 'word[[:>:]]' |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

Execute the SQL statement in MySQL8.0.27:

mysql> select 'a word a' REGEXP 'word[[:>:]]';
ERROR 3685 (HY000): Illegal argument to a regular expression.
mysql>

Suggested fix:
The SQL statement is expected to be executed successfully.

Hello Brian Yue,

Thank you for the report and feedback.

regards,
Umesh

MySQL 8.0 uses ICU regexp, which is documented here:
https://unicode-org.github.io/icu/userguide/strings/regexp.html

Posted by developer:
 
MySQL 8.0 uses ICU regexp, which is documented here:
https://unicode-org.github.io/icu/userguide/strings/regexp.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm seeing this issue using WordPress on MySQL 8.0.38

#3685 - Illegal argument to a regular expression.

Error thrown from
posts.post_content REGEXP '[[:<:]]billie[[:>:]]'

Will this be fixed?

Posted by developer:
 
MySQL 5.7 used the Henry Spencer regexp library:
https://garyhouston.github.io/regex/regex7.html

There are two special cases+ of bracket expressions: the bracket
expressions `[[:<:]]' and `[[:>:]]' match the null string at the
beginning and end of a word respectively.

MySQL 8.0 and up uses ICU for regexp.
https://unicode-org.github.io/icu/userguide/strings/regexp.html

\b   Match if the current position is a word boundary. Boundaries occur at
     the transitions between word (\w) and non-word (\W) characters, with
     combining marks ignored.

So you must rewrite your regexp.