Bug #963 Problem with IN BOOLEAN MODE with FULLTEXT indexes
Submitted: 31 Jul 2003 2:41 Modified: 1 Aug 2003 7:11
Reporter: Richard van Denzel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.0.14 OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[31 Jul 2003 2:41] Richard van Denzel
Description:
Hi,

I've got a table called artikelen with a number of fields including id and problem. On this table I put a FULLTEXT index on problem.

In this table are 7 records, 4 containing the word TSM and 4 containing the word LPAR (3 records contain them both, 1 contains only TSM and one only contains LPAR).

When I perform the following query an empty set ir returned:

select id from artikelen where match(problem) against('TSM' in boolean mode);

When I perform the following query all four id's are returned:

select id from artikelen where match(problem) against('LPAR' in boolean mode);

Is this normal behaviour, did I miss something or is it a bug?
The same behaviour also occurred with versions 4.0.12 and 4.0.13.

How to repeat:
See above.

Suggested fix:
If it's a bug, please fix.
[31 Jul 2003 3:49] MySQL Verification Team
Could you please upload a test case with the tables and
data ?
[31 Jul 2003 8:53] Sergei Golubchik
normally MySQL ignores words that are shorter than 4 characters.
Try 

SHOW VARIABLES LIKE 'ft_%'

you will see what the minimal word len is.

http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html
[1 Aug 2003 0:08] Richard van Denzel
From mysql:

mysql> select id,problem from artikelen;
+----+-----------------------------------------+
| id | problem                                 |
+----+-----------------------------------------+
|  1 | p660-6H1 wil AIX 5 niet booten vanaf CD |
|  2 | Hoe start men TSM handmatig op AIX?     |
|  3 | Testrecord                              |
|  4 | Record met 2 keywords TSM en LPAR       |
|  5 | Record met keywords LPAR en TSM         |
|  6 | Record LPAR en TSM keywords             |
|  7 | Record met keyword LPAR                 |
+----+-----------------------------------------+
7 rows in set (0.17 sec)

mysql> select id from artikelen where match(problem) against('TSM' in boolean mo
de);
Empty set (0.02 sec)

mysql> select id from artikelen where match(problem) against('LPAR' in boolean m
ode);
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
|  7 |
+----+
4 rows in set (0.00 sec)

In mysql.ini the ft_min_word_len is set to 3 (see below):

#This File was made using the WinMySQLAdmin 1.4 Tool
#13-5-2003 9:46:51

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=D:/Program Files/mysql
#bind-address=10.11.3.120
datadir=D:/Program Files/mysql/data
#language=D:/Program Files/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
ft_min_word_len=3
[WinMySQLadmin]
Server=D:/Program Files/mysql/bin/mysqld-nt.exe
user=root
[1 Aug 2003 4:07] Sergei Golubchik
could you please still check that

SHOW VARIABLES LIKE 'ft_%'

shows 3 ? There are very many "bugreports" where it happens out finally that my.cnf was not read by the server.

And be sure that indexes were built (or rebult) after ft_min_word_len was changed.

Try

REPAIR TABLE yourtable QUICK;

to be sure
[1 Aug 2003 6:37] Richard van Denzel
Thanks Sergei,

I think the REPAIR TABLE did the trick, although I completly reloaded the table from a dump (after I dropped it first and the dump contained the FULLTEXT index).

Case closed.
[1 Aug 2003 7:11] Alexander Keremidarski
Closed upon user request