Merge 5.3->5.5

4 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 2528f0d7b2b..0a368826ee7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -9151,6 +9151,26 @@ CALL p1(1);
 
 DROP PROCEDURE p1; 
 
+--echo
+--echo MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements
+--echo
+
+CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());
+
+CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
+INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+
+FLUSH TABLES;
+FLUSH STATUS;
+
+SHOW STATUS LIKE '%Handler_read%';
+UPDATE t1 SET a = '+' WHERE daynum=tdn();
+SHOW STATUS LIKE '%Handler_read%';
+
+drop function tdn;
+drop table t1;
+
 --echo #
 --echo # lp:1002157 : testing stored function
 --echo # bug#62125        result for null incorrectly yields 1292 warning.
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index d939c60ddd1..37f660d6682 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1805,6 +1805,30 @@ SELECT * FROM t1 WHERE a1 IN (
 drop table t1, t2, t3;
 
 --echo #
+--echo # MDEV-4056:Server crashes in Item_func_trig_cond::val_int
+--echo # with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists
+--echo #
+
+set @optimizer_switch_MDEV4056 = @@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
+
+CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('x'),('d');
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1))  ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');
+
+SELECT * FROM t1 LEFT JOIN (  
+  SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( 
+    SELECT MIN(b), SUM(pk) FROM t1
+  ) 
+) AS alias1 ON (a = c) 
+WHERE b IS NULL OR a < 'u';
+
+drop table t1,t2;
+set @@optimizer_switch = @optimizer_switch_MDEV4056;
+
+--echo #
 --echo # MDEV-3899  Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
 --echo #
 
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index 3af8f31062c..83c36b16163 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -377,4 +377,18 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
 drop table t1;
 
 
+--echo #
+--echo # MDEV-3988 crash in create_tmp_table
+--echo #
+
+drop table if exists `t1`,`t2`;
+create table `t1`(`a` char(1) character set utf8)engine=innodb;
+create table `t2`(`b` char(1) character set utf8)engine=memory;
+select distinct (select 1 from `t2` where `a`) `d2` from `t1`;
+select distinct (select 1 from `t2` where `a`) `d2`, a from `t1`;
+select distinct a, (select 1 from `t2` where `a`) `d2` from `t1`;
+select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`;
+
+drop table t1,t2;
+
 set optimizer_switch=@subselect_innodb_tmp;
diff --git a/mysql-test/t/sum_distinct-big.test b/mysql-test/t/sum_distinct-big.test
index 0859f4b3d89..d3710056c9a 100644
--- a/mysql-test/t/sum_distinct-big.test
+++ b/mysql-test/t/sum_distinct-big.test
@@ -63,5 +63,22 @@ SELECT SUM(DISTINCT id) sm FROM t1;
 SELECT SUM(DISTINCT id) sm FROM t2;
 SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13;
 
+--echo # 
+--echo # Bug mdev-4063: SUM(DISTINCT...) with small'max_heap_table_size 
+--echo # (bug #56927)
+--echo # 
+
+SET max_heap_table_size=default;
+
+INSERT INTO t1 SELECT id+16384 FROM t1;
+DELETE FROM t2;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+
+SELECT SUM(DISTINCT id) sm FROM t2;
+
+SET max_heap_table_size=16384;
+
+SELECT SUM(DISTINCT id) sm FROM t2;
+
 DROP TABLE t1;
 DROP TABLE t2;