Bug #39826 Full-text search query hangs with specific wildcard search term combination
Submitted: 2 Oct 2008 21:42 Modified: 3 Nov 2008 0:32
Reporter: Rhonda Saylor Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.1.26 OS:Linux
Assigned to: CPU Architecture:Any

[2 Oct 2008 21:42] Rhonda Saylor
Description:
We are running MySQL 5.1.26 in our production environment.  We recently ran across what appears to be a bug related to performing searches against full-text indexes.

We have a search term for which we wanted to find occurences of the word "oracle" with either of "db*" or "dbase*".  The search term looked like this:

SELECT id FROM indexed_table
WHERE MATCH(full_text_indexed_column) AGAINST ('+oracle +(dbase* db*)' IN BOOLEAN MODE);

The query would hang the database process every time.  After random poking around we discovered that the following query WORKED (note that the order of the two wildcard search terms is reversed): 

SELECT id FROM indexed_table
WHERE MATCH(full_text_indexed_column) AGAINST ('+oracle +(db* dbase*)' IN BOOLEAN MODE);

We have the minimum word length set to 3 for full text indexing, and we think this is somehow related but aren't sure how or why.
(We recognize that the search terms are redundant, and have since repaired the query, but thought we should bring the hanging problem to your attention.)

How to repeat:
Do a query on a full-text indexed column as described above.

Suggested fix:
We aren't sure.  Seems related to the minimum word-size, and to having redundant wildcard search terms in the same query.
[3 Oct 2008 0:32] MySQL Verification Team
Thank you for the bug report. Could you please provide the dump file with data insert commands of the table. Thanks in advance.
[4 Nov 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".