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:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.6.15, 5.6.21 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: boolean, fts, innodb
Triage: Needs Triage: D2 (Serious)

[1 Feb 2014 16:02] Jan Gils
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.
[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)