Bug #79953 Fulltext-index not found in MySQL 5.7.10 when use GROUP_CONCAT
Submitted: 13 Jan 2016 13:31 Modified: 14 Jan 2016 6:50
Reporter: Oliver Beta Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.7.10 OS:Microsoft Windows (Win 2012R2)
Assigned to:
Tags: 1191, alias, fulltext, INDEX, subquery

[13 Jan 2016 13:31] Oliver Beta
Description:
MySQL 5.6.16 = no error and sql data result is fine
MySQL 5.7.9 and 5.7.10 = SQL-error

Example (see "How to repeat") returns: #1191 - Can't find FULLTEXT index matching the column list

The alias-column "bigtext" from the subquery is not used as fulltext-index when it's based on GROUP_CONCAT.

How to repeat:

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `my_text` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `my_text` (`my_text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

INSERT INTO testtable (id, my_text) VALUES (NULL, 'hello computer this is a test');

--

ERROR:
SELECT t1.id, t1.my_text from testtable t1 join (
   SELECT t2.id, t2.my_text,
    GROUP_CONCAT(t2.my_text SEPARATOR  ' ... ') as bigtext
     from testtable t2 
    ) x on x.id = t1.id
    where MATCH (x.bigtext) AGAINST ("computer" IN BOOLEAN MODE);

--

OK:
SELECT t1.id, t1.my_text from testtable t1 join (
   SELECT t2.id, t2.my_text as bigtext
     from testtable t2 
    ) x on x.id = t1.id
    where MATCH (x.bigtext) AGAINST ("computer" IN BOOLEAN MODE);
[14 Jan 2016 6:50] Umesh Shastry
Hello Oliver Beta,

Thank you for the report and test case.

Thanks,
Umesh