Bug #62447 regexp Return error results

• Submitted: 16 Sep 2011 2:27
• Modified: 16 Sep 2011 6:00
• Reporter: qu xiangfei
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: mysql-5.5.15-win32
• OS: Windows (windows xp)
• CPU Architecture: Any
• Tags: REGEXP, SELECT

[16 Sep 2011 2:27] qu xiangfei

Description:
No matter what I input search conditions return the results are all the same. When I use symbol * .

How to repeat:
mysql> select * from pet where name regexp '[bsb45y4hrhrh5]*';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| puffball | diane  | hamster | f    | NULL       | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> select * from pet where name regexp '[211341545]*';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| puffball | diane  | hamster | f    | NULL       | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> select * from pet where name regexp '[abc]*';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| puffball | diane  | hamster | f    | NULL       | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> select * from pet where name regexp '[ ]*';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| puffball | diane  | hamster | f    | NULL       | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> select * from pet where name regexp '[.]*';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| puffball | diane  | hamster | f    | NULL       | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

[16 Sep 2011 6:00] Valeriy Kravchuk

Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/regexp.html. a* in REGEXP means "Match any sequence of zero or more a characters."

Compare:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3312
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 'Fluffy' regexp '[bsb45y4hrhrh5]*';
+------------------------------------+
| 'Fluffy' regexp '[bsb45y4hrhrh5]*' |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.06 sec)

mysql> select 'Fluffy' regexp '[bsb45y4hrhrh5][bsb45y4hrhrh5]*';
+---------------------------------------------------+
| 'Fluffy' regexp '[bsb45y4hrhrh5][bsb45y4hrhrh5]*' |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'Fluffy' regexp '[bsb454hrhrh5][bsb454hrhrh5]*';
+-------------------------------------------------+
| 'Fluffy' regexp '[bsb454hrhrh5][bsb454hrhrh5]*' |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'Fluffy' regexp '[bsb454hrhrh5]*';
+-----------------------------------+
| 'Fluffy' regexp '[bsb454hrhrh5]*' |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

Note that presence of 'y' in [] matters, as 'Fluffy' contains it.

I do not see any bug here.

[16 Sep 2011 7:58] qu xiangfei

no y

Attachment: 2011-09-16_15-53-02.png (image/x-png, text), 12.74 KiB.

[16 Sep 2011 7:59] qu xiangfei

I am a novice, don't understand!

Attachment: 2011-09-16_15-56-38.png (image/x-png, text), 19.09 KiB.