Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Non-semi-join materialization creates too many temp. tables
Figured out that the 3rd temp table is created by expression cache.
Launchpad bug id: 940652
Re: Non-semi-join materialization creates too many temp. tables
EXPLAIN:
MariaDB [j16]> explain SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
---
---------------------------------------------------------------------+---
---------------------------------------------------------------------+---
---------------------------------------------------------------------+2 rows in set (2.59 sec)