Bug #86036 InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit

Description:
According to the manual (https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_result_cac...):

"The InnoDB full-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and final InnoDB full-text search query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very large InnoDB full-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.

The maximum value of innodb_ft_result_cache_limit for all platform types and bit sizes is 2**32-1."

This limit applies to all recent versions (inclusing 5.6.x as well). This is a problem because it seems internally this limit is checked even for the queries that should not need that much memory:

mysql> show create table ft\G
*************************** 1. row ***************************
       Table: ft
Create Table: CREATE TABLE `ft` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `k` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524267 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> show table status like 'ft'\G
*************************** 1. row ***************************
           Name: ft
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 386100
 Avg_row_length: 305
    Data_length: 118095872
Max_data_length: 0
   Index_length: 7880704
      Data_free: 7340032
 Auto_increment: 524267
    Create_time: 2017-04-21 12:51:00
    Update_time: 2017-04-21 12:52:54
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.03 sec)

mysql> set global innodb_ft_result_cache_limit=10000000;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select id from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit
mysql> explain select * from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit

As you can see, we get the error even for EXPLAIN, even though the query clearly has LIMIT 5 clause and there is no need to store more than 5 rows (255 bytes or so each) or final or intermediate results. 

With 4G absolute limit one just can not efficiently use FULLTEXT search for any table with total data size >4G, but then what's the point?

How to repeat:
[openxs@fc23 5.7]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ft(id int auto_increment primary key, c1 varchar(255), fulltext key k(c1)) engine=InnoDB;
Query OK, 0 rows affected (2.11 sec)

mysql> insert into ft(c1) values (repeat('order', 50));
Query OK, 1 row affected (0.03 sec)

mysql> insert into ft(c1) values (repeat('order ', 40));
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft(c1) values (repeat('nothin', 40));
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft(c1) select c1 from ft;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

...

mysql> insert into ft(c1) select c1 from ft;
Query OK, 196608 rows affected (12.51 sec)
Records: 196608  Duplicates: 0  Warnings: 0

mysql> show table status like 'ft'\G
*************************** 1. row ***************************
           Name: ft
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 386100
 Avg_row_length: 305
    Data_length: 118095872
Max_data_length: 0
   Index_length: 7880704
      Data_free: 7340032
 Auto_increment: 524267
    Create_time: 2017-04-21 12:51:00
    Update_time: 2017-04-21 12:52:54
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.03 sec)

mysql> set global innodb_ft_result_cache_limit=10000000;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select id from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit
mysql> explain select * from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit

What's interesting is that we have error even for EXPLAIN, no matter what LIMIT is set, and even if we do NOT really select long varcvhar(255) column - just integer primary key.

The only workaround is to increase global innodb_ft_result_cache_limit to make it closer to the total data size:

mysql> set global innodb_ft_result_cache_limit=100000000;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from ft where match(c1) against('order') limit 5;
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
|  1 | SIMPLE      | ft    | NULL       | fulltext | k             | k    | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted, limit = 5 |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.19 sec)

mysql> explain select id from ft where match(c1) against('order') limit 5;
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
|  1 | SIMPLE      | ft    | NULL       | fulltext | k             | k    | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted, limit = 5 |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.21 sec)

mysql> select id from ft where match(c1) against('order') limit 5;
+----+
| id |
+----+
|  2 |
|  5 |
|  8 |
| 11 |
| 15 |
+----+
5 rows in set (0.28 sec)

mysql> show create table ft\G
*************************** 1. row ***************************
       Table: ft
Create Table: CREATE TABLE `ft` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `k` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524267 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

Then everything works, but as this parameter must be <4G on all platfroms, we can not use FULLTEXT indexes efficiently for any big table no matter how much RAM we have.

Suggested fix:
Please, increase the maximum possible value for innodb_ft_result_cache_limit, so that it can be >4G!

Alternatively, improve optimizer so it does NOT require that huge cache from intermediate results when we need just few rows from the table.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 Source distribution 2017-apr-19

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE test
Database changed
mysql 5.7 > set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 >  create table ft(id int auto_increment primary key, c1 varchar(255), fulltext key k(c1)) engine=InnoDB;
Query OK, 0 rows affected (2.11 sec)

mysql 5.7 > insert into ft(c1) values (repeat('order', 50));
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > insert into ft(c1) values (repeat('order ', 40));
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > insert into ft(c1) values (repeat('nothin', 40));
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 48 rows affected (0.10 sec)
Records: 48  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 96 rows affected (0.06 sec)
Records: 96  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 192 rows affected (0.27 sec)
Records: 192  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 384 rows affected (0.17 sec)
Records: 384  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 768 rows affected (0.37 sec)
Records: 768  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 1536 rows affected (0.57 sec)
Records: 1536  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 3072 rows affected (0.07 sec)
Records: 3072  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 6144 rows affected (0.13 sec)
Records: 6144  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 12288 rows affected (0.40 sec)
Records: 12288  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 24576 rows affected (2.16 sec)
Records: 24576  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 49152 rows affected (1.85 sec)
Records: 49152  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 98304 rows affected (4.37 sec)
Records: 98304  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into ft(c1) select c1 from ft;
Query OK, 196608 rows affected (13.55 sec)
Records: 196608  Duplicates: 0  Warnings: 0

mysql 5.7 > show table status like 'ft'\G
*************************** 1. row ***************************
           Name: ft
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 385999
 Avg_row_length: 311
    Data_length: 120209408
Max_data_length: 0
   Index_length: 7880704
      Data_free: 5242880
 Auto_increment: 524267
    Create_time: 2017-04-22 21:39:08
    Update_time: 2017-04-22 21:41:55
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql 5.7 > set global innodb_ft_result_cache_limit=10000000;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > explain select id from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit
mysql 5.7 > explain select * from ft where match(c1) against('order') limit 5;
ERROR 188 (HY000): FTS query exceeds result cache limit
mysql 5.7 > set global innodb_ft_result_cache_limit=100000000;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > explain select * from ft where match(c1) against('order') limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft
   partitions: NULL
         type: fulltext
possible_keys: k
          key: k
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: sorted, limit = 5
1 row in set, 1 warning (0.12 sec)

mysql 5.7 > explain select id from ft where match(c1) against('order') limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft
   partitions: NULL
         type: fulltext
possible_keys: k
          key: k
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: sorted, limit = 5
1 row in set, 1 warning (0.08 sec)

mysql 5.7 >  select id from ft where match(c1) against('order') limit 5;
+----+
| id |
+----+
|  2 |
|  5 |
|  8 |
| 11 |
| 15 |
+----+
5 rows in set (0.17 sec)

mysql 5.7 > show create table ft\G
*************************** 1. row ***************************
       Table: ft
Create Table: CREATE TABLE `ft` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `k` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524267 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Thank you for the bug report.

https://bugs.mysql.com/bug.php?id=93168 marked as duplicate of this one.