[MDEV-3157] LP:940652 - Non-semi-join materialization creates too many temp. tables Created: 2012-02-24  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug940652.xml    

 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.

 Comments   
Comment by Sergei Petrunia [ 2012-02-24 ]

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);
------------------------------------------------------------------------+

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)

Comment by Sergei Petrunia [ 2012-02-24 ]

Re: Non-semi-join materialization creates too many temp. tables
Figured out that the 3rd temp table is created by expression cache.

Comment by Rasmus Johansson (Inactive) [ 2012-02-24 ]

Launchpad bug id: 940652

Generated at Thu Feb 08 06:46:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.