From: Sergey Petrunia
Date: December 28 2008 3:00pm
Subject: bzr commit into mysql-6.0-opt-subqueries branch (sergefp:2758)
 Bug#32665
List-Archive: http://lists.mysql.com/commits/62387
X-Bug: 32665
Message-Id: <20081228150036.BA3842EA047@pslp2.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///home/spetrunia/dev/mysql-6.0-subq-r20/ based on revid:sergefp@stripped

 2758 Sergey Petrunia	2008-12-28
      BUG#32665: Query with dependent subquery is too slow
      - Testcase (the code is subquery optimization WLs)
modified:
  mysql-test/r/subselect3.result
  mysql-test/r/subselect3_jcl6.result
  mysql-test/t/subselect3.test

per-file messages:
  mysql-test/r/subselect3.result
    BUG#32665: Query with dependent subquery is too slow
    - Testcase (the code is subquery optimization WLs)
  mysql-test/r/subselect3_jcl6.result
    BUG#32665: Query with dependent subquery is too slow
    - Testcase (the code is subquery optimization WLs)
  mysql-test/t/subselect3.test
    BUG#32665: Query with dependent subquery is too slow
    - Testcase (the code is subquery optimization WLs)
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-12-23 04:05:29 +0000
+++ b/mysql-test/r/subselect3.result	2008-12-28 15:00:21 +0000
@@ -1190,3 +1190,36 @@ a
 9
 set join_cache_level=@save_join_cache_level;
 drop table t0, t1;
+# 
+# BUG#32665 Query with dependent subquery is too slow
+#
+create table t1 (
+idIndividual int primary key
+);
+insert into t1 values (1),(2);
+create table t2 (
+idContact int primary key,
+contactType int,
+idObj int
+);
+insert into t2 values (1,1,1),(2,2,2),(3,3,3);
+create table t3 (
+idAddress int primary key,
+idContact int,
+postalStripped varchar(100)
+);
+insert into t3 values (1,1, 'foo'), (2,2,'bar');
+The following must be converted to a semi-join:
+explain extended SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+(	SELECT c.idObj FROM t3 cona 
+INNER JOIN t2 c ON c.idContact=cona.idContact 
+WHERE cona.postalStripped='T2H3B2'
+	);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start materialize; Scan
+1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize
+1	PRIMARY	a	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer
+Warnings:
+Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
+drop table t1,t2,t3;

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2008-12-23 04:05:29 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2008-12-28 15:00:21 +0000
@@ -1195,6 +1195,39 @@ a
 9
 set join_cache_level=@save_join_cache_level;
 drop table t0, t1;
+# 
+# BUG#32665 Query with dependent subquery is too slow
+#
+create table t1 (
+idIndividual int primary key
+);
+insert into t1 values (1),(2);
+create table t2 (
+idContact int primary key,
+contactType int,
+idObj int
+);
+insert into t2 values (1,1,1),(2,2,2),(3,3,3);
+create table t3 (
+idAddress int primary key,
+idContact int,
+postalStripped varchar(100)
+);
+insert into t3 values (1,1, 'foo'), (2,2,'bar');
+The following must be converted to a semi-join:
+explain extended SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+(	SELECT c.idObj FROM t3 cona 
+INNER JOIN t2 c ON c.idContact=cona.idContact 
+WHERE cona.postalStripped='T2H3B2'
+	);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start materialize; Scan
+1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize; Using join buffer
+1	PRIMARY	a	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer
+Warnings:
+Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
+drop table t1,t2,t3;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-12-22 20:10:35 +0000
+++ b/mysql-test/t/subselect3.test	2008-12-28 15:00:21 +0000
@@ -976,3 +976,35 @@ set join_cache_level=6;
 select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); 
 set join_cache_level=@save_join_cache_level;
 drop table t0, t1;
+
+--echo # 
+--echo # BUG#32665 Query with dependent subquery is too slow
+--echo #
+create table t1 (
+  idIndividual int primary key
+);
+insert into t1 values (1),(2);
+
+create table t2 (
+  idContact int primary key,
+  contactType int,
+  idObj int
+);
+insert into t2 values (1,1,1),(2,2,2),(3,3,3);
+
+create table t3 (
+  idAddress int primary key,
+  idContact int,
+  postalStripped varchar(100)
+);
+
+insert into t3 values (1,1, 'foo'), (2,2,'bar');
+
+--echo The following must be converted to a semi-join:
+explain extended SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+	(	SELECT c.idObj FROM t3 cona 
+		INNER JOIN t2 c ON c.idContact=cona.idContact 
+		WHERE cona.postalStripped='T2H3B2'
+	);
+drop table t1,t2,t3;