MySQL Bugs: #26265: Full text search for entries with ampersand returns nothing

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:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	ampersand

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.

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.

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.

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.

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.

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.

Perhaps the documentation on ft_boolean_syntax should be updated to reflect that there's not much point in changing its value, then?