[MDEV-3551] LP:938131 - Subquery materialization is not used in CREATE TABLE SELECT Created: 2012-02-21  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 Priority: Major
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug938131.xml    

 Description   

Using mariadb-5.3 create and populate table t1,t2 with the following commands:

CREATE TABLE t1(a int);
INSERT INTO t1 values(1),(2);
CREATE TABLE t2(a int);
INSERT INTO t2 values(1),(2);

Then check that the following query uses subquery materialization:
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);

MariaDB [test]> 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
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.t1.a 1  
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary

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

In debugger check that materialization is not used for :

CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);

 Comments   
Comment by Elena Stepanova [ 2012-03-18 ]

Re: Subquery materialization is not used in CREATE TABLE SELECT
Fix released with 5.3.5-ga.

Comment by Rasmus Johansson (Inactive) [ 2012-03-18 ]

Launchpad bug id: 938131

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