MySQL Bugs: #14738: FULLTEXT search gives no results when using only keywords with sign "-"

Bug #14738	FULLTEXT search gives no results when using only keywords with sign "-"
Submitted:	8 Nov 2005 10:54	Modified:	20 Jan 2006 18:20
Reporter:	Tomas Valinčius	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.15	OS:	Windows (Windows XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Mysql FULLTEXT search gives no results when using only the keywords with leading minus. For example:
SELECT * FROM sometable WHERE MATCH(somefield) AGAINST ('-keyword' IN BOOLEAN MODE);
I expect it to return all the rows from table, except the ones with a word "keyword", but it allways returns no matches, even if I have rows without the "keyword". When I use some more keywords (without "-") with "-keyword" everything works fine. For example:
SELECT * FROM sometable WHERE MATCH(somefield) AGAINST ('someword -keyword' IN BOOLEAN MODE);
or
SELECT * FROM sometable WHERE MATCH(somefield) AGAINST ('+someword -keyword' IN BOOLEAN MODE);
works fine.
So can I make a search using only keywords with "-"? Or must there also be some keywords without "-" if I want to exclude something from my search?

How to repeat:
FULLTEXT search doesn't work when using only the keywords with leading minus:
SELECT * FROM sometable WHERE MATCH(somefield) AGAINST ('-keyword' IN BOOLEAN MODE);

Could you please provide a complete test case script, table definition &
insert & query and the expected behavior.

Thanks in advance.

Here is the full test:

1) I create a table

CREATE TABLE `mytable` (
   `id` int(11) NOT NULL auto_increment,
   `sentence` text NOT NULL,
   PRIMARY KEY (`id`),
   FULLTEXT KEY `sentence` (`sentence`)
) ENGINE=MyISAM;

2) I insert some data into the table:

INSERT INTO mytable(sentence) VALUES ('I like yellowstone national park');
INSERT INTO mytable(sentence) VALUES ('I like national park');

3) I have the following data in mytable now:

SELECT * FROM mytable;
+----+-------------------------------+
|   id | sentence                              |
+----+-------------------------------+
|    1 | I like yellowstone national park |
|    2 | I like national park                  |
+----+-------------------------------+

4) I want to get all the rows where `sentence` does not have a word "yellowstone". As you can see, `sentence` with id=2 has to be a match, but here's what I get:

SELECT * FROM mytable WHERE MATCH(sentence) AGAINST('-yellowstone' IN BOOLEAN MODE);
Empty set (0.00 sec)

The following querry should return all rows, because none of them have given words, but here's what I get:

SELECT * FROM mytable WHERE MATCH(sentence) AGAINST('-childhood -brother' IN BOOLEAN MODE);
Empty set (0.00 sec)

5) But if I use at least one more word without "-", everything works fine:

SELECT * FROM mytable WHERE MATCH(sentence) AGAINST('national -yellowstone' IN BOOLEAN MODE);
+----+----------------------+
| id   | sentence                  |
+----+----------------------+
|  2   | I like national park      |
+----+----------------------+
1 row in set (0.00 sec)

SELECT * FROM mytable WHERE MATCH(sentence) AGAINST('-childhood -brother +park' IN BOOLEAN MODE);
+----+----------------------------------+
| id   | sentence                                   |
+----+----------------------------------+
|  1  | I like yellowstone national park       |
|  2  | I like national park                        |
+----+----------------------------------+
2 rows in set (0.02 sec)

6) So here comes my question - is it a bug or a search using only the words with leading minus is impossible?

It's curently a limitation of the fulltext search and for now will be
documented as limitation.

Thank you for the bug report.

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

Additional info:

I'll add a note to the section that documents the boolean
operators.

Note: The - operator acts only to exclude rows that are otherwise
matched by other search terms. Thus, a boolean-mode search that
contains only terms preceded by - returns an empty result. It does
not return "all rows except those containing any of the excluded
terms."