Bug #75538 | Multiple / Single phrase serach using Full Text In InnoDB is not working | ||
---|---|---|---|
Submitted: | 17 Jan 2015 19:54 | Modified: | 21 Jan 2015 7:27 |
Reporter: | PIYALI GHOSH DOSTIDAR | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: FULLTEXT search | Severity: | S2 (Serious) |
Version: | 5.7, 5.7.6 | OS: | Windows (Windows 8) |
Assigned to: | CPU Architecture: | Any | |
Tags: | Rick James, Umesh Umesh |
[17 Jan 2015 19:54]
PIYALI GHOSH DOSTIDAR
[18 Jan 2015 9:06]
MySQL Verification Team
Hello Piyali, Thank you for the report. Thanks, Umesh
[18 Jan 2015 9:07]
MySQL Verification Team
// 5.7.6 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS articles; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> body LONGTEXT, -> FULLTEXT (body) -> ) ENGINE=InnoDB; insert into articles(body) values("Transport Service"); Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into articles(body) values("Transport Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("Motor Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("Travel Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("A&O Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("B&C Servic"); Query OK, 1 row affected (0.00 sec) mysql> select * from articles where MATCH (body) Against ('"A&O Service"' in Boolean mode); +----+-------------------+ | id | body | +----+-------------------+ | 1 | Transport Service | | 2 | Motor Service | | 3 | Travel Service | | 4 | A&O Service | +----+-------------------+ 4 rows in set (0.00 sec) mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> DROP TABLE IF EXISTS articles; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> body LONGTEXT, -> FULLTEXT (body) -> ) ENGINE=myisam; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into articles(body) values("Transport Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("Motor Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("Travel Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("A&O Service"); Query OK, 1 row affected (0.00 sec) mysql> insert into articles(body) values("B&C Servic"); Query OK, 1 row affected (0.00 sec) mysql> select * from articles where MATCH (body) Against ('"A&O Service"' in Boolean mode); +----+-------------+ | id | body | +----+-------------+ | 4 | A&O Service | +----+-------------+ 1 row in set (0.00 sec)
[18 Jan 2015 9:09]
MySQL Verification Team
// How to repeat show variables like '%version%'; DROP TABLE IF EXISTS articles; CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, body LONGTEXT, FULLTEXT (body) ) ENGINE=InnoDB; insert into articles(body) values("Transport Service"); insert into articles(body) values("Motor Service"); insert into articles(body) values("Travel Service"); insert into articles(body) values("A&O Service"); insert into articles(body) values("B&C Servic"); select * from articles where MATCH (body) Against ('"A&O Service"' in Boolean mode); // show variables like '%version%'; DROP TABLE IF EXISTS articles; CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, body LONGTEXT, FULLTEXT (body) ) ENGINE=myisam; insert into articles(body) values("Transport Service"); insert into articles(body) values("Motor Service"); insert into articles(body) values("Travel Service"); insert into articles(body) values("A&O Service"); insert into articles(body) values("B&C Servic"); select * from articles where MATCH (body) Against ('"A&O Service"' in Boolean mode); // Per http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html '"some words"' Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words”)
[21 Jan 2015 7:27]
PIYALI GHOSH DOSTIDAR
Hi Team, Is there any update on this? Regards, Piyali