Bug #77300 Regexp match is too wide

• Submitted: 10 Jun 2015 16:24
• Modified: 8 Jun 2017 9:17
• Reporter: Max Russkikh
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.25
• OS: Any
• CPU Architecture: Any
• Tags: REGEXP, RLIKE

[10 Jun 2015 16:24] Max Russkikh

Description:
The documentation says: "The simplest regular expression is one that has no special characters in it. For example, the regular expression hello matches hello and nothing else".

But this query
Select "hello world" regexp "hello"
returns 1

As workaround, I had to use something similar to
Select "hello world" regexp "^hello$"
returns 0

How to repeat:
Select "010" regexp "1"

Suggested fix:
I think that regexp without special characters should give the same result as "LIKE".

[10 Jun 2015 16:27] Max Russkikh

Comma in tags

[8 Jun 2017 9:11] Oleg Smirnov

Hi, Maxim!

I think you misunderstood the documentation. Since it says "the regular expression hello matches hello and nothing else" it does not mean "matches strings STARTING from hello and ENDING with it and nothing else". If you want to bind to the start or to the end of string you can use ^ and $. 
I checked the same case in Oracle database and got same results: 
select count(*) from dual where regexp_like ('hello world', 'hello');
-> 1
while:
select count(*) from dual where 'hello world' like 'hello';
-> 0.

So REGEXP/RLIKE without special symbols isn't the same as LIKE.

[8 Jun 2017 9:17] Max Russkikh

Yes. I know it at the moment. But the documentation is ambiguous.

[8 Jun 2017 9:52] Oleg Smirnov

I agree. Don't know how to contribute to documentation, first I thought it is a regexp engine bug...