Merge 192.168.0.10:mysql/mysql-5.0-maint

into  pilot.mysql.com:/home/msvensson/mysql/mysql-5.0-maint


libmysql/libmysql.c:
  Auto merged
tests/mysql_client_test.c:
  Auto merged

66 files changed, 2028 insertions, 281 deletions

diff --git a/configure.in b/configure.in
index 493fe8f174f..94bc85557df 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.34)
+AM_INIT_AUTOMAKE(mysql, 5.0.36)
 AM_CONFIG_HEADER(config.h)
 
 PROTOCOL_VERSION=10
@@ -19,7 +19,7 @@ SHARED_LIB_VERSION=$SHARED_LIB_MAJOR_VERSION:0:0
 # ndb version
 NDB_VERSION_MAJOR=5
 NDB_VERSION_MINOR=0
-NDB_VERSION_BUILD=34
+NDB_VERSION_BUILD=36
 NDB_VERSION_STATUS=""
 
 # Set all version vars based on $VERSION. How do we do this more elegant ?
diff --git a/heap/hp_block.c b/heap/hp_block.c
index 35e65a94603..85219380287 100644
--- a/heap/hp_block.c
+++ b/heap/hp_block.c
@@ -75,7 +75,7 @@ int hp_get_new_block(HP_BLOCK *block, ulong *alloc_length)
     and my_default_record_cache_size we get about 1/128 unused memory.
    */
   *alloc_length=sizeof(HP_PTRS)*i+block->records_in_block* block->recbuffer;
-  if (!(root=(HP_PTRS*) my_malloc(*alloc_length,MYF(0))))
+  if (!(root=(HP_PTRS*) my_malloc(*alloc_length,MYF(MY_WME))))
     return 1;
 
   if (i == 0)
diff --git a/heap/hp_write.c b/heap/hp_write.c
index f8b268ee06a..8a392d54c51 100644
--- a/heap/hp_write.c
+++ b/heap/hp_write.c
@@ -67,11 +67,17 @@ int heap_write(HP_INFO *info, const byte *record)
   DBUG_RETURN(0);
 
 err:
-  DBUG_PRINT("info",("Duplicate key: %d", (int) (keydef - share->keydef)));
+  if (my_errno == HA_ERR_FOUND_DUPP_KEY)
+    DBUG_PRINT("info",("Duplicate key: %d", keydef - share->keydef));
   info->errkey= keydef - share->keydef;
-  if (keydef->algorithm == HA_KEY_ALG_BTREE)
+  /*
+    We don't need to delete non-inserted key from rb-tree.  Also, if
+    we got ENOMEM, the key wasn't inserted, so don't try to delete it
+    either.  Otherwise for HASH index on HA_ERR_FOUND_DUPP_KEY the key
+    was inserted and we have to delete it.
+  */
+  if (keydef->algorithm == HA_KEY_ALG_BTREE || my_errno == ENOMEM)
   {
-    /* we don't need to delete non-inserted key from rb-tree */
     keydef--;
   }
   while (keydef >= share->keydef)
diff --git a/innobase/buf/buf0buf.c b/innobase/buf/buf0buf.c
index ff7622aca81..f24f1744363 100644
--- a/innobase/buf/buf0buf.c
+++ b/innobase/buf/buf0buf.c
@@ -649,7 +649,7 @@ buf_pool_init(
 		}
 	}
 
-	buf_pool->page_hash = hash0_create(2 * max_size);
+	buf_pool->page_hash = hash_create(2 * max_size);
 
 	buf_pool->n_pend_reads = 0;
 
diff --git a/innobase/dict/dict0dict.c b/innobase/dict/dict0dict.c
index 4ceeb4cafc0..ba03e1f5e41 100644
--- a/innobase/dict/dict0dict.c
+++ b/innobase/dict/dict0dict.c
@@ -708,13 +708,13 @@ dict_init(void)
 	mutex_create(&(dict_sys->mutex));
 	mutex_set_level(&(dict_sys->mutex), SYNC_DICT);
 
-	dict_sys->table_hash = hash0_create(buf_pool_get_max_size() /
+	dict_sys->table_hash = hash_create(buf_pool_get_max_size() /
 					(DICT_POOL_PER_TABLE_HASH *
 					UNIV_WORD_SIZE));
-	dict_sys->table_id_hash = hash0_create(buf_pool_get_max_size() /
+	dict_sys->table_id_hash = hash_create(buf_pool_get_max_size() /
 					(DICT_POOL_PER_TABLE_HASH *
 					UNIV_WORD_SIZE));
-	dict_sys->col_hash = hash0_create(buf_pool_get_max_size() /
+	dict_sys->col_hash = hash_create(buf_pool_get_max_size() /
 					(DICT_POOL_PER_COL_HASH *
 					UNIV_WORD_SIZE));
 	dict_sys->size = 0;
diff --git a/innobase/fil/fil0fil.c b/innobase/fil/fil0fil.c
index 460a2c9f7b8..65320b57183 100644
--- a/innobase/fil/fil0fil.c
+++ b/innobase/fil/fil0fil.c
@@ -1294,8 +1294,8 @@ fil_system_create(
 
 	mutex_set_level(&(system->mutex), SYNC_ANY_LATCH);
 
-	system->spaces = hash0_create(hash_size);
-	system->name_hash = hash0_create(hash_size);
+	system->spaces = hash_create(hash_size);
+	system->name_hash = hash_create(hash_size);
 
 	UT_LIST_INIT(system->LRU);
 
diff --git a/innobase/ha/ha0ha.c b/innobase/ha/ha0ha.c
index 4c17f2d4b0e..ab565acd64f 100644
--- a/innobase/ha/ha0ha.c
+++ b/innobase/ha/ha0ha.c
@@ -32,7 +32,7 @@ ha_create(
 	hash_table_t*	table;
 	ulint		i;
 
-	table = hash0_create(n);
+	table = hash_create(n);
 
 	if (in_btr_search) {
 		table->adaptive = TRUE;
diff --git a/innobase/ha/hash0hash.c b/innobase/ha/hash0hash.c
index 18b5b3cefd6..facdea66198 100644
--- a/innobase/ha/hash0hash.c
+++ b/innobase/ha/hash0hash.c
@@ -74,7 +74,7 @@ Creates a hash table with >= n array cells. The actual number of cells is
 chosen to be a prime number slightly bigger than n. */
 
 hash_table_t*
-hash0_create(
+hash_create(
 /*========*/
 			/* out, own: created table */
 	ulint	n)	/* in: number of array cells */
diff --git a/innobase/include/hash0hash.h b/innobase/include/hash0hash.h
index 3abc05f174c..13f46760698 100644
--- a/innobase/include/hash0hash.h
+++ b/innobase/include/hash0hash.h
@@ -18,12 +18,15 @@ typedef struct hash_cell_struct hash_cell_t;
 
 typedef void*	hash_node_t;
 
+/* Fix Bug #13859: symbol collision between imap/mysql */
+#define hash_create hash0_create
+
 /*****************************************************************
 Creates a hash table with >= n array cells. The actual number
 of cells is chosen to be a prime number slightly bigger than n. */
 
 hash_table_t*
-hash0_create(
+hash_create(
 /*========*/
 			/* out, own: created table */
 	ulint	n);	/* in: number of array cells */
diff --git a/innobase/lock/lock0lock.c b/innobase/lock/lock0lock.c
index 52bb369d479..06475c8ef7e 100644
--- a/innobase/lock/lock0lock.c
+++ b/innobase/lock/lock0lock.c
@@ -584,7 +584,7 @@ lock_sys_create(
 {
 	lock_sys = mem_alloc(sizeof(lock_sys_t));
 
-	lock_sys->rec_hash = hash0_create(n_cells);
+	lock_sys->rec_hash = hash_create(n_cells);
 
 	/* hash_create_mutexes(lock_sys->rec_hash, 2, SYNC_REC_LOCK); */
 
diff --git a/innobase/log/log0recv.c b/innobase/log/log0recv.c
index e92b65ed139..c1ceb9791f2 100644
--- a/innobase/log/log0recv.c
+++ b/innobase/log/log0recv.c
@@ -148,7 +148,7 @@ recv_sys_init(
 	recv_sys->len = 0;
 	recv_sys->recovered_offset = 0;
 
-	recv_sys->addr_hash = hash0_create(available_memory / 64);
+	recv_sys->addr_hash = hash_create(available_memory / 64);
 	recv_sys->n_addrs = 0;
 	
 	recv_sys->apply_log_recs = FALSE;
@@ -187,7 +187,7 @@ recv_sys_empty_hash(void)
 	hash_table_free(recv_sys->addr_hash);
 	mem_heap_empty(recv_sys->heap);
 
-	recv_sys->addr_hash = hash0_create(buf_pool_get_curr_size() / 256);
+	recv_sys->addr_hash = hash_create(buf_pool_get_curr_size() / 256);
 }
 
 /************************************************************
diff --git a/innobase/thr/thr0loc.c b/innobase/thr/thr0loc.c
index f71048af2ba..033bb22807f 100644
--- a/innobase/thr/thr0loc.c
+++ b/innobase/thr/thr0loc.c
@@ -224,7 +224,7 @@ thr_local_init(void)
 
 	ut_a(thr_local_hash == NULL);
 
-	thr_local_hash = hash0_create(OS_THREAD_MAX_N + 100);
+	thr_local_hash = hash_create(OS_THREAD_MAX_N + 100);
 
 	mutex_create(&thr_local_mutex);
 	mutex_set_level(&thr_local_mutex, SYNC_THR_LOCAL);
diff --git a/myisam/mi_check.c b/myisam/mi_check.c
index 05e50022120..444dc2a1da9 100644
--- a/myisam/mi_check.c
+++ b/myisam/mi_check.c
@@ -1813,6 +1813,12 @@ int mi_sort_index(MI_CHECK *param, register MI_INFO *info, my_string name)
   MI_STATE_INFO old_state;
   DBUG_ENTER("mi_sort_index");
 
+  /* cannot sort index files with R-tree indexes */
+  for (key= 0,keyinfo= &share->keyinfo[0]; key < share->base.keys ;
+       key++,keyinfo++)
+    if (keyinfo->key_alg == HA_KEY_ALG_RTREE)
+      return 0;
+
   if (!(param->testflag & T_SILENT))
     printf("- Sorting index for MyISAM-table '%s'\n",name);
 
@@ -1905,6 +1911,8 @@ static int sort_one_index(MI_CHECK *param, MI_INFO *info, MI_KEYDEF *keyinfo,
   char llbuff[22];
   DBUG_ENTER("sort_one_index");
 
+  /* cannot walk over R-tree indices */
+  DBUG_ASSERT(keyinfo->key_alg != HA_KEY_ALG_RTREE);
   new_page_pos=param->new_file_pos;
   param->new_file_pos+=keyinfo->block_length;
 
diff --git a/myisam/mi_packrec.c b/myisam/mi_packrec.c
index 37614cc1e1f..6df64ae2cd4 100644
--- a/myisam/mi_packrec.c
+++ b/myisam/mi_packrec.c
@@ -591,8 +591,7 @@ static void fill_quick_table(uint16 *table, uint bits, uint max_bits,
 static uint copy_decode_table(uint16 *to_pos, uint offset,
 			      uint16 *decode_table)
 {
-  uint prev_offset;
-  prev_offset= offset;
+  uint prev_offset= offset;
   DBUG_ENTER("copy_decode_table");
 
   /* Descent on the left side. */
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 86ab2141e2d..32151305698 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -607,3 +607,40 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
 a	a
 DROP TABLE t1;
+CREATE TABLE t1 (a CHAR(1));
+INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'A'
+SELECT DISTINCT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'A'
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+WHERE ADDDATE(a,1) = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+COUNT(*)
+2
+SELECT COUNT(*) FROM 
+(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+COUNT(*)
+2
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index e8134a50496..05d0d5634e6 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -881,3 +881,14 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 INSERT INTO t1(foo) VALUES ('');
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+DROP TABLE t1;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 8ad6f344c4f..f3114dc55dd 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -764,3 +764,19 @@ natural join t5;
 y	c	b	a	z
 1	3	2	1	4
 drop table t1, t2, t3, t4, t5;
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+USING (Test_ID);
+DESCRIBE tv1;
+Field	Type	Null	Key	Default	Extra
+Name	varchar(50)	YES		NULL	
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+Field	Type	Null	Key	Default	Extra
+Name	varchar(50)	YES		NULL	
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+End of 5.0 tests.
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 0747418111b..f5c98f383b7 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1562,3 +1562,46 @@ id	ngroupbynsa
 2	1
 2	1
 DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (
+id int NOT NULL PRIMARY KEY,
+ct int DEFAULT NULL,
+pc int DEFAULT NULL,
+INDEX idx_ct (ct),
+INDEX idx_pc (pc)
+);
+INSERT INTO t1 VALUES  
+(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY,
+sr int NOT NULL,
+nm varchar(255) NOT NULL,
+INDEX idx_sr (sr)
+);
+INSERT INTO t2 VALUES
+(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');
+CREATE TABLE t3 (
+id int NOT NULL PRIMARY KEY,
+ct int NOT NULL,
+ln int NOT NULL,
+INDEX idx_ct (ct),
+INDEX idx_ln (ln)
+);
+CREATE TABLE t4 (
+id int NOT NULL PRIMARY KEY,
+nm varchar(255) NOT NULL
+);
+INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
+SELECT t1.*
+FROM t1 LEFT JOIN
+(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+WHERE t1.id='5';
+id	ct	pc
+5	NULL	NULL
+SELECT t1.*, t4.nm
+FROM t1 LEFT JOIN
+(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+LEFT JOIN t4 ON t2.sr=t4.id
+WHERE t1.id='5';
+id	ct	pc	nm
+5	NULL	NULL	NULL
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 7b04c1acdc0..e81d46c9199 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -29,14 +29,14 @@ INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
 INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
 INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
 INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
-INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
+INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
 INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
 INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
 INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
 select t1.id,t1.idservice,t2.ordre,t2.description  from t1, t2 where t1.id = 2   and t1.idservice = t2.id  order by t2.ordre;
 id	idservice	ordre	description
 2	1	10	Emettre un appel d'offres
-2	3	40000	Créer une fiche de client
+2	3	40000	Créer une fiche de client
 2	4	40010	Modifier des clients
 2	5	40020	Effacer des clients
 2	6	51050	Ajouter un service
@@ -874,6 +874,14 @@ num	(select num + 2 FROM t1 LIMIT 1)
 SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
 ERROR 42S22: Unknown column 'num' in 'on clause'
 DROP TABLE t1;
+CREATE TABLE t1 (a int);
+SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
+val	val1
+SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
+ERROR 23000: Column 'val' in order clause is ambiguous
+SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
+ERROR 23000: Column 'val' in order clause is ambiguous
+DROP TABLE t1;
 create table t1 (a int not null, b  int not null, c int not null);
 insert t1 values (1,1,1),(1,1,2),(1,2,1);
 select a, b from t1 group by a, b order by sum(c);
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 234c12955d9..000a9317b8d 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1514,4 +1514,26 @@ Variable_name	Value
 Slow_queries	1
 deallocate prepare no_index;
 deallocate prepare sq;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+a
+1
+2
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+EXECUTE stmt;
+a
+1
+2
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
+SET @arg=1;
+EXECUTE stmt USING @arg;
+a
+1
+2
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 4ae38861d29..0b0ad802b54 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -199,6 +199,421 @@ Pos	Instruction
 44	jump 14
 45	stmt 9 "drop temporary table sudoku_work, sud..."
 drop procedure sudoku_solve;
+DROP PROCEDURE IF EXISTS proc_19194_simple;
+DROP PROCEDURE IF EXISTS proc_19194_searched;
+DROP PROCEDURE IF EXISTS proc_19194_nested_1;
+DROP PROCEDURE IF EXISTS proc_19194_nested_2;
+DROP PROCEDURE IF EXISTS proc_19194_nested_3;
+DROP PROCEDURE IF EXISTS proc_19194_nested_4;
+CREATE PROCEDURE proc_19194_simple(i int)
+BEGIN
+DECLARE str CHAR(10);
+CASE i
+WHEN 1 THEN SET str="1";
+WHEN 2 THEN SET str="2";
+WHEN 3 THEN SET str="3";
+ELSE SET str="unknown";
+END CASE;
+SELECT str;
+END|
+CREATE PROCEDURE proc_19194_searched(i int)
+BEGIN
+DECLARE str CHAR(10);
+CASE
+WHEN i=1 THEN SET str="1";
+WHEN i=2 THEN SET str="2";
+WHEN i=3 THEN SET str="3";
+ELSE SET str="unknown";
+END CASE;
+SELECT str;
+END|
+CREATE PROCEDURE proc_19194_nested_1(i int, j int)
+BEGIN
+DECLARE str_i CHAR(10);
+DECLARE str_j CHAR(10);
+CASE i
+WHEN 10 THEN SET str_i="10";
+WHEN 20 THEN
+BEGIN
+set str_i="20";
+CASE
+WHEN j=1 THEN SET str_j="1";
+WHEN j=2 THEN SET str_j="2";
+WHEN j=3 THEN SET str_j="3";
+ELSE SET str_j="unknown";
+END CASE;
+select "i was 20";
+END;
+WHEN 30 THEN SET str_i="30";
+WHEN 40 THEN SET str_i="40";
+ELSE SET str_i="unknown";
+END CASE;
+SELECT str_i, str_j;
+END|
+CREATE PROCEDURE proc_19194_nested_2(i int, j int)
+BEGIN
+DECLARE str_i CHAR(10);
+DECLARE str_j CHAR(10);
+CASE
+WHEN i=10 THEN SET str_i="10";
+WHEN i=20 THEN
+BEGIN
+set str_i="20";
+CASE j
+WHEN 1 THEN SET str_j="1";
+WHEN 2 THEN SET str_j="2";
+WHEN 3 THEN SET str_j="3";
+ELSE SET str_j="unknown";
+END CASE;
+select "i was 20";
+END;
+WHEN i=30 THEN SET str_i="30";
+WHEN i=40 THEN SET str_i="40";
+ELSE SET str_i="unknown";
+END CASE;
+SELECT str_i, str_j;
+END|
+CREATE PROCEDURE proc_19194_nested_3(i int, j int)
+BEGIN
+DECLARE str_i CHAR(10);
+DECLARE str_j CHAR(10);
+CASE i
+WHEN 10 THEN SET str_i="10";
+WHEN 20 THEN
+BEGIN
+set str_i="20";
+CASE j
+WHEN 1 THEN SET str_j="1";
+WHEN 2 THEN SET str_j="2";
+WHEN 3 THEN SET str_j="3";
+ELSE SET str_j="unknown";
+END CASE;
+select "i was 20";
+END;
+WHEN 30 THEN SET str_i="30";
+WHEN 40 THEN SET str_i="40";
+ELSE SET str_i="unknown";
+END CASE;
+SELECT str_i, str_j;
+END|
+CREATE PROCEDURE proc_19194_nested_4(i int, j int)
+BEGIN
+DECLARE str_i CHAR(10);
+DECLARE str_j CHAR(10);
+CASE
+WHEN i=10 THEN SET str_i="10";
+WHEN i=20 THEN
+BEGIN
+set str_i="20";
+CASE
+WHEN j=1 THEN SET str_j="1";
+WHEN j=2 THEN SET str_j="2";
+WHEN j=3 THEN SET str_j="3";
+ELSE SET str_j="unknown";
+END CASE;
+select "i was 20";
+END;
+WHEN i=30 THEN SET str_i="30";
+WHEN i=40 THEN SET str_i="40";
+ELSE SET str_i="unknown";
+END CASE;
+SELECT str_i, str_j;
+END|
+SHOW PROCEDURE CODE proc_19194_simple;
+Pos	Instruction
+0	set str@1 NULL
+1	set_case_expr (12) 0 i@0
+2	jump_if_not 5(12) (case_expr@0 = 1)
+3	set str@1 _latin1'1'
+4	jump 12
+5	jump_if_not 8(12) (case_expr@0 = 2)
+6	set str@1 _latin1'2'
+7	jump 12
+8	jump_if_not 11(12) (case_expr@0 = 3)
+9	set str@1 _latin1'3'
+10	jump 12
+11	set str@1 _latin1'unknown'
+12	stmt 0 "SELECT str"
+SHOW PROCEDURE CODE proc_19194_searched;
+Pos	Instruction
+0	set str@1 NULL
+1	jump_if_not 4(11) (i@0 = 1)
+2	set str@1 _latin1'1'
+3	jump 11
+4	jump_if_not 7(11) (i@0 = 2)
+5	set str@1 _latin1'2'
+6	jump 11
+7	jump_if_not 10(11) (i@0 = 3)
+8	set str@1 _latin1'3'
+9	jump 11
+10	set str@1 _latin1'unknown'
+11	stmt 0 "SELECT str"
+SHOW PROCEDURE CODE proc_19194_nested_1;
+Pos	Instruction
+0	set str_i@2 NULL
+1	set str_j@3 NULL
+2	set_case_expr (27) 0 i@0
+3	jump_if_not 6(27) (case_expr@0 = 10)
+4	set str_i@2 _latin1'10'
+5	jump 27
+6	jump_if_not 20(27) (case_expr@0 = 20)
+7	set str_i@2 _latin1'20'
+8	jump_if_not 11(18) (j@1 = 1)
+9	set str_j@3 _latin1'1'
+10	jump 18
+11	jump_if_not 14(18) (j@1 = 2)
+12	set str_j@3 _latin1'2'
+13	jump 18
+14	jump_if_not 17(18) (j@1 = 3)
+15	set str_j@3 _latin1'3'
+16	jump 18
+17	set str_j@3 _latin1'unknown'
+18	stmt 0 "select "i was 20""
+19	jump 27
+20	jump_if_not 23(27) (case_expr@0 = 30)
+21	set str_i@2 _latin1'30'
+22	jump 27
+23	jump_if_not 26(27) (case_expr@0 = 40)
+24	set str_i@2 _latin1'40'
+25	jump 27
+26	set str_i@2 _latin1'unknown'
+27	stmt 0 "SELECT str_i, str_j"
+SHOW PROCEDURE CODE proc_19194_nested_2;
+Pos	Instruction
+0	set str_i@2 NULL
+1	set str_j@3 NULL
+2	jump_if_not 5(27) (i@0 = 10)
+3	set str_i@2 _latin1'10'
+4	jump 27
+5	jump_if_not 20(27) (i@0 = 20)
+6	set str_i@2 _latin1'20'
+7	set_case_expr (18) 0 j@1
+8	jump_if_not 11(18) (case_expr@0 = 1)
+9	set str_j@3 _latin1'1'
+10	jump 18
+11	jump_if_not 14(18) (case_expr@0 = 2)
+12	set str_j@3 _latin1'2'
+13	jump 18
+14	jump_if_not 17(18) (case_expr@0 = 3)
+15	set str_j@3 _latin1'3'
+16	jump 18
+17	set str_j@3 _latin1'unknown'
+18	stmt 0 "select "i was 20""
+19	jump 27
+20	jump_if_not 23(27) (i@0 = 30)
+21	set str_i@2 _latin1'30'
+22	jump 27
+23	jump_if_not 26(27) (i@0 = 40)
+24	set str_i@2 _latin1'40'
+25	jump 27
+26	set str_i@2 _latin1'unknown'
+27	stmt 0 "SELECT str_i, str_j"
+SHOW PROCEDURE CODE proc_19194_nested_3;
+Pos	Instruction
+0	set str_i@2 NULL
+1	set str_j@3 NULL
+2	set_case_expr (28) 0 i@0
+3	jump_if_not 6(28) (case_expr@0 = 10)
+4	set str_i@2 _latin1'10'
+5	jump 28
+6	jump_if_not 21(28) (case_expr@0 = 20)
+7	set str_i@2 _latin1'20'
+8	set_case_expr (19) 1 j@1
+9	jump_if_not 12(19) (case_expr@1 = 1)
+10	set str_j@3 _latin1'1'
+11	jump 19
+12	jump_if_not 15(19) (case_expr@1 = 2)
+13	set str_j@3 _latin1'2'
+14	jump 19
+15	jump_if_not 18(19) (case_expr@1 = 3)
+16	set str_j@3 _latin1'3'
+17	jump 19
+18	set str_j@3 _latin1'unknown'
+19	stmt 0 "select "i was 20""
+20	jump 28
+21	jump_if_not 24(28) (case_expr@0 = 30)
+22	set str_i@2 _latin1'30'
+23	jump 28
+24	jump_if_not 27(28) (case_expr@0 = 40)
+25	set str_i@2 _latin1'40'
+26	jump 28
+27	set str_i@2 _latin1'unknown'
+28	stmt 0 "SELECT str_i, str_j"
+SHOW PROCEDURE CODE proc_19194_nested_4;
+Pos	Instruction
+0	set str_i@2 NULL
+1	set str_j@3 NULL
+2	jump_if_not 5(26) (i@0 = 10)
+3	set str_i@2 _latin1'10'
+4	jump 26
+5	jump_if_not 19(26) (i@0 = 20)
+6	set str_i@2 _latin1'20'
+7	jump_if_not 10(17) (j@1 = 1)
+8	set str_j@3 _latin1'1'
+9	jump 17
+10	jump_if_not 13(17) (j@1 = 2)
+11	set str_j@3 _latin1'2'
+12	jump 17
+13	jump_if_not 16(17) (j@1 = 3)
+14	set str_j@3 _latin1'3'
+15	jump 17
+16	set str_j@3 _latin1'unknown'
+17	stmt 0 "select "i was 20""
+18	jump 26
+19	jump_if_not 22(26) (i@0 = 30)
+20	set str_i@2 _latin1'30'
+21	jump 26
+22	jump_if_not 25(26) (i@0 = 40)
+23	set str_i@2 _latin1'40'
+24	jump 26
+25	set str_i@2 _latin1'unknown'
+26	stmt 0 "SELECT str_i, str_j"
+CALL proc_19194_nested_1(10, 1);
+str_i	str_j
+10	NULL
+CALL proc_19194_nested_1(25, 1);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_1(20, 1);
+i was 20
+i was 20
+str_i	str_j
+20	1
+CALL proc_19194_nested_1(20, 2);
+i was 20
+i was 20
+str_i	str_j
+20	2
+CALL proc_19194_nested_1(20, 3);
+i was 20
+i was 20
+str_i	str_j
+20	3
+CALL proc_19194_nested_1(20, 4);
+i was 20
+i was 20
+str_i	str_j
+20	unknown
+CALL proc_19194_nested_1(30, 1);
+str_i	str_j
+30	NULL
+CALL proc_19194_nested_1(40, 1);
+str_i	str_j
+40	NULL
+CALL proc_19194_nested_1(0, 0);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_2(10, 1);
+str_i	str_j
+10	NULL
+CALL proc_19194_nested_2(25, 1);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_2(20, 1);
+i was 20
+i was 20
+str_i	str_j
+20	1
+CALL proc_19194_nested_2(20, 2);
+i was 20
+i was 20
+str_i	str_j
+20	2
+CALL proc_19194_nested_2(20, 3);
+i was 20
+i was 20
+str_i	str_j
+20	3
+CALL proc_19194_nested_2(20, 4);
+i was 20
+i was 20
+str_i	str_j
+20	unknown
+CALL proc_19194_nested_2(30, 1);
+str_i	str_j
+30	NULL
+CALL proc_19194_nested_2(40, 1);
+str_i	str_j
+40	NULL
+CALL proc_19194_nested_2(0, 0);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_3(10, 1);
+str_i	str_j
+10	NULL
+CALL proc_19194_nested_3(25, 1);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_3(20, 1);
+i was 20
+i was 20
+str_i	str_j
+20	1
+CALL proc_19194_nested_3(20, 2);
+i was 20
+i was 20
+str_i	str_j
+20	2
+CALL proc_19194_nested_3(20, 3);
+i was 20
+i was 20
+str_i	str_j
+20	3
+CALL proc_19194_nested_3(20, 4);
+i was 20
+i was 20
+str_i	str_j
+20	unknown
+CALL proc_19194_nested_3(30, 1);
+str_i	str_j
+30	NULL
+CALL proc_19194_nested_3(40, 1);
+str_i	str_j
+40	NULL
+CALL proc_19194_nested_3(0, 0);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_4(10, 1);
+str_i	str_j
+10	NULL
+CALL proc_19194_nested_4(25, 1);
+str_i	str_j
+unknown	NULL
+CALL proc_19194_nested_4(20, 1);
+i was 20
+i was 20
+str_i	str_j
+20	1
+CALL proc_19194_nested_4(20, 2);
+i was 20
+i was 20
+str_i	str_j
+20	2
+CALL proc_19194_nested_4(20, 3);
+i was 20
+i was 20
+str_i	str_j
+20	3
+CALL proc_19194_nested_4(20, 4);
+i was 20
+i was 20
+str_i	str_j
+20	unknown
+CALL proc_19194_nested_4(30, 1);
+str_i	str_j
+30	NULL
+CALL proc_19194_nested_4(40, 1);
+str_i	str_j
+40	NULL
+CALL proc_19194_nested_4(0, 0);
+str_i	str_j
+unknown	NULL
+DROP PROCEDURE proc_19194_simple;
+DROP PROCEDURE proc_19194_searched;
+DROP PROCEDURE proc_19194_nested_1;
+DROP PROCEDURE proc_19194_nested_2;
+DROP PROCEDURE proc_19194_nested_3;
+DROP PROCEDURE proc_19194_nested_4;
 DROP PROCEDURE IF EXISTS p1;
 CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1);
 SHOW PROCEDURE CODE p1;
diff --git a/mysql-test/r/sp_stress_case.result b/mysql-test/r/sp_stress_case.result
new file mode 100644
index 00000000000..8ec68363c8d
--- /dev/null
+++ b/mysql-test/r/sp_stress_case.result
@@ -0,0 +1,120 @@
+DROP PROCEDURE IF EXISTS proc_19194_codegen;
+DROP PROCEDURE IF EXISTS bug_19194_simple;
+DROP PROCEDURE IF EXISTS bug_19194_searched;
+CREATE PROCEDURE proc_19194_codegen(
+IN proc_name VARCHAR(50),
+IN count INTEGER,
+IN simple INTEGER,
+OUT body MEDIUMTEXT)
+BEGIN
+DECLARE code MEDIUMTEXT;
+DECLARE i INT DEFAULT 1;
+SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n");
+SET code = concat(code, "BEGIN\n");
+SET code = concat(code, "  DECLARE str CHAR(10);\n");
+IF (simple)
+THEN
+SET code = concat(code, "  CASE i\n");
+ELSE
+SET code = concat(code, "  CASE\n");
+END IF;
+WHILE (i <= count)
+DO
+IF (simple)
+THEN
+SET code = concat(code, "    WHEN ", i, " THEN SET str=\"", i, "\";\n");
+ELSE
+SET code = concat(code, "    WHEN i=", i, " THEN SET str=\"", i, "\";\n");
+END IF;
+SET i = i + 1;
+END WHILE;
+SET code = concat(code, "    ELSE SET str=\"unknown\";\n");
+SET code = concat(code, "  END CASE;\n");
+SET code = concat(code, "  SELECT str;\n");
+SET code = concat(code, "END\n");
+SET body = code;
+END|
+set @body="";
+call proc_19194_codegen("test_simple", 10, 1, @body);
+select @body;
+@body
+CREATE PROCEDURE test_simple(i INT)
+BEGIN
+  DECLARE str CHAR(10);
+  CASE i
+    WHEN 1 THEN SET str="1";
+    WHEN 2 THEN SET str="2";
+    WHEN 3 THEN SET str="3";
+    WHEN 4 THEN SET str="4";
+    WHEN 5 THEN SET str="5";
+    WHEN 6 THEN SET str="6";
+    WHEN 7 THEN SET str="7";
+    WHEN 8 THEN SET str="8";
+    WHEN 9 THEN SET str="9";
+    WHEN 10 THEN SET str="10";
+    ELSE SET str="unknown";
+  END CASE;
+  SELECT str;
+END
+
+call proc_19194_codegen("test_searched", 10, 0, @body);
+select @body;
+@body
+CREATE PROCEDURE test_searched(i INT)
+BEGIN
+  DECLARE str CHAR(10);
+  CASE
+    WHEN i=1 THEN SET str="1";
+    WHEN i=2 THEN SET str="2";
+    WHEN i=3 THEN SET str="3";
+    WHEN i=4 THEN SET str="4";
+    WHEN i=5 THEN SET str="5";
+    WHEN i=6 THEN SET str="6";
+    WHEN i=7 THEN SET str="7";
+    WHEN i=8 THEN SET str="8";
+    WHEN i=9 THEN SET str="9";
+    WHEN i=10 THEN SET str="10";
+    ELSE SET str="unknown";
+  END CASE;
+  SELECT str;
+END
+
+CALL bug_19194_simple(1);
+str
+1
+CALL bug_19194_simple(2);
+str
+2
+CALL bug_19194_simple(1000);
+str
+1000
+CALL bug_19194_simple(4998);
+str
+4998
+CALL bug_19194_simple(4999);
+str
+4999
+CALL bug_19194_simple(9999);
+str
+unknown
+CALL bug_19194_searched(1);
+str
+1
+CALL bug_19194_searched(2);
+str
+2
+CALL bug_19194_searched(1000);
+str
+1000
+CALL bug_19194_searched(4998);
+str
+4998
+CALL bug_19194_searched(4999);
+str
+4999
+CALL bug_19194_searched(9999);
+str
+unknown
+DROP PROCEDURE proc_19194_codegen;
+DROP PROCEDURE bug_19194_simple;
+DROP PROCEDURE bug_19194_searched;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 43247b56096..1bf6d6c7716 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3033,6 +3033,19 @@ t3	CREATE TABLE `t3` (
   `a` datetime default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+a
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+a
+1
+2
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+DROP TABLE t1;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/r/type_binary.result b/mysql-test/r/type_binary.result
index 597defb7a9b..debf4ff8fb8 100644
--- a/mysql-test/r/type_binary.result
+++ b/mysql-test/r/type_binary.result
@@ -136,4 +136,13 @@ insert into t1 values(NULL, 0x412020);
 ERROR 22001: Data too long for column 'vb' at row 1
 drop table t1;
 set @@sql_mode= @old_sql_mode;
+create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
+insert into t1 set f1=1;
+Warnings:
+Warning	1364	Field 'f2' doesn't have a default value
+Warning	1364	Field 'f3' doesn't have a default value
+select hex(f2), hex(f3) from t1;
+hex(f2)	hex(f3)
+0000	
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index eb5d771bcfe..64b7111bbc8 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -194,6 +194,17 @@ DROP FUNCTION sequence;
 DROP FUNCTION lookup;
 DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
+select * from mysql.func;
+name	ret	dl	type
+CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+select IS_const(3);
+IS_const(3)
+const
+drop function IS_const;
+select * from mysql.func;
+name	ret	dl	type
+select is_const(3);
+ERROR 42000: FUNCTION test.is_const does not exist
 CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
 select
 is_const(3) as const,
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 90954fc1ede..753c982155c 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -301,7 +301,14 @@ select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
 select @var;
 @var
 3
-drop table t1;
+create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+select * from t2;
+@var:=f2
+3
+select @var;
+@var
+3
+drop table t1,t2;
 insert into city 'blah';
 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 ''blah'' at line 1
 SHOW COUNT(*) WARNINGS;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 4d076db5c22..f8584275a5a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3014,4 +3014,13 @@ i	j
 6	3
 DROP VIEW v1, v2;
 DROP TABLE t1;
+DROP VIEW IF EXISTS v1;
+CREATE VIEW v1 AS SELECT 'The\ZEnd';
+SELECT * FROM v1;
+TheEnd
+TheEnd
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd`
+DROP VIEW v1;
 End of 5.0 tests.
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index b2cc42cc0ff..8734b940241 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -504,3 +504,24 @@ DROP TABLE t1;
 #DROP TABLE t1;
 #DROP TABLE t2;
 
+#
+# Bug #15881: cast problems
+#
+CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+SELECT DISTINCT a FROM t1 WHERE a=0;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+                WHERE ADDDATE(a,1) = '2002-08-03');
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+SELECT COUNT(*) FROM 
+  (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index e34dd14dbfc..1704fe7dc80 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -254,3 +254,20 @@ INSERT INTO t1() VALUES ();
 --error 1416
 INSERT INTO t1(foo) VALUES ('');
 DROP TABLE t1;
+
+#
+# Bug #23578: Corruption prevents Optimize table from working properly with a 
+#             spatial index
+#
+
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+
+OPTIMIZE TABLE t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index ab85cc5bed2..99dd21e8ee2 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -591,3 +591,23 @@ select * from ((t3 natural join (t1 natural join t2)) natural join t4)
 drop table t1, t2, t3, t4, t5;
 
 # End of tests for WL#2486 - natural/using join
+
+#
+# BUG#25106: A USING clause in combination with a VIEW results in column 
+#            aliases ignored
+#
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+ USING (Test_ID);
+DESCRIBE tv1;
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+
+--echo End of 5.0 tests.
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 69886d035bf..e7405418be7 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -994,3 +994,54 @@ SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
 
 DROP TABLE t1,t2,t3,t4,t5;
 
+#
+# Test for bug #24345: crash with nested left outer join when outer table is substituted
+#                      for a row that happens to have a null value for the join attribute.
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL PRIMARY KEY,
+  ct int DEFAULT NULL,
+  pc int DEFAULT NULL,
+  INDEX idx_ct (ct),
+  INDEX idx_pc (pc)
+);
+INSERT INTO t1 VALUES  
+ (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
+
+CREATE TABLE t2 (
+  id int NOT NULL PRIMARY KEY,
+  sr int NOT NULL,
+  nm varchar(255) NOT NULL,
+  INDEX idx_sr (sr)
+);
+INSERT INTO t2 VALUES
+  (2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');
+
+CREATE TABLE t3 (
+  id int NOT NULL PRIMARY KEY,
+  ct int NOT NULL,
+  ln int NOT NULL,
+  INDEX idx_ct (ct),
+  INDEX idx_ln (ln)
+);
+
+CREATE TABLE t4 (
+  id int NOT NULL PRIMARY KEY,
+  nm varchar(255) NOT NULL
+);
+
+INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
+
+SELECT t1.*
+  FROM t1 LEFT JOIN
+       (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+    WHERE t1.id='5';
+
+SELECT t1.*, t4.nm
+  FROM t1 LEFT JOIN
+      (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+          LEFT JOIN t4 ON t2.sr=t4.id
+    WHERE t1.id='5';
+
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/log.sh b/mysql-test/t/log.sh
index 20b265087cc..29cf8d3e1a3 100755
--- a/mysql-test/t/log.sh
+++ b/mysql-test/t/log.sh
@@ -1,4 +1,4 @@
-#!/bin/bash
+#!/bin/sh
 
 ###########################################################################
 
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index d7cf0e2a375..012b38ff8b7 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -44,7 +44,7 @@ INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
 INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
 INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
 INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
-INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
+INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
 INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
 INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
 INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
@@ -588,6 +588,21 @@ SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
 SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
 DROP TABLE t1;
 
+#
+# Bug #25427: crash when order by expression contains a name
+#             that cannot be resolved unambiguously               
+#
+
+CREATE TABLE t1 (a int);
+
+SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
+--error 1052
+SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
+--error 1052
+SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
+
+DROP TABLE t1;
+
 # End of 4.1 tests
 create table t1 (a int not null, b  int not null, c int not null);
 insert t1 values (1,1,1),(1,1,2),(1,2,1);
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 547f9a766d1..f6de2a40efa 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1563,4 +1563,27 @@ execute sq;
 deallocate prepare no_index;
 deallocate prepare sq;
 
+#
+# Bug 25027: query with a single-row non-correlated subquery
+#            and IS NULL predicate
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
+SET @arg=1;
+EXECUTE stmt USING @arg;
+DEALLOCATE PREPARE stmt;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 72efa831059..97bc29fcad2 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -191,6 +191,241 @@ show procedure code sudoku_solve;
 
 drop procedure sudoku_solve;
 
+#
+# Bug#19194 (Right recursion in parser for CASE causes excessive stack
+#   usage, limitation)
+# This bug also exposed a flaw in the generated code with nested case
+# statements
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS proc_19194_simple;
+DROP PROCEDURE IF EXISTS proc_19194_searched;
+DROP PROCEDURE IF EXISTS proc_19194_nested_1;
+DROP PROCEDURE IF EXISTS proc_19194_nested_2;
+DROP PROCEDURE IF EXISTS proc_19194_nested_3;
+DROP PROCEDURE IF EXISTS proc_19194_nested_4;
+--enable_warnings
+
+delimiter |;
+
+CREATE PROCEDURE proc_19194_simple(i int)
+BEGIN
+  DECLARE str CHAR(10);
+
+  CASE i
+    WHEN 1 THEN SET str="1";
+    WHEN 2 THEN SET str="2";
+    WHEN 3 THEN SET str="3";
+    ELSE SET str="unknown";
+  END CASE;
+
+  SELECT str;
+END|
+
+CREATE PROCEDURE proc_19194_searched(i int)
+BEGIN
+  DECLARE str CHAR(10);
+
+  CASE
+    WHEN i=1 THEN SET str="1";
+    WHEN i=2 THEN SET str="2";
+    WHEN i=3 THEN SET str="3";
+    ELSE SET str="unknown";
+  END CASE;
+
+  SELECT str;
+END|
+
+# Outer SIMPLE case, inner SEARCHED case
+CREATE PROCEDURE proc_19194_nested_1(i int, j int)
+BEGIN
+  DECLARE str_i CHAR(10);
+  DECLARE str_j CHAR(10);
+
+  CASE i
+    WHEN 10 THEN SET str_i="10";
+    WHEN 20 THEN
+    BEGIN
+      set str_i="20";
+      CASE
+        WHEN j=1 THEN SET str_j="1";
+        WHEN j=2 THEN SET str_j="2";
+        WHEN j=3 THEN SET str_j="3";
+      ELSE SET str_j="unknown";
+      END CASE;
+      select "i was 20";
+    END;
+    WHEN 30 THEN SET str_i="30";
+    WHEN 40 THEN SET str_i="40";
+    ELSE SET str_i="unknown";
+  END CASE;
+
+  SELECT str_i, str_j;
+END|
+
+# Outer SEARCHED case, inner SIMPLE case
+CREATE PROCEDURE proc_19194_nested_2(i int, j int)
+BEGIN
+  DECLARE str_i CHAR(10);
+  DECLARE str_j CHAR(10);
+
+  CASE
+    WHEN i=10 THEN SET str_i="10";
+    WHEN i=20 THEN
+    BEGIN
+      set str_i="20";
+      CASE j
+        WHEN 1 THEN SET str_j="1";
+        WHEN 2 THEN SET str_j="2";
+        WHEN 3 THEN SET str_j="3";
+      ELSE SET str_j="unknown";
+      END CASE;
+      select "i was 20";
+    END;
+    WHEN i=30 THEN SET str_i="30";
+    WHEN i=40 THEN SET str_i="40";
+    ELSE SET str_i="unknown";
+  END CASE;
+
+  SELECT str_i, str_j;
+END|
+
+# Outer SIMPLE case, inner SIMPLE case
+CREATE PROCEDURE proc_19194_nested_3(i int, j int)
+BEGIN
+  DECLARE str_i CHAR(10);
+  DECLARE str_j CHAR(10);
+
+  CASE i
+    WHEN 10 THEN SET str_i="10";
+    WHEN 20 THEN
+    BEGIN
+      set str_i="20";
+      CASE j
+        WHEN 1 THEN SET str_j="1";
+        WHEN 2 THEN SET str_j="2";
+        WHEN 3 THEN SET str_j="3";
+      ELSE SET str_j="unknown";
+      END CASE;
+      select "i was 20";
+    END;
+    WHEN 30 THEN SET str_i="30";
+    WHEN 40 THEN SET str_i="40";
+    ELSE SET str_i="unknown";
+  END CASE;
+
+  SELECT str_i, str_j;
+END|
+
+# Outer SEARCHED case, inner SEARCHED case
+CREATE PROCEDURE proc_19194_nested_4(i int, j int)
+BEGIN
+  DECLARE str_i CHAR(10);
+  DECLARE str_j CHAR(10);
+
+  CASE
+    WHEN i=10 THEN SET str_i="10";
+    WHEN i=20 THEN
+    BEGIN
+      set str_i="20";
+      CASE
+        WHEN j=1 THEN SET str_j="1";
+        WHEN j=2 THEN SET str_j="2";
+        WHEN j=3 THEN SET str_j="3";
+      ELSE SET str_j="unknown";
+      END CASE;
+      select "i was 20";
+    END;
+    WHEN i=30 THEN SET str_i="30";
+    WHEN i=40 THEN SET str_i="40";
+    ELSE SET str_i="unknown";
+  END CASE;
+
+  SELECT str_i, str_j;
+END|
+
+delimiter ;|
+
+SHOW PROCEDURE CODE proc_19194_simple;
+SHOW PROCEDURE CODE proc_19194_searched;
+SHOW PROCEDURE CODE proc_19194_nested_1;
+SHOW PROCEDURE CODE proc_19194_nested_2;
+SHOW PROCEDURE CODE proc_19194_nested_3;
+SHOW PROCEDURE CODE proc_19194_nested_4;
+
+CALL proc_19194_nested_1(10, 1);
+
+#
+# Before 19194, the generated code was:
+#   20      jump_if_not 23(27) 30
+#   21      set str_i@2 _latin1'30'
+# As opposed to the expected:
+#   20      jump_if_not 23(27) (case_expr@0 = 30)
+#   21      set str_i@2 _latin1'30'
+#
+# and as a result, this call returned "30",
+# because the expression 30 is always true,
+# masking the case 40, case 0 and the else.
+#
+CALL proc_19194_nested_1(25, 1);
+
+CALL proc_19194_nested_1(20, 1);
+CALL proc_19194_nested_1(20, 2);
+CALL proc_19194_nested_1(20, 3);
+CALL proc_19194_nested_1(20, 4);
+CALL proc_19194_nested_1(30, 1);
+CALL proc_19194_nested_1(40, 1);
+CALL proc_19194_nested_1(0, 0);
+
+CALL proc_19194_nested_2(10, 1);
+
+#
+# Before 19194, the generated code was:
+#   20      jump_if_not 23(27) (case_expr@0 = (i@0 = 30))
+#   21      set str_i@2 _latin1'30'
+# As opposed to the expected:
+#   20      jump_if_not 23(27) (i@0 = 30)
+#   21      set str_i@2 _latin1'30'
+# and as a result, this call crashed the server, because there is no
+# such variable as "case_expr@0".
+#
+CALL proc_19194_nested_2(25, 1);
+
+CALL proc_19194_nested_2(20, 1);
+CALL proc_19194_nested_2(20, 2);
+CALL proc_19194_nested_2(20, 3);
+CALL proc_19194_nested_2(20, 4);
+CALL proc_19194_nested_2(30, 1);
+CALL proc_19194_nested_2(40, 1);
+CALL proc_19194_nested_2(0, 0);
+
+CALL proc_19194_nested_3(10, 1);
+CALL proc_19194_nested_3(25, 1);
+CALL proc_19194_nested_3(20, 1);
+CALL proc_19194_nested_3(20, 2);
+CALL proc_19194_nested_3(20, 3);
+CALL proc_19194_nested_3(20, 4);
+CALL proc_19194_nested_3(30, 1);
+CALL proc_19194_nested_3(40, 1);
+CALL proc_19194_nested_3(0, 0);
+
+CALL proc_19194_nested_4(10, 1);
+CALL proc_19194_nested_4(25, 1);
+CALL proc_19194_nested_4(20, 1);
+CALL proc_19194_nested_4(20, 2);
+CALL proc_19194_nested_4(20, 3);
+CALL proc_19194_nested_4(20, 4);
+CALL proc_19194_nested_4(30, 1);
+CALL proc_19194_nested_4(40, 1);
+CALL proc_19194_nested_4(0, 0);
+
+DROP PROCEDURE proc_19194_simple;
+DROP PROCEDURE proc_19194_searched;
+DROP PROCEDURE proc_19194_nested_1;
+DROP PROCEDURE proc_19194_nested_2;
+DROP PROCEDURE proc_19194_nested_3;
+DROP PROCEDURE proc_19194_nested_4;
 
 #
 # Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored
diff --git a/mysql-test/t/sp_stress_case.test b/mysql-test/t/sp_stress_case.test
new file mode 100644
index 00000000000..1b5bd8991a9
--- /dev/null
+++ b/mysql-test/t/sp_stress_case.test
@@ -0,0 +1,89 @@
+#
+# Bug#19194 (Right recursion in parser for CASE causes excessive stack
+#   usage, limitation)
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS proc_19194_codegen;
+DROP PROCEDURE IF EXISTS bug_19194_simple;
+DROP PROCEDURE IF EXISTS bug_19194_searched;
+--enable_warnings
+
+delimiter |;
+
+CREATE PROCEDURE proc_19194_codegen(
+  IN proc_name VARCHAR(50),
+  IN count INTEGER,
+  IN simple INTEGER,
+  OUT body MEDIUMTEXT)
+BEGIN
+  DECLARE code MEDIUMTEXT;
+  DECLARE i INT DEFAULT 1;
+
+  SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n");
+  SET code = concat(code, "BEGIN\n");
+  SET code = concat(code, "  DECLARE str CHAR(10);\n");
+
+  IF (simple)
+  THEN
+    SET code = concat(code, "  CASE i\n");
+  ELSE
+    SET code = concat(code, "  CASE\n");
+  END IF;
+
+  WHILE (i <= count)
+  DO
+    IF (simple)
+    THEN
+      SET code = concat(code, "    WHEN ", i, " THEN SET str=\"", i, "\";\n");
+    ELSE
+      SET code = concat(code, "    WHEN i=", i, " THEN SET str=\"", i, "\";\n");
+    END IF;
+
+    SET i = i + 1;
+  END WHILE;
+
+  SET code = concat(code, "    ELSE SET str=\"unknown\";\n");
+  SET code = concat(code, "  END CASE;\n");
+  SET code = concat(code, "  SELECT str;\n");
+
+  SET code = concat(code, "END\n");
+
+  SET body = code;
+END|
+
+delimiter ;|
+
+set @body="";
+call proc_19194_codegen("test_simple", 10, 1, @body);
+select @body;
+call proc_19194_codegen("test_searched", 10, 0, @body);
+select @body;
+
+--disable_query_log
+call proc_19194_codegen("bug_19194_simple", 5000, 1, @body);
+let $proc_body = `select @body`;
+eval $proc_body;
+call proc_19194_codegen("bug_19194_searched", 5000, 1, @body);
+let $proc_body = `select @body`;
+eval $proc_body;
+--enable_query_log
+
+CALL bug_19194_simple(1);
+CALL bug_19194_simple(2);
+CALL bug_19194_simple(1000);
+CALL bug_19194_simple(4998);
+CALL bug_19194_simple(4999);
+CALL bug_19194_simple(9999);
+
+CALL bug_19194_searched(1);
+CALL bug_19194_searched(2);
+CALL bug_19194_searched(1000);
+CALL bug_19194_searched(4998);
+CALL bug_19194_searched(4999);
+CALL bug_19194_searched(9999);
+
+DROP PROCEDURE proc_19194_codegen;
+DROP PROCEDURE bug_19194_simple;
+DROP PROCEDURE bug_19194_searched;
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0bbbc5a793e..0b658f746a4 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1988,6 +1988,18 @@ SHOW CREATE TABLE t3;
 
 DROP TABLE t1,t2,t3;
 
+#
+# Bug 24670: subquery witout tables but with a WHERE clause
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+
+DROP TABLE t1;
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test
index 1639aff4711..91eba9b328e 100644
--- a/mysql-test/t/type_binary.test
+++ b/mysql-test/t/type_binary.test
@@ -91,4 +91,12 @@ insert into t1 values(NULL, 0x412020);
 drop table t1;
 set @@sql_mode= @old_sql_mode;
 
+#
+# Bug#14171: Wrong default value for a BINARY field
+#
+create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
+insert into t1 set f1=1;
+select hex(f2), hex(f3) from t1;
+drop table t1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index 52ae424e423..65cbc7ae3ae 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -188,6 +188,23 @@ DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
 
 #
+# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove 
+#             the UDF
+# 
+select * from mysql.func;
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+
+select IS_const(3);
+
+drop function IS_const;
+
+select * from mysql.func;
+
+--error 1305
+select is_const(3);
+
+#
 # Bug#18761: constant expression as UDF parameters not passed in as constant
 #
 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index 65ca1b2c1b7..70f57fdf283 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -210,7 +210,10 @@ create table t1(f1 int, f2 int);
 insert into t1 values (1,2),(2,3),(3,1);
 select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
 select @var;
-drop table t1;
+create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+select * from t2;
+select @var;
+drop table t1,t2;
 
 #
 # Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 8473458ae15..a34a1ba117d 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2960,4 +2960,19 @@ DROP VIEW v1, v2;
 DROP TABLE t1;
 
 
+#
+# BUG#24293: '\Z' token is not handled correctly in views
+#
+
+--disable_warnings
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+CREATE VIEW v1 AS SELECT 'The\ZEnd';
+SELECT * FROM v1;
+
+SHOW CREATE VIEW v1;
+
+DROP VIEW v1;
+
 --echo End of 5.0 tests.
diff --git a/scripts/mysql_explain_log.sh b/scripts/mysql_explain_log.sh
index ad23a42451f..a549817db5a 100644
--- a/scripts/mysql_explain_log.sh
+++ b/scripts/mysql_explain_log.sh
@@ -21,9 +21,6 @@ use DBI;
 use Getopt::Long;
 $Getopt::Long::ignorecase=0;
 
-print "explain_log	provided by http://www.mobile.de\n";
-print "===========	================================\n";
-
 my $Param={};
 
 $Param->{host}='';
@@ -32,16 +29,28 @@ $Param->{password}='';
 $Param->{PrintError}=0;
 $Param->{socket}='';
 
-if (!GetOptions ('date|d:i' => \$Param->{ViewDate},
+my $help;
+
+if (!GetOptions (
+		 'date|d:i' => \$Param->{ViewDate},
 		 'host|h:s' => \$Param->{host},
 		 'user|u:s' => \$Param->{user},
 		 'password|p:s' => \$Param->{password},
 		 'printerror|e:s' => \$Param->{PrintError},
 		 'socket|s:s' => \$Param->{socket},
+		 'help|h' => \$help,
 		)) {
   ShowOptions();
+  exit(0);
 }
-else {
+if (defined ($help)) {
+  ShowOptions();
+  exit(0);
+}
+
+  print "explain_log	provided by http://www.mobile.de\n";
+  print "===========	================================\n";
+
   $Param->{UpdateCount} = 0;
   $Param->{SelectCount} = 0;
   $Param->{IdxUseCount} = 0;
@@ -245,7 +254,6 @@ else {
     print "Finished:   \t".localtime(time)."\n";
 
   }
-}
 
 
 ###########################################################################
@@ -323,21 +331,26 @@ sub ShowOptions {
   print <<EOF;
 Usage: $0 [OPTIONS] < LOGFILE
 
---date=YYMMDD       select only entrys of date
--d=YYMMDD
---host=HOSTNAME     db-host to ask
--h=HOSTNAME
---user=USERNAME     db-user
--u=USERNAME
---password=PASSWORD password of db-user
--p=PASSWORD
---socket=SOCKET     mysqld socket file to connect
--s=SOCKET
---printerror=1      enable error output
--e 1
-
-Read logfile from STDIN an try to EXPLAIN all SELECT statements. All UPDATE statements are rewritten to an EXPLAIN SELECT statement. The results of the EXPLAIN statement are collected and counted. All results with type=ALL are collected in an separete list. Results are printed to STDOUT.
-
+--help, -h
+    Display this help message
+--date=YYMMDD, -d=YYMMDD
+    Select entries from the log only for the given date
+--host=HOSTNAME, -h=HOSTNAME
+    Connect to the MySQL server on the given host
+--user=USERNAME, -u=USERNAME
+    The MySQL username to use when connecting to the server
+--password=PASSWORD, -p=PASSWORD
+    The password to use when connecting to the server
+--socket=SOCKET, -s=SOCKET
+    The socket file to use when connecting to the server
+--printerror=1, -e 1
+    Enable error output
+
+mysql_explain_log reads its standard input for query log contents. It
+uses EXPLAIN to analyze SELECT statements found in the input. UPDATE
+statements are rewritten to SELECT statements and also analyzed with
+EXPLAIN. mysql_explain_log then displays a summary of its results.
+Results are printed to the standard output.
 EOF
 }
 
@@ -351,46 +364,37 @@ __END__
 
 mysql_explain_log
 
-Feed a mysqld general logfile (created with mysqld --log) back into mysql
-and collect statistics about index usage with EXPLAIN.
+Feed a mysqld general query logfile (created with mysqld --log) back
+into mysql and collect statistics about index usage with EXPLAIN.
 
 =head1 DISCUSSION
 
-To optimize your indices, you have to know which ones are actually
-used and what kind of queries are causing table scans. Especially
-if you are generating your queries dynamically and you have a huge
-amount of queries going on, this isn't easy.
+To optimize your indexes, you must know which ones are actually used
+and what kind of queries are causing table scans. This may not be easy,
+especially if you are generating your queries dynamically and you have
+a huge number of queries being executed.
 
 Use this tool to take a look at the effects of your real life queries.
-Then add indices to avoid table scans and remove those which aren't used.
+Then add indexes to avoid table scans and remove indexes that aren't used.
 
 =head1 USAGE
 
-mysql_explain_log [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] [--socket=/path/to/socket] < logfile
-
---date=YYMMDD       select only entrys of date
-
--d=YYMMDD
-
---host=HOSTNAME     db-host to ask
-
--h=HOSTNAME
-
---user=USERNAME     db-user
-
--u=USERNAME
-
---password=PASSWORD password of db-user
-
--p=PASSWORD
-
---socket=SOCKET     change path to the socket
-
--s=SOCKET
-
---printerror=1      enable error output
-
--e 1
+mysql_explain_log [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] [--socket=/path/to/socket] [--printerror=1] < logfile
+
+--help, -h
+    Display this help message
+--date=YYMMDD, -d=YYMMDD
+    Select entries from the log only for the given date
+--host=HOSTNAME, -h=HOSTNAME
+    Connect to the MySQL server on the given host
+--user=USERNAME, -u=USERNAME
+    The MySQL username to use when connecting to the server
+--password=PASSWORD, -p=PASSWORD
+    The password to use when connecting to the server
+--socket=SOCKET, -s=SOCKET
+    The socket file to use when connecting to the server
+--printerror=1, -e 1
+    Enable error output
 
 =head1 EXAMPLE
 
diff --git a/server-tools/instance-manager/listener.cc b/server-tools/instance-manager/listener.cc
index 0f05d1030d7..15583b233fb 100644
--- a/server-tools/instance-manager/listener.cc
+++ b/server-tools/instance-manager/listener.cc
@@ -187,7 +187,7 @@ void Listener_thread::run()
           else
           {
             shutdown(client_fd, SHUT_RDWR);
-            close(client_fd);
+            closesocket(client_fd);
           }
         }
       }
@@ -199,7 +199,7 @@ void Listener_thread::run()
   log_info("Listener_thread::run(): shutdown requested, exiting...");
 
   for (i= 0; i < num_sockets; i++)
-    close(sockets[i]);
+    closesocket(sockets[i]);
 
 #ifndef __WIN__
   unlink(unix_socket_address.sun_path);
@@ -212,7 +212,7 @@ void Listener_thread::run()
 err:
   // we have to close the ip sockets in case of error
   for (i= 0; i < num_sockets; i++)
-    close(sockets[i]);
+    closesocket(sockets[i]);
 
   thread_registry.unregister_thread(&thread_info);
   thread_registry.request_shutdown();
@@ -259,7 +259,7 @@ int Listener_thread::create_tcp_socket()
   {
     log_error("Listener_thread::run(): bind(ip socket) failed, '%s'",
               strerror(errno));
-    close(ip_socket);
+    closesocket(ip_socket);
     return -1;
   }
 
@@ -267,7 +267,7 @@ int Listener_thread::create_tcp_socket()
   {
     log_error("Listener_thread::run(): listen(ip socket) failed, %s",
               strerror(errno));
-    close(ip_socket);
+    closesocket(ip_socket);
     return -1;
   }
 
diff --git a/sql/field.h b/sql/field.h
index 6656ddb36c3..565342637ba 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1034,7 +1034,8 @@ public:
   bool zero_pack() const { return 0; }
   int reset(void)
   {
-    charset()->cset->fill(charset(),ptr,field_length,' ');
+    charset()->cset->fill(charset(),ptr,field_length,
+                          (has_charset() ? ' ' : 0));
     return 0;
   }
   int store(const char *to,uint length,CHARSET_INFO *charset);
diff --git a/sql/item.cc b/sql/item.cc
index 80a5609852f..cc653a7db14 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3698,6 +3698,8 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
         Item** res= find_item_in_list(this, thd->lex->current_select->item_list,
                                       &counter, REPORT_EXCEPT_NOT_FOUND,
                                       &not_used);
+        if (!res)
+          return 1;
         if (res != (Item **)not_found_item)
         {
           if ((*res)->type() == Item::FIELD_ITEM)
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index a5a7f2a051f..936ae04e93d 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2989,7 +2989,7 @@ longlong Item_func_isnull::val_int()
     Handle optimization if the argument can't be null
     This has to be here because of the test in update_used_tables().
   */
-  if (!used_tables_cache)
+  if (!used_tables_cache && !with_subselect)
     return cached_value;
   return args[0]->is_null() ? 1: 0;
 }
@@ -2998,7 +2998,7 @@ longlong Item_is_not_null_test::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   DBUG_ENTER("Item_is_not_null_test::val_int");
-  if (!used_tables_cache)
+  if (!used_tables_cache && !with_subselect)
   {
     owner->was_null|= (!cached_value);
     DBUG_PRINT("info", ("cached :%ld", (long) cached_value));
@@ -3025,7 +3025,7 @@ void Item_is_not_null_test::update_used_tables()
   else
   {
     args[0]->update_used_tables();
-    if (!(used_tables_cache=args[0]->used_tables()))
+    if (!(used_tables_cache=args[0]->used_tables()) && !with_subselect)
     {
       /* Remember if the value is always NULL or never NULL */
       cached_value= (longlong) !args[0]->is_null();
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 454aed01aff..7bdc90adcee 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1027,7 +1027,8 @@ public:
     else
     {
       args[0]->update_used_tables();
-      if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())))
+      if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())) &&
+          !with_subselect)
       {
 	/* Remember if the value is always NULL or never NULL */
 	cached_value= (longlong) args[0]->is_null();
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 0eab370237d..1ef77208469 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -3688,8 +3688,9 @@ update_hash(user_var_entry *entry, bool set_null, void *ptr, uint length,
 	char *pos= (char*) entry+ ALIGN_SIZE(sizeof(user_var_entry));
 	if (entry->value == pos)
 	  entry->value=0;
-	if (!(entry->value=(char*) my_realloc(entry->value, length,
-					      MYF(MY_ALLOW_ZERO_PTR))))
+        entry->value= (char*) my_realloc(entry->value, length,
+                                         MYF(MY_ALLOW_ZERO_PTR | MY_WME));
+        if (!entry->value)
 	  return 1;
       }
     }
@@ -4056,6 +4057,105 @@ void Item_func_set_user_var::make_field(Send_field *tmp_field)
     Item::make_field(tmp_field);
 }
 
+
+/*
+  Save the value of a user variable into a field
+
+  SYNOPSIS
+    save_in_field()
+      field           target field to save the value to
+      no_conversion   flag indicating whether conversions are allowed
+
+  DESCRIPTION
+    Save the function value into a field and update the user variable
+    accordingly. If a result field is defined and the target field doesn't
+    coincide with it then the value from the result field will be used as
+    the new value of the user variable.
+
+    The reason to have this method rather than simply using the result
+    field in the val_xxx() methods is that the value from the result field
+    not always can be used when the result field is defined.
+    Let's consider the following cases:
+    1) when filling a tmp table the result field is defined but the value of it
+    is undefined because it has to be produced yet. Thus we can't use it.
+    2) on execution of an INSERT ... SELECT statement the save_in_field()
+    function will be called to fill the data in the new record. If the SELECT
+    part uses a tmp table then the result field is defined and should be
+    used in order to get the correct result.
+
+    The difference between the SET_USER_VAR function and regular functions
+    like CONCAT is that the Item_func objects for the regular functions are
+    replaced by Item_field objects after the values of these functions have
+    been stored in a tmp table. Yet an object of the Item_field class cannot
+    be used to update a user variable.
+    Due to this we have to handle the result field in a special way here and
+    in the Item_func_set_user_var::send() function.
+
+  RETURN VALUES
+    FALSE       Ok
+    TRUE        Error
+*/
+
+int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions)
+{
+  bool use_result_field= (result_field && result_field != field);
+  int error;
+
+  /* Update the value of the user variable */
+  check(use_result_field);
+  update();
+
+  if (result_type() == STRING_RESULT ||
+      result_type() == REAL_RESULT &&
+      field->result_type() == STRING_RESULT)
+  {
+    String *result;
+    CHARSET_INFO *cs= collation.collation;
+    char buff[MAX_FIELD_WIDTH];		// Alloc buffer for small columns
+    str_value.set_quick(buff, sizeof(buff), cs);
+    result= entry->val_str(&null_value, &str_value, decimals);
+
+    if (null_value)
+    {
+      str_value.set_quick(0, 0, cs);
+      return set_field_to_null_with_conversions(field, no_conversions);
+    }
+
+    /* NOTE: If null_value == FALSE, "result" must be not NULL.  */
+
+    field->set_notnull();
+    error=field->store(result->ptr(),result->length(),cs);
+    str_value.set_quick(0, 0, cs);
+  }
+  else if (result_type() == REAL_RESULT)
+  {
+    double nr= entry->val_real(&null_value);
+    if (null_value)
+      return set_field_to_null(field);
+    field->set_notnull();
+    error=field->store(nr);
+  }
+  else if (result_type() == DECIMAL_RESULT)
+  {
+    my_decimal decimal_value;
+    my_decimal *value= entry->val_decimal(&null_value, &decimal_value);
+    if (null_value)
+      return set_field_to_null(field);
+    field->set_notnull();
+    error=field->store_decimal(value);
+  }
+  else
+  {
+    longlong nr= entry->val_int(&null_value);
+    if (null_value)
+      return set_field_to_null_with_conversions(field, no_conversions);
+    field->set_notnull();
+    error=field->store(nr, unsigned_flag);
+  }
+  return error;
+}
+
+
 String *
 Item_func_get_user_var::val_str(String *str)
 {
diff --git a/sql/item_func.h b/sql/item_func.h
index c116c18bc50..68591f9c6f5 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1188,6 +1188,7 @@ public:
   void print(String *str);
   void print_as_stmt(String *str);
   const char *func_name() const { return "set_user_var"; }
+  int save_in_field(Field *field, bool no_conversions);
 };
 
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 28aaf41b39f..d5d26b7b741 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -348,6 +348,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
       */
       !(select_lex->item_list.head()->type() == FIELD_ITEM ||
 	select_lex->item_list.head()->type() == REF_ITEM) &&
+      !join->conds && !join->having &&
       /*
         switch off this optimization for prepare statement,
         because we do not rollback this changes
@@ -372,8 +373,6 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     */
     substitution->walk(&Item::remove_dependence_processor,
 		       (byte *) select_lex->outer_select());
-    /* SELECT without FROM clause can't have WHERE or HAVING clause */
-    DBUG_ASSERT(join->conds == 0 && join->having == 0);
     return RES_REDUCE;
   }
   return RES_OK;
@@ -2277,6 +2276,22 @@ bool subselect_single_select_engine::no_tables()
 
 
 /*
+  Check statically whether the subquery can return NULL
+
+  SINOPSYS
+    subselect_single_select_engine::may_be_null()
+
+  RETURN
+    FALSE  can guarantee that the subquery never return NULL
+    TRUE   otherwise
+*/
+bool subselect_single_select_engine::may_be_null()
+{
+  return ((no_tables() && !join->conds && !join->having) ? maybe_null : 1);
+}
+
+
+/*
   Report about presence of tables in subquery
 
   SYNOPSIS
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 5b2ab419b77..69ac78d859b 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -362,7 +362,7 @@ public:
   enum Item_result type() { return res_type; }
   enum_field_types field_type() { return res_field_type; }
   virtual void exclude()= 0;
-  bool may_be_null() { return maybe_null; };
+  virtual bool may_be_null() { return maybe_null; };
   virtual table_map upper_select_const_tables()= 0;
   static table_map calc_const_tables(TABLE_LIST *);
   virtual void print(String *str)= 0;
@@ -399,6 +399,7 @@ public:
   void print (String *str);
   bool change_result(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  bool may_be_null();
   bool is_executed() const { return executed; }
   bool no_rows();
 };
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 67d2d29422c..534d42e1c17 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1157,6 +1157,7 @@ File open_binlog(IO_CACHE *log, const char *log_file_name,
 /* mysqld.cc */
 extern void MYSQLerror(const char*);
 void refresh_status(THD *thd);
+my_bool mysql_rm_tmp_tables(void);
 
 /* item_func.cc */
 extern bool check_reserved_words(LEX_STRING *name);
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index eb2c0ca68d1..47d5ec6a2a0 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3496,7 +3496,7 @@ we force server id to 2, but this MySQL server will not act as a slave.");
   */
   error_handler_hook= my_message_sql;
   start_signal_handler();				// Creates pidfile
-  if (acl_init(opt_noacl) ||
+  if (mysql_rm_tmp_tables() || acl_init(opt_noacl) ||
       my_tz_init((THD *)0, default_tz_name, opt_bootstrap))
   {
     abort_loop=1;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index ff39421cef7..fb99ed1bb5c 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -605,27 +605,6 @@ sp_head::create(THD *thd)
   DBUG_PRINT("info", ("type: %d name: %s params: %s body: %s",
 		      m_type, m_name.str, m_params.str, m_body.str));
 
-#ifndef DBUG_OFF
-  optimize();
-  {
-    String s;
-    sp_instr *i;
-    uint ip= 0;
-    while ((i = get_instr(ip)))
-    {
-      char buf[8];
-
-      sprintf(buf, "%4u: ", ip);
-      s.append(buf);
-      i->print(&s);
-      s.append('\n');
-      ip+= 1;
-    }
-    s.append('\0');
-    DBUG_PRINT("info", ("Code %s\n%s", m_qname.str, s.ptr()));
-  }
-#endif
-
   if (m_type == TYPE_ENUM_FUNCTION)
     ret= sp_create_function(thd, this);
   else
@@ -2173,7 +2152,7 @@ sp_head::show_create_function(THD *thd)
   This is the main mark and move loop; it relies on the following methods
   in sp_instr and its subclasses:
 
-  opt_mark()           Mark instruction as reachable (will recurse for jumps)
+  opt_mark()           Mark instruction as reachable
   opt_shortcut_jump()  Shortcut jumps to the final destination;
                        used by opt_mark().
   opt_move()           Update moved instruction
@@ -2186,7 +2165,7 @@ void sp_head::optimize()
   sp_instr *i;
   uint src, dst;
 
-  opt_mark(0);
+  opt_mark();
 
   bp.empty();
   src= dst= 0;
@@ -2220,13 +2199,50 @@ void sp_head::optimize()
   bp.empty();
 }
 
+void sp_head::add_mark_lead(uint ip, List<sp_instr> *leads)
+{
+  sp_instr *i= get_instr(ip);
+
+  if (i && ! i->marked)
+    leads->push_front(i);
+}
+
 void
-sp_head::opt_mark(uint ip)
+sp_head::opt_mark()
 {
+  uint ip;
   sp_instr *i;
+  List<sp_instr> leads;
 
-  while ((i= get_instr(ip)) && !i->marked)
-    ip= i->opt_mark(this);
+  /*
+    Forward flow analysis algorithm in the instruction graph:
+    - first, add the entry point in the graph (the first instruction) to the
+      'leads' list of paths to explore.
+    - while there are still leads to explore:
+      - pick one lead, and follow the path forward. Mark instruction reached.
+        Stop only if the end of the routine is reached, or the path converge
+        to code already explored (marked).
+      - while following a path, collect in the 'leads' list any fork to
+        another path (caused by conditional jumps instructions), so that these
+        paths can be explored as well.
+  */
+
+  /* Add the entry point */
+  i= get_instr(0);
+  leads.push_front(i);
+
+  /* For each path of code ... */
+  while (leads.elements != 0)
+  {
+    i= leads.pop();
+
+    /* Mark the entire path, collecting new leads. */
+    while (i && ! i->marked)
+    {
+      ip= i->opt_mark(this, & leads);
+      i= get_instr(ip);
+    }
+  }
 }
 
 
@@ -2619,7 +2635,7 @@ sp_instr_jump::print(String *str)
 }
 
 uint
-sp_instr_jump::opt_mark(sp_head *sp)
+sp_instr_jump::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
   m_dest= opt_shortcut_jump(sp, this);
   if (m_dest != m_ip+1)		/* Jumping to following instruction? */
@@ -2713,7 +2729,7 @@ sp_instr_jump_if_not::print(String *str)
 
 
 uint
-sp_instr_jump_if_not::opt_mark(sp_head *sp)
+sp_instr_jump_if_not::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
   sp_instr *i;
 
@@ -2723,13 +2739,13 @@ sp_instr_jump_if_not::opt_mark(sp_head *sp)
     m_dest= i->opt_shortcut_jump(sp, this);
     m_optdest= sp->get_instr(m_dest);
   }
-  sp->opt_mark(m_dest);
+  sp->add_mark_lead(m_dest, leads);
   if ((i= sp->get_instr(m_cont_dest)))
   {
     m_cont_dest= i->opt_shortcut_jump(sp, this);
     m_cont_optdest= sp->get_instr(m_cont_dest);
   }
-  sp->opt_mark(m_cont_dest);
+  sp->add_mark_lead(m_cont_dest, leads);
   return m_ip+1;
 }
 
@@ -2850,7 +2866,7 @@ sp_instr_hpush_jump::print(String *str)
 
 
 uint
-sp_instr_hpush_jump::opt_mark(sp_head *sp)
+sp_instr_hpush_jump::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
   sp_instr *i;
 
@@ -2860,7 +2876,7 @@ sp_instr_hpush_jump::opt_mark(sp_head *sp)
     m_dest= i->opt_shortcut_jump(sp, this);
     m_optdest= sp->get_instr(m_dest);
   }
-  sp->opt_mark(m_dest);
+  sp->add_mark_lead(m_dest, leads);
   return m_ip+1;
 }
 
@@ -2925,15 +2941,13 @@ sp_instr_hreturn::print(String *str)
 
 
 uint
-sp_instr_hreturn::opt_mark(sp_head *sp)
+sp_instr_hreturn::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
   if (m_dest)
-    return sp_instr_jump::opt_mark(sp);
-  else
-  {
-    marked= 1;
-    return UINT_MAX;
-  }
+    return sp_instr_jump::opt_mark(sp, leads);
+
+  marked= 1;
+  return UINT_MAX;
 }
 
 
@@ -3276,7 +3290,7 @@ sp_instr_set_case_expr::print(String *str)
 }
 
 uint
-sp_instr_set_case_expr::opt_mark(sp_head *sp)
+sp_instr_set_case_expr::opt_mark(sp_head *sp, List<sp_instr> *leads)
 {
   sp_instr *i;
 
@@ -3286,7 +3300,7 @@ sp_instr_set_case_expr::opt_mark(sp_head *sp)
     m_cont_dest= i->opt_shortcut_jump(sp, this);
     m_cont_optdest= sp->get_instr(m_cont_dest);
   }
-  sp->opt_mark(m_cont_dest);
+  sp->add_mark_lead(m_cont_dest, leads);
   return m_ip+1;
 }
 
diff --git a/sql/sp_head.h b/sql/sp_head.h
index a82a65458ea..0139f879ce4 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -301,8 +301,19 @@ public:
 
   void restore_thd_mem_root(THD *thd);
 
+  /**
+    Optimize the code.
+  */
   void optimize();
-  void opt_mark(uint ip);
+
+  /**
+    Helper used during flow analysis during code optimization.
+    See the implementation of <code>opt_mark()</code>.
+    @param ip the instruction to add to the leads list
+    @param leads the list of remaining paths to explore in the graph that
+    represents the code, during flow analysis.
+  */
+  void add_mark_lead(uint ip, List<sp_instr> *leads);
 
   void recursion_level_error(THD *thd);
 
@@ -392,6 +403,12 @@ private:
   bool
   execute(THD *thd);
 
+  /**
+    Perform a forward flow analysis in the generated code.
+    Mark reachable instructions, for the optimizer.
+  */
+  void opt_mark();
+
   /*
     Merge the list of tables used by query into the multi-set of tables used
     by routine.
@@ -459,10 +476,10 @@ public:
 
   /*
     Mark this instruction as reachable during optimization and return the
-    index to the next instruction. Jump instruction will mark their
-    destination too recursively.
+    index to the next instruction. Jump instruction will add their
+    destination to the leads list.
   */
-  virtual uint opt_mark(sp_head *sp)
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads)
   {
     marked= 1;
     return m_ip+1;
@@ -734,7 +751,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp);
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
   virtual uint opt_shortcut_jump(sp_head *sp, sp_instr *start);
 
@@ -784,7 +801,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp);
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
   /* Override sp_instr_jump's shortcut; we stop here */
   virtual uint opt_shortcut_jump(sp_head *sp, sp_instr *start)
@@ -830,7 +847,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp)
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads)
   {
     marked= 1;
     return UINT_MAX;
@@ -867,7 +884,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp);
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
   /* Override sp_instr_jump's shortcut; we stop here. */
   virtual uint opt_shortcut_jump(sp_head *sp, sp_instr *start)
@@ -932,7 +949,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp);
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
 private:
 
@@ -1102,7 +1119,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp)
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads)
   {
     marked= 1;
     return UINT_MAX;
@@ -1135,7 +1152,7 @@ public:
 
   virtual void print(String *str);
 
-  virtual uint opt_mark(sp_head *sp);
+  virtual uint opt_mark(sp_head *sp, List<sp_instr> *leads);
 
   virtual void opt_move(uint dst, List<sp_instr> *ibp);
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index c5a9fad333d..a4318f7b4bf 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -36,7 +36,6 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
 			     TABLE_LIST *table_list, MEM_ROOT *mem_root,
                              uint flags);
 static void free_cache_entry(TABLE *entry);
-static void mysql_rm_tmp_tables(void);
 static bool open_new_frm(THD *thd, const char *path, const char *alias,
                          const char *db, const char *table_name,
                          uint db_stat, uint prgflag,
@@ -53,7 +52,6 @@ extern "C" byte *table_cache_key(const byte *record,uint *length,
 
 bool table_cache_init(void)
 {
-  mysql_rm_tmp_tables();
   return hash_init(&open_cache, &my_charset_bin, table_cache_size+16,
 		   0, 0,table_cache_key,
 		   (hash_free_key) free_cache_entry, 0) != 0;
@@ -2977,6 +2975,19 @@ find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name,
       column reference. See create_view_field() for details.
     */
     item= nj_col->create_item(thd);
+    /*
+     *ref != NULL means that *ref contains the item that we need to
+     replace. If the item was aliased by the user, set the alias to
+     the replacing item.
+     We need to set alias on both ref itself and on ref real item.
+     */
+    if (*ref && !(*ref)->is_autogenerated_name)
+    {
+      item->set_name((*ref)->name, (*ref)->name_length,
+                     system_charset_info);
+      item->real_item()->set_name((*ref)->name, (*ref)->name_length,
+                                  system_charset_info);
+    }
     if (register_tree_change && arena)
       thd->restore_active_arena(arena, &backup);
 
@@ -5157,14 +5168,21 @@ fill_record_n_invoke_before_triggers(THD *thd, Field **ptr,
 }
 
 
-static void mysql_rm_tmp_tables(void)
+my_bool mysql_rm_tmp_tables(void)
 {
   uint i, idx;
-  char	filePath[FN_REFLEN], *tmpdir;
+  char	filePath[FN_REFLEN], *tmpdir, filePathCopy[FN_REFLEN];
   MY_DIR *dirp;
   FILEINFO *file;
+  TABLE tmp_table;
+  THD *thd;
   DBUG_ENTER("mysql_rm_tmp_tables");
 
+  if (!(thd= new THD))
+    DBUG_RETURN(1);
+  thd->thread_stack= (char*) &thd;
+  thd->store_globals();
+
   for (i=0; i<=mysql_tmpdir_list.max; i++)
   {
     tmpdir=mysql_tmpdir_list.list[i];
@@ -5185,13 +5203,37 @@ static void mysql_rm_tmp_tables(void)
 
     if (!bcmp(file->name,tmp_file_prefix,tmp_file_prefix_length))
     {
-        sprintf(filePath,"%s%s",tmpdir,file->name);
-        VOID(my_delete(filePath,MYF(MY_WME)));
+      char *ext= fn_ext(file->name);
+      uint ext_len= strlen(ext);
+      uint filePath_len= my_snprintf(filePath, sizeof(filePath),
+                                     "%s%s", tmpdir, file->name);
+      if (!bcmp(reg_ext, ext, ext_len))
+      {
+        TABLE tmp_table;
+        if (!openfrm(thd, filePath, "tmp_table", (uint) 0,
+                     READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
+                     0, &tmp_table))
+        {
+          /* We should cut file extention before deleting of table */
+          memcpy(filePathCopy, filePath, filePath_len - ext_len);
+          filePathCopy[filePath_len - ext_len]= 0;
+          tmp_table.file->delete_table(filePathCopy);
+          closefrm(&tmp_table);
+        }
+      }
+      /*
+        File can be already deleted by tmp_table.file->delete_table().
+        So we hide error messages which happnes during deleting of these
+        files(MYF(0)).
+      */
+      VOID(my_delete(filePath, MYF(0))); 
     }
   }
   my_dirend(dirp);
   }
-  DBUG_VOID_RETURN;
+  delete thd;
+  my_pthread_setspecific_ptr(THR_THD,  0);
+  DBUG_RETURN(0);
 }
 
 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 84d2ce77014..0794d4c797a 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -884,6 +884,13 @@ void select_result::cleanup()
   /* do nothing */
 }
 
+bool select_result::check_simple_select() const
+{
+  my_error(ER_SP_BAD_CURSOR_QUERY, MYF(0));
+  return TRUE;
+}
+
+
 static String default_line_term("\n",default_charset_info);
 static String default_escaped("\\",default_charset_info);
 static String default_field_term("\t",default_charset_info);
@@ -1521,6 +1528,13 @@ int select_dumpvar::prepare(List<Item> &list, SELECT_LEX_UNIT *u)
 }
 
 
+bool select_dumpvar::check_simple_select() const
+{
+  my_error(ER_SP_BAD_CURSOR_SELECT, MYF(0));
+  return TRUE;
+}
+
+
 void select_dumpvar::cleanup()
 {
   row_count= 0;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index efc13c02a59..d74158ec07c 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1723,7 +1723,14 @@ public:
   virtual bool initialize_tables (JOIN *join=0) { return 0; }
   virtual void send_error(uint errcode,const char *err);
   virtual bool send_eof()=0;
-  virtual bool simple_select() { return 0; }
+  /**
+    Check if this query returns a result set and therefore is allowed in
+    cursors and set an error message if it is not the case.
+
+    @retval FALSE     success
+    @retval TRUE      error, an error message is set
+  */
+  virtual bool check_simple_select() const;
   virtual void abort() {}
   /*
     Cleanup instance of this class for next execution of a prepared
@@ -1761,7 +1768,7 @@ public:
   bool send_fields(List<Item> &list, uint flags);
   bool send_data(List<Item> &items);
   bool send_eof();
-  bool simple_select() { return 1; }
+  virtual bool check_simple_select() const { return FALSE; }
   void abort();
 };
 
@@ -2200,6 +2207,7 @@ public:
   int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
   bool send_data(List<Item> &items);
   bool send_eof();
+  virtual bool check_simple_select() const;
   void cleanup();
 };
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 45272645633..986cb4760de 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1163,7 +1163,6 @@ void st_select_lex::init_select()
   options= 0;
   sql_cache= SQL_CACHE_UNSPECIFIED;
   braces= 0;
-  when_list.empty();
   expr_list.empty();
   udf_list.empty();
   interval_list.empty();
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index db119d527d9..a7b162c6062 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -517,7 +517,6 @@ public:
 
   SQL_LIST order_list;                /* ORDER clause */
   List<List_item>     expr_list;
-  List<List_item>     when_list;      /* WHEN clause (expression) */
   SQL_LIST *gorder_list;
   Item *select_limit, *offset_limit;  /* LIMIT clause parameters */
   // Arrays of pointers to top elements of all_fields list
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index e9f33a172c4..152cc3aa385 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2902,10 +2902,9 @@ bool Prepared_statement::execute(String *expanded_query, bool open_cursor)
     in INSERT ... SELECT and similar commands.
   */
 
-  if (open_cursor && lex->result && !lex->result->simple_select())
+  if (open_cursor && lex->result && lex->result->check_simple_select())
   {
     DBUG_PRINT("info",("Cursor asked for not SELECT stmt"));
-    my_error(ER_SP_BAD_CURSOR_QUERY, MYF(0));
     return TRUE;
   }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 30514f09493..abb949ae473 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2305,8 +2305,18 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
         substitution of a const table the key value happens to be null
         then we can state that there are no matches for this equi-join.
       */  
-      if ((keyuse= s->keyuse) && *s->on_expr_ref)
+      if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map)
       {
+        /* 
+          When performing an outer join operation if there are no matching rows
+          for the single row of the outer table all the inner tables are to be
+          null complemented and thus considered as constant tables.
+          Here we apply this consideration to the case of outer join operations 
+          with a single inner table only because the case with nested tables
+          would require a more thorough analysis.
+          TODO. Apply single row substitution to null complemented inner tables
+          for nested outer join operations. 
+	*/              
         while (keyuse->table == table)
         {
           if (!(keyuse->val->used_tables() & ~join->const_table_map) &&
@@ -8432,6 +8442,46 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
   return cond;					// Point at next and level
 }
 
+/* 
+  Check if equality can be used in removing components of GROUP BY/DISTINCT
+  
+  SYNOPSIS
+    test_if_equality_guarantees_uniqueness()
+      l          the left comparison argument (a field if any)
+      r          the right comparison argument (a const of any)
+  
+  DESCRIPTION    
+    Checks if an equality predicate can be used to take away 
+    DISTINCT/GROUP BY because it is known to be true for exactly one 
+    distinct value (e.g. <expr> == <const>).
+    Arguments must be of the same type because e.g. 
+    <string_field> = <int_const> may match more than 1 distinct value from 
+    the column. 
+    We must take into consideration and the optimization done for various 
+    string constants when compared to dates etc (see Item_int_with_ref) as
+    well as the collation of the arguments.
+  
+  RETURN VALUE  
+    TRUE    can be used
+    FALSE   cannot be used
+*/
+static bool
+test_if_equality_guarantees_uniqueness(Item *l, Item *r)
+{
+  return r->const_item() &&
+    /* elements must be of the same result type */
+    (r->result_type() == l->result_type() ||
+    /* or dates compared to longs */
+     (((l->type() == Item::FIELD_ITEM &&
+        ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
+       (l->type() == Item::FUNC_ITEM &&
+        ((Item_func *)l)->result_as_longlong())) &&
+      r->result_type() == INT_RESULT))
+    /* and must have the same collation if compared as strings */
+    && (l->result_type() != STRING_RESULT ||
+        l->collation.collation == r->collation.collation);
+}
+
 /*
   Return 1 if the item is a const value in all the WHERE clause
 */
@@ -8468,7 +8518,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
     Item *right_item= ((Item_func*) cond)->arguments()[1];
     if (left_item->eq(comp_item,1))
     {
-      if (right_item->const_item())
+      if (test_if_equality_guarantees_uniqueness (left_item, right_item))
       {
 	if (*const_item)
 	  return right_item->eq(*const_item, 1);
@@ -8478,7 +8528,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
     }
     else if (right_item->eq(comp_item,1))
     {
-      if (left_item->const_item())
+      if (test_if_equality_guarantees_uniqueness (right_item, left_item))
       {
 	if (*const_item)
 	  return left_item->eq(*const_item, 1);
diff --git a/sql/sql_string.cc b/sql/sql_string.cc
index 16f35e09e02..ef50e5864a5 100644
--- a/sql/sql_string.cc
+++ b/sql/sql_string.cc
@@ -1032,8 +1032,8 @@ void String::print(String *str)
     case '\r':
       str->append(STRING_WITH_LEN("\\r"));
       break;
-    case 26: //Ctrl-Z
-      str->append(STRING_WITH_LEN("\\z"));
+    case '\032': // Ctrl-Z
+      str->append(STRING_WITH_LEN("\\Z"));
       break;
     default:
       str->append(c);
diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc
index 1ebf2a79d7c..077660f0bb9 100644
--- a/sql/sql_udf.cc
+++ b/sql/sql_udf.cc
@@ -509,6 +509,8 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
   TABLE *table;
   TABLE_LIST tables;
   udf_func *udf;
+  char *exact_name_str;
+  uint exact_name_len;
   DBUG_ENTER("mysql_drop_function");
   if (!initialized)
   {
@@ -522,6 +524,8 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
     my_error(ER_FUNCTION_NOT_DEFINED, MYF(0), udf_name->str);
     goto err;
   }
+  exact_name_str= udf->name.str;
+  exact_name_len= udf->name.length;
   del_udf(udf);
   /*
     Close the handle if this was function that was found during boot or
@@ -535,7 +539,7 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
   tables.table_name= tables.alias= (char*) "func";
   if (!(table = open_ltable(thd,&tables,TL_WRITE)))
     goto err;
-  table->field[0]->store(udf_name->str, udf_name->length, system_charset_info);
+  table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin);
   table->file->extra(HA_EXTRA_RETRIEVE_ALL_COLS);
   if (!table->file->index_read_idx(table->record[0], 0,
 				   (byte*) table->field[0]->ptr,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 90dc6d54fe1..1fa23beb2f7 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -95,6 +95,187 @@ void turn_parser_debug_on()
 }
 #endif
 
+
+/**
+  Helper action for a case statement (entering the CASE).
+  This helper is used for both 'simple' and 'searched' cases.
+  This helper, with the other case_stmt_action_..., is executed when
+  the following SQL code is parsed:
+<pre>
+CREATE PROCEDURE proc_19194_simple(i int)
+BEGIN
+  DECLARE str CHAR(10);
+
+  CASE i
+    WHEN 1 THEN SET str="1";
+    WHEN 2 THEN SET str="2";
+    WHEN 3 THEN SET str="3";
+    ELSE SET str="unknown";
+  END CASE;
+
+  SELECT str;
+END
+</pre>
+  The actions are used to generate the following code:
+<pre>
+SHOW PROCEDURE CODE proc_19194_simple;
+Pos     Instruction
+0       set str@1 NULL
+1       set_case_expr (12) 0 i@0
+2       jump_if_not 5(12) (case_expr@0 = 1)
+3       set str@1 _latin1'1'
+4       jump 12
+5       jump_if_not 8(12) (case_expr@0 = 2)
+6       set str@1 _latin1'2'
+7       jump 12
+8       jump_if_not 11(12) (case_expr@0 = 3)
+9       set str@1 _latin1'3'
+10      jump 12
+11      set str@1 _latin1'unknown'
+12      stmt 0 "SELECT str"
+</pre>
+
+  @param lex the parser lex context
+*/
+
+void case_stmt_action_case(LEX *lex)
+{
+  lex->sphead->new_cont_backpatch(NULL);
+
+  /*
+    BACKPATCH: Creating target label for the jump to
+    "case_stmt_action_end_case"
+    (Instruction 12 in the example)
+  */
+
+  lex->spcont->push_label((char *)"", lex->sphead->instructions());
+}
+
+/**
+  Helper action for a case expression statement (the expr in 'CASE expr').
+  This helper is used for 'searched' cases only.
+  @param lex the parser lex context
+  @param expr the parsed expression
+  @return 0 on success
+*/
+
+int case_stmt_action_expr(LEX *lex, Item* expr)
+{
+  sp_head *sp= lex->sphead;
+  sp_pcontext *parsing_ctx= lex->spcont;
+  int case_expr_id= parsing_ctx->register_case_expr();
+  sp_instr_set_case_expr *i;
+
+  if (parsing_ctx->push_case_expr_id(case_expr_id))
+    return 1;
+
+  i= new sp_instr_set_case_expr(sp->instructions(),
+                                parsing_ctx, case_expr_id, expr, lex);
+
+  sp->add_cont_backpatch(i);
+  sp->add_instr(i);
+
+  return 0;
+}
+
+/**
+  Helper action for a case when condition.
+  This helper is used for both 'simple' and 'searched' cases.
+  @param lex the parser lex context
+  @param when the parsed expression for the WHEN clause
+  @param simple true for simple cases, false for searched cases
+*/
+
+void case_stmt_action_when(LEX *lex, Item *when, bool simple)
+{
+  sp_head *sp= lex->sphead;
+  sp_pcontext *ctx= lex->spcont;
+  uint ip= sp->instructions();
+  sp_instr_jump_if_not *i;
+  Item_case_expr *var;
+  Item *expr;
+
+  if (simple)
+  {
+    var= new Item_case_expr(ctx->get_current_case_expr_id());
+
+#ifndef DBUG_OFF
+    if (var)
+    {
+      var->m_sp= sp;
+    }
+#endif
+
+    expr= new Item_func_eq(var, when);
+    i= new sp_instr_jump_if_not(ip, ctx, expr, lex);
+  }
+  else
+    i= new sp_instr_jump_if_not(ip, ctx, when, lex);
+
+  /*
+    BACKPATCH: Registering forward jump from
+    "case_stmt_action_when" to "case_stmt_action_then"
+    (jump_if_not from instruction 2 to 5, 5 to 8 ... in the example)
+  */
+
+  sp->push_backpatch(i, ctx->push_label((char *)"", 0));
+  sp->add_cont_backpatch(i);
+  sp->add_instr(i);
+}
+
+/**
+  Helper action for a case then statements.
+  This helper is used for both 'simple' and 'searched' cases.
+  @param lex the parser lex context
+*/
+
+void case_stmt_action_then(LEX *lex)
+{
+  sp_head *sp= lex->sphead;
+  sp_pcontext *ctx= lex->spcont;
+  uint ip= sp->instructions();
+  sp_instr_jump *i = new sp_instr_jump(ip, ctx);
+  sp->add_instr(i);
+
+  /*
+    BACKPATCH: Resolving forward jump from
+    "case_stmt_action_when" to "case_stmt_action_then"
+    (jump_if_not from instruction 2 to 5, 5 to 8 ... in the example)
+  */
+
+  sp->backpatch(ctx->pop_label());
+
+  /*
+    BACKPATCH: Registering forward jump from
+    "case_stmt_action_then" to "case_stmt_action_end_case"
+    (jump from instruction 4 to 12, 7 to 12 ... in the example)
+  */
+
+  sp->push_backpatch(i, ctx->last_label());
+}
+
+/**
+  Helper action for an end case.
+  This helper is used for both 'simple' and 'searched' cases.
+  @param lex the parser lex context
+  @param simple true for simple cases, false for searched cases
+*/
+
+void case_stmt_action_end_case(LEX *lex, bool simple)
+{
+  /*
+    BACKPATCH: Resolving forward jump from
+    "case_stmt_action_then" to "case_stmt_action_end_case"
+    (jump from instruction 4 to 12, 7 to 12 ... in the example)
+  */
+  lex->sphead->backpatch(lex->spcont->pop_label());
+
+  if (simple)
+    lex->spcont->pop_case_expr_id();
+
+  lex->sphead->do_cont_backpatch();
+}
+
 %}
 %union {
   int  num;
@@ -831,7 +1012,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 	select_item_list select_item values_list no_braces
 	opt_limit_clause delete_limit_clause fields opt_values values
 	procedure_list procedure_list2 procedure_item
-	when_list2 expr_list2 udf_expr_list3 handler
+	expr_list2 udf_expr_list3 handler
 	opt_precision opt_ignore opt_column opt_restrict
 	grant revoke set lock unlock string_list field_options field_option
 	field_opt_list opt_binary table_lock_list table_lock
@@ -859,6 +1040,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         view_algorithm view_or_trigger_or_sp view_or_trigger_or_sp_tail
 	view_suid view_tail view_list_opt view_list view_select
 	view_check_option trigger_tail sp_tail
+        case_stmt_specification simple_case_stmt searched_case_stmt
 END_OF_INPUT
 
 %type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt
@@ -1993,43 +2175,7 @@ sp_proc_stmt:
           { Lex->sphead->new_cont_backpatch(NULL); }
           sp_if END IF
           { Lex->sphead->do_cont_backpatch(); }
-	| CASE_SYM WHEN_SYM
-	  {
-	    Lex->sphead->m_flags&= ~sp_head::IN_SIMPLE_CASE;
-            Lex->sphead->new_cont_backpatch(NULL);
-	  }
-          sp_case END CASE_SYM { Lex->sphead->do_cont_backpatch(); }
-        | CASE_SYM
-          {
-            Lex->sphead->reset_lex(YYTHD);
-            Lex->sphead->new_cont_backpatch(NULL);
-          }
-          expr WHEN_SYM
-	  {
-	    LEX *lex= Lex;
-	    sp_head *sp= lex->sphead;
-	    sp_pcontext *parsing_ctx= lex->spcont;
-	    int case_expr_id= parsing_ctx->register_case_expr();
-            sp_instr_set_case_expr *i;
-	    
-	    if (parsing_ctx->push_case_expr_id(case_expr_id))
-              YYABORT;
-
-            i= new sp_instr_set_case_expr(sp->instructions(),
-                                          parsing_ctx,
-                                          case_expr_id,
-                                          $3,
-                                          lex);
-            sp->add_cont_backpatch(i);
-            sp->add_instr(i);
-	    sp->m_flags|= sp_head::IN_SIMPLE_CASE;
-	    sp->restore_lex(YYTHD);
-	  }
-	  sp_case END CASE_SYM
-	  {
-	    Lex->spcont->pop_case_expr_id();
-            Lex->sphead->do_cont_backpatch();
-	  }
+        | case_stmt_specification
 	| sp_labeled_control
 	  {}
 	| { /* Unlabeled controls get a secret label. */
@@ -2240,72 +2386,114 @@ sp_elseifs:
 	| ELSE sp_proc_stmts1
 	;
 
-sp_case:
-	  { Lex->sphead->reset_lex(YYTHD); }
-          expr THEN_SYM
-	  {
-            LEX *lex= Lex;
-	    sp_head *sp= lex->sphead;
-	    sp_pcontext *ctx= Lex->spcont;
-	    uint ip= sp->instructions();
-	    sp_instr_jump_if_not *i;
-
-	    if (! (sp->m_flags & sp_head::IN_SIMPLE_CASE))
-	      i= new sp_instr_jump_if_not(ip, ctx, $2, lex);
-	    else
-	    { /* Simple case: <caseval> = <whenval> */
+case_stmt_specification:
+          simple_case_stmt
+        | searched_case_stmt
+        ;
 
-	      Item_case_expr *var;
-              Item *expr;
+simple_case_stmt:
+          CASE_SYM
+          {
+            LEX *lex= Lex;
+            case_stmt_action_case(lex);
+            lex->sphead->reset_lex(YYTHD); /* For expr $3 */
+          }
+          expr
+          {
+            LEX *lex= Lex;
+            if (case_stmt_action_expr(lex, $3))
+              YYABORT;
 
-              var= new Item_case_expr(ctx->get_current_case_expr_id());
+            lex->sphead->restore_lex(YYTHD); /* For expr $3 */
+          }
+          simple_when_clause_list
+          else_clause_opt
+          END
+          CASE_SYM
+          {
+            LEX *lex= Lex;
+            case_stmt_action_end_case(lex, true);
+          }
+        ;
 
-#ifndef DBUG_OFF
-              if (var)
-                var->m_sp= sp;
-#endif
+searched_case_stmt:
+          CASE_SYM
+          {
+            LEX *lex= Lex;
+            case_stmt_action_case(lex);
+          }
+          searched_when_clause_list
+          else_clause_opt
+          END
+          CASE_SYM
+          {
+            LEX *lex= Lex;
+            case_stmt_action_end_case(lex, false);
+          }
+        ;
 
-	      expr= new Item_func_eq(var, $2);
+simple_when_clause_list:
+          simple_when_clause
+        | simple_when_clause_list simple_when_clause
+        ;
 
-	      i= new sp_instr_jump_if_not(ip, ctx, expr, lex);
-	    }
-	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
-            sp->add_cont_backpatch(i);
-            sp->add_instr(i);
-            sp->restore_lex(YYTHD);
-	  }
-	  sp_proc_stmts1
-	  {
-	    sp_head *sp= Lex->sphead;
-	    sp_pcontext *ctx= Lex->spcont;
-	    uint ip= sp->instructions();
-	    sp_instr_jump *i = new sp_instr_jump(ip, ctx);
+searched_when_clause_list:
+          searched_when_clause
+        | searched_when_clause_list searched_when_clause
+        ;
 
-	    sp->add_instr(i);
-	    sp->backpatch(ctx->pop_label());
-	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
-	  }
-	  sp_whens
-	  {
-	    LEX *lex= Lex;
+simple_when_clause:
+          WHEN_SYM
+          {
+            Lex->sphead->reset_lex(YYTHD); /* For expr $3 */
+          }
+          expr
+          {
+            /* Simple case: <caseval> = <whenval> */
 
-	    lex->sphead->backpatch(lex->spcont->pop_label());
-	  }
-	;
+            LEX *lex= Lex;
+            case_stmt_action_when(lex, $3, true);
+            lex->sphead->restore_lex(YYTHD); /* For expr $3 */
+          }
+          THEN_SYM
+          sp_proc_stmts1
+          {
+            LEX *lex= Lex;
+            case_stmt_action_then(lex);
+          }
+        ;
 
-sp_whens:
-	  /* Empty */
-	  {
-	    sp_head *sp= Lex->sphead;
-	    uint ip= sp->instructions();
-	    sp_instr_error *i= new sp_instr_error(ip, Lex->spcont,
-						  ER_SP_CASE_NOT_FOUND);
+searched_when_clause:
+          WHEN_SYM
+          {
+            Lex->sphead->reset_lex(YYTHD); /* For expr $3 */
+          }
+          expr
+          {
+            LEX *lex= Lex;
+            case_stmt_action_when(lex, $3, false);
+            lex->sphead->restore_lex(YYTHD); /* For expr $3 */
+          }
+          THEN_SYM
+          sp_proc_stmts1
+          {
+            LEX *lex= Lex;
+            case_stmt_action_then(lex);
+          }
+        ;
 
-	    sp->add_instr(i);
-	  }
-	| ELSE sp_proc_stmts1 {}
-	| WHEN_SYM sp_case {}
-	;
+else_clause_opt:
+          /* empty */
+          {
+            LEX *lex= Lex;
+            sp_head *sp= lex->sphead;
+            uint ip= sp->instructions();
+            sp_instr_error *i= new sp_instr_error(ip, lex->spcont,
+                                                  ER_SP_CASE_NOT_FOUND);
+            sp->add_instr(i);
+          }
+        | ELSE sp_proc_stmts1
+        ;
 
 sp_labeled_control:
 	  label_ident ':'
@@ -4372,8 +4560,8 @@ simple_expr:
             if (!$$)
               YYABORT;
 	  }
-	| CASE_SYM opt_expr WHEN_SYM when_list opt_else END
-	  { $$= new Item_func_case(* $4, $2, $5 ); }
+	| CASE_SYM opt_expr when_list opt_else END
+	  { $$= new Item_func_case(* $3, $2, $4 ); }
 	| CONVERT_SYM '(' expr ',' cast_type ')'
 	  {
 	    $$= create_func_cast($3, $5,
@@ -5182,23 +5370,19 @@ opt_else:
 	| ELSE expr    { $$= $2; };
 
 when_list:
-        { Select->when_list.push_front(new List<Item>); }
-	when_list2
-	{ $$= Select->when_list.pop(); };
-
-when_list2:
-	expr THEN_SYM expr
-	  {
-	    SELECT_LEX *sel=Select;
-	    sel->when_list.head()->push_back($1);
-	    sel->when_list.head()->push_back($3);
-	}
-	| when_list2 WHEN_SYM expr THEN_SYM expr
-	  {
-	    SELECT_LEX *sel=Select;
-	    sel->when_list.head()->push_back($3);
-	    sel->when_list.head()->push_back($5);
-	  };
+          WHEN_SYM expr THEN_SYM expr
+          {
+            $$= new List<Item>;
+            $$->push_back($2);
+            $$->push_back($4);
+          }
+        | when_list WHEN_SYM expr THEN_SYM expr
+          {
+            $1->push_back($3);
+            $1->push_back($5);
+            $$= $1;
+          }
+        ;
 
 /* Warning - may return NULL in case of incomplete SELECT */
 table_ref:
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 8918992bfc3..1937f74f797 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -15531,6 +15531,33 @@ static void test_bug21635()
   DBUG_VOID_RETURN;
 }
 
+/*
+  Bug#24179 "select b into $var" fails with --cursor_protocol"
+  The failure is correct, check that the returned message is meaningful.
+*/
+
+static void test_bug24179()
+{
+  int rc;
+  MYSQL_STMT *stmt;
+
+  DBUG_ENTER("test_bug24179");
+  myheader("test_bug24179");
+
+  stmt= open_cursor("select 1 into @a");
+  rc= mysql_stmt_execute(stmt);
+  DIE_UNLESS(rc);
+  if (!opt_silent)
+  {
+    printf("Got error (as expected): %d %s\n",
+           mysql_stmt_errno(stmt),
+           mysql_stmt_error(stmt));
+  }
+  DIE_UNLESS(mysql_stmt_errno(stmt) == 1323);
+
+  DBUG_VOID_RETURN;
+}
+
 
 /*
   Read and parse arguments and MySQL options from my.cnf
@@ -15811,6 +15838,7 @@ static struct my_tests_st my_tests[]= {
   { "test_bug15518", test_bug15518 },
   { "test_bug23383", test_bug23383 },
   { "test_bug21635", test_bug21635 },
+  { "test_bug24179", test_bug24179 },
   { 0, 0 }
 };