Bug #26265 Full text search for entries with ampersand returns nothing
Submitted: 11 Feb 2007 7:46 Modified: 7 Mar 2007 13:11
Reporter: Dan Cavina Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S4 (Feature request)
Version:5.0 OS:Microsoft Windows (Windows XP)
Assigned to:
Tags: ampersand
Triage: D5 (Feature request)

[11 Feb 2007 7:46] Dan Cavina
Description:
When using full text search, searching for a string containing an ampersand (e.g. "AT&T") does not return any results.

This is the case even when the full text search stop word length is changed from four to three.

How to repeat:
Create a table such as:

NAME VARCHAR(30)

And populate with various string, including "AT&T"

Search for the string using:

SELECT * FROM mytable MATCH ( Name ) AGAINST( 'AT&T')

This returns no matches.
[12 Feb 2007 10:55] Valerii Kravchuk
Thank you for a problem report. Sorry, but it looks like expected behaviour. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html (about ft_boolean_syntax). '&' is treated as operator.
[12 Feb 2007 20:01] Dan Cavina
I should have also mentioned that this occurs even if the text containing the ampersand is enclosed in quotes. I would expect that any symbol contained within the quotes would not special behavior.
[6 Mar 2007 15:29] Ben Byrne
I'm having the same problem on Debian running 4.1.11 (in fact, it was even a search for "AT&T" that brought the problem to my attention.

As per the documentation, I have changed the value of ft_boolean_syntax to remove the ampersand, first via SET GLOBAL VARIABLE, and then through editing my.cnf. At present,

ft_boolean_syntax = + -><()~*:""^|

So there is no longer an ampersand present, it has been replaced by a carat. Unfortunately, this has not resolved the problem -- a boolean fulltext search for "AT&T" still returns no values.

Either I misunderstood the documentation on ft_boolean_syntax, or this should be reclassified as a bug.
[6 Mar 2007 18:54] Dan Cavina
The ability to search for company names such as "AT&T" and "M & K Speakers" in text fields is pretty important. I am changing the status to Open hoping this can be looked at again.
[7 Mar 2007 13:11] Sergei Golubchik
We cannot change the fulltext parser in 5.0.
In 5.1 a fulltext parser can be in a plugin, and any behaviour can be implemented by the user.
[8 Mar 2007 22:19] Ben Byrne
Perhaps the documentation on ft_boolean_syntax should be updated to reflect that there's not much point in changing its value, then?