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)