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);
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);