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:
None 
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
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.
[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