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: | |
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 |
[22 Nov 2007 16:04]
Henning Sander
[22 Nov 2007 20:49]
Sveta Smirnova
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.
[23 Nov 2007 7:31]
Henning Sander
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>
[23 Nov 2007 7:38]
Sveta Smirnova
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.