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

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


sql/item.h:
  Auto merged
sql/sql_table.cc:
  Auto merged
sql/sql_update.cc:
  Auto merged

51 files changed, 2264 insertions, 334 deletions

diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index 9d337a1ed34..ba4e9386312 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -176,6 +176,14 @@ create table t1 (a int);
 delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5;
 delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5;
 drop table t1;
+create table t1(f1 int primary key);
+insert into t1 values (4),(3),(1),(2);
+delete from t1 where (@a:= f1) order by f1 limit 1;
+select @a;
+@a
+1
+drop table t1;
+End of 4.1 tests
 CREATE TABLE t1 (a int not null,b int not null);
 CREATE TABLE t2 (a int not null, b int not null, primary key (a,b));
 CREATE TABLE t3 (a int not null, b int not null, primary key (a,b));
diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result
index 68a424fa3a5..fdf11c14cc4 100644
--- a/mysql-test/r/fulltext_left_join.result
+++ b/mysql-test/r/fulltext_left_join.result
@@ -67,3 +67,26 @@ id	d	e	m_id	f
 4	bword	aword	NULL	NULL
 5	aword and bword	NULL	5	
 drop table t1,t2;
+CREATE TABLE t1 (
+id int(10) NOT NULL auto_increment,
+link int(10) default NULL,
+name mediumtext default NULL,
+PRIMARY KEY (id),
+FULLTEXT (name)
+);
+INSERT INTO t1 VALUES (1, 1, 'string');
+INSERT INTO t1 VALUES (2, 0, 'string');
+CREATE TABLE t2 (
+id int(10) NOT NULL auto_increment,
+name mediumtext default NULL,
+PRIMARY KEY (id),
+FULLTEXT (name)
+);
+INSERT INTO t2 VALUES (1, 'string');
+SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance 
+FROM t1 LEFT JOIN t2 ON t1.link = t2.id
+WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE);
+id	link	name	relevance
+1	1	string	0
+2	0	string	0
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 38250173dd1..d9ca9e80e44 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -355,4 +355,47 @@ some_id
 1
 2
 drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2);
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a int PRIMARY KEY);
+INSERT INTO t4 VALUES (1),(2);
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
+SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+a	a	b	a	b	a
+3	3	1	3	2	1
+3	3	1	3	2	2
+4	4	1	4	2	1
+4	4	1	4	2	2
+EXPLAIN SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
+SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index b5a9d4de105..052451f8c54 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -817,7 +817,7 @@ explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'moo
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")`
+Note	1003	select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")`
 SELECT lpad(12345, 5, "#");
 lpad(12345, 5, "#")
 12345
@@ -1084,6 +1084,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
 DROP TABLE t1;
+create table t1(f1 varchar(4));
+explain extended select encode(f1,'zxcv') as 'enc' from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+Warnings:
+Note	1003	select encode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+explain extended select decode(f1,'zxcv') as 'enc' from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+Warnings:
+Note	1003	select decode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+drop table t1;
 End of 4.1 tests
 create table t1 (d decimal default null);
 insert into t1 values (null);
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7d1e8832069..97375898f41 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -933,3 +933,105 @@ b	sum(1)
 18	6
 19	6
 DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
+MAX(a)-MIN(a)
+1
+1
+1
+SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
+CEILING(MIN(a))
+1
+3
+5
+SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 
+GROUP BY b;
+CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
+Positive
+Positive
+Positive
+SELECT a + 1 FROM t1 GROUP BY a;
+a + 1
+2
+3
+4
+5
+6
+7
+SELECT a + b FROM t1 GROUP BY b;
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 
+FROM t1 AS t1_outer;
+(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
+1
+2
+3
+4
+5
+6
+SELECT 1 FROM t1 as t1_outer GROUP BY a 
+HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
+1
+1
+1
+1
+1
+1
+1
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY
+SELECT 1 FROM t1 as t1_outer GROUP BY a 
+HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
+ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
+21
+21
+21
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+FROM t1 AS t1_outer;
+(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+3
+3
+3
+3
+3
+3
+SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY
+SELECT 1 FROM t1 as t1_outer 
+WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
+1
+1
+1
+1
+1
+1
+1
+SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
+b
+1
+2
+3
+SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
+1
+1
+SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
+ERROR 42S22: Unknown column 'a' in 'having clause'
+SELECT 1 FROM t1 GROUP BY SUM(b);
+ERROR HY000: Invalid use of group function
+SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 
+(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
+HAVING SUM(t1_inner.b)+t1_outer.b > 5);
+ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY
+DROP TABLE t1;
+SET SQL_MODE = '';
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index 78ef6fbccba..7900e0b7695 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -325,3 +325,24 @@ select row_count();
 row_count()
 1
 drop table t1;
+create table t1 (f1 int unique, f2 int);
+create table t2 (f3 int, f4 int);
+create view v1 as select * from t1, t2 where f1= f3;
+insert into t1 values (1,11), (2,22);
+insert into t2 values (1,12), (2,24);
+insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10;
+ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+f1	f2
+1	11
+2	22
+3	NULL
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+f1	f2
+1	11
+2	22
+12	NULL
+drop view v1;
+drop table t1,t2;
diff --git a/mysql-test/r/ndb_subquery.result b/mysql-test/r/ndb_subquery.result
index b19571b05c1..a67efb144b2 100644
--- a/mysql-test/r/ndb_subquery.result
+++ b/mysql-test/r/ndb_subquery.result
@@ -9,7 +9,7 @@ insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5);
 explain select * from t2 where p NOT IN (select p from t1);
 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	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	
 select * from t2 where p NOT IN (select p from t1) order by p;
 p	u	o
 4	4	4
@@ -17,7 +17,7 @@ p	u	o
 explain select * from t2 where p NOT IN (select u from t1);
 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	unique_subquery	u	u	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	unique_subquery	u	u	4	func	1	
 select * from t2 where p NOT IN (select u from t1) order by p;
 p	u	o
 4	4	4
@@ -25,7 +25,7 @@ p	u	o
 explain select * from t2 where p NOT IN (select o from t1);
 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	index_subquery	o	o	4	func	1	Using index
+2	DEPENDENT SUBQUERY	t1	index_subquery	o	o	4	func	1	
 select * from t2 where p NOT IN (select o from t1) order by p;
 p	u	o
 4	4	4
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 9d2da82813f..e342e8d1d7f 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -674,6 +674,49 @@ select a from t1 where a > 'x';
 a
 xx
 drop table t1;
+CREATE TABLE t1 (
+OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
+OXLEFT int NOT NULL DEFAULT '0',
+OXRIGHT int NOT NULL DEFAULT '0',
+OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+PRIMARY KEY  (OXID),
+KEY OXNID (OXID),
+KEY OXLEFT (OXLEFT),
+KEY OXRIGHT (OXRIGHT),
+KEY OXROOTID (OXROOTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
+INSERT INTO t1 VALUES
+('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
+('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
+'d8c4177d09f8b11f5.52725521');
+EXPLAIN
+SELECT s.oxid FROM t1 v, t1 s 
+WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using where
+1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	5	Range checked for each record (index map: 0x4)
+SELECT s.oxid FROM t1 v, t1 s 
+WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+oxid
+d8c4177d151affab2.81582770
+d8c4177d206a333d2.74422679
+d8c4177d225791924.30714720
+d8c4177d2380fc201.39666693
+d8c4177d24ccef970.14957924
+DROP TABLE t1;
 End of 4.1 tests
 CREATE TABLE t1 (
 id int(11) NOT NULL auto_increment,
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index d31be4390f7..5dcf830ca97 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3628,6 +3628,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
+CREATE TABLE t2 ( f11 int PRIMARY KEY );
+INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
+INSERT INTO t2 VALUES (62);
+SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
+f1	f2	f3	f4	f5	f6	checked_out	f11
+1	1	1	0	0	0	0	NULL
+DROP TABLE t1, t2;
 DROP TABLE IF EXISTS t1;
 CREATE TABLE t1(a int);
 INSERT into t1 values (1), (2), (3);
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 0b0ad802b54..67b030f87a4 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -187,7 +187,7 @@ Pos	Instruction
 32	set v_dig@4 (v_dig@4 + 1)
 33	stmt 4 "update sudoku_work set dig = v_dig wh..."
 34	set v_tcounter@6 (v_tcounter@6 + 1)
-35	jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))
+35	jump_if_not 37(37) (not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)))
 36	jump 15
 37	set v_i@3 (v_i@3 + 1)
 38	jump 15
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1bf6d6c7716..57b2c0cd0f2 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -744,7 +744,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
+Note	1003	select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
 SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
 id
 SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -892,7 +892,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	a	a	5	func	2	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
 CREATE TABLE t3 (a int(11) default '0');
 INSERT INTO t3 VALUES (1),(2),(3);
 SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
@@ -907,7 +907,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
 drop table t1,t2,t3;
 create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1305,7 +1305,7 @@ a
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1462,27 +1462,27 @@ a3	1
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
-2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Full scan on NULL key
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
-2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Full scan on NULL key
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
-2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Full scan on NULL key
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
-2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Using where; Full scan on NULL key
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
 drop table t1,t2;
 create table t2 (a int, b int);
 create table t3 (a int);
@@ -1737,14 +1737,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
 Warnings:
-Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
 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
 Warnings:
 Note	1276	Field or reference '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)))
+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));
 insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
@@ -2816,19 +2816,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))))
+Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
 explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
@@ -3009,7 +3009,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
 EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
-2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using index
+2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using index; Full scan on NULL key
 SELECT a, a IN (SELECT a FROM t1) FROM t2;
 a	a IN (SELECT a FROM t1)
 1	1
@@ -3605,3 +3605,36 @@ FROM t1) t;
 COUNT(*)
 3000
 DROP TABLE t1,t2;
+CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
+CREATE TABLE t2 (c int);
+INSERT INTO t1 VALUES ('aa', 1);
+INSERT INTO t2 VALUES (1);
+SELECT * FROM t1 
+WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+UNION 
+SELECT c from t2 WHERE c=t1.c);
+id	c
+aa	1
+INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
+SELECT * FROM t1 
+WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+UNION 
+SELECT c from t2 WHERE c=t1.c);
+id	c
+aa	1
+bb	2
+cc	3
+dd	1
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 
+WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
+UNION 
+SELECT c from t2 WHERE c=t1.c);
+id	c
+aa	1
+bb	2
+cc	3
+dd	1
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 75aa339fb29..27ec0ccfc0f 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -126,11 +126,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	DDOCTYPEID_IDX	NULL	NULL	NULL	9	Using where
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using index; Using where
-3	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using index; Using where
-4	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using index; Using where
-5	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using index; Using where
-6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	Using index; Using where
+2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using where
+3	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using where
+4	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using where
+5	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using where
+6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	Using where
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 5ab8e448b39..07ff17c9df9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -15,9 +15,8 @@ insert into t2 values
 (4, NULL),
 (2, NULL);
 select a, oref, a in (select max(ie) 
-from t1 where oref=t2.oref group by grp) from t2;
-a	oref	a in (select max(ie) 
-from t1 where oref=t2.oref group by grp)
+from t1 where oref=t2.oref group by grp) Z from t2;
+a	oref	Z
 1	1	1
 2	2	0
 3	3	NULL
@@ -25,14 +24,13 @@ NULL	4	0
 NULL	2	NULL
 explain extended
 select a, oref, a in (select max(ie) 
-from t1 where oref=t2.oref group by grp) from t2;
+from t1 where oref=t2.oref group by grp) Z from t2;
 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	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 `a in (select max(ie) 
-from t1 where oref=t2.oref group by grp)` from `test`.`t2`
+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 
 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
@@ -42,6 +40,16 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1276	Field or reference '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
+select max(ie) from t1 where oref=t2.oref group by grp
+) Z from t2;
+a	oref	Z
+1	1	1
+2	2	0
+3	3	NULL
+NULL	4	0
+NULL	2	NULL
 create table t3 (a int);
 insert into t3 values (NULL), (NULL);
 flush status;
@@ -81,10 +89,10 @@ explain extended
 select oref, a, a in (select a 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	4	
-2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using index; Using where
+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	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`)))) AS `Z` from `test`.`t2`
+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);
 oref	a
@@ -145,9 +153,479 @@ t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
 from t3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
-2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	4	Using where
+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	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));
+insert into t1 values 
+(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref, 
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+a	oref	Z
+1	1	1
+NULL	1	NULL
+NULL	0	0
+This must show a trig_cond:
+explain extended
+select a, oref, 
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
+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	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);
+insert into t1 (oref, grp) values
+(1, 1),
+(1, 1);
+create table t2 (oref int, a int);
+insert into t2 values 
+(1, NULL),
+(2, NULL);
+select a, oref, 
+a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+a	oref	Z
+NULL	1	NULL
+NULL	2	0
+This must show a trig_cond:
+explain extended
+select a, oref, 
+a in (select count(*) from t1 group by grp having grp=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	2	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+Warnings:
+Note	1276	Field or reference '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 count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
+drop table t1, t2;
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+select a,b, a in (select a from t1 where t1.b = t2.b union select a from
+t1 where t1.b = t2.b) Z from t2 ;
+a	b	Z
+1	1	1
+2	1	0
+NULL	1	NULL
+NULL	0	0
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+a	b	Z
+1	1	1
+2	1	0
+NULL	1	NULL
+NULL	0	0
+drop table t1, t2;
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=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	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	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
+NULL	1	100	0
+NULL	2	100	NULL
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended 
+select a,b, oref, 
+(a,b) in (select a,b from t1,t4 where c=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	2	
+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	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 
+from t2;
+a	b	oref	Z
+NULL	1	100	0
+NULL	2	100	NULL
+drop table t1,t2,t3,t4;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+oref	a	b	Z
+cc	3	NULL	NULL
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+oref	a	b	Z
+new1	10	10	NULL
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+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	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);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('aa', 20, NULL),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, NULL),
+('ee', 10, NULL),
+('ee', 10, NULL),
+('ff', 20, 2),
+('ff', 20, 1);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('ff', 2),
+('cc', 3),
+('aa', 1),
+('dd', NULL),
+('bb', NULL);
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	0
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where 
+a in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+aa	1
+select oref, a from t2 where 
+a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+bb	2
+ff	2
+dd	NULL
+update t1 set ie=3 where oref='ff' and ie=1;
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+alter table t1 add index idx(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	index_subquery	idx	idx	5	func	4	Using where; 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
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+alter table t1 drop index idx;
+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
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+explain 
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+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
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1) Z 
+from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+select oref, a, b, 
+(a,b) in (select min(ie1),max(ie2) from t1 
+where oref=t2.oref group by grp) Z 
+from t2;
+oref	a	b	Z
+ee	NULL	1	0
+bb	2	1	0
+ff	2	2	0
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where 
+(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+aa	1	1
+select oref, a, b from t2 where
+(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+ee	NULL	1
+bb	2	1
+ff	2	2
+dd	1	NULL
+alter table t1 add index idx(ie1,ie2);
+explain select oref, a, b, (a,b) in (select ie1,ie2 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	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 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	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	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);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, 5),
+('cc', 10, 6);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('cc', 5),
+('cc', 2),
+('cc', NULL),
+('aa', 1),
+('bb', NULL);
+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	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where; 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	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+cc	5
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+ee	NULL
+bb	2
+cc	2
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) 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	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+drop table t1,t2;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 9f34f60eb1a..3d40a2d05df 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1278,4 +1278,36 @@ a	b
 2	b
 3	c
 drop table t1;
+CREATE TABLE t1 (
+id int NOT NULL DEFAULT '0',
+a  varchar(10) NOT NULL,
+b  varchar(10),
+c  varchar(10),
+d  timestamp NOT NULL,
+PRIMARY KEY (id, a)
+);
+CREATE TABLE t2 (
+fubar_id         int unsigned NOT NULL DEFAULT '0',
+last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+PRIMARY KEY  (fubar_id)
+);
+CREATE TRIGGER fubar_change
+AFTER UPDATE ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (fubar_id, last_change_time)
+SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
+FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
+ON DUPLICATE KEY UPDATE
+last_change_time =
+IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
+END
+|
+INSERT INTO t1 (id,a, b,c,d) VALUES
+(1,'a','b','c',now()),(2,'a','b','c',now());
+UPDATE t1 SET c='Bang!' WHERE id=1;
+SELECT fubar_id FROM t2;
+fubar_id
+1
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 64b7111bbc8..d5f59247084 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -240,3 +240,37 @@ drop table bug18761;
 select is_const((1,2,3));
 ERROR 21000: Operand should contain 1 column(s)
 drop function if exists is_const;
+CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+create function f1(p1 varchar(255))
+returns varchar(255)
+begin
+return metaphon(p1);
+end//
+create function f2(p1 varchar(255))
+returns double
+begin
+return myfunc_double(p1);
+end//
+create function f3(p1 varchar(255))
+returns double
+begin
+return myfunc_int(p1);
+end//
+select f3(NULL);
+f3(NULL)
+0
+select f2(NULL);
+f2(NULL)
+NULL
+select f1(NULL);
+f1(NULL)
+NULL
+drop function f1;
+drop function f2;
+drop function f3;
+drop function metaphon;
+drop function myfunc_double;
+drop function myfunc_int;
+End of 5.0 tests.
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index f8584275a5a..7185a70689a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3014,6 +3014,17 @@ i	j
 6	3
 DROP VIEW v1, v2;
 DROP TABLE t1;
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+View	Create View
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x`
+SELECT !0 * 5 AS x FROM DUAL;
+x
+5
+SELECT * FROM v;
+x
+5
+DROP VIEW v;
 DROP VIEW IF EXISTS v1;
 CREATE VIEW v1 AS SELECT 'The\ZEnd';
 SELECT * FROM v1;
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 865e1746fd3..306447dbd5a 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -163,6 +163,17 @@ delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5;
 delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5;
 drop table t1;
 
+#
+# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and
+#            non-restricting WHERE is present.
+#
+create table t1(f1 int primary key);
+insert into t1 values (4),(3),(1),(2);
+delete from t1 where (@a:= f1) order by f1 limit 1;
+select @a;
+drop table t1;
+
+--echo End of 4.1 tests
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test
index 7c22f49ed8c..5942ce119ee 100644
--- a/mysql-test/t/fulltext_left_join.test
+++ b/mysql-test/t/fulltext_left_join.test
@@ -58,4 +58,32 @@ insert into t2 values (1, 'bword'), (3, 'aword'), (5, '');
 select * from t1 left join t2 on m_id = id where match(d, e, f) against ('+aword +bword' in boolean mode);
 drop table t1,t2;
 
+#
+# BUG#25637: LEFT JOIN with BOOLEAN FULLTEXT loses left table matches
+#            (this is actually the same bug as bug #14708)
+#
+
+CREATE TABLE t1 (
+  id int(10) NOT NULL auto_increment,
+  link int(10) default NULL,
+  name mediumtext default NULL,
+  PRIMARY KEY (id),
+  FULLTEXT (name)
+);
+INSERT INTO t1 VALUES (1, 1, 'string');
+INSERT INTO t1 VALUES (2, 0, 'string');
+CREATE TABLE t2 (
+    id int(10) NOT NULL auto_increment,
+    name mediumtext default NULL,
+    PRIMARY KEY (id),
+    FULLTEXT (name)
+);
+INSERT INTO t2 VALUES (1, 'string');
+
+SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance 
+  FROM t1 LEFT JOIN t2 ON t1.link = t2.id
+    WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE);
+
+DROP TABLE t1,t2;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 86b0268f008..54b81bed133 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -261,5 +261,41 @@ select some_id from t1 where some_id not in('-1', '0');
 
 drop table t1;
 
+#
+# BUG#20420: optimizer reports wrong keys on left join with IN
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2);
+
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+
+CREATE TABLE t4 (a int PRIMARY KEY);
+INSERT INTO t4 VALUES (1),(2);
+
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
+  JOIN t1 ON t3.a=t1.a 
+  JOIN t2 ON t3.a=t2.a
+  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+SELECT STRAIGHT_JOIN * FROM t3 
+  JOIN t1 ON t3.a=t1.a 
+  JOIN t2 ON t3.a=t2.a
+  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+EXPLAIN SELECT STRAIGHT_JOIN 
+   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+  FROM t3, t1, t2
+  WHERE t3.a=t1.a AND t3.a=t2.a;
+
+SELECT STRAIGHT_JOIN 
+   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+  FROM t3, t1, t2
+  WHERE t3.a=t1.a AND t3.a=t2.a;
+
+DROP TABLE t1,t2,t3,t4;  
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index a55b633b91e..64b59025d44 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -730,6 +730,14 @@ EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
 
 DROP TABLE t1;
  
+#
+# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly
+#
+create table t1(f1 varchar(4));
+explain extended select encode(f1,'zxcv') as 'enc' from t1;
+explain extended select decode(f1,'zxcv') as 'enc' from t1;
+drop table t1;
+
 --echo End of 4.1 tests
 
 #
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 3e926fba0c6..92c92bf3957 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -701,3 +701,54 @@ EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
 SELECT b, sum(1) FROM t1 GROUP BY b;
 SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
 DROP TABLE t1;
+
+#
+# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
+
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
+SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
+SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 
+ GROUP BY b;
+SELECT a + 1 FROM t1 GROUP BY a;
+--error ER_WRONG_FIELD_WITH_GROUP 
+SELECT a + b FROM t1 GROUP BY b;
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 
+  FROM t1 AS t1_outer;
+SELECT 1 FROM t1 as t1_outer GROUP BY a 
+  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
+--error ER_WRONG_FIELD_WITH_GROUP 
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 
+  FROM t1 AS t1_outer GROUP BY t1_outer.b;
+--error ER_BAD_FIELD_ERROR 
+SELECT 1 FROM t1 as t1_outer GROUP BY a 
+  HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 
+  FROM t1 AS t1_outer GROUP BY t1_outer.b;
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+  FROM t1 AS t1_outer;
+--error ER_WRONG_FIELD_WITH_GROUP 
+SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 
+  FROM t1 AS t1_outer GROUP BY t1_outer.b;
+
+SELECT 1 FROM t1 as t1_outer 
+  WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
+
+SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
+
+SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
+SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
+
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 FROM t1 GROUP BY SUM(b);
+--error ER_WRONG_FIELD_WITH_GROUP 
+SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 
+  (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
+   HAVING SUM(t1_inner.b)+t1_outer.b > 5);
+DROP TABLE t1;
+SET SQL_MODE = '';
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 029c4d19e63..0a8e184ea5c 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -198,3 +198,21 @@ select row_count();
 insert into t1 values (5, 5) on duplicate key update data= data + 10;
 select row_count();
 drop table t1;
+
+#
+# Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table
+#
+create table t1 (f1 int unique, f2 int);
+create table t2 (f3 int, f4 int);
+create view v1 as select * from t1, t2 where f1= f3;
+insert into t1 values (1,11), (2,22);
+insert into t2 values (1,12), (2,24);
+--error 1393
+insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10;
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+drop view v1;
+drop table t1,t2;
+
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index c7f27d7ca08..72be4e12c6a 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -525,6 +525,49 @@ explain select a from t1 where a > 'x';
 select a from t1 where a > 'x';
 drop table t1;
 
+#
+# Bug #24776: assertion abort for 'range checked for each record' 
+#
+
+CREATE TABLE t1 (
+  OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+  OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
+  OXLEFT int NOT NULL DEFAULT '0',
+  OXRIGHT int NOT NULL DEFAULT '0',
+  OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+  PRIMARY KEY  (OXID),
+  KEY OXNID (OXID),
+  KEY OXLEFT (OXLEFT),
+  KEY OXRIGHT (OXRIGHT),
+  KEY OXROOTID (OXROOTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
+
+INSERT INTO t1 VALUES
+('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
+('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
+ 'd8c4177d09f8b11f5.52725521');
+
+EXPLAIN
+SELECT s.oxid FROM t1 v, t1 s 
+  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+        v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+        s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+
+SELECT s.oxid FROM t1 v, t1 s 
+  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+        v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+        s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+
+DROP TABLE t1;
+
 --echo End of 4.1 tests
 
 #
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index e8d864b81f9..19b625451d4 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3103,6 +3103,16 @@ SELECT t3.a FROM t1,t2,t3
         t3.c IN ('bb','ee');
 
 DROP TABLE t1,t2,t3;
+ 
+#
+# Bug#25172: Not checked buffer size leads to a server crash
+#
+CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
+CREATE TABLE t2 ( f11 int PRIMARY KEY );
+INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
+INSERT INTO t2 VALUES (62);
+SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
+DROP TABLE t1, t2;
 
 #
 # Bug#6298: LIMIT #, -1 no longer works to set start with no end limit
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0b658f746a4..c6dd34b5172 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2508,3 +2508,37 @@ SELECT SQL_NO_CACHE COUNT(*)
           FROM t1) t;
 
 DROP TABLE t1,t2;
+
+#
+# Bug #25219: EXIST subquery with UNION over a mix of
+#             correlated and uncorrelated selects
+#             
+
+CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
+CREATE TABLE t2 (c int);
+
+INSERT INTO t1 VALUES ('aa', 1);
+INSERT INTO t2 VALUES (1);
+
+SELECT * FROM t1 
+  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+                UNION 
+                SELECT c from t2 WHERE c=t1.c);
+
+INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
+
+SELECT * FROM t1 
+  WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+                UNION 
+                SELECT c from t2 WHERE c=t1.c);
+
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1);
+
+SELECT * FROM t1 
+  WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
+                UNION 
+                SELECT c from t2 WHERE c=t1.c);
+
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index f7fbafdd17f..23d78721dbe 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -16,13 +16,14 @@ insert into t1 (oref, grp, ie) values
  (3, 1, 4),
  (3, 2, NULL);
 
-#  Ok, for  
+# Ok, for
 #   select max(ie) from t1 where oref=PARAM group by grp
 # we'll have:
-#  1  ->  (1, NULL)    matching + NULL
-#  2  ->  (3)          non-matching
-#  3  ->  (3, NULL)    non-matching + NULL
-#  4  ->  ()           nothing.
+# PARAM      subquery result  
+#   1   ->   {(1), (NULL)}    matching + NULL
+#   2   ->   {(3)}            non-matching
+#   3   ->   {(3), (NULL)}    non-matching + NULL
+#   4   ->   {}               empty set
 
 create table t2 (oref int, a int);
 insert into t2 values 
@@ -34,18 +35,21 @@ insert into t2 values
 
 # true, false, null, false, null
 select a, oref, a in (select max(ie) 
-  from t1 where oref=t2.oref group by grp) from t2;
+  from t1 where oref=t2.oref group by grp) Z from t2;
 
 # This must have a trigcond
 explain extended
 select a, oref, a in (select max(ie) 
-  from t1 where oref=t2.oref group by grp) from t2;
+  from t1 where oref=t2.oref group by grp) Z from t2;
 
 # This must not have a trigcond:
 explain extended
 select a, oref from t2 
 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
-
+select a, oref, a in (
+  select max(ie) from t1 where oref=t2.oref group by grp union
+  select max(ie) from t1 where oref=t2.oref group by grp
+  ) Z from t2;
 
 # Non-correlated subquery, 2 NULL evaluations
 create table t3 (a int);
@@ -135,3 +139,336 @@ from t3;
 
 drop table t1, t2, t3;
 
+
+#
+# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
+#
+
+# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
+create table t1 (a int NOT NULL, b int NOT NULL, key(a));
+insert into t1 values 
+  (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref, 
+       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+
+--echo This must show a trig_cond:
+explain extended
+select a, oref, 
+       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+drop table t1,t2,t3;
+
+
+# case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY
+create table t1 (oref int, grp int);
+insert into t1 (oref, grp) values
+ (1, 1),
+ (1, 1);
+
+# Ok, for  
+#   select count(*) from t1 group by grp having grp=PARAM
+# we'll have:
+#  PARAM    subuqery result
+#    1  ->    {(2)}   
+#    2  ->    {} - empty set
+create table t2 (oref int, a int);
+insert into t2 values 
+  (1, NULL),
+  (2, NULL);
+
+select a, oref, 
+       a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+
+--echo This must show a trig_cond:
+explain extended
+select a, oref, 
+       a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+
+drop table t1, t2;
+
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+
+select a,b, a in (select a from t1 where t1.b = t2.b union select a from
+t1 where t1.b = t2.b) Z from t2 ;
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+drop table t1, t2;
+
+
+#
+# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side.
+#
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+
+# First test index subquery engine
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+
+# Then check that we do turn off 'ref' scans in the subquery
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended 
+  select a,b, oref, 
+         (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
+  from t2;
+select a,b, oref, 
+       (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
+from t2;
+
+drop table t1,t2,t3,t4;
+
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some arent.
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+ ('bb', 10, 3, 1),
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+ 
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+ 
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+  ('ee', NULL, 1),
+  ('bb', 2, 1),
+  ('ff', 2, 2),
+  ('cc', 3, NULL),
+  ('bb', NULL, NULL),
+  ('aa', 1, 1),
+  ('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
+
+--cc 3 NULL NULL
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL); 
+-- new1, 10, 10, NULL,
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
+drop table t1, t2;
+
+# Now test different column types:
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+ ('aa', 20, NULL),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, NULL),
+ 
+ ('ee', 10, NULL),
+ ('ee', 10, NULL),
+ 
+ ('ff', 20, 2),
+ ('ff', 20, 1);
+
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('ff', 2),
+  ('cc', 3),
+  ('aa', 1),
+  ('dd', NULL),
+  ('bb', NULL);
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a from t2 where 
+  a in (select min(ie) from t1 where oref=t2.oref group by grp);
+  
+select oref, a from t2 where 
+  a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+
+#
+update t1 set ie=3 where oref='ff' and ie=1;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+
+#
+alter table t1 add index idx(ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+alter table t1 drop index idx;
+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;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+                                   group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+                                       group by grp having min(ie) > 1);
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+
+ ('bb', 10, 3, 1),
+
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+ 
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+ 
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+  ('ee', NULL, 1),
+  ('bb', 2, 1),
+  ('ff', 2, 2),
+  ('cc', 3, NULL),
+  ('bb', NULL, NULL),
+  ('aa', 1, 1),
+  ('dd', 1, NULL);
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b, 
+             (a,b) in (select min(ie1),max(ie2) from t1 
+                       where oref=t2.oref group by grp) Z 
+from t2;
+
+select oref, a, b from t2 where 
+  (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+  
+select oref, a, b from t2 where
+  (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+
+alter table t1 add index idx(ie1,ie2);
+
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, 5),
+ ('cc', 10, 6);
+ 
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('cc', 5),
+  ('cc', 2),
+  ('cc', NULL),
+  ('aa', 1),
+  ('bb', NULL);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+drop table t1,t2;
+
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 63cef614b77..aa6cb2bf025 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1554,4 +1554,50 @@ select * from t1;
 
 drop table t1;
 
+#
+# Bug#25398: crash when a trigger contains a SELECT with 
+#            trigger fields in the select list under DISTINCT
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL DEFAULT '0',
+  a  varchar(10) NOT NULL,
+  b  varchar(10),
+  c  varchar(10),
+  d  timestamp NOT NULL,
+  PRIMARY KEY (id, a)
+);
+
+CREATE TABLE t2 (
+  fubar_id         int unsigned NOT NULL DEFAULT '0',
+  last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  PRIMARY KEY  (fubar_id)
+);
+
+DELIMITER |;
+
+CREATE TRIGGER fubar_change
+  AFTER UPDATE ON t1
+    FOR EACH ROW
+      BEGIN
+        INSERT INTO t2 (fubar_id, last_change_time)
+          SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
+            FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
+        ON DUPLICATE KEY UPDATE
+          last_change_time =
+            IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
+      END
+|
+
+DELIMITER ;|
+
+INSERT INTO t1 (id,a, b,c,d) VALUES
+ (1,'a','b','c',now()),(2,'a','b','c',now());
+
+UPDATE t1 SET c='Bang!' WHERE id=1;
+
+SELECT fubar_id FROM t2;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index 65cbc7ae3ae..0b582dc61b6 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -242,3 +242,50 @@ drop table bug18761;
 select is_const((1,2,3));
 
 drop function if exists is_const;
+
+#
+# Bug #25382: Passing NULL to an UDF called from stored procedures 
+# crashes server
+#
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB";
+
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
+
+delimiter //;
+create function f1(p1 varchar(255))
+returns varchar(255)
+begin
+  return metaphon(p1);
+end//
+
+create function f2(p1 varchar(255))
+returns double
+begin
+  return myfunc_double(p1);
+end//
+
+create function f3(p1 varchar(255))
+returns double
+begin
+  return myfunc_int(p1);
+end//
+
+delimiter ;//
+
+select f3(NULL);
+select f2(NULL);
+select f1(NULL);
+
+drop function f1;
+drop function f2;
+drop function f3;
+drop function metaphon;
+drop function myfunc_double;
+drop function myfunc_int;
+
+--echo End of 5.0 tests.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index a34a1ba117d..47a5a54007b 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2959,6 +2959,17 @@ SELECT * FROM t1;
 DROP VIEW v1, v2;
 DROP TABLE t1;
 
+#
+# Bug #25580: !0 as an operand in a select expression of a view
+#
+
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+
+SELECT !0 * 5 AS x FROM DUAL;
+SELECT * FROM v;
+
+DROP VIEW v;
 
 #
 # BUG#24293: '\Z' token is not handled correctly in views
diff --git a/sql/item.cc b/sql/item.cc
index d73be7206d6..0017b64ba0d 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3469,6 +3469,16 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
     {
       if (*from_field)
       {
+        if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+            select->cur_pos_in_select_list != UNDEF_POS)
+        {
+          /*
+            As this is an outer field it should be added to the list of
+            non aggregated fields of the outer select.
+          */
+          marker= select->cur_pos_in_select_list;
+          select->non_agg_fields.push_back(this);
+        }
         if (*from_field != view_ref_found)
         {
           prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
@@ -3671,10 +3681,11 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
 bool Item_field::fix_fields(THD *thd, Item **reference)
 {
   DBUG_ASSERT(fixed == 0);
+  Field *from_field= (Field *)not_found_field;
+  bool outer_fixed= false;
+
   if (!field)					// If field is not checked
   {
-    Field *from_field= (Field *)not_found_field;
-    bool outer_fixed= false;
     /*
       In case of view, find_field_in_tables() write pointer to view field
       expression to 'reference', i.e. it substitute that expression instead
@@ -3766,6 +3777,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
         goto error;
       else if (!ret)
         return FALSE;
+      outer_fixed= 1;
     }
 
     set_field(from_field);
@@ -3809,6 +3821,13 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
   }
 #endif
   fixed= 1;
+  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      !outer_fixed && !thd->lex->in_sum_func &&
+      thd->lex->current_select->cur_pos_in_select_list != UNDEF_POS)
+  {
+    thd->lex->current_select->non_agg_fields.push_back(this);
+    marker= thd->lex->current_select->cur_pos_in_select_list;
+  }
   return FALSE;
 
 error:
diff --git a/sql/item.h b/sql/item.h
index c7c1218d3e9..6c41aa09f80 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -447,7 +447,8 @@ public:
   Item *next;
   uint32 max_length;
   uint name_length;                     /* Length of name */
-  uint8 marker, decimals;
+  int8 marker;
+  uint8 decimals;
   my_bool maybe_null;			/* If item may be null */
   my_bool null_value;			/* if item is null */
   my_bool unsigned_flag;
@@ -2263,6 +2264,9 @@ public:
   bool fix_fields(THD *, Item **);
   void print(String *str);
   table_map used_tables() const { return (table_map)0L; }
+  Field *get_tmp_table_field() { return 0; }
+  Item *copy_or_same(THD *thd) { return this; }
+  Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
   void cleanup();
 
 private:
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 936ae04e93d..e3505d2b4a9 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -147,6 +147,22 @@ longlong Item_func_not::val_int()
 }
 
 /*
+  We put any NOT expression into parenthesis to avoid
+  possible problems with internal view representations where
+  any '!' is converted to NOT. It may cause a problem if
+  '!' is used in an expression together with other operators
+  whose precedence is lower than the precedence of '!' yet
+  higher than the precedence of NOT.
+*/
+
+void Item_func_not::print(String *str)
+{
+  str->append('(');
+  Item_func::print(str);
+  str->append(')');
+}
+
+/*
   special NOT for ALL subquery
 */
 
@@ -813,11 +829,35 @@ longlong Item_in_optimizer::val_int()
           We disable the predicates we've pushed down into subselect, run the
           subselect and see if it has produced any rows.
         */
-        ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE;
-        longlong tmp= args[1]->val_bool_result();
-        result_for_null_param= null_value= 
-          !((Item_in_subselect*)args[1])->engine->no_rows();
-        ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE;
+        Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; 
+        if (cache->cols() == 1)
+        {
+          item_subs->set_cond_guard_var(0, FALSE);
+          longlong tmp= args[1]->val_bool_result();
+          result_for_null_param= null_value= !item_subs->engine->no_rows();
+          item_subs->set_cond_guard_var(0, TRUE);
+        }
+        else
+        {
+          uint i;
+          uint ncols= cache->cols();
+          /*
+            Turn off the predicates that are based on column compares for
+            which the left part is currently NULL
+          */
+          for (i= 0; i < ncols; i++)
+          {
+            if (cache->el(i)->null_value)
+              item_subs->set_cond_guard_var(i, FALSE);
+          }
+          
+          longlong tmp= args[1]->val_bool_result();
+          result_for_null_param= null_value= !item_subs->engine->no_rows();
+          
+          /* Turn all predicates back on */
+          for (i= 0; i < ncols; i++)
+            item_subs->set_cond_guard_var(i, TRUE);
+        }
       }
     }
     return 0;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7bdc90adcee..f18728c554b 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -269,6 +269,7 @@ public:
   enum Functype functype() const { return NOT_FUNC; }
   const char *func_name() const { return "not"; }
   Item *neg_transformer(THD *thd);
+  void print(String *str);
 };
 
 class Item_maxmin_subselect;
@@ -311,6 +312,7 @@ public:
   enum Functype functype() const { return TRIG_COND_FUNC; };
   const char *func_name() const { return "trigcond"; };
   bool const_item() const { return FALSE; }
+  bool *get_trig_var() { return trig_var; }
 };
 
 class Item_func_not_all :public Item_func_not
@@ -965,6 +967,10 @@ class Item_func_in :public Item_func_opt_neg
 {
 public:
   Item_result cmp_type;
+  /* 
+    an array of values when the right hand arguments of IN
+    are all SQL constant and there are no nulls 
+  */
   in_vector *array;
   cmp_item *in_item;
   bool have_null;
@@ -990,7 +996,7 @@ public:
     DBUG_VOID_RETURN;
   }
   optimize_type select_optimize() const
-    { return array ? OPTIMIZE_KEY : OPTIMIZE_NONE; }
+    { return OPTIMIZE_KEY; }
   void print(String *str);
   enum Functype functype() const { return IN_FUNC; }
   const char *func_name() const { return " IN "; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 1ef77208469..32cc90b96d6 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2729,25 +2729,28 @@ udf_handler::fix_fields(THD *thd, Item_result_field *func,
 
       if (arguments[i]->const_item())
       {
-        if (arguments[i]->null_value)
-          continue;
-
         switch (arguments[i]->result_type()) 
         {
         case STRING_RESULT:
         case DECIMAL_RESULT:
         {
           String *res= arguments[i]->val_str(&buffers[i]);
+          if (arguments[i]->null_value)
+            continue;
           f_args.args[i]= (char*) res->ptr();
           break;
         }
         case INT_RESULT:
           *((longlong*) to)= arguments[i]->val_int();
+          if (arguments[i]->null_value)
+            continue;
           f_args.args[i]= to;
           to+= ALIGN_SIZE(sizeof(longlong));
           break;
         case REAL_RESULT:
           *((double*) to)= arguments[i]->val_real();
+          if (arguments[i]->null_value)
+            continue;
           f_args.args[i]= to;
           to+= ALIGN_SIZE(sizeof(double));
           break;
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index f98a14d2cc6..95cc32b5c8e 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1694,6 +1694,19 @@ String *Item_func_encode::val_str(String *str)
   return res;
 }
 
+void Item_func_encode::print(String *str)
+{
+  str->append(func_name());
+  str->append('(');
+  args[0]->print(str);
+  str->append(',');
+  str->append('\'');
+  str->append(seed);
+  str->append('\'');
+  str->append(')');
+}
+
+
 String *Item_func_decode::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 67dd71fc886..60547d00a5c 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -358,19 +358,24 @@ class Item_func_encode :public Item_str_func
 {
  protected:
   SQL_CRYPT sql_crypt;
+  String seed;
 public:
-  Item_func_encode(Item *a, char *seed):
-    Item_str_func(a),sql_crypt(seed) {}
+  Item_func_encode(Item *a, char *seed_arg):
+    Item_str_func(a), sql_crypt(seed_arg)
+    {
+      seed.copy(seed_arg, strlen(seed_arg), default_charset_info);
+    }
   String *val_str(String *);
   void fix_length_and_dec();
   const char *func_name() const { return "encode"; }
+  void print(String *str);
 };
 
 
 class Item_func_decode :public Item_func_encode
 {
 public:
-  Item_func_decode(Item *a, char *seed): Item_func_encode(a,seed) {}
+  Item_func_decode(Item *a, char *seed_arg): Item_func_encode(a, seed_arg) {}
   String *val_str(String *);
   const char *func_name() const { return "decode"; }
 };
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index d5d26b7b741..e19815960a6 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -191,16 +191,16 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
   return res;
 }
 
-bool Item_subselect::exec(bool full_scan)
+bool Item_subselect::exec()
 {
   int res;
 
-  res= engine->exec(full_scan);
+  res= engine->exec();
 
   if (engine_changed)
   {
     engine_changed= 0;
-    return exec(full_scan);
+    return exec();
   }
   return (res);
 }
@@ -448,13 +448,13 @@ bool Item_singlerow_subselect::null_inside()
 
 void Item_singlerow_subselect::bring_value()
 {
-  exec(FALSE);
+  exec();
 }
 
 double Item_singlerow_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_real();
@@ -469,7 +469,7 @@ double Item_singlerow_subselect::val_real()
 longlong Item_singlerow_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_int();
@@ -483,7 +483,7 @@ longlong Item_singlerow_subselect::val_int()
 
 String *Item_singlerow_subselect::val_str(String *str)
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_str(str);
@@ -498,7 +498,7 @@ String *Item_singlerow_subselect::val_str(String *str)
 
 my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_decimal(decimal_value);
@@ -513,7 +513,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
 
 bool Item_singlerow_subselect::val_bool()
 {
-  if (!exec(FALSE) && !value->null_value)
+  if (!exec() && !value->null_value)
   {
     null_value= 0;
     return value->val_bool();
@@ -565,7 +565,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
   Item_exists_subselect(), optimizer(0), transformed(0),
-  enable_pushed_conds(TRUE), upper_item(0)
+  pushed_cond_guards(NULL), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -610,7 +610,7 @@ void Item_exists_subselect::fix_length_and_dec()
 double Item_exists_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -621,7 +621,7 @@ double Item_exists_subselect::val_real()
 longlong Item_exists_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -632,7 +632,7 @@ longlong Item_exists_subselect::val_int()
 String *Item_exists_subselect::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -645,7 +645,7 @@ String *Item_exists_subselect::val_str(String *str)
 my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -658,7 +658,7 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
 bool Item_exists_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec(FALSE))
+  if (exec())
   {
     reset();
     return 0;
@@ -676,7 +676,7 @@ double Item_in_subselect::val_real()
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -697,7 +697,7 @@ longlong Item_in_subselect::val_int()
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -718,7 +718,7 @@ String *Item_in_subselect::val_str(String *str)
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -738,7 +738,7 @@ bool Item_in_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -758,7 +758,7 @@ my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value)
   DBUG_ASSERT(0);
   null_value= 0;
   DBUG_ASSERT(fixed == 1);
-  if (exec(!enable_pushed_conds))
+  if (exec())
   {
     reset();
     null_value= 1;
@@ -922,7 +922,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 
   if (!substitution)
   {
-    //first call for this unit
+    /* We're invoked for the 1st (or the only) SELECT in the subquery UNION */
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
     substitution= optimizer;
 
@@ -948,19 +948,14 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
   }
+  if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
+  {
+    if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool))))
+      DBUG_RETURN(RES_ERROR);
+    pushed_cond_guards[0]= TRUE;
+  }
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
-  /*
-    Add the left part of a subselect to a WHERE or HAVING clause of
-    the right part, e.g. 
-    
-       SELECT 1 IN (SELECT a FROM t1)  =>
-       
-       SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1)
-       
-    HAVING is used only if the right part contains a SUM function, a GROUP
-    BY or a HAVING clause.
-  */
   if (join->having || select_lex->with_sum_func ||
       select_lex->group_list.elements)
   {
@@ -972,13 +967,13 @@ Item_in_subselect::single_value_transformer(JOIN *join,
                                                       ref_pointer_array,
                                                       (char *)"<ref>",
                                                       this->full_name()));
-    if (!abort_on_null && ((Item*)select_lex->item_list.head())->maybe_null)
+    if (!abort_on_null && left_expr->maybe_null)
     {
       /* 
         We can encounter "NULL IN (SELECT ...)". Wrap the added condition
-        within a trigger.
+        within a trig_cond.
       */
-      item= new Item_func_trig_cond(item, &enable_pushed_conds);
+      item= new Item_func_trig_cond(item, get_cond_guard(0));
     }
     
     /*
@@ -987,6 +982,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       argument (reference) to fix_fields()
     */
     select_lex->having= join->having= and_items(join->having, item);
+    if (join->having == item)
+      item->name= (char*)in_having_cond;
     select_lex->having_fix_field= 1;
     /*
       we do not check join->having->fixed, because Item_and (from and_items)
@@ -1013,14 +1010,19 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       item= func->create(expr, item);
       if (!abort_on_null && orig_item->maybe_null)
       {
-	having= 
-          new Item_func_trig_cond(new Item_is_not_null_test(this, having),
-                                  &enable_pushed_conds);
+	having= new Item_is_not_null_test(this, having);
+        if (left_expr->maybe_null)
+        {
+          if (!(having= new Item_func_trig_cond(having,
+                                                get_cond_guard(0))))
+            DBUG_RETURN(RES_ERROR);
+        }
 	/*
 	  Item_is_not_null_test can't be changed during fix_fields()
 	  we can assign select_lex->having here, and pass 0 as last
 	  argument (reference) to fix_fields()
 	*/
+        having->name= (char*)in_having_cond;
 	select_lex->having= join->having= having;
 	select_lex->having_fix_field= 1;
         /*
@@ -1032,17 +1034,25 @@ Item_in_subselect::single_value_transformer(JOIN *join,
         select_lex->having_fix_field= 0;
         if (tmp)
 	  DBUG_RETURN(RES_ERROR);
-        /* 
-          NOTE: It is important that we add this "IS NULL" here, even when
-          orig_item can't be NULL. This is needed so that this predicate is
-          only used by ref[_or_null] analyzer (and, e.g. is not used by const
-          propagation).
-        */
 	item= new Item_cond_or(item,
 			       new Item_func_isnull(orig_item));
-        item= new Item_func_trig_cond(item, &enable_pushed_conds);
       }
+      /* 
+        If we may encounter NULL IN (SELECT ...) and care whether subquery
+        result is NULL or FALSE, wrap condition in a trig_cond.
+      */
+      if (!abort_on_null && left_expr->maybe_null)
+      {
+        if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
+          DBUG_RETURN(RES_ERROR);
+      }
+      /*
+        TODO: figure out why the following is done here in 
+        single_value_transformer but there is no corresponding action in
+        row_value_transformer?
+      */
       item->name= (char *)in_additional_cond;
+
       /*
 	AND can't be changed during fix_fields()
 	we can assign select_lex->having here, and pass 0 as last
@@ -1073,10 +1083,16 @@ Item_in_subselect::single_value_transformer(JOIN *join,
                                             select_lex->ref_pointer_array,
                                             (char *)"<no matter>",
                                             (char *)"<result>"));
-        new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds);
+        if (!abort_on_null && left_expr->maybe_null)
+        {
+          if (!(new_having= new Item_func_trig_cond(new_having,
+                                                    get_cond_guard(0))))
+            DBUG_RETURN(RES_ERROR);
+        }
+        new_having->name= (char*)in_having_cond;
 	select_lex->having= join->having= new_having;
-
 	select_lex->having_fix_field= 1;
+        
         /*
           we do not check join->having->fixed, because comparison function
           (from func->create) can't be fixed after creation
@@ -1146,6 +1162,15 @@ Item_in_subselect::row_value_transformer(JOIN *join)
 
     thd->lex->current_select= current;
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
+
+    if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
+    {
+      if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) *
+                                                        left_expr->cols())))
+        DBUG_RETURN(RES_ERROR);
+      for (uint i= 0; i < cols_num; i++)
+        pushed_cond_guards[i]= TRUE;
+    }
   }
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
@@ -1162,6 +1187,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                                 is_not_null_test(v3))
       where is_not_null_test used to register nulls in case if we have
       not found matching to return correct NULL value
+      TODO: say here explicitly if the order of AND parts matters or not.
     */
     Item *item_having_part2= 0;
     for (uint i= 0; i < cols_num; i++)
@@ -1190,21 +1216,28 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                                       (char *)"<no matter>",
                                       (char *)"<list ref>")
                             );
-      having_item=
-        and_items(having_item,
-                  new Item_cond_or(item_eq, item_isnull));
-      item_having_part2=
-        and_items(item_having_part2,
-                  new
-                  Item_is_not_null_test(this,
-                                        new
-                                        Item_ref(&select_lex->context,
-                                                 select_lex->
-                                                 ref_pointer_array + i,
-                                                 (char *)"<no matter>",
-                                                 (char *)"<list ref>")
-                                       )
-                 );
+      Item *col_item= new Item_cond_or(item_eq, item_isnull);
+      if (!abort_on_null && left_expr->el(i)->maybe_null)
+      {
+        if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i))))
+          DBUG_RETURN(RES_ERROR);
+      }
+      having_item= and_items(having_item, col_item);
+      
+      Item *item_nnull_test= 
+         new Item_is_not_null_test(this,
+                                   new Item_ref(&select_lex->context,
+                                                select_lex->
+                                                ref_pointer_array + i,
+                                                (char *)"<no matter>",
+                                                (char *)"<list ref>"));
+      if (!abort_on_null && left_expr->el(i)->maybe_null)
+      {
+        if (!(item_nnull_test= 
+              new Item_func_trig_cond(item_nnull_test, get_cond_guard(i))))
+          DBUG_RETURN(RES_ERROR);
+      }
+      item_having_part2= and_items(item_having_part2, item_nnull_test);
       item_having_part2->top_level_item();
     }
     having_item= and_items(having_item, item_having_part2);
@@ -1253,18 +1286,15 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                         );
       if (!abort_on_null)
       {
-        having_item=
-          and_items(having_item,
-                    new
-                    Item_is_not_null_test(this,
-                                          new
-                                          Item_ref(&select_lex->context,
-                                                   select_lex->
-                                                   ref_pointer_array + i,
-                                                   (char *)"<no matter>",
-                                                   (char *)"<list ref>")
-                                         )
-                  );
+        Item *having_col_item=
+          new Item_is_not_null_test(this,
+                                    new
+                                    Item_ref(&select_lex->context, 
+                                             select_lex->ref_pointer_array + i,
+                                             (char *)"<no matter>",
+                                             (char *)"<list ref>"));
+        
+        
         item_isnull= new
           Item_func_isnull(new
                            Item_direct_ref(&select_lex->context,
@@ -1273,14 +1303,23 @@ Item_in_subselect::row_value_transformer(JOIN *join)
                                            (char *)"<no matter>",
                                            (char *)"<list ref>")
                           );
-
         item= new Item_cond_or(item, item_isnull);
+        /* 
+          TODO: why we create the above for cases where the right part
+                cant be NULL?
+        */
+        if (left_expr->el(i)->maybe_null)
+        {
+          if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
+            DBUG_RETURN(RES_ERROR);
+          if (!(having_col_item= 
+                  new Item_func_trig_cond(having_col_item, get_cond_guard(i))))
+            DBUG_RETURN(RES_ERROR);
+        }
+        having_item= and_items(having_item, having_col_item);
       }
-
       where_item= and_items(where_item, item);
     }
-    if (where_item)
-      where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds);
     /*
       AND can't be changed during fix_fields()
       we can assign select_lex->where here, and pass 0 as last
@@ -1294,9 +1333,9 @@ Item_in_subselect::row_value_transformer(JOIN *join)
   if (having_item)
   {
     bool res;
-    having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds);
-
     select_lex->having= join->having= and_items(join->having, having_item);
+    if (having_item == select_lex->having)
+      having_item->name= (char*)in_having_cond;
     select_lex->having->top_level_item();
     /*
       AND can't be changed during fix_fields()
@@ -1682,7 +1721,7 @@ int  init_read_record_seq(JOIN_TAB *tab);
 int join_read_always_key_or_null(JOIN_TAB *tab);
 int join_read_next_same_or_null(READ_RECORD *info);
 
-int subselect_single_select_engine::exec(bool full_scan)
+int subselect_single_select_engine::exec()
 {
   DBUG_ENTER("subselect_single_select_engine::exec");
   char const *save_where= thd->where;
@@ -1720,9 +1759,13 @@ int subselect_single_select_engine::exec(bool full_scan)
   if (!executed)
   {
     item->reset_value_registration();
-    if (full_scan)
+    bool have_changed_access= FALSE;
+    JOIN_TAB *changed_tabs[MAX_TABLES];
+    JOIN_TAB **last_changed_tab= changed_tabs;
+    if (item->have_guarded_conds())
     {
       /*
+        For at least one of the pushed predicates the following is true:
         We should not apply optimizations based on the condition that was
         pushed down into the subquery. Those optimizations are ref[_or_null]
         acceses. Change them to be full table scans.
@@ -1730,32 +1773,36 @@ int subselect_single_select_engine::exec(bool full_scan)
       for (uint i=join->const_tables ; i < join->tables ; i++)
       {
         JOIN_TAB *tab=join->join_tab+i;
-        if (tab->keyuse && tab->keyuse->outer_ref)
+        if (tab && tab->keyuse)
         {
-          tab->read_first_record= init_read_record_seq;
-          tab->read_record.record= tab->table->record[0];
-          tab->read_record.thd= join->thd;
-          tab->read_record.ref_length= tab->table->file->ref_length;
+          for (uint i= 0; i < tab->ref.key_parts; i++)
+          {
+            bool *cond_guard= tab->ref.cond_guards[i];
+            if (cond_guard && !*cond_guard)
+            {
+              /* Change the access method to full table scan */
+              tab->read_first_record= init_read_record_seq;
+              tab->read_record.record= tab->table->record[0];
+              tab->read_record.thd= join->thd;
+              tab->read_record.ref_length= tab->table->file->ref_length;
+              *(last_changed_tab++)= tab;
+              break;
+            }
+          }
         }
       }
     }
     
     join->exec();
 
-    if (full_scan)
+    /* Enable the optimizations back */
+    for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
     {
-      /* Enable the optimizations back */
-      for (uint i=join->const_tables ; i < join->tables ; i++)
-      {
-        JOIN_TAB *tab=join->join_tab+i;
-        if (tab->keyuse && tab->keyuse->outer_ref)
-        {
-          tab->read_record.record= 0;
-          tab->read_record.ref_length= 0;
-          tab->read_first_record= join_read_always_key_or_null;
-          tab->read_record.read_record= join_read_next_same_or_null;
-        }
-      }
+      JOIN_TAB *tab= *ptab;
+      tab->read_record.record= 0;
+      tab->read_record.ref_length= 0;
+      tab->read_first_record= join_read_always_key_or_null;
+      tab->read_record.read_record= join_read_next_same_or_null;
     }
     executed= 1;
     thd->where= save_where;
@@ -1767,13 +1814,9 @@ int subselect_single_select_engine::exec(bool full_scan)
   DBUG_RETURN(0);
 }
 
-int subselect_union_engine::exec(bool full_scan)
+int subselect_union_engine::exec()
 {
   char const *save_where= thd->where;
-  /* 
-    Ignore the full_scan parameter: the pushed down predicates are only used
-    for filtering, and the caller has disabled them if necessary.
-  */
   int res= unit->exec();
   thd->where= save_where;
   return res;
@@ -1781,7 +1824,7 @@ int subselect_union_engine::exec(bool full_scan)
 
 
 /*
-  Search for at least on row satisfying select condition
+  Search for at least one row satisfying select condition
  
   SYNOPSIS
     subselect_uniquesubquery_engine::scan_table()
@@ -1790,8 +1833,8 @@ int subselect_union_engine::exec(bool full_scan)
     Scan the table using sequential access until we find at least one row
     satisfying select condition.
     
-    The result of this function (info about whether a row was found) is
-    stored in this->empty_result_set.
+    The caller must set this->empty_result_set=FALSE before calling this
+    function. This function will set it to TRUE if it finds a matching row.
 
   RETURN
     FALSE - OK
@@ -1803,7 +1846,6 @@ int subselect_uniquesubquery_engine::scan_table()
   int error;
   TABLE *table= tab->table;
   DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
-  empty_result_set= TRUE;
 
   if (table->file->inited)
     table->file->ha_index_end();
@@ -1896,10 +1938,13 @@ bool subselect_uniquesubquery_engine::copy_ref_key()
       - FALSE otherwise.
 
     In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
-    the caller doesn't distinguish between NULL and FALSE result and we just 
+    the caller doesn't distinguish between NULL and FALSE result and we just
     return FALSE. 
-    Otherwise we make a full table scan to see if there is at least one matching row.
-  
+    Otherwise we make a full table scan to see if there is at least one 
+    matching row.
+    
+    The result of this function (info about whether a row was found) is
+    stored in this->empty_result_set.
   NOTE
     
   RETURN
@@ -1907,11 +1952,12 @@ bool subselect_uniquesubquery_engine::copy_ref_key()
     TRUE  - an error occured while scanning
 */
 
-int subselect_uniquesubquery_engine::exec(bool full_scan)
+int subselect_uniquesubquery_engine::exec()
 {
   DBUG_ENTER("subselect_uniquesubquery_engine::exec");
   int error;
   TABLE *table= tab->table;
+  empty_result_set= TRUE;
  
   /* TODO: change to use of 'full_scan' here? */
   if (copy_ref_key())
@@ -1932,9 +1978,13 @@ int subselect_uniquesubquery_engine::exec(bool full_scan)
   {
     error= 0;
     table->null_row= 0;
-    ((Item_in_subselect *) item)->value= (!table->status &&
-                                          (!cond || cond->val_int()) ? 1 :
-                                          0);
+    if (!table->status && (!cond || cond->val_int()))
+    {
+      ((Item_in_subselect *) item)->value= 1;
+      empty_result_set= FALSE;
+    }
+    else
+      ((Item_in_subselect *) item)->value= 0;
   }
 
   DBUG_RETURN(error != 0);
@@ -2000,7 +2050,7 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine()
     1
 */
 
-int subselect_indexsubquery_engine::exec(bool full_scan)
+int subselect_indexsubquery_engine::exec()
 {
   DBUG_ENTER("subselect_indexsubquery_engine::exec");
   int error;
@@ -2041,8 +2091,9 @@ int subselect_indexsubquery_engine::exec(bool full_scan)
       table->null_row= 0;
       if (!table->status)
       {
-        if (!cond || cond->val_int())
+        if ((!cond || cond->val_int()) && (!having || having->val_int()))
         {
+          empty_result_set= FALSE;
           if (null_finding)
             ((Item_in_subselect *) item)->was_null= 1;
           else
@@ -2185,11 +2236,16 @@ void subselect_indexsubquery_engine::print(String *str)
   str->append(key_info->name);
   if (check_null)
     str->append(STRING_WITH_LEN(" checking NULL"));
-    if (cond)
+  if (cond)
   {
     str->append(STRING_WITH_LEN(" where "));
     cond->print(str);
   }
+  if (having)
+  {
+    str->append(STRING_WITH_LEN(" having "));
+    having->print(str);
+  }
   str->append(')');
 }
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 69ac78d859b..25a0326f8b6 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -94,7 +94,7 @@ public:
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
-  virtual bool exec(bool full_scan);
+  virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
   table_map not_null_tables() const { return 0; }
@@ -104,6 +104,7 @@ public:
   Item *get_tmp_table_item(THD *thd);
   void update_used_tables();
   void print(String *str);
+  virtual bool have_guarded_conds() { return FALSE; }
   bool change_engine(subselect_engine *eng)
   {
     old_engine= engine;
@@ -249,13 +250,21 @@ protected:
   bool transformed;
 public:
   /* Used to trigger on/off conditions that were pushed down to subselect */
-  bool enable_pushed_conds;
+  bool *pushed_cond_guards;
+
+  bool *get_cond_guard(int i)
+  {
+    return pushed_cond_guards ? pushed_cond_guards + i : NULL;
+  }
+  void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; }
+  bool have_guarded_conds() { return test(pushed_cond_guards); }
+
   Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery
 
   Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect()
     :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
-     enable_pushed_conds(TRUE), upper_item(0)
+     pushed_cond_guards(NULL), upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -340,23 +349,22 @@ public:
 
     SYNOPSIS
       exec()
-        full_scan TRUE  - Pushed-down predicates are disabled, the engine
-                          must disable made based on those predicates.
-                  FALSE - Pushed-down predicates are in effect.
+
     DESCRIPTION
       Execute the engine. The result of execution is subquery value that is
       either captured by previously set up select_result-based 'sink' or
       stored somewhere by the exec() method itself.
 
-      A required side effect: if full_scan==TRUE, subselect_engine->no_rows()
-      should return correct result.
+      A required side effect: If at least one pushed-down predicate is
+      disabled, subselect_engine->no_rows() must return correct result after 
+      the exec() call.
 
     RETURN
       0 - OK
-      1 - Either an execution error, or the engine was be "changed", and
+      1 - Either an execution error, or the engine was "changed", and the
           caller should call exec() again for the new engine.
   */
-  virtual int exec(bool full_scan)= 0;
+  virtual int exec()= 0;
   virtual uint cols()= 0; /* return number of columns in select */
   virtual uint8 uncacheable()= 0; /* query is uncacheable */
   enum Item_result type() { return res_type; }
@@ -391,7 +399,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols();
   uint8 uncacheable();
   void exclude();
@@ -415,7 +423,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols();
   uint8 uncacheable();
   void exclude();
@@ -429,11 +437,30 @@ public:
 
 
 struct st_join_table;
+
+
+/*
+  A subquery execution engine that evaluates the subquery by doing one index
+  lookup in a unique index.
+
+  This engine is used to resolve subqueries in forms
+  
+    outer_expr IN (SELECT tbl.unique_key FROM tbl WHERE subq_where) 
+    
+  or, tuple-based:
+  
+    (oe1, .. oeN) IN (SELECT uniq_key_part1, ... uniq_key_partK
+                      FROM tbl WHERE subqwhere) 
+  
+  i.e. the subquery is a single table SELECT without GROUP BY, aggregate
+  functions, etc.
+*/
+
 class subselect_uniquesubquery_engine: public subselect_engine
 {
 protected:
   st_join_table *tab;
-  Item *cond;
+  Item *cond; /* The WHERE condition of subselect */
   /* 
     TRUE<=> last execution produced empty set. Valid only when left
     expression is NULL.
@@ -453,7 +480,7 @@ public:
   void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
-  int exec(bool full_scan);
+  int exec();
   uint cols() { return 1; }
   uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
   void exclude();
@@ -471,16 +498,47 @@ class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine
 {
   /* FALSE for 'ref', TRUE for 'ref-or-null'. */
   bool check_null;
+  /* 
+    The "having" clause. This clause (further reffered to as "artificial
+    having") was inserted by subquery transformation code. It contains 
+    Item(s) that have a side-effect: they record whether the subquery has 
+    produced a row with NULL certain components. We need to use it for cases
+    like
+      (oe1, oe2) IN (SELECT t.key, t.no_key FROM t1)
+    where we do index lookup on t.key=oe1 but need also to check if there
+    was a row such that t.no_key IS NULL.
+    
+    NOTE: This is currently here and not in the uniquesubquery_engine. Ideally
+    it should have been in uniquesubquery_engine in order to allow execution of
+    subqueries like
+    
+      (oe1, oe2) IN (SELECT primary_key, non_key_maybe_null_field FROM tbl)
+
+    We could use uniquesubquery_engine for the first component and let
+    Item_is_not_null_test( non_key_maybe_null_field) to handle the second.
+
+    However, subqueries like the above are currently not handled by index
+    lookup-based subquery engines, the engine applicability check misses
+    them: it doesn't switch the engine for case of artificial having and
+    [eq_]ref access (only for artifical having + ref_or_null or no having).
+    The above example subquery is handled as a full-blown SELECT with eq_ref
+    access to one table.
+
+    Due to this limitation, the "artificial having" currently needs to be 
+    checked by only in indexsubquery_engine.
+  */
+  Item *having;
 public:
 
   // constructor can assign THD because it will be called after JOIN::prepare
   subselect_indexsubquery_engine(THD *thd, st_join_table *tab_arg,
 				 Item_subselect *subs, Item *where,
-				 bool chk_null)
+                                 Item *having_arg, bool chk_null)
     :subselect_uniquesubquery_engine(thd, tab_arg, subs, where),
-     check_null(chk_null)
+     check_null(chk_null),
+     having(having_arg)
   {}
-  int exec(bool full_scan);
+  int exec();
   void print (String *str);
 };
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 8ffa73d1707..f25dd064b19 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -414,7 +414,11 @@ MY_LOCALE *my_locale_by_number(uint number);
 #define UNCACHEABLE_EXPLAIN     8
 /* Don't evaluate subqueries in prepare even if they're not correlated */
 #define UNCACHEABLE_PREPARE    16
+/* For uncorrelated SELECT in an UNION with some correlated SELECTs */
+#define UNCACHEABLE_UNITED     32
 
+/* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */
+#define UNDEF_POS (-1)
 #ifdef EXTRA_DEBUG
 /*
   Sync points allow us to force the server to reach a certain line of code
@@ -1197,7 +1201,7 @@ extern const char *command_name[];
 extern const char *first_keyword, *my_localhost, *delayed_user, *binary_keyword;
 extern const char **errmesg;			/* Error messages */
 extern const char *myisam_recover_options_str;
-extern const char *in_left_expr_name, *in_additional_cond;
+extern const char *in_left_expr_name, *in_additional_cond, *in_having_cond;
 extern const char * const triggers_file_ext;
 extern const char * const trigname_file_ext;
 extern Eq_creator eq_creator;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 4ee8c2e7e31..eff0a3dd227 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -453,10 +453,13 @@ char *mysqld_unix_port, *opt_mysql_tmpdir;
 const char **errmesg;			/* Error messages */
 const char *myisam_recover_options_str="OFF";
 const char *myisam_stats_method_str="nulls_unequal";
+
 /* name of reference on left espression in rewritten IN subquery */
 const char *in_left_expr_name= "<left expr>";
 /* name of additional condition */
 const char *in_additional_cond= "<IN COND>";
+const char *in_having_cond= "<IN HAVING>";
+
 my_decimal decimal_zero;
 /* classes for comparation parsing/processing */
 Eq_creator eq_creator;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a4318f7b4bf..0949d4aa331 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4406,6 +4406,7 @@ bool setup_fields(THD *thd, Item **ref_pointer_array,
     bzero(ref_pointer_array, sizeof(Item *) * fields.elements);
 
   Item **ref= ref_pointer_array;
+  thd->lex->current_select->cur_pos_in_select_list= 0;
   while ((item= it++))
   {
     if (!item->fixed && item->fix_fields(thd, it.ref()) ||
@@ -4421,7 +4422,10 @@ bool setup_fields(THD *thd, Item **ref_pointer_array,
 	sum_func_list)
       item->split_sum_func(thd, ref_pointer_array, *sum_func_list);
     thd->used_tables|= item->used_tables();
+    thd->lex->current_select->cur_pos_in_select_list++;
   }
+  thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS;
+
   thd->lex->allow_sum_func= save_allow_sum_func;
   thd->set_query_id= save_set_query_id;
   DBUG_RETURN(test(thd->net.report_error));
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 9264362a1c8..e8291edda5d 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -142,7 +142,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
 
   if (order && order->elements)
   {
-    uint         length;
+    uint         length= 0;
     SORT_FIELD  *sortorder;
     TABLE_LIST   tables;
     List<Item>   fields;
@@ -162,7 +162,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       DBUG_RETURN(TRUE);
     }
     
-    if (!select && limit != HA_POS_ERROR)
+    if ((!select || table->quick_keys.is_clear_all()) && limit != HA_POS_ERROR)
       usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);
 
     if (usable_index == MAX_KEY)
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index c60d3c307d0..fb59aeea8e7 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -80,6 +80,65 @@ static bool check_view_insertability(THD *thd, TABLE_LIST *view);
 #define my_safe_afree(ptr, size, min_length) if (size > min_length) my_free(ptr,MYF(0))
 #endif
 
+/*
+  Check that insert/update fields are from the same single table of a view.
+
+  SYNOPSIS
+    check_view_single_update()
+    fields            The insert/update fields to be checked.
+    view              The view for insert.
+    map     [in/out]  The insert table map.
+
+  DESCRIPTION
+    This function is called in 2 cases:
+    1. to check insert fields. In this case *map will be set to 0.
+       Insert fields are checked to be all from the same single underlying
+       table of the given view. Otherwise the error is thrown. Found table
+       map is returned in the map parameter.
+    2. to check update fields of the ON DUPLICATE KEY UPDATE clause.
+       In this case *map contains table_map found on the previous call of
+       the function to check insert fields. Update fields are checked to be
+       from the same table as the insert fields.
+
+  RETURN
+    0   OK
+    1   Error
+*/
+
+bool check_view_single_update(List<Item> &fields, TABLE_LIST *view,
+                              table_map *map)
+{
+  /* it is join view => we need to find the table for update */
+  List_iterator_fast<Item> it(fields);
+  Item *item;
+  TABLE_LIST *tbl= 0;            // reset for call to check_single_table()
+  table_map tables= 0;
+
+  while ((item= it++))
+    tables|= item->used_tables();
+
+  /* Check found map against provided map */
+  if (*map)
+  {
+    if (tables != *map)
+      goto error;
+    return FALSE;
+  }
+
+  if (view->check_single_table(&tbl, tables, view) || tbl == 0)
+    goto error;
+
+  view->table= tbl->table;
+  *map= tables;
+
+  return FALSE;
+
+error:
+  my_error(ER_VIEW_MULTIUPDATE, MYF(0),
+           view->view_db.str, view->view_name.str);
+  return TRUE;
+}
+
 
 /*
   Check if insert fields are correct.
@@ -104,7 +163,7 @@ static bool check_view_insertability(THD *thd, TABLE_LIST *view);
 
 static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
                                List<Item> &fields, List<Item> &values,
-                               bool check_unique)
+                               bool check_unique, table_map *map)
 {
   TABLE *table= table_list->table;
 
@@ -177,21 +236,9 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
 
     if (table_list->effective_algorithm == VIEW_ALGORITHM_MERGE)
     {
-      /* it is join view => we need to find table for update */
-      List_iterator_fast<Item> it(fields);
-      Item *item;
-      TABLE_LIST *tbl= 0;            // reset for call to check_single_table()
-      table_map map= 0;
-
-      while ((item= it++))
-        map|= item->used_tables();
-      if (table_list->check_single_table(&tbl, map, table_list) || tbl == 0)
-      {
-        my_error(ER_VIEW_MULTIUPDATE, MYF(0),
-                 table_list->view_db.str, table_list->view_name.str);
+      if (check_view_single_update(fields, table_list, map))
         return -1;
-      }
-      table_list->table= table= tbl->table;
+      table= table_list->table;
     }
 
     if (check_unique && thd->dupp_field)
@@ -241,7 +288,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
 */
 
 static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
-                               List<Item> &update_fields)
+                               List<Item> &update_fields, table_map *map)
 {
   TABLE *table= insert_table_list->table;
   query_id_t timestamp_query_id;
@@ -264,6 +311,10 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
   if (setup_fields(thd, 0, update_fields, 1, 0, 0))
     return -1;
 
+  if (insert_table_list->effective_algorithm == VIEW_ALGORITHM_MERGE &&
+      check_view_single_update(update_fields, insert_table_list, map))
+    return -1;
+
   if (table->timestamp_field)
   {
     /* Don't set timestamp column if this is modified. */
@@ -910,6 +961,7 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
   Name_resolution_context_state ctx_state;
   bool insert_into_view= (table_list->view != 0);
   bool res= 0;
+  table_map map= 0;
   DBUG_ENTER("mysql_prepare_insert");
   DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d",
 		       (ulong)table_list, (ulong)table,
@@ -959,12 +1011,12 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
   /* Prepare the fields in the statement. */
   if (values &&
       !(res= check_insert_fields(thd, context->table_list, fields, *values,
-                                 !insert_into_view) ||
+                                 !insert_into_view, &map) ||
         setup_fields(thd, 0, *values, 0, 0, 0)) &&
       duplic == DUP_UPDATE)
   {
     select_lex->no_wrap_view_item= TRUE;
-    res= check_update_fields(thd, context->table_list, update_fields);
+    res= check_update_fields(thd, context->table_list, update_fields, &map);
     select_lex->no_wrap_view_item= FALSE;
     /*
       When we are not using GROUP BY we can refer to other tables in the
@@ -2286,6 +2338,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
 {
   LEX *lex= thd->lex;
   int res;
+  table_map map= 0;
   SELECT_LEX *lex_current_select_save= lex->current_select;
   DBUG_ENTER("select_insert::prepare");
 
@@ -2297,7 +2350,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
   */
   lex->current_select= &lex->select_lex;
   res= check_insert_fields(thd, table_list, *fields, values,
-                           !insert_into_view) ||
+                           !insert_into_view, &map) ||
        setup_fields(thd, 0, values, 0, 0, 0);
 
   if (info.handle_duplicates == DUP_UPDATE)
@@ -2315,7 +2368,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
 
     lex->select_lex.no_wrap_view_item= TRUE;
     res= res || check_update_fields(thd, context->table_list,
-                                    *info.update_fields);
+                                    *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
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index f14856e23fa..a362e6c7fd2 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1190,6 +1190,8 @@ void st_select_lex::init_select()
   offset_limit= 0;      /* denotes the default offset = 0 */
   with_sum_func= 0;
   is_correlated= 0;
+  cur_pos_in_select_list= UNDEF_POS;
+  non_agg_fields.empty();
 }
 
 /*
@@ -1379,9 +1381,17 @@ void st_select_lex::mark_as_dependent(SELECT_LEX *last)
     if (!(s->uncacheable & UNCACHEABLE_DEPENDENT))
     {
       // Select is dependent of outer select
-      s->uncacheable|= UNCACHEABLE_DEPENDENT;
+      s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) |
+                       UNCACHEABLE_DEPENDENT;
       SELECT_LEX_UNIT *munit= s->master_unit();
-      munit->uncacheable|= UNCACHEABLE_DEPENDENT;
+      munit->uncacheable= (munit->uncacheable & ~UNCACHEABLE_UNITED) |
+                       UNCACHEABLE_DEPENDENT;
+      for (SELECT_LEX *sl= munit->first_select(); sl ; sl= sl->next_select())
+      {
+        if (sl != s &&
+            !(sl->uncacheable & (UNCACHEABLE_DEPENDENT | UNCACHEABLE_UNITED)))
+          sl->uncacheable|= UNCACHEABLE_UNITED;
+      }
     }
   is_correlated= TRUE;
   this->master_unit()->item->is_correlated= TRUE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5731e009cb4..d9dbc80e9a7 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -469,7 +469,7 @@ public:
   void set_thd(THD *thd_arg) { thd= thd_arg; }
 
   friend void lex_start(THD *thd, uchar *buf, uint length);
-  friend int subselect_union_engine::exec(bool);
+  friend int subselect_union_engine::exec();
 
   List<Item> *get_unit_column_types();
 };
@@ -580,6 +580,10 @@ public:
   bool no_wrap_view_item;
   /* exclude this select from check of unique_table() */
   bool exclude_from_table_unique_test;
+  /* List of fields that aren't under an aggregate function */
+  List<Item_field> non_agg_fields;
+  /* index in the select list of the expression currently being fixed */
+  int cur_pos_in_select_list;
 
   List<udf_func>     udf_list;                  /* udf function calls stack */
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 152cc3aa385..002b1d52331 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1676,7 +1676,7 @@ static bool check_prepared_statement(Prepared_statement *stmt,
   case SQLCOM_INSERT:
     res= mysql_test_insert(stmt, tables, lex->field_list,
                            lex->many_values,
-                           select_lex->item_list, lex->value_list,
+                           lex->update_list, lex->value_list,
                            lex->duplicates);
     break;
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d62032c2b44..dbfdb783c9a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -513,72 +513,88 @@ err:
 
 
 /*
-  test if it is known for optimisation IN subquery
+  Remove the predicates pushed down into the subquery
 
   SYNOPSIS
-    JOIN::test_in_subselect()
-    where - pointer for variable in which conditions should be
-            stored if subquery is known
+    JOIN::remove_subq_pushed_predicates()
+      where   IN  Must be NULL
+              OUT The remaining WHERE condition, or NULL
 
-  RETURN
-    1 - known
-    0 - unknown
+  DESCRIPTION
+    Given that this join will be executed using (unique|index)_subquery,
+    without "checking NULL", remove the predicates that were pushed down
+    into the subquery.
+
+    We can remove the equalities that will be guaranteed to be true by the
+    fact that subquery engine will be using index lookup.
+
+    If the subquery compares scalar values, we can remove the condition that
+    was wrapped into trig_cond (it will be checked when needed by the subquery
+    engine)
+
+    If the subquery compares row values, we need to keep the wrapped
+    equalities in the WHERE clause: when the left (outer) tuple has both NULL
+    and non-NULL values, we'll do a full table scan and will rely on the
+    equalities corresponding to non-NULL parts of left tuple to filter out
+    non-matching records.
 */
 
-bool JOIN::test_in_subselect(Item **where)
+void JOIN::remove_subq_pushed_predicates(Item **where)
 {
   if (conds->type() == Item::FUNC_ITEM &&
       ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
       ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
       ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
   {
-    join_tab->info= "Using index";
     *where= 0;
-    return 1;
+    return;
   }
   if (conds->type() == Item::COND_ITEM &&
       ((class Item_func *)this->conds)->functype() ==
       Item_func::COND_AND_FUNC)
   {
-    if ((*where= remove_additional_cond(conds)))
-      join_tab->info= "Using index; Using where";
-    else
-      join_tab->info= "Using index";
-    return 1;
+    *where= remove_additional_cond(conds);
   }
-  return 0;
 }
 
 
 /*
-  Check if the passed HAVING clause is a clause added by subquery optimizer
+  Index lookup-based subquery: save some flags for EXPLAIN output
 
   SYNOPSIS
-    is_having_subq_predicates()
-      having  Having clause
+    save_index_subquery_explain_info()
+      join_tab  Subquery's join tab (there is only one as index lookup is
+                only used for subqueries that are single-table SELECTs)
+      where     Subquery's WHERE clause
 
-  RETURN
-    TRUE   The passed HAVING clause was added by the subquery optimizer
-    FALSE  Otherwise
+  DESCRIPTION
+    For index lookup-based subquery (i.e. one executed with
+    subselect_uniquesubquery_engine or subselect_indexsubquery_engine),
+    check its EXPLAIN output row should contain 
+      "Using index" (TAB_INFO_FULL_SCAN_ON_NULL) 
+      "Using Where" (TAB_INFO_USING_WHERE)
+      "Full scan on NULL key" (TAB_INFO_FULL_SCAN_ON_NULL)
+    and set appropriate flags in join_tab->packed_info.
 */
 
-bool is_having_subq_predicates(Item *having)
+static void save_index_subquery_explain_info(JOIN_TAB *join_tab, Item* where)
 {
-  if (having->type() == Item::FUNC_ITEM)
+  join_tab->packed_info= TAB_INFO_HAVE_VALUE;
+  if (join_tab->table->used_keys.is_set(join_tab->ref.key))
+    join_tab->packed_info |= TAB_INFO_USING_INDEX;
+  if (where)
+    join_tab->packed_info |= TAB_INFO_USING_WHERE;
+  for (uint i = 0; i < join_tab->ref.key_parts; i++)
   {
-    if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
-      return TRUE;
-    if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC)
+    if (join_tab->ref.cond_guards[i])
     {
-      having= ((Item_func*)having)->arguments()[0];
-      if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
-        return TRUE;
+      join_tab->packed_info |= TAB_INFO_FULL_SCAN_ON_NULL;
+      break;
     }
-    return TRUE;
   }
-  return FALSE;
 }
 
+
 /*
   global select optimisation.
   return 0 - success
@@ -1017,51 +1033,47 @@ JOIN::optimize()
       if (join_tab[0].type == JT_EQ_REF &&
 	  join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
-	if (test_in_subselect(&where))
-	{
-	  join_tab[0].type= JT_UNIQUE_SUBQUERY;
-	  error= 0;
-	  DBUG_RETURN(unit->item->
-		      change_engine(new
-				    subselect_uniquesubquery_engine(thd,
-								    join_tab,
-								    unit->item,
-								    where)));
-	}
+        remove_subq_pushed_predicates(&where);
+        save_index_subquery_explain_info(join_tab, where);
+        join_tab[0].type= JT_UNIQUE_SUBQUERY;
+        error= 0;
+        DBUG_RETURN(unit->item->
+                    change_engine(new
+                                  subselect_uniquesubquery_engine(thd,
+                                                                  join_tab,
+                                                                  unit->item,
+                                                                  where)));
       }
       else if (join_tab[0].type == JT_REF &&
 	       join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
-	if (test_in_subselect(&where))
-	{
-	  join_tab[0].type= JT_INDEX_SUBQUERY;
-	  error= 0;
-	  DBUG_RETURN(unit->item->
-		      change_engine(new
-				    subselect_indexsubquery_engine(thd,
-								   join_tab,
-								   unit->item,
-								   where,
-								   0)));
-	}
+	remove_subq_pushed_predicates(&where);
+        save_index_subquery_explain_info(join_tab, where);
+        join_tab[0].type= JT_INDEX_SUBQUERY;
+        error= 0;
+        DBUG_RETURN(unit->item->
+                    change_engine(new
+                                  subselect_indexsubquery_engine(thd,
+                                                                 join_tab,
+                                                                 unit->item,
+                                                                 where,
+                                                                 NULL,
+                                                                 0)));
       }
     } else if (join_tab[0].type == JT_REF_OR_NULL &&
 	       join_tab[0].ref.items[0]->name == in_left_expr_name &&
-               is_having_subq_predicates(having))
+               having->name == in_having_cond)
     {
       join_tab[0].type= JT_INDEX_SUBQUERY;
       error= 0;
-
-      if ((conds= remove_additional_cond(conds)))
-	join_tab->info= "Using index; Using where";
-      else
-	join_tab->info= "Using index";
-
+      conds= remove_additional_cond(conds);
+      save_index_subquery_explain_info(join_tab, conds);
       DBUG_RETURN(unit->item->
 		  change_engine(new subselect_indexsubquery_engine(thd,
 								   join_tab,
 								   unit->item,
 								   conds,
+                                                                   having,
 								   1)));
     }
 
@@ -2557,9 +2569,7 @@ typedef struct key_field_t {		// Used when finding key fields
     when val IS NULL.
   */
   bool          null_rejecting; 
-
-  /* TRUE<=> This ref access is an outer subquery reference access */
-  bool          outer_ref;
+  bool         *cond_guard; /* See KEYUSE::cond_guard */
 } KEY_FIELD;
 
 /* Values in optimize */
@@ -2858,7 +2868,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
                                    cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
                                   ((*value)->type() == Item::FIELD_ITEM) &&
                                   ((Item_field*)*value)->field->maybe_null());
-  (*key_fields)->outer_ref=      FALSE;
+  (*key_fields)->cond_guard= NULL;
   (*key_fields)++;
 }
 
@@ -2955,25 +2965,26 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
   }
 
   /* 
-    Subquery optimization: check if the encountered condition is one
-    added by condition push down into subquery.
+    Subquery optimization: Conditions that are pushed down into subqueries
+    are wrapped into Item_func_trig_cond. We process the wrapped condition
+    but need to set cond_guard for KEYUSE elements generated from it.
   */
   {
     if (cond->type() == Item::FUNC_ITEM &&
         ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC)
     {
-      cond= ((Item_func*)cond)->arguments()[0];
+      Item *cond_arg= ((Item_func*)cond)->arguments()[0];
       if (!join->group_list && !join->order &&
           join->unit->item && 
           join->unit->item->substype() == Item_subselect::IN_SUBS &&
           !join->unit->first_select()->next_select())
       {
         KEY_FIELD *save= *key_fields;
-        add_key_fields(join, key_fields, and_level, cond, usable_tables,
+        add_key_fields(join, key_fields, and_level, cond_arg, usable_tables,
                        sargables);
         // Indicate that this ref access candidate is for subquery lookup:
         for (; save != *key_fields; save++)
-          save->outer_ref= TRUE;
+          save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
       }
       return;
     }
@@ -3153,7 +3164,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
 	  keyuse.used_tables=key_field->val->used_tables();
 	  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
           keyuse.null_rejecting= key_field->null_rejecting;
-          keyuse.outer_ref= key_field->outer_ref;
+          keyuse.cond_guard= key_field->cond_guard;
 	  VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
 	}
       }
@@ -4992,7 +5003,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   if (!(j->ref.key_buff= (byte*) thd->calloc(ALIGN_SIZE(length)*2)) ||
       !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *
 						   (keyparts+1)))) ||
-      !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)))
+      !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
+      !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))
   {
     DBUG_RETURN(TRUE);
   }
@@ -5007,6 +5019,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   if (ftkey)
   {
     j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
+    /* Predicates pushed down into subquery can't be used FT access */
+    j->ref.cond_guards[0]= NULL;
     if (keyuse->used_tables)
       DBUG_RETURN(TRUE);                        // not supported yet. SerG
 
@@ -5023,6 +5037,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
 
       uint maybe_null= test(keyinfo->key_part[i].null_bit);
       j->ref.items[i]=keyuse->val;		// Save for cond removal
+      j->ref.cond_guards[i]= keyuse->cond_guard;
       if (keyuse->null_rejecting) 
         j->ref.null_rejecting |= 1 << i;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
@@ -7653,7 +7668,7 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list,
 
   SYNOPSIS
     remove_additional_cond()
-    conds - condition for processing
+      conds  Condition for processing
 
   RETURN VALUES
     new conditions
@@ -8766,8 +8781,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
 
   if (type != Item::FIELD_ITEM &&
       item->real_item()->type() == Item::FIELD_ITEM &&
-      (item->type() != Item::REF_ITEM ||
-       !((Item_ref *) item)->depended_from))
+      !((Item_ref *) item)->depended_from)
   {
     orig_item= item;
     item= item->real_item();
@@ -10912,7 +10926,9 @@ int rr_sequential(READ_RECORD *info);
 int init_read_record_seq(JOIN_TAB *tab)
 {
   tab->read_record.read_record= rr_sequential;
-  return tab->read_record.file->ha_rnd_init(1);
+  if (tab->read_record.file->ha_rnd_init(1))
+    return 1;
+  return (*tab->read_record.read_record)(&tab->read_record);
 }
 
 static int
@@ -12263,7 +12279,7 @@ static int
 create_sort_index(THD *thd, JOIN *join, ORDER *order,
 		  ha_rows filesort_limit, ha_rows select_limit)
 {
-  uint length;
+  uint length= 0;
   ha_rows examined_rows;
   TABLE *table;
   SQL_SELECT *select;
@@ -12284,8 +12300,10 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
        !(join->select_options & SELECT_BIG_RESULT)) &&
       test_if_skip_sort_order(tab,order,select_limit,0))
     DBUG_RETURN(0);
+  for (ORDER *ord= join->order; ord; ord= ord->next)
+    length++;
   if (!(join->sortorder= 
-        make_unireg_sortorder(order,&length,join->sortorder)))
+        make_unireg_sortorder(order, &length, join->sortorder)))
     goto err;				/* purecov: inspected */
 
   table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
@@ -12691,8 +12709,10 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length,
   for (ORDER *tmp = order; tmp; tmp=tmp->next)
     count++;
   if (!sortorder)
-    sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
-  pos=sort=sortorder;
+    sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD) *
+                                       (max(count, *length) + 1));
+  pos= sort= sortorder;
+
   if (!pos)
     return 0;
 
@@ -13215,49 +13235,83 @@ setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 	    bool *hidden_group_fields)
 {
   *hidden_group_fields=0;
+  ORDER *ord;
+
   if (!order)
     return 0;				/* Everything is ok */
 
-  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
-  {
-    Item *item;
-    List_iterator<Item> li(fields);
-    while ((item=li++))
-      item->marker=0;			/* Marker that field is not used */
-  }
   uint org_fields=all_fields.elements;
 
   thd->where="group statement";
-  for (; order; order=order->next)
+  for (ord= order; ord; ord= ord->next)
   {
-    if (find_order_in_list(thd, ref_pointer_array, tables, order, fields,
+    if (find_order_in_list(thd, ref_pointer_array, tables, ord, fields,
 			   all_fields, TRUE))
       return 1;
-    (*order->item)->marker=1;		/* Mark found */
-    if ((*order->item)->with_sum_func)
+    (*ord->item)->marker= UNDEF_POS;		/* Mark found */
+    if ((*ord->item)->with_sum_func)
     {
-      my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*order->item)->full_name());
+      my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*ord->item)->full_name());
       return 1;
     }
   }
   if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
   {
-    /* Don't allow one to use fields that is not used in GROUP BY */
+    /*
+      Don't allow one to use fields that is not used in GROUP BY
+      For each select a list of field references that aren't under an
+      aggregate function is created. Each field in this list keeps the
+      position of the select list expression which it belongs to.
+
+      First we check an expression from the select list against the GROUP BY
+      list. If it's found there then it's ok. It's also ok if this expression
+      is a constant or an aggregate function. Otherwise we scan the list
+      of non-aggregated fields and if we'll find at least one field reference
+      that belongs to this expression and doesn't occur in the GROUP BY list
+      we throw an error. If there are no fields in the created list for a
+      select list expression this means that all fields in it are used under
+      aggregate functions.
+    */
     Item *item;
+    Item_field *field;
+    int cur_pos_in_select_list= 0;
     List_iterator<Item> li(fields);
+    List_iterator<Item_field> naf_it(thd->lex->current_select->non_agg_fields);
 
-    while ((item=li++))
+    field= naf_it++;
+    while (field && (item=li++))
     {
-      if (item->type() != Item::SUM_FUNC_ITEM && !item->marker &&
-	  !item->const_item())
+      if (item->type() != Item::SUM_FUNC_ITEM && item->marker >= 0 &&
+          !item->const_item() &&
+          !(item->real_item()->type() == Item::FIELD_ITEM &&
+            item->used_tables() & OUTER_REF_TABLE_BIT))
       {
-        /*
-          TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed
-          ER_NON_GROUPING_FIELD_USED
-        */
-	my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), item->full_name());
-	return 1;
+        while (field)
+        {
+          /* Skip fields from previous expressions. */
+          if (field->marker < cur_pos_in_select_list)
+            goto next_field;
+          /* Found a field from the next expression. */
+          if (field->marker > cur_pos_in_select_list)
+            break;
+          /*
+            Check whether the field occur in the GROUP BY list.
+            Throw the error later if the field isn't found.
+          */
+          for (ord= order; ord; ord= ord->next)
+            if ((*ord->item)->eq((Item*)field, 0))
+              goto next_field;
+          /*
+            TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed
+            ER_NON_GROUPING_FIELD_USED
+          */
+          my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), field->full_name());
+          return 1;
+next_field:
+          field= naf_it++;
+        }
       }
+      cur_pos_in_select_list++;
     }
   }
   if (org_fields != all_fields.elements)
@@ -13383,10 +13437,12 @@ count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
   param->quick_group=1;
   while ((field=li++))
   {
-    Item::Type type=field->real_item()->type();
-    if (type == Item::FIELD_ITEM)
+    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))
       param->field_count++;
-    else if (type == Item::SUM_FUNC_ITEM)
+    else if (real_type == Item::SUM_FUNC_ITEM)
     {
       if (! field->const_item())
       {
@@ -14820,6 +14876,24 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
         
       if (tab->info)
 	item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
+      else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+      {
+        if (tab->packed_info & TAB_INFO_USING_INDEX)
+          extra.append(STRING_WITH_LEN("; Using index"));
+        if (tab->packed_info & TAB_INFO_USING_WHERE)
+          extra.append(STRING_WITH_LEN("; Using where"));
+        if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+          extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+        /* Skip initial "; "*/
+        const char *str= extra.ptr();
+        uint32 len= extra.length();
+        if (len)
+        {
+          str += 2;
+          len -= 2;
+        }
+	item_list.push_back(new Item_string(str, len, cs));
+      }
       else
       {
         if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || 
@@ -14878,6 +14952,15 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
 	}
 	if (distinct & test_all_bits(used_tables,thd->used_tables))
 	  extra.append(STRING_WITH_LEN("; Distinct"));
+
+        for (uint part= 0; part < tab->ref.key_parts; part++)
+        {
+          if (tab->ref.cond_guards[part])
+          {
+            extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+            break;
+          }
+        }
         
         /* Skip initial "; "*/
         const char *str= extra.ptr();
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ccdd66d5b95..a17d7fcb362 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -35,8 +35,17 @@ typedef struct keyuse_t {
     satisfied if val has NULL 'value'.
   */
   bool null_rejecting;
-  /* TRUE<=> This ref access is an outer subquery reference access */
-  bool outer_ref;
+  /*
+    !NULL - This KEYUSE was created from an equality that was wrapped into
+            an Item_func_trig_cond. This means the equality (and validity of 
+            this KEYUSE element) can be turned on and off. The on/off state 
+            is indicted by the pointed value:
+              *cond_guard == TRUE <=> equality condition is on
+              *cond_guard == FALSE <=> equality condition is off
+
+    NULL  - Otherwise (the source equality can't be turned off)
+  */
+  bool *cond_guard;
 } KEYUSE;
 
 class store_key;
@@ -51,6 +60,18 @@ typedef struct st_table_ref
   byte          *key_buff2;               // key_buff+key_length
   store_key     **key_copy;               //
   Item          **items;                  // val()'s for each keypart
+  /*  
+    Array of pointers to trigger variables. Some/all of the pointers may be
+    NULL.  The ref access can be used iff
+    
+      for each used key part i, (!cond_guards[i] || *cond_guards[i]) 
+
+    This array is used by subquery code. The subquery code may inject
+    triggered conditions, i.e. conditions that can be 'switched off'. A ref 
+    access created from such condition is not valid when at least one of the 
+    underlying conditions is switched off (see subquery code for more details)
+  */
+  bool          **cond_guards;
   /*
     (null_rejecting & (1<<i)) means the condition is '=' and no matching
     rows will be produced if items[i] IS NULL (see add_not_null_conds())
@@ -99,6 +120,13 @@ enum enum_nested_loop_state
   NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4
 };
 
+
+/* Values for JOIN_TAB::packed_info */
+#define TAB_INFO_HAVE_VALUE 1
+#define TAB_INFO_USING_INDEX 2
+#define TAB_INFO_USING_WHERE 4
+#define TAB_INFO_FULL_SCAN_ON_NULL 8
+
 typedef enum_nested_loop_state
 (*Next_select_func)(JOIN *, struct st_join_table *, bool);
 typedef int (*Read_record_func)(struct st_join_table *tab);
@@ -119,7 +147,15 @@ typedef struct st_join_table {
   st_join_table *last_inner;    /* last table table for embedding outer join */
   st_join_table *first_upper;  /* first inner table for embedding outer join */
   st_join_table *first_unmatched; /* used for optimization purposes only     */
+  
+  /* Special content for EXPLAIN 'Extra' column or NULL if none */
   const char	*info;
+  /* 
+    Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra'
+    column, or 0 if there is no info.
+  */
+  uint          packed_info;
+
   Read_record_func read_first_record;
   Next_select_func next_select;
   READ_RECORD	read_record;
@@ -386,7 +422,7 @@ public:
 			  Item_sum ***func);
   int rollup_send_data(uint idx);
   int rollup_write_data(uint idx, TABLE *table);
-  bool test_in_subselect(Item **where);
+  void remove_subq_pushed_predicates(Item **where);
   /*
     Release memory and, if possible, the open tables held by this execution
     plan (and nested plans). It's used to release some tables before
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index d9dadfbfd81..6f57ac1ec42 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3931,7 +3931,7 @@ copy_data_between_tables(TABLE *from,TABLE *to,
   Copy_field *copy,*copy_end;
   ulong found_count,delete_count;
   THD *thd= current_thd;
-  uint length;
+  uint length= 0;
   SORT_FIELD *sortorder;
   READ_RECORD info;
   TABLE_LIST   tables;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 55e52389a83..8b7dde2f818 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -732,6 +732,7 @@ bool st_select_lex::cleanup()
   {
     error= (bool) ((uint) error | (uint) lex_unit->cleanup());
   }
+  non_agg_fields.empty();
   DBUG_RETURN(error);
 }
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 4043fe17a46..b85c617b12d 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -302,7 +302,7 @@ int mysql_update(THD *thd,
 	Doing an ORDER BY;  Let filesort find and sort the rows we are going
 	to update
       */
-      uint         length;
+      uint         length= 0;
       SORT_FIELD  *sortorder;
       ha_rows examined_rows;