Non-semi-join materialization creates too many temp. tables

[Sergey Petrunia]

Non-semi-join materialization strategy creates too many temp. tables:

CREATE TABLE t1(a int);
INSERT INTO t1 values(1),(2);
CREATE TABLE t2(a int);
INSERT INTO t2 values(1),(2);
set optimizer_switch='semijoin=off';
flush status;
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
SHOW STATUS LIKE 'Created_tmp_tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 3 |
+-------------------------+-------+

The query creates 3 temporary tables. Note that with default settings (semijoin=on) it will create two:
- one for group by
- another for doing subquery materialization.
one could argue that even two tables are too many for this example since they have identical data and keys. In general case, two tables may be required. However, creating three tables is a bug.

[Sergey Petrunia (sergefp)]

EXPLAIN:

MariaDB [j16]> explain SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+----+--------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------+------+---------------+------+---------+------+------+-----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary |
+----+--------------+-------+------+---------------+------+---------+------+------+-----------------+
2 rows in set (2.59 sec)

[Sergey Petrunia (sergefp)]

Figured out that the 3rd temp table is created by expression cache.