MySQL Bugs: #3150: match against query does not work with "abc-f" but works with "abcd-f"

Bug #3150	match against query does not work with "abc-f" but works with "abcd-f"
Submitted:	11 Mar 2004 20:53	Modified:	15 Mar 2004 4:09
Reporter:	murugesan dinesh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	Mysql - 4.0.15.	OS:	Linux (RHL 8.0)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When I executed the query in mysql client,

select  * from table_name where match(field1,field2) against('abc-f');
it gives me nothing

but
when the query
select  * from table_name where match(field1,field2) against('abcd-f');
it gives me the results,
In /etc/my.cnf I have ft_min_word_length being set to 3.

Kindly clarify me If I am wrong here.

How to repeat:
have the field1,field2 being set to the following values

field1=abcd-a
field2=abc-def

with field1 & field2 set as FULL TEXT indices.
now do

>select * from table where match(field1,field2) against("abcd-a")
This returns the values
then do

>select * from table where match(field1,field2) against("abc-def")
returns nothing

I need to know which specific version you are using, and we need a complete test case with the SQL to create, populate and query a table which demonstrates this issue.  Also please ensure that you are not simply encountering the 50% threshhold.

My server version is 4.0.15.

mysql> select * from test;
+---------+-------+
| tt      | tt1   |
+---------+-------+
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("ABCD-EF" in boolean mode);
+---------+-------+
| tt      | tt1   |
+---------+-------+
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
+---------+-------+
6 rows in set (0.01 sec)

mysql> select * from test where match(tt,tt1) against("EF-GH" in boolean mode);
Empty set (0.00 sec)

mysql> update test set tt="ABC-EFG";
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test where match(tt,tt1) against("ABC-EFG" in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("ABC-EFGH" in boolean mode);
+----------+-------+
| tt       | tt1   |
+----------+-------+
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
+----------+-------+
6 rows in set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("A-EFGH" in boolean mode);
+--------+-------+
| tt     | tt1   |
+--------+-------+
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
+--------+-------+
6 rows in set (0.00 sec)

mysql> update test set tt="-EFGH";
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test where match(tt,tt1) against("-EFGH" in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("\-EFGH" in boolean mode);
Empty set (0.00 sec)

Let me know if I am not clear here.

Thanks,
Murugesan

Oops! I missed an update statement in the mid of the report. Here I am posting it again. 

My server version is 4.0.15.

mysql> select * from test;
+---------+-------+
| tt      | tt1   |
+---------+-------+
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("ABCD-EF" in boolean
mode);
+---------+-------+
| tt      | tt1   |
+---------+-------+
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
| ABCD-EF | EF-GH |
+---------+-------+
6 rows in set (0.01 sec)

mysql> select * from test where match(tt,tt1) against("EF-GH" in boolean mode);
Empty set (0.00 sec)

mysql> update test set tt="ABC-EFG";
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test where match(tt,tt1) against("ABC-EFG" in boolean
mode);
Empty set (0.00 sec)

mysql> update test set tt="ABC-EFGH";
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test where match(tt,tt1) against("ABC-EFGH" in boolean
mode);
+----------+-------+
| tt       | tt1   |
+----------+-------+
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
| ABC-EFGH | EF-GH |
+----------+-------+
6 rows in set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("A-EFGH" in boolean
mode);
+--------+-------+
| tt     | tt1   |
+--------+-------+
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
| A-EFGH | EF-GH |
+--------+-------+
6 rows in set (0.00 sec)

mysql> update test set tt="-EFGH";
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test where match(tt,tt1) against("-EFGH" in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(tt,tt1) against("\-EFGH" in boolean
mode);
Empty set (0.00 sec)

Thanks,
Murugesan

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

select * from test where match(tt,tt1) against("EF-GH" in boolean mode)

finds nothing because you apparently have ft_min_word_len=4 (default value), thus 2-letter words are not indexed. Dash is not a valid word char.

Same for

select * from test where match(tt,tt1) against("ABC-EFG" in boolean mode);

As for

select * from test where match(tt,tt1) against("-EFGH" in boolean mode);

it finds nothing because dash '-' is treated as a boolean operator. There is no escaping in fulltext search (yet).

If you'll search for

select * from test where match(tt,tt1) against("EFGH" in boolean mode);

It'll work as you expect it