Bug #80723 @ Distance operator does not bring expected results when used in with wildcards
Submitted: 14 Mar 2016 8:40 Modified: 7 Apr 2016 7:23
Reporter: Thomas H. Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.23, 5.6.29, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: Distance, fulltext, innodb, wildcard

[14 Mar 2016 8:40] Thomas H.
Description:
I'm running a full text index on a rather huge InnoDB table which I'd like to run the following query against: 

[...] 
MATCH(doc_text) AGAINST ('"+capital* +market* +union*" @5' IN BOOLEAN MODE) 
[...] 

I would expect MySQL to return rows that contain the words "capital market union" as well as "capital markets union" in a max. distance of 5 words - in the second example please notice the 's' in markets, which should be covered with the wildcard.

However, it doesnt.

How to repeat:
1.) Set up an InnoDB table with a fulltext index
2.) Fill the FT column with the text

"hello this is my capital markets union test text"

3.) Run 

SELECT * FROM 
mytable
WHERE
MATCH(ft_column) AGAINST ('"+capital* +market* +union*" @5' IN BOOLEAN MODE);

 

Suggested fix:
Make the FT index consider wildcards in conjunction with the distance operator.
[7 Apr 2016 7:23] Umesh Shastry
Hello Thomas,

Thank you for the report.

Thanks,
Umesh