bzr push into mysql-6.0-opt branch (sergefp:2759)

[Sergey Petrunia]

 2759 Sergey Petrunia	2009-01-03 [merge]
      merge mysql-6.0 -> mysql-6.0-opt
modified:
  .bzr-mysql/default.conf
  mysql-test/r/func_math.result
  mysql-test/r/innodb_mrr.result
  mysql-test/r/type_varchar.result
  mysql-test/t/func_math.test
  mysql-test/t/innodb_mrr.test
  sql/item_func.cc
  sql/sql_base.cc
  sql/sql_select.cc

=== 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;