MySQL Bugs: #6706: FT search returns incorrect results when a ' is used

Bug #6706	FT search returns incorrect results when a ' is used
Submitted:	18 Nov 2004 21:31	Modified:	19 Nov 2004 20:05
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Hi,

FT search returns an incorrect result when a ' is used at the end of the searched word altough ' is not part of the ft_boolean_syntax characters.

Regards,
  Jocelyn

How to repeat:
DROP TABLE IF EXISTS t1;

CREATE TABLE `t1` (
  `ft_col` text NOT NULL,
  FULLTEXT KEY (`ft_col`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES ('test1 ftbug1');

SELECT * FROM t1 WHERE MATCH (ft_col) AGAINST ('test1\'' IN BOOLEAN MODE);

+--------------+
| ft_col       |
+--------------+
| test1 ftbug1 |
+--------------+
1 row in set (0.00 sec)

However this one works correctly :

SELECT * FROM t1 WHERE MATCH (ft_col) AGAINST ('test1\'0' IN BOOLEAN MODE);
Empty set (0.00 sec)

INSERT INTO t1 VALUES ('test1\'0');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM t1 WHERE MATCH (ft_col) AGAINST ('test1\'0' IN BOOLEAN MODE);
+---------+
| ft_col  |
+---------+
| test1'0 |
+---------+
1 row in set (0.00 sec)

Verified with 4.1.8-debug-log

It's a feature, not a bug :)

A "word" for the parser is any sequence of "true" word characters (letters, digits, and underscore) that could be optionally separated by no more than one "misc" word character (single quote).

This means that
   ABC'EDF           is parsed as one word "ABC'DEF"
   ABC''EDF          is parsed as two words "ABC" and "EF"
   'ABCEDF'          is parsed as one word "ABCDEF"

Paul, could you please document it ?

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).