Bug #3734 Stored procedure returns wrong rows with fulltext parameter
Submitted: 13 May 2004 1:58 Modified: 28 May 2004 18:12
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1-alpha-debug/Win XP OS:Linux (SuSE 8.2)
Assigned to: Bugs System CPU Architecture:Any

[13 May 2004 1:58] Peter Gulutzan
Description:
If I have a stored procedure which uses a parameter in a fulltext "MATCH"  clause, the 
result is always the same -- it doesn't change if I call the procedure with a different 
argument. 
 
I'm aware that the manual says you should only use a constant with "MATCH". Still, if 
MySQL accepts the syntax, it should try to return the right answer. 
 

How to repeat:
-- The first lines are taken from the MySQL Reference Manual example, page "Full-Text  
-- Search Functions", http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html 
 
mysql> CREATE TABLE articles ( 
    ->        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    ->       title VARCHAR(200), 
    ->        body TEXT, 
    ->       FULLTEXT (title,body) 
    ->      ); 
Query OK, 0 rows affected (0.64 sec) 
 
mysql> 
mysql> INSERT INTO articles (title,body) VALUES 
    ->      ('MySQL Tutorial','DBMS stands for DataBase ...'), 
    ->      ('How To Use MySQL Well','After you went through a ...'), 
    ->      ('Optimizing MySQL','In this tutorial we will show ...'), 
    ->      ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 
    ->      ('MySQL vs. YourSQL','In the following database comparison ...'), 
    ->      ('MySQL Security','When configured properly, MySQL ...'); 
Query OK, 6 rows affected (0.00 sec) 
Records: 6  Duplicates: 0  Warnings: 0 
 
mysql> 
mysql> 
mysql>  SELECT * FROM articles 
    ->      WHERE MATCH (title,body) AGAINST ('database'); 
+----+-------------------+------------------------------------------+ 
| id | title             | body                                     | 
+----+-------------------+------------------------------------------+ 
|  5 | MySQL vs. YourSQL | In the following database comparison ... | 
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | 
+----+-------------------+------------------------------------------+ 
2 rows in set (0.00 sec) 
 
-- Now I add my own lines. I make a procedure which does the same full-text search: 
 
mysql> CREATE PROCEDURE p (param1 VARCHAR(100)) 
    -> SELECT * FROM articles 
    -> WHERE MATCH (title,body) AGAINST (param1); 
Query OK, 0 rows affected (2.10 sec) 
 
mysql> CALL p('database'); 
+----+-------------------+------------------------------------------+ 
| id | title             | body                                     | 
+----+-------------------+------------------------------------------+ 
|  5 | MySQL vs. YourSQL | In the following database comparison ... | 
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | 
+----+-------------------+------------------------------------------+ 
2 rows in set (0.00 sec) 
 
Query OK, -1 rows affected (0.00 sec) 
 
-- That's the right answer, these rows contain 'Database'. 
 
mysql> CALL p('Security'); 
+----+-------------------+------------------------------------------+ 
| id | title             | body                                     | 
+----+-------------------+------------------------------------------+ 
|  5 | MySQL vs. YourSQL | In the following database comparison ... | 
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | 
+----+-------------------+------------------------------------------+ 
2 rows in set (0.00 sec) 
 
Query OK, -1 rows affected (0.00 sec) 
 
-- Oops! Neither of the returned rows contains the word 'Security'. 
 
mysql> select * from articles where match(title,body) against ('Security'); 
+----+----------------+-------------------------------------+ 
| id | title          | body                                | 
+----+----------------+-------------------------------------+ 
|  6 | MySQL Security | When configured properly, MySQL ... | 
+----+----------------+-------------------------------------+ 
1 row in set (0.00 sec) 
 
-- This is what  CALL p('Security') should have returned.
[28 May 2004 18:12] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html