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.
