create table t1 (a int);

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

alter table t1 add b int;

create table t2 as select * from t1;

set optimizer_switch='derived_merge=off';

explain select * from (select t1.a, t2.b from t1, t2) T;

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                              |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  100 |                                    |

|  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |

|  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+

3 rows in set (0.01 sec)

flush status;

select * from (select t1.a, t2.b from t1, t2) T;

show status like 'created%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_files       | 0     |

| Created_tmp_tables      | 1     |

+-------------------------+-------+

3 rows in set (0.00 sec)

set optimizer_switch='derived_merge=on';

explain select * from (select t1.a, t2.b from t1, t2) T;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |

|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+

2 rows in set (0.00 sec)

flush status;

select * from (select t1.a, t2.b from t1, t2) T;

show status like 'created%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_files       | 0     |

| Created_tmp_tables      | 1     |

+-------------------------+-------+

3 rows in set (0.00 sec)