| 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: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Apr 2013 16:08]
MySQL Verification Team
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.
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.