| Bug #71551 | ft_boolean_syntax has no impact on InnoDB FTS | ||
|---|---|---|---|
| Submitted: | 1 Feb 2014 16:02 | Modified: | 24 Jul 2014 20:35 |
| Reporter: | Jan Gils | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S2 (Serious) |
| Version: | 5.6.15, 5.6.21, 8.0.24 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | boolean, fts, innodb | ||
[24 Jul 2014 20:27]
Sveta Smirnova
Thank you for the report.
I cannot repeat described behavior and always get same result with '-' and '$'. Please provide repeatable test case: SHOW CREATE TABLE deliverables; few rows and sample output of queries SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new $york' IN BOOLEAN MODE); and SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new -york' IN BOOLEAN MODE);
If you use InnoDB please also try with current version 5.6.19: we fixed few Full Text bugs in InnoDB lately.
[24 Jul 2014 20:35]
Sveta Smirnova
I am sorry: missed bug title. Please disregard previous comment. Verified as described.
Even more: InnoDB behave inconsistently with MyISAM.
CREATE TABLE `deliverables` (
`Description` varchar(255) DEFAULT NULL,
FULLTEXT KEY `Description` (`Description`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> SELECT * FROM deliverables;
+---------------+
| Description |
+---------------+
| new york |
| new-york |
| cat |
| dog |
| new |
| new dog |
| new cat's dog |
| newcastle |
| newyork |
+---------------+
9 rows in set (0.00 sec)
mysql> select @@ft_boolean_syntax;
+---------------------+
| @@ft_boolean_syntax |
+---------------------+
| + $><()~*:""&| |
+---------------------+
1 row in set (0.00 sec)
InnoDB:
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new* -york' IN BOOLEAN MODE);
+---------------+
| Description |
+---------------+
| new |
| new dog |
| new cat's dog |
| newcastle |
| newyork |
+---------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new* $york' IN BOOLEAN MODE);
+---------------+
| Description |
+---------------+
| new york |
| new-york |
| new |
| new dog |
| new cat's dog |
| newcastle |
| newyork |
+---------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new $york' IN BOOLEAN MODE);
+---------------+
| Description |
+---------------+
| new york |
| new-york |
| new |
| new dog |
| new cat's dog |
+---------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new -york' IN BOOLEAN MODE);
+---------------+
| Description |
+---------------+
| new |
| new dog |
| new cat's dog |
+---------------+
3 rows in set (0.00 sec)
MyISAM:
mysql> alter table deliverables engine=myisam;
Query OK, 9 rows affected (0.42 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new $york' IN BOOLEAN MODE);
Empty set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new -york' IN BOOLEAN MODE);
+-------------+
| Description |
+-------------+
| new york |
| new-york |
+-------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new* $york' IN BOOLEAN MODE);
+-------------+
| Description |
+-------------+
| newcastle |
| newyork |
+-------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new* -york' IN BOOLEAN MODE);
+-------------+
| Description |
+-------------+
| newcastle |
| newyork |
+-------------+
2 rows in set (0.00 sec)
Description: The parameter ft_boolean_syntax has no impact on a boolean FTS search in InnoDB table: How to repeat: Change the "-" value by "$": ft_boolean_syntax=+ $><()~*:""&| and query in boolean mode: SELECT * FROM deliverables WHERE MATCH (Description) AGAINST ('+new* $york' IN BOOLEAN MODE) the "$" sign does not have the functionality of the old "-" sign (ignore word). Server was restarted after the change, FTS index was rebuild on the table Suggested fix: Implement a new parameter: innodb_ft_boolean_syntax=+ $><()~*:""&| inline with innodb_ft_min_token_size, innodb_ft_enable_stopword.