Merge mysql.com:/home/gluh/MySQL/Merge/5.0

into  mysql.com:/home/gluh/MySQL/Merge/5.0-opt


sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_union.cc:
  Auto merged
sql/sql_update.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

33 files changed, 779 insertions, 126 deletions

diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 33398d1ddd7..91c385112b4 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1442,7 +1442,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	c	c	11	const	#	Using where
 explain select count(*) from t1 where t='a  ';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	t	t	13	NULL	#	Using where
+1	SIMPLE	t1	ref	t	t	13	const	#	Using where
 explain select count(*) from t1 where v like 'a%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	v	v	13	NULL	#	Using where
diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result
index 140d7e73d48..0ee5f326e0f 100644
--- a/mysql-test/r/blackhole.result
+++ b/mysql-test/r/blackhole.result
@@ -123,3 +123,14 @@ master-bin.000001	#	Query	1	#	use `test`; create table t3 like t1
 master-bin.000001	#	Query	1	#	use `test`; insert into t1 select * from t3
 master-bin.000001	#	Query	1	#	use `test`; replace into t1 select * from t3
 drop table t1,t2,t3;
+CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 ADD INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD UNIQUE INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+DELETE FROM t1 WHERE a=10;
+DROP TABLE t1;
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
index 0e68418a80f..003ee7ffd5e 100644
--- a/mysql-test/r/endspace.result
+++ b/mysql-test/r/endspace.result
@@ -98,7 +98,7 @@ concat('|', text1, '|')
 |teststring |
 explain select concat('|', text1, '|') from t1 where text1='teststring ';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	key1	key1	22	NULL	2	Using where
+1	SIMPLE	t1	ref	key1	key1	22	const	2	Using where
 select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
 concat('|', text1, '|')
 |teststring	|
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 38d71ac7a42..496c8aa90d2 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1991,7 +1991,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	c	c	11	const	#	Using where; Using index
 explain select count(*) from t1 where t='a  ';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	t	t	13	NULL	#	Using where
+1	SIMPLE	t1	ref	t	t	13	const	#	Using where
 explain select count(*) from t1 where v like 'a%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	v	v	13	NULL	#	Using where; Using index
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 2e0acf303c2..92b3ea0e42b 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -731,3 +731,16 @@ select @@identity;
 @@identity
 0
 drop table t1;
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2  (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+SELECT f1, f1 FROM t2 src WHERE f1 < 2
+ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+f1	f2
+101	1
+2	2
+10	10
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 6be37450835..f658ff06624 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -219,3 +219,20 @@ SELECT * FROM t1;
 a	b
 45	2
 DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+INSERT INTO t1 SELECT 1, j;
+ERROR 42S22: Unknown column 'j' in 'field list'
+DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
+ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (1), (3);
+INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
+ERROR 42S22: Unknown column 'a' in 'field list'
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 0f6e0ad537a..83359048a8c 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1071,7 +1071,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	c	c	11	const	#	Using where; Using index
 explain select count(*) from t1 where t='a  ';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	t	t	13	NULL	#	Using where
+1	SIMPLE	t1	ref	t	t	13	const	#	Using where
 explain select count(*) from t1 where v like 'a%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	v	v	13	NULL	#	Using where; Using index
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index f50f4859d48..c3132a1b5f6 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3785,4 +3785,152 @@ case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
 coalesce(cast(1111111111111111111 as unsigned), 1) co;
 i	c	co
 1111111111111111111	1111111111111111111	1111111111111111111
+CREATE TABLE t1 (name varchar(255));
+CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name	n
+bb	1
+aa	2
+cc   	3
+cc 	4
+cc	5
+bb 	6
+cc 	7
+SELECT * FROM t2 ORDER BY name;
+name	n
+aa	2
+bb	1
+bb 	6
+cc 	4
+cc   	3
+cc	5
+cc 	7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name	LENGTH(name)	n
+aa	2	2
+bb	2	1
+bb 	3	6
+cc 	4	4
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	name	name	6	const	3	Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name	LENGTH(name)	n
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	name	name	6	NULL	3	Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name	LENGTH(name)	n
+cc   	5	3
+cc 	4	4
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	name	name	6	NULL	3	Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name	LENGTH(name)	n
+cc 	4	4
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name	name	n
+ccc	NULL	NULL
+bb	bb	1
+bb	bb 	6
+cc 	cc   	3
+cc 	cc	5
+cc 	cc 	7
+aa  	aa	2
+aa	aa	2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (name text);
+CREATE TABLE t2 (name text, n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name	n
+bb	1
+aa	2
+cc   	3
+cc 	4
+cc	5
+bb 	6
+cc 	7
+SELECT * FROM t2 ORDER BY name;
+name	n
+aa	2
+bb	1
+bb 	6
+cc 	4
+cc   	3
+cc	5
+cc 	7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name	LENGTH(name)	n
+aa	2	2
+bb	2	1
+bb 	3	6
+cc 	4	4
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	name	name	6	const	3	Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name	LENGTH(name)	n
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	name	name	6	NULL	3	Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name	LENGTH(name)	n
+cc   	5	3
+cc 	4	4
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	name	name	6	NULL	3	Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name	LENGTH(name)	n
+cc 	4	4
+cc   	5	3
+cc	2	5
+cc 	3	7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name	name	n
+ccc	NULL	NULL
+bb	bb	1
+bb	bb 	6
+cc 	cc   	3
+cc 	cc	5
+cc 	cc 	7
+aa  	aa	2
+aa	aa	2
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 06f8c019265..7177b4e4432 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -224,7 +224,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 Warnings:
-Note	1276	Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
 Note	1003	select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
 select * from t3 where exists (select * from t2 where t2.b=t3.a);
 a
@@ -313,8 +313,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1276	Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
-Note	1276	Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
 Note	1003	select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 ERROR 21000: Subquery returns more than 1 row
@@ -330,9 +330,9 @@ patient_uq	clinic_uq
 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t6	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	Using index
+2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	t6.clinic_uq	1	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
 ERROR 23000: Column 'a' in field list is ambiguous
@@ -868,7 +868,7 @@ explain extended select (select a+1) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 Warnings:
-Note	1276	Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1249	Select 2 was reduced during optimization
 Note	1003	select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
 select (select a+1) from t1;
@@ -1741,9 +1741,9 @@ Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	Using where
-2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	tt.id	1	Using where; Using index
 Warnings:
-Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
 insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
 create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
@@ -2279,7 +2279,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	up	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 Warnings:
-Note	1276	Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
 drop table t1;
 CREATE TABLE t1 (t1_a int);
@@ -3712,3 +3712,45 @@ bb	2
 cc	3
 dd	1
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+sq
+2
+4
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+tt
+2
+2
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+sq
+2
+4
+EXECUTE stmt1;
+sq
+2
+4
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21), 
+(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+FROM t2 GROUP BY f2;
+f2	AVG(f21)	test
+1	1.0000	2004-02-29 11:11:11
+2	2.0000	2004-02-29 11:11:11
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES                                                         
+(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),                      
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),                      
+(3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),                                                             
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test      
+FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 29143b9e504..b975ea8cbdc 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -29,7 +29,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
 explain extended
 select a, oref from t2 
@@ -38,7 +38,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
 select a, oref, a in (
 select max(ie) from t1 where oref=t2.oref group by grp union
@@ -91,7 +91,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
 2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
 flush status;
 select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
@@ -156,7 +156,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	4	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.b	1	Using where
 Warnings:
-Note	1276	Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
 drop table t1, t2, t3;
 create table t1 (a int NOT NULL, b int NOT NULL, key(a));
@@ -184,7 +184,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref	a	a	4	func	2	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t2	ref	a	a	4	test.t1.b	1	Using where
 Warnings:
-Note	1276	Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
 drop table t1,t2,t3;
 create table t1 (oref int, grp int);
@@ -240,7 +240,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
 select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
 a	b	oref	Z
@@ -257,7 +257,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	Using where
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
 select a,b, oref, 
 (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
@@ -302,7 +302,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	8	Using where
 2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
 drop table t1, t2;
 create table t1 (oref char(4), grp int, ie int);
@@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie);
 explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
-2	DEPENDENT SUBQUERY	t1	ref_or_null	idx	idx	10	test.t2.oref,func	4	Using where; Using index; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t1	ref_or_null	idx	idx	10	t2.oref,func	4	Using where; Using index; Full scan on NULL key
 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 oref	a	Z
 ee	NULL	NULL
@@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z
 from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
-2	DEPENDENT SUBQUERY	t1	ref	idx	idx	5	test.t2.oref	2	Using where; Using temporary; Using filesort
+2	DEPENDENT SUBQUERY	t1	ref	idx	idx	5	t2.oref	2	Using where; Using temporary; Using filesort
 select oref, a, 
 a in (select min(ie) from t1 where oref=t2.oref 
 group by grp having min(ie) > 1) Z 
@@ -572,7 +572,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
 2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
 drop table t1,t2;
 create table t1 (oref char(4), grp int, ie int primary key);
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 73b67a2241e..b28f404c641 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -610,12 +610,12 @@ create table t1 (id integer primary key auto_increment, txt text, index txt_inde
 insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
 select * from t1 where txt='Chevy' or txt is NULL;
 id	txt
-3	NULL
 1	Chevy
 2	Chevy 
+3	NULL
 explain select * from t1 where txt='Chevy' or txt is NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	txt_index	txt_index	23	NULL	2	Using where
+1	SIMPLE	t1	ref_or_null	txt_index	txt_index	23	const	2	Using where
 select * from t1 where txt='Chevy ';
 id	txt
 1	Chevy
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 9e2bc52657a..b3c5760e2c9 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -434,3 +434,22 @@ Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_next	21
 DROP TABLE t1;
+CREATE TABLE t1 (
+a INT(11),
+quux decimal( 31, 30 ),
+UNIQUE KEY bar (a),
+KEY quux (quux)
+);
+INSERT INTO
+t1 ( a, quux )
+VALUES
+( 1,    1 ),
+( 2,  0.1 );
+INSERT INTO t1( a )
+SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
+SELECT * FROM t1;
+a	quux
+1	1.000000000000000000000000000000
+2	0.100000000000000000000000000000
+3	NULL
+DROP TABLE t1;
diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test
index e40b84eb5cd..4bafad2d777 100644
--- a/mysql-test/t/blackhole.test
+++ b/mysql-test/t/blackhole.test
@@ -127,4 +127,19 @@ show binlog events;
 
 drop table t1,t2,t3;
 
+#
+#Bug#19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX
+#
+CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 ADD INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD UNIQUE INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+DELETE FROM t1 WHERE a=10;
+DROP TABLE t1;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 5c60fc8e1f0..31508b3d6c4 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -292,3 +292,18 @@ select @@identity;
 insert ignore t1(f2) select 1;
 select @@identity;
 drop table t1;
+
+#
+# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
+#
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2  (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+  SELECT f1, f1 FROM t2 src WHERE f1 < 2
+  ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+DROP TABLE t1, t2;
+
+ 
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index b3813864464..4581cc7a875 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -139,3 +139,26 @@ INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
 SELECT * FROM t1;
 
 DROP TABLE t1;
+
+#
+# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving.
+#
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 SELECT 1, j;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
+  ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (1), (3);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index c4737814137..ea5fadb2e1b 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3253,4 +3253,50 @@ select
   case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
   coalesce(cast(1111111111111111111 as unsigned), 1) co;
 
+#
+# Bug #22971: indexes on text columns are ignored for ref accesses 
+#
+
+CREATE TABLE t1 (name varchar(255));
+CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+SELECT * FROM t2 ORDER BY name;
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (name text);
+CREATE TABLE t2 (name text, n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+SELECT * FROM t2 ORDER BY name;
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d8478bae258..11cec291d8c 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2600,4 +2600,31 @@ SELECT * FROM t1
                 UNION 
                 SELECT c from t2 WHERE c=t1.c);
 
-DROP TABLE t1,t2,t3;
+DROP TABLE t1,t2,t3;                                                                               
+#                                                                             
+# Bug#23800: Outer fields in correlated subqueries is used in a temporary     
+#            table created for sorting.                                       
+#                                                                             
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+EXECUTE stmt1;
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21), 
+      (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+  FROM t2 GROUP BY f2;
+DROP TABLE t1,t2;                                                             
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);                          
+INSERT INTO t1 VALUES                                                         
+  (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),                      
+  (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),                      
+  (3,2,'k'), (3,1,'l'), (1,9,'m');                                            
+SELECT a, MAX(b),                                                             
+      (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test      
+  FROM t1 GROUP BY a;                                                         
+DROP TABLE t1;      
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 3ce7ef72670..23ee75d61ea 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -342,3 +342,29 @@ UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
 show status like '%Handler_read%';
 
 DROP TABLE t1;
+
+#
+# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it 
+# doesn't select
+#
+CREATE TABLE t1 (
+
+  a INT(11),
+  quux decimal( 31, 30 ),
+
+  UNIQUE KEY bar (a),
+  KEY quux (quux)
+);
+
+INSERT INTO
+ t1 ( a, quux )
+VALUES
+    ( 1,    1 ),
+    ( 2,  0.1 );
+
+INSERT INTO t1( a )
+  SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/sql/ha_blackhole.cc b/sql/ha_blackhole.cc
index 61a8658be53..3f4285ec595 100644
--- a/sql/ha_blackhole.cc
+++ b/sql/ha_blackhole.cc
@@ -180,7 +180,7 @@ int ha_blackhole::index_read(byte * buf, const byte * key,
                              uint key_len, enum ha_rkey_function find_flag)
 {
   DBUG_ENTER("ha_blackhole::index_read");
-  DBUG_RETURN(0);
+  DBUG_RETURN(HA_ERR_END_OF_FILE);
 }
 
 
diff --git a/sql/item.cc b/sql/item.cc
index 4c346070f10..257687ebaaf 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1607,7 +1607,7 @@ void Item_ident_for_show::make_field(Send_field *tmp_field)
 Item_field::Item_field(Field *f)
   :Item_ident(0, NullS, *f->table_name, f->field_name),
   item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
   set_field(f);
   /*
@@ -1621,7 +1621,7 @@ Item_field::Item_field(THD *thd, Name_resolution_context *context_arg,
                        Field *f)
   :Item_ident(context_arg, f->table->s->db, *f->table_name, f->field_name),
    item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
   /*
     We always need to provide Item_field with a fully qualified field
@@ -1660,9 +1660,12 @@ Item_field::Item_field(Name_resolution_context *context_arg,
                        const char *field_name_arg)
   :Item_ident(context_arg, db_arg,table_name_arg,field_name_arg),
    field(0), result_field(0), item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
+  SELECT_LEX *select= current_thd->lex->current_select;
   collation.set(DERIVATION_IMPLICIT);
+  if (select && select->parsing_place != IN_HAVING)
+      select->select_n_where_fields++;
 }
 
 // Constructor need to process subselect with temporary tables (see Item)
@@ -1673,7 +1676,8 @@ Item_field::Item_field(THD *thd, Item_field *item)
    item_equal(item->item_equal),
    no_const_subst(item->no_const_subst),
    have_privileges(item->have_privileges),
-   any_privileges(item->any_privileges)
+   any_privileges(item->any_privileges),
+   fixed_as_field(item->fixed_as_field)
 {
   collation.set(DERIVATION_IMPLICIT);
 }
@@ -3483,8 +3487,46 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
         }
         if (*from_field != view_ref_found)
         {
+
           prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
           prev_subselect_item->const_item_cache= 0;
+          if (!last_checked_context->select_lex->having_fix_field &&
+              !fixed_as_field)
+          {
+            Item_outer_ref *rf;
+            Query_arena *arena= 0, backup;
+            /*
+              Each outer field is replaced for an Item_outer_ref object.
+              This is done in order to get correct results when the outer
+              select employs a temporary table.
+              The original fields are saved in the inner_fields_list of the
+              outer select. This list is created by the following reasons:
+              1. We can't add field items to the outer select list directly
+                 because the outer select hasn't been fully fixed yet.
+              2. We need a location to refer to in the Item_ref object
+                 so the inner_fields_list is used as such temporary
+                 reference storage.
+              The new Item_outer_ref object replaces the original field and is
+              also saved in the inner_refs_list of the outer select. Here
+              it is only created. It can be fixed only after the original
+              field has been fixed and this is done in the fix_inner_refs()
+              function.
+            */
+            set_field(*from_field);
+            arena= thd->activate_stmt_arena_if_needed(&backup);
+            rf= new Item_outer_ref(context, this);
+            if (!rf)
+            {
+              if (arena)
+                thd->restore_active_arena(arena, &backup);
+              return -1;
+            }
+            *reference= rf;
+            select->inner_refs_list.push_back(rf);
+            if (arena)
+              thd->restore_active_arena(arena, &backup);
+            fixed_as_field= 1;
+          }
           if (thd->lex->in_sum_func &&
               thd->lex->in_sum_func->nest_level == 
               thd->lex->current_select->nest_level)
@@ -3612,7 +3654,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
   {
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex,
-                      this, this);
+                      this, (Item_ident*)*reference);
     if (last_checked_context->select_lex->having_fix_field)
     {
       Item_ref *rf;
@@ -4809,6 +4851,51 @@ void Item_field::update_null_value()
 }
 
 
+/*
+  Add the field to the select list and substitute it for the reference to
+  the field.
+
+  SYNOPSIS
+    Item_field::update_value_transformer()
+    select_arg      current select
+
+  DESCRIPTION
+    If the field doesn't belong to the table being inserted into then it is
+    added to the select list, pointer to it is stored in the ref_pointer_array
+    of the select and the field itself is substituted for the Item_ref object.
+    This is done in order to get correct values from update fields that
+    belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY
+    UPDATE statement.
+
+  RETURN
+    0             if error occured
+    ref           if all conditions are met
+    this field    otherwise
+*/
+
+Item *Item_field::update_value_transformer(byte *select_arg)
+{
+  SELECT_LEX *select= (SELECT_LEX*)select_arg;
+  DBUG_ASSERT(fixed);
+
+  if (field->table != select->context.table_list->table &&
+      type() != Item::TRIGGER_FIELD_ITEM)
+  {
+    List<Item> *all_fields= &select->join->all_fields;
+    Item **ref_pointer_array= select->ref_pointer_array;
+    int el= all_fields->elements;
+    Item_ref *ref;
+
+    ref_pointer_array[el]= (Item*)this;
+    all_fields->push_front((Item*)this);
+    ref= new Item_ref(&select->context, ref_pointer_array + el,
+                      table_name, field_name);
+    return ref;
+  }
+  return this;
+}
+
+
 Item_ref::Item_ref(Name_resolution_context *context_arg,
                    Item **item, const char *table_name_arg,
                    const char *field_name_arg)
@@ -4818,8 +4905,7 @@ Item_ref::Item_ref(Name_resolution_context *context_arg,
   /*
     This constructor used to create some internals references over fixed items
   */
-  DBUG_ASSERT(ref != 0);
-  if (*ref && (*ref)->fixed)
+  if (ref && *ref && (*ref)->fixed)
     set_properties();
 }
 
@@ -5120,7 +5206,7 @@ void Item_ref::print(String *str)
   if (ref)
   {
     if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
-        name && alias_name_used)
+        ref_type() != OUTER_REF && name && alias_name_used)
     {
       THD *thd= current_thd;
       append_identifier(thd, str, name, (uint) strlen(name));
@@ -5368,7 +5454,7 @@ bool Item_direct_ref::get_date(TIME *ltime,uint fuzzydate)
 
 
 /*
-  Prepare referenced view viewld then call usual Item_direct_ref::fix_fields
+  Prepare referenced field then call usual Item_direct_ref::fix_fields
 
   SYNOPSIS
     Item_direct_view_ref::fix_fields()
@@ -5392,6 +5478,31 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference)
 }
 
 /*
+  Prepare referenced outer field then call usual Item_direct_ref::fix_fields
+
+  SYNOPSIS
+    Item_outer_ref::fix_fields()
+    thd         thread handler
+    reference   reference on reference where this item stored
+
+  RETURN
+    FALSE   OK
+    TRUE    Error
+*/
+
+bool Item_outer_ref::fix_fields(THD *thd, Item **reference)
+{
+  DBUG_ASSERT(*ref);
+  /* outer_field->check_cols() will be made in Item_direct_ref::fix_fields */
+  outer_field->fixed_as_field= 1;
+  if (!outer_field->fixed &&
+      (outer_field->fix_fields(thd, reference)))
+    return TRUE;
+  table_name= outer_field->table_name;
+  return Item_direct_ref::fix_fields(thd, reference);
+}
+
+/*
   Compare two view column references for equality.
 
   SYNOPSIS
diff --git a/sql/item.h b/sql/item.h
index c005b8f32d4..833bebdee7e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -325,10 +325,10 @@ private:
   TABLE_LIST *save_first_name_resolution_table;
   TABLE_LIST *save_next_name_resolution_table;
   bool        save_resolve_in_select_list;
+  TABLE_LIST *save_next_local;
 
 public:
   Name_resolution_context_state() {}          /* Remove gcc warning */
-  TABLE_LIST *save_next_local;
 
 public:
   /* Save the state of a name resolution context. */
@@ -350,6 +350,11 @@ public:
     context->first_name_resolution_table=  save_first_name_resolution_table;
     context->resolve_in_select_list=       save_resolve_in_select_list;
   }
+
+  TABLE_LIST *get_first_name_resolution_table()
+  {
+    return save_first_name_resolution_table;
+  }
 };
 
 /*************************************************************************/
@@ -817,6 +822,7 @@ public:
   virtual Item_field *filed_for_view_update() { return 0; }
 
   virtual Item *neg_transformer(THD *thd) { return NULL; }
+  virtual Item *update_value_transformer(byte *select_arg) { return this; }
   virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
   void delete_self()
   {
@@ -1215,7 +1221,7 @@ public:
   uint have_privileges;
   /* field need any privileges (for VIEW creation) */
   bool any_privileges;
-
+  bool fixed_as_field;
   Item_field(Name_resolution_context *context_arg,
              const char *db_arg,const char *table_name_arg,
 	     const char *field_name_arg);
@@ -1296,6 +1302,7 @@ public:
   Item_field *filed_for_view_update() { return this; }
   Item *safe_charset_converter(CHARSET_INFO *tocs);
   int fix_outer_field(THD *thd, Field **field, Item **reference);
+  virtual Item *update_value_transformer(byte *select_arg);
   friend class Item_default_value;
   friend class Item_insert_value;
   friend class st_select_lex_unit;
@@ -1818,7 +1825,7 @@ class Item_ref :public Item_ident
 protected:
   void set_properties();
 public:
-  enum Ref_Type { REF, DIRECT_REF, VIEW_REF };
+  enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF };
   Field *result_field;			 /* Save result here */
   Item **ref;
   Item_ref(Name_resolution_context *context_arg,
@@ -1879,7 +1886,7 @@ public:
                           (*ref)->get_tmp_table_item(thd));
   }
   table_map used_tables() const		
-  { 
+  {
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
@@ -1952,6 +1959,40 @@ public:
 };
 
 
+class Item_outer_ref :public Item_direct_ref
+{
+public:
+  Item_field *outer_field;
+  Item_outer_ref(Name_resolution_context *context_arg,
+                 Item_field *outer_field_arg)
+    :Item_direct_ref(context_arg, 0, outer_field_arg->table_name,
+                          outer_field_arg->field_name),
+    outer_field(outer_field_arg)
+  {
+    ref= (Item**)&outer_field;
+    set_properties();
+    fixed= 0;
+  }
+  void cleanup()
+  {
+    ref= (Item**)&outer_field;
+    fixed= 0;
+    Item_direct_ref::cleanup();
+    outer_field->cleanup();
+  }
+  void save_in_result_field(bool no_conversions)
+  {
+    outer_field->save_org_in_field(result_field);
+  }
+  bool fix_fields(THD *, Item **);
+  table_map used_tables() const
+  {
+    return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
+  }
+  virtual Ref_Type ref_type() { return OUTER_REF; }
+};
+
+
 class Item_in_subselect;
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 05d98600b1f..59bae191230 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -754,6 +754,8 @@ int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 		List<Item> &fields, List<Item> &all_fields, ORDER *order,
 		bool *hidden_group_fields);
+bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                   Item **ref_pointer_array);
 
 bool handle_select(THD *thd, LEX *lex, select_result *result,
                    ulong setup_tables_done_option);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 767b955252c..630d135be48 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4528,21 +4528,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
   uint tablenr= 0;
   DBUG_ENTER("setup_tables");
 
-  /*
-    Due to the various call paths that lead to setup_tables() it may happen
-    that context->table_list and context->first_name_resolution_table can be
-    NULL (this is typically done when creating TABLE_LISTs internally).
-    TODO:
-    Investigate all cases when this my happen, initialize the name resolution
-    context correctly in all those places, and remove the context reset below.
-  */
-  if (!context->table_list || !context->first_name_resolution_table)
-  {
-    /* Test whether the context is in a consistent state. */
-    DBUG_ASSERT(!context->first_name_resolution_table && !context->table_list);
-    context->table_list= context->first_name_resolution_table= tables;
-  }
-
+  DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) || !tables || 
+               (context->table_list && context->first_name_resolution_table));
   /*
     this is used for INSERT ... SELECT.
     For select we setup tables except first (and its underlying tables)
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 94d753eb703..749ee04493b 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -353,6 +353,7 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
   Item *fake_conds= 0;
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   DBUG_ENTER("mysql_prepare_delete");
+  List<Item> all_fields;
 
   thd->lex->allow_sum_func= 0;
   if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
@@ -376,6 +377,11 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
       DBUG_RETURN(TRUE);
     }
   }
+
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
+
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index 78349a6ef0d..ba7f1a534ea 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -656,6 +656,8 @@ bool mysqld_help(THD *thd, const char *mask)
     Init tables and fields to be usable from items
     tables do not contain VIEWs => we can pass 0 as conds
   */
+  thd->lex->select_lex.context.table_list= 
+    thd->lex->select_lex.context.first_name_resolution_table= &tables[0];
   setup_tables(thd, &thd->lex->select_lex.context,
                &thd->lex->select_lex.top_join_list,
                tables, 0, &leaves, FALSE);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 3e7c3d15e78..f1edfb9d787 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -965,6 +965,8 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
   DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d",
 		       (ulong)table_list, (ulong)table,
 		       (int)insert_into_view));
+  /* INSERT should have a SELECT or VALUES clause */
+  DBUG_ASSERT (!select_insert || !values);
 
   /*
     For subqueries in VALUES() we should not see the table in which we are
@@ -997,44 +999,40 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
                                        select_insert))
     DBUG_RETURN(TRUE);
 
-  /* Save the state of the current name resolution context. */
-  ctx_state.save_state(context, table_list);
-
-  /*
-    Perform name resolution only in the first table - 'table_list',
-    which is the table that is inserted into.
-  */
-  table_list->next_local= 0;
-  context->resolve_in_table_list_only(table_list);
 
   /* Prepare the fields in the statement. */
-  if (values &&
-      !(res= check_insert_fields(thd, context->table_list, fields, *values,
-                                 !insert_into_view, &map) ||
-        setup_fields(thd, 0, *values, 0, 0, 0)) &&
-      duplic == DUP_UPDATE)
+  if (values)
   {
-    select_lex->no_wrap_view_item= TRUE;
-    res= check_update_fields(thd, context->table_list, update_fields, &map);
-    select_lex->no_wrap_view_item= FALSE;
+    /* if we have INSERT ... VALUES () we cannot have a GROUP BY clause */
+    DBUG_ASSERT (!select_lex->group_list.elements);
+
+    /* Save the state of the current name resolution context. */
+    ctx_state.save_state(context, table_list);
+
     /*
-      When we are not using GROUP BY we can refer to other tables in the
-      ON DUPLICATE KEY part.
-    */       
-    if (select_lex->group_list.elements == 0)
+      Perform name resolution only in the first table - 'table_list',
+      which is the table that is inserted into.
+     */
+    table_list->next_local= 0;
+    context->resolve_in_table_list_only(table_list);
+
+    if (!(res= check_insert_fields(thd, context->table_list, fields, *values,
+                                 !insert_into_view, &map) ||
+          setup_fields(thd, 0, *values, 0, 0, 0)) 
+        && duplic == DUP_UPDATE)
     {
-      context->table_list->next_local=       ctx_state.save_next_local;
-      /* first_name_resolution_table was set by resolve_in_table_list_only() */
-      context->first_name_resolution_table->
-        next_name_resolution_table=          ctx_state.save_next_local;
+      select_lex->no_wrap_view_item= TRUE;
+      res= check_update_fields(thd, context->table_list, update_fields, &map);
+      select_lex->no_wrap_view_item= FALSE;
     }
+
+    /* Restore the current context. */
+    ctx_state.restore_state(context, table_list);
+
     if (!res)
       res= setup_fields(thd, 0, update_values, 1, 0, 0);
   }
 
-  /* Restore the current context. */
-  ctx_state.restore_state(context, table_list);
-
   if (res)
     DBUG_RETURN(res);
 
@@ -2360,7 +2358,6 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
 
   if (info.handle_duplicates == DUP_UPDATE)
   {
-    /* Save the state of the current name resolution context. */
     Name_resolution_context *context= &lex->select_lex.context;
     Name_resolution_context_state ctx_state;
 
@@ -2376,18 +2373,38 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
                                     *info.update_fields, &map);
     lex->select_lex.no_wrap_view_item= FALSE;
     /*
-      When we are not using GROUP BY we can refer to other tables in the
-      ON DUPLICATE KEY part
+      When we are not using GROUP BY and there are no ungrouped aggregate functions 
+      we can refer to other tables in the ON DUPLICATE KEY part.
+      We use next_name_resolution_table descructively, so check it first (views?)
     */       
-    if (lex->select_lex.group_list.elements == 0)
-    {
-      context->table_list->next_local=       ctx_state.save_next_local;
-      /* first_name_resolution_table was set by resolve_in_table_list_only() */
-      context->first_name_resolution_table->
-        next_name_resolution_table=          ctx_state.save_next_local;
-    }
+    DBUG_ASSERT (!table_list->next_name_resolution_table);
+    if (lex->select_lex.group_list.elements == 0 &&
+        !lex->select_lex.with_sum_func)
+      /*
+        We must make a single context out of the two separate name resolution contexts :
+        the INSERT table and the tables in the SELECT part of INSERT ... SELECT.
+        To do that we must concatenate the two lists
+      */  
+      table_list->next_name_resolution_table= ctx_state.get_first_name_resolution_table();
+
     res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0);
+    if (!res)
+    {
+      /*
+        Traverse the update values list and substitute fields from the
+        select for references (Item_ref objects) to them. This is done in
+        order to get correct values from those fields when the select
+        employs a temporary table.
+      */
+      List_iterator<Item> li(*info.update_values);
+      Item *item;
 
+      while ((item= li++))
+      {
+        item->transform(&Item::update_value_transformer,
+                        (byte*)lex->current_select);
+      }
+    }
     /* Restore the current context. */
     ctx_state.restore_state(context, table_list);
   }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 1a7675c394e..cd4d2e85316 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1153,6 +1153,7 @@ void st_select_lex::init_query()
   cond_count= between_count= with_wild= 0;
   conds_processed_with_permanent_arena= 0;
   ref_pointer_array= 0;
+  select_n_where_fields= 0;
   select_n_having_items= 0;
   subquery_in_having= explicit_limit= 0;
   is_item_list_lookup= 0;
@@ -1194,6 +1195,7 @@ void st_select_lex::init_select()
   is_correlated= 0;
   cur_pos_in_select_list= UNDEF_POS;
   non_agg_fields.empty();
+  inner_refs_list.empty();
 }
 
 /*
@@ -1551,6 +1553,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
           (Item **)arena->alloc(sizeof(Item*) * (n_child_sum_items +
                                                  item_list.elements +
                                                  select_n_having_items +
+                                                 select_n_where_fields +
                                                  order_group_num)*5)) == 0;
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index afbb264112d..86d01675c2a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -531,6 +531,11 @@ public:
   uint select_n_having_items;
   uint cond_count;    /* number of arguments of and/or/xor in where/having/on */
   uint between_count; /* number of between predicates in where/having/on      */   
+  /*
+    Number of fields used in select list or where clause of current select
+    and all inner subselects.
+  */
+  uint select_n_where_fields;
   enum_parsing_place parsing_place; /* where we are parsing expression */
   bool with_sum_func;   /* sum function indicator */
   /* 
@@ -548,7 +553,8 @@ public:
   bool  braces;   	/* SELECT ... UNION (SELECT ... ) <- this braces */
   /* TRUE when having fix field called in processing of this SELECT */
   bool having_fix_field;
-
+  /* List of references to fields referenced from inner selects */
+  List<Item_outer_ref> inner_refs_list;
   /* Number of Item_sum-derived objects in this SELECT */
   uint n_sum_items;
   /* Number of Item_sum-derived objects in children and descendant SELECTs */
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 774b82ce618..0fdbd24e05c 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1570,21 +1570,16 @@ error:
 
 static bool mysql_insert_select_prepare_tester(THD *thd)
 {
-  TABLE_LIST *first;
-  bool res;
   SELECT_LEX *first_select= &thd->lex->select_lex;
+  TABLE_LIST *second_table= ((TABLE_LIST*)first_select->table_list.first)->
+    next_local;
+
   /* Skip first table, which is the table we are inserting in */
-  first_select->table_list.first= (byte*)(first=
-                                          ((TABLE_LIST*)first_select->
-                                           table_list.first)->next_local);
-  res= mysql_insert_select_prepare(thd);
-  /*
-    insert/replace from SELECT give its SELECT_LEX for SELECT,
-    and item_list belong to SELECT
-  */
-  thd->lex->select_lex.context.resolve_in_select_list= TRUE;
-  thd->lex->select_lex.context.table_list= first;
-  return res;
+  first_select->table_list.first= (byte *) second_table;
+  thd->lex->select_lex.context.table_list=
+    thd->lex->select_lex.context.first_name_resolution_table= second_table;
+
+  return mysql_insert_select_prepare(thd);
 }
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 63c8ddad3ae..a20e1f06147 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -268,6 +268,70 @@ bool handle_select(THD *thd, LEX *lex, select_result *result,
 
 
 /*
+  Fix fields referenced from inner selects.
+
+  SYNOPSIS
+    fix_inner_refs()
+    thd               Thread handle
+    all_fields        List of all fields used in select
+    select            Current select
+    ref_pointer_array Array of references to Items used in current select
+
+  DESCRIPTION
+    The function fixes fields referenced from inner selects and
+    also fixes references (Item_ref objects) to these fields. Each field
+    is fixed as a usual hidden field of the current select - it is added
+    to the all_fields list and the pointer to it is saved in the
+    ref_pointer_array if latter is provided.
+    After the field has been fixed we proceed with fixing references
+    (Item_ref objects) to this field from inner subqueries. If the
+    ref_pointer_array is provided then Item_ref objects is set to
+    reference element in that array with the pointer to the field.
+
+  RETURN
+    TRUE  an error occured
+    FALSE ok
+*/
+
+bool
+fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                 Item **ref_pointer_array)
+{
+  Item_outer_ref *ref;
+  bool res= FALSE;
+  List_iterator<Item_outer_ref> ref_it(select->inner_refs_list);
+  while ((ref= ref_it++))
+  {
+    Item_field *item= ref->outer_field;
+    /*
+      TODO: this field item already might be present in the select list.
+      In this case instead of adding new field item we could use an
+      existing one. The change will lead to less operations for copying fields,
+      smaller temporary tables and less data passed through filesort.
+    */
+    if (ref_pointer_array)
+    {
+      int el= all_fields.elements;
+      ref_pointer_array[el]= (Item*)item;
+      /* Add the field item to the select list of the current select. */
+      all_fields.push_front((Item*)item);
+      /*
+        If it's needed reset each Item_ref item that refers this field with
+        a new reference taken from ref_pointer_array.
+      */
+      ref->ref= ref_pointer_array + el;
+    }
+    if (!ref->fixed && ref->fix_fields(thd, 0))
+    {
+      res= TRUE;
+      break;
+    }
+    thd->used_tables|= item->used_tables();
+  }
+  return res;
+}
+
+/*
   Function to setup clauses without sum functions
 */
 inline int setup_without_group(THD *thd, Item **ref_pointer_array,
@@ -394,6 +458,10 @@ JOIN::prepare(Item ***rref_pointer_array,
   if (having && having->with_sum_func)
     having->split_sum_func2(thd, ref_pointer_array, all_fields,
                             &having, TRUE);
+  if (select_lex->inner_refs_list.elements &&
+      fix_inner_refs(thd, all_fields, select_lex, ref_pointer_array))
+    DBUG_RETURN(-1);
+
   if (select_lex->inner_sum_func_list)
   {
     Item_sum *end=select_lex->inner_sum_func_list;
@@ -481,6 +549,9 @@ JOIN::prepare(Item ***rref_pointer_array,
     }
   }
 
+  if (!procedure && result && result->prepare(fields_list, unit_arg))
+    goto err;					/* purecov: inspected */
+
   /* Init join struct */
   count_field_types(&tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
@@ -494,9 +565,6 @@ JOIN::prepare(Item ***rref_pointer_array,
     goto err;
   }
 #endif
-  if (!procedure && result && result->prepare(fields_list, unit_arg))
-    goto err;					/* purecov: inspected */
-
   if (select_lex->olap == ROLLUP_TYPE && rollup_init())
     goto err;
   if (alloc_func_list())
@@ -2853,15 +2921,9 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
           /*
             We can't use indexes if the effective collation
             of the operation differ from the field collation.
-
-            We also cannot use index on a text column, as the column may
-            contain 'x' 'x\t' 'x ' and 'read_next_same' will stop after
-            'x' when searching for WHERE col='x '
           */
           if (field->cmp_type() == STRING_RESULT &&
-              (((Field_str*)field)->charset() != cond->compare_collation() ||
-               ((*value)->type() != Item::NULL_ITEM &&
-                (field->flags & BLOB_FLAG) && !field->binary())))
+              ((Field_str*)field)->charset() != cond->compare_collation())
             return;
         }
       }
@@ -5131,13 +5193,15 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
 				    key_part->length,
 				    keyuse->val);
   }
-  else if (keyuse->val->type() == Item::FIELD_ITEM)
+  else if (keyuse->val->type() == Item::FIELD_ITEM ||
+           (keyuse->val->type() == Item::REF_ITEM &&
+            ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF) )
     return new store_key_field(thd,
 			       key_part->field,
 			       key_buff + maybe_null,
 			       maybe_null ? key_buff : 0,
 			       key_part->length,
-			       ((Item_field*) keyuse->val)->field,
+			       ((Item_field*) keyuse->val->real_item())->field,
 			       keyuse->val->full_name());
   return new store_key_item(thd,
 			    key_part->field,
@@ -8704,7 +8768,8 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
       type they needed to be handled separately.
     */
     if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
-        type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE)
+        type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE ||
+        type == MYSQL_TYPE_TIMESTAMP)
       new_field= item->tmp_table_field_from_field_type(table);
     /* 
       Make sure that the blob fits into a Field_varstring which has 
@@ -8811,8 +8876,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
   Item *orig_item= 0;
 
   if (type != Item::FIELD_ITEM &&
-      item->real_item()->type() == Item::FIELD_ITEM &&
-      !((Item_ref *) item)->depended_from)
+      item->real_item()->type() == Item::FIELD_ITEM)
   {
     orig_item= item;
     item= item->real_item();
@@ -13472,8 +13536,7 @@ count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
   {
     Item::Type type=field->type();
     Item::Type real_type= field->real_item()->type();
-    if (type == Item::FIELD_ITEM || (real_type == Item::FIELD_ITEM &&
-        !((Item_ref *) field)->depended_from))
+    if (real_type == Item::FIELD_ITEM)
       param->field_count++;
     else if (real_type == Item::SUM_FUNC_ITEM)
     {
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index c94a7e17e59..16df0059217 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -147,6 +147,8 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg)
   fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
 					   (byte **)
 					   &result_table_list.next_local);
+  fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table= 
+    fake_select_lex->get_table_list();
   for (ORDER *order= (ORDER *)global_parameters->order_list.first;
        order;
        order=order->next)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d43289d147d..4858184557b 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -133,6 +133,7 @@ int mysql_update(THD *thd,
   READ_RECORD	info;
   SELECT_LEX    *select_lex= &thd->lex->select_lex;
   bool need_reopen;
+  List<Item> all_fields;
   DBUG_ENTER("mysql_update");
 
   LINT_INIT(timestamp_query_id);
@@ -225,6 +226,10 @@ int mysql_update(THD *thd,
     DBUG_RETURN(1);				/* purecov: inspected */
   }
 
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
+
   if (conds)
   {
     Item::cond_result cond_value;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index efe8d85d8f5..6becb6cc3a3 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4188,8 +4188,13 @@ select_into:
 	| select_from into;
 
 select_from:
-	  FROM join_table_list where_clause group_clause having_clause
+        FROM join_table_list where_clause group_clause having_clause
 	       opt_order_clause opt_limit_clause procedure_clause
+          {
+            Select->context.table_list=
+              Select->context.first_name_resolution_table= 
+                (TABLE_LIST *) Select->table_list.first;
+          }
         | FROM DUAL_SYM where_clause opt_limit_clause
           /* oracle compatibility: oracle always requires FROM clause,
              and DUAL is system table without fields.
@@ -9266,6 +9271,12 @@ subselect_end:
 	  lex->current_select = lex->current_select->return_after_parsing();
           lex->nest_level--;
           lex->current_select->n_child_sum_items += child->n_sum_items;
+          /*
+            A subselect can add fields to an outer select. Reserve space for
+            them.
+          */
+          lex->current_select->select_n_where_fields+=
+            child->select_n_where_fields;
 	};
 
 /**************************************************************************