Merge xiphis.org:/anubis/antony/work/mysql-5.0-engines

into  xiphis.org:/anubis/antony/work/mysql-5.0-engines.merge

30 files changed, 867 insertions, 101 deletions

diff --git a/client/mysqldump.c b/client/mysqldump.c
index f72cb0171e1..9ccea308a1f 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -2122,8 +2122,7 @@ static void dump_triggers_for_table(char *table,
   }
   if (mysql_num_rows(result))
   {
-    if (opt_compact)
-      fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n");
+    fprintf(sql_file, "\n/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;\n");
     fprintf(sql_file, "\nDELIMITER ;;\n");
   }
   while ((row= mysql_fetch_row(result)))
@@ -2167,9 +2166,11 @@ static void dump_triggers_for_table(char *table,
             row[3] /* Statement */);
   }
   if (mysql_num_rows(result))
+  {
     fprintf(sql_file,
             "DELIMITER ;\n"
-            "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n");
+            "/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;\n");
+  }
   mysql_free_result(result);
   /*
     make sure to set back opt_compatible mode to
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index e692dbf3938..3cba394189a 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1503,4 +1503,33 @@ t1	CREATE TABLE `t1` (
   `c17` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
+
+Bug #26104 Bug on foreign key class constructor
+
+Check that ref_columns is initalized correctly in the constructor
+and semantic checks in mysql_prepare_table work.
+
+We do not need a storage engine that supports foreign keys
+for this test, as the checks are purely syntax-based, and the
+syntax is supported for all engines.
+
+drop table if exists t1,t2;
+create table t1(a int not null, b int not null, primary key (a, b));
+create table t2(a int not null, b int not null, c int not null, primary key (a),
+foreign key fk_bug26104 (b,c) references t1(a));
+ERROR 42000: Incorrect foreign key definition for 'fk_bug26104': Key reference and table reference don't match
+drop table t1;
+create table t1(f1 int,f2 int);
+insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
+flush status;
+create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
+show status like 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	7
+drop table t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index 9b131109809..f113304c767 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -158,6 +158,25 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
 DROP table t1;
+CREATE TABLE t1 (a int PRIMARY KEY);
+CREATE TABLE t2 (b int PRIMARY KEY, a int);
+CREATE TABLE t3 (b int, flag int);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
+INSERT INTO t3(b,flag) VALUES (2, 1);
+SELECT t1.a
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a
+SELECT DISTINCT t1.a, MAX(t3.flag)
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a	MAX(t3.flag)
+SELECT DISTINCT t1.a
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a
+DROP TABLE t1,t2,t3;
 create table t1 (col1 int, col2 varchar(5), col_t1 int);
 create table t2 (col1 int, col2 varchar(5), col_t2 int);
 create table t3 (col1 int, col2 varchar(5), col_t3 int);
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 80b46e5098a..6082a30bce3 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1648,7 +1648,7 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 drop table t2;
 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
-ERROR HY000: Can't create table './test/t2' (errno: 150)
+ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
 create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
 show create table t2;
 Table	Create Table
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index f32878309b8..5b3ca972467 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -739,4 +739,272 @@ drop table if exists t1;
 create table t1 (a int) engine=innodb;
 alter table t1 alter a set default 1;
 drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	const
+rows	16
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+a	b
+1	2
+2	2
+3	2
+4	2
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+11	2
+12	2
+13	2
+14	2
+15	2
+16	2
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t1
+type	range
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	NULL
+rows	16
+Extra	Using where; Using index; Using filesort
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+a	b
+1	2
+2	2
+3	2
+4	2
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+11	2
+12	2
+13	2
+14	2
+15	2
+16	2
+17	1
+18	1
+19	1
+20	1
+21	1
+22	1
+23	1
+24	1
+25	1
+26	1
+27	1
+28	1
+29	1
+30	1
+31	1
+32	1
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+id	1
+select_type	SIMPLE
+table	t1
+type	range
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	NULL
+rows	16
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+a	b
+17	1
+18	1
+19	1
+20	1
+21	1
+22	1
+23	1
+24	1
+25	1
+26	1
+27	1
+28	1
+29	1
+30	1
+31	1
+32	1
+1	2
+2	2
+3	2
+4	2
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+11	2
+12	2
+13	2
+14	2
+15	2
+16	2
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	const
+rows	8
+Extra	Using where; Using index; Using filesort
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a + 8  FROM t1;
+INSERT INTO t1 SELECT a + 16 FROM t1;
+CREATE PROCEDURE p1 ()
+BEGIN
+DECLARE i INT DEFAULT 50;
+DECLARE cnt INT;
+START TRANSACTION;
+ALTER TABLE t1 ENGINE=InnoDB;
+COMMIT;
+START TRANSACTION;
+WHILE (i > 0) DO
+SET i = i - 1;
+SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
+END WHILE;
+COMMIT;
+END;|
+CALL p1();
+CALL p1();
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index da05fe7bc5b..7178cbb5d49 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2238,6 +2238,8 @@ INSERT INTO `t1` VALUES (1,NULL),(2,NULL),(4,NULL),(11,NULL);
 /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
 UNLOCK TABLES;
 
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
 DELIMITER ;;
 /*!50003 SET SESSION SQL_MODE="" */;;
 /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW begin
@@ -2260,7 +2262,7 @@ end if;
 end */;;
 
 DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
 DROP TABLE IF EXISTS `t2`;
 CREATE TABLE `t2` (
   `a` int(11) default NULL
@@ -2271,6 +2273,8 @@ LOCK TABLES `t2` WRITE;
 /*!40000 ALTER TABLE `t2` ENABLE KEYS */;
 UNLOCK TABLES;
 
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
 DELIMITER ;;
 /*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER" */;;
 /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW begin
@@ -2280,7 +2284,7 @@ end if;
 end */;;
 
 DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -2628,13 +2632,15 @@ INSERT INTO "t1 test" VALUES (1),(2),(3);
 /*!40000 ALTER TABLE "t1 test" ENABLE KEYS */;
 UNLOCK TABLES;
 
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
 DELIMITER ;;
 /*!50003 SET SESSION SQL_MODE="" */;;
 /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
 INSERT INTO `t2 test` SET a2 = NEW.a1; END */;;
 
 DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
 DROP TABLE IF EXISTS "t2 test";
 CREATE TABLE "t2 test" (
   "a2" int(11) default NULL
@@ -2788,6 +2794,8 @@ LOCK TABLES `t1` WRITE;
 /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
 UNLOCK TABLES;
 
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
 DELIMITER ;;
 /*!50003 SET SESSION SQL_MODE="IGNORE_SPACE" */;;
 /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN
@@ -2795,7 +2803,7 @@ SET new.a = 0;
 END */;;
 
 DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -3334,5 +3342,27 @@ SELECT * FROM v1;
 1
 DROP VIEW v1;
 #
+# Bug #29788: mysqldump discards the NO_AUTO_VALUE_ON_ZERO value of
+#             the SQL_MODE variable after the dumping of triggers.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TRIGGER t1bd BEFORE DELETE ON t1 FOR EACH ROW BEGIN END;
+CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
+SET @TMP_SQL_MODE = @@SQL_MODE;
+SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t2 VALUES (0), (1), (2);
+SET SQL_MODE = @TMP_SQL_MODE;
+SELECT * FROM t2;
+c1
+0
+1
+2
+SELECT * FROM t2;
+c1
+0
+1
+2
+DROP TABLE t1,t2;
+#
 # End of 5.0 tests
 #
diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
index 5eac54803f0..c19bd1abd26 100644
--- a/mysql-test/r/sp-prelocking.result
+++ b/mysql-test/r/sp-prelocking.result
@@ -267,4 +267,26 @@ drop table bug_27907_logs;
 insert into bug_27907_t1(a) values (1);
 ERROR 42S02: Table 'test.bug_27907_logs' doesn't exist
 drop table bug_27907_t1;
+
+Bug#22427 create table if not exists + stored function results in
+inconsistent behavior
+
+Add a test case, the bug itself was fixed by the patch for
+Bug#20662
+
+drop table if exists t1;
+drop function if exists f_bug22427;
+create table t1 (i int);
+insert into t1 values (1);
+create function f_bug22427() returns int return (select max(i) from t1);
+select f_bug22427();
+f_bug22427()
+1
+create table if not exists t1 select f_bug22427() as i;
+Warnings:
+Note	1050	Table 't1' already exists
+create table t1 select f_bug22427() as i;
+ERROR 42S01: Table 't1' already exists
+drop table t1;
+drop function f_bug22427;
 End of 5.0 tests
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 7a4deb3ea5f..448428d07f4 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,5 +1,7 @@
 use test;
 drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
 create table t1 (
 id   char(16) not null default '',
 data int not null
@@ -6144,7 +6146,7 @@ drop table t1,t2;
 CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
 CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
 set @a=0;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
 begin
 insert into t1 values (null);
 set @a=@a+1;
@@ -6201,4 +6203,34 @@ Level	Code	Message
 use test;
 drop procedure sp_bug29050;
 drop table t1;
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	c1	5	NULL	5	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	c1	5	NULL	5	Using where; Using index
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 4b18e525e62..a07318435f6 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1933,4 +1933,32 @@ Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi
 After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
 drop view v1;
 drop table t1, t2, t1_op_log;
+
+Bug#27248 Triggers: error if insert affects temporary table
+
+The bug was fixed by the fix for Bug#26141
+
+drop table if exists t1;
+drop temporary table if exists t2;
+create table t1 (s1 int);
+create temporary table t2 (s1 int);
+create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
+create trigger t1_bd before delete on t1 for each row delete from t2;
+insert into t1 values (0);
+insert into t1 values (0);
+select * from t1;
+s1
+0
+0
+select * from t2;
+s1
+0
+0
+delete from t1;
+select * from t1;
+s1
+select * from t2;
+s1
+drop table t1;
+drop temporary table t2;
 End of 5.0 tests
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index ca516f027ba..994001d94e5 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1829,3 +1829,27 @@ c1 + 0
 0
 2
 DROP TABLE t1,t2;
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 3
+SELECT a FROM t1;
+a
+d
+a
+
+c
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT a FROM t1 WHERE a=0;
+a
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	1	const	1	Using index
+SELECT a FROM t1 WHERE a=0;
+a
+
+DROP TABLE t1;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 99f3fea416a..64081c0248a 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1118,5 +1118,34 @@ show create table t1;
 
 drop table t1;
 
+--echo
+--echo Bug #26104 Bug on foreign key class constructor
+--echo
+--echo Check that ref_columns is initalized correctly in the constructor
+--echo and semantic checks in mysql_prepare_table work.
+--echo
+--echo We do not need a storage engine that supports foreign keys
+--echo for this test, as the checks are purely syntax-based, and the
+--echo syntax is supported for all engines.
+--echo
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+create table t1(a int not null, b int not null, primary key (a, b));
+--error ER_WRONG_FK_DEF
+create table t2(a int not null, b int not null, c int not null, primary key (a),
+foreign key fk_bug26104 (b,c) references t1(a));
+drop table t1;
+
+#
+# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
+#
+create table t1(f1 int,f2 int);
+insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
+flush status;
+create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
+show status like 'handler_read%';
+drop table t1,t2;
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 9bea78a7bca..827b83f11a0 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -151,6 +151,32 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
 
 DROP table t1;  
 
+#
+# Bug #29911: HAVING clause depending on constant table and evaluated to false
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY);
+CREATE TABLE t2 (b int PRIMARY KEY, a int);
+CREATE TABLE t3 (b int, flag int);
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
+INSERT INTO t3(b,flag) VALUES (2, 1);
+
+SELECT t1.a
+  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a, MAX(t3.flag)
+  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a
+  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+DROP TABLE t1,t2,t3;
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index a9679c01071..04dfa1d0836 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1180,7 +1180,7 @@ drop table t2;
 # Clean up filename -- embedded server reports whole path without .frm,
 # regular server reports relative path with .frm (argh!)
 --replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t2.frm t2
---error 1005
+--error ER_WRONG_FK_DEF
 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
 
 # bug#3749
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 0d43d13ec3a..63431e10bbf 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -740,6 +740,8 @@ SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
 set @@sort_buffer_size=default;
 
 DROP TABLE t1,t2;
+
+
 # Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
 # (possible deadlock).
 #
@@ -754,4 +756,89 @@ create table t1 (a int) engine=innodb;
 alter table t1 alter a set default 1;
 drop table t1;
 
+#
+# Bug #28591: MySQL need not sort the records in case of ORDER BY
+# primary_key on InnoDB table
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+  ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+
+DROP TABLE t1,t2;
+
+
+#
+# Bug #29644: alter table hangs if records locked in share mode by long
+# running transaction
+#
+
+CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a + 8  FROM t1;
+INSERT INTO t1 SELECT a + 16 FROM t1;
+
+DELIMITER |;
+CREATE PROCEDURE p1 ()
+BEGIN
+  DECLARE i INT DEFAULT 50;
+  DECLARE cnt INT;
+  START TRANSACTION;
+    ALTER TABLE t1 ENGINE=InnoDB;
+  COMMIT;
+  START TRANSACTION;
+  WHILE (i > 0) DO
+    SET i = i - 1;
+    SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
+  END WHILE;
+  COMMIT;
+END;|
+
+DELIMITER ;|
+
+CONNECT (con1,localhost,root,,);
+CONNECT (con2,localhost,root,,);
+
+CONNECTION con1;
+SEND CALL p1();
+CONNECTION con2;
+SEND CALL p1();
+CONNECTION default;
+CALL p1();
+
+CONNECTION con1;
+REAP;
+CONNECTION con2;
+REAP;
+CONNECTION default;
+DISCONNECT con1;
+DISCONNECT con2;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index d42162541de..3c62577e781 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1555,5 +1555,27 @@ SELECT * FROM v1;
 DROP VIEW v1;
 
 --echo #
+--echo # Bug #29788: mysqldump discards the NO_AUTO_VALUE_ON_ZERO value of
+--echo #             the SQL_MODE variable after the dumping of triggers.
+--echo #
+
+CREATE TABLE t1 (c1 INT);
+CREATE TRIGGER t1bd BEFORE DELETE ON t1 FOR EACH ROW BEGIN END;
+
+CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
+
+SET @TMP_SQL_MODE = @@SQL_MODE;
+SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t2 VALUES (0), (1), (2);
+SET SQL_MODE = @TMP_SQL_MODE;
+SELECT * FROM t2;
+
+--exec $MYSQL_DUMP --routines test >$MYSQLTEST_VARDIR/tmp/bug29788.sql
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug29788.sql
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;
+
+--echo #
 --echo # End of 5.0 tests
 --echo #
diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
index ec5b7fbad7c..60e97260839 100644
--- a/mysql-test/t/sp-prelocking.test
+++ b/mysql-test/t/sp-prelocking.test
@@ -333,4 +333,27 @@ insert into bug_27907_t1(a) values (1);
 
 drop table bug_27907_t1;
 
+--echo
+--echo Bug#22427 create table if not exists + stored function results in
+--echo inconsistent behavior
+--echo
+--echo Add a test case, the bug itself was fixed by the patch for
+--echo Bug#20662
+--echo
+--disable_warnings
+drop table if exists t1;
+drop function if exists f_bug22427;
+--enable_warnings
+create table t1 (i int);
+insert into t1 values (1);
+create function f_bug22427() returns int return (select max(i) from t1);
+select f_bug22427();
+# Until this bug was fixed, the following emitted error
+# ERROR 1213: Deadlock found when trying to get lock
+create table if not exists t1 select f_bug22427() as i;
+--error ER_TABLE_EXISTS_ERROR
+create table t1 select f_bug22427() as i;
+drop table t1;
+drop function f_bug22427;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index ef66f1c98f0..5aaf4f21ae0 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -23,6 +23,8 @@ use test;
 #
 --disable_warnings
 drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
 --enable_warnings
 create table t1 (
 	id   char(16) not null default '',
@@ -7098,7 +7100,7 @@ CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
 set @a=0;
 
 delimiter |;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
 begin
 insert into t1 values (null);
 set @a=@a+1;
@@ -7172,4 +7174,34 @@ use test;
 drop procedure sp_bug29050;
 drop table t1;
 
+#
+# Bug #29338: no optimization for stored functions with a trivial body
+# always returning constant.
+#
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index a6390036322..2f62ad38621 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -2194,4 +2194,27 @@ drop table t1, t2, t1_op_log;
 
 #
 # TODO: test LOAD DATA INFILE
+#
+--echo
+--echo Bug#27248 Triggers: error if insert affects temporary table
+--echo
+--echo The bug was fixed by the fix for Bug#26141
+--echo
+--disable_warnings
+drop table if exists t1;
+drop temporary table if exists t2;
+--enable_warnings
+create table t1 (s1 int);
+create temporary table t2 (s1 int);
+create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
+create trigger t1_bd before delete on t1 for each row delete from t2;
+insert into t1 values (0);
+insert into t1 values (0);
+select * from t1;
+select * from t2;
+delete from t1;
+select * from t1;
+select * from t2;
+drop table t1;
+drop temporary table t2;
 --echo End of 5.0 tests
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index fbba38f926d..3dedb0018b1 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -200,3 +200,27 @@ CREATE TABLE t2 SELECT * FROM t1;
 SELECT c1 + 0 FROM t2;
 
 DROP TABLE t1,t2;
+
+#
+# Bug#29661: Lookup by 0 for a primary index over a enum type  
+#
+
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+
+SELECT a FROM t1;
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+DROP TABLE t1;
+
+
+
+
+
diff --git a/sql-common/client.c b/sql-common/client.c
index a5dbeb66658..cb1d224ef2e 100644
--- a/sql-common/client.c
+++ b/sql-common/client.c
@@ -1226,12 +1226,12 @@ unpack_fields(MYSQL_DATA *data,MEM_ROOT *alloc,uint fields,
       /* fields count may be wrong */
       DBUG_ASSERT((uint) (field - result) < fields);
       cli_fetch_lengths(&lengths[0], row->data, default_value ? 8 : 7);
-      field->catalog  = strdup_root(alloc,(char*) row->data[0]);
-      field->db       = strdup_root(alloc,(char*) row->data[1]);
-      field->table    = strdup_root(alloc,(char*) row->data[2]);
-      field->org_table= strdup_root(alloc,(char*) row->data[3]);
-      field->name     = strdup_root(alloc,(char*) row->data[4]);
-      field->org_name = strdup_root(alloc,(char*) row->data[5]);
+      field->catalog=   strmake_root(alloc,(char*) row->data[0], lengths[0]);
+      field->db=        strmake_root(alloc,(char*) row->data[1], lengths[1]);
+      field->table=     strmake_root(alloc,(char*) row->data[2], lengths[2]);
+      field->org_table= strmake_root(alloc,(char*) row->data[3], lengths[3]);
+      field->name=      strmake_root(alloc,(char*) row->data[4], lengths[4]);
+      field->org_name=  strmake_root(alloc,(char*) row->data[5], lengths[5]);
 
       field->catalog_length=	lengths[0];
       field->db_length=		lengths[1];
@@ -1252,7 +1252,7 @@ unpack_fields(MYSQL_DATA *data,MEM_ROOT *alloc,uint fields,
         field->flags|= NUM_FLAG;
       if (default_value && row->data[7])
       {
-        field->def=strdup_root(alloc,(char*) row->data[7]);
+        field->def=strmake_root(alloc,(char*) row->data[7], lengths[7]);
 	field->def_length= lengths[7];
       }
       else
diff --git a/sql/field.cc b/sql/field.cc
index 78b2515c55f..3f74210807b 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7641,8 +7641,11 @@ int Field_enum::store(longlong nr, bool unsigned_val)
   if ((ulonglong) nr > typelib->count || nr == 0)
   {
     set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1);
-    nr=0;
-    error=1;
+    if (nr != 0 || table->in_use->count_cuted_fields)
+    {
+      nr= 0;
+      error= 1;
+    }
   }
   store_type((ulonglong) (uint) nr);
   return error;
diff --git a/sql/filesort.cc b/sql/filesort.cc
index f8868ed6927..db73ede99b0 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -1430,7 +1430,8 @@ get_addon_fields(THD *thd, Field **ptabfield, uint sortlength, uint *plength)
     doesn't work for alter table
   */
   if (thd->lex->sql_command != SQLCOM_SELECT &&
-      thd->lex->sql_command != SQLCOM_INSERT_SELECT)
+      thd->lex->sql_command != SQLCOM_INSERT_SELECT &&
+      thd->lex->sql_command != SQLCOM_CREATE_TABLE)
     return 0;
   for (pfield= ptabfield; (field= *pfield) ; pfield++)
   {
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index b03dc9bb986..1c952f490da 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -6721,17 +6721,6 @@ ha_innobase::store_lock(
 		    && !thd->tablespace_op
 		    && thd->lex->sql_command != SQLCOM_TRUNCATE
 		    && thd->lex->sql_command != SQLCOM_OPTIMIZE
-
-#ifdef __WIN__
-                /* For alter table on win32 for succesful operation
-                completion it is used TL_WRITE(=10) lock instead of
-                TL_WRITE_ALLOW_READ(=6), however here in innodb handler
-                TL_WRITE is lifted to TL_WRITE_ALLOW_WRITE, which causes
-                race condition when several clients do alter table
-                simultaneously (bug #17264). This fix avoids the problem. */
-		    && thd->lex->sql_command != SQLCOM_ALTER_TABLE
-#endif
-
 		    && thd->lex->sql_command != SQLCOM_CREATE_TABLE) {
 
 			lock_type = TL_WRITE_ALLOW_WRITE;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index b256ce4624a..e05f0a45083 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -5591,5 +5591,15 @@ Item_func_sp::fix_fields(THD *thd, Item **ref)
     
 #endif /* ! NO_EMBEDDED_ACCESS_CHECKS */
   }
+  if (!m_sp->m_chistics->detistic)
+   used_tables_cache |= RAND_TABLE_BIT;
   DBUG_RETURN(res);
 }
+
+
+void Item_func_sp::update_used_tables()
+{
+  Item_func::update_used_tables();
+  if (!m_sp->m_chistics->detistic)
+   used_tables_cache |= RAND_TABLE_BIT;
+}
diff --git a/sql/item_func.h b/sql/item_func.h
index 9a0201cb28b..56b5e75652c 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1467,7 +1467,7 @@ public:
   virtual ~Item_func_sp()
   {}
 
-  table_map used_tables() const { return RAND_TABLE_BIT; }
+  void update_used_tables();
 
   void cleanup();
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index bf1c6d7cb0d..911e8d4d1f5 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -62,11 +62,11 @@ Prelock_error_handler::handle_error(uint sql_errno,
   if (sql_errno == ER_NO_SUCH_TABLE)
   {
     m_handled_errors++;
-    return TRUE;                                // 'TRUE', as per coding style
+    return TRUE;
   }
 
   m_unhandled_errors++;
-  return FALSE;                                 // 'FALSE', as per coding style
+  return FALSE;
 }
 
 
@@ -2674,7 +2674,7 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
   */
   for (tables= *start; tables ;tables= tables->next_global)
   {
-    safe_to_ignore_table= FALSE;                // 'FALSE', as per coding style
+    safe_to_ignore_table= FALSE;
 
     if (tables->lock_type == TL_WRITE_DEFAULT)
     {
@@ -2938,13 +2938,6 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 
   if (table)
   {
-#if defined( __WIN__) || defined(OS2)
-    /* Win32 can't drop a file that is open */
-    if (lock_type == TL_WRITE_ALLOW_READ)
-    {
-      lock_type= TL_WRITE;
-    }
-#endif /* __WIN__ || OS2 */
     table_list->lock_type= lock_type;
     table_list->table=	   table;
     table->grant= table_list->grant;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 445a3ce437c..058f130d4e7 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -450,7 +450,7 @@ public:
 	      Table_ident *table,   List<key_part_spec> &ref_cols,
 	      uint delete_opt_arg, uint update_opt_arg, uint match_opt_arg)
     :Key(FOREIGN_KEY, name_arg, HA_KEY_ALG_UNDEF, 0, cols),
-    ref_table(table), ref_columns(cols),
+    ref_table(table), ref_columns(ref_cols),
     delete_opt(delete_opt_arg), update_opt(update_opt_arg),
     match_opt(match_opt_arg)
   {}
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 73f8c5e4418..19c9360b0ed 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1702,18 +1702,18 @@ Delayed_insert *find_handler(THD *thd, TABLE_LIST *table_list)
   thd->proc_info="waiting for delay_list";
   pthread_mutex_lock(&LOCK_delayed_insert);	// Protect master list
   I_List_iterator<Delayed_insert> it(delayed_threads);
-  Delayed_insert *tmp;
-  while ((tmp=it++))
+  Delayed_insert *di;
+  while ((di= it++))
   {
-    if (!strcmp(tmp->thd.db,table_list->db) &&
-	!strcmp(table_list->table_name,tmp->table->s->table_name))
+    if (!strcmp(table_list->db, di->table_list.db) &&
+	!strcmp(table_list->table_name, di->table_list.table_name))
     {
-      tmp->lock();
+      di->lock();
       break;
     }
   }
   pthread_mutex_unlock(&LOCK_delayed_insert); // For unlink from list
-  return tmp;
+  return di;
 }
 
 
@@ -1739,21 +1739,41 @@ Delayed_insert *find_handler(THD *thd, TABLE_LIST *table_list)
                 Two latter cases indicate a request for lock upgrade.
 
   XXX: why do we regard INSERT DELAYED into a view as an error and
-  do not simply a lock upgrade?
+  do not simply perform a lock upgrade?
+
+  TODO: The approach with using two mutexes to work with the
+  delayed thread list -- LOCK_delayed_insert and
+  LOCK_delayed_create -- is redundant, and we only need one of
+  them to protect the list.  The reason we have two locks is that
+  we do not want to block look-ups in the list while we're waiting
+  for the newly created thread to open the delayed table. However,
+  this wait itself is redundant -- we always call get_local_table
+  later on, and there wait again until the created thread acquires
+  a table lock.
+
+  As is redundant the concept of locks_in_memory, since we already
+  have another counter with similar semantics - tables_in_use,
+  both of them are devoted to counting the number of producers for
+  a given consumer (delayed insert thread), only at different
+  stages of producer-consumer relationship.
+
+  'dead' and 'status' variables in Delayed_insert are redundant
+  too, since there is already 'di->thd.killed' and
+  di->stacked_inserts.
 */
 
 static
 bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
 {
   int error;
-  Delayed_insert *tmp;
+  Delayed_insert *di;
   DBUG_ENTER("delayed_get_table");
 
   /* Must be set in the parser */
   DBUG_ASSERT(table_list->db);
 
   /* Find the thread which handles this table. */
-  if (!(tmp=find_handler(thd,table_list)))
+  if (!(di= find_handler(thd, table_list)))
   {
     /*
       No match. Create a new thread to handle the table, but
@@ -1767,9 +1787,9 @@ bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
       The first search above was done without LOCK_delayed_create.
       Another thread might have created the handler in between. Search again.
     */
-    if (! (tmp= find_handler(thd, table_list)))
+    if (! (di= find_handler(thd, table_list)))
     {
-      if (!(tmp=new Delayed_insert()))
+      if (!(di= new Delayed_insert()))
       {
 	my_error(ER_OUTOFMEMORY,MYF(0),sizeof(Delayed_insert));
         thd->fatal_error();
@@ -1778,28 +1798,30 @@ bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
       pthread_mutex_lock(&LOCK_thread_count);
       thread_count++;
       pthread_mutex_unlock(&LOCK_thread_count);
-      tmp->thd.set_db(table_list->db, strlen(table_list->db));
-      tmp->thd.query= my_strdup(table_list->table_name,MYF(MY_WME));
-      if (tmp->thd.db == NULL || tmp->thd.query == NULL)
+      di->thd.set_db(table_list->db, strlen(table_list->db));
+      di->thd.query= my_strdup(table_list->table_name, MYF(MY_WME));
+      if (di->thd.db == NULL || di->thd.query == NULL)
       {
         /* The error is reported */
-	delete tmp;
+	delete di;
         thd->fatal_error();
         goto end_create;
       }
-      tmp->table_list= *table_list;			// Needed to open table
-      tmp->table_list.alias= tmp->table_list.table_name= tmp->thd.query;
-      tmp->lock();
-      pthread_mutex_lock(&tmp->mutex);
-      if ((error=pthread_create(&tmp->thd.real_id,&connection_attrib,
-				handle_delayed_insert,(void*) tmp)))
+      di->table_list= *table_list;			// Needed to open table
+      /* Replace volatile strings with local copies */
+      di->table_list.alias= di->table_list.table_name= di->thd.query;
+      di->table_list.db= di->thd.db;
+      di->lock();
+      pthread_mutex_lock(&di->mutex);
+      if ((error= pthread_create(&di->thd.real_id, &connection_attrib,
+                                 handle_delayed_insert, (void*) di)))
       {
 	DBUG_PRINT("error",
 		   ("Can't create thread to handle delayed insert (error %d)",
 		    error));
-	pthread_mutex_unlock(&tmp->mutex);
-	tmp->unlock();
-	delete tmp;
+	pthread_mutex_unlock(&di->mutex);
+	di->unlock();
+	delete di;
 	my_error(ER_CANT_CREATE_THREAD, MYF(0), error);
         thd->fatal_error();
         goto end_create;
@@ -1807,15 +1829,15 @@ bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
 
       /* Wait until table is open */
       thd->proc_info="waiting for handler open";
-      while (!tmp->thd.killed && !tmp->table && !thd->killed)
+      while (!di->thd.killed && !di->table && !thd->killed)
       {
-	pthread_cond_wait(&tmp->cond_client,&tmp->mutex);
+	pthread_cond_wait(&di->cond_client, &di->mutex);
       }
-      pthread_mutex_unlock(&tmp->mutex);
+      pthread_mutex_unlock(&di->mutex);
       thd->proc_info="got old table";
-      if (tmp->thd.killed)
+      if (di->thd.killed)
       {
-	if (tmp->thd.net.report_error)
+	if (di->thd.net.report_error)
 	{
           /*
             Copy the error message. Note that we don't treat fatal
@@ -1823,31 +1845,34 @@ bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
             main thread. Use of my_message will enable stored
             procedures continue handlers.
           */
-          my_message(tmp->thd.net.last_errno, tmp->thd.net.last_error,
+          my_message(di->thd.net.last_errno, di->thd.net.last_error,
                      MYF(0));
 	}
-	tmp->unlock();
+	di->unlock();
         goto end_create;
       }
       if (thd->killed)
       {
-	tmp->unlock();
+	di->unlock();
         goto end_create;
       }
+      pthread_mutex_lock(&LOCK_delayed_insert);
+      delayed_threads.append(di);
+      pthread_mutex_unlock(&LOCK_delayed_insert);
     }
     pthread_mutex_unlock(&LOCK_delayed_create);
   }
 
-  pthread_mutex_lock(&tmp->mutex);
-  table_list->table= tmp->get_local_table(thd);
-  pthread_mutex_unlock(&tmp->mutex);
+  pthread_mutex_lock(&di->mutex);
+  table_list->table= di->get_local_table(thd);
+  pthread_mutex_unlock(&di->mutex);
   if (table_list->table)
   {
     DBUG_ASSERT(thd->net.report_error == 0);
-    thd->di=tmp;
+    thd->di= di;
   }
   /* Unlock the delayed insert object after its last access. */
-  tmp->unlock();
+  di->unlock();
   DBUG_RETURN(table_list->table == NULL);
 
 end_create:
@@ -2077,26 +2102,26 @@ void kill_delayed_threads(void)
   VOID(pthread_mutex_lock(&LOCK_delayed_insert)); // For unlink from list
 
   I_List_iterator<Delayed_insert> it(delayed_threads);
-  Delayed_insert *tmp;
-  while ((tmp=it++))
+  Delayed_insert *di;
+  while ((di= it++))
   {
-    tmp->thd.killed= THD::KILL_CONNECTION;
-    if (tmp->thd.mysys_var)
+    di->thd.killed= THD::KILL_CONNECTION;
+    if (di->thd.mysys_var)
     {
-      pthread_mutex_lock(&tmp->thd.mysys_var->mutex);
-      if (tmp->thd.mysys_var->current_cond)
+      pthread_mutex_lock(&di->thd.mysys_var->mutex);
+      if (di->thd.mysys_var->current_cond)
       {
 	/*
 	  We need the following test because the main mutex may be locked
 	  in handle_delayed_insert()
 	*/
-	if (&tmp->mutex != tmp->thd.mysys_var->current_mutex)
-	  pthread_mutex_lock(tmp->thd.mysys_var->current_mutex);
-	pthread_cond_broadcast(tmp->thd.mysys_var->current_cond);
-	if (&tmp->mutex != tmp->thd.mysys_var->current_mutex)
-	  pthread_mutex_unlock(tmp->thd.mysys_var->current_mutex);
+	if (&di->mutex != di->thd.mysys_var->current_mutex)
+	  pthread_mutex_lock(di->thd.mysys_var->current_mutex);
+	pthread_cond_broadcast(di->thd.mysys_var->current_cond);
+	if (&di->mutex != di->thd.mysys_var->current_mutex)
+	  pthread_mutex_unlock(di->thd.mysys_var->current_mutex);
       }
-      pthread_mutex_unlock(&tmp->thd.mysys_var->mutex);
+      pthread_mutex_unlock(&di->thd.mysys_var->mutex);
     }
   }
   VOID(pthread_mutex_unlock(&LOCK_delayed_insert)); // For unlink from list
@@ -2176,11 +2201,6 @@ pthread_handler_t handle_delayed_insert(void *arg)
   }
   di->table->copy_blobs=1;
 
-  /* One can now use this */
-  pthread_mutex_lock(&LOCK_delayed_insert);
-  delayed_threads.append(di);
-  pthread_mutex_unlock(&LOCK_delayed_insert);
-
   /* Tell client that the thread is initialized */
   pthread_cond_signal(&di->cond_client);
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d8bf8466f58..2d9d261bb31 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12009,6 +12009,7 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx,
   key_part_end=key_part+table->key_info[idx].key_parts;
   key_part_map const_key_parts=table->const_key_parts[idx];
   int reverse=0;
+  my_bool on_primary_key= FALSE;
   DBUG_ENTER("test_if_order_by_key");
 
   for (; order ; order=order->next, const_key_parts>>=1)
@@ -12023,7 +12024,31 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx,
     for (; const_key_parts & 1 ; const_key_parts>>= 1)
       key_part++; 
 
-    if (key_part == key_part_end || key_part->field != field)
+    if (key_part == key_part_end)
+    {
+      /* 
+        We are at the end of the key. Check if the engine has the primary
+        key as a suffix to the secondary keys. If it has continue to check
+        the primary key as a suffix.
+      */
+      if (!on_primary_key &&
+          (table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
+          table->s->db_type == DB_TYPE_INNODB &&
+          table->s->primary_key != MAX_KEY)
+      {
+        on_primary_key= TRUE;
+        key_part= table->key_info[table->s->primary_key].key_part;
+        key_part_end=key_part+table->key_info[table->s->primary_key].key_parts;
+        const_key_parts=table->const_key_parts[table->s->primary_key];
+
+        for (; const_key_parts & 1 ; const_key_parts>>= 1)
+          key_part++; 
+      }
+      else
+        DBUG_RETURN(0);
+    }
+
+    if (key_part->field != field)
       DBUG_RETURN(0);
 
     /* set flag to 1 if we can use read-next on key, else to -1 */
@@ -12034,7 +12059,8 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx,
     reverse=flag;				// Remember if reverse
     key_part++;
   }
-  *used_key_parts= (uint) (key_part - table->key_info[idx].key_part);
+  *used_key_parts= on_primary_key ? table->key_info[idx].key_parts :
+    (uint) (key_part - table->key_info[idx].key_part);
   if (reverse == -1 && !(table->file->index_flags(idx, *used_key_parts-1, 1) &
                          HA_READ_PREV))
     reverse= 0;                                 // Index can't be used
@@ -12738,7 +12764,7 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having)
       field_count++;
   }
 
-  if (!field_count && !(join->select_options & OPTION_FOUND_ROWS)) 
+  if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having) 
   {                    // only const items with no OPTION_FOUND_ROWS
     join->unit->select_limit_cnt= 1;		// Only send first row
     DBUG_RETURN(0);
diff --git a/sql/table.cc b/sql/table.cc
index f24f5c6fbcc..ce894e6910f 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -780,7 +780,12 @@ int openfrm(THD *thd, const char *name, const char *alias, uint db_stat,
 	      the primary key, then we can use any key to find this column
 	    */
 	    if (ha_option & HA_PRIMARY_KEY_IN_READ_INDEX)
+            {
 	      field->part_of_key= share->keys_in_use;
+              if (share->db_type == DB_TYPE_INNODB && 
+                  field->part_of_sortkey.is_set(key))
+                field->part_of_sortkey= share->keys_in_use;
+            }
 	  }
 	  if (field->key_length() != key_part->length)
 	  {