Merge jbruehe@bk-internal.mysql.com:/home/bk/mysql-5.1-opt

into  trift2.:/MySQL/M51/push-5.1


sql/item_func.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged

26 files changed, 618 insertions, 15 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index e1a8a3151e9..109ddcd02e1 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -530,7 +530,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
-CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
+PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -644,3 +645,26 @@ SELECT COUNT(*) FROM
 COUNT(*)
 2
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT, UNIQUE (a));
+INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);
+EXPLAIN SELECT DISTINCT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	6	Using index
+SELECT DISTINCT a FROM t1;
+a
+NULL
+1
+2
+3
+4
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	6	Using index
+SELECT a FROM t1 GROUP BY a;
+a
+NULL
+1
+2
+3
+4
+DROP TABLE t1;
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 1f52076f3d2..d5888d13a06 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1183,6 +1183,20 @@ set time_zone= @@global.time_zone;
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
 NULL
+CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), 
+(2, '11:00:00', '11:15:00', '1972-02-06');
+SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) 
+FROM t1;
+t1	t2	SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) )	QUARTER(d)
+10:00:00	NULL	NULL	NULL
+11:00:00	11:15:00	00:15:00	1
+SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
+FROM t1 ORDER BY a DESC;
+t1	t2	SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) )	QUARTER(d)
+11:00:00	11:15:00	00:15:00	1
+10:00:00	NULL	NULL	NULL
+DROP TABLE t1;
 End of 5.0 tests
 select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
 date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 13f9950a337..fd218f2de62 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1037,4 +1037,24 @@ REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;
 ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
 REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
 ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
+CREATE USER bug23556@localhost;
+CREATE DATABASE bug23556;
+GRANT SELECT ON bug23556.* TO bug23556@localhost;
+USE bug23556;
+CREATE TABLE t1 (a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
+GRANT DELETE ON t1 TO bug23556@localhost;
+USE bug23556;
+TRUNCATE t1;
+ERROR 42000: DROP command denied to user 'bug23556'@'localhost' for table 't1'
+USE bug23556;
+REVOKE DELETE ON t1 FROM bug23556@localhost;
+GRANT DROP ON t1 TO bug23556@localhost;
+USE bug23556;
+TRUNCATE t1;
+USE bug23556;
+DROP TABLE t1;
+USE test;
+DROP DATABASE bug23556;
+DROP USER bug23556@localhost;
 End of 5.0 tests
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index e8599241cc0..58dc82b6c2a 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1342,6 +1342,16 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	2	
 drop view v1;
+create table t1 (f1 int(11));
+create table t2 (f1 int(11), f2 int(11));
+select table_name from information_schema.tables
+where table_schema = 'test' and table_name not in
+(select table_name from information_schema.columns
+where table_schema = 'test' and column_name = 'f3');
+table_name
+t1
+t2
+drop table t1,t2;
 End of 5.0 tests.
 select * from information_schema.engines WHERE ENGINE="MyISAM";
 ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 75caa6b45a4..8df5a9220a8 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1605,3 +1605,31 @@ WHERE t1.id='5';
 id	ct	pc	nm
 5	NULL	NULL	NULL
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT, c INT);
+CREATE TABLE t4 (a INT, c INT);
+CREATE TABLE t5 (a INT, c INT);
+SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+b
+SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+ERROR 23000: Column 'c' in field list is ambiguous
+SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+b
+SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+ERROR 23000: Column 'c' in field list is ambiguous
+DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t3 VALUES (1,1);
+SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);
+ERROR 23000: Column 'a' in from clause is ambiguous
+DROP TABLE t1,t2,t3;
+End of 5.0 tests
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index ea8d4338d08..853b837c46e 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -482,3 +482,10 @@ alter table t1 drop index i3, drop index i2, drop index i1;
 alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
 ERROR 23000: Duplicate entry '1' for key 'i1'
 drop table t1;
+CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES( 1 );
+ALTER TABLE t1 DISABLE KEYS;
+EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
diff --git a/mysql-test/r/ndb_read_multi_range.result b/mysql-test/r/ndb_read_multi_range.result
index 8bc0f519cde..12f3185bb1a 100644
--- a/mysql-test/r/ndb_read_multi_range.result
+++ b/mysql-test/r/ndb_read_multi_range.result
@@ -417,12 +417,12 @@ a	b	c
 9199	9200	NULL
 223456	223457	NULL
 245651	245652	2005-12-08 15:58:27
-select c, count(*)
+select t21.c, count(*)
 from t21 
 inner join t22 using (a)
 where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
-group by c
-order by c;
+group by t21.c
+order by t21.c;
 c	count(*)
 NULL	7
 2005-12-08 15:58:27	1
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 15ca73dd7c9..8a440284c53 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -30,7 +30,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index
 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	2	Using where; Using index
+1	SIMPLE	t1	range	a,b	a	9	NULL	3	Using where; Using index
 explain select * from t1 where a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
@@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4
 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
 explain select id from t1 where uniq_id is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	1	Using where
+1	SIMPLE	t1	ref	idx1	idx1	5	const	5	Using where
 explain select id from t1 where uniq_id =1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	idx1	idx1	5	const	1	
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 6704842320f..981c341bc0d 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3642,3 +3642,89 @@ INSERT into t1 values (1), (2), (3);
 SELECT * FROM t1 LIMIT 2, -1;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
 DROP TABLE t1;
+CREATE TABLE t1 (
+ID_with_null int NULL,
+ID_better int NOT NULL,
+INDEX idx1 (ID_with_null),
+INDEX idx2 (ID_better)
+);
+INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
+COUNT(*)
+128
+SELECT COUNT(*) FROM t1 WHERE ID_better=1;
+COUNT(*)
+2
+EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+DROP INDEX idx1 ON t1;
+CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
+EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+DROP TABLE t1;
+CREATE TABLE t1 (
+ID1_with_null int NULL,
+ID2_with_null int NULL,
+ID_better int NOT NULL,
+INDEX idx1 (ID1_with_null, ID2_with_null),
+INDEX idx2 (ID_better)
+);
+INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
+(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
+COUNT(*)
+24
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
+COUNT(*)
+24
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
+COUNT(*)
+192
+SELECT COUNT(*) FROM t1 WHERE ID_better=1;
+COUNT(*)
+2
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+DROP INDEX idx1 ON t1;
+CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+EXPLAIN SELECT * FROM t1
+WHERE ID_better=1 AND ID1_with_null IS NULL AND 
+(ID2_with_null=1 OR ID2_with_null=2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a8cff158338..ad68ac56fad 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3046,6 +3046,80 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 DROP TABLE t1;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (4), (1), (3);
+CREATE TABLE t2 (b int, c int);
+INSERT INTO t2 VALUES
+(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
+a
+2
+4
+1
+3
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
+a
+1
+2
+3
+4
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
+ERROR 21000: Subquery returns more than 1 row
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
+b	MAX(c)
+1	4
+2	2
+4	4
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
+ERROR 21000: Subquery returns more than 1 row
+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;
+a
+1
+2
+3
+4
+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;
+ERROR 21000: Subquery returns more than 1 row
+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;
+a
+4
+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;
+ERROR 21000: Subquery returns more than 1 row
+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));
+a
+2
+4
+1
+3
+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));
+ERROR 21000: Subquery returns more than 1 row
+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));
+a
+2
+1
+3
+4
+SELECT a FROM t1 
+ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t1,t2;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index caca7484f07..42dd87a82de 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -629,3 +629,19 @@ cc	NULL	NULL
 aa	1	1
 bb	NULL	NULL
 drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 values (0,0), (2,2), (3,3);
+create table t2 (a int, b int);
+insert into t2 values (1,1), (3,3);
+select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
+a	b	Z
+0	0	0
+2	2	0
+3	3	1
+insert into t2 values (NULL,4);
+select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
+a	b	Z
+0	0	0
+2	2	0
+3	3	1
+drop table t1,t2;
diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result
index eb211be0270..49c36513fbc 100644
--- a/mysql-test/r/trigger-grant.result
+++ b/mysql-test/r/trigger-grant.result
@@ -14,8 +14,8 @@ CREATE TABLE t1(num_value INT);
 CREATE TABLE t2(user_str TEXT);
 
 ---> connection: default
-GRANT INSERT, DELETE ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT INSERT, DELETE ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
 
 ---> connection: default
 GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index dcd582fb55e..9e5e9c8244c 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1241,6 +1241,31 @@ i	j
 2	2
 13	13
 drop table t1;
+CREATE TABLE t1 (a INT PRIMARY KEY);
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW 
+INSERT INTO t2 VALUES (OLD.a);
+FLUSH STATUS;
+TRUNCATE t1;
+SHOW STATUS LIKE 'handler_delete';
+Variable_name	Value
+Handler_delete	0
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+DELETE FROM t2;
+FLUSH STATUS;
+DELETE FROM t1;
+SHOW STATUS LIKE 'handler_delete';
+Variable_name	Value
+Handler_delete	8
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+8
+DROP TRIGGER trg_t1;
+DROP TABLE t1,t2;
 drop table if exists t1;
 drop function if exists f1;
 create table t1 (i int);
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 8734b940241..476e4ce7735 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -364,7 +364,8 @@ EXPLAIN SELECT a FROM t1 GROUP BY a;
 EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 
-CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
+                PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 EXPLAIN SELECT DISTINCT a,a FROM t2;
@@ -525,3 +526,17 @@ SELECT COUNT(*) FROM
   (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
 
 DROP TABLE t1, t2;
+
+#
+# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
+#
+CREATE TABLE t1 (a INT, UNIQUE (a));
+INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);
+EXPLAIN SELECT DISTINCT a FROM t1;
+#result must have one row with NULL
+SELECT DISTINCT a FROM t1;
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+#result must have one row with NULL
+SELECT a FROM t1 GROUP BY a;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index ebaa6d84bd3..9e5a3012934 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -692,6 +692,18 @@ set time_zone= @@global.time_zone;
 #
 
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
+
+#
+# Bug #25643: SEC_TO_TIME function problem
+#
+CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), 
+       (2, '11:00:00', '11:15:00', '1972-02-06');
+SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) 
+  FROM t1;
+SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
+  FROM t1 ORDER BY a DESC;
+DROP TABLE t1;
 --echo End of 5.0 tests
 
 #
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index 1d8991e92a1..1fabe2667a4 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -939,4 +939,39 @@ REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;
 
 --error ER_WRONG_STRING_LENGTH
 REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
+
+
+#
+# BUG#23556: TRUNCATE TABLE still maps to DELETE
+#
+CREATE USER bug23556@localhost;
+CREATE DATABASE bug23556;
+GRANT SELECT ON bug23556.* TO bug23556@localhost;
+connect (bug23556,localhost,bug23556,,bug23556);
+
+connection default;
+USE bug23556;
+CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
+GRANT DELETE ON t1 TO bug23556@localhost;
+
+connection bug23556;
+USE bug23556;
+--error ER_TABLEACCESS_DENIED_ERROR
+TRUNCATE t1;
+
+connection default;
+USE bug23556;
+REVOKE DELETE ON t1 FROM bug23556@localhost;
+GRANT DROP ON t1 TO bug23556@localhost;
+
+connection bug23556;
+USE bug23556;
+TRUNCATE t1;
+
+connection default;
+USE bug23556;
+DROP TABLE t1;
+USE test;
+DROP DATABASE bug23556;
+DROP USER bug23556@localhost;
 --echo End of 5.0 tests
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 6d98e4c4aa9..de329ce7b0b 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -988,6 +988,18 @@ explain select * from v1;
 explain select * from (select table_name from information_schema.tables) as a;
 drop view v1;
 
+#
+# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
+#
+create table t1 (f1 int(11));
+create table t2 (f1 int(11), f2 int(11));
+
+select table_name from information_schema.tables
+where table_schema = 'test' and table_name not in
+(select table_name from information_schema.columns
+ where table_schema = 'test' and column_name = 'f3');
+drop table t1,t2;
+
 --echo End of 5.0 tests.
 #
 # Show engines
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index e7405418be7..f29366797f6 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -1045,3 +1045,42 @@ SELECT t1.*, t4.nm
     WHERE t1.id='5';
 
 DROP TABLE t1,t2,t3,t4;
+
+#
+# BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
+#
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT, c INT);
+CREATE TABLE t4 (a INT, c INT);
+CREATE TABLE t5 (a INT, c INT);
+
+SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+
+--error ER_NON_UNIQ_ERROR
+SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+
+SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+
+--error ER_NON_UNIQ_ERROR
+SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+
+DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, b INT);
+
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t3 VALUES (1,1);
+
+--error ER_NON_UNIQ_ERROR
+SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);
+
+DROP TABLE t1,t2,t3;
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 0a5eb17f6f5..1a53344c8ef 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -442,3 +442,14 @@ alter table t1 drop index i3, drop index i2, drop index i1;
 alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
 drop table t1;
 
+
+#
+# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX.
+#
+
+CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES( 1 );
+ALTER TABLE t1 DISABLE KEYS;
+EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
+
+drop table t1;
diff --git a/mysql-test/t/ndb_read_multi_range.test b/mysql-test/t/ndb_read_multi_range.test
index c9059bcbd6b..9d7415880ce 100644
--- a/mysql-test/t/ndb_read_multi_range.test
+++ b/mysql-test/t/ndb_read_multi_range.test
@@ -249,12 +249,12 @@ t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a a
 delete from t22 where a > 245651;
 update t22 set b = a + 1;
 select * from t22 order by 1,2,3;
-select c, count(*)
+select t21.c, count(*)
 from t21 
 inner join t22 using (a)
 where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
-group by c
-order by c;
+group by t21.c
+order by t21.c;
 
 DROP TABLE t1, t11, t12, t21, t22;
 
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index dd70f7edfa7..a9d4488b1be 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -579,7 +579,7 @@ prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ;
 create table t5 (a int) ;
 # rename must fail, t7 does not exist
 # Clean up the filename here because embedded server reports whole path
---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ "" t7.frm t7
+--replace_result \\ / $MYSQLTEST_VARDIR . /master-data/ "" t7.frm t7
 --error 1017
 execute stmt1 ;
 create table t7 (a int) ;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index fbb2b4d8d3d..ba4c789ec08 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3134,3 +3134,77 @@ SELECT * FROM t1 LIMIT 2, -1;
 
 DROP TABLE t1;
 
+#
+# 25407: wrong estimate of NULL keys for unique indexes
+#
+
+CREATE TABLE t1 (
+  ID_with_null int NULL,
+  ID_better int NOT NULL,
+  INDEX idx1 (ID_with_null),
+  INDEX idx2 (ID_better)
+);
+
+INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3);
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL;
+
+SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL;
+SELECT COUNT(*) FROM t1 WHERE ID_better=1;
+
+EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
+
+DROP INDEX idx1 ON t1;
+CREATE UNIQUE INDEX idx1 ON t1(ID_with_null);
+
+EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  ID1_with_null int NULL,
+  ID2_with_null int NULL,
+  ID_better int NOT NULL,
+  INDEX idx1 (ID1_with_null, ID2_with_null),
+  INDEX idx2 (ID_better)
+);
+
+INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3),
+  (3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3);
+
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL;
+INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL;
+
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3;
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL;
+SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
+SELECT COUNT(*) FROM t1 WHERE ID_better=1;
+
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
+
+DROP INDEX idx1 ON t1;
+CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);
+
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ;
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
+EXPLAIN SELECT * FROM t1
+  WHERE ID_better=1 AND ID1_with_null IS NULL AND 
+        (ID2_with_null=1 OR ID2_with_null=2);
+
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 98471622bc5..4fd1542ea0f 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2000,6 +2000,65 @@ SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
 EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
 
 DROP TABLE t1;
+
+#
+# Bug 24653: sorting by expressions containing subselects 
+#            that return more than one row
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (4), (1), (3);
+
+CREATE TABLE t2 (b int, c int);
+INSERT INTO t2 VALUES
+  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
+
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
+--error 1242   
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);  
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;  
+--error 1242   
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
+ 
+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);
+
+
+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;
+--error 1242
+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;
+
+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;
+--error 1242 
+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;
+
+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));
+--error 1242
+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));
+
+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));
+--error 1242
+SELECT a FROM t1 
+  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+
+DROP TABLE t1,t2; 
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 23d78721dbe..ed8480ba464 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -472,3 +472,20 @@ select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z
 
 drop table t1,t2;
 
+#
+# BUG#24420: row-based IN suqueries with aggregation when the left operand
+#            of the subquery predicate may contain NULL values
+#
+
+create table t1 (a int, b int);
+insert into t1 values (0,0), (2,2), (3,3);
+create table t2 (a int, b int);
+insert into t2 values (1,1), (3,3);
+
+select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
+
+insert into t2 values (NULL,4);
+select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
+
+drop table t1,t2;
+
diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test
index 6dd0c83dc92..2a0cf829bae 100644
--- a/mysql-test/t/trigger-grant.test
+++ b/mysql-test/t/trigger-grant.test
@@ -60,8 +60,8 @@ CREATE TABLE t2(user_str TEXT);
 --echo
 --echo ---> connection: default
 
-GRANT INSERT, DELETE ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT INSERT, DELETE ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
 
 #
 # Check that the user must have TRIGGER privilege to create a trigger.
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index b40a8538e04..b6bf8fcb40e 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1505,6 +1505,31 @@ update t1 set i= i+ 10 where j > 2;
 select * from t1;
 drop table t1;
 
+#
+# Bug#23556 TRUNCATE TABLE still maps to DELETE
+#
+CREATE TABLE t1 (a INT PRIMARY KEY);
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+
+CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW 
+  INSERT INTO t2 VALUES (OLD.a);
+
+FLUSH STATUS;
+TRUNCATE t1;
+SHOW STATUS LIKE 'handler_delete';
+SELECT COUNT(*) FROM t2;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+DELETE FROM t2;
+
+FLUSH STATUS;
+DELETE FROM t1;
+SHOW STATUS LIKE 'handler_delete';
+SELECT COUNT(*) FROM t2;
+
+DROP TRIGGER trg_t1;
+DROP TABLE t1,t2;
 
 #
 # Bug #23651 "Server crashes when trigger which uses stored function