Merge whalegate.ndb.mysql.com:/home/tomas/mysql-5.0

into  whalegate.ndb.mysql.com:/home/tomas/mysql-5.0-ndb

89 files changed, 3436 insertions, 766 deletions

diff --git a/CMakeLists.txt b/CMakeLists.txt
index f5a1c0cfaba..890ee2676e8 100755
--- a/CMakeLists.txt
+++ b/CMakeLists.txt
@@ -154,6 +154,11 @@ IF(EMBED_MANIFESTS)
     # Disable automatic manifest generation.
     STRING(REPLACE "/MANIFEST" "/MANIFEST:NO" CMAKE_EXE_LINKER_FLAGS 
     	   ${CMAKE_EXE_LINKER_FLAGS})
+    # Explicitly disable it since it is the default for newer versions of VS
+    STRING(REGEX MATCH "MANIFEST:NO" tmp_manifest ${CMAKE_EXE_LINKER_FLAGS})
+    IF(NOT tmp_manifest)
+        SET(CMAKE_EXE_LINKER_FLAGS "${CMAKE_EXE_LINKER_FLAGS} /MANIFEST:NO")
+    ENDIF(tmp_manifest)
     # Set the processor architecture.
     IF(CMAKE_GENERATOR MATCHES "Visual Studio 8 2005 Win64")
     	SET(PROCESSOR_ARCH "X64") 
diff --git a/configure.in b/configure.in
index 1026c855bf2..a779ab80ced 100644
--- a/configure.in
+++ b/configure.in
@@ -7,7 +7,7 @@ AC_INIT(sql/mysqld.cc)
 AC_CANONICAL_SYSTEM
 # The Docs Makefile.am parses this line!
 # remember to also change ndb version below and update version.c in ndb
-AM_INIT_AUTOMAKE(mysql, 5.0.42)
+AM_INIT_AUTOMAKE(mysql, 5.0.44)
 AM_CONFIG_HEADER(config.h)
 
 PROTOCOL_VERSION=10
@@ -23,7 +23,7 @@ NDB_SHARED_LIB_VERSION=$NDB_SHARED_LIB_MAJOR_VERSION:0:0
 # ndb version
 NDB_VERSION_MAJOR=5
 NDB_VERSION_MINOR=0
-NDB_VERSION_BUILD=42
+NDB_VERSION_BUILD=44
 NDB_VERSION_STATUS=""
 
 # Set all version vars based on $VERSION. How do we do this more elegant ?
diff --git a/include/decimal.h b/include/decimal.h
index 56962009025..c5385b58658 100644
--- a/include/decimal.h
+++ b/include/decimal.h
@@ -47,6 +47,7 @@ int decimal_bin_size(int precision, int scale);
 int decimal_result_size(decimal_t *from1, decimal_t *from2, char op,
                         int param);
 
+int decimal_intg(decimal_t *from);
 int decimal_add(decimal_t *from1, decimal_t *from2, decimal_t *to);
 int decimal_sub(decimal_t *from1, decimal_t *from2, decimal_t *to);
 int decimal_cmp(decimal_t *from1, decimal_t *from2);
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index 541a15561e2..f18d1c9b583 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -352,3 +352,13 @@ select c1 mod 50 as result from t1;
 result
 6
 drop table t1;
+select cast(19999999999999999999 as signed);
+cast(19999999999999999999 as signed)
+9223372036854775807
+Warnings:
+Error	1292	Truncated incorrect DECIMAL value: ''
+select cast(-19999999999999999999 as signed);
+cast(-19999999999999999999 as signed)
+-9223372036854775808
+Warnings:
+Error	1292	Truncated incorrect DECIMAL value: ''
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 454a3766572..6eceeff87e2 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -103,7 +103,7 @@ Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'a'
 select 10.0+cast('a' as decimal);
 10.0+cast('a' as decimal)
-10.00
+10.0
 Warnings:
 Warning	1292	Truncated incorrect DECIMAL value: 'a'
 select 10E+0+'a';
@@ -378,7 +378,9 @@ create table t1(s1 time);
 insert into t1 values ('11:11:11');
 select cast(s1 as decimal(7,2)) from t1;
 cast(s1 as decimal(7,2))
-111111.00
+99999.99
+Warnings:
+Error	1264	Out of range value adjusted for column 'cast(s1 as decimal(7,2))' at row 1
 drop table t1;
 CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
 mt mediumtext, lt longtext);
@@ -386,7 +388,7 @@ INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
 SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
 CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
 CAST(v AS DECIMAL)	CAST(tt AS DECIMAL)	CAST(t AS DECIMAL)	CAST(mt AS DECIMAL)	CAST(lt AS DECIMAL)
-1.01	2.02	3.03	4.04	5.05
+1	2	3	4	5
 DROP TABLE t1;
 select cast(NULL as decimal(6)) as t1;
 t1
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index afa005e74c0..e1262c7d2c2 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -769,6 +769,100 @@ t1	CREATE TABLE `t1` (
   `i` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295
 drop table t1;
+create table t1 select * from t2;
+ERROR 42S02: Table 'test.t2' doesn't exist
+create table t1 select * from t1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
+create table t1 (primary key(a)) select "b" as b;
+ERROR 42000: Key column 'a' doesn't exist in table
+create table t1 (a int);
+create table if not exists t1 select 1 as a, 2 as b;
+ERROR 21S01: Column count doesn't match value count at row 1
+drop table t1;
+create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
+ERROR 23000: Duplicate entry '1' for key 1
+create table t1 (i int);
+create table t1 select 1 as i;
+ERROR 42S01: Table 't1' already exists
+create table if not exists t1 select 1 as i;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+1
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
+select * from t1;
+i
+1
+alter table t1 add primary key (i);
+create table if not exists t1 (select 2 as i) union all (select 2 as i);
+ERROR 23000: Duplicate entry '2' for key 1
+select * from t1;
+i
+1
+2
+drop table t1;
+create temporary table t1 (j int);
+create table if not exists t1 select 1;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+j
+1
+drop temporary table t1;
+select * from t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+drop table t1;
+ERROR 42S02: Unknown table 't1'
+create table t1 (i int);
+insert into t1 values (1), (2);
+lock tables t1 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
+create table t2 (j int);
+lock tables t1 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
+lock table t1 read, t2 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+unlock tables;
+lock table t1 read, t2 write;
+create table t2 select * from t1;
+ERROR 42S01: Table 't2' already exists
+create table if not exists t2 select * from t1;
+Warnings:
+Note	1050	Table 't2' already exists
+select * from t1;
+i
+1
+2
+unlock tables;
+drop table t2;
+lock tables t1 read;
+create temporary table t2 select * from t1;
+create temporary table if not exists t2 select * from t1;
+Warnings:
+Note	1050	Table 't2' already exists
+select * from t2;
+i
+1
+2
+1
+2
+unlock tables;
+drop table t1, t2;
 create table t1 (upgrade int);
 drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 71419b5b2c3..20df776ec1b 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -737,4 +737,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
 GROUP_CONCAT(DISTINCT UCASE(b))
 ONE.1,TWO.2,ONE.3
 DROP TABLE t1;
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
+( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+GROUP_CONCAT( a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+GROUP_CONCAT( DISTINCT a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+GROUP_CONCAT( a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+GROUP_CONCAT( DISTINCT a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index f245d272ede..e5720cc1ee0 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1330,4 +1330,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
 a	average
 1	32768.5000
 DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a)	MIN(b)
+NULL	1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	a	a	5	const	2	Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b)	MIN(c)
+3	2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b)	min(c)
+NULL	2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a)	MIN(b)
+1	1
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+MIN(a)	MIN(b)
+1	2
+DROP TABLE t1, t2, t3, t4, t5;
 End of 5.0 tests
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 6d014fbb71b..a4c51cca277 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1105,4 +1105,23 @@ ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table '
 DROP DATABASE mysqltest1;
 DROP DATABASE mysqltest2;
 DROP USER mysqltest_1@localhost;
+use test;
+CREATE TABLE t1 (f1 int, f2 int);
+INSERT INTO t1 VALUES(1,1), (2,2);
+CREATE DATABASE db27878;
+GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
+GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
+GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
+use db27878;
+CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
+use db27878;
+UPDATE v1 SET f2 = 4;
+ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+SELECT * FROM test.t1;
+f1	f2
+1	1
+2	2
+DROP DATABASE db27878;
+use test;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 009ae8776c3..45cb116f08b 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -144,6 +144,27 @@ SELECT * FROM t1;
 c1	cnt
 1a	2
 DROP TABLE t1;
+CREATE TABLE t1 (
+a1 decimal(10,0) DEFAULT NULL,
+a2 blob,
+a3 time DEFAULT NULL,
+a4 blob,
+a5 char(175) DEFAULT NULL,
+a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+a7 tinyblob,
+INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
 End of 4.1 tests
 create table t1m (a int) engine=myisam;
 create table t1i (a int) engine=innodb;
@@ -544,4 +565,56 @@ id	c	counter
 3	b	2
 4	a	2
 drop table t1;
+CREATE TABLE t1(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+CREATE TABLE t2(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+INSERT INTO t1(stat_id,acct_id) VALUES
+(1,759), (2,831), (3,785), (4,854), (1,921),
+(1,553), (2,589), (3,743), (2,827), (2,545),
+(4,779), (4,783), (1,597), (1,785), (4,832),
+(1,741), (1,833), (3,788), (2,973), (1,907);
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785 
+WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40960
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+COUNT(*)
+8702
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx1	9	NULL	2	Using where; Using index
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	optimize	status	OK
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx1,idx2	idx1	9	NULL	2	Using where; Using index
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index 7900e0b7695..fa6e23d09f9 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -346,3 +346,119 @@ f1	f2
 12	NULL
 drop view v1;
 drop table t1,t2;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+CREATE TABLE t1 (i INT);
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (1);
+RETURN 1;
+END |
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+INSERT DELAYED INTO t1 VALUES (2);
+RETURN 1;
+END |
+SELECT f1();
+f1()
+1
+SELECT f2();
+f2()
+1
+INSERT INTO t1 VALUES (3);
+INSERT DELAYED INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT DELAYED INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT DELAYED INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT INTO t1 VALUES (NEW.i);
+INSERT INTO t1 VALUES (1);
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT DELAYED INTO t1 VALUES (1);
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+SELECT * FROM t1;
+i
+1
+2
+3
+4
+DROP FUNCTION f2;
+DROP FUNCTION f1;
+DROP TABLE t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT INTO t1 VALUES (1);
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT * FROM t1;
+i
+1
+2
+UPDATE t1 SET i = 3 WHERE i = 1;
+SELECT * FROM t1;
+i
+3
+2
+DELETE FROM t1 WHERE i = 3;
+SELECT * FROM t1;
+i
+2
+SELECT * FROM t2;
+i
+1
+2
+3
+3
+DROP TABLE t1, t2;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+SET @a= NEW.i;
+SET @a= 0;
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT @a;
+@a
+1
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT @a;
+@a
+2
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+INSERT INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT * FROM t1;
+i
+1
+UPDATE t1 SET i = 2 WHERE i = 1;
+SELECT * FROM t1;
+i
+2
+DELETE FROM t1 WHERE i = 2;
+SELECT * FROM t1;
+i
+SELECT * FROM t2;
+i
+1
+2
+2
+DROP TABLE t1, t2;
+End of 5.0 tests.
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 4a3e87d9d48..375961292a3 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -358,3 +358,38 @@ id	c1	cnt
 5	Y	1
 6	Z	1
 DROP TABLE t1;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 INT NOT NULL,
+cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+id	c1	cnt
+1	10	1
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL;
+ERROR 23000: Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	10	1
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error	1048	Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	0	2
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error	1048	Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	0	3
+2	2	1
+DROP TABLE t1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 20bff6bda1c..8a10a52ee65 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -26,11 +26,11 @@ ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEA
 execute stmt1;
 ERROR HY000: Incorrect arguments to EXECUTE
 prepare stmt2 from 'prepare nested_stmt from "select 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 '"select 1"' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'execute stmt1';
-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 'stmt1' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'deallocate prepare z';
-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 'z' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from 'insert into t1 values (?,?)';
 set @arg1=5, @arg2='five';
 execute stmt3 using @arg1, @arg2;
@@ -1114,6 +1114,28 @@ execute stmt;
 show create table t1;
 drop table t1;
 deallocate prepare stmt;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+PREPARE st1 FROM
+'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
 End of 4.1 tests.
 create table t1 (a varchar(20));
 insert into t1 values ('foo');
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index ac8ae6def9f..df4ec793325 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -391,11 +391,11 @@ drop table t5 ;
 deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 prepare stmt1 from ' prepare stmt2 from '' select 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 '' select 1 '' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' execute stmt2 ' ;
-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 'stmt2' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
-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 'never_prepared' at line 1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt4 from ' use test ' ;
 ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from ' create database mysqltest ';
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 151ddd95f84..79471ee5c02 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1315,3 +1315,97 @@ insert into t1(c1) select c1 from v1;
 drop table t1, t2, t3;
 drop view v1;
 set global query_cache_size=0;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+reset query cache;
+set LOCAL default_week_format = 0;
+select week('2007-01-04');
+week('2007-01-04')
+0
+select week('2007-01-04') from t1;
+week('2007-01-04')
+0
+0
+0
+select extract(WEEK FROM '2007-01-04') from t1;
+extract(WEEK FROM '2007-01-04')
+0
+0
+0
+set LOCAL default_week_format = 2;
+select week('2007-01-04');
+week('2007-01-04')
+53
+select week('2007-01-04') from t1;
+week('2007-01-04')
+53
+53
+53
+select extract(WEEK FROM '2007-01-04') from t1;
+extract(WEEK FROM '2007-01-04')
+53
+53
+53
+reset query cache;
+set LOCAL div_precision_increment=2;
+select 1/7;
+1/7
+0.14
+select 1/7 from t1;
+1/7
+0.14
+0.14
+0.14
+set LOCAL div_precision_increment=4;
+select 1/7;
+1/7
+0.1429
+select 1/7 from t1;
+1/7
+0.1429
+0.1429
+0.1429
+drop table t1;
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
+('Full-text indexes', 'are called collections'),
+('Only MyISAM tables','support collections'),
+('Function MATCH ... AGAINST()','is used to do a search'),
+('Full-text search in MySQL', 'implements vector space model');
+set GLOBAL ft_boolean_syntax='+ -><()~*:""&|';
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+a	b	x
+MySQL has now support	for full-text search	0
+Full-text indexes	are called collections	1
+Only MyISAM tables	support collections	0
+Function MATCH ... AGAINST()	is used to do a search	0
+Full-text search in MySQL	implements vector space model	0
+set GLOBAL ft_boolean_syntax='- +><()~*:""&|';
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+a	b	x
+MySQL has now support	for full-text search	0
+Full-text indexes	are called collections	0
+Only MyISAM tables	support collections	0
+Function MATCH ... AGAINST()	is used to do a search	0
+Full-text search in MySQL	implements vector space model	0
+create function change_global() returns integer
+begin
+set global ft_boolean_syntax='+ -><()~*:""&|';
+return 1;
+end|
+select *, change_global() from t1;
+a	b	change_global()
+MySQL has now support	for full-text search	1
+Full-text indexes	are called collections	1
+Only MyISAM tables	support collections	1
+Function MATCH ... AGAINST()	is used to do a search	1
+Full-text search in MySQL	implements vector space model	1
+drop function change_global;
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_min_res_unit=default;
+set GLOBAL query_cache_size= default;
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 9d86a6bc08d..219f3c9b37a 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -620,4 +620,117 @@ SHOW PROCEDURE CODE p1;
 Pos	Instruction
 0	stmt 2 "CREATE INDEX idx ON t1 (c1)"
 DROP PROCEDURE p1;
+drop table if exists t1;
+drop procedure if exists proc_26977_broken;
+drop procedure if exists proc_26977_works;
+create table t1(a int unique);
+create procedure proc_26977_broken(v int)
+begin
+declare i int default 5;
+declare continue handler for sqlexception
+begin
+select 'caught something';
+retry:
+while i > 0 do
+begin
+set i = i - 1;
+select 'looping', i;
+end;
+end while retry;
+end;
+select 'do something';
+insert into t1 values (v);
+select 'do something again';
+insert into t1 values (v);
+end//
+create procedure proc_26977_works(v int)
+begin
+declare i int default 5;
+declare continue handler for sqlexception
+begin
+select 'caught something';
+retry:
+while i > 0 do
+begin
+set i = i - 1;
+select 'looping', i;
+end;
+end while retry;
+select 'optimizer: keep hreturn';
+end;
+select 'do something';
+insert into t1 values (v);
+select 'do something again';
+insert into t1 values (v);
+end//
+show procedure code proc_26977_broken;
+Pos	Instruction
+0	set i@1 5
+1	hpush_jump 8 2 CONTINUE
+2	stmt 0 "select 'caught something'"
+3	jump_if_not 7(7) (i@1 > 0)
+4	set i@1 (i@1 - 1)
+5	stmt 0 "select 'looping', i"
+6	jump 3
+7	hreturn 2
+8	stmt 0 "select 'do something'"
+9	stmt 5 "insert into t1 values (v)"
+10	stmt 0 "select 'do something again'"
+11	stmt 5 "insert into t1 values (v)"
+12	hpop 1
+show procedure code proc_26977_works;
+Pos	Instruction
+0	set i@1 5
+1	hpush_jump 9 2 CONTINUE
+2	stmt 0 "select 'caught something'"
+3	jump_if_not 7(7) (i@1 > 0)
+4	set i@1 (i@1 - 1)
+5	stmt 0 "select 'looping', i"
+6	jump 3
+7	stmt 0 "select 'optimizer: keep hreturn'"
+8	hreturn 2
+9	stmt 0 "select 'do something'"
+10	stmt 5 "insert into t1 values (v)"
+11	stmt 0 "select 'do something again'"
+12	stmt 5 "insert into t1 values (v)"
+13	hpop 1
+call proc_26977_broken(1);
+do something
+do something
+do something again
+do something again
+caught something
+caught something
+looping	i
+looping	4
+looping	i
+looping	3
+looping	i
+looping	2
+looping	i
+looping	1
+looping	i
+looping	0
+call proc_26977_works(2);
+do something
+do something
+do something again
+do something again
+caught something
+caught something
+looping	i
+looping	4
+looping	i
+looping	3
+looping	i
+looping	2
+looping	i
+looping	1
+looping	i
+looping	0
+optimizer: keep hreturn
+optimizer: keep hreturn
+drop table t1;
+drop procedure proc_26977_broken;
+drop procedure proc_26977_works;
 End of 5.0 tests.
diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result
index b112c6bece6..f3eb40b3d70 100644
--- a/mysql-test/r/sp-vars.result
+++ b/mysql-test/r/sp-vars.result
@@ -1161,3 +1161,44 @@ CALL p1();
 v_text
 abc|def
 DROP PROCEDURE p1;
+DROP PROCEDURE IF EXISTS bug27415_text_test|
+DROP PROCEDURE IF EXISTS bug27415_text_test2|
+CREATE PROCEDURE bug27415_text_test(entity_id_str_in text)
+BEGIN
+DECLARE str_remainder text;
+SET str_remainder = entity_id_str_in;
+select 'before substr', str_remainder;
+SET str_remainder = SUBSTRING(str_remainder, 3);
+select 'after substr', str_remainder;
+END|
+CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text)
+BEGIN
+DECLARE str_remainder text;
+DECLARE str_remainder2 text;
+SET str_remainder2 = entity_id_str_in;
+select 'before substr', str_remainder2;
+SET str_remainder = SUBSTRING(str_remainder2, 3);
+select 'after substr', str_remainder;
+END|
+CALL bug27415_text_test('a,b,c')|
+before substr	str_remainder
+before substr	a,b,c
+after substr	str_remainder
+after substr	b,c
+CALL bug27415_text_test('a,b,c')|
+before substr	str_remainder
+before substr	a,b,c
+after substr	str_remainder
+after substr	b,c
+CALL bug27415_text_test2('a,b,c')|
+before substr	str_remainder2
+before substr	a,b,c
+after substr	str_remainder
+after substr	b,c
+CALL bug27415_text_test('a,b,c')|
+before substr	str_remainder
+before substr	a,b,c
+after substr	str_remainder
+after substr	b,c
+DROP PROCEDURE bug27415_text_test|
+DROP PROCEDURE bug27415_text_test2|
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 9ba6a356db2..b5b79af031e 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6118,6 +6118,13 @@ Warning	1265	Data truncated for column 'bug5274_f1' at row 1
 Warning	1265	Data truncated for column 'bug5274_f1' at row 1
 DROP FUNCTION bug5274_f1|
 DROP FUNCTION bug5274_f2|
+drop procedure if exists proc_21513|
+create procedure proc_21513()`my_label`:BEGIN END|
+show create procedure proc_21513|
+Procedure	sql_mode	Create Procedure
+proc_21513		CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`()
+`my_label`:BEGIN END
+drop procedure proc_21513|
 End of 5.0 tests.
 drop table t1,t2;
 CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result
index f09645703ba..c74909e7e8f 100644
--- a/mysql-test/r/sp_trans.result
+++ b/mysql-test/r/sp_trans.result
@@ -530,8 +530,6 @@ count(*)
 drop table t3, t4|
 drop procedure bug14210|
 set @@session.max_heap_table_size=default|
-drop function if exists bug23333|
-drop table if exists t1,t2|
 CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
 CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
 insert into t2 values (1,1)|
@@ -551,3 +549,4 @@ Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 select count(*),@a from t1 /* must be 1,1 */|
 count(*)	@a
 1	1
+drop table t1, t2|
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index eecdc545be7..74bd2a171e3 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -3,7 +3,7 @@ set @@sql_mode='ansi,traditional';
 select @@sql_mode;
 @@sql_mode
 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1, t2;
 CREATE TABLE t1 (col1 date);
 INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
 INSERT INTO t1 VALUES('0000-10-31');
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 2e82d948edb..92a4a6f3f5a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4041,4 +4041,34 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+3	FL
+1	GA
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+1	GA
+3	FL
+7	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id	st
+2	GA
+4	FL
+SELECT id, st FROM t1 
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id	st
+2	GA
+4	FL
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index f52249db8a1..9bbfdc6c5f9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -711,3 +711,34 @@ a
 1
 4
 DROP TABLE t1,t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Using index; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Full scan on NULL key
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id
+2
+NULL
+3
+1
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3 
+WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+FROM t1;
+x
+0
+0
+0
+0
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 0a0be41927a..3e6a901dc00 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1414,4 +1414,39 @@ id	val
 DROP TRIGGER trg27006_a_insert;
 DROP TRIGGER trg27006_a_update;
 drop table t1,t2;
+drop table if exists t1, t2, t3;
+create table t1 (i int);
+create trigger t1_bi before insert on t1 for each row set new.i = 7;
+create trigger t1_ai after insert on t1 for each row set @a := 7;
+create table t2 (j int);
+insert into t2 values (1), (2);
+set @a:="";
+create table if not exists t1 select * from t2;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+7
+7
+select @a;
+@a
+7
+drop trigger t1_bi;
+drop trigger t1_ai;
+create table t3 (isave int);
+create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
+create table if not exists t1 select * from t2;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+7
+7
+1
+2
+select * from t3;
+isave
+1
+2
+drop table t1, t2, t3;
 End of 5.0 tests
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 8e671597bca..48cc54fb3ef 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -328,8 +328,8 @@ least(cast('01-01-01' as datetime), '01-01-02') + 0
 select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
 cast(least(cast('01-01-01' as datetime), '01-01-02') as signed)
 20010101000000
-select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal);
-cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal)
+select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2));
+cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2))
 20010101000000.00
 DROP PROCEDURE IF EXISTS test27759 ;
 CREATE PROCEDURE test27759()
@@ -346,3 +346,33 @@ call test27759();
 a	b	a_then_b	b_then_a	c_then_a
 2007-04-10	2007-04-11	2007-04-10	2007-04-10	2004-04-09 00:00:00
 drop procedure test27759;
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+left(f1,10) = curdate()
+1
+drop table t1;
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+Warnings:
+Warning	1292	Incorrect date value: '' for column 'f1' at row 1
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+drop table t1;
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index c103de81bd7..cbcab126439 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1430,4 +1430,39 @@ select * from t1;
 a
 123456789012345678
 drop table t1;
+select cast(11.1234 as DECIMAL(3,2));
+cast(11.1234 as DECIMAL(3,2))
+9.99
+Warnings:
+Error	1264	Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
+select * from (select cast(11.1234 as DECIMAL(3,2))) t;
+cast(11.1234 as DECIMAL(3,2))
+9.99
+Warnings:
+Error	1264	Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
+select cast(a as DECIMAL(3,2))
+from (select 11.1233 as a
+UNION select 11.1234
+UNION select 12.1234
+) t;
+cast(a as DECIMAL(3,2))
+9.99
+9.99
+9.99
+Warnings:
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+select cast(a as DECIMAL(3,2)), count(*)
+from (select 11.1233 as a
+UNION select 11.1234
+UNION select 12.1234
+) t group by 1;
+cast(a as DECIMAL(3,2))	count(*)
+9.99	3
+Warnings:
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error	1264	Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
 End of 5.0 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 70dd6b2550f..8d9d802949d 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1789,7 +1789,7 @@ drop table t1;
 create view v1 as select cast(1 as decimal);
 select * from v1;
 cast(1 as decimal)
-1.00
+1
 drop view v1;
 create table t1(f1 int);
 create table t2(f2 int);
@@ -3354,4 +3354,17 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;
+SELECT * FROM v1;
+col
+1.23457
+DESCRIBE v1;
+Field	Type	Null	Key	Default	Extra
+col	decimal(7,5)	NO		0.00000	
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col`
+DROP VIEW v1;
 End of 5.0 tests.
diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test
index 6c1229db83f..9a5fb11229d 100644
--- a/mysql-test/t/bigint.test
+++ b/mysql-test/t/bigint.test
@@ -288,3 +288,9 @@ insert into t1 values (10000002383263201056);
 select c1 mod 50 as result from t1;
 drop table t1;
 
+#
+# Bug #8663 cant use bgint unsigned as input to cast
+#
+
+select cast(19999999999999999999 as signed);
+select cast(-19999999999999999999 as signed);
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index ba5f56e12b1..35198c793b8 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -669,6 +669,117 @@ alter table t1 max_rows=100000000000;
 show create table t1;
 drop table t1;
 
+
+#
+# Tests for errors happening at various stages of CREATE TABLES ... SELECT
+#
+# (Also checks that it behaves atomically in the sense that in case
+#  of error it is automatically dropped if it has not existed before.)
+#
+# Error during open_and_lock_tables() of tables
+--error ER_NO_SUCH_TABLE
+create table t1 select * from t2;
+# Rather special error which also caught during open tables pahse
+--error ER_UPDATE_TABLE_USED
+create table t1 select * from t1;
+# Error which happens before select_create::prepare()
+--error ER_CANT_AGGREGATE_2COLLATIONS
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+# Error during table creation
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+create table t1 (primary key(a)) select "b" as b;
+# Error in select_create::prepare() which is not related to table creation
+create table t1 (a int);
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+create table if not exists t1 select 1 as a, 2 as b;
+drop table t1;
+# Finally error which happens during insert
+--error ER_DUP_ENTRY
+create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
+# What happens if table already exists ?
+create table t1 (i int);
+--error ER_TABLE_EXISTS_ERROR
+create table t1 select 1 as i;
+create table if not exists t1 select 1 as i;
+select * from t1;
+# Error before select_create::prepare()
+--error ER_CANT_AGGREGATE_2COLLATIONS
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+select * from t1;
+# Error which happens during insertion of rows
+alter table t1 add primary key (i);
+--error ER_DUP_ENTRY
+create table if not exists t1 (select 2 as i) union all (select 2 as i);
+select * from t1;
+drop table t1;
+
+
+# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
+# results of CREATE TABLE ... SELECT when temporary table exists").
+# In this situation we either have to create non-temporary table and
+# insert data in it or insert data in temporary table without creation
+# of permanent table. Since currently temporary tables always shadow
+# permanent tables we adopt second approach.
+create temporary table t1 (j int);
+create table if not exists t1 select 1;
+select * from t1;
+drop temporary table t1;
+--error ER_NO_SUCH_TABLE
+select * from t1;
+--error ER_BAD_TABLE_ERROR
+drop table t1;
+
+
+#
+# CREATE TABLE ... SELECT and LOCK TABLES
+#
+# There is little sense in using CREATE TABLE ... SELECT under
+# LOCK TABLES as it mostly does not work. At least we check that
+# the server doesn't crash, hang and produces sensible errors.
+# Includes test for bug #20662 "Infinite loop in CREATE TABLE
+# IF NOT EXISTS ... SELECT with locked tables".
+create table t1 (i int);
+insert into t1 values (1), (2);
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED
+create table t2 select * from t1;
+--error ER_TABLE_NOT_LOCKED
+create table if not exists t2 select * from t1;
+unlock tables;
+create table t2 (j int);
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED
+create table t2 select * from t1;
+# This should not be ever allowed as it will undermine
+# lock-all-at-once approach
+--error ER_TABLE_NOT_LOCKED
+create table if not exists t2 select * from t1;
+unlock tables;
+lock table t1 read, t2 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create table t2 select * from t1;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create table if not exists t2 select * from t1;
+unlock tables;
+lock table t1 read, t2 write;
+--error ER_TABLE_EXISTS_ERROR
+create table t2 select * from t1;
+# This is the only case which really works.
+create table if not exists t2 select * from t1;
+select * from t1;
+unlock tables;
+drop table t2;
+
+# OTOH CREATE TEMPORARY TABLE ... SELECT should work
+# well under LOCK TABLES.
+lock tables t1 read;
+create temporary table t2 select * from t1;
+create temporary table if not exists t2 select * from t1;
+select * from t2;
+unlock tables;
+drop table t1, t2;
+
+
 #
 # Bug#21772: can not name a column 'upgrade' when create a table
 #
diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
index df56165950f..85685234de9 100644
--- a/mysql-test/t/disabled.def
+++ b/mysql-test/t/disabled.def
@@ -12,3 +12,5 @@
 
 ndb_load        : Bug#17233
 user_limits     : Bug#23921 random failure of user_limits.test
+im_life_cycle        : Bug#27851: Instance manager test im_life_cycle fails randomly
+im_daemon_life_cycle : Bug#20294: Instance manager tests fail randomly
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 0dd82864520..7771f216f69 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
 SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
+#
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
+                      ( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;  
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;          
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; 
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 654bb8bb75d..2293ac71454 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -827,4 +827,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
 
 DROP TABLE t1;
 
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ 
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) ); 
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+
+DROP TABLE t1, t2, t3, t4, t5;
+
+###
 --echo End of 5.0 tests
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index 197f20db76e..aa43e4225c5 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -413,6 +413,7 @@ connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
 connection user1;
 -- error 1142
 alter table t1 rename t2;
+disconnect user1;
 connection root;
 revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
 delete from mysql.user where user=_binary'mysqltest_1';
@@ -1122,5 +1123,29 @@ DROP DATABASE mysqltest2;
 
 DROP USER mysqltest_1@localhost;
 
+#
+# Bug#27878: Unchecked privileges on a view referring to a table from another 
+#            database.
+#
+use test;
+CREATE TABLE t1 (f1 int, f2 int);
+INSERT INTO t1 VALUES(1,1), (2,2);
+CREATE DATABASE db27878;
+GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
+GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
+GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
+use db27878;
+CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+use db27878;
+--error 1356
+UPDATE v1 SET f2 = 4;
+SELECT * FROM test.t1;
+disconnect user1;
+connection default;
+DROP DATABASE db27878;
+use test;
+DROP TABLE t1;
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index c9e1de8c3ab..d9e50add8bf 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -169,6 +169,31 @@ INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
 SELECT * FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
+#
+
+CREATE TABLE t1 (
+  a1 decimal(10,0) DEFAULT NULL,
+  a2 blob,
+  a3 time DEFAULT NULL,
+  a4 blob,
+  a5 char(175) DEFAULT NULL,
+  a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  a7 tinyblob,
+  INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+DROP TABLE t1;
+
 --echo End of 4.1 tests
 #
 # Bug #12882  	min/max inconsistent on empty table
@@ -518,4 +543,58 @@ select * from t1;
 
 drop table t1;
 
+#
+# Bug #28189: optimizer erroniously prefers ref access to range access 
+#             for an InnoDB table
+#
+
+CREATE TABLE t1(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1(stat_id,acct_id) VALUES
+  (1,759), (2,831), (3,785), (4,854), (1,921),
+  (1,553), (2,589), (3,743), (2,827), (2,545),
+  (4,779), (4,783), (1,597), (1,785), (4,832),
+  (1,741), (1,833), (3,788), (2,973), (1,907);
+
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785 
+  WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 
+
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+
+DROP TABLE t1,t2; 
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 0a8e184ea5c..76177403bd0 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -216,3 +216,142 @@ select * from t1;
 drop view v1;
 drop table t1,t2;
 
+
+#
+# BUG#21483: Server abort or deadlock on INSERT DELAYED with another
+# implicit insert 
+#
+# The solution is to downgrade INSERT DELAYED to normal INSERT if the
+# statement uses functions and access tables or triggers, or is called
+# from a function or a trigger.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (1);
+  RETURN 1;
+END |
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+  INSERT DELAYED INTO t1 VALUES (2);
+  RETURN 1;
+END |
+delimiter ;|
+
+SELECT f1();
+SELECT f2();
+INSERT INTO t1 VALUES (3);
+INSERT DELAYED INTO t1 VALUES (4);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (f1());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (f1());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (f2());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (f2());
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  INSERT INTO t1 VALUES (NEW.i);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (1);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (1);
+
+SELECT * FROM t1;
+
+DROP FUNCTION f2;
+DROP FUNCTION f1;
+DROP TABLE t1;
+
+#
+# BUG#20497: Trigger with INSERT DELAYED causes Error 1165
+#
+# Fixed by the patch for Bug#21483
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (OLD.i);
+
+INSERT INTO t1 VALUES (1);
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT * FROM t1;
+UPDATE t1 SET i = 3 WHERE i = 1;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE i = 3;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+#
+# BUG#21714: Wrong NEW.value and server abort on INSERT DELAYED to a
+# table with a trigger 
+#
+# Fixed by the patch for Bug#21483
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  SET @a= NEW.i;
+
+SET @a= 0;
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT @a;
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT @a;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+  INSERT INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (OLD.i);
+
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT * FROM t1;
+UPDATE t1 SET i = 2 WHERE i = 1;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE i = 2;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+--echo End of 5.0 tests.
+
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index 0e199dab4bd..725fbdb25d7 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -264,3 +264,29 @@ INSERT  INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
   ON DUPLICATE KEY UPDATE cnt=cnt+1;
 SELECT * FROM t1;
 DROP TABLE t1;
+
+#
+# Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE
+# with erroneous UPDATE: NOT NULL field with NULL value.
+#
+CREATE TABLE t1 (
+  id INT AUTO_INCREMENT PRIMARY KEY,
+  c1 INT NOT NULL,
+  cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+--error 1048
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+  ON DUPLICATE KEY UPDATE c1=NULL;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 3fbcf84a1f9..5b7adc40755 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -33,13 +33,13 @@ deallocate prepare no_such_statement;
 execute stmt1;
 
 # Nesting ps commands is not allowed: 
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt2 from 'prepare nested_stmt from "select 1"';
 
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt2 from 'execute stmt1';
 
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt2 from 'deallocate prepare z';
 
 # PS insert 
@@ -1194,6 +1194,24 @@ show create table t1;
 --enable_warnings
 drop table t1;
 deallocate prepare stmt;
+#
+
+#
+# Bug #27937: crash on the second execution for prepared statement 
+#             from UNION with ORDER BY an expression containing RAND()
+#
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+
+PREPARE st1 FROM
+  '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+
+EXECUTE st1;
+EXECUTE st1;
+
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
 
 --echo End of 4.1 tests.
 
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index d4e6a62c09e..2e7fea2ff3d 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -416,11 +416,11 @@ deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 
 ## nonsense like prepare of prepare,execute or deallocate
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' execute stmt2 ' ;
---error 1064
+--error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
 
 ## switch the database connection
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 427334805ce..1ef104f820b 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -899,3 +899,75 @@ insert into t1(c1) select c1 from v1;
 drop table t1, t2, t3;
 drop view v1;
 set global query_cache_size=0;
+
+#
+# Query cache and changes to system variables
+#
+
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+
+# default_week_format
+reset query cache;
+set LOCAL default_week_format = 0;
+select week('2007-01-04');
+select week('2007-01-04') from t1;
+select extract(WEEK FROM '2007-01-04') from t1;
+
+set LOCAL default_week_format = 2;
+select week('2007-01-04');
+select week('2007-01-04') from t1;
+select extract(WEEK FROM '2007-01-04') from t1;
+
+# div_precision_increment
+reset query cache;
+set LOCAL div_precision_increment=2;
+select 1/7;
+select 1/7 from t1;
+
+set LOCAL div_precision_increment=4;
+select 1/7;
+select 1/7 from t1;
+
+drop table t1;
+
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
+                       ('Full-text indexes', 'are called collections'),
+                          ('Only MyISAM tables','support collections'),
+             ('Function MATCH ... AGAINST()','is used to do a search'),
+        ('Full-text search in MySQL', 'implements vector space model');
+
+
+set GLOBAL ft_boolean_syntax='+ -><()~*:""&|';
+
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+
+# swap +/-
+set GLOBAL ft_boolean_syntax='- +><()~*:""&|';
+
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+
+# If in the future we need to cache queries with functions
+# be sure not to cause dead lock if the query cache is flushed
+# while inserting a query in the query cache.
+delimiter |;
+create function change_global() returns integer
+begin
+  set global ft_boolean_syntax='+ -><()~*:""&|';
+  return 1;
+end|
+delimiter ;|
+select *, change_global() from t1;
+drop function change_global;
+
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_min_res_unit=default;
+set GLOBAL query_cache_size= default;
+
+# End of 5.0 tests
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 97bc29fcad2..0f249c95172 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -446,4 +446,79 @@ SHOW PROCEDURE CODE p1;
 DROP PROCEDURE p1;
 
 
+#
+# Bug#26977 exception handlers never hreturn
+#
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists proc_26977_broken;
+drop procedure if exists proc_26977_works;
+--enable_warnings
+
+create table t1(a int unique);
+
+delimiter //;
+
+create procedure proc_26977_broken(v int)
+begin
+  declare i int default 5;
+
+  declare continue handler for sqlexception
+  begin
+    select 'caught something';
+    retry:
+    while i > 0 do
+      begin
+        set i = i - 1;
+        select 'looping', i;
+      end;
+    end while retry;
+  end;
+
+  select 'do something';
+  insert into t1 values (v);
+  select 'do something again';
+  insert into t1 values (v);
+end//
+
+create procedure proc_26977_works(v int)
+begin
+  declare i int default 5;
+
+  declare continue handler for sqlexception
+  begin
+    select 'caught something';
+    retry:
+    while i > 0 do
+      begin
+        set i = i - 1;
+        select 'looping', i;
+      end;
+    end while retry;
+    select 'optimizer: keep hreturn';
+  end;
+
+  select 'do something';
+  insert into t1 values (v);
+  select 'do something again';
+  insert into t1 values (v);
+end//
+delimiter ;//
+
+show procedure code proc_26977_broken;
+
+show procedure code proc_26977_works;
+
+## This caust an error because of jump short cut
+## optimization.
+call proc_26977_broken(1);
+
+## This works
+call proc_26977_works(2);
+
+drop table t1;
+drop procedure proc_26977_broken;
+drop procedure proc_26977_works;
+
+
 --echo End of 5.0 tests.
diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test
index 0014dc1f6af..2cc68b054d5 100644
--- a/mysql-test/t/sp-vars.test
+++ b/mysql-test/t/sp-vars.test
@@ -1367,4 +1367,48 @@ CALL p1();
 
 DROP PROCEDURE p1;
 
+#
+# Bug #27415 Text Variables in stored procedures
+# If the SP varible was also referenced on the right side
+# the result was corrupted.
+#
+DELIMITER |;
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug27415_text_test|
+DROP PROCEDURE IF EXISTS bug27415_text_test2|
+--enable_warnings
+
+CREATE PROCEDURE bug27415_text_test(entity_id_str_in text)
+BEGIN
+    DECLARE str_remainder text;
+
+    SET str_remainder = entity_id_str_in;
+
+    select 'before substr', str_remainder;
+    SET str_remainder = SUBSTRING(str_remainder, 3);
+    select 'after substr', str_remainder;
+END|
+
+CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text)
+BEGIN
+    DECLARE str_remainder text;
+    DECLARE str_remainder2 text;
+ 
+    SET str_remainder2 = entity_id_str_in;
+    select 'before substr', str_remainder2;
+    SET str_remainder = SUBSTRING(str_remainder2, 3);
+    select 'after substr', str_remainder;
+END|
+
+CALL bug27415_text_test('a,b,c')|
+CALL bug27415_text_test('a,b,c')|
+CALL bug27415_text_test2('a,b,c')|
+CALL bug27415_text_test('a,b,c')|
+
+DROP PROCEDURE bug27415_text_test|
+DROP PROCEDURE bug27415_text_test2|
+
+DELIMITER ;|
+
 # End of 5.0 tests.
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index c94a526e10c..ff203a85ef7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -7054,6 +7054,17 @@ SELECT bug5274_f2()|
 DROP FUNCTION bug5274_f1|
 DROP FUNCTION bug5274_f2|
 
+#
+# Bug#21513 (SP having body starting with quoted label rendered unusable)
+#
+--disable_warnings
+drop procedure if exists proc_21513|
+--enable_warnings
+
+create procedure proc_21513()`my_label`:BEGIN END|
+show create procedure proc_21513|
+
+drop procedure proc_21513|
 
 ###
 --echo End of 5.0 tests.
diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test
index d9b34c303ae..8eccaafcf0e 100644
--- a/mysql-test/t/sp_trans.test
+++ b/mysql-test/t/sp_trans.test
@@ -559,12 +559,8 @@ set @@session.max_heap_table_size=default|
 #            breaks stmt-based binlog
 # Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
 #
---disable_warnings
-drop function if exists bug23333|
-drop table if exists t1,t2|
---enable_warnings
- CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
- CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
+CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
+CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
 
 insert into t2 values (1,1)|
 
@@ -583,6 +579,7 @@ insert into t2 values (bug23333(),1)|
 --replace_column 2 # 5 # 6 #
 show binlog events from 98 /* with fixes for #23333 will show there is the query */|
 select count(*),@a from t1 /* must be 1,1 */|
+drop table t1, t2|
 
 #
 # BUG#NNNN: New bug synopsis
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index faca9e7b080..fc663853174 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -7,7 +7,7 @@ set @@sql_mode='ansi,traditional';
 select @@sql_mode;
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1, t2;
 --enable_warnings
 
 # Test INSERT with DATE
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 64de2ada93c..5840e434b64 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2882,4 +2882,28 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 DROP TABLE t1;
 
+#
+# Bug #28377: grouping query with a correlated subquery in WHERE condition
+#  
+
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1 
+  WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+    GROUP BY id;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index dfe09968fa2..65556012588 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -546,3 +546,28 @@ SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
 
 DROP TABLE t1,t2;
+
+#
+# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL  
+#
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+
+EXPLAIN
+SELECT * FROM t1
+  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
+                        WHERE t3.name='xxx' AND t2.id=t3.id);
+SELECT * FROM t1
+  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
+                        WHERE t3.name='xxx' AND t2.id=t3.id);
+
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3 
+                    WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+  FROM t1;
+
+DROP TABLE t1,t2,t3;   
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index a01efba11db..82de4dac111 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1737,4 +1737,30 @@ DROP TRIGGER trg27006_a_insert;
 DROP TRIGGER trg27006_a_update;
 drop table t1,t2;
 
+#
+# Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
+#
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+create table t1 (i int);
+create trigger t1_bi before insert on t1 for each row set new.i = 7;
+create trigger t1_ai after insert on t1 for each row set @a := 7;
+create table t2 (j int);
+insert into t2 values (1), (2);
+set @a:="";
+create table if not exists t1 select * from t2;
+select * from t1;
+select @a;
+# Let us check that trigger that involves table also works ok.
+drop trigger t1_bi;
+drop trigger t1_ai;
+create table t3 (isave int);
+create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
+create table if not exists t1 select * from t2;
+select * from t1;
+select * from t3;
+drop table t1, t2, t3;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 5eb9d317a8c..cf512aa3649 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -207,7 +207,7 @@ select least(cast('01-01-01' as date), '01-01-02') + 0;
 select greatest(cast('01-01-01' as date), '01-01-02') + 0;
 select least(cast('01-01-01' as datetime), '01-01-02') + 0;
 select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
-select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal);
+select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2));
 --disable_warnings
 DROP PROCEDURE IF EXISTS test27759 ;
 --enable_warnings
@@ -225,3 +225,24 @@ END;|
 DELIMITER ;|
 call test27759();
 drop procedure test27759;
+
+#
+# Bug#28208: Wrong result of a non-const STRING function with a const
+#            DATETIME function.
+#
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+drop table t1;
+
+#
+# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
+#            is involved.
+#
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+drop table t1;
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index 4c6098d2121..a7906be79d4 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1130,4 +1130,23 @@ alter table t1 modify column a decimal(19);
 select * from t1;
 drop table t1;
 
+#
+# Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect 
+#
+
+select cast(11.1234 as DECIMAL(3,2));
+select * from (select cast(11.1234 as DECIMAL(3,2))) t;
+
+select cast(a as DECIMAL(3,2))
+ from (select 11.1233 as a
+  UNION select 11.1234
+  UNION select 12.1234
+ ) t;
+
+select cast(a as DECIMAL(3,2)), count(*)
+ from (select 11.1233 as a
+  UNION select 11.1234
+  UNION select 12.1234
+ ) t group by 1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index e5bf9de13eb..3275ba0a687 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3221,4 +3221,16 @@ EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
 DROP VIEW v1;
 DROP TABLE t1;
 
+#
+# Bug #27921 View ignores precision for CAST()
+#
+CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;
+SELECT * FROM v1;
+DESCRIBE v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
 --echo End of 5.0 tests.
diff --git a/scripts/make_win_bin_dist b/scripts/make_win_bin_dist
index 87d35a0850b..30127b0043f 100755
--- a/scripts/make_win_bin_dist
+++ b/scripts/make_win_bin_dist
@@ -39,7 +39,7 @@ The "package-base-name" argument should be something like
 
   mysql-noinstall-5.0.25-win32  (or winx64)
 
-and will be the name of the directory of the unpacked ZIP (stripping
+and will become the name of the directory of the unpacked ZIP (stripping
 away the "noinstall" part of the ZIP file name if any) and the base
 for the resulting package name.
 
@@ -51,6 +51,7 @@ Options are
   --no-embedded    Don't pack the embedded server even if built
 
   --debug          Pack the debug binaries and give error if not built.
+                   The default is to pack them if they are built.
 
   --no-debug       Don't pack the debug binaries even if built
 
@@ -58,10 +59,10 @@ Options are
                    want to replace the normal binaries with debug
                    versions, i.e. no separate "debug" directories.
 
-  --exe-suffix=SUF Add a suffix to the "mysqld" binary.
+  --exe-suffix=SUF Add a suffix to the filename part of the "mysqld" binary.
 
 As you might want to include files of directories from other builds
-(like a "mysqld-max.exe" server), you can instruct this script do copy
+(like a "mysqld-max.exe" server), you can instruct this script to copy
 them in for you. This is the "copy-def" arguments, and they are of the
 form
 
@@ -172,10 +173,10 @@ else
   BASENAME="mysqld"        # New style CMake build
 fi
 
-if [ x"$PACK_DEBUG" = "" -a -f "sql/debug/$BASENAME.exe" -o \
-     x"$PACK_DEBUG" = "yes" ] ; then
+if [ x"$PACK_DEBUG" = x"" -a -f "sql/debug/$BASENAME.exe" -o \
+     x"$PACK_DEBUG" = x"yes" ] ; then
   cp sql/debug/$BASENAME.exe   $DESTDIR/bin/mysqld-debug.exe
-  cp sql/debug/$BASENAME.pdb   $DESTDIR/bin/mysqld-debug.pdb
+  cp sql/debug/$BASENAME.pdb   $DESTDIR/bin/mysqld-debug.pdb || true
   cp sql/debug/$BASENAME.map   $DESTDIR/bin/mysqld-debug.map || true
 fi
 
@@ -221,8 +222,8 @@ copy_embedded()
   cp libmysqld/$TARGET/libmysqld.exp   $DESTDIR/Embedded/DLL/release/
   cp libmysqld/$TARGET/libmysqld.lib   $DESTDIR/Embedded/DLL/release/
 
-  if [ x"$PACK_DEBUG" = "" -a -f "libmysqld/debug/libmysqld.lib" -o \
-       x"$PACK_DEBUG" = "yes" ] ; then
+  if [ x"$PACK_DEBUG" = x"" -a -f "libmysqld/debug/libmysqld.lib" -o \
+       x"$PACK_DEBUG" = x"yes" ] ; then
     mkdir -p $DESTDIR/Embedded/DLL/debug
     cp libmysqld/debug/libmysqld.dll     $DESTDIR/Embedded/DLL/debug/
     cp libmysqld/debug/libmysqld.exp     $DESTDIR/Embedded/DLL/debug/
@@ -230,10 +231,10 @@ copy_embedded()
   fi
 }
 
-if [ x"$PACK_EMBEDDED" = "" -a \
+if [ x"$PACK_EMBEDDED" = x"" -a \
      -f "libmysqld/$TARGET/mysqlserver.lib" -a \
      -f "libmysqld/$TARGET/libmysqld.lib" -o \
-     x"$PACK_EMBEDDED" = "yes" ] ; then
+     x"$PACK_EMBEDDED" = x"yes" ] ; then
   copy_embedded
 fi
 
@@ -271,8 +272,8 @@ cp libmysql/$TARGET/libmysql.dll \
    strings/$TARGET/strings.lib \
    zlib/$TARGET/zlib.lib $DESTDIR/lib/opt/
 
-if [ x"$PACK_DEBUG" = "" -a -f "libmysql/debug/libmysql.lib" -o \
-     x"$PACK_DEBUG" = "yes" ] ; then
+if [ x"$PACK_DEBUG" = x"" -a -f "libmysql/debug/libmysql.lib" -o \
+     x"$PACK_DEBUG" = x"yes" ] ; then
   mkdir -p $DESTDIR/lib/debug
   cp libmysql/debug/libmysql.dll \
      libmysql/debug/libmysql.lib \
@@ -329,12 +330,11 @@ fi
 
 # ----------------------------------------------------------------------
 # Copy what could be usable in the "scripts" directory. Currently
-# only SQL files, others are bourne shell scripts or Perl scripts
+# only SQL files, others are Bourne shell scripts or Perl scripts
 # not really usable on Windows.
 #
 # But to be nice to the few Cygwin users we might have in 5.0 we
-# continue to copy the stuff, but don't include it include it in
-# the WiX install.
+# continue to copy the stuff, but don't include it in the WiX install.
 # ----------------------------------------------------------------------
 
 mkdir -p $DESTDIR/scripts
@@ -360,7 +360,7 @@ fi
 cp -pR sql-bench $DESTDIR/
 rm -f $DESTDIR/sql-bench/*.sh $DESTDIR/sql-bench/Makefile*
 
-# The SQL initiation code is really expected to be in "share"
+# The SQL initialisation code is really expected to be in "share"
 mv $DESTDIR/scripts/*.sql $DESTDIR/share/ || true
 
 # ----------------------------------------------------------------------
@@ -381,7 +381,7 @@ for arg do
 done
 
 # ----------------------------------------------------------------------
-# Finally creat the ZIP archive
+# Finally create the ZIP archive
 # ----------------------------------------------------------------------
 
 rm -f $NOINST_NAME.zip
diff --git a/sql/field.cc b/sql/field.cc
index b2def4ca8d2..d2e72371bc1 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -8426,8 +8426,7 @@ bool create_field::init(THD *thd, char *fld_name, enum_field_types fld_type,
   case FIELD_TYPE_NULL:
     break;
   case FIELD_TYPE_NEWDECIMAL:
-    if (!fld_length && !decimals)
-      length= 10;
+    my_decimal_trim(&length, &decimals);
     if (length > DECIMAL_MAX_PRECISION)
     {
       my_error(ER_TOO_BIG_PRECISION, MYF(0), length, fld_name,
diff --git a/sql/item.cc b/sql/item.cc
index 30486e7559a..651ab826484 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3503,7 +3503,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
           prev_subselect_item->const_item_cache= 0;
           set_field(*from_field);
           if (!last_checked_context->select_lex->having_fix_field &&
-              select->group_list.elements)
+              select->group_list.elements &&
+              (place == SELECT_LIST || place == IN_HAVING))
           {
             Item_outer_ref *rf;
             /*
@@ -4622,7 +4623,6 @@ inline uint char_val(char X)
 
 Item_hex_string::Item_hex_string(const char *str, uint str_length)
 {
-  name=(char*) str-2;				// Lex makes this start with 0x
   max_length=(str_length+1)/2;
   char *ptr=(char*) sql_alloc(max_length+1);
   if (!ptr)
@@ -4733,7 +4733,6 @@ Item_bin_string::Item_bin_string(const char *str, uint str_length)
   uchar bits= 0;
   uint power= 1;
 
-  name= (char*) str - 2;
   max_length= (str_length + 7) >> 3;
   char *ptr= (char*) sql_alloc(max_length + 1);
   if (!ptr)
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 4f0347dd506..3a79940b571 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -633,7 +633,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
 
   if (cmp_type != CMP_DATE_DFLT)
   {
-    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+    /*
+      Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+      for the current thread but it still may change during the execution.
+    */
+    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() &&
+        (str_arg->type() != Item::FUNC_ITEM ||
+        ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC))
     {
       THD *thd= current_thd;
       ulonglong value;
@@ -661,7 +667,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
                                         Item_result type)
 {
   enum enum_date_cmp_type cmp_type;
-  ulonglong const_value;
+  ulonglong const_value= (ulonglong)-1;
   a= a1;
   b= a2;
 
@@ -674,8 +680,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
     a_cache= 0;
     b_cache= 0;
 
-    if (cmp_type != CMP_DATE_WITH_DATE &&
-        ((*b)->const_item() || (*a)->const_item()))
+    if (const_value != (ulonglong)-1)
     {
       Item_cache_int *cache= new Item_cache_int();
       /* Mark the cache as non-const to prevent re-caching. */
@@ -781,7 +786,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
       MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
     value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
   }
-  if (item->const_item() && cache_arg)
+  /*
+    Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+    for the current thread but it still may change during the execution.
+  */
+  if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+      ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
   {
     Item_cache_int *cache= new Item_cache_int();
     /* Mark the cache as non-const to prevent re-caching. */
diff --git a/sql/item_create.cc b/sql/item_create.cc
index c1a81da0285..c4008d36aae 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -445,11 +445,13 @@ Item *create_load_file(Item* a)
 }
 
 
-Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec,
+Item *create_func_cast(Item *a, Cast_target cast_type,
+                       const char *c_len, const char *c_dec,
 		       CHARSET_INFO *cs)
 {
   Item *res;
-  int tmp_len;
+  ulong len;
+  uint dec;
   LINT_INIT(res);
 
   switch (cast_type) {
@@ -460,15 +462,18 @@ Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec,
   case ITEM_CAST_TIME:		res= new Item_time_typecast(a); break;
   case ITEM_CAST_DATETIME:	res= new Item_datetime_typecast(a); break;
   case ITEM_CAST_DECIMAL:
-    tmp_len= (len>0) ? len : 10;
-    if (tmp_len < dec)
+    len= c_len ? atoi(c_len) : 0;
+    dec= c_dec ? atoi(c_dec) : 0;
+    my_decimal_trim(&len, &dec);
+    if (len < dec)
     {
       my_error(ER_M_BIGGER_THAN_D, MYF(0), "");
       return 0;
     }
-    res= new Item_decimal_typecast(a, tmp_len, dec ? dec : 2);
+    res= new Item_decimal_typecast(a, len, dec);
     break;
   case ITEM_CAST_CHAR:
+    len= c_len ? atoi(c_len) : -1;
     res= new Item_char_typecast(a, len, cs ? cs : 
 				current_thd->variables.collation_connection);
     break;
@@ -476,6 +481,7 @@ Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec,
   return res;
 }
 
+
 Item *create_func_is_free_lock(Item* a)
 {
   current_thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
diff --git a/sql/item_create.h b/sql/item_create.h
index 2ff849263c6..46b209b3e49 100644
--- a/sql/item_create.h
+++ b/sql/item_create.h
@@ -27,7 +27,8 @@ Item *create_func_bit_length(Item* a);
 Item *create_func_coercibility(Item* a);
 Item *create_func_ceiling(Item* a);
 Item *create_func_char_length(Item* a);
-Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec,
+Item *create_func_cast(Item *a, Cast_target cast_type,
+                       const char *len, const char *dec,
                        CHARSET_INFO *cs);
 Item *create_func_connection_id(void);
 Item *create_func_conv(Item* a, Item *b, Item *c);
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 7a31b564502..1bd41cf87a4 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1050,18 +1050,61 @@ longlong Item_decimal_typecast::val_int()
 my_decimal *Item_decimal_typecast::val_decimal(my_decimal *dec)
 {
   my_decimal tmp_buf, *tmp= args[0]->val_decimal(&tmp_buf);
+  bool sign;
+  uint precision;
+
   if ((null_value= args[0]->null_value))
     return NULL;
   my_decimal_round(E_DEC_FATAL_ERROR, tmp, decimals, FALSE, dec);
+  sign= dec->sign();
+  if (unsigned_flag)
+  {
+    if (sign)
+    {
+      my_decimal_set_zero(dec);
+      goto err;
+    }
+  }
+  precision= my_decimal_length_to_precision(max_length,
+                                            decimals, unsigned_flag);
+  if (precision - decimals < (uint) my_decimal_intg(dec))
+  {
+    max_my_decimal(dec, precision, decimals);
+    dec->sign(sign);
+    goto err;
+  }
+  return dec;
+
+err:
+  push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_ERROR,
+                      ER_WARN_DATA_OUT_OF_RANGE,
+                      ER(ER_WARN_DATA_OUT_OF_RANGE),
+                      name, 1);
   return dec;
 }
 
 
 void Item_decimal_typecast::print(String *str)
 {
+  char len_buf[20*3 + 1];
+  char *end;
+
+  uint precision= my_decimal_length_to_precision(max_length, decimals,
+                                                 unsigned_flag);
   str->append(STRING_WITH_LEN("cast("));
   args[0]->print(str);
-  str->append(STRING_WITH_LEN(" as decimal)"));
+  str->append(STRING_WITH_LEN(" as decimal("));
+
+  end=int10_to_str(precision, len_buf,10);
+  str->append(len_buf, (uint32) (end - len_buf));
+
+  str->append(',');
+
+  end=int10_to_str(decimals, len_buf,10);
+  str->append(len_buf, (uint32) (end - len_buf));
+
+  str->append(')');
+  str->append(')');
 }
 
 
@@ -4407,7 +4450,7 @@ int get_var_with_binlog(THD *thd, enum_sql_command sql_command,
     List<set_var_base> tmp_var_list;
     LEX *sav_lex= thd->lex, lex_tmp;
     thd->lex= &lex_tmp;
-    lex_start(thd, NULL, 0);
+    lex_start(thd);
     tmp_var_list.push_back(new set_var_user(new Item_func_set_user_var(name,
                                                                        new Item_null())));
     /* Create the variable */
diff --git a/sql/item_func.h b/sql/item_func.h
index 0443e394585..7ec3c0b00c4 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -331,8 +331,8 @@ class Item_decimal_typecast :public Item_func
 public:
   Item_decimal_typecast(Item *a, int len, int dec) :Item_func(a)
   {
-    max_length= len + 2;
     decimals= dec;
+    max_length= my_decimal_precision_to_length(len, dec, unsigned_flag);
   }
   String *val_str(String *str);
   double val_real();
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index ccd361dba99..48b82e3cde6 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1829,6 +1829,8 @@ int subselect_single_select_engine::exec()
             if (cond_guard && !*cond_guard)
             {
               /* Change the access method to full table scan */
+              tab->save_read_first_record= tab->read_first_record;
+              tab->save_read_record= tab->read_record.read_record;
               tab->read_first_record= init_read_record_seq;
               tab->read_record.record= tab->table->record[0];
               tab->read_record.thd= join->thd;
@@ -1849,8 +1851,8 @@ int subselect_single_select_engine::exec()
       JOIN_TAB *tab= *ptab;
       tab->read_record.record= 0;
       tab->read_record.ref_length= 0;
-      tab->read_first_record= join_read_always_key_or_null;
-      tab->read_record.read_record= join_read_next_same_or_null;
+      tab->read_first_record= tab->save_read_first_record; 
+      tab->read_record.read_record= tab->save_read_record;
     }
     executed= 1;
     thd->where= save_where;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 4579ecd48ae..d6b31d43389 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -3339,6 +3339,10 @@ String* Item_func_group_concat::val_str(String* str)
   DBUG_ASSERT(fixed == 1);
   if (null_value)
     return 0;
+  if (!result.length() && tree)
+    /* Tree is used for sorting as in ORDER BY */
+    tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
+              left_root_right);
   if (count_cut_values && !warning)
   {
     /*
@@ -3350,11 +3354,6 @@ String* Item_func_group_concat::val_str(String* str)
                           ER_CUT_VALUE_GROUP_CONCAT,
                           ER(ER_CUT_VALUE_GROUP_CONCAT));
   }
-  if (result.length())
-    return &result;
-  if (tree)
-    tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
-              left_root_right);
   return &result;
 }
 
diff --git a/sql/lock.cc b/sql/lock.cc
index 233d12d9cc4..9298b33b4d2 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -853,7 +853,6 @@ int lock_table_name(THD *thd, TABLE_LIST *table_list)
   TABLE *table;
   char  key[MAX_DBKEY_LENGTH];
   char *db= table_list->db;
-  int table_in_key_offset;
   uint  key_length;
   HASH_SEARCH_STATE state;
   DBUG_ENTER("lock_table_name");
@@ -861,10 +860,8 @@ int lock_table_name(THD *thd, TABLE_LIST *table_list)
 
   safe_mutex_assert_owner(&LOCK_open);
 
-  table_in_key_offset= strmov(key, db) - key + 1;
-  key_length= (uint)(strmov(key + table_in_key_offset, table_list->table_name)
-                     - key) + 1;
-
+  key_length= (uint)(strmov(strmov(key, db) + 1, table_list->table_name) -
+                     key) + 1;
 
   /* Only insert the table if we haven't insert it already */
   for (table=(TABLE*) hash_first(&open_cache, (byte*)key, key_length, &state);
@@ -873,29 +870,11 @@ int lock_table_name(THD *thd, TABLE_LIST *table_list)
     if (table->in_use == thd)
       DBUG_RETURN(0);
 
-  /*
-    Create a table entry with the right key and with an old refresh version
-    Note that we must use my_malloc() here as this is freed by the table
-    cache
-  */
-  if (!(table= (TABLE*) my_malloc(sizeof(*table)+key_length,
-				  MYF(MY_WME | MY_ZEROFILL))))
-    DBUG_RETURN(-1);
-  table->s= &table->share_not_to_be_used;
-  memcpy((table->s->table_cache_key= (char*) (table+1)), key, key_length);
-  table->s->db= table->s->table_cache_key;
-  table->s->table_name= table->s->table_cache_key + table_in_key_offset;
-  table->s->key_length=key_length;
-  table->in_use=thd;
-  table->locked_by_name=1;
-  table_list->table=table;
-
-  if (my_hash_insert(&open_cache, (byte*) table))
-  {
-    my_free((gptr) table,MYF(0));
+  if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
     DBUG_RETURN(-1);
-  }
-  
+
+  table_list->table= table;
+
   /* Return 1 if table is in use */
   DBUG_RETURN(test(remove_table_from_cache(thd, db, table_list->table_name,
                                            RTFC_NO_FLAG)));
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 8bb63e72bde..173ca6232ee 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -1879,7 +1879,8 @@ int Query_log_event::exec_event(struct st_relay_log_info* rli,
         thd->variables.collation_database= thd->db_charset;
       
       /* Execute the query (note that we bypass dispatch_command()) */
-      mysql_parse(thd, thd->query, thd->query_length);
+      const char* found_semicolon= NULL;
+      mysql_parse(thd, thd->query, thd->query_length, &found_semicolon);
 
     }
     else
@@ -2987,10 +2988,12 @@ int Load_log_event::exec_event(NET* net, struct st_relay_log_info* rli,
   thd->query_error= 0;
   clear_all_errors(thd, rli);
   /*
-    Usually mysql_init_query() is called by mysql_parse(), but we need it here
+    Usually lex_start() is called by mysql_parse(), but we need it here
     as the present method does not call mysql_parse().
   */
-  mysql_init_query(thd, 0, 0);
+  lex_start(thd);
+  mysql_reset_thd_for_next_command(thd);
+
   if (!use_rli_only_for_errors)
   {
     /* Saved for InnoDB, see comment in Query_log_event::exec_event() */
diff --git a/sql/my_decimal.h b/sql/my_decimal.h
index 45270150d22..9558b00f0cf 100644
--- a/sql/my_decimal.h
+++ b/sql/my_decimal.h
@@ -387,5 +387,25 @@ int my_decimal_cmp(const my_decimal *a, const my_decimal *b)
   return decimal_cmp((decimal_t*) a, (decimal_t*) b);
 }
 
+
+inline
+int my_decimal_intg(const my_decimal *a)
+{
+    return decimal_intg((decimal_t*) a);
+}
+
+
+inline
+void my_decimal_trim(ulong *precision, uint *scale)
+{
+  if (!(*precision) && !(*scale))
+  {
+    *precision= 10;
+    *scale= 0;
+    return;
+  }
+}
+
+
 #endif /*my_decimal_h*/
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index c0dfcc1f19e..f03ef487154 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -640,6 +640,8 @@ struct Query_cache_query_flags
   ulong sql_mode;
   ulong max_sort_length;
   ulong group_concat_max_len;
+  ulong default_week_format;
+  ulong div_precision_increment;
   MY_LOCALE *lc_time_names;
 };
 #define QUERY_CACHE_FLAGS_SIZE sizeof(Query_cache_query_flags)
@@ -674,6 +676,8 @@ struct Query_cache_query_flags
 #define query_cache_invalidate_by_MyISAM_filename_ref NULL
 #endif /*HAVE_QUERY_CACHE*/
 
+uint build_table_path(char *buff, size_t bufflen, const char *db,
+                      const char *table, const char *ext);
 bool mysql_create_db(THD *thd, char *db, HA_CREATE_INFO *create, bool silent);
 bool mysql_alter_db(THD *thd, const char *db, HA_CREATE_INFO *create);
 bool mysql_rm_db(THD *thd,char *db,bool if_exists, bool silent);
@@ -694,13 +698,15 @@ bool do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db,
                       bool skip_error);
 bool mysql_change_db(THD *thd, const LEX_STRING *new_db_name,
                      bool force_switch);
-void mysql_parse(THD *thd,char *inBuf,uint length);
+
+void mysql_parse(THD *thd, const char *inBuf, uint length,
+                 const char ** semicolon);
+
 bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length);
 bool is_update_query(enum enum_sql_command command);
 bool alloc_query(THD *thd, const char *packet, uint packet_length);
 void mysql_init_select(LEX *lex);
 void mysql_reset_thd_for_next_command(THD *thd);
-void mysql_init_query(THD *thd, uchar *buf, uint length);
 bool mysql_new_select(LEX *lex, bool move_down);
 void create_select_for_variable(const char *var_name);
 void mysql_init_multi_delete(LEX *lex);
@@ -854,12 +860,14 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create);
 TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type update);
 TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT* mem,
 		  bool *refresh, uint flags);
-bool reopen_name_locked_table(THD* thd, TABLE_LIST* table);
+bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in);
+TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
+                                      uint key_length);
+bool table_cache_has_open_placeholder(THD *thd, const char *db,
+                                      const char *table_name);
 TABLE *find_locked_table(THD *thd, const char *db,const char *table_name);
 bool reopen_table(TABLE *table,bool locked);
 bool reopen_tables(THD *thd,bool get_locks,bool in_refresh);
-void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
-			  bool send_refresh);
 bool close_data_tables(THD *thd,const char *db, const char *table_name);
 bool wait_for_tables(THD *thd);
 bool table_is_used(TABLE *table, bool wait_for_name_lock);
@@ -1015,6 +1023,8 @@ void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields,
                       SELECT_LEX *lex);
 bool add_proc_to_list(THD *thd, Item *item);
 TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find);
+void drop_open_table(THD *thd, TABLE *table, const char *db_name,
+                     const char *table_name);
 void update_non_unique_table_error(TABLE_LIST *update,
                                    const char *operation,
                                    TABLE_LIST *duplicate);
@@ -1267,7 +1277,7 @@ extern double log_01[32];
 extern ulonglong log_10_int[20];
 extern ulonglong keybuff_size;
 extern ulonglong thd_startup_options;
-extern ulong refresh_version,flush_version, thread_id;
+extern ulong flush_version, thread_id;
 extern ulong binlog_cache_use, binlog_cache_disk_use;
 extern ulong aborted_threads,aborted_connects;
 extern ulong delayed_insert_timeout;
@@ -1441,7 +1451,7 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count,
 #define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK      0x0001
 #define MYSQL_LOCK_IGNORE_FLUSH                 0x0002
 #define MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN        0x0004
-#define MYSQL_OPEN_IGNORE_LOCKED_TABLES         0x0008
+#define MYSQL_OPEN_TEMPORARY_ONLY               0x0008
 
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock);
 void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock);
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 9222e15ff91..b9de54dbf5c 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -206,12 +206,68 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
 
           if (!ref.key_length)
             error= table->file->index_first(table->record[0]);
-          else
-	    error= table->file->index_read(table->record[0],key_buff,
-					   ref.key_length,
-					   range_fl & NEAR_MIN ?
-					   HA_READ_AFTER_KEY :
-					   HA_READ_KEY_OR_NEXT);
+          else 
+          {
+            /*
+              Use index to replace MIN/MAX functions with their values
+              according to the following rules:
+           
+              1) Insert the minimum non-null values where the WHERE clause still
+                 matches, or
+              2) a NULL value if there are only NULL values for key_part_k.
+              3) Fail, producing a row of nulls
+
+              Implementation: Read the smallest value using the search key. If
+              the interval is open, read the next value after the search
+              key. If read fails, and we're looking for a MIN() value for a
+              nullable column, test if there is an exact match for the key.
+            */
+            if (!(range_fl & NEAR_MIN))
+              /* 
+                 Closed interval: Either The MIN argument is non-nullable, or
+                 we have a >= predicate for the MIN argument.
+              */
+              error= table->file->index_read(table->record[0], ref.key_buff,
+                                             ref.key_length, 
+                                             HA_READ_KEY_OR_NEXT);
+            else
+            {
+              /*
+                Open interval: There are two cases:
+                1) We have only MIN() and the argument column is nullable, or
+                2) there is a > predicate on it, nullability is irrelevant.
+                We need to scan the next bigger record first.
+              */
+              error= table->file->index_read(table->record[0], ref.key_buff, 
+                                             ref.key_length, HA_READ_AFTER_KEY);
+              /* 
+                 If the found record is outside the group formed by the search
+                 prefix, or there is no such record at all, check if all
+                 records in that group have NULL in the MIN argument
+                 column. If that is the case return that NULL.
+
+                 Check if case 1 from above holds. If it does, we should read
+                 the skipped tuple.
+              */
+              if (ref.key_buff[prefix_len] == 1 && 
+                  /* 
+                     Last keypart (i.e. the argument to MIN) is set to NULL by
+                     find_key_for_maxmin only if all other keyparts are bound
+                     to constants in a conjunction of equalities. Hence, we
+                     can detect this by checking only if the last keypart is
+                     NULL.
+                  */                     
+                  (error == HA_ERR_KEY_NOT_FOUND ||
+                   key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
+              {
+                DBUG_ASSERT(item_field->field->real_maybe_null());
+                error= table->file->index_read(table->record[0], ref.key_buff,
+                                               ref.key_length, 
+                                               HA_READ_KEY_EXACT);
+              }
+            }
+          }
+          /* Verify that the read tuple indeed matches the search key */
 	  if (!error && reckey_in_range(0, &ref, item_field->field, 
 			                conds, range_fl, prefix_len))
 	    error= HA_ERR_KEY_NOT_FOUND;
@@ -739,14 +795,24 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
           if (!max_fl && key_part_used == key_part_to_use && part->null_bit)
           {
             /*
-              SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
-              If key_part2 may be NULL, then we want to find the first row
-              that is not null
+              The query is on this form:
+
+              SELECT MIN(key_part_k) 
+              FROM t1 
+              WHERE key_part_1 = const and ... and key_part_k-1 = const
+
+              If key_part_k is nullable, we want to find the first matching row
+              where key_part_k is not null. The key buffer is now {const, ...,
+              NULL}. This will be passed to the handler along with a flag
+              indicating open interval. If a tuple is read that does not match
+              these search criteria, an attempt will be made to read an exact
+              match for the key buffer.
             */
+            /* Set the first byte of key_part_k to 1, that means NULL */
             ref->key_buff[ref->key_length]= 1;
             ref->key_length+= part->store_length;
             *range_fl&= ~NO_MIN_RANGE;
-            *range_fl|= NEAR_MIN;                // > NULL
+            *range_fl|= NEAR_MIN; // Open interval
           }
           /*
             The following test is false when the key in the key tree is
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 44ad15c3c4e..8808327ce0a 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -1204,6 +1204,11 @@ static bool sys_update_ftb_syntax(THD *thd, set_var * var)
 {
   strmake(ft_boolean_syntax, var->value->str_value.c_ptr(),
 	  sizeof(ft_boolean_syntax)-1);
+
+#ifdef HAVE_QUERY_CACHE
+  query_cache.flush();
+#endif /* HAVE_QUERY_CACHE */
+
   return 0;
 }
 
diff --git a/sql/slave.cc b/sql/slave.cc
index 97615165803..84f409d7f34 100644
--- a/sql/slave.cc
+++ b/sql/slave.cc
@@ -1517,6 +1517,7 @@ static int create_table_from_dump(THD* thd, MYSQL *mysql, const char* db,
   handler *file;
   ulonglong save_options;
   NET *net= &mysql->net;
+  const char *found_semicolon= NULL;
   DBUG_ENTER("create_table_from_dump");  
 
   packet_len= my_net_read(net); // read create table statement
@@ -1567,7 +1568,7 @@ static int create_table_from_dump(THD* thd, MYSQL *mysql, const char* db,
   save_db_length= thd->db_length;
   DBUG_ASSERT(db != 0);
   thd->reset_db((char*)db, strlen(db));
-  mysql_parse(thd, thd->query, packet_len); // run create table
+  mysql_parse(thd, thd->query, packet_len, &found_semicolon); // run create table
   thd->db = save_db;		// leave things the way the were before
   thd->db_length= save_db_length;
   thd->options = save_options;
diff --git a/sql/sp.cc b/sql/sp.cc
index 4fc9ea1f602..976bb1911b2 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -434,10 +434,15 @@ db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
   if ((ret= sp_use_new_db(thd, name->m_db, &old_db, 1, &dbchanged)))
     goto end;
 
-  lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length());
+  {
+    Lex_input_stream lip(thd, defstr.c_ptr(), defstr.length());
+    thd->m_lip= &lip;
+    lex_start(thd);
+    ret= MYSQLparse(thd);
+  }
 
   thd->spcont= 0;
-  if (MYSQLparse(thd) || thd->is_fatal_error || newlex.sphead == NULL)
+  if (ret || thd->is_fatal_error || newlex.sphead == NULL)
   {
     sp_head *sp= newlex.sphead;
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 338b2eb84bb..835d8bf038f 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -512,6 +512,8 @@ sp_head::init_sp_name(THD *thd, sp_name *spname)
   m_qname.length= spname->m_qname.length;
   m_qname.str= strmake_root(thd->mem_root, spname->m_qname.str,
                             m_qname.length);
+
+  DBUG_VOID_RETURN;
 }
 
 
@@ -519,9 +521,10 @@ void
 sp_head::init_strings(THD *thd, LEX *lex)
 {
   DBUG_ENTER("sp_head::init_strings");
-  uchar *endp;                  /* Used to trim the end */
+  const char *endp;                  /* Used to trim the end */
   /* During parsing, we must use thd->mem_root */
   MEM_ROOT *root= thd->mem_root;
+  Lex_input_stream *lip=thd->m_lip;
 
   if (m_param_begin && m_param_end)
   {
@@ -531,17 +534,17 @@ sp_head::init_strings(THD *thd, LEX *lex)
   }
 
   /* If ptr has overrun end_of_query then end_of_query is the end */
-  endp= (lex->ptr > lex->end_of_query ? lex->end_of_query : lex->ptr);
+  endp= (lip->ptr > lip->end_of_query ? lip->end_of_query : lip->ptr);
   /*
     Trim "garbage" at the end. This is sometimes needed with the
     "/ * ! VERSION... * /" wrapper in dump files.
   */
-  endp= skip_rear_comments(m_body_begin, endp);
+  endp= skip_rear_comments((char*) m_body_begin, (char*) endp);
 
   m_body.length= endp - m_body_begin;
-  m_body.str= strmake_root(root, (char *)m_body_begin, m_body.length);
-  m_defstr.length= endp - lex->buf;
-  m_defstr.str= strmake_root(root, (char *)lex->buf, m_defstr.length);
+  m_body.str= strmake_root(root, m_body_begin, m_body.length);
+  m_defstr.length= endp - lip->buf;
+  m_defstr.str= strmake_root(root, lip->buf, m_defstr.length);
   DBUG_VOID_RETURN;
 }
 
@@ -1756,24 +1759,13 @@ sp_head::reset_lex(THD *thd)
   DBUG_ENTER("sp_head::reset_lex");
   LEX *sublex;
   LEX *oldlex= thd->lex;
-  my_lex_states org_next_state= oldlex->next_state;
 
   (void)m_lex.push_front(oldlex);
   thd->lex= sublex= new st_lex;
 
-  /* Reset most stuff. The length arguments doesn't matter here. */
-  lex_start(thd, oldlex->buf, (ulong) (oldlex->end_of_query - oldlex->ptr));
+  /* Reset most stuff. */
+  lex_start(thd);
 
-  /*
-    next_state is normally the same (0), but it happens that we swap lex in
-    "mid-sentence", so we must restore it.
-   */
-  sublex->next_state= org_next_state;
-  /* We must reset ptr and end_of_query again */
-  sublex->ptr= oldlex->ptr;
-  sublex->end_of_query= oldlex->end_of_query;
-  sublex->tok_start= oldlex->tok_start;
-  sublex->yylineno= oldlex->yylineno;
   /* And keep the SP stuff too */
   sublex->sphead= oldlex->sphead;
   sublex->spcont= oldlex->spcont;
@@ -1806,9 +1798,6 @@ sp_head::restore_lex(THD *thd)
   if (! oldlex)
     return;			// Nothing to restore
 
-  // Update some state in the old one first
-  oldlex->ptr= sublex->ptr;
-  oldlex->next_state= sublex->next_state;
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
 
   /*
@@ -2995,10 +2984,20 @@ sp_instr_hreturn::print(String *str)
 uint
 sp_instr_hreturn::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
-  if (m_dest)
-    return sp_instr_jump::opt_mark(sp, leads);
-
   marked= 1;
+  
+  if (m_dest)
+  {
+    /*
+      This is an EXIT handler; next instruction step is in m_dest.
+     */
+    return m_dest;
+  }
+  
+  /*
+    This is a CONTINUE handler; next instruction step will come from
+    the handler stack and not from opt_mark.
+   */
   return UINT_MAX;
 }
 
@@ -3491,6 +3490,14 @@ sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
       tname[tlen]= '\0';
 
       /*
+        Upgrade the lock type because this table list will be used
+        only in pre-locked mode, in which DELAYED inserts are always
+        converted to normal inserts.
+      */
+      if (table->lock_type == TL_WRITE_DELAYED)
+        table->lock_type= TL_WRITE;
+
+      /*
         We ignore alias when we check if table was already marked as temporary
         (and therefore should not be prelocked). Otherwise we will erroneously
         treat table with same name but with different alias as non-temporary.
diff --git a/sql/sp_head.h b/sql/sp_head.h
index cce400d6a14..ed99885ae9a 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -125,7 +125,7 @@ public:
 
   create_field m_return_field_def; /* This is used for FUNCTIONs only. */
 
-  uchar *m_tmp_query;		// Temporary pointer to sub query string
+  const char *m_tmp_query;	// Temporary pointer to sub query string
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
   ulong m_sql_mode;		// For SHOW CREATE and execution
@@ -174,7 +174,9 @@ public:
   */
   HASH m_sroutines;
   // Pointers set during parsing
-  uchar *m_param_begin, *m_param_end, *m_body_begin;
+  const char *m_param_begin;
+  const char *m_param_end;
+  const char *m_body_begin;
 
   /*
     Security context for stored routine which should be run under
@@ -971,6 +973,12 @@ public:
 
   virtual void print(String *str);
 
+  /* This instruction will not be short cut optimized. */
+  virtual uint opt_shortcut_jump(sp_head *sp, sp_instr *start)
+  {
+    return m_ip;
+  }
+
   virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
 private:
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 6e6611d54d2..ed48ca577fb 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -94,6 +94,8 @@ static bool open_new_frm(THD *thd, const char *path, const char *alias,
                          uint db_stat, uint prgflag,
                          uint ha_open_flags, TABLE *outparam,
                          TABLE_LIST *table_desc, MEM_ROOT *mem_root);
+static void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
+                                 bool send_refresh);
 
 extern "C" byte *table_cache_key(const byte *record,uint *length,
 				 my_bool not_used __attribute__((unused)))
@@ -374,7 +376,21 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
       for (uint idx=0 ; idx < open_cache.records ; idx++)
       {
 	TABLE *table=(TABLE*) hash_element(&open_cache,idx);
-	if ((table->s->version) < refresh_version && table->db_stat)
+        /*
+          Note that we wait here only for tables which are actually open, and
+          not for placeholders with TABLE::open_placeholder set. Waiting for
+          latter will cause deadlock in the following scenario, for example:
+
+          conn1: lock table t1 write;
+          conn2: lock table t2 write;
+          conn1: flush tables;
+          conn2: flush tables;
+
+          It also does not make sense to wait for those of placeholders that
+          are employed by CREATE TABLE as in this case table simply does not
+          exist yet.
+        */
+	if (table->needs_reopen_or_name_lock() && table->db_stat)
 	{
 	  found=1;
           DBUG_PRINT("signal", ("Waiting for COND_refresh"));
@@ -616,10 +632,10 @@ bool close_thread_table(THD *thd, TABLE **table_ptr)
   TABLE *table= *table_ptr;
   DBUG_ENTER("close_thread_table");
   DBUG_ASSERT(table->key_read == 0);
-  DBUG_ASSERT(table->file->inited == handler::NONE);
+  DBUG_ASSERT(!table->file || table->file->inited == handler::NONE);
 
   *table_ptr=table->next;
-  if (table->s->version != refresh_version ||
+  if (table->needs_reopen_or_name_lock() ||
       thd->version != refresh_version || !table->db_stat)
   {
     VOID(hash_delete(&open_cache,(byte*) table));
@@ -627,6 +643,12 @@ bool close_thread_table(THD *thd, TABLE **table_ptr)
   }
   else
   {
+    /*
+      Open placeholders have TABLE::db_stat set to 0, so they should be
+      handled by the first alternative.
+    */
+    DBUG_ASSERT(!table->open_placeholder);
+
     if (table->s->flush_version != flush_version)
     {
       table->s->flush_version= flush_version;
@@ -1114,6 +1136,43 @@ TABLE *unlink_open_table(THD *thd, TABLE *list, TABLE *find)
 }
 
 
+/**
+    @brief Auxiliary routine which closes and drops open table.
+
+    @param  thd         Thread handle
+    @param  table       TABLE object for table to be dropped
+    @param  db_name     Name of database for this table
+    @param  table_name  Name of this table
+
+    @note This routine assumes that table to be closed is open only
+          by calling thread so we needn't wait until other threads
+          will close the table. Also unless called under implicit or
+          explicit LOCK TABLES mode it assumes that table to be
+          dropped is already unlocked. In the former case it will
+          also remove lock on the table. But one should not rely on
+          this behaviour as it may change in future.
+*/
+
+void drop_open_table(THD *thd, TABLE *table, const char *db_name,
+                     const char *table_name)
+{
+  if (table->s->tmp_table)
+    close_temporary_table(thd, db_name, table_name);
+  else
+  {
+    enum db_type table_type= table->s->db_type;
+    VOID(pthread_mutex_lock(&LOCK_open));
+    /*
+      unlink_open_table() also tells threads waiting for refresh or close
+      that something has happened.
+    */
+    thd->open_tables= unlink_open_table(thd, thd->open_tables, table);
+    quick_rm_table(table_type, db_name, table_name);
+    VOID(pthread_mutex_unlock(&LOCK_open));
+  }
+}
+
+
 /*
    When we call the following function we must have a lock on
    LOCK_open ; This lock will be unlocked on return.
@@ -1152,6 +1211,11 @@ void wait_for_refresh(THD *thd)
       table_list  TABLE_LIST object for table to be open, TABLE_LIST::table
                   member should point to TABLE object which was used for
                   name-locking.
+      link_in     TRUE  - if TABLE object for table to be opened should be
+                          linked into THD::open_tables list.
+                  FALSE - placeholder used for name-locking is already in
+                          this list so we only need to preserve TABLE::next
+                          pointer.
 
   NOTE
     This function assumes that its caller already acquired LOCK_open mutex.
@@ -1161,7 +1225,7 @@ void wait_for_refresh(THD *thd)
     TRUE  - Error
 */
 
-bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
+bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in)
 {
   TABLE *table= table_list->table;
   TABLE_SHARE *share;
@@ -1199,12 +1263,33 @@ bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
   share= table->s;
   share->db= share->table_cache_key;
   share->key_length=key_length;
+  /*
+    We want to prevent other connections from opening this table until end
+    of statement as it is likely that modifications of table's metadata are
+    not yet finished (for example CREATE TRIGGER have to change .TRG file,
+    or we might want to drop table if CREATE TABLE ... SELECT fails).
+    This also allows us to assume that no other connection will sneak in
+    before we will get table-level lock on this table.
+  */
   share->version=0;
   share->flush_version=0;
   table->in_use = thd;
   check_unused();
-  table->next = thd->open_tables;
-  thd->open_tables = table;
+
+  if (link_in)
+  {
+    table->next= thd->open_tables;
+    thd->open_tables= table;
+  }
+  else
+  {
+    /*
+      TABLE object should be already in THD::open_tables list so we just
+      need to set TABLE::next correctly.
+    */
+    table->next= orig_table.next;
+  }
+
   table->tablenr=thd->current_tablenr++;
   table->used_fields=0;
   table->const_table=0;
@@ -1216,6 +1301,167 @@ bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
 }
 
 
+/**
+    @brief Create and insert into table cache placeholder for table
+           which will prevent its opening (or creation) (a.k.a lock
+           table name).
+
+    @param  thd         Thread context
+    @param  key         Table cache key for name to be locked
+    @param  key_length  Table cache key length
+
+    @return Pointer to TABLE object used for name locking or 0 in
+            case of failure.
+*/
+
+TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
+                                      uint key_length)
+{
+  TABLE *table;
+  char *key_buff;
+  DBUG_ENTER("table_cache_insert_placeholder");
+
+  safe_mutex_assert_owner(&LOCK_open);
+
+  /*
+    Create a table entry with the right key and with an old refresh version
+    Note that we must use my_multi_malloc() here as this is freed by the
+    table cache
+  */
+  if (!my_multi_malloc(MYF(MY_WME | MY_ZEROFILL),
+                       &table, sizeof(*table),
+                       &key_buff, key_length,
+                       NULL))
+    DBUG_RETURN(NULL);
+
+  table->s= &table->share_not_to_be_used;
+  memcpy(key_buff, key, key_length);
+  table->s->table_cache_key= key_buff;
+  table->s->db= table->s->table_cache_key;
+  table->s->table_name= table->s->table_cache_key + strlen(table->s->db) + 1;
+  table->s->key_length= key_length;
+  table->in_use= thd;
+  table->locked_by_name= 1;
+
+  if (my_hash_insert(&open_cache, (byte*)table))
+  {
+    my_free((gptr) table, MYF(0));
+    DBUG_RETURN(NULL);
+  }
+
+  DBUG_RETURN(table);
+}
+
+
+/**
+    @brief Check if table cache contains an open placeholder for the
+           table and if this placeholder was created by another thread.
+
+    @param  thd         Thread context
+    @param  db          Name of database for table in question
+    @param  table_name  Table name
+
+    @note The presence of open placeholder indicates that either some
+          other thread is trying to create table in question and obtained
+          an exclusive name-lock on it or that this table already exists
+          and is being flushed at the moment.
+
+    @note One should acquire LOCK_open mutex before calling this function.
+
+    @note This function is a hack which was introduced in 5.0 only to
+          minimize code changes. It doesn't present in 5.1.
+
+    @retval  TRUE   Table cache contains open placeholder for the table
+                    which was created by some other thread.
+    @retval  FALSE  Otherwise.
+*/
+
+bool table_cache_has_open_placeholder(THD *thd, const char *db,
+                                      const char *table_name)
+{
+  char key[MAX_DBKEY_LENGTH];
+  uint key_length;
+  HASH_SEARCH_STATE state;
+  TABLE *search;
+  DBUG_ENTER("table_cache_has_open_placeholder");
+
+  safe_mutex_assert_owner(&LOCK_open);
+
+  key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
+  for (search= (TABLE*) hash_first(&open_cache, (byte*) key, key_length,
+                                   &state);
+      search ;
+      search= (TABLE*) hash_next(&open_cache, (byte*) key, key_length,
+                                 &state))
+  {
+    if (search->in_use == thd)
+      continue;
+    if (search->open_placeholder)
+        DBUG_RETURN(1);
+  }
+  DBUG_RETURN(0);
+}
+
+
+/**
+    @brief Check that table exists on disk or in some storage engine.
+
+    @param  thd          Thread context
+    @param  table        Table list element
+    @param  exists[out]  Out parameter which is set to TRUE if table
+                         exists and to FALSE otherwise.
+
+    @note This function assumes that caller owns LOCK_open mutex.
+          It also assumes that the fact that there are no name-locks
+          on the table was checked beforehand.
+
+    @note If there is no .FRM file for the table but it exists in one
+          of engines (e.g. it was created on another node of NDB cluster)
+          this function will fetch and create proper .FRM file for it.
+
+    @retval  TRUE   Some error occured
+    @retval  FALSE  No error. 'exists' out parameter set accordingly.
+*/
+
+bool check_if_table_exists(THD *thd, TABLE_LIST *table, bool *exists)
+{
+  char path[FN_REFLEN];
+  int rc;
+  DBUG_ENTER("check_if_table_exists");
+
+  safe_mutex_assert_owner(&LOCK_open);
+
+  *exists= TRUE;
+
+  build_table_path(path, sizeof(path), table->db, table->table_name, reg_ext);
+
+  if (!access(path, F_OK))
+    DBUG_RETURN(FALSE);
+
+  /* .FRM file doesn't exist. Check if some engine can provide it. */
+
+  rc= ha_create_table_from_engine(thd, table->db, table->table_name);
+
+  if (rc < 0)
+  {
+    /* Table does not exists in engines as well. */
+    *exists= FALSE;
+    DBUG_RETURN(FALSE);
+  }
+  else if (!rc)
+  {
+    /* Table exists in some engine and .FRM for it was created. */
+    DBUG_RETURN(FALSE);
+  }
+  else /* (rc > 0) */
+  {
+    my_printf_error(ER_UNKNOWN_ERROR, "Failed to open '%-.64s', error while "
+                    "unpacking from engine", MYF(0), table->table_name);
+    DBUG_RETURN(TRUE);
+  }
+}
+
+
 /*
   Open a table.
 
@@ -1231,12 +1477,17 @@ bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
                           MYSQL_LOCK_IGNORE_FLUSH - Open table even if
                           someone has done a flush or namelock on it.
                           No version number checking is done.
-                          MYSQL_OPEN_IGNORE_LOCKED_TABLES - Open table
-                          ignoring set of locked tables and prelocked mode.
+                          MYSQL_OPEN_TEMPORARY_ONLY - Open only temporary
+                          table not the base table or view.
 
   IMPLEMENTATION
     Uses a cache of open tables to find a table not in use.
 
+    If table list element for the table to be opened has "create" flag
+    set and table does not exist, this function will automatically insert
+    a placeholder for exclusive name lock into the open tables cache and
+    will return the TABLE instance that corresponds to this placeholder.
+
   RETURN
     NULL  Open failed.  If refresh is set then one should close
           all other tables and retry the open.
@@ -1305,6 +1556,12 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     }
   }
 
+  if (flags & MYSQL_OPEN_TEMPORARY_ONLY)
+  {
+    my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->table_name);
+    DBUG_RETURN(0);
+  }
+
   /*
     The table is not temporary - if we're in pre-locked or LOCK TABLES
     mode, let's try to find the requested table in the list of pre-opened
@@ -1312,8 +1569,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     open not pre-opened tables in pre-locked/LOCK TABLES mode.
     TODO: move this block into a separate function.
   */
-  if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) &&
-      (thd->locked_tables || thd->prelocked_mode))
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
     TABLE *best_table= 0;
     int best_distance= INT_MIN;
@@ -1495,7 +1751,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
       c1: name lock t2; -- blocks
       c2: open t1; -- blocks
     */
-    if (table->s->version != refresh_version)
+    if (table->needs_reopen_or_name_lock())
     {
       DBUG_PRINT("note",
                  ("Found table '%s.%s' with different refresh version",
@@ -1507,6 +1763,14 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
         continue;
       }
 
+      /* Avoid self-deadlocks by detecting self-dependencies. */
+      if (table->open_placeholder && table->in_use == thd)
+      {
+	VOID(pthread_mutex_unlock(&LOCK_open));
+        my_error(ER_UPDATE_TABLE_USED, MYF(0), table->s->table_name);
+        DBUG_RETURN(0);
+      }
+
       /*
         Back off, part 1: mark the table as "unused" for the
         purpose of name-locking by setting table->db_stat to 0. Do
@@ -1523,6 +1787,14 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
         and wait till the operation is complete: when any
         operation that juggles with table->s->version completes,
         it broadcasts COND_refresh condition variable.
+        If 'old' table we met is in use by current thread we return
+        without waiting since in this situation it's this thread
+        which is responsible for broadcasting on COND_refresh
+        (and this was done already in close_old_data_files()).
+        Good example of such situation is when we have statement
+        that needs two instances of table and FLUSH TABLES comes
+        after we open first instance but before we open second
+        instance.
       */
       if (table->in_use != thd)
       {
@@ -1564,6 +1836,40 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     while (open_cache.records > table_cache_size && unused_tables)
       VOID(hash_delete(&open_cache,(byte*) unused_tables)); /* purecov: tested */
 
+    if (table_list->create)
+    {
+      bool exists;
+
+      if (check_if_table_exists(thd, table_list, &exists))
+      {
+        VOID(pthread_mutex_unlock(&LOCK_open));
+        DBUG_RETURN(NULL);
+      }
+
+      if (!exists)
+      {
+        /*
+          Table to be created, so we need to create placeholder in table-cache.
+        */
+        if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
+        {
+          VOID(pthread_mutex_unlock(&LOCK_open));
+          DBUG_RETURN(NULL);
+        }
+        /*
+          Link placeholder to the open tables list so it will be automatically
+          removed once tables are closed. Also mark it so it won't be ignored
+          by other trying to take name-lock.
+        */
+        table->open_placeholder= 1;
+        table->next= thd->open_tables;
+        thd->open_tables= table;
+        VOID(pthread_mutex_unlock(&LOCK_open));
+        DBUG_RETURN(table);
+      }
+      /* Table exists. Let us try to open it. */
+    }
+
     /* make a new table */
     if (!(table=(TABLE*) my_malloc(sizeof(*table),MYF(MY_WME))))
     {
@@ -1794,9 +2100,24 @@ bool close_data_tables(THD *thd,const char *db, const char *table_name)
 }
 
 
-/*
-  Reopen all tables with closed data files
-  One should have lock on LOCK_open when calling this
+/**
+    @brief Reopen all tables with closed data files.
+
+    @param thd         Thread context
+    @param get_locks   Should we get locks after reopening tables ?
+    @param in_refresh  Are we in FLUSH TABLES ? TODO: It seems that
+                       we can remove this parameter.
+
+    @note Since this function can't properly handle prelocking and
+          create placeholders it should be used in very special
+          situations like FLUSH TABLES or ALTER TABLE. In general
+          case one should just repeat open_tables()/lock_tables()
+          combination when one needs tables to be reopened (for
+          example see open_and_lock_tables()).
+
+    @note One should have lock on LOCK_open when calling this.
+
+    @return FALSE in case of success, TRUE - otherwise.
 */
 
 bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
@@ -1841,7 +2162,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
       if (in_refresh)
       {
 	table->s->version=0;
-	table->locked_by_flush=0;
+	table->open_placeholder= 0;
       }
     }
   }
@@ -1867,35 +2188,71 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
   DBUG_RETURN(error);
 }
 
-/*
-  Close handlers for tables in list, but leave the TABLE structure
-  intact so that we can re-open these quickly
-  abort_locks is set if called from flush_tables.
+
+/**
+    @brief Close handlers for tables in list, but leave the TABLE structure
+           intact so that we can re-open these quickly.
+
+    @param thd           Thread context
+    @param table         Head of the list of TABLE objects
+    @param morph_locks   TRUE  - remove locks which we have on tables being closed
+                                 but ensure that no DML or DDL will sneak in before
+                                 we will re-open the table (i.e. temporarily morph
+                                 our table-level locks into name-locks).
+                         FALSE - otherwise
+    @param send_refresh  Should we awake waiters even if we didn't close any tables?
 */
 
-void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
+void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
 			  bool send_refresh)
 {
   DBUG_ENTER("close_old_data_files");
   bool found=send_refresh;
   for (; table ; table=table->next)
   {
-    if (table->s->version != refresh_version)
+    if (table->needs_reopen_or_name_lock())
     {
       found=1;
-      if (!abort_locks)				// If not from flush tables
+      /*
+        Note that it is safe to update version even for open placeholders
+        as later in this function we reset TABLE::open_placeholder and thus
+        effectively remove them from the table cache.
+      */
+      if (!morph_locks)                         // If not from flush tables
 	table->s->version= refresh_version;	// Let other threads use table
       if (table->db_stat)
       {
-	if (abort_locks)
-	{
-	  mysql_lock_abort(thd,table);		// Close waiting threads
-	  mysql_lock_remove(thd, thd->locked_tables,table);
-	  table->locked_by_flush=1;		// Will be reopened with locks
-	}
+        if (morph_locks)
+        {
+          /*
+            Wake up threads waiting for table-level lock on this table
+            so they won't sneak in when we will temporarily remove our
+            lock on it. This will also give them a chance to close their
+            instances of this table.
+          */
+          mysql_lock_abort(thd, table);
+          mysql_lock_remove(thd, thd->locked_tables, table);
+          /*
+            We want to protect the table from concurrent DDL operations
+            (like RENAME TABLE) until we will re-open and re-lock it.
+          */
+          table->open_placeholder= 1;
+        }
 	table->file->close();
 	table->db_stat=0;
       }
+      else if (table->open_placeholder)
+      {
+        /*
+          We come here only in close-for-back-off scenario. So we have to
+          "close" create placeholder here to avoid deadlocks (for example,
+          in case of concurrent execution of CREATE TABLE t1 SELECT * FROM t2
+          and RENAME TABLE t2 TO t1). In close-for-re-open scenario we will
+          probably want to let it stay.
+        */
+        DBUG_ASSERT(!morph_locks);
+        table->open_placeholder= 0;
+      }
     }
   }
   if (found)
@@ -1923,9 +2280,8 @@ bool table_is_used(TABLE *table, bool wait_for_name_lock)
          search= (TABLE*) hash_next(&open_cache, (byte*) key,
                                     key_length, &state))
     {
-      if (search->locked_by_flush ||
-	  search->locked_by_name && wait_for_name_lock ||
-	  search->db_stat && search->s->version < refresh_version)
+      if (search->locked_by_name && wait_for_name_lock ||
+	  search->is_name_opened() && search->needs_reopen_or_name_lock())
 	return 1;				// Table is used
     }
   } while ((table=table->next));
@@ -2277,7 +2633,7 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
   */
 
   if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-      thd->lex->sroutines_list.elements)
+      thd->lex->uses_stored_routines())
   {
     bool first_no_prelocking, need_prelocking, tabs_changed;
     TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
@@ -2465,7 +2821,7 @@ process_view_routines:
     */
     if (tables->view && !thd->prelocked_mode &&
         !thd->lex->requires_prelocking() &&
-        tables->view->sroutines_list.elements)
+        tables->view->uses_stored_routines())
     {
       /* We have at least one table in TL here. */
       if (!query_tables_last_own)
@@ -5661,7 +6017,7 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
       else if (in_use != thd)
       {
         in_use->some_tables_deleted=1;
-        if (table->db_stat)
+        if (table->is_name_opened())
         {
           DBUG_PRINT("info", ("Found another active instance of the table"));
   	  result=1;
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 795711b34d8..33d658ce6a1 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -865,9 +865,12 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
     flags.max_sort_length= thd->variables.max_sort_length;
     flags.lc_time_names= thd->variables.lc_time_names;
     flags.group_concat_max_len= thd->variables.group_concat_max_len;
+    flags.div_precision_increment= thd->variables.div_precincrement;
+    flags.default_week_format= thd->variables.default_week_format;
     DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, pkt_nr: %d, \
 CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \
-sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
+sql mode: 0x%lx, sort len: %lu, conncat len: %lu, div_precision: %lu, \
+def_week_frmt: %lu",                          
                           (int)flags.client_long_flag,
                           (int)flags.client_protocol_41,
                           (int)flags.more_results_exists,
@@ -879,7 +882,9 @@ sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
                           (ulong) flags.time_zone,
                           flags.sql_mode,
                           flags.max_sort_length,
-                          flags.group_concat_max_len));
+                          flags.group_concat_max_len,
+                          flags.div_precision_increment,
+                          flags.default_week_format));
     /*
      Make InnoDB to release the adaptive hash index latch before
      acquiring the query cache mutex.
@@ -1107,10 +1112,13 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
   flags.sql_mode= thd->variables.sql_mode;
   flags.max_sort_length= thd->variables.max_sort_length;
   flags.group_concat_max_len= thd->variables.group_concat_max_len;
+  flags.div_precision_increment= thd->variables.div_precincrement;
+  flags.default_week_format= thd->variables.default_week_format;
   flags.lc_time_names= thd->variables.lc_time_names;
   DBUG_PRINT("qcache", ("long %d, 4.1: %d, more results %d, pkt_nr: %d, \
 CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \
-sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
+sql mode: 0x%lx, sort len: %lu, conncat len: %lu, div_precision: %lu, \
+def_week_frmt: %lu",                          
                           (int)flags.client_long_flag,
                           (int)flags.client_protocol_41,
                           (int)flags.more_results_exists,
@@ -1122,7 +1130,9 @@ sql mode: 0x%lx, sort len: %lu, conncat len: %lu",
                           (ulong) flags.time_zone,
                           flags.sql_mode,
                           flags.max_sort_length,
-                          flags.group_concat_max_len));
+                          flags.group_concat_max_len,
+                          flags.div_precision_increment,
+                          flags.default_week_format));
   memcpy((void *)(sql + (tot_length - QUERY_CACHE_FLAGS_SIZE)),
 	 &flags, QUERY_CACHE_FLAGS_SIZE);
   query_block = (Query_cache_block *)  hash_search(&queries, (byte*) sql,
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 6ec14426162..010dc101e0d 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -176,7 +176,7 @@ THD::THD()
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), last_insert_id_used_bin_log(0), insert_id_used(0),
    clear_next_insert_id(0), in_lock_tables(0), bootstrap(0),
-   derived_tables_processing(FALSE), spcont(NULL)
+   derived_tables_processing(FALSE), spcont(NULL), m_lip(NULL)
 {
   ulong tmp;
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 7dd46e2efe7..5f813e82307 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -28,6 +28,7 @@ class Slave_log_event;
 class Format_description_log_event;
 class sp_rcontext;
 class sp_cache;
+class Lex_input_stream;
 
 enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE };
 enum enum_ha_read_modes { RFIRST, RNEXT, RPREV, RLAST, RKEY, RNEXT_SAME };
@@ -494,7 +495,7 @@ public:
 };
 
 
-class delayed_insert;
+class Delayed_insert;
 class select_result;
 
 #define THD_SENTRY_MAGIC 0xfeedd1ff
@@ -1247,7 +1248,7 @@ public:
   time_t     start_time,time_after_lock,user_time;
   time_t     connect_time,thr_create_time; // track down slow pthread_create
   thr_lock_type update_lock_default;
-  delayed_insert *di;
+  Delayed_insert *di;
 
   /* <> 0 if we are inside of trigger or stored function. */
   uint in_sub_stmt;
@@ -1500,6 +1501,15 @@ public:
     query_id_t first_query_id;
   } binlog_evt_union;
 
+  /**
+    Character input stream consumed by the lexical analyser,
+    used during parsing.
+    Note that since the parser is not re-entrant, we keep only one input
+    stream here. This member is valid only when executing code during parsing,
+    and may point to invalid memory after that.
+  */
+  Lex_input_stream *m_lip;
+
   THD();
   ~THD();
 
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index d1a5ab7dfa8..e1318aa2736 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -670,7 +670,7 @@ int mysql_ha_flush(THD *thd, TABLE_LIST *tables, uint mode_flags,
     while (*table_ptr)
     {
       if ((mode_flags & MYSQL_HA_FLUSH_ALL) ||
-          ((*table_ptr)->s->version != refresh_version))
+          (*table_ptr)->needs_reopen_or_name_lock())
       {
         /* The first time it is required, lock for close_thread_table(). */
         if (! did_lock && ! is_locked)
@@ -771,15 +771,22 @@ void mysql_ha_mark_tables_for_reopen(THD *thd, TABLE *table)
   safe_mutex_assert_owner(&LOCK_open);
   for (; table; table= table->next)
   {
-    TABLE_LIST *hash_tables;
-    if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
-                                                (byte*) table->alias,
-                                                strlen(table->alias) + 1)))
+    /*
+      Some elements in open table list, for example placeholders used for
+      name-locking, can have alias set to 0.
+    */
+    if (table->alias)
     {
-      /* Mark table as ready for reopen. */
-      hash_tables->table= NULL;
-      /* End open index/table scans. */
-      table->file->ha_index_or_rnd_end();
+      TABLE_LIST *hash_tables;
+      if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
+                                                  (byte*) table->alias,
+                                                  strlen(table->alias) + 1)))
+      {
+        /* Mark table as ready for reopen. */
+        hash_tables->table= NULL;
+        /* End open index/table scans. */
+        table->file->ha_index_or_rnd_end();
+      }
     }
   }
   DBUG_VOID_RETURN;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index f1d86224adb..5720758128e 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -29,7 +29,7 @@
   waited for to open and lock the table.
 
   If accessing the thread succeeded, in
-  delayed_insert::get_local_table() the table of the thread is copied
+  Delayed_insert::get_local_table() the table of the thread is copied
   for local use. A copy is required because the normal insert logic
   works on a target table, but the other threads table object must not
   be used. The insert logic uses the record buffer to create a record.
@@ -61,7 +61,7 @@
 #include "slave.h"
 
 #ifndef EMBEDDED_LIBRARY
-static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list);
+static bool delayed_get_table(THD *thd, TABLE_LIST *table_list);
 static int write_delayed(THD *thd,TABLE *table, enum_duplicates dup, bool ignore,
 			 char *query, uint query_length, bool log_on);
 static void end_delayed_insert(THD *thd);
@@ -401,6 +401,154 @@ void mark_fields_used_by_triggers_for_insert_stmt(THD *thd, TABLE *table,
 }
 
 
+/**
+  Upgrade table-level lock of INSERT statement to TL_WRITE if
+  a more concurrent lock is infeasible for some reason. This is
+  necessary for engines without internal locking support (MyISAM).
+  An engine with internal locking implementation might later
+  downgrade the lock in handler::store_lock() method.
+*/
+
+static
+void upgrade_lock_type(THD *thd, thr_lock_type *lock_type,
+                       enum_duplicates duplic,
+                       bool is_multi_insert)
+{
+  if (duplic == DUP_UPDATE ||
+      duplic == DUP_REPLACE && *lock_type == TL_WRITE_CONCURRENT_INSERT)
+  {
+    *lock_type= TL_WRITE;
+    return;
+  }
+
+  if (*lock_type == TL_WRITE_DELAYED)
+  {
+    /*
+      We do not use delayed threads if:
+      - we're running in the safe mode or skip-new mode -- the
+        feature is disabled in these modes
+      - we're executing this statement on a replication slave --
+        we need to ensure serial execution of queries on the
+        slave
+      - it is INSERT .. ON DUPLICATE KEY UPDATE - in this case the
+        insert cannot be concurrent
+      - this statement is directly or indirectly invoked from
+        a stored function or trigger (under pre-locking) - to
+        avoid deadlocks, since INSERT DELAYED involves a lock
+        upgrade (TL_WRITE_DELAYED -> TL_WRITE) which we should not
+        attempt while keeping other table level locks.
+      - this statement itself may require pre-locking.
+        We should upgrade the lock even though in most cases
+        delayed functionality may work. Unfortunately, we can't
+        easily identify whether the subject table is not used in
+        the statement indirectly via a stored function or trigger:
+        if it is used, that will lead to a deadlock between the
+        client connection and the delayed thread.
+    */
+    if (specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE) ||
+        thd->slave_thread ||
+        thd->variables.max_insert_delayed_threads == 0 ||
+        thd->prelocked_mode ||
+        thd->lex->uses_stored_routines())
+    {
+      *lock_type= TL_WRITE;
+      return;
+    }
+    bool log_on= (thd->options & OPTION_BIN_LOG ||
+                  ! (thd->security_ctx->master_access & SUPER_ACL));
+    if (log_on && mysql_bin_log.is_open() && is_multi_insert)
+    {
+      /*
+        Statement-based binary logging does not work in this case, because:
+        a) two concurrent statements may have their rows intermixed in the
+        queue, leading to autoincrement replication problems on slave (because
+        the values generated used for one statement don't depend only on the
+        value generated for the first row of this statement, so are not
+        replicable)
+        b) if first row of the statement has an error the full statement is
+        not binlogged, while next rows of the statement may be inserted.
+        c) if first row succeeds, statement is binlogged immediately with a
+        zero error code (i.e. "no error"), if then second row fails, query
+        will fail on slave too and slave will stop (wrongly believing that the
+        master got no error).
+        So we fall back to non-delayed INSERT.
+      */
+      *lock_type= TL_WRITE;
+    }
+  }
+}
+
+
+/**
+  Find or create a delayed insert thread for the first table in
+  the table list, then open and lock the remaining tables.
+  If a table can not be used with insert delayed, upgrade the lock
+  and open and lock all tables using the standard mechanism.
+
+  @param thd         thread context
+  @param table_list  list of "descriptors" for tables referenced
+                     directly in statement SQL text.
+                     The first element in the list corresponds to
+                     the destination table for inserts, remaining
+                     tables, if any, are usually tables referenced
+                     by sub-queries in the right part of the
+                     INSERT.
+
+  @return Status of the operation. In case of success 'table'
+  member of every table_list element points to an instance of
+  class TABLE.
+
+  @sa open_and_lock_tables for more information about MySQL table
+  level locking
+*/
+
+static
+bool open_and_lock_for_insert_delayed(THD *thd, TABLE_LIST *table_list)
+{
+  DBUG_ENTER("open_and_lock_for_insert_delayed");
+
+#ifndef EMBEDDED_LIBRARY
+  if (delayed_get_table(thd, table_list))
+    DBUG_RETURN(TRUE);
+
+  if (table_list->table)
+  {
+    /*
+      Open tables used for sub-selects or in stored functions, will also
+      cache these functions.
+    */
+    if (open_and_lock_tables(thd, table_list->next_global))
+    {
+      end_delayed_insert(thd);
+      DBUG_RETURN(TRUE);
+    }
+    /*
+      First table was not processed by open_and_lock_tables(),
+      we need to set updatability flag "by hand".
+    */
+    if (!table_list->derived && !table_list->view)
+      table_list->updatable= 1;  // usual table
+    DBUG_RETURN(FALSE);
+  }
+#endif
+  /*
+    * This is embedded library and we don't have auxiliary
+    threads OR
+    * a lock upgrade was requested inside delayed_get_table
+      because
+      - there are too many delayed insert threads OR
+      - the table has triggers.
+    Use a normal insert.
+  */
+  table_list->lock_type= TL_WRITE;
+  DBUG_RETURN(open_and_lock_tables(thd, table_list));
+}
+
+
+/**
+  INSERT statement implementation
+*/
+
 bool mysql_insert(THD *thd,TABLE_LIST *table_list,
                   List<Item> &fields,
                   List<List_item> &values_list,
@@ -410,11 +558,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
 		  bool ignore)
 {
   int error, res;
-  /*
-    log_on is about delayed inserts only.
-    By default, both logs are enabled (this won't cause problems if the server
-    runs without --log-update or --log-bin).
-  */
   bool transactional_table, joins_freed= FALSE;
   bool changed;
   uint value_count;
@@ -428,92 +571,49 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
   Name_resolution_context_state ctx_state;
 #ifndef EMBEDDED_LIBRARY
   char *query= thd->query;
-#endif
+  /*
+    log_on is about delayed inserts only.
+    By default, both logs are enabled (this won't cause problems if the server
+    runs without --log-update or --log-bin).
+  */
   bool log_on= (thd->options & OPTION_BIN_LOG) ||
     (!(thd->security_ctx->master_access & SUPER_ACL));
+#endif
   thr_lock_type lock_type = table_list->lock_type;
   Item *unused_conds= 0;
   DBUG_ENTER("mysql_insert");
 
   /*
-    in safe mode or with skip-new change delayed insert to be regular
-    if we are told to replace duplicates, the insert cannot be concurrent
-    delayed insert changed to regular in slave thread
-   */
-#ifdef EMBEDDED_LIBRARY
-  if (lock_type == TL_WRITE_DELAYED)
-    lock_type=TL_WRITE;
-#else
-  if ((lock_type == TL_WRITE_DELAYED &&
-       ((specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) ||
-	thd->slave_thread || !thd->variables.max_insert_delayed_threads)) ||
-      (lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE) ||
-      (duplic == DUP_UPDATE))
-    lock_type=TL_WRITE;
-#endif
-  if ((lock_type == TL_WRITE_DELAYED) &&
-      log_on && mysql_bin_log.is_open() &&
-      (values_list.elements > 1))
+    Upgrade lock type if the requested lock is incompatible with
+    the current connection mode or table operation.
+  */
+  upgrade_lock_type(thd, &table_list->lock_type, duplic,
+                    values_list.elements > 1);
+
+  /*
+    We can't write-delayed into a table locked with LOCK TABLES:
+    this will lead to a deadlock, since the delayed thread will
+    never be able to get a lock on the table. QQQ: why not
+    upgrade the lock here instead?
+  */
+  if (table_list->lock_type == TL_WRITE_DELAYED && thd->locked_tables &&
+      find_locked_table(thd, table_list->db, table_list->table_name))
   {
-    /*
-      Statement-based binary logging does not work in this case, because:
-      a) two concurrent statements may have their rows intermixed in the
-      queue, leading to autoincrement replication problems on slave (because
-      the values generated used for one statement don't depend only on the
-      value generated for the first row of this statement, so are not
-      replicable)
-      b) if first row of the statement has an error the full statement is
-      not binlogged, while next rows of the statement may be inserted.
-      c) if first row succeeds, statement is binlogged immediately with a
-      zero error code (i.e. "no error"), if then second row fails, query
-      will fail on slave too and slave will stop (wrongly believing that the
-      master got no error).
-      So we fallback to non-delayed INSERT.
-    */
-    lock_type= TL_WRITE;
+    my_error(ER_DELAYED_INSERT_TABLE_LOCKED, MYF(0),
+             table_list->table_name);
+    DBUG_RETURN(TRUE);
   }
-  table_list->lock_type= lock_type;
 
-#ifndef EMBEDDED_LIBRARY
-  if (lock_type == TL_WRITE_DELAYED)
+  if (table_list->lock_type == TL_WRITE_DELAYED)
   {
-    res= 1;
-    if (thd->locked_tables)
-    {
-      DBUG_ASSERT(table_list->db); /* Must be set in the parser */
-      if (find_locked_table(thd, table_list->db, table_list->table_name))
-      {
-	my_error(ER_DELAYED_INSERT_TABLE_LOCKED, MYF(0),
-                 table_list->table_name);
-	DBUG_RETURN(TRUE);
-      }
-    }
-    if ((table= delayed_get_table(thd,table_list)) && !thd->is_fatal_error)
-    {
-      /*
-        Open tables used for sub-selects or in stored functions, will also
-        cache these functions.
-      */
-      res= open_and_lock_tables(thd, table_list->next_global);
-      /*
-	First is not processed by open_and_lock_tables() => we need set
-	updateability flags "by hands".
-      */
-      if (!table_list->derived && !table_list->view)
-        table_list->updatable= 1;  // usual table
-    }
-    else if (thd->net.last_errno != ER_WRONG_OBJECT)
-    {
-      /* Too many delayed insert threads;  Use a normal insert */
-      table_list->lock_type= lock_type= TL_WRITE;
-      res= open_and_lock_tables(thd, table_list);
-    }
+    if (open_and_lock_for_insert_delayed(thd, table_list))
+      DBUG_RETURN(TRUE);
   }
   else
-#endif /* EMBEDDED_LIBRARY */
-    res= open_and_lock_tables(thd, table_list);
-  if (res || thd->is_fatal_error)
-    DBUG_RETURN(TRUE);
+  {
+    if (open_and_lock_tables(thd, table_list))
+      DBUG_RETURN(TRUE);
+  }
 
   thd->proc_info="init";
   thd->used_tables=0;
@@ -531,6 +631,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
 
   /* mysql_prepare_insert set table_list->table if it was not set */
   table= table_list->table;
+  lock_type= table_list->lock_type;
 
   context= &thd->lex->select_lex.context;
   /*
@@ -1258,7 +1359,8 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
         DBUG_ASSERT(info->update_fields->elements ==
                     info->update_values->elements);
         if (fill_record_n_invoke_before_triggers(thd, *info->update_fields,
-                                                 *info->update_values, 0,
+                                                 *info->update_values,
+                                                 info->ignore,
                                                  table->triggers,
                                                  TRG_EVENT_UPDATE))
           goto before_trg_err;
@@ -1460,8 +1562,14 @@ public:
   }
 };
 
+/**
+  Delayed_insert - context of a thread responsible for delayed insert
+  into one table. When processing delayed inserts, we create an own
+  thread for every distinct table. Later on all delayed inserts directed
+  into that table are handled by a dedicated thread.
+*/
 
-class delayed_insert :public ilink {
+class Delayed_insert :public ilink {
   uint locks_in_memory;
 public:
   THD thd;
@@ -1475,7 +1583,7 @@ public:
   ulong group_count;
   TABLE_LIST table_list;			// Argument
 
-  delayed_insert()
+  Delayed_insert()
     :locks_in_memory(0),
      table(0),tables_in_use(0),stacked_inserts(0), status(0), dead(0),
      group_count(0)
@@ -1500,7 +1608,7 @@ public:
     delayed_insert_threads++;
     VOID(pthread_mutex_unlock(&LOCK_thread_count));
   }
-  ~delayed_insert()
+  ~Delayed_insert()
   {
     /* The following is not really needed, but just for safety */
     delayed_row *row;
@@ -1548,15 +1656,21 @@ public:
 };
 
 
-I_List<delayed_insert> delayed_threads;
+I_List<Delayed_insert> delayed_threads;
+
 
+/**
+  Return an instance of delayed insert thread that can handle
+  inserts into a given table, if it exists. Otherwise return NULL.
+*/
 
-delayed_insert *find_handler(THD *thd, TABLE_LIST *table_list)
+static
+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;
+  I_List_iterator<Delayed_insert> it(delayed_threads);
+  Delayed_insert *tmp;
   while ((tmp=it++))
   {
     if (!strcmp(tmp->thd.db,table_list->db) &&
@@ -1571,11 +1685,36 @@ delayed_insert *find_handler(THD *thd, TABLE_LIST *table_list)
 }
 
 
-static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
+/**
+  Attempt to find or create a delayed insert thread to handle inserts
+  into this table.
+
+  @return In case of success, table_list->table points to a local copy
+          of the delayed table or is set to NULL, which indicates a
+          request for lock upgrade. In case of failure, value of
+          table_list->table is undefined.
+  @retval TRUE  - this thread ran out of resources OR
+                - a newly created delayed insert thread ran out of
+                  resources OR
+                - the created thread failed to open and lock the table
+                  (e.g. because it does not exist) OR
+                - the table opened in the created thread turned out to
+                  be a view
+  @retval FALSE - table successfully opened OR
+                - too many delayed insert threads OR
+                - the table has triggers and we have to fall back to
+                  a normal INSERT
+                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?
+*/
+
+static
+bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
 {
   int error;
-  delayed_insert *tmp;
-  TABLE *table;
+  Delayed_insert *tmp;
   DBUG_ENTER("delayed_get_table");
 
   /* Must be set in the parser */
@@ -1598,10 +1737,11 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
     */
     if (! (tmp= find_handler(thd, table_list)))
     {
-      if (!(tmp=new delayed_insert()))
+      if (!(tmp=new Delayed_insert()))
       {
-	my_error(ER_OUTOFMEMORY,MYF(0),sizeof(delayed_insert));
-	goto err1;
+	my_error(ER_OUTOFMEMORY,MYF(0),sizeof(Delayed_insert));
+        thd->fatal_error();
+        goto end_create;
       }
       pthread_mutex_lock(&LOCK_thread_count);
       thread_count++;
@@ -1610,9 +1750,10 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
       tmp->thd.query= my_strdup(table_list->table_name,MYF(MY_WME));
       if (tmp->thd.db == NULL || tmp->thd.query == NULL)
       {
+        /* The error is reported */
 	delete tmp;
-	my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
-	goto err1;
+        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;
@@ -1628,7 +1769,8 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
 	tmp->unlock();
 	delete tmp;
 	my_error(ER_CANT_CREATE_THREAD, MYF(0), error);
-	goto err1;
+        thd->fatal_error();
+        goto end_create;
       }
 
       /* Wait until table is open */
@@ -1641,57 +1783,69 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
       thd->proc_info="got old table";
       if (tmp->thd.killed)
       {
-	if (tmp->thd.is_fatal_error)
+	if (tmp->thd.net.report_error)
 	{
-	  /* Copy error message and abort */
-	  thd->fatal_error();
-	  strmov(thd->net.last_error,tmp->thd.net.last_error);
-	  thd->net.last_errno=tmp->thd.net.last_errno;
+          /*
+            Copy the error message. Note that we don't treat fatal
+            errors in the delayed thread as fatal errors in the
+            main thread. Use of my_message will enable stored
+            procedures continue handlers.
+          */
+          my_message(tmp->thd.net.last_errno, tmp->thd.net.last_error,
+                     MYF(0));
 	}
 	tmp->unlock();
-	goto err;
+        goto end_create;
       }
       if (thd->killed)
       {
 	tmp->unlock();
-	goto err;
+        goto end_create;
       }
     }
     pthread_mutex_unlock(&LOCK_delayed_create);
   }
 
   pthread_mutex_lock(&tmp->mutex);
-  table= tmp->get_local_table(thd);
+  table_list->table= tmp->get_local_table(thd);
   pthread_mutex_unlock(&tmp->mutex);
-  if (table)
+  if (table_list->table)
+  {
+    DBUG_ASSERT(thd->net.report_error == 0);
     thd->di=tmp;
-  else if (tmp->thd.is_fatal_error)
-    thd->fatal_error();
+  }
   /* Unlock the delayed insert object after its last access. */
   tmp->unlock();
-  DBUG_RETURN((table_list->table=table));
+  DBUG_RETURN(table_list->table == NULL);
 
- err1:
-  thd->fatal_error();
- err:
+end_create:
   pthread_mutex_unlock(&LOCK_delayed_create);
-  DBUG_RETURN(0); // Continue with normal insert
+  DBUG_RETURN(thd->net.report_error);
 }
 
 
-/*
-  As we can't let many threads modify the same TABLE structure, we create
-  an own structure for each tread.  This includes a row buffer to save the
-  column values and new fields that points to the new row buffer.
-  The memory is allocated in the client thread and is freed automaticly.
+/**
+  As we can't let many client threads modify the same TABLE
+  structure of the dedicated delayed insert thread, we create an
+  own structure for each client thread. This includes a row
+  buffer to save the column values and new fields that point to
+  the new row buffer. The memory is allocated in the client
+  thread and is freed automatically.
+
+  @pre This function is called from the client thread.  Delayed
+       insert thread mutex must be acquired before invoking this
+       function.
+
+  @return Not-NULL table object on success. NULL in case of an error,
+                    which is set in client_thd.
 */
 
-TABLE *delayed_insert::get_local_table(THD* client_thd)
+TABLE *Delayed_insert::get_local_table(THD* client_thd)
 {
   my_ptrdiff_t adjust_ptrs;
   Field **field,**org_field, *found_next_number_field;
   TABLE *copy;
-  DBUG_ENTER("delayed_insert::get_local_table");
+  DBUG_ENTER("Delayed_insert::get_local_table");
 
   /* First request insert thread to get a lock */
   status=1;
@@ -1709,8 +1863,7 @@ TABLE *delayed_insert::get_local_table(THD* client_thd)
       goto error;
     if (dead)
     {
-      strmov(client_thd->net.last_error,thd.net.last_error);
-      client_thd->net.last_errno=thd.net.last_errno;
+      my_message(thd.net.last_errno, thd.net.last_error, MYF(0));
       goto error;
     }
   }
@@ -1755,7 +1908,7 @@ TABLE *delayed_insert::get_local_table(THD* client_thd)
   for (org_field= table->field; *org_field; org_field++, field++)
   {
     if (!(*field= (*org_field)->new_field(client_thd->mem_root, copy, 1)))
-      DBUG_RETURN(0);
+      goto error;
     (*field)->orig_table= copy;			// Remove connection
     (*field)->move_field(adjust_ptrs);		// Point at copy->record[0]
     if (*org_field == found_next_number_field)
@@ -1795,11 +1948,12 @@ TABLE *delayed_insert::get_local_table(THD* client_thd)
 
 /* Put a question in queue */
 
-static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool ignore,
-			 char *query, uint query_length, bool log_on)
+static
+int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool ignore,
+                  char *query, uint query_length, bool log_on)
 {
   delayed_row *row=0;
-  delayed_insert *di=thd->di;
+  Delayed_insert *di=thd->di;
   DBUG_ENTER("write_delayed");
 
   thd->proc_info="waiting for handler insert";
@@ -1863,11 +2017,15 @@ static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool igno
   DBUG_RETURN(1);
 }
 
+/**
+  Signal the delayed insert thread that this user connection
+  is finished using it for this statement.
+*/
 
 static void end_delayed_insert(THD *thd)
 {
   DBUG_ENTER("end_delayed_insert");
-  delayed_insert *di=thd->di;
+  Delayed_insert *di=thd->di;
   pthread_mutex_lock(&di->mutex);
   DBUG_PRINT("info",("tables in use: %d",di->tables_in_use));
   if (!--di->tables_in_use || di->thd.killed)
@@ -1886,8 +2044,8 @@ 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;
+  I_List_iterator<Delayed_insert> it(delayed_threads);
+  Delayed_insert *tmp;
   while ((tmp=it++))
   {
     tmp->thd.killed= THD::KILL_CONNECTION;
@@ -1919,7 +2077,7 @@ void kill_delayed_threads(void)
 
 pthread_handler_t handle_delayed_insert(void *arg)
 {
-  delayed_insert *di=(delayed_insert*) arg;
+  Delayed_insert *di=(Delayed_insert*) arg;
   THD *thd= &di->thd;
 
   pthread_detach_this_thread();
@@ -1975,6 +2133,15 @@ pthread_handler_t handle_delayed_insert(void *arg)
     my_error(ER_ILLEGAL_HA, MYF(0), di->table_list.table_name);
     goto end;
   }
+  if (di->table->triggers)
+  {
+    /*
+      Table has triggers. This is not an error, but we do
+      not support triggers with delayed insert. Terminate the delayed
+      thread without an error and thus request lock upgrade.
+    */
+    goto end;
+  }
   di->table->copy_blobs=1;
 
   /* One can now use this */
@@ -2155,7 +2322,7 @@ static void free_delayed_insert_blobs(register TABLE *table)
 }
 
 
-bool delayed_insert::handle_inserts(void)
+bool Delayed_insert::handle_inserts(void)
 {
   int error;
   ulong max_rows;
@@ -2179,7 +2346,7 @@ bool delayed_insert::handle_inserts(void)
 
   thd.proc_info="insert";
   max_rows= delayed_insert_limit;
-  if (thd.killed || table->s->version != refresh_version)
+  if (thd.killed || table->needs_reopen_or_name_lock())
   {
     thd.killed= THD::KILL_CONNECTION;
     max_rows= ~(ulong)0;                        // Do as much as possible
@@ -2791,8 +2958,8 @@ bool select_insert::send_eof()
 ***************************************************************************/
 
 /*
-  Create table from lists of fields and items (or open existing table
-  with same name).
+  Create table from lists of fields and items (or just return TABLE
+  object for pre-opened existing table).
 
   SYNOPSIS
     create_table_from_items()
@@ -2807,18 +2974,24 @@ bool select_insert::send_eof()
                           of fields for the table (corresponding fields will
                           be added to the end of alter_info->create_list)
       lock         out    Pointer to the MYSQL_LOCK object for table created
-                          (open) will be returned in this parameter. Since
-                          this table is not included in THD::lock caller is
-                          responsible for explicitly unlocking this table.
+                          (or open temporary table) will be returned in this
+                          parameter. Since this table is not included in
+                          THD::lock caller is responsible for explicitly
+                          unlocking this table.
 
   NOTES
-    If 'create_info->options' bitmask has HA_LEX_CREATE_IF_NOT_EXISTS
-    flag and table with name provided already exists then this function will
-    simply open existing table.
-    Also note that create, open and lock sequence in this function is not
-    atomic and thus contains gap for deadlock and can cause other troubles.
-    Since this function contains some logic specific to CREATE TABLE ... SELECT
-    it should be changed before it can be used in other contexts.
+    This function behaves differently for base and temporary tables:
+    - For base table we assume that either table exists and was pre-opened
+      and locked at open_and_lock_tables() stage (and in this case we just
+      emit error or warning and return pre-opened TABLE object) or special
+      placeholder was put in table cache that guarantees that this table
+      won't be created or opened until the placeholder will be removed
+      (so there is an exclusive lock on this table).
+    - We don't pre-open existing temporary table, instead we either open
+      or create and then open table in this function.
+
+    Since this function contains some logic specific to CREATE TABLE ...
+    SELECT it should be changed before it can be used in other contexts.
 
   RETURN VALUES
     non-zero  Pointer to TABLE object for table created or opened
@@ -2841,6 +3014,25 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
   bool not_used;
   DBUG_ENTER("create_table_from_items");
 
+  DBUG_EXECUTE_IF("sleep_create_select_before_check_if_exists", my_sleep(6000000););
+
+  if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE) &&
+      create_table->table->db_stat)
+  {
+    /* Table already exists and was open at open_and_lock_tables() stage. */
+    if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
+    {
+      create_info->table_existed= 1;		// Mark that table existed
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+                          ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
+                          create_table->table_name);
+      DBUG_RETURN(create_table->table);
+    }
+
+    my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+    DBUG_RETURN(0);
+  }
+
   tmp_table.alias= 0;
   tmp_table.timestamp_field= 0;
   tmp_table.s= &tmp_table.share_not_to_be_used;
@@ -2869,8 +3061,15 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
       cr_field->flags &= ~NOT_NULL_FLAG;
     alter_info->create_list.push_back(cr_field);
   }
+
+  DBUG_EXECUTE_IF("sleep_create_select_before_create", my_sleep(6000000););
+
   /*
-    create and lock table
+    Create and lock table.
+
+    Note that we either creating (or opening existing) temporary table or
+    creating base table on which name we have exclusive lock. So code below
+    should not cause deadlocks or races.
 
     We don't log the statement, it will be logged later.
 
@@ -2880,59 +3079,70 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
     don't want to delete from it) 2) it would be written before the CREATE
     TABLE, which is a wrong order. So we keep binary logging disabled when we
     open_table().
-    NOTE: By locking table which we just have created (or for which we just have
-    have found that it already exists) separately from other tables used by the
-    statement we create potential window for deadlock.
-    TODO: create and open should be done atomic !
   */
   {
     tmp_disable_binlog(thd);
     if (!mysql_create_table(thd, create_table->db, create_table->table_name,
                             create_info, alter_info, 0, select_field_count))
     {
-      /*
-        If we are here in prelocked mode we either create temporary table
-        or prelocked mode is caused by the SELECT part of this statement.
-      */
-      DBUG_ASSERT(!thd->prelocked_mode ||
-                  create_info->options & HA_LEX_CREATE_TMP_TABLE ||
-                  thd->lex->requires_prelocking());
 
-      /*
-        NOTE: We don't want to ignore set of locked tables here if we are
-              under explicit LOCK TABLES since it will open gap for deadlock
-              too wide (and also is not backward compatible).
-      */
-      if (! (table= open_table(thd, create_table, thd->mem_root, (bool*) 0,
-                               (MYSQL_LOCK_IGNORE_FLUSH |
-                                ((thd->prelocked_mode == PRELOCKED) ?
-                                 MYSQL_OPEN_IGNORE_LOCKED_TABLES:0)))))
-        quick_rm_table(create_info->db_type, create_table->db,
-                       table_case_name(create_info, create_table->table_name));
+      if (create_info->table_existed &&
+          !(create_info->options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        /*
+          This means that someone created table underneath server
+          or it was created via different mysqld front-end to the
+          cluster. We don't have much options but throw an error.
+        */
+        my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+        DBUG_RETURN(0);
+      }
+
+      DBUG_EXECUTE_IF("sleep_create_select_before_open", my_sleep(6000000););
+
+      if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        VOID(pthread_mutex_lock(&LOCK_open));
+        if (reopen_name_locked_table(thd, create_table, FALSE))
+        {
+          quick_rm_table(create_info->db_type, create_table->db,
+                         table_case_name(create_info,
+                                         create_table->table_name));
+        }
+        else
+          table= create_table->table;
+        VOID(pthread_mutex_unlock(&LOCK_open));
+      }
+      else
+      {
+        if (!(table= open_table(thd, create_table, thd->mem_root, (bool*) 0,
+                                MYSQL_OPEN_TEMPORARY_ONLY)) &&
+            !create_info->table_existed)
+        {
+          /*
+            This shouldn't happen as creation of temporary table should make
+            it preparable for open. But let us do close_temporary_table() here
+            just in case.
+          */
+          close_temporary_table(thd, create_table->db, create_table->table_name);
+        }
+      }
     }
     reenable_binlog(thd);
     if (!table)                                   // open failed
       DBUG_RETURN(0);
   }
 
-  /*
-    FIXME: What happens if trigger manages to be created while we are
-           obtaining this lock ? May be it is sensible just to disable
-           trigger execution in this case ? Or will MYSQL_LOCK_IGNORE_FLUSH
-           save us from that ?
-  */
+  DBUG_EXECUTE_IF("sleep_create_select_before_lock", my_sleep(6000000););
+
   table->reginfo.lock_type=TL_WRITE;
   if (! ((*lock)= mysql_lock_tables(thd, &table, 1,
                                     MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
   {
-    VOID(pthread_mutex_lock(&LOCK_open));
-    hash_delete(&open_cache,(byte*) table);
-    VOID(pthread_mutex_unlock(&LOCK_open));
-    quick_rm_table(create_info->db_type, create_table->db,
-		   table_case_name(create_info, create_table->table_name));
+    if (!create_info->table_existed)
+      drop_open_table(thd, table, create_table->db, create_table->table_name);
     DBUG_RETURN(0);
   }
-  table->file->extra(HA_EXTRA_WRITE_CACHE);
   DBUG_RETURN(table);
 }
 
@@ -2983,8 +3193,10 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
                           (thd->variables.sql_mode &
                            (MODE_STRICT_TRANS_TABLES |
                             MODE_STRICT_ALL_TABLES)));
-  DBUG_RETURN(check_that_all_fields_are_given_values(thd, table,
-                                                     table_list));
+  if (check_that_all_fields_are_given_values(thd, table, table_list))
+    DBUG_RETURN(1);
+  table->file->extra(HA_EXTRA_WRITE_CACHE);
+  DBUG_RETURN(0);
 }
 
 
@@ -3016,31 +3228,18 @@ bool select_create::send_eof()
   {
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
-    VOID(pthread_mutex_lock(&LOCK_open));
-    mysql_unlock_tables(thd, lock);
-    /*
-      TODO:
-      Check if we can remove the following two rows.
-      We should be able to just keep the table in the table cache.
-    */
-    if (!table->s->tmp_table)
+    if (lock)
     {
-      ulong version= table->s->version;
-      hash_delete(&open_cache,(byte*) table);
-      /* Tell threads waiting for refresh that something has happened */
-      if (version != refresh_version)
-        broadcast_refresh();
+      mysql_unlock_tables(thd, lock);
+      lock= 0;
     }
-    lock=0;
-    table=0;
-    VOID(pthread_mutex_unlock(&LOCK_open));
   }
   return tmp;
 }
 
+
 void select_create::abort()
 {
-  VOID(pthread_mutex_lock(&LOCK_open));
   if (lock)
   {
     mysql_unlock_tables(thd, lock);
@@ -3050,22 +3249,10 @@ void select_create::abort()
   {
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
-    enum db_type table_type=table->s->db_type;
-    if (!table->s->tmp_table)
-    {
-      ulong version= table->s->version;
-      hash_delete(&open_cache,(byte*) table);
-      if (!create_info->table_existed)
-        quick_rm_table(table_type, create_table->db, create_table->table_name);
-      /* Tell threads waiting for refresh that something has happened */
-      if (version != refresh_version)
-        broadcast_refresh();
-    }
-    else if (!create_info->table_existed)
-      close_temporary_table(thd, create_table->db, create_table->table_name);
+    if (!create_info->table_existed)
+      drop_open_table(thd, table, create_table->db, create_table->table_name);
     table=0;
   }
-  VOID(pthread_mutex_unlock(&LOCK_open));
 }
 
 
@@ -3076,8 +3263,8 @@ void select_create::abort()
 #ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
 template class List_iterator_fast<List_item>;
 #ifndef EMBEDDED_LIBRARY
-template class I_List<delayed_insert>;
-template class I_List_iterator<delayed_insert>;
+template class I_List<Delayed_insert>;
+template class I_List_iterator<Delayed_insert>;
 template class I_List<delayed_row>;
 #endif /* EMBEDDED_LIBRARY */
 #endif /* HAVE_EXPLICIT_TEMPLATE_INSTANTIATION */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 3be844b6761..7bcdc499011 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -32,13 +32,13 @@ sys_var_long_ptr trg_new_row_fake_var(0, 0);
 
 /* Macros to look like lex */
 
-#define yyGet()		*(lex->ptr++)
-#define yyGetLast()	lex->ptr[-1]
-#define yyPeek()	lex->ptr[0]
-#define yyPeek2()	lex->ptr[1]
-#define yyUnget()	lex->ptr--
-#define yySkip()	lex->ptr++
-#define yyLength()	((uint) (lex->ptr - lex->tok_start)-1)
+#define yyGet()		*(lip->ptr++)
+#define yyGetLast()	lip->ptr[-1]
+#define yyPeek()	lip->ptr[0]
+#define yyPeek2()	lip->ptr[1]
+#define yyUnget()	lip->ptr--
+#define yySkip()	lip->ptr++
+#define yyLength()	((uint) (lip->ptr - lip->tok_start)-1)
 
 /* Longest standard keyword name */
 #define TOCK_NAME_LENGTH 24
@@ -108,6 +108,29 @@ st_parsing_options::reset()
   allows_derived= TRUE;
 }
 
+Lex_input_stream::Lex_input_stream(THD *thd,
+                                   const char* buffer,
+                                   unsigned int length)
+: m_thd(thd),
+  yylineno(1),
+  yytoklen(0),
+  yylval(NULL),
+  ptr(buffer),
+  tok_start(NULL),
+  tok_end(NULL),
+  end_of_query(buffer + length),
+  tok_start_prev(NULL),
+  buf(buffer),
+  next_state(MY_LEX_START),
+  found_semicolon(NULL),
+  ignore_space(test(thd->variables.sql_mode & MODE_IGNORE_SPACE)),
+  stmt_prepare_mode(FALSE)
+{
+}
+
+Lex_input_stream::~Lex_input_stream()
+{}
+
 
 /*
   This is called before every query that is to be parsed.
@@ -115,14 +138,12 @@ st_parsing_options::reset()
   (We already do too much here)
 */
 
-void lex_start(THD *thd, uchar *buf,uint length)
+void lex_start(THD *thd)
 {
   LEX *lex= thd->lex;
   DBUG_ENTER("lex_start");
 
   lex->thd= lex->unit.thd= thd;
-  lex->buf= lex->ptr= buf;
-  lex->end_of_query= buf+length;
 
   lex->context_stack.empty();
   lex->unit.init_query();
@@ -152,18 +173,15 @@ void lex_start(THD *thd, uchar *buf,uint length)
   lex->describe= 0;
   lex->subqueries= FALSE;
   lex->view_prepare_mode= FALSE;
-  lex->stmt_prepare_mode= FALSE;
   lex->derived_tables= 0;
   lex->lock_option= TL_READ;
-  lex->found_semicolon= 0;
   lex->safe_to_cache_query= 1;
   lex->time_zone_tables_used= 0;
   lex->leaf_tables_insert= 0;
   lex->parsing_options.reset();
   lex->empty_field_list_on_rset= 0;
   lex->select_lex.select_number= 1;
-  lex->next_state=MY_LEX_START;
-  lex->yylineno = 1;
+
   lex->in_comment=0;
   lex->length=0;
   lex->select_lex.in_sum_expr=0;
@@ -175,7 +193,6 @@ void lex_start(THD *thd, uchar *buf,uint length)
   lex->select_lex.udf_list.empty();
   lex->current_select= &lex->select_lex;
   lex->yacc_yyss=lex->yacc_yyvs=0;
-  lex->ignore_space=test(thd->variables.sql_mode & MODE_IGNORE_SPACE);
   lex->sql_command= lex->orig_sql_command= SQLCOM_END;
   lex->duplicates= DUP_ERROR;
   lex->ignore= 0;
@@ -201,22 +218,22 @@ void lex_end(LEX *lex)
 }
 
 
-static int find_keyword(LEX *lex, uint len, bool function)
+static int find_keyword(Lex_input_stream *lip, uint len, bool function)
 {
-  uchar *tok=lex->tok_start;
+  const char *tok= lip->tok_start;
 
-  SYMBOL *symbol = get_hash_symbol((const char *)tok,len,function);
+  SYMBOL *symbol= get_hash_symbol(tok, len, function);
   if (symbol)
   {
-    lex->yylval->symbol.symbol=symbol;
-    lex->yylval->symbol.str= (char*) tok;
-    lex->yylval->symbol.length=len;
+    lip->yylval->symbol.symbol=symbol;
+    lip->yylval->symbol.str= (char*) tok;
+    lip->yylval->symbol.length=len;
     
     if ((symbol->tok == NOT_SYM) &&
-        (lex->thd->variables.sql_mode & MODE_HIGH_NOT_PRECEDENCE))
+        (lip->m_thd->variables.sql_mode & MODE_HIGH_NOT_PRECEDENCE))
       return NOT2_SYM;
     if ((symbol->tok == OR_OR_SYM) &&
-	!(lex->thd->variables.sql_mode & MODE_PIPES_AS_CONCAT))
+	!(lip->m_thd->variables.sql_mode & MODE_PIPES_AS_CONCAT))
       return OR2_SYM;
     
     return symbol->tok;
@@ -245,12 +262,12 @@ bool is_keyword(const char *name, uint len)
 
 /* make a copy of token before ptr and set yytoklen */
 
-static LEX_STRING get_token(LEX *lex,uint length)
+static LEX_STRING get_token(Lex_input_stream *lip, uint skip, uint length)
 {
   LEX_STRING tmp;
   yyUnget();			// ptr points now after last token char
-  tmp.length=lex->yytoklen=length;
-  tmp.str=(char*) lex->thd->strmake((char*) lex->tok_start,tmp.length);
+  tmp.length=lip->yytoklen=length;
+  tmp.str= lip->m_thd->strmake(lip->tok_start + skip, tmp.length);
   return tmp;
 }
 
@@ -261,16 +278,19 @@ static LEX_STRING get_token(LEX *lex,uint length)
    future to operate multichar strings (like ucs2)
 */
 
-static LEX_STRING get_quoted_token(LEX *lex,uint length, char quote)
+static LEX_STRING get_quoted_token(Lex_input_stream *lip,
+                                   uint skip,
+                                   uint length, char quote)
 {
   LEX_STRING tmp;
   byte *from, *to, *end;
   yyUnget();			// ptr points now after last token char
-  tmp.length=lex->yytoklen=length;
-  tmp.str=(char*) lex->thd->alloc(tmp.length+1);
-  for (from= (byte*) lex->tok_start, to= (byte*) tmp.str, end= to+length ;
-       to != end ;
-       )
+  tmp.length=lip->yytoklen=length;
+  tmp.str=(char*) lip->m_thd->alloc(tmp.length+1);
+  from= (byte*) lip->tok_start + skip;
+  to= (byte*) tmp.str;
+  end= to+length;
+  for ( ; to != end; )
   {
     if ((*to++= *from++) == quote)
       from++;					// Skip double quotes
@@ -285,15 +305,14 @@ static LEX_STRING get_quoted_token(LEX *lex,uint length, char quote)
   Fix sometimes to do only one scan of the string
 */
 
-static char *get_text(LEX *lex)
+static char *get_text(Lex_input_stream *lip)
 {
   reg1 uchar c,sep;
   uint found_escape=0;
-  CHARSET_INFO *cs= lex->thd->charset();
+  CHARSET_INFO *cs= lip->m_thd->charset();
 
   sep= yyGetLast();			// String should end with this
-  //lex->tok_start=lex->ptr-1;		// Remember '
-  while (lex->ptr != lex->end_of_query)
+  while (lip->ptr != lip->end_of_query)
   {
     c = yyGet();
 #ifdef USE_MB
@@ -301,18 +320,18 @@ static char *get_text(LEX *lex)
       int l;
       if (use_mb(cs) &&
           (l = my_ismbchar(cs,
-                           (const char *)lex->ptr-1,
-                           (const char *)lex->end_of_query))) {
-	lex->ptr += l-1;
+                           lip->ptr-1,
+                           lip->end_of_query))) {
+	lip->ptr += l-1;
 	continue;
       }
     }
 #endif
     if (c == '\\' &&
-	!(lex->thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))
+	!(lip->m_thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))
     {					// Escaped character
       found_escape=1;
-      if (lex->ptr == lex->end_of_query)
+      if (lip->ptr == lip->end_of_query)
 	return 0;
       yySkip();
     }
@@ -327,21 +346,23 @@ static char *get_text(LEX *lex)
 	yyUnget();
 
       /* Found end. Unescape and return string */
-      uchar *str,*end,*start;
+      const char *str;
+      const char *end;
+      char *start;
 
-      str=lex->tok_start+1;
-      end=lex->ptr-1;
-      if (!(start=(uchar*) lex->thd->alloc((uint) (end-str)+1)))
+      str=lip->tok_start+1;
+      end=lip->ptr-1;
+      if (!(start=(char*) lip->m_thd->alloc((uint) (end-str)+1)))
 	return (char*) "";		// Sql_alloc has set error flag
       if (!found_escape)
       {
-	lex->yytoklen=(uint) (end-str);
-	memcpy(start,str,lex->yytoklen);
-	start[lex->yytoklen]=0;
+	lip->yytoklen=(uint) (end-str);
+	memcpy(start,str,lip->yytoklen);
+	start[lip->yytoklen]=0;
       }
       else
       {
-	uchar *to;
+	char *to;
 
 	for (to=start ; str != end ; str++)
 	{
@@ -356,7 +377,7 @@ static char *get_text(LEX *lex)
 	      continue;
 	  }
 #endif
-	  if (!(lex->thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES) &&
+	  if (!(lip->m_thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES) &&
               *str == '\\' && str+1 != end)
 	  {
 	    switch(*++str) {
@@ -393,7 +414,7 @@ static char *get_text(LEX *lex)
 	    *to++ = *str;
 	}
 	*to=0;
-	lex->yytoklen=(uint) (to-start);
+	lip->yytoklen=(uint) (to-start);
       }
       return (char*) start;
     }
@@ -506,20 +527,21 @@ int MYSQLlex(void *arg, void *yythd)
   int	tokval, result_state;
   uint length;
   enum my_lex_states state;
-  LEX	*lex= ((THD *)yythd)->lex;
+  THD *thd= (THD *)yythd;
+  Lex_input_stream *lip= thd->m_lip;
+  LEX *lex= thd->lex;
   YYSTYPE *yylval=(YYSTYPE*) arg;
-  CHARSET_INFO *cs= ((THD *) yythd)->charset();
+  CHARSET_INFO *cs= thd->charset();
   uchar *state_map= cs->state_map;
   uchar *ident_map= cs->ident_map;
 
-  lex->yylval=yylval;			// The global state
+  lip->yylval=yylval;			// The global state
 
-  lex->tok_end_prev= lex->tok_end;
-  lex->tok_start_prev= lex->tok_start;
+  lip->tok_start_prev= lip->tok_start;
 
-  lex->tok_start=lex->tok_end=lex->ptr;
-  state=lex->next_state;
-  lex->next_state=MY_LEX_OPERATOR_OR_IDENT;
+  lip->tok_start=lip->tok_end=lip->ptr;
+  state=lip->next_state;
+  lip->next_state=MY_LEX_OPERATOR_OR_IDENT;
   LINT_INIT(c);
   for (;;)
   {
@@ -530,9 +552,9 @@ int MYSQLlex(void *arg, void *yythd)
       for (c=yyGet() ; (state_map[c] == MY_LEX_SKIP) ; c= yyGet())
       {
 	if (c == '\n')
-	  lex->yylineno++;
+	  lip->yylineno++;
       }
-      lex->tok_start=lex->ptr-1;	// Start of real token
+      lip->tok_start=lip->ptr-1;	// Start of real token
       state= (enum my_lex_states) state_map[c];
       break;
     case MY_LEX_ESCAPE:
@@ -551,20 +573,20 @@ int MYSQLlex(void *arg, void *yythd)
         state=MY_LEX_COMMENT;
         break;
       }
-      yylval->lex_str.str=(char*) (lex->ptr=lex->tok_start);// Set to first chr
+      yylval->lex_str.str=(char*) (lip->ptr=lip->tok_start);// Set to first chr
       yylval->lex_str.length=1;
       c=yyGet();
       if (c != ')')
-	lex->next_state= MY_LEX_START;	// Allow signed numbers
+	lip->next_state= MY_LEX_START;	// Allow signed numbers
       if (c == ',')
-	lex->tok_start=lex->ptr;	// Let tok_start point at next item
+	lip->tok_start=lip->ptr;	// Let tok_start point at next item
       /*
         Check for a placeholder: it should not precede a possible identifier
         because of binlogging: when a placeholder is replaced with
         its value in a query for the binlog, the query must stay
         grammatically correct.
       */
-      else if (c == '?' && lex->stmt_prepare_mode && !ident_map[yyPeek()])
+      else if (c == '?' && lip->stmt_prepare_mode && !ident_map[yyPeek()])
         return(PARAM_MARKER);
       return((int) c);
 
@@ -575,14 +597,14 @@ int MYSQLlex(void *arg, void *yythd)
 	break;
       }
       /* Found N'string' */
-      lex->tok_start++;                 // Skip N
+      lip->tok_start++;                 // Skip N
       yySkip();                         // Skip '
-      if (!(yylval->lex_str.str = get_text(lex)))
+      if (!(yylval->lex_str.str = get_text(lip)))
       {
 	state= MY_LEX_CHAR;             // Read char by char
 	break;
       }
-      yylval->lex_str.length= lex->yytoklen;
+      yylval->lex_str.length= lip->yytoklen;
       return(NCHAR_STRING);
 
     case MY_LEX_IDENT_OR_HEX:
@@ -598,7 +620,7 @@ int MYSQLlex(void *arg, void *yythd)
         break;
       }
     case MY_LEX_IDENT:
-      uchar *start;
+      const char *start;
 #if defined(USE_MB) && defined(USE_MB_IDENT)
       if (use_mb(cs))
       {
@@ -606,13 +628,13 @@ int MYSQLlex(void *arg, void *yythd)
         if (my_mbcharlen(cs, yyGetLast()) > 1)
         {
           int l = my_ismbchar(cs,
-                              (const char *)lex->ptr-1,
-                              (const char *)lex->end_of_query);
+                              lip->ptr-1,
+                              lip->end_of_query);
           if (l == 0) {
             state = MY_LEX_CHAR;
             continue;
           }
-          lex->ptr += l - 1;
+          lip->ptr += l - 1;
         }
         while (ident_map[c=yyGet()])
         {
@@ -620,10 +642,10 @@ int MYSQLlex(void *arg, void *yythd)
           {
             int l;
             if ((l = my_ismbchar(cs,
-                              (const char *)lex->ptr-1,
-                              (const char *)lex->end_of_query)) == 0)
+                                 lip->ptr-1,
+                                 lip->end_of_query)) == 0)
               break;
-            lex->ptr += l-1;
+            lip->ptr += l-1;
           }
         }
       }
@@ -634,9 +656,9 @@ int MYSQLlex(void *arg, void *yythd)
         /* If there were non-ASCII characters, mark that we must convert */
         result_state= result_state & 0x80 ? IDENT_QUOTED : IDENT;
       }
-      length= (uint) (lex->ptr - lex->tok_start)-1;
-      start= lex->ptr;
-      if (lex->ignore_space)
+      length= (uint) (lip->ptr - lip->tok_start)-1;
+      start= lip->ptr;
+      if (lip->ignore_space)
       {
         /*
           If we find a space then this can't be an identifier. We notice this
@@ -644,19 +666,19 @@ int MYSQLlex(void *arg, void *yythd)
         */
         for (; state_map[c] == MY_LEX_SKIP ; c= yyGet());
       }
-      if (start == lex->ptr && c == '.' && ident_map[yyPeek()])
-	lex->next_state=MY_LEX_IDENT_SEP;
+      if (start == lip->ptr && c == '.' && ident_map[yyPeek()])
+	lip->next_state=MY_LEX_IDENT_SEP;
       else
       {					// '(' must follow directly if function
 	yyUnget();
-	if ((tokval = find_keyword(lex,length,c == '(')))
+	if ((tokval = find_keyword(lip, length, c == '(')))
 	{
-	  lex->next_state= MY_LEX_START;	// Allow signed numbers
+	  lip->next_state= MY_LEX_START;	// Allow signed numbers
 	  return(tokval);		// Was keyword
 	}
 	yySkip();			// next state does a unget
       }
-      yylval->lex_str=get_token(lex,length);
+      yylval->lex_str=get_token(lip, 0, length);
 
       /* 
          Note: "SELECT _bla AS 'alias'"
@@ -673,12 +695,12 @@ int MYSQLlex(void *arg, void *yythd)
       return(result_state);			// IDENT or IDENT_QUOTED
 
     case MY_LEX_IDENT_SEP:		// Found ident and now '.'
-      yylval->lex_str.str=(char*) lex->ptr;
+      yylval->lex_str.str=(char*) lip->ptr;
       yylval->lex_str.length=1;
       c=yyGet();			// should be '.'
-      lex->next_state= MY_LEX_IDENT_START;// Next is an ident (not a keyword)
+      lip->next_state= MY_LEX_IDENT_START;// Next is an ident (not a keyword)
       if (!ident_map[yyPeek()])		// Probably ` or "
-	lex->next_state= MY_LEX_START;
+	lip->next_state= MY_LEX_START;
       return((int) c);
 
     case MY_LEX_NUMBER_IDENT:		// number or ident which num-start
@@ -698,36 +720,32 @@ int MYSQLlex(void *arg, void *yythd)
 	  {
 	    yySkip();
 	    while (my_isdigit(cs,yyGet())) ;
-	    yylval->lex_str=get_token(lex,yyLength());
+	    yylval->lex_str=get_token(lip, 0, yyLength());
 	    return(FLOAT_NUM);
 	  }
 	}
 	yyUnget(); /* purecov: inspected */
       }
-      else if (c == 'x' && (lex->ptr - lex->tok_start) == 2 &&
-	  lex->tok_start[0] == '0' )
+      else if (c == 'x' && (lip->ptr - lip->tok_start) == 2 &&
+	  lip->tok_start[0] == '0' )
       {						// Varbinary
 	while (my_isxdigit(cs,(c = yyGet()))) ;
-	if ((lex->ptr - lex->tok_start) >= 4 && !ident_map[c])
+	if ((lip->ptr - lip->tok_start) >= 4 && !ident_map[c])
 	{
-	  yylval->lex_str=get_token(lex,yyLength());
-	  yylval->lex_str.str+=2;		// Skip 0x
-	  yylval->lex_str.length-=2;
-	  lex->yytoklen-=2;
+          /* skip '0x' */
+	  yylval->lex_str=get_token(lip, 2, yyLength()-2);
 	  return (HEX_NUM);
 	}
 	yyUnget();
       }
-      else if (c == 'b' && (lex->ptr - lex->tok_start) == 2 &&
-               lex->tok_start[0] == '0' )
+      else if (c == 'b' && (lip->ptr - lip->tok_start) == 2 &&
+               lip->tok_start[0] == '0' )
       {						// b'bin-number'
 	while (my_isxdigit(cs,(c = yyGet()))) ;
-	if ((lex->ptr - lex->tok_start) >= 4 && !ident_map[c])
+	if ((lip->ptr - lip->tok_start) >= 4 && !ident_map[c])
 	{
-	  yylval->lex_str= get_token(lex, yyLength());
-	  yylval->lex_str.str+= 2;		// Skip 0x
-	  yylval->lex_str.length-= 2;
-	  lex->yytoklen-= 2;
+          /* Skip '0b' */
+	  yylval->lex_str= get_token(lip, 2, yyLength()-2);
 	  return (BIN_NUM);
 	}
 	yyUnget();
@@ -745,10 +763,10 @@ int MYSQLlex(void *arg, void *yythd)
           {
             int l;
             if ((l = my_ismbchar(cs,
-                                 (const char *)lex->ptr-1,
-                                 (const char *)lex->end_of_query)) == 0)
+                                 lip->ptr-1,
+                                 lip->end_of_query)) == 0)
               break;
-            lex->ptr += l-1;
+            lip->ptr += l-1;
           }
         }
       }
@@ -760,16 +778,15 @@ int MYSQLlex(void *arg, void *yythd)
         result_state= result_state & 0x80 ? IDENT_QUOTED : IDENT;
       }
       if (c == '.' && ident_map[yyPeek()])
-	lex->next_state=MY_LEX_IDENT_SEP;// Next is '.'
+	lip->next_state=MY_LEX_IDENT_SEP;// Next is '.'
 
-      yylval->lex_str= get_token(lex,yyLength());
+      yylval->lex_str= get_token(lip, 0, yyLength());
       return(result_state);
 
     case MY_LEX_USER_VARIABLE_DELIMITER:	// Found quote char
     {
       uint double_quotes= 0;
       char quote_char= c;                       // Used char
-      lex->tok_start=lex->ptr;			// Skip first `
       while ((c=yyGet()))
       {
 	int var_length;
@@ -789,23 +806,24 @@ int MYSQLlex(void *arg, void *yythd)
 #ifdef USE_MB
 	else if (var_length < 1)
 	  break;				// Error
-	lex->ptr+= var_length-1;
+	lip->ptr+= var_length-1;
 #endif
       }
       if (double_quotes)
-	yylval->lex_str=get_quoted_token(lex,yyLength() - double_quotes,
+	yylval->lex_str=get_quoted_token(lip, 1,
+                                         yyLength() - double_quotes -1,
 					 quote_char);
       else
-	yylval->lex_str=get_token(lex,yyLength());
+	yylval->lex_str=get_token(lip, 1, yyLength() -1);
       if (c == quote_char)
 	yySkip();			// Skip end `
-      lex->next_state= MY_LEX_START;
+      lip->next_state= MY_LEX_START;
       return(IDENT_QUOTED);
     }
-    case MY_LEX_INT_OR_REAL:		// Compleat int or incompleat real
+    case MY_LEX_INT_OR_REAL:		// Complete int or incomplete real
       if (c != '.')
       {					// Found complete integer number.
-	yylval->lex_str=get_token(lex,yyLength());
+	yylval->lex_str=get_token(lip, 0, yyLength());
 	return int_token(yylval->lex_str.str,yylval->lex_str.length);
       }
       // fall through
@@ -823,47 +841,45 @@ int MYSQLlex(void *arg, void *yythd)
 	  break;
 	}
 	while (my_isdigit(cs,yyGet())) ;
-	yylval->lex_str=get_token(lex,yyLength());
+	yylval->lex_str=get_token(lip, 0, yyLength());
 	return(FLOAT_NUM);
       }
-      yylval->lex_str=get_token(lex,yyLength());
+      yylval->lex_str=get_token(lip, 0, yyLength());
       return(DECIMAL_NUM);
 
     case MY_LEX_HEX_NUMBER:		// Found x'hexstring'
       yyGet();				// Skip '
       while (my_isxdigit(cs,(c = yyGet()))) ;
-      length=(lex->ptr - lex->tok_start);	// Length of hexnum+3
+      length=(lip->ptr - lip->tok_start);	// Length of hexnum+3
       if (!(length & 1) || c != '\'')
       {
 	return(ABORT_SYM);		// Illegal hex constant
       }
       yyGet();				// get_token makes an unget
-      yylval->lex_str=get_token(lex,length);
-      yylval->lex_str.str+=2;		// Skip x'
-      yylval->lex_str.length-=3;	// Don't count x' and last '
-      lex->yytoklen-=3;
+      yylval->lex_str=get_token(lip,
+                                2,          // skip x'
+                                length-3);  // don't count x' and last '
       return (HEX_NUM);
 
     case MY_LEX_BIN_NUMBER:           // Found b'bin-string'
       yyGet();                                // Skip '
       while ((c= yyGet()) == '0' || c == '1');
-      length= (lex->ptr - lex->tok_start);    // Length of bin-num + 3
+      length= (lip->ptr - lip->tok_start);    // Length of bin-num + 3
       if (c != '\'')
       return(ABORT_SYM);              // Illegal hex constant
       yyGet();                        // get_token makes an unget
-      yylval->lex_str= get_token(lex, length);
-      yylval->lex_str.str+= 2;        // Skip b'
-      yylval->lex_str.length-= 3;     // Don't count b' and last '
-      lex->yytoklen-= 3;
-      return (BIN_NUM); 
+      yylval->lex_str= get_token(lip,
+                                 2,         // skip b'
+                                 length-3); // don't count b' and last '
+      return (BIN_NUM);
 
     case MY_LEX_CMP_OP:			// Incomplete comparison operator
       if (state_map[yyPeek()] == MY_LEX_CMP_OP ||
 	  state_map[yyPeek()] == MY_LEX_LONG_CMP_OP)
 	yySkip();
-      if ((tokval = find_keyword(lex,(uint) (lex->ptr - lex->tok_start),0)))
+      if ((tokval = find_keyword(lip,(uint) (lip->ptr - lip->tok_start),0)))
       {
-	lex->next_state= MY_LEX_START;	// Allow signed numbers
+	lip->next_state= MY_LEX_START;	// Allow signed numbers
 	return(tokval);
       }
       state = MY_LEX_CHAR;		// Something fishy found
@@ -877,9 +893,9 @@ int MYSQLlex(void *arg, void *yythd)
 	if (state_map[yyPeek()] == MY_LEX_CMP_OP)
 	  yySkip();
       }
-      if ((tokval = find_keyword(lex,(uint) (lex->ptr - lex->tok_start),0)))
+      if ((tokval = find_keyword(lip,(uint) (lip->ptr - lip->tok_start),0)))
       {
-	lex->next_state= MY_LEX_START;	// Found long op
+	lip->next_state= MY_LEX_START;	// Found long op
 	return(tokval);
       }
       state = MY_LEX_CHAR;		// Something fishy found
@@ -892,24 +908,24 @@ int MYSQLlex(void *arg, void *yythd)
 	break;
       }
       yySkip();
-      tokval = find_keyword(lex,2,0);	// Is a bool operator
-      lex->next_state= MY_LEX_START;	// Allow signed numbers
+      tokval = find_keyword(lip,2,0);	// Is a bool operator
+      lip->next_state= MY_LEX_START;	// Allow signed numbers
       return(tokval);
 
     case MY_LEX_STRING_OR_DELIMITER:
-      if (((THD *) yythd)->variables.sql_mode & MODE_ANSI_QUOTES)
+      if (thd->variables.sql_mode & MODE_ANSI_QUOTES)
       {
 	state= MY_LEX_USER_VARIABLE_DELIMITER;
 	break;
       }
       /* " used for strings */
     case MY_LEX_STRING:			// Incomplete text string
-      if (!(yylval->lex_str.str = get_text(lex)))
+      if (!(yylval->lex_str.str = get_text(lip)))
       {
 	state= MY_LEX_CHAR;		// Read char by char
 	break;
       }
-      yylval->lex_str.length=lex->yytoklen;
+      yylval->lex_str.length=lip->yytoklen;
       return(TEXT_STRING);
 
     case MY_LEX_COMMENT:			//  Comment
@@ -933,7 +949,7 @@ int MYSQLlex(void *arg, void *yythd)
 	state=MY_LEX_START;
 	if (my_isdigit(cs,yyPeek()))
 	{				// Version number
-	  version=strtol((char*) lex->ptr,(char**) &lex->ptr,10);
+	  version=strtol((char*) lip->ptr,(char**) &lip->ptr,10);
 	}
 	if (version <= MYSQL_VERSION_ID)
 	{
@@ -941,13 +957,13 @@ int MYSQLlex(void *arg, void *yythd)
 	  break;
 	}
       }
-      while (lex->ptr != lex->end_of_query &&
+      while (lip->ptr != lip->end_of_query &&
 	     ((c=yyGet()) != '*' || yyPeek() != '/'))
       {
 	if (c == '\n')
-	  lex->yylineno++;
+	  lip->yylineno++;
       }
-      if (lex->ptr != lex->end_of_query)
+      if (lip->ptr != lip->end_of_query)
 	yySkip();			// remove last '/'
       state = MY_LEX_START;		// Try again
       break;
@@ -972,14 +988,13 @@ int MYSQLlex(void *arg, void *yythd)
     case MY_LEX_SEMICOLON:			// optional line terminator
       if (yyPeek())
       {
-        THD* thd= (THD*)yythd;
         if ((thd->client_capabilities & CLIENT_MULTI_STATEMENTS) && 
-            !lex->stmt_prepare_mode)
+            !lip->stmt_prepare_mode)
         {
 	  lex->safe_to_cache_query= 0;
-          lex->found_semicolon=(char*) lex->ptr;
+          lip->found_semicolon= lip->ptr;
           thd->server_status|= SERVER_MORE_RESULTS_EXISTS;
-          lex->next_state=     MY_LEX_END;
+          lip->next_state=     MY_LEX_END;
           return (END_OF_INPUT);
         }
         state= MY_LEX_CHAR;		// Return ';'
@@ -987,15 +1002,15 @@ int MYSQLlex(void *arg, void *yythd)
       }
       /* fall true */
     case MY_LEX_EOL:
-      if (lex->ptr >= lex->end_of_query)
+      if (lip->ptr >= lip->end_of_query)
       {
-	lex->next_state=MY_LEX_END;	// Mark for next loop
+	lip->next_state=MY_LEX_END;	// Mark for next loop
 	return(END_OF_INPUT);
       }
       state=MY_LEX_CHAR;
       break;
     case MY_LEX_END:
-      lex->next_state=MY_LEX_END;
+      lip->next_state=MY_LEX_END;
       return(0);			// We found end of input last time
       
       /* Actually real shouldn't start with . but allow them anyhow */
@@ -1015,26 +1030,26 @@ int MYSQLlex(void *arg, void *yythd)
       case MY_LEX_STRING_OR_DELIMITER:
 	break;
       case MY_LEX_USER_END:
-	lex->next_state=MY_LEX_SYSTEM_VAR;
+	lip->next_state=MY_LEX_SYSTEM_VAR;
 	break;
       default:
-	lex->next_state=MY_LEX_HOSTNAME;
+	lip->next_state=MY_LEX_HOSTNAME;
 	break;
       }
-      yylval->lex_str.str=(char*) lex->ptr;
+      yylval->lex_str.str=(char*) lip->ptr;
       yylval->lex_str.length=1;
       return((int) '@');
     case MY_LEX_HOSTNAME:		// end '@' of user@hostname
       for (c=yyGet() ; 
 	   my_isalnum(cs,c) || c == '.' || c == '_' ||  c == '$';
 	   c= yyGet()) ;
-      yylval->lex_str=get_token(lex,yyLength());
+      yylval->lex_str=get_token(lip, 0, yyLength());
       return(LEX_HOSTNAME);
     case MY_LEX_SYSTEM_VAR:
-      yylval->lex_str.str=(char*) lex->ptr;
+      yylval->lex_str.str=(char*) lip->ptr;
       yylval->lex_str.length=1;
       yySkip();					// Skip '@'
-      lex->next_state= (state_map[yyPeek()] ==
+      lip->next_state= (state_map[yyPeek()] ==
 			MY_LEX_USER_VARIABLE_DELIMITER ?
 			MY_LEX_OPERATOR_OR_IDENT :
 			MY_LEX_IDENT_OR_KEYWORD);
@@ -1051,16 +1066,16 @@ int MYSQLlex(void *arg, void *yythd)
       result_state= result_state & 0x80 ? IDENT_QUOTED : IDENT;
       
       if (c == '.')
-	lex->next_state=MY_LEX_IDENT_SEP;
-      length= (uint) (lex->ptr - lex->tok_start)-1;
+	lip->next_state=MY_LEX_IDENT_SEP;
+      length= (uint) (lip->ptr - lip->tok_start)-1;
       if (length == 0) 
         return(ABORT_SYM);              // Names must be nonempty.
-      if ((tokval= find_keyword(lex,length,0)))
+      if ((tokval= find_keyword(lip, length,0)))
       {
 	yyUnget();				// Put back 'c'
 	return(tokval);				// Was keyword
       }
-      yylval->lex_str=get_token(lex,length);
+      yylval->lex_str=get_token(lip, 0, length);
       return(result_state);
     }
   }
@@ -1093,7 +1108,7 @@ Alter_info::Alter_info(const Alter_info &rhs, MEM_ROOT *mem_root)
     Pointer to the last non-comment symbol of the statement.
 */
 
-uchar *skip_rear_comments(uchar *begin, uchar *end)
+char *skip_rear_comments(char *begin, char *end)
 {
   while (begin < end && (end[-1] <= ' ' || end[-1] == '*' ||
                          end[-1] == '/' || end[-1] == ';'))
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 2ff29d684d1..6c9283126c4 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -470,7 +470,7 @@ public:
   void set_thd(THD *thd_arg) { thd= thd_arg; }
   inline bool is_union (); 
 
-  friend void lex_start(THD *thd, uchar *buf, uint length);
+  friend void lex_start(THD *thd);
   friend int subselect_union_engine::exec();
 
   List<Item> *get_unit_column_types();
@@ -676,7 +676,7 @@ public:
   void cut_subtree() { slave= 0; }
   bool test_limit();
 
-  friend void lex_start(THD *thd, uchar *buf, uint length);
+  friend void lex_start(THD *thd);
   st_select_lex() : n_sum_items(0), n_child_sum_items(0) {}
   void make_empty_select()
   {
@@ -886,6 +886,12 @@ public:
       query_tables_own_last= 0;
     }
   }
+  /**
+    true if the parsed tree contains references to stored procedures
+    or functions, false otherwise
+  */
+  bool uses_stored_routines() const
+  { return sroutines_list.elements != 0; }
 };
 
 
@@ -906,30 +912,78 @@ struct st_parsing_options
 };
 
 
+/**
+  This class represents the character input stream consumed during
+  lexical analysis.
+*/
+class Lex_input_stream
+{
+public:
+  Lex_input_stream(THD *thd, const char* buff, unsigned int length);
+  ~Lex_input_stream();
+
+  /** Current thread. */
+  THD *m_thd;
+
+  /** Current line number. */
+  uint yylineno;
+
+  /** Length of the last token parsed. */
+  uint yytoklen;
+
+  /** Interface with bison, value of the last token parsed. */
+  LEX_YYSTYPE yylval;
+
+  /** Pointer to the current position in the input stream. */
+  const char* ptr;
+
+  /** Starting position of the last token parsed. */
+  const char* tok_start;
+
+  /** Ending position of the last token parsed. */
+  const char* tok_end;
+
+  /** End of the query text in the input stream. */
+  const char* end_of_query;
+
+  /** Starting position of the previous token parsed. */
+  const char* tok_start_prev;
+
+  /** Begining of the query text in the input stream. */
+  const char* buf;
+
+  /** Current state of the lexical analyser. */
+  enum my_lex_states next_state;
+
+  /** Position of ';' in the stream, to delimit multiple queries. */
+  const char* found_semicolon;
+
+  /** SQL_MODE = IGNORE_SPACE. */
+  bool ignore_space;
+  /*
+    TRUE if we're parsing a prepared statement: in this mode
+    we should allow placeholders and disallow multi-statements.
+  */
+  bool stmt_prepare_mode;
+};
+
+
 /* The state of the lex parsing. This is saved in the THD struct */
 
 typedef struct st_lex : public Query_tables_list
 {
-  uint	 yylineno,yytoklen;			/* Simulate lex */
-  LEX_YYSTYPE yylval;
   SELECT_LEX_UNIT unit;                         /* most upper unit */
   SELECT_LEX select_lex;                        /* first SELECT_LEX */
   /* current SELECT_LEX in parsing */
   SELECT_LEX *current_select;
   /* list of all SELECT_LEX */
   SELECT_LEX *all_selects_list;
-  uchar *buf;			/* The beginning of string, used by SPs */
-  uchar *ptr,*tok_start,*tok_end,*end_of_query;
-  
-  /* The values of tok_start/tok_end as they were one call of MYSQLlex before */
-  uchar *tok_start_prev, *tok_end_prev;
 
   char *length,*dec,*change,*name;
   char *help_arg;
   char *backup_dir;				/* For RESTORE/BACKUP */
   char* to_log;                                 /* For PURGE MASTER LOGS TO */
   char* x509_subject,*x509_issuer,*ssl_cipher;
-  char* found_semicolon;                        /* For multi queries - next query */
   String *wild;
   sql_exchange *exchange;
   select_result *result;
@@ -998,7 +1052,6 @@ typedef struct st_lex : public Query_tables_list
   enum_sql_command sql_command, orig_sql_command;
   thr_lock_type lock_option;
   enum SSL_type ssl_type;			/* defined in violite.h */
-  enum my_lex_states next_state;
   enum enum_duplicates duplicates;
   enum enum_tx_isolation tx_isolation;
   enum enum_ha_read_modes ha_read_mode;
@@ -1030,7 +1083,7 @@ typedef struct st_lex : public Query_tables_list
   uint8 create_view_algorithm;
   uint8 create_view_check;
   bool drop_if_exists, drop_temporary, local_file, one_shot_set;
-  bool in_comment, ignore_space, verbose, no_write_to_binlog;
+  bool in_comment, verbose, no_write_to_binlog;
   bool tx_chain, tx_release;
   /*
     Special JOIN::prepare mode: changing of query is prohibited.
@@ -1040,11 +1093,6 @@ typedef struct st_lex : public Query_tables_list
     to an .frm file. We need this definition to stay untouched.
   */
   bool view_prepare_mode;
-  /*
-    TRUE if we're parsing a prepared statement: in this mode
-    we should allow placeholders and disallow multistatements.
-  */
-  bool stmt_prepare_mode;
   bool safe_to_cache_query;
   bool subqueries, ignore;
   st_parsing_options parsing_options;
@@ -1109,8 +1157,9 @@ typedef struct st_lex : public Query_tables_list
     Pointers to part of LOAD DATA statement that should be rewritten
     during replication ("LOCAL 'filename' REPLACE INTO" part).
   */
-  uchar *fname_start, *fname_end;
-  
+  const char *fname_start;
+  const char *fname_end;
+
   bool escape_used;
 
   st_lex();
@@ -1219,7 +1268,7 @@ struct st_lex_local: public st_lex
 
 extern void lex_init(void);
 extern void lex_free(void);
-extern void lex_start(THD *thd, uchar *buf,uint length);
+extern void lex_start(THD *thd);
 extern void lex_end(LEX *lex);
 extern int MYSQLlex(void *arg, void *yythd);
-extern uchar *skip_rear_comments(uchar *begin, uchar *end);
+extern char *skip_rear_comments(char *begin, char *end);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 3ca0c78d96a..7e58b36a939 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1238,6 +1238,7 @@ pthread_handler_t handle_bootstrap(void *arg)
   THD *thd=(THD*) arg;
   FILE *file=bootstrap_file;
   char *buff;
+  const char* found_semicolon= NULL;
 
   /* The following must be called before DBUG_ENTER */
   thd->thread_stack= (char*) &thd;
@@ -1314,7 +1315,7 @@ pthread_handler_t handle_bootstrap(void *arg)
     */
     thd->query_id=next_query_id();
     thd->set_time();
-    mysql_parse(thd,thd->query,length);
+    mysql_parse(thd, thd->query, length, & found_semicolon);
     close_thread_tables(thd);			// Free tables
 
     if (thd->is_fatal_error)
@@ -1793,17 +1794,19 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     char *packet_end= thd->query + thd->query_length;
     /* 'b' stands for 'buffer' parameter', special for 'my_snprintf' */
     const char *format= "%.*b";
+    const char* found_semicolon= NULL;
+
     mysql_log.write(thd,command, format, thd->query_length, thd->query);
     DBUG_PRINT("query",("%-.4096s",thd->query));
 
     if (!(specialflag & SPECIAL_NO_PRIOR))
       my_pthread_setprio(pthread_self(),QUERY_PRIOR);
 
-    mysql_parse(thd,thd->query, thd->query_length);
+    mysql_parse(thd, thd->query, thd->query_length, & found_semicolon);
 
-    while (!thd->killed && thd->lex->found_semicolon && !thd->net.report_error)
+    while (!thd->killed && found_semicolon && !thd->net.report_error)
     {
-      char *next_packet= thd->lex->found_semicolon;
+      char *next_packet= (char*) found_semicolon;
       net->no_send_error= 0;
       /*
         Multiple queries exits, execute them individually
@@ -1828,7 +1831,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
       thd->set_time(); /* Reset the query start time. */
       /* TODO: set thd->lex->sql_command to SQLCOM_END here */
       VOID(pthread_mutex_unlock(&LOCK_thread_count));
-      mysql_parse(thd, next_packet, length);
+      mysql_parse(thd, next_packet, length, & found_semicolon);
     }
 
     if (!(specialflag & SPECIAL_NO_PRIOR))
@@ -1849,7 +1852,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     LEX_STRING conv_name;
 
     /* used as fields initializator */
-    lex_start(thd, 0, 0);
+    lex_start(thd);
 
     statistic_increment(thd->status_var.com_stat[SQLCOM_SHOW_FIELDS],
 			&LOCK_status);
@@ -1886,7 +1889,10 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
       break;
     /* init structures for VIEW processing */
     table_list.select_lex= &(thd->lex->select_lex);
-    mysql_init_query(thd, (uchar*)"", 0);
+
+    lex_start(thd);
+    mysql_reset_thd_for_next_command(thd);
+
     thd->lex->
       select_lex.table_list.link_in_list((byte*) &table_list,
                                          (byte**) &table_list.next_local);
@@ -3008,7 +3014,13 @@ mysql_execute_command(THD *thd)
       select_lex->options|= SELECT_NO_UNLOCK;
       unit->set_limit(select_lex);
 
-      if (!(res= open_and_lock_tables(thd, select_tables)))
+      if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        lex->link_first_table_back(create_table, link_to_local);
+        create_table->create= TRUE;
+      }
+
+      if (!(res= open_and_lock_tables(thd, lex->query_tables)))
       {
         /*
           Is table which we are changing used somewhere in other parts
@@ -3017,6 +3029,7 @@ mysql_execute_command(THD *thd)
         if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE))
         {
           TABLE_LIST *duplicate;
+          create_table= lex->unlink_first_table(&link_to_local);
           if ((duplicate= unique_table(thd, create_table, select_tables, 0)))
           {
             update_non_unique_table_error(create_table, "CREATE", duplicate);
@@ -3060,6 +3073,9 @@ mysql_execute_command(THD *thd)
           delete sel_result;
         }
       }
+      else if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+        create_table= lex->unlink_first_table(&link_to_local);
+
     }
     else
     {
@@ -5775,20 +5791,6 @@ bool my_yyoverflow(short **yyss, YYSTYPE **yyvs, ulong *yystacksize)
 }
 
 
-/****************************************************************************
-  Initialize global thd variables needed for query
-****************************************************************************/
-
-void
-mysql_init_query(THD *thd, uchar *buf, uint length)
-{
-  DBUG_ENTER("mysql_init_query");
-  lex_start(thd, buf, length);
-  mysql_reset_thd_for_next_command(thd);
-  DBUG_VOID_RETURN;
-}
-
-
 /*
  Reset THD part responsible for command processing state.
 
@@ -5975,21 +5977,55 @@ void mysql_init_multi_delete(LEX *lex)
   mysql_test_parse_for_slave() in this same file.
 */
 
-void mysql_parse(THD *thd, char *inBuf, uint length)
+/**
+  Parse a query.
+  @param thd Current thread
+  @param inBuf Begining of the query text
+  @param length Length of the query text
+  @param [out] semicolon For multi queries, position of the character of
+  the next query in the query text.
+*/
+
+void mysql_parse(THD *thd, const char *inBuf, uint length,
+                 const char ** found_semicolon)
 {
   DBUG_ENTER("mysql_parse");
 
   DBUG_EXECUTE_IF("parser_debug", turn_parser_debug_on(););
 
-  mysql_init_query(thd, (uchar*) inBuf, length);
-  if (query_cache_send_result_to_client(thd, inBuf, length) <= 0)
+  /*
+    Warning.
+    The purpose of query_cache_send_result_to_client() is to lookup the
+    query in the query cache first, to avoid parsing and executing it.
+    So, the natural implementation would be to:
+    - first, call query_cache_send_result_to_client,
+    - second, if caching failed, initialise the lexical and syntactic parser.
+    The problem is that the query cache depends on a clean initialization
+    of (among others) lex->safe_to_cache_query and thd->server_status,
+    which are reset respectively in
+    - lex_start()
+    - mysql_reset_thd_for_next_command()
+    So, initializing the lexical analyser *before* using the query cache
+    is required for the cache to work properly.
+    FIXME: cleanup the dependencies in the code to simplify this.
+  */
+  lex_start(thd);
+  mysql_reset_thd_for_next_command(thd);
+
+  if (query_cache_send_result_to_client(thd, (char*) inBuf, length) <= 0)
   {
     LEX *lex= thd->lex;
-    
+
     sp_cache_flush_obsolete(&thd->sp_proc_cache);
     sp_cache_flush_obsolete(&thd->sp_func_cache);
-    
-    if (!MYSQLparse((void *)thd) && ! thd->is_fatal_error)
+
+    Lex_input_stream lip(thd, inBuf, length);
+    thd->m_lip= &lip;
+
+    int err= MYSQLparse(thd);
+    *found_semicolon= lip.found_semicolon;
+
+    if (!err && ! thd->is_fatal_error)
     {
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
       if (mqh_used && thd->user_connect &&
@@ -6012,8 +6048,8 @@ void mysql_parse(THD *thd, char *inBuf, uint length)
             PROCESSLIST.
             Note that we don't need LOCK_thread_count to modify query_length.
           */
-          if (lex->found_semicolon &&
-              (thd->query_length= (ulong)(lex->found_semicolon - thd->query)))
+          if (lip.found_semicolon &&
+              (thd->query_length= (ulong)(lip.found_semicolon - thd->query)))
             thd->query_length--;
           /* Actually execute the query */
 	  mysql_execute_command(thd);
@@ -6040,6 +6076,12 @@ void mysql_parse(THD *thd, char *inBuf, uint length)
     thd->cleanup_after_query();
     DBUG_ASSERT(thd->change_list.is_empty());
   }
+  else
+  {
+    /* There are no multi queries in the cache. */
+    *found_semicolon= NULL;
+  }
+
   DBUG_VOID_RETURN;
 }
 
@@ -6060,8 +6102,13 @@ bool mysql_test_parse_for_slave(THD *thd, char *inBuf, uint length)
   bool error= 0;
   DBUG_ENTER("mysql_test_parse_for_slave");
 
-  mysql_init_query(thd, (uchar*) inBuf, length);
-  if (!MYSQLparse((void*) thd) && ! thd->is_fatal_error &&
+  Lex_input_stream lip(thd, inBuf, length);
+  thd->m_lip= &lip;
+  lex_start(thd);
+  mysql_reset_thd_for_next_command(thd);
+  int err= MYSQLparse((void*) thd);
+
+  if (!err && ! thd->is_fatal_error &&
       all_tables_not_ok(thd,(TABLE_LIST*) lex->select_lex.table_list.first))
     error= 1;                  /* Ignore question */
   thd->end_statement();
@@ -7120,8 +7167,9 @@ bool check_simple_select()
   if (lex->current_select != &lex->select_lex)
   {
     char command[80];
-    strmake(command, lex->yylval->symbol.str,
-	    min(lex->yylval->symbol.length, sizeof(command)-1));
+    Lex_input_stream *lip= thd->m_lip;
+    strmake(command, lip->yylval->symbol.str,
+	    min(lip->yylval->symbol.length, sizeof(command)-1));
     my_error(ER_CANT_USE_OPTION_HERE, MYF(0), command);
     return 1;
   }
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 1ec65743b0f..42655608196 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1164,8 +1164,9 @@ static int mysql_test_update(Prepared_statement *stmt,
     goto error;
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-  /* TABLE_LIST contain right privilages request */
-  want_privilege= table_list->grant.want_privilege;
+  /* Force privilege re-checking for views after they have been opened. */
+  want_privilege= (table_list->view ? UPDATE_ACL :
+                   table_list->grant.want_privilege);
 #endif
 
   if (mysql_prepare_update(thd, table_list, &select->where,
@@ -1491,8 +1492,21 @@ static bool mysql_test_create_table(Prepared_statement *stmt)
 
   if (select_lex->item_list.elements)
   {
+    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+    {
+      lex->link_first_table_back(create_table, link_to_local);
+      create_table->create= TRUE;
+    }
+
+    if (open_and_lock_tables(stmt->thd, lex->query_tables))
+      DBUG_RETURN(TRUE);
+
+    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+      create_table= lex->unlink_first_table(&link_to_local);
+
     select_lex->context.resolve_in_select_list= TRUE;
-    res= select_like_stmt_test_with_open_n_lock(stmt, tables, 0, 0);
+
+    res= select_like_stmt_test(stmt, 0, 0);
   }
 
   /* put tables back for PS rexecuting */
@@ -1762,6 +1776,9 @@ static bool check_prepared_statement(Prepared_statement *stmt,
   case SQLCOM_OPTIMIZE:
     break;
 
+  case SQLCOM_PREPARE:
+  case SQLCOM_EXECUTE:
+  case SQLCOM_DEALLOCATE_PREPARE:
   default:
     /* All other statements are not supported yet. */
     my_message(ER_UNSUPPORTED_PS, ER(ER_UNSUPPORTED_PS), MYF(0));
@@ -2799,11 +2816,15 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len)
 
   old_stmt_arena= thd->stmt_arena;
   thd->stmt_arena= this;
-  lex_start(thd, (uchar*) thd->query, thd->query_length);
+
+  Lex_input_stream lip(thd, thd->query, thd->query_length);
+  lip.stmt_prepare_mode= TRUE;
+  thd->m_lip= &lip;
+  lex_start(thd);
   lex->safe_to_cache_query= FALSE;
-  lex->stmt_prepare_mode= TRUE;
+  int err= MYSQLparse((void *)thd);
 
-  error= MYSQLparse((void *)thd) || thd->is_fatal_error ||
+  error= err || thd->is_fatal_error ||
       thd->net.report_error || init_param_array(this);
 
   /*
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3a480c01ac1..ea59cbbe9f2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -976,6 +976,12 @@ JOIN::optimize()
     }
   }
 
+  if (conds &&!outer_join && const_table_map != found_const_table_map && 
+      (select_options & SELECT_DESCRIBE) &&
+      select_lex->master_unit() == &thd->lex->unit) // upper level SELECT
+  {
+    conds=new Item_int((longlong) 0,1);	// Always false
+  }
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=
@@ -4189,7 +4195,7 @@ best_access_path(JOIN      *join,
       !(s->quick && best_key && s->quick->index == best_key->key &&      // (2)
         best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
       !((s->table->file->table_flags() & HA_TABLE_SCAN_ON_INDEX) &&      // (3)
-        ! s->table->used_keys.is_clear_all() && best_key) &&             // (3)
+        ! s->table->used_keys.is_clear_all() && best_key && !s->quick) &&// (3)
       !(s->table->force_index && best_key && !s->quick))                 // (4)
   {                                             // Check full join
     ha_rows rnd_records= s->found_records;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 5081366c10b..3cdd265df9a 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -159,6 +159,13 @@ typedef struct st_join_table {
   Read_record_func read_first_record;
   Next_select_func next_select;
   READ_RECORD	read_record;
+  /* 
+    Currently the following two fields are used only for a [NOT] IN subquery
+    if it is executed by an alternative full table scan when the left operand of
+    the subquery predicate is evaluated to NULL.
+  */  
+  Read_record_func save_read_first_record;/* to save read_first_record */ 
+  int (*save_read_record) (READ_RECORD *);/* to save read_record.read_record */
   double	worst_seeks;
   key_map	const_keys;			/* Keys with constant part */
   key_map	checked_keys;			/* Keys checked in find_best */
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 42d59a10712..079cc0d6456 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -62,8 +62,8 @@ static void set_tmp_file_path(char *buf, size_t bufsize, THD *thd);
     #                   Size of path
  */
 
-static uint build_table_path(char *buff, size_t bufflen, const char *db,
-                             const char *table, const char *ext)
+uint build_table_path(char *buff, size_t bufflen, const char *db,
+                      const char *table, const char *ext)
 {
   strxnmov(buff, bufflen-1, mysql_data_home, "/", db, "/", table, ext,
            NullS);
@@ -1722,7 +1722,14 @@ bool mysql_create_table(THD *thd,const char *db, const char *table_name,
   VOID(pthread_mutex_lock(&LOCK_open));
   if (!internal_tmp_table && !(create_info->options & HA_LEX_CREATE_TMP_TABLE))
   {
-    if (!access(path,F_OK))
+    /*
+      Inspecting table cache for placeholders created by concurrent
+      CREATE TABLE ... SELECT statements to avoid interfering with them
+      is 5.0-only solution. Starting from 5.1 we solve this problem by
+      obtaining name-lock on the table to be created first.
+    */
+    if (table_cache_has_open_placeholder(thd, db, table_name) ||
+        !access(path, F_OK))
     {
       if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
         goto warn;
@@ -2051,7 +2058,7 @@ static int prepare_for_restore(THD* thd, TABLE_LIST* table,
     to finish the restore in the handler later on
   */
   pthread_mutex_lock(&LOCK_open);
-  if (reopen_name_locked_table(thd, table))
+  if (reopen_name_locked_table(thd, table, TRUE))
   {
     unlock_table_name(thd, table);
     pthread_mutex_unlock(&LOCK_open);
@@ -2158,7 +2165,7 @@ static int prepare_for_repair(THD* thd, TABLE_LIST *table_list,
     to finish the repair in the handler later on.
   */
   pthread_mutex_lock(&LOCK_open);
-  if (reopen_name_locked_table(thd, table_list))
+  if (reopen_name_locked_table(thd, table_list, TRUE))
   {
     unlock_table_name(thd, table_list);
     pthread_mutex_unlock(&LOCK_open);
@@ -2803,10 +2810,24 @@ bool mysql_create_like_table(THD* thd, TABLE_LIST* table,
   }
   else
   {
+    bool exists;
     strxmov(dst_path, mysql_data_home, "/", db, "/", table_name,
 	    reg_ext, NullS);
     fn_format(dst_path, dst_path, "", "", MYF(MY_UNPACK_FILENAME));
-    if (!access(dst_path, F_OK))
+
+    /*
+      Note that this critical section should actually cover most
+      of mysql_create_like_table() function. See bugs #18950 and
+      #23667 for more information.
+      Also note that starting from 5.1 we obtain name-lock on
+      target table instead of inspecting table cache for presence
+      of open placeholders (see comment in mysql_create_table()).
+    */
+    pthread_mutex_lock(&LOCK_open);
+    exists= (table_cache_has_open_placeholder(thd, db, table_name) ||
+             !access(dst_path, F_OK));
+    pthread_mutex_unlock(&LOCK_open);
+    if (exists)
       goto table_exists;
   }
 
@@ -3160,9 +3181,14 @@ view_err:
       else
       {
 	char dir_buff[FN_REFLEN];
+        bool exists;
 	strxnmov(dir_buff, FN_REFLEN, mysql_real_data_home, new_db, NullS);
-	if (!access(fn_format(new_name_buff,new_name_buff,dir_buff,reg_ext,0),
-		    F_OK))
+        VOID(pthread_mutex_lock(&LOCK_open));
+        exists= (table_cache_has_open_placeholder(thd, new_db, new_name) ||
+                 !access(fn_format(new_name_buff, new_name_buff, dir_buff,
+                                   reg_ext, 0), F_OK));
+        VOID(pthread_mutex_unlock(&LOCK_open));
+        if (exists)
 	{
 	  /* Table will be closed in do_command() */
 	  my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
@@ -3247,8 +3273,22 @@ view_err:
     if (!error && (new_name != table_name || new_db != db))
     {
       thd->proc_info="rename";
-      /* Then do a 'simple' rename of the table */
-      if (!access(new_name_buff,F_OK))
+      /*
+        Then do a 'simple' rename of the table. First we need to close all
+        instances of 'source' table.
+      */
+      close_cached_table(thd, table);
+      /*
+        Then, we want check once again that target table does not exist.
+        Note that we can't fully rely on results of previous check since
+        no lock was taken on target table during it. We also can't do this
+        before calling close_cached_table() as the latter temporarily
+        releases LOCK_open mutex.
+        Also note that starting from 5.1 we use approach with obtaining
+        of name-lock on target table.
+      */
+      if (table_cache_has_open_placeholder(thd, new_db, new_name) ||
+          !access(new_name_buff,F_OK))
       {
 	my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name);
 	error= -1;
@@ -3256,7 +3296,6 @@ view_err:
       else
       {
 	*fn_ext(new_name)=0;
-	close_cached_table(thd, table);
 	if (mysql_rename_table(old_db_type,db,table_name,new_db,new_alias))
 	  error= -1;
         else if (Table_triggers_list::change_table_name(thd, db, table_name,
@@ -3806,17 +3845,6 @@ view_err:
 	      current_pid, thd->thread_id);
   if (lower_case_table_names)
     my_casedn_str(files_charset_info, old_name);
-  if (new_name != table_name || new_db != db)
-  {
-    if (!access(new_name_buff,F_OK))
-    {
-      error=1;
-      my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name_buff);
-      VOID(quick_rm_table(new_db_type,new_db,tmp_name));
-      VOID(pthread_mutex_unlock(&LOCK_open));
-      goto err;
-    }
-  }
 
 #if (!defined( __WIN__) && !defined( __EMX__) && !defined( OS2))
   if (table->file->has_transactions())
@@ -3835,6 +3863,22 @@ view_err:
     table->file->extra(HA_EXTRA_FORCE_REOPEN);	// Don't use this file anymore
 #endif
 
+  if (new_name != table_name || new_db != db)
+  {
+    /*
+      Check that there is no table with target name. See the
+      comment describing code for 'simple' ALTER TABLE ... RENAME.
+    */
+    if (table_cache_has_open_placeholder(thd, new_db, new_name) ||
+        !access(new_name_buff,F_OK))
+    {
+      error=1;
+      my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name_buff);
+      VOID(quick_rm_table(new_db_type,new_db,tmp_name));
+      VOID(pthread_mutex_unlock(&LOCK_open));
+      goto err;
+    }
+  }
 
   error=0;
   if (!need_copy_table)
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 55d51ad07b7..cf6db22fbcb 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -271,7 +271,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
   /* We also don't allow creation of triggers on views. */
   tables->required_type= FRMTYPE_TABLE;
 
-  if (reopen_name_locked_table(thd, tables))
+  if (reopen_name_locked_table(thd, tables, TRUE))
   {
     unlock_table_name(thd, tables);
     goto end;
@@ -978,10 +978,14 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
         LEX_STRING *trg_definer= it_definer++;
 
         thd->variables.sql_mode= (ulong)*trg_sql_mode;
-        lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length);
 
+        Lex_input_stream lip(thd, trg_create_str->str, trg_create_str->length);
+        thd->m_lip= &lip;
+        lex_start(thd);
 	thd->spcont= 0;
-        if (MYSQLparse((void *)thd) || thd->is_fatal_error)
+        int err= MYSQLparse((void *)thd);
+
+        if (err || thd->is_fatal_error)
         {
           /* Currently sphead is always deleted in case of a parse error */
           DBUG_ASSERT(lex.sphead == 0);
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7fb75732012..373b03d45e6 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -147,8 +147,16 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg)
   fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
 					   (byte **)
 					   &result_table_list.next_local);
-  fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table= 
+  fake_select_lex->context.table_list= 
+    fake_select_lex->context.first_name_resolution_table= 
     fake_select_lex->get_table_list();
+  if (!fake_select_lex->first_execution)
+  {
+    for (ORDER *order= (ORDER *) global_parameters->order_list.first;
+         order;
+         order= order->next)
+      order->item= &order->item_ptr;
+  }
   for (ORDER *order= (ORDER *)global_parameters->order_list.first;
        order;
        order=order->next)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index e17c71ae541..222e33345cc 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -173,8 +173,9 @@ int mysql_update(THD *thd,
   table->quick_keys.clear_all();
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-  /* TABLE_LIST contain right privilages request */
-  want_privilege= table_list->grant.want_privilege;
+  /* Force privilege re-checking for views after they have been opened. */
+  want_privilege= (table_list->view ? UPDATE_ACL :
+                   table_list->grant.want_privilege);
 #endif
   if (mysql_prepare_update(thd, table_list, &conds, order_num, order))
     DBUG_RETURN(1);
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index d717aea9a3e..ba367040b36 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -772,8 +772,8 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view,
   view->query.str= (char*)str.ptr();
   view->query.length= str.length()-1; // we do not need last \0
   view->source.str= thd->query + thd->lex->create_view_select_start;
-  view->source.length= (char *)skip_rear_comments((uchar *)view->source.str,
-                                                  (uchar *)thd->query +
+  view->source.length= (char *)skip_rear_comments((char *)view->source.str,
+                                                  (char *)thd->query +
                                                   thd->query_length) -
                         view->source.str;
   view->file_version= 1;
@@ -984,10 +984,14 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
     now Lex placed in statement memory
   */
   table->view= lex= thd->lex= (LEX*) new(thd->mem_root) st_lex_local;
-  lex_start(thd, (uchar*)table->query.str, table->query.length);
-  view_select= &lex->select_lex;
-  view_select->select_number= ++thd->select_number;
+
   {
+    Lex_input_stream lip(thd, table->query.str, table->query.length);
+    thd->m_lip= &lip;
+    lex_start(thd);
+    view_select= &lex->select_lex;
+    view_select->select_number= ++thd->select_number;
+
     ulong save_mode= thd->variables.sql_mode;
     /* switch off modes which can prevent normal parsing of VIEW
       - MODE_REAL_AS_FLOAT            affect only CREATE TABLE parsing
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 4a50a602121..c1da870960b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -86,12 +86,13 @@ const LEX_STRING null_lex_str={0,0};
 void my_parse_error(const char *s)
 {
   THD *thd= current_thd;
+  Lex_input_stream *lip= thd->m_lip;
 
-  char *yytext= (char*) thd->lex->tok_start;
+  const char *yytext= lip->tok_start;
   /* Push an error into the error stack */
   my_printf_error(ER_PARSE_ERROR,  ER(ER_PARSE_ERROR), MYF(0), s,
-                  (yytext ? (char*) yytext : ""),
-                  thd->lex->yylineno);
+                  (yytext ? yytext : ""),
+                  lip->yylineno);
 }
 
 /**
@@ -1275,11 +1276,6 @@ deallocate:
         {
           THD *thd=YYTHD;
           LEX *lex= thd->lex;
-          if (lex->stmt_prepare_mode)
-          {
-            my_parse_error(ER(ER_SYNTAX_ERROR));
-            MYSQL_YYABORT;
-          }
           lex->sql_command= SQLCOM_DEALLOCATE_PREPARE;
           lex->prepared_stmt_name= $3;
         };
@@ -1295,11 +1291,6 @@ prepare:
         {
           THD *thd=YYTHD;
           LEX *lex= thd->lex;
-          if (lex->stmt_prepare_mode)
-          {
-            my_parse_error(ER(ER_SYNTAX_ERROR));
-            MYSQL_YYABORT;
-          }
           lex->sql_command= SQLCOM_PREPARE;
           lex->prepared_stmt_name= $2;
         };
@@ -1325,11 +1316,6 @@ execute:
         {
           THD *thd=YYTHD;
           LEX *lex= thd->lex;
-          if (lex->stmt_prepare_mode)
-          {
-            my_parse_error(ER(ER_SYNTAX_ERROR));
-            MYSQL_YYABORT;
-          }
           lex->sql_command= SQLCOM_EXECUTE;
           lex->prepared_stmt_name= $2;
         }
@@ -1488,9 +1474,7 @@ create:
 	  lex->sql_command= SQLCOM_CREATE_TABLE;
 	  if (!lex->select_lex.add_table_to_list(thd, $5, NULL,
 						 TL_OPTION_UPDATING,
-						 (using_update_log ?
-						  TL_READ_NO_INSERT:
-						  TL_READ)))
+						 TL_WRITE))
 	    MYSQL_YYABORT;
           lex->alter_info.reset();
 	  lex->col_list.empty();
@@ -1619,7 +1603,9 @@ create_function_tail:
 	  }
 	| '('
 	  {
-	    LEX *lex= Lex;
+            THD *thd= YYTHD;
+	    LEX *lex= thd->lex;
+            Lex_input_stream *lip= thd->m_lip;
 	    sp_head *sp;
 
             /* 
@@ -1639,9 +1625,9 @@ create_function_tail:
 	    }
 	    /* Order is important here: new - reset - init */
 	    sp= new sp_head();
-	    sp->reset_thd_mem_root(YYTHD);
+	    sp->reset_thd_mem_root(thd);
 	    sp->init(lex);
-            sp->init_sp_name(YYTHD, lex->spname);
+            sp->init_sp_name(thd, lex->spname);
 
 	    sp->m_type= TYPE_ENUM_FUNCTION;
 	    lex->sphead= sp;
@@ -1650,15 +1636,17 @@ create_function_tail:
 	     * stored procedure, otherwise yylex will chop it into pieces
 	     * at each ';'.
 	     */
-	    sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES;
-	    YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES;
-	    lex->sphead->m_param_begin= lex->tok_start+1;
+	    sp->m_old_cmq= thd->client_capabilities & CLIENT_MULTI_QUERIES;
+	    thd->client_capabilities &= ~CLIENT_MULTI_QUERIES;
+	    lex->sphead->m_param_begin= lip->tok_start+1;
 	  }
           sp_fdparam_list ')'
 	  {
-	    LEX *lex= Lex;
+            THD *thd= YYTHD;
+	    LEX *lex= thd->lex;
+            Lex_input_stream *lip= thd->m_lip;
 
-	    lex->sphead->m_param_end= lex->tok_start;
+	    lex->sphead->m_param_end= lip->tok_start;
 	  }
 	  RETURNS_SYM
 	  {
@@ -1682,10 +1670,12 @@ create_function_tail:
 	  }
 	  sp_c_chistics
 	  {
-	    LEX *lex= Lex;
+            THD *thd= YYTHD;
+	    LEX *lex= thd->lex;
+            Lex_input_stream *lip= thd->m_lip;
 
 	    lex->sphead->m_chistics= &lex->sp_chistics;
-	    lex->sphead->m_body_begin= lex->tok_start;
+	    lex->sphead->m_body_begin= lip->tok_start;
 	  }
 	  sp_proc_stmt
 	  {
@@ -2233,14 +2223,18 @@ sp_opt_default:
 
 sp_proc_stmt:
 	  {
-	    LEX *lex= Lex;
+            THD *thd= YYTHD;
+	    LEX *lex= thd->lex;
+            Lex_input_stream *lip= thd->m_lip;
 
-	    lex->sphead->reset_lex(YYTHD);
-	    lex->sphead->m_tmp_query= lex->tok_start;
+	    lex->sphead->reset_lex(thd);
+	    lex->sphead->m_tmp_query= lip->tok_start;
 	  }
 	  statement
 	  {
-	    LEX *lex= Lex;
+            THD *thd= YYTHD;
+	    LEX *lex= thd->lex;
+            Lex_input_stream *lip= thd->m_lip;
 	    sp_head *sp= lex->sphead;
 
             sp->m_flags|= sp_get_flags_for_command(lex);
@@ -2267,15 +2261,15 @@ sp_proc_stmt:
                 lex->tok_end otherwise.
               */
               if (yychar == YYEMPTY)
-                i->m_query.length= lex->ptr - sp->m_tmp_query;
+                i->m_query.length= lip->ptr - sp->m_tmp_query;
               else
-                i->m_query.length= lex->tok_end - sp->m_tmp_query;
-              i->m_query.str= strmake_root(YYTHD->mem_root,
-                                           (char *)sp->m_tmp_query,
+                i->m_query.length= lip->tok_end - sp->m_tmp_query;
+              i->m_query.str= strmake_root(thd->mem_root,
+                                           sp->m_tmp_query,
                                            i->m_query.length);
               sp->add_instr(i);
             }
-	    sp->restore_lex(YYTHD);
+	    sp->restore_lex(thd);
           }
           | RETURN_SYM 
           { Lex->sphead->reset_lex(YYTHD); }
@@ -4428,26 +4422,36 @@ select_item_list:
 select_item:
 	  remember_name select_item2 remember_end select_alias
 	  {
-	    if (add_item_to_list(YYTHD, $2))
+            THD *thd= YYTHD;
+            DBUG_ASSERT($1 < $3);
+
+	    if (add_item_to_list(thd, $2))
 	      MYSQL_YYABORT;
 	    if ($4.str)
             {
               $2->is_autogenerated_name= FALSE;
 	      $2->set_name($4.str, $4.length, system_charset_info);
             }
-	    else if (!$2->name) {
-	      char *str = $1;
-	      if (str[-1] == '`')
-	        str--;
-	      $2->set_name(str,(uint) ($3 - str), YYTHD->charset());
+	    else if (!$2->name)
+            {
+	      $2->set_name($1, (uint) ($3 - $1), thd->charset());
 	    }
 	  };
 
+
 remember_name:
-	{ $$=(char*) Lex->tok_start; };
+	{
+          THD *thd= YYTHD;
+          Lex_input_stream *lip= thd->m_lip;
+          $$= (char*) lip->tok_start;
+        };
 
 remember_end:
-	{ $$=(char*) Lex->tok_end; };
+	{
+          THD *thd= YYTHD;
+          Lex_input_stream *lip= thd->m_lip;
+          $$=(char*) lip->tok_end;
+        };
 
 select_item2:
 	table_wild	{ $$=$1; } /* table.* */
@@ -4699,15 +4703,12 @@ simple_expr:
 	| ASCII_SYM '(' expr ')' { $$= new Item_func_ascii($3); }
 	| BINARY simple_expr %prec NEG
 	  {
-            $$= create_func_cast($2, ITEM_CAST_CHAR, -1, 0, &my_charset_bin);
+            $$= create_func_cast($2, ITEM_CAST_CHAR, NULL, NULL, &my_charset_bin);
 	  }
 	| CAST_SYM '(' expr AS cast_type ')'
 	  {
             LEX *lex= Lex;
-	    $$= create_func_cast($3, $5,
-                                 lex->length ? atoi(lex->length) : -1,
-                                 lex->dec ? atoi(lex->dec) : 0,
-                                 lex->charset);
+	    $$= create_func_cast($3, $5, lex->length, lex->dec, lex->charset);
             if (!$$)
               MYSQL_YYABORT;
 	  }
@@ -4715,10 +4716,7 @@ simple_expr:
 	  { $$= new Item_func_case(* $3, $2, $4 ); }
 	| CONVERT_SYM '(' expr ',' cast_type ')'
 	  {
-	    $$= create_func_cast($3, $5,
-				 Lex->length ? atoi(Lex->length) : -1,
-                                 Lex->dec ? atoi(Lex->dec) : 0,
-				 Lex->charset);
+	    $$= create_func_cast($3, $5, Lex->length, Lex->dec, Lex->charset);
             if (!$$)
               MYSQL_YYABORT;
 	  }
@@ -6292,12 +6290,14 @@ procedure_list2:
 procedure_item:
 	  remember_name expr
 	  {
-	    LEX *lex= Lex;
-	    if (add_proc_to_list(lex->thd, $2))
+            THD *thd= YYTHD;
+            Lex_input_stream *lip= thd->m_lip;
+
+	    if (add_proc_to_list(thd, $2))
 	      MYSQL_YYABORT;
 	    if (!$2->name)
-	      $2->set_name($1,(uint) ((char*) lex->tok_end - $1),
-                           YYTHD->charset());
+	      $2->set_name($1,(uint) ((char*) lip->tok_end - $1),
+                           thd->charset());
 	  }
           ;
 
@@ -7337,13 +7337,16 @@ use:	USE_SYM ident
 
 load:   LOAD DATA_SYM
         {
-          LEX *lex=Lex;
+          THD *thd= YYTHD;
+          LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
+
 	  if (lex->sphead)
 	  {
 	    my_error(ER_SP_BADSTATEMENT, MYF(0), "LOAD DATA");
 	    MYSQL_YYABORT;
 	  }
-          lex->fname_start= lex->ptr;
+          lex->fname_start= lip->ptr;
         }
         load_data
         {}
@@ -7378,8 +7381,10 @@ load_data:
         }
         opt_duplicate INTO
         {
-	  LEX *lex=Lex;
-	  lex->fname_end= lex->ptr;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
+	  lex->fname_end= lip->ptr;
 	}
         TABLE_SYM table_ident
         {
@@ -7559,15 +7564,16 @@ text_string:
 param_marker:
         PARAM_MARKER
         {
-          THD *thd=YYTHD;
+          THD *thd= YYTHD;
 	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
           Item_param *item;
           if (! lex->parsing_options.allows_variable)
           {
             my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
             MYSQL_YYABORT;
           }
-          item= new Item_param((uint) (lex->tok_start - (uchar *) thd->query));
+          item= new Item_param((uint) (lip->tok_start - thd->query));
           if (!($$= item) || lex->param_list.push_back(item))
           {
             my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
@@ -7590,8 +7596,11 @@ signed_literal:
 literal:
 	text_literal	{ $$ =	$1; }
 	| NUM_literal	{ $$ = $1; }
-	| NULL_SYM	{ $$ =	new Item_null();
-			  Lex->next_state=MY_LEX_OPERATOR_OR_IDENT;}
+	| NULL_SYM
+          {
+            $$ = new Item_null();
+            YYTHD->m_lip->next_state=MY_LEX_OPERATOR_OR_IDENT;
+          }
 	| FALSE_SYM	{ $$= new Item_int((char*) "FALSE",0,1); }
 	| TRUE_SYM	{ $$= new Item_int((char*) "TRUE",1,1); }
 	| HEX_NUM	{ $$ =	new Item_hex_string($1.str, $1.length);}
@@ -7681,8 +7690,10 @@ order_ident:
 simple_ident:
 	ident
 	{
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 	  sp_variable_t *spv;
-	  LEX *lex = Lex;
           sp_pcontext *spc = lex->spcont;
 	  if (spc && (spv = spc->find_variable(&$1)))
 	  {
@@ -7695,7 +7706,7 @@ simple_ident:
 
             Item_splocal *splocal;
             splocal= new Item_splocal($1, spv->offset, spv->type,
-                                      lex->tok_start_prev - 
+                                      lip->tok_start_prev - 
                                       lex->sphead->m_tmp_query);
 #ifndef DBUG_OFF
             if (splocal)
@@ -8291,7 +8302,11 @@ option_value_list:
 
 option_type_value:
         {
-          if (Lex->sphead)
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
+
+          if (lex->sphead)
           {
             /*
               If we are in SP we want have own LEX for each assignment.
@@ -8303,9 +8318,8 @@ option_type_value:
 
               QQ: May be we should simply prohibit group assignments in SP?
             */
-            LEX *lex;
-            Lex->sphead->reset_lex(YYTHD);
-            lex= Lex;
+            Lex->sphead->reset_lex(thd);
+            lex= thd->lex;
 
             /* Set new LEX as if we at start of set rule. */
 	    lex->sql_command= SQLCOM_SET_OPTION;
@@ -8313,12 +8327,14 @@ option_type_value:
 	    lex->option_type=OPT_SESSION;
 	    lex->var_list.empty();
             lex->one_shot_set= 0;
-	    lex->sphead->m_tmp_query= lex->tok_start;
+	    lex->sphead->m_tmp_query= lip->tok_start;
           }
         }
 	ext_option_value
         {
-          LEX *lex= Lex;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 
           if (lex->sphead)
           {
@@ -8340,24 +8356,24 @@ option_type_value:
 
               /*
                 Extract the query statement from the tokenizer.  The
-                end is either lex->ptr, if there was no lookahead,
-                lex->tok_end otherwise.
+                end is either lip->ptr, if there was no lookahead,
+                lip->tok_end otherwise.
               */
               if (yychar == YYEMPTY)
-                qbuff.length= lex->ptr - sp->m_tmp_query;
+                qbuff.length= lip->ptr - sp->m_tmp_query;
               else
-                qbuff.length= lex->tok_end - sp->m_tmp_query;
+                qbuff.length= lip->tok_end - sp->m_tmp_query;
 
-              if (!(qbuff.str= alloc_root(YYTHD->mem_root, qbuff.length + 5)))
+              if (!(qbuff.str= alloc_root(thd->mem_root, qbuff.length + 5)))
                 MYSQL_YYABORT;
 
-              strmake(strmake(qbuff.str, "SET ", 4), (char *)sp->m_tmp_query,
+              strmake(strmake(qbuff.str, "SET ", 4), sp->m_tmp_query,
                       qbuff.length);
               qbuff.length+= 4;
               i->m_query= qbuff;
               sp->add_instr(i);
             }
-            lex->sphead->restore_lex(YYTHD);
+            lex->sphead->restore_lex(thd);
           }
         };
 
@@ -9615,7 +9631,9 @@ trigger_tail:
 	TRIGGER_SYM remember_name sp_name trg_action_time trg_event
 	ON remember_name table_ident FOR_SYM remember_name EACH_SYM ROW_SYM
 	{
-	  LEX *lex= Lex;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 	  sp_head *sp;
 	 
 	  if (lex->sphead)
@@ -9626,9 +9644,9 @@ trigger_tail:
 	
 	  if (!(sp= new sp_head()))
 	    MYSQL_YYABORT;
-	  sp->reset_thd_mem_root(YYTHD);
+	  sp->reset_thd_mem_root(thd);
 	  sp->init(lex);
-          sp->init_sp_name(YYTHD, $3);
+          sp->init_sp_name(thd, $3);
 	
 	  lex->stmt_definition_begin= $2;
           lex->ident.str= $7;
@@ -9642,12 +9660,12 @@ trigger_tail:
 	    stored procedure, otherwise yylex will chop it into pieces
 	    at each ';'.
 	  */
-	  sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES;
-	  YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES;
+	  sp->m_old_cmq= thd->client_capabilities & CLIENT_MULTI_QUERIES;
+	  thd->client_capabilities &= ~CLIENT_MULTI_QUERIES;
 	  
 	  bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
 	  lex->sphead->m_chistics= &lex->sp_chistics;
-	  lex->sphead->m_body_begin= lex->ptr;
+	  lex->sphead->m_body_begin= lip->ptr;
           while (my_isspace(system_charset_info, lex->sphead->m_body_begin[0]))
             ++lex->sphead->m_body_begin;
 	}
@@ -9726,24 +9744,30 @@ sp_tail:
 	}
         '('
 	{
-	  LEX *lex= Lex;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 
-	  lex->sphead->m_param_begin= lex->tok_start+1;
+	  lex->sphead->m_param_begin= lip->tok_start+1;
 	}
 	sp_pdparam_list
 	')'
 	{
-	  LEX *lex= Lex;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 
-	  lex->sphead->m_param_end= lex->tok_start;
+	  lex->sphead->m_param_end= lip->tok_start;
 	  bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
 	}
 	sp_c_chistics
 	{
-	  LEX *lex= Lex;
+          THD *thd= YYTHD;
+	  LEX *lex= thd->lex;
+          Lex_input_stream *lip= thd->m_lip;
 
 	  lex->sphead->m_chistics= &lex->sp_chistics;
-	  lex->sphead->m_body_begin= lex->tok_start;
+	  lex->sphead->m_body_begin= lip->tok_start;
 	}
 	sp_proc_stmt
 	{
diff --git a/sql/table.h b/sql/table.h
index 61232a39f0e..da2c90ab212 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -188,6 +188,8 @@ typedef struct st_table_share
 } TABLE_SHARE;
 
 
+extern ulong refresh_version;
+
 /* Information for one open table */
 
 struct st_table {
@@ -268,7 +270,24 @@ struct st_table {
   my_bool force_index;
   my_bool distinct,const_table,no_rows;
   my_bool key_read, no_keyread;
-  my_bool locked_by_flush;
+  /*
+    Placeholder for an open table which prevents other connections
+    from taking name-locks on this table. Typically used with
+    TABLE_SHARE::version member to take an exclusive name-lock on
+    this table name -- a name lock that not only prevents other
+    threads from opening the table, but also blocks other name
+    locks. This is achieved by:
+    - setting open_placeholder to 1 - this will block other name
+      locks, as wait_for_locked_table_name will be forced to wait,
+      see table_is_used for details.
+    - setting version to 0 - this will force other threads to close
+      the instance of this table and wait (this is the same approach
+      as used for usual name locks).
+    An exclusively name-locked table currently can have no handler
+    object associated with it (db_stat is always 0), but please do
+    not rely on that.
+  */
+  my_bool open_placeholder;
   my_bool locked_by_name;
   my_bool fulltext_searched;
   my_bool no_cache;
@@ -291,6 +310,13 @@ struct st_table {
 
   bool fill_item_list(List<Item> *item_list) const;
   void reset_item_list(List<Item> *item_list) const;
+  /* Is table open or should be treated as such by name-locking? */
+  inline bool is_name_opened() { return db_stat || open_placeholder; }
+  /*
+    Is this instance of the table should be reopen or represents a name-lock?
+  */
+  inline bool needs_reopen_or_name_lock()
+  { return s->version != refresh_version; }
 };
 
 enum enum_schema_table_state
@@ -648,6 +674,12 @@ typedef struct st_table_list
     used for implicit LOCK TABLES only and won't be used in real statement.
   */
   bool          prelocking_placeholder;
+  /*
+    This TABLE_LIST object corresponds to the table to be created
+    so it is possible that it does not exist (used in CREATE TABLE
+    ... SELECT implementation).
+  */
+  bool          create;
 
   enum enum_schema_table_state schema_table_state;
   void calc_md5(char *buffer);
@@ -655,7 +687,11 @@ typedef struct st_table_list
   int view_check_option(THD *thd, bool ignore_failure);
   bool setup_underlying(THD *thd);
   void cleanup_items();
-  bool placeholder() {return derived || view || schema_table || !table; }
+  bool placeholder()
+  {
+    return derived || view || schema_table || create && !table->db_stat ||
+           !table;
+  }
   void print(THD *thd, String *str);
   bool check_single_table(st_table_list **table, table_map map,
                           st_table_list *view);
diff --git a/strings/decimal.c b/strings/decimal.c
index ea92174bfc8..1ae75167794 100644
--- a/strings/decimal.c
+++ b/strings/decimal.c
@@ -1083,7 +1083,11 @@ int decimal2longlong(decimal_t *from, longlong *to)
     x=x*DIG_BASE - *buf++;
     if (unlikely(y < (LONGLONG_MIN/DIG_BASE) || x > y))
     {
-      *to= from->sign ? y : -y;
+      /*
+        the decimal is bigger than any possible integer
+        return border integer depending on the sign
+      */
+      *to= from->sign ? LONGLONG_MIN : LONGLONG_MAX;
       return E_DEC_OVERFLOW;
     }
   }
@@ -1911,6 +1915,14 @@ static int do_sub(decimal_t *from1, decimal_t *from2, decimal_t *to)
   return error;
 }
 
+int decimal_intg(decimal_t *from)
+{
+  int res;
+  dec1 *tmp_res;
+  tmp_res= remove_leading_zeroes(from, &res);
+  return res;
+}
+
 int decimal_add(decimal_t *from1, decimal_t *from2, decimal_t *to)
 {
   if (likely(from1->sign == from2->sign))