Bug #68944 Innodb fulltext problem matching phrases in boolean mode.

• Submitted: 12 Apr 2013 18:03
• Modified: 12 May 2013 22:10
• Reporter: A Fiddaman
• Status: No Feedback
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.6.10
• OS: Solaris (10 update 11)
• CPU Architecture: Any

[12 Apr 2013 18:03] A Fiddaman

Description:
Innodb fulltext searching does not seem to be working for phrases, such as +"some words", in boolean mode. Other types of search seem to work fine and the boolean phrases work fine with a MyISAM table.

Having set the innodb_ft_enable_diag_print option on:

keynr=7, '+"House Doctor"'
BOOL search
'doctor' -> 379362/1166 2.51236
'house' -> 379362/1739 2.33875
FTS Search Processing time: 0 secs: 14 millisec: row(s) -1

eynr=7, '+House +Doctor'
BOOL search
'doctor' -> 379362/1166 2.51236
'house' -> 379362/1739 2.33875
FTS Search Processing time: 0 secs: 6 millisec: row(s) 496

keynr=7, '+"House Doctor"'
NL search
'+' -> 379362/0 0.00000
'doctor' -> 379362/1166 2.51236
'house' -> 379362/1739 2.33875
FTS Search Processing time: 0 secs: 15 millisec: row(s) 496

How to repeat:
Table structure:

CREATE TABLE `epg` (
`service_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`event_id` bigint(20) unsigned NOT NULL DEFAULT '0',
...
`name` varchar(255) DEFAULT NULL,
`text` varchar(255) DEFAULT NULL,
...
PRIMARY KEY (`service_id`,`event_id`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `text` (`text`),
FULLTEXT KEY `name_2` (`name`,`text`)
) ENGINE=XXX DEFAULT CHARSET=latin1

With MyISAM:

mysql> select name, match(name) against('+"House Doctor"' in boolean mode) as score from epg where name = 'House Doctor';
+--------------+-------+
| name | score |
+--------------+-------+
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
...

Whereas with an InnoDB table, same structure, same data, same query:

+--------------+-------+
| name | score |
+--------------+-------+
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |

If I drop the 'in boolean mode', then:

+--------------+--------------------+
| name | score |
+--------------+--------------------+
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |

and trying against('+House +Doctor' in boolean mode):

+--------------+--------------------+
| name | score |
+--------------+--------------------+
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |

as expected, so I can't see why the +"House Doctor" phrase isn't working.

mysql> show variables like '%ft%';
+---------------------------------+----------------+
| Variable_name | Value |
+---------------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | OFF |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 2 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_user_stopword_table | |
+---------------------------------+----------------+
15 rows in set (0.00 sec)

[12 Apr 2013 22:10] MySQL Verification Team

Thank you for the bug report. Could you please provide the dump of the table to insert data and run the queries. Thanks.

[13 May 2013 1: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".