MySQL Bugs: #32628: stored routine function in where clause

Bug #32628	stored routine function in where clause
Submitted:	22 Nov 2007 16:04	Modified:	23 Nov 2007 7:31
Reporter:	Henning Sander	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (SLES9-SP3)
Assigned to:		CPU Architecture:	Any
Tags:	user defined function

Description:
Wrong result from a query with a stored function in the where clause:

How to repeat:
CREATE TABLE `uvt` (
  `u` int(10) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `uvt` VALUES
  (1),(2),(3),(4);

CREATE TABLE `s` (
  `n` varchar(30) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `s` VALUES
  ('filer1'),('info'),('servicepoint'),('Test');

delimiter //
create function w(s varchar(512), r varchar(512)) returns boolean
  deterministic
  sql security invoker
  begin
    return s REGEXP CONCAT('^',REPLACE(r, '*', '.*') , '$');
  end//

delimiter;
select count(s.n) from s, uvt where w(n, '*i*') = 0;
select count(s.n) from s, uvt where w(n, '*i*') >= 0;
select count(s.n) from s, uvt where w(n, '*i*') > 0;
select count(s.n) from uvt, s where w(n, '*i*') > 0;

The first result is 4.
The second result is 16.
The third result is 0 and shuld be 12.
The fourth result is 12.

If one deletes one line from table s or table uvt the results are correct.

Thank you for the report.

I can not repeat described behaviour with version 5.0.45, but can not with current developer sources. So I'll close the report as "Can't repeat".

Please wait next release.

I made a new installation of the MySQL-Server on Windows. There I got the same results. (Download from: http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-essential-5.0.45-win32.msi/from/http://...)

C:\>mysql -u root -p test
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

mysql> CREATE TABLE uvt(
    ->   u int(10) unsigned default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO uvt VALUES
    ->   (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE s (
    ->   n varchar(30) NOT NULL default ''
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO s VALUES
    ->   ('filer1'),('info'),('servicepoint'),('Test');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> delimiter //
mysql> create function w(s varchar(512), r varchar(512)) returns boolean
    ->   deterministic
    ->   sql security invoker
    ->   begin
    ->     return s REGEXP CONCAT('^',REPLACE(r, '*', '.*') , '$');
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> select count(s.n) from s, uvt where w(n, '*i*') = 0;
+------------+
| count(s.n) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select count(s.n) from s, uvt where w(n, '*i*') >= 0;
+------------+
| count(s.n) |
+------------+
|         16 |
+------------+
1 row in set (0.00 sec)

mysql> select count(s.n) from s, uvt where w(n, '*i*') > 0;
+------------+
| count(s.n) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select count(s.n) from uvt, s where w(n, '*i*') > 0;
+------------+
| count(s.n) |
+------------+
|         12 |
+------------+
1 row in set (0.00 sec)

mysql>

I am sorry for missprint. My previous comment should be written like:

I can repeat described behaviour with version 5.0.45, but can not with current
developer sources.

So, please, wait release following 5.0.45.