Regular (RLIKE and REGEXP) expressions: Good News

Ten years ago, MySQL got a "bug report" about trouble with RLIKE. It got marked "won't fix" because MySQL used a regular-expression library that couldn't handle non-ASCII characters reliably. Over time there were multiple similar or duplicate bug reports but the only result was a note in the MySQL manual saying, in effect, "tough luck". Until now.

Actually the fix is in a pre-release of the bzr branch for MariaDB 10.0.5, and I can't predict that the MySQL folks will copy it, but it looks good to me, and I clap for the original coders who made the "Perl Compatible Regular Expressions" library, the "Google Summer of Code" folks who pay students to help with open source projects, the student coder in question -- Sudheera Palihakkara from a Sri Lanka university -- and finally Alexander Barkov from the MariaDB foundation.

So what were the problems and what's been solved -- or not solved?

Solved: Handling any characters

Most of the complaints about REGEXP have been about handling of accented Latin characters, Cyrillic characters, or, in short: regardless of character set or collation, if any character in the pattern was outside the ASCII range or was NUL, then there would be false hits, no hits, miscalculated lengths, or failed case conversions. I took examples from the "how to repeat" section of those bug reports, and tried them with MariaDB 10.0.5. Results:

/* Bug#34473 */
MariaDB [test]> SELECT 'Ørneborgen' RLIKE '^[Ö]';
| 'Ørneborgen' RLIKE '^[Ö]'   |
|                           0 |
1 row in set (0.03 sec)

/* Bug#54576 */
MariaDB [test]> SELECT 'č' REGEXP '^.$';
| 'č' REGEXP '^.$'  |
|                 1 |
1 row in set (0.00 sec)

/* Bug#52080 */
MariaDB [test]> SELECT 'Я' regexp 'я';
| 'Я' regexp 'я'   |
|                1 |
1 row in set (0.02 sec)

/* Bug#63439 */
MariaDB [test]> SELECT 'ääliö' REGEXP '^[aä]{1}[aä]{1}li[oö]{1}$';
| 'ääliö' REGEXP '^[aä]{1}[aä]{1}li[oö]{1}$'       |
|                                                1 |
1 row in set (0.03 sec)

/* Bug#70470 */
MariaDB [test]> SELECT 'abc\0def' REGEXP 'def';
| 'abc\0def' REGEXP 'def' |
|                       1 |
1 row in set (0.00 sec)

All the above answers are correct, for the first time ever.

In fact one could say that the new REGEXP handles too many characters. It's adjusted for Unicode 6.2, a version that neither MySQL nor MariaDB support yet. This will of course be of concern to those who use the Meroitic alphabet, but they're a fading minority.

Not solved: incompatibilities

I guess it was inevitable that, since there's a different underlying library, the text of some error messages would change. For example I get this:

MariaDB [test]> select '1' rlike rpad('1',100,'(');
ERROR 1139 (42000): Got error 'missing ) at offset 100' from regexp

and that's okay, but in MySQL 5.6.14 the error message is:
"ERROR 1139 (42000): Got error 'parentheses not balanced' from regexp".

A bit more interesting is this query:

/* Bug#68153 */
MariaDB [test]> SELECT CHAR(126) REGEXP '[[.tilde.]]';
ERROR 1139 (42000): Got error 'POSIX collating elements
 are not supported at offset 1' from regexp

and that looks a little less okay. In MySQL 5.6.14 the same search condition doesn't cause an error, it's considered to be "true".

Solved: Handling more Posix

The MariaDB folks have proudly announced that version 10.0.5 supports "recursive patterns, named capture, look-ahead and look-behind assertions, non-capturing groups, non-greedy quantifiers". To which I'm sure that the response will be: What? How shocking that non-greedy quantifiers weren't added ten years ago!

Well, maybe not. But when looking at new features, it's wrong to ask merely whether there's some advantage for the non-greedy man on the Clapham omnibus. The point is it's supposed to be more compatible with modern Perl programs, with Posix, and with all the box-tickers who will ask: do you really support regular expressions? And if someday I reach that pinnacle of ambition, to figure out grep, then I'll doubtless appreciate look-behind assertions in the way they deserve to be appreciated.

Unsolved: handling more standard SQL

In the operetta Die Fledermaus there's a refrain: "Happy is the one who forgets what isn't going to be changed". But I, alas, can't forget the comment that I wrote on Bug#746 ten years ago, showing there are at least six behavioural differences between REGEXP and the optional-SQL-standard operator, SIMILAR TO. The incompatibilities arise because SIMILAR TO works like LIKE, so it's more SQL-ish, and less Posix-ish. Thus, improvements in REGEXP alone are not an advance towards more standard SQL. This would matter more if there were many other SQL implementations supporting SIMILAR TO. The only ones I know of are PostgreSQL and Firebird.

, October 22, 2013. Category: MySQL / MariaDB.

About pgulutzan

Co-author of four computer books. Software Architect at MySQL/Sun/Oracle from 2003-2011, and at HP for a little while after that. Currently with Ocelot Computer Services Inc. in Edmonton Canada.


  1. Alexander Barkov says:

    Peter, along with the switch to PCRE, MariaDB-10.0.5
    also introduced new SQL functions REGEXP_REPLACE,
    REGEXP_SUBSTR, REGEXP_INSTR, which greatly improve the power
    of MariaDB text processing capabilities.

  2. […] stehen verbesserte Funktionen für reguläre Ausdrücke zur Verfügung. Damit können, anders als bislang in MySQL, auch Ausdrücke per REGEXP_REPLACE […]