Bug #69032 InnoDB FTS differs with multiple boolean operators
Submitted: 22 Apr 2013 15:59 Modified: 17 May 2013 19:29
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[22 Apr 2013 15:59] Todd Farmer
Description:
InnoDB FTS accepts boolean modifiers, but the results differ if multiple modifiers are given per word:

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('arugula' IN BOOLEAN MODE);
+--------------------------+
| a                        |
+--------------------------+
| arugula basalt chameleon |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('+arugula' IN BOOLEAN MODE);
+--------------------------+
| a                        |
+--------------------------+
| arugula basalt chameleon |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('++arugula' IN BOOLEAN MODE);
Empty set (0.00 sec)

This differs from MyISAM behavior:

mysql> ALTER TABLE ft ENGINE = MyISAM;
Query OK, 3 rows affected (0.36 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('++arugula' IN BOOLEAN MODE);
+--------------------------+
| a                        |
+--------------------------+
| arugula basalt chameleon |
+--------------------------+
1 row in set (0.06 sec)

It's worth noting that MyISAM seems to ignore all modifiers except that immediately adjacent to the word:

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('-arugula' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('- arugula' IN BOOLEAN MODE);
+--------------------------+
| a                        |
+--------------------------+
| arugula basalt chameleon |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('-+arugula' IN BOOLEAN MODE);
+--------------------------+
| a                        |
+--------------------------+
| arugula basalt chameleon |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ft
    -> WHERE MATCH(a) AGAINST ('+-arugula' IN BOOLEAN MODE);
Empty set (0.00 sec)

Whether this is "correct" is questionable, and should be reviewed.

How to repeat:
See above

Suggested fix:
Clarify "correct" behavior for boolean fulltext modifier handling.
[22 Apr 2013 16:08] Shane Bester
same on 5.6.11, testcase:

drop table if exists ft;
create table ft(a text character set latin1,fulltext key(a))engine=innodb;
insert into ft values ('arugula basalt chameleon');
select * from ft where match(a) against('arugula' in boolean mode);
select * from ft where match(a) against('+arugula' in boolean mode);
select * from ft where match(a) against('++arugula' in boolean mode);
alter table ft engine=myisam;
select * from ft where match(a) against('arugula' in boolean mode);
select * from ft where match(a) against('+arugula' in boolean mode);
select * from ft where match(a) against('++arugula' in boolean mode);
[17 May 2013 19:29] Bugs System
Added information to 5.6/5.7 reference manual regarding support of multiple boolean operators:

5.6:

"InnoDB" full-text search does not support the use of multiple
operators on a single search word, as in this example: "'++apple'.".
MyISAM full-text search will successfully process the same search ignoring
all operators except for the operator immediately adjacent to the search
word.

5.7:

"InnoDB" full-text search does not support the use of multiple operators
on a single search word, as in this example: "'++apple'." Use of multiple
operators on a single search word returns a syntax error to standard out.
MyISAM full-text search will successfully process the same search ignoring
all operators except for the operator immediately adjacent to the search
word.

Thank you for your report.