18 files changed, 157 insertions, 31 deletions
diff --git a/mysql-test/suite/pbxt/t/derived.test b/mysql-test/suite/pbxt/t/derived.test
index a9127b49741..49effe4f14b 100644
--- a/mysql-test/suite/pbxt/t/derived.test
+++ b/mysql-test/suite/pbxt/t/derived.test
@@ -3,6 +3,9 @@
 drop table if exists t1,t2,t3;
 --enable_warnings
 
+set @save_derived_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
+
 select * from (select 2 from DUAL) b;
 -- error 1054
 SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
@@ -107,6 +110,7 @@ create table t1 select 1 as a;
 analyze table t1; # PBXT: Required to get a consitent result
 connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
 connection con1;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
 --error 1046
 select 2 as a from (select * from t1) b;
 use test;
@@ -283,3 +287,5 @@ drop user mysqltest_1;
 drop database pbxt;
 --enable_query_log
 # End of 4.1 tests
+
+set optimizer_switch=@save_derived_optimizer_switch;
diff --git a/mysql-test/suite/pbxt/t/distinct.test b/mysql-test/suite/pbxt/t/distinct.test
index 5d02b38aed8..854d7fc838e 100644
--- a/mysql-test/suite/pbxt/t/distinct.test
+++ b/mysql-test/suite/pbxt/t/distinct.test
@@ -158,6 +158,10 @@ create table t3 (
 insert into t1 values (1,'yes'), (2,'no');
 insert into t2 values (1,1);
 insert into t3 values (1,1);
+
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 EXPLAIN
 SELECT DISTINCT
     t1.id
@@ -195,6 +199,8 @@ WHERE
     AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
 drop table t1,t2,t3;
 
+set join_cache_level=@save_join_cache_level;
+
 #
 # Test using DISTINCT on a function that contains a group function
 # This also test the case when one doesn't use all fields in GROUP BY.
diff --git a/mysql-test/suite/pbxt/t/func_group.test b/mysql-test/suite/pbxt/t/func_group.test
index ca303030749..f3b466c463d 100644
--- a/mysql-test/suite/pbxt/t/func_group.test
+++ b/mysql-test/suite/pbxt/t/func_group.test
@@ -136,6 +136,8 @@ insert into t2 values('AAA', 10, 0.5);
 insert into t2 values('BBB', 20, 1.0);
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
 select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
@@ -144,6 +146,7 @@ select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
 select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 
 #
diff --git a/mysql-test/suite/pbxt/t/func_op.test b/mysql-test/suite/pbxt/t/func_op.test
index 5ac127ad25f..4a6474df946 100644
--- a/mysql-test/suite/pbxt/t/func_op.test
+++ b/mysql-test/suite/pbxt/t/func_op.test
@@ -28,7 +28,10 @@ create table t1(a int);
 create table t2(a int, b int);
 insert into t1 values (1), (2), (3);
 insert into t2 values (1, 7), (3, 7);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2;
 
 --disable_query_log
diff --git a/mysql-test/suite/pbxt/t/grant_cache.test b/mysql-test/suite/pbxt/t/grant_cache.test
index f3faa579035..ce53a88ac0c 100644
--- a/mysql-test/suite/pbxt/t/grant_cache.test
+++ b/mysql-test/suite/pbxt/t/grant_cache.test
@@ -5,6 +5,17 @@
 --source include/add_anonymous_users.inc
 
 #
+# some statements have different results in ps-mode
+#
+let $actual1=4;
+let $actual2=5;
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0`)
+{
+  let $actual1=3;
+  let $actual2=4;
+}
+
+#
 # Test grants with query cache
 #
 --disable_warnings
@@ -112,6 +123,7 @@ select * from t2;
 select mysqltest.t1.c from test.t1,mysqltest.t1;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
+--replace_result $actual1 4
 show status like "Qcache_not_cached";
 
 # Connect without a database
@@ -128,6 +140,7 @@ select a from mysqltest.t1;
 select a from mysqltest.t1;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
+--replace_result $actual2 5
 show status like "Qcache_not_cached";
 
 # Cleanup
diff --git a/mysql-test/suite/pbxt/t/greedy_optimizer.test b/mysql-test/suite/pbxt/t/greedy_optimizer.test
index fd0be172e83..b3c05f4b3cd 100644
--- a/mysql-test/suite/pbxt/t/greedy_optimizer.test
+++ b/mysql-test/suite/pbxt/t/greedy_optimizer.test
@@ -10,6 +10,9 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7;
 --enable_warnings
 
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 create table t1 (
   c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
   primary key (c11)
@@ -312,6 +315,8 @@ show status like 'Last_query_cost';
 
 drop table t1,t2,t3,t4,t5,t6,t7;
 
+set join_cache_level=@save_join_cache_level;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/group_by.test b/mysql-test/suite/pbxt/t/group_by.test
index c1909668b55..b29519369c7 100644
--- a/mysql-test/suite/pbxt/t/group_by.test
+++ b/mysql-test/suite/pbxt/t/group_by.test
@@ -338,8 +338,11 @@ drop table t1,t2;
 CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
 ));
 insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2;
 select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1;
 
 #
diff --git a/mysql-test/suite/pbxt/t/join.test b/mysql-test/suite/pbxt/t/join.test
index 7e7e1c10f06..fe73c67a6a3 100644
--- a/mysql-test/suite/pbxt/t/join.test
+++ b/mysql-test/suite/pbxt/t/join.test
@@ -4,6 +4,9 @@
 drop table if exists t1,t2,t3;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 #
 # Test different join syntaxes
 #
@@ -521,7 +524,7 @@ select * from v1a join v1b on t1.b = t2.b;
 # Bug #17523 natural join and information_schema
 #
 # We mask out the Privileges column because it differs with embedded server
---replace_column 31 #
+--replace_column 32 #
 select * from information_schema.statistics join information_schema.columns
               using(table_name,column_name) where table_name='user';
 
@@ -648,6 +651,8 @@ DESCRIBE tv2;
 DROP VIEW v1;
 DROP TABLE t1,t2,tv1,tv2;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test
index 3b72dc1e293..25c8a5a782e 100644
--- a/mysql-test/suite/pbxt/t/join_nested.test
+++ b/mysql-test/suite/pbxt/t/join_nested.test
@@ -3,6 +3,9 @@
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 CREATE TABLE t0 (a int, b int, c int);
 CREATE TABLE t1 (a int, b int, c int);
 CREATE TABLE t2 (a int, b int, c int);
@@ -587,6 +590,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 --echo .          costs. To combat the plan change it uses --sorted_result and
 --echo .          and --replace tricks
 
+INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0);
 CREATE INDEX idx_b ON t1(b);
 CREATE INDEX idx_a ON t0(a);
 
@@ -614,7 +618,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-          (t1.a != 2),
+          (t1.a != 2) AND t1.a>0,
        t9
      WHERE t0.a=1 AND
            t0.b=t1.b AND          
@@ -653,7 +657,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        )
        ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-          (t1.a != 2),
+          (t1.a != 2) AND t1.a>0,
        t9
      WHERE t0.a=1 AND
            t0.b=t1.b AND          
@@ -1062,6 +1066,9 @@ SELECT t1.*, t4.nm
 
 DROP TABLE t1,t2,t3,t4;
 
+
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/join_outer.test b/mysql-test/suite/pbxt/t/join_outer.test
index a9635de7081..4c5006c6b0a 100644
--- a/mysql-test/suite/pbxt/t/join_outer.test
+++ b/mysql-test/suite/pbxt/t/join_outer.test
@@ -6,6 +6,9 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 CREATE TABLE t1 (
   grp int(11) default NULL,
   a bigint(20) unsigned default NULL,
@@ -809,6 +812,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
 DROP TABLE t1,t2;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/key_diff.test b/mysql-test/suite/pbxt/t/key_diff.test
index 5e9d7bac9cc..fc93c7418ab 100644
--- a/mysql-test/suite/pbxt/t/key_diff.test
+++ b/mysql-test/suite/pbxt/t/key_diff.test
@@ -13,11 +13,19 @@ CREATE TABLE t1 (
 );
 
 INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a');
+INSERT INTO t1 VALUES 
+  ('AA','BB'),('bb','AA'),('CC','cc'),('DD','EE'),('aa','aa');
+INSERT INTO t1 VALUES 
+  ('AAA','BBB'),('bbb','AAA'),('CCC','ccc'),('DDD','EEE'),('aaa','aaa');
 
-select * from t1,t1 as t2;
-explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
+select * from t1,t1 as t2
+  where length(t1.A)=1 and length(t2.B)=1 ;
+explain select t1.*,t2.* from t1,t1 as t2
+  where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1;
 #select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
-select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a;
+select t1.*,t2.* from t1,t1 as t2
+  where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1
+order by binary t1.a,t2.a;
 select * from t1 where a='a';
 drop table t1;
 
diff --git a/mysql-test/suite/pbxt/t/lock_multi.test b/mysql-test/suite/pbxt/t/lock_multi.test
index 1104620b6b5..f90e31699c8 100644
--- a/mysql-test/suite/pbxt/t/lock_multi.test
+++ b/mysql-test/suite/pbxt/t/lock_multi.test
@@ -48,7 +48,7 @@ insert t1 select * from t2;
 connection locker;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "insert t1 select * from t2";
+  where state = "Table Lock" and info = "insert t1 select * from t2";
 --source include/wait_condition.inc
 drop table t2;
 connection reader;
@@ -72,7 +72,7 @@ connection locker;
 # Sleep a bit till the insert of connection reader is in work and hangs
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "insert t1 select * from t2";
+  where state = "Table Lock" and info = "insert t1 select * from t2";
 --source include/wait_condition.inc
 drop table t2;
 connection reader;
@@ -251,7 +251,7 @@ connection reader;
 # Wait till connection writer is blocked
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "alter table t1 auto_increment=0";
+  where state = "Table Lock" and info = "alter table t1 auto_increment=0";
 --source include/wait_condition.inc
 send
 alter table t1 auto_increment=0;
@@ -259,7 +259,7 @@ connection locker;
 # Wait till connection reader is blocked
 let $wait_condition=
   select count(*) = 2 from information_schema.processlist
-  where state = "Locked" and info = "alter table t1 auto_increment=0";
+  where state = "Table Lock" and info = "alter table t1 auto_increment=0";
 --source include/wait_condition.inc
 unlock tables;
 connection writer;
@@ -414,16 +414,16 @@ update t1 set i= 10;
 connection reader;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "update t1 set i= 10";
+  where state = "Table Lock" and info = "update t1 set i= 10";
 --source include/wait_condition.inc
 send
 select * from t1;
 connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "select * from t1";
+  where state = "Table Lock" and info = "select * from t1";
 --source include/wait_condition.inc
-let $ID= `select id from information_schema.processlist where state = "Locked" and info = "update t1 set i= 10"`;
+let $ID= `select id from information_schema.processlist where state = "Table Lock" and info = "update t1 set i= 10"`;
 --replace_result $ID ID
 eval kill query $ID;
 connection reader;
@@ -557,7 +557,7 @@ connection waiter;
 connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
-  where state = "Locked" and info = "insert into t1 values(1)";
+  where state = "Table Lock" and info = "insert into t1 values(1)";
 --source include/wait_condition.inc
 let $tlwb= `show status like 'Table_locks_waited'`;
 unlock tables;
diff --git a/mysql-test/suite/pbxt/t/mysqlshow.test b/mysql-test/suite/pbxt/t/mysqlshow.test
index d8f5c20afcd..41505cc0b10 100644
--- a/mysql-test/suite/pbxt/t/mysqlshow.test
+++ b/mysql-test/suite/pbxt/t/mysqlshow.test
@@ -1,5 +1,6 @@
 # Can't run test of external client with embedded server
 -- source include/not_embedded.inc
+-- source include/have_innodb.inc
 
 --disable_warnings
 DROP TABLE IF EXISTS t1,t2,test1,test2;
diff --git a/mysql-test/suite/pbxt/t/pbxt_xa_binlog.test b/mysql-test/suite/pbxt/t/pbxt_xa_binlog.test
new file mode 100644
index 00000000000..4a4578a5595
--- /dev/null
+++ b/mysql-test/suite/pbxt/t/pbxt_xa_binlog.test
@@ -0,0 +1,32 @@
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+
+SET binlog_format = 'mixed';
+
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb;
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=pbxt;
+BEGIN;
+# verify that binlog is on
+SELECT @@log_bin;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+COMMIT;
+select * from t1;
+select * from t2;
+
+# Test 2-phase commit when we disable binlogging.
+SET sql_log_bin = 0;
+BEGIN;
+INSERT INTO t1 VALUES (3);
+INSERT INTO t2 VALUES (4);
+COMMIT;
+select * from t1 order by a;
+select * from t2 order by b;
+
+drop table t1, t2;
+drop database pbxt;
+
diff --git a/mysql-test/suite/pbxt/t/select.test b/mysql-test/suite/pbxt/t/select.test
index 7074f4ac364..3a1cdcf5451 100644
--- a/mysql-test/suite/pbxt/t/select.test
+++ b/mysql-test/suite/pbxt/t/select.test
@@ -13,6 +13,9 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
   Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -3130,6 +3133,9 @@ SELECT * FROM t1 LIMIT 2, -1;
 
 DROP TABLE t1;
 
+
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test
index 154e8d1e627..aae7ff2d5f5 100644
--- a/mysql-test/suite/pbxt/t/subselect.test
+++ b/mysql-test/suite/pbxt/t/subselect.test
@@ -80,8 +80,11 @@ explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc
 select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
 select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
 (select * from t2 where a>1) as tt;
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
 explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
 (select * from t2 where a>1) as tt;
+set optimizer_switch=@tmp_optimizer_switch;
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
@@ -278,8 +281,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT *
 SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
 SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
 INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
--- error 1242
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
+-- error 1242
+SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
 EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 drop table t1;
@@ -477,6 +481,9 @@ drop table t1;
 # Null with keys
 #
 
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -825,6 +832,8 @@ commit;
 enable_query_log;
 insert into t2 values (2), (3), (4), (5);
 insert into t3 values (10,3), (20,4), (30,5);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 select * from t2 where t2.a in (select a from t1);
 explain extended select * from t2 where t2.a in (select a from t1);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -835,6 +844,7 @@ insert into t1 values (3,31);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 
 #
@@ -1121,6 +1131,8 @@ select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (i
 explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
 drop table t1,t2;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 #
 # Static tables & rund() in subqueries
 #
@@ -1784,6 +1796,10 @@ drop table t1;
 # Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
 #
 
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+SET optimizer_switch='semijoin_with_cache=off';
+
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -1811,6 +1827,9 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR
 explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
 DROP TABLE t1,t2;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
+
 #
 # Bug #12392: where cond with IN predicate for rows and NULL values in table 
 #
@@ -1972,6 +1991,9 @@ DROP TABLE t1, t2;
 #             with possible NULL values by index access from the outer query
 #
 
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+
 CREATE TABLE t1(a int, INDEX (a));
 INSERT INTO t1 VALUES (1), (3), (5), (7);
 INSERT INTO t1 VALUES (NULL);
@@ -1984,6 +2006,8 @@ SELECT a, a IN (SELECT a FROM t1) FROM t2;
 
 DROP TABLE t1,t2;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 #
 # Bug #11302: getObject() returns a String for a sub-query of type datetime
 #
@@ -2036,7 +2060,7 @@ SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
 --error 1242
 SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
 
-
+--sorted_result
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
@@ -2044,7 +2068,7 @@ SELECT a FROM t1 GROUP BY a
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
-
+--sorted_result
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
@@ -2052,7 +2076,7 @@ SELECT a FROM t1 GROUP BY a
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                 (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
-
+--sorted_result
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
                   (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
@@ -2060,7 +2084,7 @@ SELECT a FROM t1
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
                   (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
-
+--sorted_result
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
                   (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
@@ -3075,6 +3099,10 @@ DROP TABLE t1, t2, t3;
 # Bug #30788: Inconsistent retrieval of char/varchar
 #
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
+SET optimizer_switch='materialization=off';
+
 CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
 INSERT INTO t1 VALUES ('a', 'aa');
 INSERT INTO t1 VALUES ('a', 'aaa');
@@ -3096,6 +3124,8 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
 
 DROP TABLE t1,t2;
 
+SET optimizer_switch= @save_optimizer_switch;
+
 #
 # Bug #32400: Complex SELECT query returns correct result only on some
 # occasions
@@ -3211,6 +3241,7 @@ INSERT INTO t1 VALUES ('a');
 SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 DROP TABLE t1;
 
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/type_timestamp.test b/mysql-test/suite/pbxt/t/type_timestamp.test
index 88b4c4e2587..65659b2acca 100644
--- a/mysql-test/suite/pbxt/t/type_timestamp.test
+++ b/mysql-test/suite/pbxt/t/type_timestamp.test
@@ -67,17 +67,6 @@ INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
 SELECT * FROM t1;
 drop table t1;
 
-create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6),
-                 t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
-                 t14 timestamp(14));
-insert t1 values (0,0,0,0,0,0,0),
-("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
-"1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
-"1997-12-31 23:47:59");
-select * from t1;
-select * from t1;
-drop table t1;
-
 #
 # Let us check if we properly treat wrong datetimes and produce proper warnings
 # (for both strings and numbers)
@@ -298,7 +287,7 @@ drop table t1;
 # mode regardless of whether a display width is given.
 #
 set sql_mode='maxdb';
-create table t1 (a timestamp, b timestamp(19));
+create table t1 (a timestamp, b timestamp(5));
 show create table t1;
 # restore default mode
 set sql_mode='';
diff --git a/mysql-test/suite/pbxt/t/union.test b/mysql-test/suite/pbxt/t/union.test
index c216b3caceb..5a719f00cb3 100644
--- a/mysql-test/suite/pbxt/t/union.test
+++ b/mysql-test/suite/pbxt/t/union.test
@@ -481,7 +481,10 @@ drop table t1;
 #
 create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
 insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
+SET optimizer_switch=@save_optimizer_switch;
 drop  table t1;
 
 #