WL#6711: Support InnoDB as additional storage engine for tmp table

Status: Complete

Optimizer should allow the use of InnoDB for temp usage.

InnoDB temporary tables should also aupport Uniques, or similar algorithm, for
faster resolution of DISTINCT queries and for multi-table DML's. 

User Documentation

Functional requirement:
1. User can configure optimizer to use intrinsic temp-table for all its existing
use-cases between MyISAM and InnoDB.
2. With introduction of InnoDB temp-table, there shouldn't be any Optimizer
query plan and result changes.
3. One optimizer trace for intrinsic InnoDB temp table usage is added which is
the same as trace for intrinsic MyISAM temp table.
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 11,
                "key_length": 10,
                "unique_constraint": false,
                "location": "disk (InnoDB)",
                "record_format": "fixed"

Non-functional requirements:
Definition of Intrinsic Temporary Table
This is special kind temporary table created internally by modules
such as Optimizer, FTS Query Processor, etc. Mainly to stage data during
plan execution. There are such cases to use intrinsic temp table:
1. order-by
2. group-by
3. distinct
4. semi-join(including duplicate-weedout and materialization)
5. multiple update
6. query dictionary data such as from information_schema table
7. union
8. using SQL_BUFFER_RESULT hint
9. derived table
10. count(distinct *)
11. group_concat function

Intrinsic temp table is classified into two kinds. One is heap temp table which
stores data in memory. The other is on-disk temp table including MyISAM temp
table and InnoDB temp table which store data on disk. If total bytes of data is
over max_heap_table_size, heap temp table will be transformed into on-disk temp

User can't create this table explicitly using any SQL statement. Creation of
this table is completely dependent on module which needs stage the intermediate
result set.

1. This WL provides InnoDB intrinsic temp table to replace MyISAM's.

2. All of the changes is invisible to users.

3. In order to replace MyISAM intrinsic temp table with InnoDB's, one new system
variable is added.
INTERNAL_TMP_DISK_STORAGE_ENGINE is a global system variable which can be used
to configure which temp table engine users prefer between MyISAM and InnoDB.
This variable can be command-line option. Default value is MyISAM. 
set global internal_tmp_disk_storage_engine = 'INNODB';
set global internal_tmp_disk_storage_engine = 'MYISAM';
set global internal_tmp_disk_storage_engine = DEFAULT;

4. Bug fixes done in scope of this WL.
Described in LLD. 
1.  Replace MyISAM intrinsic temp-table with InnoDB intrinsic temp table
1)Replace all cases using on-disk MyISAM intrinsic temp table with Innodb
intrinsic temp table except using TMP_TABLE_FORCE_MYISAM to create intrinsic
temp table . The following functions are modified in sql/sql_tmp_table.cc.
 a) create_tmp_table
 b) create-duplicate_weedout_tmp_table
 c) create_ondisk_from_heap
2)Introduce a function create_innodb_tmp_table(TABLE *table, KEY *keyinfo) to
create InnoDB intrinsic temp-table in sql/sql_tmp_table.cc 

2. Add a new global system variable 'internal_tmp_disk_storage_engine' in

3. Optimizer uses MyISAM relaxed semantics for creating intrinsic temp-table. In
order to satisfy the InnoDB temp-table, some places on MySQL layer are modified
1) Eliminate empty-name of temp-table fields for Order-by and Group-by. 
If Order-by or Group-by Item's item_name is empty, some fixes in sql/sql_yacc.yy
make sure item_name to be Order-by or Group-by part.

2) Eliminate empty-name of temp-table fields for other cases.
Construct a fake name like 'tmp_field_xx'. xx is the field index number in
function create_tmp_table in sql/sql_tmp_table.cc.

3) In order to make innodb support unique_constraint, a new design on
unique_constraint is introduced.
When group, order-by or distinct parts is too big or too long, blob type is
included in distinct query, or too many distinct columns(general >=16) are
included in query, unique_constraint will be used. 

4)Remove the field_name duplication
When creating temp table, Innodb syntax is stricter than MyISAM, which doesn't
allow field_name duplication. In order to remove the duplication, we took a
workaround which named field_name as table_alias+field_name+field_index when
Innodb creates temp table. This is because we try to keep MySQL backward
compatibility because if we modify the field_name of temp table on Optimizer,
all the system tables will be changed. But the more sensible solution is to fix
such a problem on Optimizer so that all temp SEs don't have to fix such a
problem one by one. We can re-implement it at later time.

The hash field used to check the unique value is added into temp table but not
on the fly.