Bug #110976 full-text index does not change when ngram parser changed
Submitted: 10 May 2023 13:09 Modified: 11 May 2023 9:10
Reporter: Ke Yu (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:8.0.33, 5.7.42 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[10 May 2023 13:09] Ke Yu
Description:
When the ngram parser is added to a full-text index without a parser using the inplace DDL algorithm in a separate DDL statement, the full-text index does not change, and data can not be selected by 'MATCH ... AGAINST ...' statement. When the copy DDL algorithm is used or the rebuilt DDL statement is divided into two DDL statement, the problem can be solved.

How to repeat:
step 1: create a table
CREATE TABLE t (name varchar(256)) ENGINE=InnoDB ; 

step 2: insert a record
insert into t values('goat勒布朗');

step 3: add a fulltext index without parser
ALTER TABLE t ADD FULLTEXT INDEX ft_index (name);

step 4: use the inplace algorithm to rebuild the index with ngram parser in a DDL statement
ALTER TABLE t
DROP INDEX ft_index ,
ADD FULLTEXT INDEX ft_index (name) WITH PARSER ngram , algorithm = inplace;

The rebuilt fulltext index does not have the ngram parser, and data can not be selected by 'MATCH ... AGAINST ...' statement :

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `name` varchar(256) DEFAULT NULL,
  FULLTEXT KEY `ft_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE MATCH(name) AGAINST ('goat');
Empty set (0.00 sec)

step 5: When the copy algorithm is used or rebuild the index by two DDL statement,  the index will be successfully rebuilt,  and data can be selected by 'MATCH ... AGAINST ...' statement:
ALTER TABLE t
DROP INDEX ft_index ,
ADD FULLTEXT INDEX ft_index (name) WITH PARSER ngram , algorithm = copy;
or
ALTER TABLE t DROP INDEX ft_index;
ALTER TABLE t ADD FULLTEXT INDEX ft_index (name) WITH PARSER ngram , algorithm = inplace;

the full-text index is changed with PARSER `ngram`:
mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `name` varchar(256) DEFAULT NULL,
  FULLTEXT KEY `ft_index` (`name`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

and data can be selected by 'MATCH ... AGAINST ...' statement: 
mysql> SELECT * FROM t WHERE MATCH(name) AGAINST ('goat');
+--------------+
| name         |
+--------------+
| goat勒布朗    |
+--------------+
1 row in set (0.00 sec)

Suggested fix:
Suggested fix:
When the full-text index's parser is changed, the ful-text index should be to be rebuilt.
I will give fixed code and some test case later.
[11 May 2023 8:51] MySQL Verification Team
Hello Ke Yu,

Thank you for the report and test case.

regards,
Umesh
[28 Jun 2023 7:59] Ke Yu
Contributed patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug110976.diff (application/octet-stream, text), 6.99 KiB.

[24 Jan 9:14] Ke Yu
This is my latest fix code, based on 8.0.33.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug_110976.diff (application/octet-stream, text), 7.01 KiB.