Bug #283 FULLTEXT index on a TEXT field converted to a CHAR field doesn't work anymore
Submitted: 13 Apr 2003 12:14 Modified: 23 Apr 2003 4:54
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.x OS:
Assigned to: Sergei Golubchik CPU Architecture:Any

[13 Apr 2003 12:14] jocelyn fournier 
Description:
When a TEXT field with FULLTEXT index is converted to a CHAR field, FULLTEXT search doesn't work anymore unless the FULLTEXT index is created again.

How to repeat:
In my.cnf, add :

set-variable    = ft_min_word_len=1
(I don't know if it changes anything for this bug, but it is set to 1 in my configuration)

Then :

CREATE TABLE `stocks1` (
  `ref_mag` TEXT NOT NULL default '',
  FULLTEXT KEY `ref_mag` (`ref_mag`)
) TYPE=MyISAM ROW_FORMAT=FIXED;

INSERT INTO stocks1 VALUES ('test');
Query OK, 1 row affected (0.01 sec)

SELECT ref_mag FROM stocks1 WHERE MATCH (ref_mag) AGAINST ('+test' IN BOOLEAN MODE) ORDER BY ref_mag;
+---------+
| ref_mag |
+---------+
| test    |
+---------+
1 row in set (0.00 sec)

=> OK

ALTER TABLE stocks1 CHANGE ref_mag ref_mag CHAR (255) NOT NULL;

SELECT ref_mag FROM stocks1 WHERE MATCH (ref_mag) AGAINST ('+test' IN BOOLEAN MODE) ORDER BY ref_mag;
Empty set (0.00 sec)

=> Fulltext search doesn't work anymore

ALTER TABLE stocks1 DROP KEY ref_mag, ADD FULLTEXT(ref_mag);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT ref_mag FROM stocks1 WHERE MATCH (ref_mag) AGAINST ('+test' IN BOOLEAN MODE) ORDER BY ref_mag;
+---------+
| ref_mag |
+---------+
| test    |
+---------+
1 row in set (0.00 sec)

=> OK
[13 Apr 2003 12:36] jocelyn fournier 
Hi,

Finally you don't have to modify the ft_min_word_len parameter in the my.cnf file to reproduce the bug.

Regards,
  Jocelyn
[14 Apr 2003 1:56] Lenz Grimmer 
Assigned to our FT expert - Serg, please verify.
[23 Apr 2003 4:54] Sergei Golubchik 
fixed in 4.0