Bug #119986 Value of sys var temptable_max_mmap not honored unless temptable_use_mmap is ON

Description:
"WL#16179 Change default values for InnoDB variables" has disabled system variable `temptable_use_mmap`and deprecated it as well.

This worklog also changed the default value of `temptable_max_mmap` to 0 . 
If one wishes the TempTable engine to allocate memory mapped files 
for temporary tables, then one must set the valid non-zero value of this variable. 

In 8.4, despite setting the valid value of sys variable `temptable_max_mmap`,  TempTable doesn't create memory mapped files unless one turns ON the system variable `temptable_use_mmap`. It creates the temporary table from RAM to INNODB. 
This is not expected behavior as per the description of temptable_max_mmap in the manual : 
https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html

P.S. The variable `temptable_use_mmap` is later removed in 9.4.0. So this issue is specific to 8.4.x 

How to repeat:
Current behavior : 
------------------

(root@localhost) {18000} [test]> SET SESSION internal_tmp_mem_storage_engine = TempTable;                                                       
Query OK, 0 rows affected (0.00 sec)                                                                                                            

(root@localhost) {18000} [test]> SET GLOBAL temptable_max_ram = 2*1024*1024;
Query OK, 0 rows affected (0.00 sec) 

(root@localhost) {18000} [test]> SET GLOBAL temptable_max_mmap=18446744073709551615;
Query OK, 0 rows affected (0.00 sec) 

(root@localhost) {18000} [test]> SET GLOBAL temptable_use_mmap=OFF;
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost) {18000} [test]> SET SESSION cte_max_recursion_depth = 50000000;
Query OK, 0 rows affected (0.00 sec) 

(root@localhost) {18000} [test]> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec) 

(root@localhost) {18000} [test]> WITH RECURSIVE cte (c) AS
    ->   (SELECT 1 UNION ALL SELECT 1+c FROM cte WHERE c < 81901)
    -> SELECT MIN(c), MAX(c), AVG(c) FROM cte;
+--------+--------+------------+
| MIN(c) | MAX(c) | AVG(c)     |
+--------+--------+------------+
|      1 |  81901 | 40951.0000 |
+--------+--------+------------+
1 row in set (0.76 sec)

(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Apply the following patch : 

diff --git a/storage/temptable/include/temptable/allocator.h b/storage/temptable/include/temptable/allocator.h
index cef47b3df6c..d8e2d383df6 100644
--- a/storage/temptable/include/temptable/allocator.h
+++ b/storage/temptable/include/temptable/allocator.h
@@ -101,11 +101,7 @@ struct MemoryMonitor {
      *
      * @return MMAP-memory threshold. */
     static size_t threshold() {
-      if (temptable_use_mmap) {
         return temptable_max_mmap;
-      } else {
-        return 0;
-      }
     }
     /** Get current level of MMAP-backed memory consumption.
      *

and repeat the test again 

(root@localhost) {18000} [test]> SET SESSION internal_tmp_mem_storage_engine = TempTable;                                                              
Query OK, 0 rows affected (0.05 sec)                                                                                                            

(root@localhost) {18000} [test]> SET GLOBAL temptable_max_ram = 2*1024*1024;
Query OK, 0 rows affected (0.00 sec)                                    

(root@localhost) {18000} [test]> SET GLOBAL temptable_max_mmap=18446744073709551615;
Query OK, 0 rows affected (0.00 sec)                                    

(root@localhost) {18000} [test]> SET GLOBAL temptable_use_mmap=OFF;     
Query OK, 0 rows affected, 1 warning (0.00 sec)                         

(root@localhost) {18000} [test]> SET SESSION cte_max_recursion_depth = 50000000;
Query OK, 0 rows affected (0.00 sec)                                    

(root@localhost) {18000} [test]> FLUSH STATUS;                          
Query OK, 0 rows affected (0.00 sec)                                    

(root@localhost) {18000} [test]> WITH RECURSIVE cte (c) AS              
    ->   (SELECT 1 UNION ALL SELECT 1+c FROM cte WHERE c < 81901)       
    -> SELECT MIN(c), MAX(c), AVG(c) FROM cte;                          
+--------+--------+------------+    
| MIN(c) | MAX(c) | AVG(c)     |    
+--------+--------+------------+    
|      1 |  81901 | 40951.0000 |    
+--------+--------+------------+    
1 row in set (0.12 sec)             

(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 1     |
+--------------------+-------+
1 row in set (0.01 sec)

(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

Notice no table was created on the disk this time.

Hi Rahul,

Thanks for filing the bug. However, a contribution can be evaluated only when OCA is signed.

And also, contribution to be attached in "contribution" tab.

Regards
Mayank

Contribution to address the issue reported

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: contribution.patch (application/octet-stream, text), 1.07 KiB.