Bug #68720 Literal phrases cannot be combined with + or - operator
Submitted: 19 Mar 2013 16:18 Modified: 10 Jun 2013 18:05
Reporter: Jan Rusch Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.10, 5.6.12, 5.7.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: Boolean Full-Text Search, fts, fulltext

[19 Mar 2013 16:18] Jan Rusch
When searching for a phrase literally like "some words", the literal phrase token "some words" cannot be combined with + or -. In combination with a group clause inconsistent results are returned.

How to repeat:
A row contains a column with the following content:

...Dichtung AGR Ventil # Seal EGR valve # Joint vanne EGR #...

The column is included in the fulltext index.

A search for '"EGR valve"' returns correctly the given row.

However, combining the search token '"EGR valve"' with any combination of + returns no result:

1. +"EGR valve" --> FAIL, No hit
2. +("EGR valve") -> FAIL, No hit

3. ("EGR valve" seal) -> OK, hit
4. (seal "EGR valve") -> OK, hit

5. +(seal "EGR valve") -> FAIL, No hit
6. +("EGR valve" seal) -> FAIL, No hit

7. +("EGR valve" "AGR Ventil") -> FAIL, No hit

One can point out, that the + (or -) is not allowed in combination with literal tokens, but then, if combined with a search containing a group clause, the group clause shouldn't work either. As cases 3 & 4 return a correct result, I would assume that also 6 & 7 would return a hit as the group clause contains at least the match on 'seal', but even this returns no hit.

Suggested fix:
Fix InnoDB FTS to return consistent results or state that literal phrase MUST no be used in combination with + or -.
[19 Mar 2013 19:06] Sveta Smirnova
Thank you for the report.

Verified as described. MiISAM handles this case correctly.

Test case for MTR:

--source include/have_innodb.inc

create table t1(f1 text, fulltext(f1)) engine=myisam;
create table t2(f1 text, fulltext(f1)) engine=innodb;

insert into t1 values('some text');
insert into t1 values('other text');
insert into t1 values('some test');
insert into t1 values('other test');
insert into t1 values('foo bar');

insert into t2 select * from t1;

select * from t1 where match(f1) against('"some text"+' in boolean mode);
select * from t2 where match(f1) against('"some text"+' in boolean mode);
[10 Jun 2013 18:05] Paul DuBois
Noted in 5.6.13, 5.7.2 changelogs.

Full-text search on InnoDB tables failed on searches for literal
phrases combined with + or - operators.