Bug #35360 * is not working correctly in fulltext boolean searches

• Submitted: 18 Mar 2008 5:56
• Modified: 18 Mar 2008 23:22
• Reporter: Rizwan Maredia
• Status: Not a Bug
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.1.22
• OS: Windows
• CPU Architecture: Any
• Tags: *, ft_boolean_syntax

[18 Mar 2008 5:56] Rizwan Maredia

Description:
In full text searching in Boolean mode, * is not working correctly. It is not returning all rows that are matched.

How to repeat:

Sample Test:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
INSERT INTO articles (title,body) VALUES
('1001 MySQL Tricks','1. Never run mysqld as root'),
('100 Tips for Myisam','1. Myisam is faster than innodb'),
('10 Tips for Innodb','1. Innodb supports transactions');
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('10*' IN BOOLEAN MODE);
id	title	body
1	1001 MySQL Tricks	1. Never run mysqld as root

Suggested fix:
All rows should have been returned as 10* matches 1001,100,10.

[18 Mar 2008 23:22] MySQL Verification Team

Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

" Some words are ignored in full-text searches:

    *

      Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is four characters. "

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-community MySQL Community Server (GPL)

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

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS articles;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> )Engine MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('1001 MySQL Tricks','1. Never run mysqld as root'),
    -> ('100 Tips for Myisam','1. Myisam is faster than innodb'),
    -> ('10 Tips for Innodb','1. Innodb supports transactions'),
    -> ('1002 Tips for FT','1. here I have 4 characters'),
    -> ('1003 Tips for FT','1. here I have 4 characters too');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('10*' IN BOOLEAN MODE);
+----+-------------------+---------------------------------+
| id | title             | body                            |
+----+-------------------+---------------------------------+
|  1 | 1001 MySQL Tricks | 1. Never run mysqld as root     |
|  4 | 1002 Tips for FT  | 1. here I have 4 characters     |
|  5 | 1003 Tips for FT  | 1. here I have 4 characters too |
+----+-------------------+---------------------------------+
3 rows in set (0.00 sec)

mysql>