Bug #94709 Regression behavior for full text index
| Submitted: | 19 Mar 2019 19:41 | Modified: | 20 Mar 2019 7:57 |
|---|---|---|---|
| Reporter: | Carlos Tutte | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
| Version: | 5.7.21,5.7.25, 8.0.15, 5.6.43 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[20 Mar 2019 7:57]
MySQL Verification Team
Hello Carlos Tutte, Thank you for the report and test case. Thanks, Umesh
[20 Mar 2019 8:00]
Nikolai Ikhalainen
Hi, 5.7.21+ contains the fix: InnoDB: Full-text search on indexed columns that use a binary collation did not return case-sensitive matches. (Bug #21625016, Bug #78048) SET GLOBAL innodb_ft_aux_table='test/posts_translations_copy2'; mysql> select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE where word like '%ring'; | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | Spring | 2 | 2 | 1 | 2 | 8 | In 5.7.20: | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | spring | 2 | 2 | 1 | 2 | 8 | After alter table posts_translations_copy2 modify column content mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | spring | 2 | 2 | 1 | 2 | 8 | Even after set names utf8mb4 COLLATE utf8mb4_bin; It looks like a full text search in boolean mode for double quotes is trying to find lowercase phrase even if we are providing a correct case in AGAINST.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Creating a table with full text index gives different query result than adding fulltext index after table creation when using that index. The behavior changed from 5.7.20 to 5.7.21 How to repeat: The following 2 test scenarios give 1 result each for MySQL versions up to 5.7.20, and only 1st test case returns a result after 5.7.21 ---------------------------------------------------------- use test; ---------------------------------------------------------- ------------------------ 1 result ------------------------ DROP TABLE IF EXISTS `posts_translations_copy2`; CREATE TABLE `posts_translations_copy2` ( `id` int(11) unsigned NOT NULL, `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, PRIMARY KEY (`id`), FULLTEXT KEY `searchablepc` (`content`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `posts_translations_copy2` (`id`, `content`) VALUES (1,'content Spring fling'); SELECT count(*) FROM `test`.`posts_translations_copy2` `PostsTranslations` USE INDEX (searchablepc) WHERE ((MATCH(PostsTranslations.content) AGAINST('"Spring fling"' in boolean mode))) ORDER BY `PostsTranslations`.`id` DESC LIMIT 20 OFFSET 0; ---------------------------------------------------------- ------------------------ 0 result ------------------------ DROP TABLE IF EXISTS `posts_translations_copy2`; CREATE TABLE `posts_translations_copy2` ( `id` int(11) unsigned NOT NULL, `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `posts_translations_copy2` (`id`, `content`) VALUES (1,'content Spring fling'); CREATE FULLTEXT INDEX searchablepc ON posts_translations_copy2(content); SELECT count(*) FROM `test`.`posts_translations_copy2` `PostsTranslations` USE INDEX (searchablepc) WHERE ((MATCH(PostsTranslations.content) AGAINST('"Spring fling"' in boolean mode))) ORDER BY `PostsTranslations`.`id` DESC LIMIT 20 OFFSET 0; ------------------------------------------------------------ Suggested fix: This is a regression as it changes how query behaves. I suggest returning same result as prior versions for version >= MySQL 5.7.21 , or add a documentation bug showing this incompatible change.