| 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: | Microsoft Windows (Windows 8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Rick James, Umesh Umesh | ||
[18 Jan 2015 9:06]
Umesh Shastry
Hello Piyali, Thank you for the report. Thanks, Umesh
[18 Jan 2015 9:07]
Umesh Shastry
// 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]
Umesh Shastry
// 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
Description: Suppose I have table Notes in InnoDB engine and the having a column Notes_Desc is LONGTEXT type. I have five records. The data of the Notes_Desc are as follows : 1. For 1st row a long text and the word "Transport Service" is present. 2. For 2nd record a long text and the word "Motor Service" is present. 3. For 3rd record a long text and the word "Travel Service" is present. 4. For 4th record a long text and the word "A&O Service" is present. 5. For 5th record a long text and the word "B&C Service" is present. Now when I run the query select * from Notes where MATCH (Notes_Desc) Against ('"A&O Service"' in Boolean mode); Its returning all the above 5 records which is incorrect. I created the same table in MyISAM engine and run the same query and its returning only one record which is correct. Please help me out as I have to use MySQL Server 5.7 and InnoDB engine for the table. How to repeat: Create a table Notes with one column Notes_Desc of LONGTEXT type for both the engine (InnoDB And MyISAM). Add 5 records as follows 1. For 1st row a long text and the word "Transport Service" is present. 2. For 2nd record a long text and the word "Motor Service" is present. 3. For 3rd record a long text and the word "Travel Service" is present. 4. For 4th record a long text and the word "A&O Service" is present. 5. For 5th record a long text and the word "B&C Service" is present. Now run the below query. select * from Notes where MATCH (Notes_Desc) Against ('"A&O Service"' in Boolean mode); It should return only One record for both case. Suggested fix: The way it is working for MyISAM engine it should also work for InnoDB engine too.