Bug #22568 REGEXP '[0-9]' and REGEXP '[:digit:]' produces different results

• Submitted: 21 Sep 2006 21:44
• Modified: 21 Sep 2006 21:58
• Reporter: David Brown
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: mysql-5.1.11-beta-osx10.4-i686
• OS: Max OSX 10.4
• Assigned to: Dean Ellis
• CPU Architecture: Any

[21 Sep 2006 21:44] David Brown

Description:
When comparing REGEXP '[:digit:]' and REGEXP and [0-9], REGEXP '[:digit:] produces incorrect results:

How to repeat:
The is the contents of an example table that is provided for Bob Lafore's book "
MySQL Crash Course":

mysql> SELECT prod_name FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   | 
| 1 ton anvil    | 
| 2 ton anvil    | 
| Detonator      | 
| Bird seed      | 
| Carrots        | 
| Fuses          | 
| JetPack 1000   | 
| JetPack 2000   | 
| Oil can        | 
| Safe           | 
| Sling          | 
| TNT (1 stick)  | 
| TNT (5 sticks) | 
+----------------+
14 rows in set (0.00 sec)

Here are the correct results produced using REGEXP and [0-9]:

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]';
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   | 
| 1 ton anvil    | 
| 2 ton anvil    | 
| JetPack 1000   | 
| JetPack 2000   | 
| TNT (1 stick)  | 
| TNT (5 sticks) | 
+----------------+
7 rows in set (0.00 sec)

Here are the incorrect results produced using REGEXP and [:digit:]:

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[:digit:]';
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   | 
| 1 ton anvil    | 
| 2 ton anvil    | 
| Detonator      | 
| Bird seed      | 
| Carrots        | 
| JetPack 1000   | 
| JetPack 2000   | 
| Oil can        | 
| Sling          | 
| TNT (1 stick)  | 
| TNT (5 sticks) | 
+----------------+
12 rows in set (0.00 sec)

Suggested fix:
Patch ;-)

[21 Sep 2006 21:54] Paul DuBois

The class name includes the surrounding [ and ] characters.
As they are intended for use within a range, add another
set of [ and ] characters.

mysql> select '1' regexp '[:digit:]';
+------------------------+
| '1' regexp '[:digit:]' |
+------------------------+
|                      0 | 
+------------------------+
1 row in set (0.01 sec)

mysql> select '1' regexp '[[:digit:]]';
+--------------------------+
| '1' regexp '[[:digit:]]' |
+--------------------------+
|                        1 | 
+--------------------------+
1 row in set (0.00 sec)