| Bug #87182 | Sporadic MATCH AGAINST results with unique index | ||
|---|---|---|---|
| Submitted: | 25 Jul 2017 8:27 | Modified: | 31 Jul 2017 12:37 |
| Reporter: | Chris Brown | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
| Version: | 5.6.35 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | fulltext, unique key | ||
[26 Jul 2017 12:45]
MySQL Verification Team
Hi, just to be sure I understand you properly, you add the index and run the query 100 times and you get 3 different results? thanks Bogdan
[27 Jul 2017 8:50]
Chris Brown
Hey Bogdan, Actually no, from my brief experimentation I've found that the query gives the same results when executed in succession, but rebuilding the schema or changing the query in some insignificant way (like adding superfluous brackets) can cause it to move to one of the other result states.
[31 Jul 2017 12:30]
MySQL Verification Team
Can't reproduce this on 5.7.19
[31 Jul 2017 12:37]
MySQL Verification Team
verified as describerd on 5.6.37
[31 Jul 2017 12:37]
MySQL Verification Team
[arhimed@localhost msb_5_6_37]$ ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost] {msandbox} ((none)) > use test
Database changed
mysql [localhost] {msandbox} (test) > CREATE TABLE `group` (
-> `group_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> `name` VARCHAR(256),
-> FULLTEXT INDEX `search` (`name`)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.39 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > CREATE TABLE `group_alias` (
-> `group_id` INT UNSIGNED NOT NULL,
-> `alias` VARCHAR(256),
-> CONSTRAINT `alias_group_id`
-> FOREIGN KEY (`group_id`)
-> REFERENCES `group` (`group_id`),
-> FULLTEXT INDEX `search` (`alias`)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.36 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > CREATE TABLE `item` (
-> `item_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> `group_id` INT UNSIGNED,
-> `name` VARCHAR(255) NOT NULL,
-> CONSTRAINT `item_group_id`
-> FOREIGN KEY (`group_id`)
-> REFERENCES `group` (`group_id`),
-> FULLTEXT INDEX `search` (`name`)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.38 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > CREATE TABLE `item_with_unique` LIKE `item`;
Query OK, 0 rows affected (0.36 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE `item_with_unique` ADD UNIQUE INDEX `unique_item_group` (`group_id`, `name`);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > INSERT INTO `group` (`group_id`, `name`) VALUES (1, 'Thompson');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `group` (`group_id`, `name`) VALUES (2, 'MacDonald');
Query OK, 1 row affected (0.02 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `group` (`group_id`, `name`) VALUES (3, 'Stewart');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (1, 'Tomson');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (2, 'Something');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (3, 'MacStewart');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (1, 1, 'MacTavish');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (2, 1, 'MacTavish; Red');
Query OK, 1 row affected (0.02 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (3, 2, 'MacAgnew');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (4, 3, 'Spider');
Query OK, 1 row affected (0.00 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (5, 2, 'blahblah');
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > INSERT INTO `item_with_unique` SELECT * FROM `item`;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
[31 Jul 2017 12:38]
MySQL Verification Team
mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name` FROM `item` `i` JOIN `group_alias` `g_a` USING (group_id) WHERE MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE) OR MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);
+---------+------------+----------------+
| item_id | group | name |
+---------+------------+----------------+
| 1 | Tomson | MacTavish |
| 2 | Tomson | MacTavish; Red |
| 3 | Something | MacAgnew |
| 4 | MacStewart | Spider |
+---------+------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name` FROM `item` `i` JOIN `group_alias` `g_a` USING (group_id) WHERE MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE) OR MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);
+---------+------------+----------------+
| item_id | group | name |
+---------+------------+----------------+
| 1 | Tomson | MacTavish |
| 2 | Tomson | MacTavish; Red |
| 3 | Something | MacAgnew |
| 4 | MacStewart | Spider |
+---------+------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name` FROM `item` `i` JOIN `group_alias` `g_a` USING (group_id) WHERE MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE) OR MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);
+---------+------------+----------------+
| item_id | group | name |
+---------+------------+----------------+
| 1 | Tomson | MacTavish |
| 2 | Tomson | MacTavish; Red |
| 3 | Something | MacAgnew |
| 4 | MacStewart | Spider |
+---------+------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > alter table item add index xx (`group_id`, `name`);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name` FROM `item` `i` JOIN `group_alias` `g_a` USING (group_id) WHERE MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE) OR MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);
+---------+------------+--------+
| item_id | group | name |
+---------+------------+--------+
| 4 | MacStewart | Spider |
+---------+------------+--------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.37 |
+-----------+
1 row in set (0.00 sec)
[31 Jul 2017 12:40]
MySQL Verification Team
Hi, Thanks for the bug submission. What I can suggest is upgrade to 5.7 as bug is fixed there. As for 5.6, I verified the bug with latest 5.6 but can't say when/if the fix will be available. best regards Bogdan Kecman p.s. fulltext index is not the most optimal index for what you are using it in this query
Description: I'm experiencing strange behaviour with fulltext search that I'm unable to find explanation for in the docs. The following query: SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name` FROM `item` `i` JOIN `group_alias` `g_a` USING (group_id) WHERE MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE) OR MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE); works fine until you add a unique index to (`group_id`, `name`) on `item`. At which point the query returns one of 3 result sets: 1. As if there was no where clause 2. As if there was no OR part to the where clause 3. Correct results Behaviour seems to be consistent to whichever of these 3 states it's in until the query is changed in some minor way (adding brackets, say) or the schema is rebuilt - at which point there's a chance it might change. Stackoverflow question for reference in case useful insight develops: https://stackoverflow.com/questions/45281641/mysql-sporadic-match-against-behaviour-with-u... How to repeat: SQLFiddle: http://sqlfiddle.com/#!9/8d95ba/18 Be aware that this fiddle MAY work first time, since that is one of the three states it varies between. In which case add whitespace to the schema, rebuild and rerun. I have replicated this fiddle on my local copy of mysql with the same behaviour.