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

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


BitKeeper/etc/ignore:
  auto-union
mysql-test/r/func_in.result:
  Auto merged
mysql-test/r/range.result:
  Auto merged
mysql-test/r/sp-code.result:
  Auto merged
mysql-test/r/trigger.result:
  Auto merged
mysql-test/r/udf.result:
  Auto merged
mysql-test/t/func_in.test:
  Auto merged
mysql-test/t/range.test:
  Auto merged
mysql-test/t/trigger.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged
mysql-test/r/select.result:
  manual merge
mysql-test/r/view.result:
  manual merge
mysql-test/t/select.test:
  manual merge
mysql-test/t/view.test:
  manual merge

58 files changed, 1287 insertions, 156 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/func_in.result b/mysql-test/r/func_in.result
index 7336665fa40..cf44c56f061 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -380,7 +380,7 @@ Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'b'
 explain select f1 from t1 where f1 in ('a',1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	t1f1_idx	2	NULL	3	Using where; Using index
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
 select f1 from t1 where f1 in ('a','b');
 f1
 a
@@ -409,7 +409,7 @@ Warning	1292	Truncated incorrect DOUBLE value: 'a'
 Warning	1292	Truncated incorrect DOUBLE value: 'a'
 explain select f2 from t2 where f2 in ('a',2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
 select f2 from t2 where f2 in ('a','b');
 f2
 0
@@ -431,6 +431,6 @@ Warning	1292	Truncated incorrect DOUBLE value: 'b'
 Warning	1292	Truncated incorrect DOUBLE value: 'b'
 explain select f2 from t2 where f2 in (1,'b');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
 drop table t1, t2;
 End of 5.1 tests
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index 9a2c55b802d..565c283ee83 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -177,11 +177,46 @@ drop table t1;
 select abs(-2) * -2;
 abs(-2) * -2
 -4
-create table t1 (i int);
-insert into t1 values (1);
-select rand(i) from t1;
-ERROR HY000: Incorrect arguments to RAND
-drop table t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(1),(1),(2);
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+FROM t1;
+CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
+656	405
+122	405
+645	405
+858	656
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+FROM t1 WHERE a = 1;
+CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
+656	405
+122	405
+645	405
+INSERT INTO t1 VALUES (3);
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+FROM t1;
+CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
+656	405
+122	405
+645	405
+858	656
+354	906
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+FROM t1 WHERE a = 1;
+CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
+656	405
+122	405
+645	405
+PREPARE stmt FROM 
+"SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED)
+    FROM t1 WHERE a = 1";
+set @var=2;
+EXECUTE stmt USING @var;
+CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(?) * 1000 AS UNSIGNED)
+656	656
+122	122
+645	645
+DROP TABLE t1;
 create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;
 insert into t1 values ('http://www.foo.com/', now());
 select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0));
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 0496c3eb91f..e6c323276ea 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1304,6 +1304,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	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	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1003	select encode(`test`.`t1`.`f1`,_latin1'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	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1003	select decode(`test`.`t1`.`f1`,_latin1'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/gis.result b/mysql-test/r/gis.result
index b7f0e4b6b80..8cbc344f062 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -718,3 +718,14 @@ desc t1;
 Field	Type	Null	Key	Default	Extra
 GeomFromText('point(1 1)')	geometry	NO			
 drop table t1;
+create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
+create view v1 as select * from t1;
+desc v1;
+Field	Type	Null	Key	Default	Extra
+f1	tinyint(1)	YES		NULL	
+f2	char(1)	YES		NULL	
+f3	varchar(1)	YES		NULL	
+f4	geometry	YES		NULL	
+f5	datetime	YES		NULL	
+drop view v1;
+drop table t1;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 663ef6cced4..d583eb02aeb 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -933,6 +933,108 @@ 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 = '';
 CREATE TABLE t1 (a INT, b INT, KEY(a));
 INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
 EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result
index b9c0cae5efb..2ea2a0bec0b 100644
--- a/mysql-test/r/information_schema_inno.result
+++ b/mysql-test/r/information_schema_inno.result
@@ -31,11 +31,11 @@ CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2),
 CONSTRAINT A1
 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2)
 ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB;
-CREATE TABLE t3(b1 INT, b2 INT, INDEX (b1, b2),
+CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2),
 CONSTRAINT A2
 FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2)
 ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB;
-CREATE TABLE t4(b1 INT, b2 INT, INDEX (b1, b2),
+CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2),
 CONSTRAINT A3
 FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2)
 ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB;
@@ -45,16 +45,16 @@ FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2)
 ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB;
 select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE,
 b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,
-MATCH_OPTION, UPDATE_RULE, DELETE_RULE
+MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME
 from information_schema.TABLE_CONSTRAINTS a,
 information_schema.REFERENTIAL_CONSTRAINTS b
 where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
 a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;
-CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE
-test	t2	FOREIGN KEY	A1	test	t1	NONE	CASCADE	NO ACTION
-test	t3	FOREIGN KEY	A2	test	t2	NONE	SET NULL	RESTRICT
-test	t4	FOREIGN KEY	A3	test	t3	NONE	NO ACTION	SET NULL
-test	t5	FOREIGN KEY	A4	test	t4	NONE	RESTRICT	CASCADE
+CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	REFERENCED_TABLE_NAME
+test	t2	FOREIGN KEY	A1	test	PRIMARY	NONE	CASCADE	NO ACTION	t1
+test	t3	FOREIGN KEY	A2	test	b1	NONE	SET NULL	RESTRICT	t2
+test	t4	FOREIGN KEY	A3	test	t3_indx	NONE	NO ACTION	SET NULL	t3
+test	t5	FOREIGN KEY	A4	test	t4_ukey	NONE	RESTRICT	CASCADE	t4
 drop tables t5, t4, t3, t2, t1;
 create database `db-1`;
 use `db-1`;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index e7fa950a131..9032ea00e11 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -3447,8 +3447,6 @@ SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
 a
 1
 drop table t2, t1;
-create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
-ERROR HY000: The used table type doesn't support SPATIAL indexes
 CREATE TABLE t1 ( a int ) ENGINE=innodb;
 BEGIN;
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result
index 09f0aebaefa..e714dbeffc0 100644
--- a/mysql-test/r/innodb_gis.result
+++ b/mysql-test/r/innodb_gis.result
@@ -460,3 +460,5 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 insert into t1 (fl) values (pointfromtext('point(1,1)'));
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 drop table t1;
+create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
+ERROR HY000: The used table type doesn't support SPATIAL indexes
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index ef2ed6f0acf..b039ee49235 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -347,6 +347,27 @@ 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;
 create table t1 (id int primary key auto_increment, data int, unique(data));
 insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
 insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 8d09686b7d3..a056e1244e6 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -926,3 +926,12 @@ NULL
 2
 3
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
+INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
+EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY,b	b	5	const	1	
+1	SIMPLE	t2	ref	a	a	5	const	2	Using where; Using index
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 55ba8380665..f7eda649dd2 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -1138,4 +1138,85 @@ PARTITION p_100 VALUES LESS THAN (100),
 PARTITION p_X VALUES LESS THAN MAXVALUE 
 );
 drop table t1;
+CREATE TABLE t2 (
+taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+id int(11) NOT NULL DEFAULT '0',
+PRIMARY KEY (id,taken),
+KEY taken (taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES 
+('2006-09-27 21:50:01',16421),
+('2006-10-02 21:50:01',16421),
+('2006-09-27 21:50:01',19092),
+('2006-09-28 21:50:01',19092),
+('2006-09-29 21:50:01',19092),
+('2006-09-30 21:50:01',19092),
+('2006-10-01 21:50:01',19092),
+('2006-10-02 21:50:01',19092),
+('2006-09-27 21:50:01',22589),
+('2006-09-29 21:50:01',22589);
+CREATE TABLE t1 (
+id int(8) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES 
+(16421),
+(19092),
+(22589);
+CREATE TABLE t4 (
+taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+id int(11) NOT NULL DEFAULT '0',
+PRIMARY KEY (id,taken),
+KEY taken (taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 
+PARTITION BY RANGE (to_days(taken)) 
+(
+PARTITION p01 VALUES LESS THAN (732920) , 
+PARTITION p02 VALUES LESS THAN (732950) , 
+PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
+INSERT INTO t4 select * from t2;
+set @f_date='2006-09-28';
+set @t_date='2006-10-02';
+SELECT t1.id AS MyISAM_part
+FROM t1
+WHERE t1.id IN (
+SELECT distinct id
+FROM t4
+WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY))
+ORDER BY t1.id
+;
+MyISAM_part
+16421
+19092
+22589
+drop table t1, t2, t4;
+CREATE TABLE t1 (
+taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+id int(11) NOT NULL DEFAULT '0',
+status varchar(20) NOT NULL DEFAULT '',
+PRIMARY KEY (id,taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (to_days(taken))
+(
+PARTITION p15 VALUES LESS THAN (732950) ,
+PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
+INSERT INTO t1 VALUES
+('2006-09-27 21:50:01',22589,'Open'),
+('2006-09-29 21:50:01',22589,'Verified');
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note	1051	Unknown table 't2'
+CREATE TABLE t2 (
+id int(8) NOT NULL,
+severity tinyint(4) NOT NULL DEFAULT '0',
+priority tinyint(4) NOT NULL DEFAULT '0',
+status varchar(20) DEFAULT NULL,
+alien tinyint(4) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES
+(22589,1,1,'Need Feedback',0);
+SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
+id
+22589
+drop table t1, t2;
 End of 5.1 tests
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 7a280c2201c..5cd7e3ebfbd 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	6	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 cf84f197c8e..6704842320f 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 377870696e1..ae828cf06fc 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1464,7 +1464,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	100.00	Using index
 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))))) 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	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
@@ -1476,13 +1476,13 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	100.00	Using index
 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))))) 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	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	100.00	Using index; Using where
 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')))))) 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	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
 2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	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	filtered	Extra
 1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
 2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	1	100.00	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');
@@ -3605,6 +3605,39 @@ 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;
 CREATE TABLE t1 (s1 char(1));
 INSERT INTO t1 VALUES ('a');
 SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index e302f5def1b..dcd582fb55e 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 dbbe432e614..593ac63ca80 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -278,3 +278,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 b338ba12685..8e37774f70d 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2060,17 +2060,6 @@ CALL p1();
 DROP PROCEDURE p1;
 DROP VIEW v1;
 DROP TABLE t1;
-create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
-create view v1 as select * from t1;
-desc v1;
-Field	Type	Null	Key	Default	Extra
-f1	tinyint(1)	YES		NULL	
-f2	char(1)	YES		NULL	
-f3	varchar(1)	YES		NULL	
-f4	geometry	YES		NULL	
-f5	datetime	YES		NULL	
-drop view v1;
-drop table t1;
 create table t1(f1 datetime);
 insert into t1 values('2005.01.01 12:0:0');
 create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1;
@@ -2970,16 +2959,6 @@ UPDATE t1 SET i= f1();
 DROP FUNCTION f1;
 DROP VIEW v1;
 DROP TABLE t1;
-CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
-CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION;
-INSERT INTO v1 (val) VALUES (2);
-INSERT INTO v1 (val) VALUES (4);
-INSERT INTO v1 (val) VALUES (6);
-ERROR HY000: CHECK OPTION failed 'test.v1'
-UPDATE v1 SET val=6 WHERE id=2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
-DROP VIEW v1;
-DROP TABLE t1;
 DROP VIEW IF EXISTS v1, v2;
 DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
@@ -3014,6 +2993,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;
@@ -3024,6 +3014,50 @@ View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd`
 DROP VIEW v1;
 End of 5.0 tests.
+CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
+CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION;
+INSERT INTO v1 (val) VALUES (2);
+INSERT INTO v1 (val) VALUES (4);
+INSERT INTO v1 (val) VALUES (6);
+ERROR HY000: CHECK OPTION failed 'test.v1'
+UPDATE v1 SET val=6 WHERE id=2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+DROP VIEW v1;
+DROP TABLE t1;
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
+CREATE VIEW v1 AS SELECT j FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1;
+INSERT INTO t1 (j) VALUES (1);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v1 (j) VALUES (2);
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v2 (j) VALUES (3);
+# LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+INSERT INTO v1 (j) SELECT j FROM t1;
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+SELECT * FROM t1;
+i	j
+1	1
+2	2
+3	3
+4	1
+5	2
+6	3
+DROP VIEW v1, v2;
+DROP TABLE t1;
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;
 USE `d-1`;
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/func_in.test b/mysql-test/t/func_in.test
index 75d85065419..b5afdc122a3 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -261,6 +261,44 @@ select some_id from t1 where some_id not in('-1', '0');
 
 drop table t1;
 
+# TODO:Disabled until re-resolution of bug #20420 for 5.1.
+# Results must be the same as in 5.0
+##
+## 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),(100,100),(101,201),(102,102);
+#
+#CREATE TABLE t3 (a int PRIMARY KEY);
+#INSERT INTO t3 VALUES (1),(2),(3),(4);
+#
+#CREATE TABLE t4 (a int PRIMARY KEY,b int);
+#INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),(1003,1003),(1004,1004);
+#
+#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_math.test b/mysql-test/t/func_math.test
index 09aa5751301..639ced6a1c0 100644
--- a/mysql-test/t/func_math.test
+++ b/mysql-test/t/func_math.test
@@ -115,11 +115,25 @@ select abs(-2) * -2;
 #
 # Bug #6172 RAND(a) should only accept constant values as arguments
 #
-create table t1 (i int);
-insert into t1 values (1);
---error 1210
-select rand(i) from t1;
-drop table t1;
+CREATE TABLE t1 (a INT);
+
+INSERT INTO t1 VALUES (1),(1),(1),(2);
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+  FROM t1;
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+  FROM t1 WHERE a = 1;
+INSERT INTO t1 VALUES (3);
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+  FROM t1;
+SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
+  FROM t1 WHERE a = 1;
+PREPARE stmt FROM 
+  "SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED)
+    FROM t1 WHERE a = 1";
+set @var=2;
+EXECUTE stmt USING @var;
+
+DROP TABLE t1;  
 
 #
 # Bug #14009: use of abs() on null value causes problems with filesort
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index ab072f0e692..535af6907ad 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -774,6 +774,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/gis.test b/mysql-test/t/gis.test
index 196ff5b8ff0..d1ea8dbe18f 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -428,3 +428,13 @@ drop table t1;
 create table t1 select GeomFromText('point(1 1)');
 desc t1;
 drop table t1;
+
+#
+# Bug #11335 View redefines column types
+#
+create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
+create view v1 as select * from t1;
+desc v1;
+drop view v1;
+drop table t1;
+
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 7f887335753..76e4af8f610 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -703,6 +703,57 @@ 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 = '';
+
+#
 # Bug #21174: Index degrades sort performance and 
 #             optimizer does not honor IGNORE INDEX
 #
diff --git a/mysql-test/t/information_schema_inno.test b/mysql-test/t/information_schema_inno.test
index 014bdacfeea..e73f4ba2792 100644
--- a/mysql-test/t/information_schema_inno.test
+++ b/mysql-test/t/information_schema_inno.test
@@ -32,11 +32,11 @@ CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2),
                 CONSTRAINT A1
                 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2)
                 ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB;
-CREATE TABLE t3(b1 INT, b2 INT, INDEX (b1, b2),
+CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2),
 		CONSTRAINT A2
 		FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2)
 		ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB;
-CREATE TABLE t4(b1 INT, b2 INT, INDEX (b1, b2),
+CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2),
                 CONSTRAINT A3
                 FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2)
                 ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB;
@@ -45,10 +45,9 @@ CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2),
                 FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2)
                 ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB;
 		
-		
 select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE,
        b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,
-       MATCH_OPTION, UPDATE_RULE, DELETE_RULE
+       MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME
 from information_schema.TABLE_CONSTRAINTS a,
      information_schema.REFERENTIAL_CONSTRAINTS b
 where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index e1e74ac8a11..bbecd787324 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -2489,12 +2489,6 @@ SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
 drop table t2, t1;
 
 #
-# Bug #15680 (SPATIAL key in innodb)
-#
---error ER_TABLE_CANT_HANDLE_SPKEYS
-create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
-
-#
 # Test optimize on table with open transaction
 #
 
diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test
index 142b526af92..9675b6b69dc 100644
--- a/mysql-test/t/innodb_gis.test
+++ b/mysql-test/t/innodb_gis.test
@@ -1,3 +1,9 @@
 --source include/have_innodb.inc
 SET storage_engine=innodb;
 --source include/gis_generic.inc
+
+#
+# Bug #15680 (SPATIAL key in innodb)
+#
+--error ER_TABLE_CANT_HANDLE_SPKEYS
+create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 398b0bc89d8..0a6b081e7c3 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -225,6 +225,23 @@ 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;
+
 # Test of INSERT IGNORE and re-using auto_increment values
 create table t1 (id int primary key auto_increment, data int, unique(data));
 insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 012b38ff8b7..15fb6771101 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -640,3 +640,16 @@ SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
 ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
 
 DROP TABLE t1,t2,t3,t4;
+
+#
+# BUG#16590: Optimized does not do right "const" table pre-read
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
+INSERT INTO t1 VALUES (1,1),(2,2);
+
+CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
+INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
+
+EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 02c7ca0b05c..7d7ef95626a 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -1370,4 +1370,97 @@ REORGANIZE PARTITION p_X INTO (
 
 drop table t1;
 
+#
+# Bug #24186 (nested query across partitions returns fewer records)
+#
+
+CREATE TABLE t2 (
+  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  id int(11) NOT NULL DEFAULT '0',
+  PRIMARY KEY (id,taken),
+  KEY taken (taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+INSERT INTO t2 VALUES 
+('2006-09-27 21:50:01',16421),
+('2006-10-02 21:50:01',16421),
+('2006-09-27 21:50:01',19092),
+('2006-09-28 21:50:01',19092),
+('2006-09-29 21:50:01',19092),
+('2006-09-30 21:50:01',19092),
+('2006-10-01 21:50:01',19092),
+('2006-10-02 21:50:01',19092),
+('2006-09-27 21:50:01',22589),
+('2006-09-29 21:50:01',22589);
+
+CREATE TABLE t1 (
+  id int(8) NOT NULL,
+  PRIMARY KEY (id)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 VALUES 
+(16421),
+(19092),
+(22589);
+
+CREATE TABLE t4 (
+  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  id int(11) NOT NULL DEFAULT '0',
+  PRIMARY KEY (id,taken),
+  KEY taken (taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 
+PARTITION BY RANGE (to_days(taken)) 
+(
+PARTITION p01 VALUES LESS THAN (732920) , 
+PARTITION p02 VALUES LESS THAN (732950) , 
+PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
+
+INSERT INTO t4 select * from t2;
+
+set @f_date='2006-09-28';
+set @t_date='2006-10-02';
+
+SELECT t1.id AS MyISAM_part
+FROM t1
+WHERE t1.id IN (
+    SELECT distinct id
+    FROM t4
+    WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY))
+ORDER BY t1.id
+;
+
+drop table t1, t2, t4;
+
+CREATE TABLE t1 (
+  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  id int(11) NOT NULL DEFAULT '0',
+  status varchar(20) NOT NULL DEFAULT '',
+  PRIMARY KEY (id,taken)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (to_days(taken))
+(
+PARTITION p15 VALUES LESS THAN (732950) ,
+PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
+
+
+INSERT INTO t1 VALUES
+('2006-09-27 21:50:01',22589,'Open'),
+('2006-09-29 21:50:01',22589,'Verified');
+
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t2 (
+  id int(8) NOT NULL,
+  severity tinyint(4) NOT NULL DEFAULT '0',
+  priority tinyint(4) NOT NULL DEFAULT '0',
+  status varchar(20) DEFAULT NULL,
+  alien tinyint(4) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+INSERT INTO t2 VALUES
+(22589,1,1,'Need Feedback',0);
+
+SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
+
+drop table t1, t2;
+
 --echo End of 5.1 tests
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index af90b6cd4f0..b1cec049ab2 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 0e7361218aa..fbb2b4d8d3d 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3104,6 +3104,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 eefa2528a17..98471622bc5 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2510,6 +2510,40 @@ SELECT SQL_NO_CACHE COUNT(*)
 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;
+
+#
 # Bug#20835 (literal string with =any values)
 #
 CREATE TABLE t1 (s1 char(1));
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 56d310eec37..b40a8538e04 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 010a532f48b..7d870a86bc8 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -294,3 +294,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 ea429e2a0f0..2e67fc3d2d2 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1879,15 +1879,6 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 #
-# Bug #11335 View redefines column types
-#
-create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
-create view v1 as select * from t1;
-desc v1;
-drop view v1;
-drop table t1;
-
-#
 # Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned
 #             subtime() in view
 create table t1(f1 datetime);
@@ -2907,6 +2898,57 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 #
+# BUG#22584: last_insert_id not updated after inserting a record
+# through a updatable view
+#
+# We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is
+# not accessible through a view.  However, we do not reset the value
+# of LAST_INSERT_ID, but keep it unchanged.
+#
+--disable_warnings
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
+CREATE VIEW v1 AS SELECT j FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1;
+
+INSERT INTO t1 (j) VALUES (1);
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v1 (j) VALUES (2);
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v2 (j) VALUES (3);
+--echo # LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v1 (j) SELECT j FROM t1;
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+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;
+
+
+--echo End of 5.0 tests.
+
 # Bug #16813 (WITH CHECK OPTION doesn't work with UPDATE)
 #
 CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index feb08f474b7..9d4cd69be12 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -4015,6 +4015,7 @@ int ha_partition::handle_ordered_index_scan(byte *buf, bool reverse_order)
     m_queue.elements= j;
     queue_fix(&m_queue);
     return_top_record(buf);
+    table->status= 0;
     DBUG_PRINT("info", ("Record returned from partition %d", m_top_entry));
     DBUG_RETURN(0);
   }
@@ -4083,6 +4084,7 @@ int ha_partition::handle_ordered_next(byte *buf, bool is_next_same)
          DBUG_PRINT("info", ("Record returned from partition %u (2)",
                      m_top_entry));
          return_top_record(buf);
+         table->status= 0;
          error= 0;
       }
     }
@@ -4126,6 +4128,7 @@ int ha_partition::handle_ordered_prev(byte *buf)
 	DBUG_PRINT("info", ("Record returned from partition %d (2)",
 			    m_top_entry));
         error= 0;
+        table->status= 0;
       }
     }
     DBUG_RETURN(error);
diff --git a/sql/item.cc b/sql/item.cc
index 7ba5ab3e09d..0f28572163e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3503,6 +3503,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;
@@ -3705,10 +3715,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
@@ -3800,6 +3811,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
         goto error;
       if (!ret)
         return FALSE;
+      outer_fixed= 1;
     }
 
     set_field(from_field);
@@ -3859,6 +3871,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 8c97010389e..be67d188297 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -482,7 +482,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;
@@ -2372,6 +2373,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 3a615d4c10a..3a887e6dda6 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -176,6 +176,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
 */
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index a51cc0d4b30..899d29d4786 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -271,6 +271,7 @@ public:
   const char *func_name() const { return "not"; }
   Item *neg_transformer(THD *thd);
   bool check_partition_func_processor(byte *int_arg) {return FALSE;}
+  void print(String *str);
 };
 
 class Item_maxmin_subselect;
@@ -1033,6 +1034,11 @@ public:
 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;
   bool have_null;
   Item_result left_result_type;
@@ -1064,7 +1070,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 1e89a579420..fe3a2c393fb 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2042,6 +2042,18 @@ my_decimal *Item_func_round::decimal_op(my_decimal *decimal_value)
 }
 
 
+void Item_func_rand::seed_random(Item *arg)
+{
+  /*
+    TODO: do not do reinit 'rand' for every execute of PS/SP if
+    args[0] is a constant.
+  */
+  uint32 tmp= (uint32) arg->val_int();
+  randominit(rand, (uint32) (tmp*0x10001L+55555555L),
+             (uint32) (tmp*0x10000001L));
+}
+
+
 bool Item_func_rand::fix_fields(THD *thd,Item **ref)
 {
   if (Item_real_func::fix_fields(thd, ref))
@@ -2049,11 +2061,6 @@ bool Item_func_rand::fix_fields(THD *thd,Item **ref)
   used_tables_cache|= RAND_TABLE_BIT;
   if (arg_count)
   {					// Only use argument once in query
-    if (!args[0]->const_during_execution())
-    {
-      my_error(ER_WRONG_ARGUMENTS, MYF(0), "RAND");
-      return TRUE;
-    }
     /*
       Allocate rand structure once: we must use thd->stmt_arena
       to create rand in proper mem_root if it's a prepared statement or
@@ -2065,20 +2072,9 @@ bool Item_func_rand::fix_fields(THD *thd,Item **ref)
     if (!rand && !(rand= (struct rand_struct*)
                    thd->stmt_arena->alloc(sizeof(*rand))))
       return TRUE;
-    /*
-      PARAM_ITEM is returned if we're in statement prepare and consequently
-      no placeholder value is set yet.
-    */
-    if (args[0]->type() != PARAM_ITEM)
-    {
-      /*
-        TODO: do not do reinit 'rand' for every execute of PS/SP if
-        args[0] is a constant.
-      */
-      uint32 tmp= (uint32) args[0]->val_int();
-      randominit(rand, (uint32) (tmp*0x10001L+55555555L),
-                 (uint32) (tmp*0x10000001L));
-    }
+
+    if (args[0]->const_item())
+      seed_random (args[0]);
   }
   else
   {
@@ -2108,6 +2104,8 @@ void Item_func_rand::update_used_tables()
 double Item_func_rand::val_real()
 {
   DBUG_ASSERT(fixed == 1);
+  if (arg_count && !args[0]->const_item())
+    seed_random (args[0]);
   return my_rnd(rand);
 }
 
@@ -2746,25 +2744,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_func.h b/sql/item_func.h
index 7810c0ce9a9..3306b059097 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -679,6 +679,8 @@ public:
   bool const_item() const { return 0; }
   void update_used_tables();
   bool fix_fields(THD *thd, Item **ref);
+private:
+  void seed_random (Item * val);  
 };
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index caeeb615e0a..56a7403b372 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -420,7 +420,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
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 930615133da..c03910a7829 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5321,6 +5321,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()) ||
@@ -5337,7 +5338,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->mark_used_columns= save_mark_used_columns;
   DBUG_PRINT("info", ("thd->mark_used_columns: %d", thd->mark_used_columns));
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index df313d8040c..e3405d9c9d9 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -153,7 +153,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;
@@ -173,7 +173,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 989b78f3517..4ad8e5ee128 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -81,6 +81,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.
@@ -105,7 +164,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;
 
@@ -183,21 +242,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->dup_field)
@@ -255,7 +302,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;
   my_bool timestamp_mark;
@@ -276,6 +323,10 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
   if (setup_fields(thd, 0, update_fields, MARK_COLUMNS_WRITE, 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. */
@@ -892,6 +943,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,
@@ -940,12 +992,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, MARK_COLUMNS_READ, 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
@@ -2404,6 +2456,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");
 
@@ -2416,7 +2469,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, MARK_COLUMNS_READ, 0, 0);
 
   if (info.handle_duplicates == DUP_UPDATE)
@@ -2434,7 +2487,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 97b5df241f7..db2a6182caa 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1208,6 +1208,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();
 }
 
 /*
@@ -1397,9 +1399,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 8e9eb578911..247c35ca73a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -615,6 +615,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;
 
   void init_query();
   void init_select();
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 249d69d174a..07f852282b7 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1677,7 +1677,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 919024ba457..65bfd8b5acb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7722,6 +7722,22 @@ static void update_const_equal_items(COND *cond, JOIN_TAB *tab)
         key_map possible_keys= field->key_start;
         possible_keys.intersect(field->table->keys_in_use_for_query);
         stat[0].const_keys.merge(possible_keys);
+
+        /*
+          For each field in the multiple equality (for which we know that it 
+          is a constant) we have to find its corresponding key part, and set 
+          that key part in const_key_parts.
+        */  
+        if (!possible_keys.is_clear_all())
+        {
+          TABLE *tab= field->table;
+          KEYUSE *use;
+          for (use= stat->keyuse; use && use->table == tab; use++)
+            if (possible_keys.is_set(use->key) && 
+                tab->key_info[use->key].key_part[use->keypart].field ==
+                field)
+              tab->const_key_parts[use->key]|= use->keypart_map;
+        }
       }
     }
   }
@@ -8938,8 +8954,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();
@@ -12484,7 +12499,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;
@@ -12505,8 +12520,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),
@@ -12914,8 +12931,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;
 
@@ -13443,49 +13462,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)
@@ -13611,10 +13664,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())
       {
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index c4bb6a8fc92..a895bf14ce0 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -4497,8 +4497,10 @@ get_referential_constraints_record(THD *thd, struct st_table_list *tables,
                              f_key_info->forein_id->length, cs);
       table->field[4]->store(f_key_info->referenced_db->str, 
                              f_key_info->referenced_db->length, cs);
-      table->field[5]->store(f_key_info->referenced_table->str, 
+      table->field[10]->store(f_key_info->referenced_table->str, 
                              f_key_info->referenced_table->length, cs);
+      table->field[5]->store(f_key_info->referenced_key_name->str, 
+                             f_key_info->referenced_key_name->length, cs);
       table->field[6]->store(STRING_WITH_LEN("NONE"), cs);
       table->field[7]->store(f_key_info->update_method->str, 
                              f_key_info->update_method->length, cs);
@@ -5668,6 +5670,7 @@ ST_FIELD_INFO referential_constraints_fields_info[]=
   {"UPDATE_RULE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
   {"DELETE_RULE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
   {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
+  {"REFERENCED_TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
 };
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 35a5547a730..b6ecfbd0888 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6691,7 +6691,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 e9e244676d1..985982d48de 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -734,6 +734,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 9952a4f534b..2e956a3ea74 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -320,7 +320,7 @@ int mysql_update(THD *thd,
 	to update
         NOTE: filesort will call table->prepare_for_position()
       */
-      uint         length;
+      uint         length= 0;
       SORT_FIELD  *sortorder;
       ha_rows examined_rows;
 
diff --git a/sql/table.h b/sql/table.h
index 80add0e0b91..82083d79570 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -460,6 +460,7 @@ typedef struct st_foreign_key_info
   LEX_STRING *referenced_table;
   LEX_STRING *update_method;
   LEX_STRING *delete_method;
+  LEX_STRING *referenced_key_name;
   List<LEX_STRING> foreign_fields;
   List<LEX_STRING> referenced_fields;
 } FOREIGN_KEY_INFO;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index ee74fce322e..b5b354d4b39 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -6003,8 +6003,14 @@ ha_innobase::get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list)
           }
           f_key_info.update_method= make_lex_string(thd, f_key_info.update_method,
                                                     tmp_buff, length, 1);
-
-
+          if (foreign->referenced_index &&
+              foreign->referenced_index->name)
+          {
+            f_key_info.referenced_key_name= 
+              make_lex_string(thd, f_key_info.referenced_key_name,
+                              foreign->referenced_index->name,
+                              strlen(foreign->referenced_index->name), 1);
+          }
 
 	  FOREIGN_KEY_INFO *pf_key_info= ((FOREIGN_KEY_INFO *)
 		  thd->memdup((gptr) &f_key_info,