5.3 merge

9 files changed, 129 insertions, 11 deletions

diff --git a/mysql-test/include/common-tests.inc b/mysql-test/include/common-tests.inc
index 2273d7d688d..4b61826c347 100644
--- a/mysql-test/include/common-tests.inc
+++ b/mysql-test/include/common-tests.inc
@@ -1536,6 +1536,10 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25
 #
 # Test of left join.
 #
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 insert into t2 (fld1, companynr) values (999999,99);
 
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
@@ -1568,6 +1572,8 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 #
 # Joins with forms.
 #
diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf
index c3ac934f7b3..4eb758ce520 100644
--- a/mysql-test/include/default_mysqld.cnf
+++ b/mysql-test/include/default_mysqld.cnf
@@ -1,4 +1,4 @@
-# Copyright (c) 2007, 2010, Oracle and/or its affiliates. All rights reserved.
+# Copyright (c) 2007, 2010, Oracle and/or its affiliates
 #
 # This program is free software; you can redistribute it and/or modify
 # it under the terms of the GNU General Public License as published by
diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc
index ad00c7e1ef9..cc8ec68f7d1 100644
--- a/mysql-test/include/gis_keys.inc
+++ b/mysql-test/include/gis_keys.inc
@@ -33,6 +33,7 @@ EXPLAIN
 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
 
+--replace_column 9 #
 EXPLAIN 
 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
diff --git a/mysql-test/include/have_plugin_auth.inc b/mysql-test/include/have_plugin_auth.inc
index 44ff4c2520d..daa5f5114e8 100644
--- a/mysql-test/include/have_plugin_auth.inc
+++ b/mysql-test/include/have_plugin_auth.inc
@@ -1,5 +1,4 @@
-disable_query_log;
---require r/true.require
-select (PLUGIN_LIBRARY LIKE 'auth_test_plugin%') as `TRUE` FROM INFORMATION_SCHEMA.PLUGINS 
-  WHERE PLUGIN_NAME='test_plugin_server';
-enable_query_log;
+if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'test_plugin_server' and plugin_status='active'`)
+{
+  --skip Needs auth_test_plugin
+}
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index ea4fc6439d2..e77cb220375 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -438,6 +438,7 @@ LIMIT 1;
 DROP TABLE t1;
 
 --echo #
+--echo #
 --echo # Bug#59259 "Incorrect rows returned for a correlated subquery
 --echo #            when ICP is on"
 --echo #
@@ -744,3 +745,110 @@ SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug#904832: an attempt to perform an index condition pushdown  
+--echo #             of a condition containing a subquery
+--echo #
+
+CREATE TABLE t1 ( 
+  a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+  b INT, c INT, d DATE NOT NULL, e VARCHAR(1),
+  KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b)
+);
+
+INSERT INTO t1 (b,c,d,e) VALUES
+(6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'),
+(4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'),
+(8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'),
+(4,1,'2001-06-05','x'),(7,1,'2006-05-28','g');
+
+CREATE TABLE t2 (
+  f INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+  g INT,
+  h VARCHAR(1),
+  KEY (g)
+);
+
+INSERT INTO t2 (g,h) VALUES
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),
+(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
+(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
+(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
+(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
+(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
+(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'),
+(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
+(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
+(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
+(1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),
+(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u');
+
+SET @save_optimize_switch=@@optimizer_switch;
+SET optimizer_switch='materialization=on';
+
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+      AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f  AND h <= t.e AND a > t.b)
+           OR a = 0 AND h < 'z' );
+
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+      AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f  AND h <= t.e AND a > t.b)
+           OR a = 0 AND h < 'z' );
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+      AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f  AND h <= t.e AND a > t.b)
+           OR a = 0 AND h < 'z' );
+
+SET optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index 86298f86906..88dd4c31910 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -264,7 +264,10 @@ explain select * from t1 where key1=3 or key2=4
   union select * from t1 where key1<4 or key3=5;
 
 #  index merge in subselect
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
 explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
+set optimizer_switch=@tmp_optimizer_switch;
 
 # 12. check for long index_merges.
 create table t3 like t0;
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 588de7b9f6a..87d6698865a 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1188,9 +1188,9 @@ DROP TABLE t1;
 create table t1 (a bit(1) not null,b int) engine=myisam;
 create table t2 (c int) engine=innodb;
 set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off';
+set @@optimizer_switch='in_to_exists=on,materialization=off';
 explain
-select b from t1 where a not in (select b from t1,t2 group by a) group by a;
+select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a;
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
 
diff --git a/mysql-test/include/mtr_check.sql b/mysql-test/include/mtr_check.sql
index c9708f988de..939a3215fd5 100644
--- a/mysql-test/include/mtr_check.sql
+++ b/mysql-test/include/mtr_check.sql
@@ -1,4 +1,4 @@
--- Copyright (c) 2008, 2010, Oracle and/or its affiliates. All rights reserved.
+-- Copyright (c) 2008, 2010, Oracle and/or its affiliates
 --
 -- This program is free software; you can redistribute it and/or modify
 -- it under the terms of the GNU General Public License as published by
@@ -29,7 +29,8 @@ BEGIN
   SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
     WHERE variable_name NOT IN ('timestamp', 'innodb_file_format_max')
      AND variable_name not like "Last_IO_Err*"
-      ORDER BY VARIABLE_NAME;
+     AND variable_name != 'INNODB_IBUF_MAX_SIZE'
+   ORDER BY variable_name;
 
   -- Dump all databases, there should be none
   -- except those that was created during bootstrap
diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql
index ce1563fc993..1248e72f3b9 100644
--- a/mysql-test/include/mtr_warnings.sql
+++ b/mysql-test/include/mtr_warnings.sql
@@ -1,4 +1,4 @@
--- Copyright (c) 2008, 2011, Oracle and/or its affiliates. All rights reserved.
+-- Copyright (c) 2008, 2011, Oracle and/or its affiliates
 --
 -- This program is free software; you can redistribute it and/or modify
 -- it under the terms of the GNU General Public License as published by