Description:
When comparing REGEXP '[:upper:]' and REGEXP and [A-Z], REGEXP '[A-Z]
produces incorrect results:
How to repeat:
This reveals 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 [:upper:]:
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[:upper:]';
+--------------+
| prod_name |
+--------------+
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Safe |
+--------------+
7 rows in set (0.00 sec)
Here are the incorrect results produced using REGEXP and [A-Z]:
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[A-Z]';
+----------------+
| 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)
Suggested fix:
Another Patch
