Description:
To whom it may concern:
I do not believe the NOT (-) operator works properly with phrases preserving the word
order. As part of my testing before filing this bug, I tested the phrase matching
behavior in terms of OR and AND (+) operators to establish a baseline of expected
behavior. It seems that the NOT (-) operator does enforce the exclusion of rows
containing words in the phrase, but the exclusion does not take order or location of the
words into account (as the OR and AND (+) matching does).
Thank you for any assistance in this matter in advance.
Sincerely,
Thomas S. Chin
How to repeat:
mysql> CREATE TABLE test (data TEXT, FULLTEXT(data));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test VALUES ('foo bar');
Query OK, 1 row affected (0.00 sec)
mysql> -- expect 1 row
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+"foo bar"' IN BOOLEAN MODE);
+---------+
| data |
+---------+
| foo bar |
+---------+
1 row in set (0.00 sec)
mysql> -- expect empty
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+"bar foo"' IN BOOLEAN MODE);
Empty set (0.00 sec)
mysql> -- expect 1 row
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"foo bars"' IN BOOLEAN
MODE);
+---------+
| data |
+---------+
| foo bar |
+---------+
1 row in set (0.00 sec)
mysql> -- expect 1 row (*** unexpected result ***)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bar foo"' IN BOOLEAN
MODE);
Empty set (0.00 sec)
mysql> -- expect 1 row
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bars foo"' IN BOOLEAN
MODE);
+---------+
| data |
+---------+
| foo bar |
+---------+
1 row in set (0.00 sec)
Suggested fix:
I believe the following query (in the provided example) should return 1 results:
SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bar foo"' IN BOOLEAN MODE);
Description: To whom it may concern: I do not believe the NOT (-) operator works properly with phrases preserving the word order. As part of my testing before filing this bug, I tested the phrase matching behavior in terms of OR and AND (+) operators to establish a baseline of expected behavior. It seems that the NOT (-) operator does enforce the exclusion of rows containing words in the phrase, but the exclusion does not take order or location of the words into account (as the OR and AND (+) matching does). Thank you for any assistance in this matter in advance. Sincerely, Thomas S. Chin How to repeat: mysql> CREATE TABLE test (data TEXT, FULLTEXT(data)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test VALUES ('foo bar'); Query OK, 1 row affected (0.00 sec) mysql> -- expect 1 row mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+"foo bar"' IN BOOLEAN MODE); +---------+ | data | +---------+ | foo bar | +---------+ 1 row in set (0.00 sec) mysql> -- expect empty mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+"bar foo"' IN BOOLEAN MODE); Empty set (0.00 sec) mysql> -- expect 1 row mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"foo bars"' IN BOOLEAN MODE); +---------+ | data | +---------+ | foo bar | +---------+ 1 row in set (0.00 sec) mysql> -- expect 1 row (*** unexpected result ***) mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bar foo"' IN BOOLEAN MODE); Empty set (0.00 sec) mysql> -- expect 1 row mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bars foo"' IN BOOLEAN MODE); +---------+ | data | +---------+ | foo bar | +---------+ 1 row in set (0.00 sec) Suggested fix: I believe the following query (in the provided example) should return 1 results: SELECT data FROM test WHERE MATCH(data) AGAINST('foo -"bar foo"' IN BOOLEAN MODE);