MySQL Bugs: #38733: Stored procedure using rlike against a null-valued parameter hangs

Bug #38733	Stored procedure using rlike against a null-valued parameter hangs
Submitted:	12 Aug 2008 1:15	Modified:	10 Sep 2008 14:02
Reporter:	Dave Shifflett	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Windows (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	hang, null, regression, RLIKE, Store Procedure, variable

Description:
After upgrading from MySQL 5.0.51a to 5.0.67, we found that our stored procedures using regular expression matching would hang.

After some investigation, we found that if you pass a parameter into a stored procedure that has 'null' as its value, and then use rlike against it, then the stored procedure will hang. Additionally, even statements such as "where parameter is null OR field rlike parameter," which should short-circuit before evaluating the rlike expression, will hang. Such stored procedures did not hang in the previous version we were using.

How to repeat:
I will attach a SQL script that triggers the bug, but in general, it can be duplicated following these steps:

1. Create a table with at least one text field; character set and table engine do not seem to make a difference in duplicating this problem.

2. Insert some rows into that new table.

3. Create a stored procedure where one of its parameter is used in a rlike-based expression in a 'where' clause.

4. Call that stored procedure with 'null' as the value of that parameter.

SQL Script to Create Database and Trigger Bug

Attachment: rlike-reproduction.sql (application/octet-stream, text), 924 bytes.

Verified on Windows XP. I will test with Linux:

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > create database dg;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use dg
Database changed
mysql 5.0 > create table entContact(
    ->     id int unsigned NOT NULL auto_increment,
    ->     person varchar(32) NOT NULL,
    ->     primary key(id)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql 5.0 >
mysql 5.0 > insert into entContact(person) values('Joe'), ('Jessica'), ('Alan');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > delimiter $$
mysql 5.0 > create procedure lstContacts(pPersonMatch varchar(64))
    -> begin
    ->     select * from entContact
    ->     where pPersonMatch is null or person rlike pPersonMatch;
    -> end$$
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > delimiter ;
mysql 5.0 > -- Either of these successfully return Joe.
mysql 5.0 > call lstContacts('Joe(seph)?');
+----+--------+
| id | person |
+----+--------+
|  1 | Joe    |
+----+--------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

mysql 5.0 > select * from entContact where person rlike 'Joe(seph)?';
+----+--------+
| id | person |
+----+--------+
|  1 | Joe    |
+----+--------+
1 row in set (0.00 sec)

mysql 5.0 > -- Any of these successfully return an empty set.
mysql 5.0 > call lstContacts('Sarah?');
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > select * from entContact where person rlike 'Sarah?';
Empty set (0.00 sec)

mysql 5.0 > select * from entContact where person rlike null;
Empty set (0.00 sec)

mysql 5.0 >
mysql 5.0 > -- Successfully returns all Joe, Jessica, and Alan.
mysql 5.0 > select * from entContact where null is null or person rlike null;
+----+---------+
| id | person  |
+----+---------+
|  1 | Joe     |
|  2 | Jessica |
|  3 | Alan    |
+----+---------+
3 rows in set (0.00 sec)

mysql 5.0 >
mysql 5.0 > -- This one hangs, but should return the same thing as the previous statement.
mysql 5.0 > call lstContacts(null);
Query aborted by Ctrl+C
^CDeseja interromper o arquivo em lotes (S/N)? s

Thank you for the bug report. Not repeatable on 5.1.

Possibly a duplicate of bug #39021

Confirmed : fixed by the fix for bug #39021