MySQL Bugs: #68720: Literal phrases cannot be combined with + or

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:	None
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

Description:
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 -.

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);

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.