Merge bk-internal.mysql.com:/data0/bk/mysql-5.0

into  bk-internal.mysql.com:/data0/bk/mysql-5.0-opt

34 files changed, 688 insertions, 86 deletions

diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index e2aa3b4a3cc..0b2b6769505 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -193,6 +193,16 @@ a	b	c	d	e	f	g	h	i	x
 two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	1
 	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	2
 two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	3
-	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	4
+		00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	4
 drop table bug20691;
+create table t1 (id int not null);
+insert into t1 values(default);
+Warnings:
+Warning	1364	Field 'id' doesn't have a default value
+create view v1 (c) as select id from t1;
+insert into t1 values(default);
+Warnings:
+Warning	1364	Field 'id' doesn't have a default value
+drop view v1;
+drop table t1;
 End of 5.0 tests.
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 870e160e563..df39a4d8ca2 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -717,3 +717,7 @@ desc t1;
 Field	Type	Null	Key	Default	Extra
 GeomFromText('point(1 1)')	geometry	NO			
 drop table t1;
+create table t1 (g geometry not null);
+insert into t1 values(default);
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+drop table t1;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index b93a4c28849..436bb70d0e7 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1262,8 +1262,7 @@ from information_schema.tables
 order by object_schema;
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
-2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
+1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 explain select * from (select table_name from information_schema.tables) as a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
@@ -1279,3 +1278,40 @@ table_name
 t1
 t2
 drop table t1,t2;
+select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
+(select cast(table_name as char)  from information_schema.tables
+order by table_name limit 1) limit 1;
+f1
+1
+select t.table_name, group_concat(t.table_schema, '.', t.table_name),
+count(*) as num1
+from information_schema.tables t
+inner join information_schema.columns c1
+on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
+where t.table_schema = 'information_schema' and
+c1.ordinal_position =
+(select isnull(c2.column_type) -
+isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
+count(*) as num
+from information_schema.columns c2 where
+c2.table_schema='information_schema' and
+(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
+group by c2.column_type order by num limit 1)
+group by t.table_name order by num1, t.table_name;
+table_name	group_concat(t.table_schema, '.', t.table_name)	num1
+CHARACTER_SETS	information_schema.CHARACTER_SETS	1
+COLLATIONS	information_schema.COLLATIONS	1
+COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
+COLUMNS	information_schema.COLUMNS	1
+COLUMN_PRIVILEGES	information_schema.COLUMN_PRIVILEGES	1
+KEY_COLUMN_USAGE	information_schema.KEY_COLUMN_USAGE	1
+ROUTINES	information_schema.ROUTINES	1
+SCHEMATA	information_schema.SCHEMATA	1
+SCHEMA_PRIVILEGES	information_schema.SCHEMA_PRIVILEGES	1
+STATISTICS	information_schema.STATISTICS	1
+TABLES	information_schema.TABLES	1
+TABLE_CONSTRAINTS	information_schema.TABLE_CONSTRAINTS	1
+TABLE_PRIVILEGES	information_schema.TABLE_PRIVILEGES	1
+TRIGGERS	information_schema.TRIGGERS	1
+USER_PRIVILEGES	information_schema.USER_PRIVILEGES	1
+VIEWS	information_schema.VIEWS	1
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index f150af4b6c2..298fc58ffcb 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -383,6 +383,33 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (
+id int NOT NULL,
+name varchar(20) NOT NULL,
+dept varchar(20) NOT NULL,
+age tinyint(3) unsigned NOT NULL,
+PRIMARY KEY (id),
+INDEX (name,dept)
+) ENGINE=InnoDB;
+INSERT INTO t1(id, dept, age, name) VALUES
+(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+rs5	cs10
+rs5	cs9
+DELETE FROM t1;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+DROP TABLE t1;
 show variables like 'innodb_rollback_on_timeout';
 Variable_name	Value
 innodb_rollback_on_timeout	OFF
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 1453d25ac75..2e0acf303c2 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -705,3 +705,29 @@ use bug21774_1;
 INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 DROP DATABASE bug21774_1;
 DROP DATABASE bug21774_2;
+USE test;
+create table t1(f1 int primary key, f2 int);
+insert into t1 values (1,1);
+affected rows: 1
+insert into t1 values (1,1) on duplicate key update f2=1;
+affected rows: 0
+insert into t1 values (1,1) on duplicate key update f2=2;
+affected rows: 2
+select * from t1;
+f1	f2
+1	2
+drop table t1;
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+@@identity
+1
+insert ignore t1(f2) values(1);
+select @@identity;
+@@identity
+0
+insert ignore t1(f2) select 1;
+select @@identity;
+@@identity
+0
+drop table t1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 89bb26c4b3f..df66336bd81 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1194,3 +1194,23 @@ a	b
 3	3
 4	NULL
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f2 varchar(16) collate latin1_swedish_ci
+);
+CREATE TABLE t2 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f3 varchar(16) collate latin1_swedish_ci
+);
+INSERT INTO t1 VALUES ('bla','blah');
+INSERT INTO t2 VALUES ('bla','sheep');
+SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 702ac39cb95..3c4df47ca7b 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3728,6 +3728,34 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+a	ts	a	dt1	dt2
+30	2006-01-03 23:00:00	30	2006-01-01 00:00:00	2999-12-31 00:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+DROP TABLE t1,t2;
 create table t1 (a bigint unsigned);
 insert into t1 values
 (if(1, 9223372036854775808, 1)),
@@ -3758,3 +3786,4 @@ coalesce(cast(1111111111111111111 as unsigned), 1) co;
 i	c	co
 1111111111111111111	1111111111111111111	1111111111111111111
 End of 5.0 tests
+
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 4b7dbb3dbe9..9e2bc52657a 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -377,3 +377,60 @@ create table t1(f1 int, `*f2` int);
 insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
+CREATE TABLE t1 (
+request_id int unsigned NOT NULL auto_increment,
+user_id varchar(12) default NULL,
+time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+ip_address varchar(15) default NULL,
+PRIMARY KEY (request_id),
+KEY user_id_2 (user_id,time_stamp)
+);
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	7
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	14
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	21
+DROP TABLE t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7185a70689a..50b41e1352f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2543,7 +2543,7 @@ create table t1(f1 int, f2 int);
 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
 .f1 and ta.f2=tb.f2;
 insert into t1 values(1,1),(2,2);
-create view v2 as select * from v1 where a > 1 with check option;
+create view v2 as select * from v1 where a > 1 with local check option;
 select * from v2;
 a	b
 2	2
@@ -3034,4 +3034,105 @@ SHOW CREATE VIEW v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd`
 DROP VIEW v1;
+CREATE TABLE t1 (mydate DATETIME);
+INSERT INTO t1 VALUES 
+('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
+CREATE VIEW v1 AS SELECT mydate from t1;
+SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1), (2);
+CREATE VIEW v1 AS
+SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
+SELECT * FROM v1;
+b
+1
+2
+UPDATE v1 SET b=3;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM v1;
+b
+1
+2
+SELECT * FROM t1;
+a
+1
+2
+SELECT * FROM t2;
+b
+1
+2
+DROP VIEW v1;
+DROP TABLE t1,t2;
+create table t1(f1 int, f2 int);
+insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
+select * from t1;
+f1	f2
+1	2
+1	3
+1	1
+2	3
+2	1
+2	2
+create view v1 as select * from t1 order by f2;
+select * from v1;
+f1	f2
+1	1
+2	1
+1	2
+2	2
+1	3
+2	3
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2`
+select * from v1 order by f1;
+f1	f2
+1	1
+1	2
+1	3
+2	1
+2	2
+2	3
+explain extended select * from v1 order by f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2`
+drop view v1;
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL PRIMARY KEY,
+country varchar(32),
+code int(11) default NULL
+);
+INSERT INTO t1 VALUES
+(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.
diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index 225ddbc3ee2..14aa4b02cfe 100644
--- a/mysql-test/t/default.test
+++ b/mysql-test/t/default.test
@@ -137,6 +137,13 @@ insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAUL
 select * from bug20691 order by x asc;
 drop table bug20691;
 
-###
+create table t1 (id int not null);
+insert into t1 values(default);
+
+create view v1 (c) as select id from t1;
+insert into t1 values(default);
+drop view v1;
+drop table t1;
+
 --echo End of 5.0 tests.
 
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 95fdf642b94..ff9fcad1fcf 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -428,3 +428,12 @@ drop table t1;
 create table t1 select GeomFromText('point(1 1)');
 desc t1;
 drop table t1;
+
+#
+# Bug #20691 (DEFAULT over NOT NULL field)
+#
+create table t1 (g geometry not null);
+--error ER_CANT_CREATE_GEOMETRY_OBJECT
+insert into t1 values(default);
+drop table t1;
+
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index d1dd485e21c..f8922317eb3 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -999,4 +999,28 @@ where table_schema = 'test' and table_name not in
  where table_schema = 'test' and column_name = 'f3');
 drop table t1,t2;
 
+
+#
+# Bug#24630  Subselect query crashes mysqld
+#
+select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
+(select cast(table_name as char)  from information_schema.tables
+ order by table_name limit 1) limit 1;
+
+select t.table_name, group_concat(t.table_schema, '.', t.table_name),
+       count(*) as num1
+from information_schema.tables t
+inner join information_schema.columns c1
+on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
+where t.table_schema = 'information_schema' and
+        c1.ordinal_position =
+        (select isnull(c2.column_type) -
+         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
+         count(*) as num
+         from information_schema.columns c2 where
+         c2.table_schema='information_schema' and
+         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
+          group by c2.column_type order by num limit 1)
+group by t.table_name order by num1, t.table_name;
+
 # End of 5.0 tests.
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 45a2ede091b..c351891d205 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -384,6 +384,32 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
 DROP TABLE t1;
 
 
+#
+# Bug#26159: crash for a loose scan of a table that has been emptied 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL,
+  name varchar(20) NOT NULL,
+  dept varchar(20) NOT NULL,
+  age tinyint(3) unsigned NOT NULL,
+  PRIMARY KEY (id),
+  INDEX (name,dept)
+) ENGINE=InnoDB;
+INSERT INTO t1(id, dept, age, name) VALUES
+  (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+  (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+  (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+  (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+DELETE FROM t1;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+
+DROP TABLE t1;
+
 --source include/innodb_rollback_on_timeout.inc
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 404d67390ab..5c60fc8e1f0 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -265,4 +265,30 @@ INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 
 DROP DATABASE bug21774_1;
 DROP DATABASE bug21774_2;
+USE test;
 
+#
+# Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
+#            updated.
+#
+create table t1(f1 int primary key, f2 int);
+--enable_info
+insert into t1 values (1,1);
+insert into t1 values (1,1) on duplicate key update f2=1;
+insert into t1 values (1,1) on duplicate key update f2=2;
+--disable_info
+select * from t1;
+drop table t1;
+
+#
+# Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT if no rows
+#            were inserted.
+#
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) select 1;
+select @@identity;
+drop table t1;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 20462f2ca3f..a0620e144c2 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -802,3 +802,26 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
 DROP TABLE t1,t2;
+
+#
+# Bug 26017: LEFT OUTER JOIN over two constant tables and 
+#            a case-insensitive comparison predicate field=const 
+# 
+
+CREATE TABLE t1 (
+  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+  f2 varchar(16) collate latin1_swedish_ci
+);
+CREATE TABLE t2 (
+  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+  f3 varchar(16) collate latin1_swedish_ci
+);
+
+INSERT INTO t1 VALUES ('bla','blah');
+INSERT INTO t2 VALUES ('bla','sheep');
+
+SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 92b831dd79b..4cdceff6755 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3207,6 +3207,29 @@ EXPLAIN SELECT * FROM t1
         (ID2_with_null=1 OR ID2_with_null=2);
 
 DROP TABLE t1;
+
+#
+# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
+#
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+DROP TABLE t1,t2;
 #
 # Bug #22026: Warning when using IF statement and large unsigned bigint
 #
@@ -3231,3 +3254,4 @@ select
   coalesce(cast(1111111111111111111 as unsigned), 1) co;
 
 --echo End of 5.0 tests
+
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 5a49de248b1..3ce7ef72670 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -307,3 +307,38 @@ insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
 # End of 4.1 tests
+
+#
+# Bug #24035: performance degradation with condition int_field=big_decimal
+#
+
+CREATE TABLE t1 (
+  request_id int unsigned NOT NULL auto_increment,
+  user_id varchar(12) default NULL,
+  time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+  ip_address varchar(15) default NULL,
+  PRIMARY KEY (request_id),
+  KEY user_id_2 (user_id,time_stamp)
+);
+
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999; 
+show status like '%Handler_read%';
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; 
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+
+DROP TABLE t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 47a5a54007b..33e381af476 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2385,7 +2385,7 @@ create table t1(f1 int, f2 int);
 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
 .f1 and ta.f2=tb.f2;
 insert into t1 values(1,1),(2,2);
-create view v2 as select * from v1 where a > 1 with check option;
+create view v2 as select * from v1 where a > 1 with local check option;
 select * from v2;
 update v2 set b=3 where a=2;
 select * from v2;
@@ -2986,4 +2986,76 @@ SHOW CREATE VIEW v1;
 
 DROP VIEW v1;
 
+#
+# Bug #26124: BETWEEN over a view column of the DATETIME type
+#
+
+CREATE TABLE t1 (mydate DATETIME);
+INSERT INTO t1 VALUES 
+  ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
+
+CREATE VIEW v1 AS SELECT mydate from t1;
+
+SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+#
+# Bug #25931: update of a multi-table view with check option
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE VIEW v1 AS
+  SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
+
+SELECT * FROM v1;
+--error 1369
+UPDATE v1 SET b=3;
+SELECT * FROM v1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+#
+# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm.
+#
+create table t1(f1 int, f2 int);
+insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
+select * from t1;
+create view v1 as select * from t1 order by f2;
+select * from v1;
+explain extended select * from v1;
+select * from v1 order by f1;
+explain extended select * from v1 order by f1;
+drop view v1;
+drop table t1;
+
+#
+# Bug#26209: queries with GROUP BY and ORDER BY using views 
+#
+
+CREATE TABLE t1 (
+  id int(11) NOT NULL PRIMARY KEY,
+  country varchar(32),
+  code int(11) default NULL
+);
+INSERT INTO t1 VALUES
+  (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
+SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
diff --git a/mysys/my_pthread.c b/mysys/my_pthread.c
index b9da9d4817a..0422e0c6512 100644
--- a/mysys/my_pthread.c
+++ b/mysys/my_pthread.c
@@ -29,7 +29,7 @@
 #define SCHED_POLICY SCHED_OTHER
 #endif
 
-uint thd_lib_detected;
+uint thd_lib_detected= 0;
 
 #ifndef my_pthread_setprio
 void my_pthread_setprio(pthread_t thread_id,int prior)
diff --git a/sql/field.h b/sql/field.h
index 7f71fd2aec8..fe3c59c89f5 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -29,7 +29,7 @@
 class Send_field;
 class Protocol;
 struct st_cache_field;
-void field_conv(Field *to,Field *from);
+int field_conv(Field *to,Field *from);
 
 inline uint get_enum_pack_length(int elements)
 {
@@ -1252,7 +1252,7 @@ public:
   uint max_packed_col_length(uint max_length);
   void free() { value.free(); }
   inline void clear_temporary() { bzero((char*) &value,sizeof(value)); }
-  friend void field_conv(Field *to,Field *from);
+  friend int field_conv(Field *to,Field *from);
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index e5752708123..dbe58d804ad 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -679,7 +679,7 @@ void (*Copy_field::get_copy_func(Field *to,Field *from))(Copy_field*)
 
 /* Simple quick field convert that is called on insert */
 
-void field_conv(Field *to,Field *from)
+int field_conv(Field *to,Field *from)
 {
   if (to->real_type() == from->real_type() &&
       !(to->type() == FIELD_TYPE_BLOB && to->table->copy_blobs))
@@ -707,7 +707,7 @@ void field_conv(Field *to,Field *from)
       if (to->ptr != from->ptr)
 #endif
         memcpy(to->ptr,from->ptr,to->pack_length());
-      return;
+      return 0;
     }
   }
   if (to->type() == FIELD_TYPE_BLOB)
@@ -723,8 +723,7 @@ void field_conv(Field *to,Field *from)
          from->real_type() != MYSQL_TYPE_STRING &&
          from->real_type() != MYSQL_TYPE_VARCHAR))
       blob->value.copy();
-    blob->store(blob->value.ptr(),blob->value.length(),from->charset());
-    return;
+    return blob->store(blob->value.ptr(),blob->value.length(),from->charset());
   }
   if ((from->result_type() == STRING_RESULT &&
        (to->result_type() == STRING_RESULT ||
@@ -741,15 +740,15 @@ void field_conv(Field *to,Field *from)
       end with \0. Can be replaced with .ptr() when we have our own
       string->double conversion.
     */
-    to->store(result.c_ptr_quick(),result.length(),from->charset());
+    return to->store(result.c_ptr_quick(),result.length(),from->charset());
   }
   else if (from->result_type() == REAL_RESULT)
-    to->store(from->val_real());
+    return to->store(from->val_real());
   else if (from->result_type() == DECIMAL_RESULT)
   {
     my_decimal buff;
-    to->store_decimal(from->val_decimal(&buff));
+    return to->store_decimal(from->val_decimal(&buff));
   }
   else
-    to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
+    return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
 }
diff --git a/sql/item.cc b/sql/item.cc
index 9a55eb25e2c..b4a7820eabf 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4245,18 +4245,19 @@ void Item_field::save_org_in_field(Field *to)
 
 int Item_field::save_in_field(Field *to, bool no_conversions)
 {
+  int res;
   if (result_field->is_null())
   {
     null_value=1;
-    return set_field_to_null_with_conversions(to, no_conversions);
+    res= set_field_to_null_with_conversions(to, no_conversions);
   }
   else
   {
     to->set_notnull();
-    field_conv(to,result_field);
+    res= field_conv(to,result_field);
     null_value=0;
   }
-  return 0;
+  return res;
 }
 
 
@@ -5276,18 +5277,7 @@ my_decimal *Item_ref::val_decimal(my_decimal *decimal_value)
 int Item_ref::save_in_field(Field *to, bool no_conversions)
 {
   int res;
-  if (result_field)
-  {
-    if (result_field->is_null())
-    {
-      null_value= 1;
-      return set_field_to_null_with_conversions(to, no_conversions);
-    }
-    to->set_notnull();
-    field_conv(to, result_field);
-    null_value= 0;
-    return 0;
-  }
+  DBUG_ASSERT(!result_field);
   res= (*ref)->save_in_field(to, no_conversions);
   null_value= (*ref)->null_value;
   return res;
@@ -5505,6 +5495,13 @@ int Item_default_value::save_in_field(Field *field_arg, bool no_conversions)
   {
     if (field_arg->flags & NO_DEFAULT_VALUE_FLAG)
     {
+      if (field_arg->reset())
+      {
+        my_message(ER_CANT_CREATE_GEOMETRY_OBJECT,
+                   ER(ER_CANT_CREATE_GEOMETRY_OBJECT), MYF(0));
+        return -1;
+      }
+
       if (context->error_processor == &view_error_processor)
       {
         TABLE_LIST *view= cached_table->top_table();
@@ -5523,7 +5520,6 @@ int Item_default_value::save_in_field(Field *field_arg, bool no_conversions)
                             ER(ER_NO_DEFAULT_FOR_FIELD),
                             field_arg->field_name);
       }
-      field_arg->set_default();
       return 1;
     }
     field_arg->set_default();
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 7b69e2778ad..9ca010e7cf5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1240,11 +1240,11 @@ void Item_func_between::fix_length_and_dec()
     They are compared as integers, so for const item this time-consuming
     conversion can be done only once, not for every single comparison
   */
-  if (args[0]->type() == FIELD_ITEM &&
+  if (args[0]->real_item()->type() == FIELD_ITEM &&
       thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
       thd->lex->sql_command != SQLCOM_SHOW_CREATE)
   {
-    Field *field=((Item_field*) args[0])->field;
+    Field *field=((Item_field*) (args[0]->real_item()))->field;
     if (field->can_be_compared_as_longlong())
     {
       /*
@@ -4089,11 +4089,9 @@ longlong Item_equal::val_int()
 
 void Item_equal::fix_length_and_dec()
 {
-  Item *item= const_item ? const_item : get_first();
+  Item *item= get_first();
   eval_item= cmp_item::get_comparator(item->result_type(),
                                       item->collation.collation);
-  if (item->result_type() == STRING_RESULT)
-    eval_item->cmp_charset= cmp_collation.collation;
 }
 
 bool Item_equal::walk(Item_processor processor, byte *arg)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index eb1f4dfdabd..d7db928e281 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1299,7 +1299,6 @@ class Item_equal: public Item_bool_func
   Item *const_item;        /* optional constant item equal to fields items */
   cmp_item *eval_item;
   bool cond_false;
-  DTCollation cmp_collation;
 public:
   inline Item_equal()
     : Item_bool_func(), const_item(0), eval_item(0), cond_false(0)
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 1bde8306072..0948487869a 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -708,6 +708,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 		      char* packet, uint packet_length);
 void log_slow_statement(THD *thd);
 bool check_dup(const char *db, const char *name, TABLE_LIST *tables);
+bool compare_record(TABLE *table, query_id_t query_id);
 
 bool table_cache_init(void);
 void table_cache_free(void);
@@ -938,7 +939,8 @@ int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
-bool get_schema_tables_result(JOIN *join);
+bool get_schema_tables_result(JOIN *join,
+                              enum enum_schema_table_state executed_place);
 #define is_schema_db(X) \
   !my_strcasecmp(system_charset_info, information_schema_name.str, (X))
 
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f0af4b7db2a..4838e690dd7 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -4296,7 +4296,22 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
   err= value->save_in_field_no_warnings(field, 1);
   if (err > 0 && field->cmp_type() != value->result_type())
   {
-    tree= 0;
+    if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
+	value->result_type() == item_cmp_type(field->result_type(),
+                                              value->result_type()))
+
+    {
+      tree= new (alloc) SEL_ARG(field, 0, 0);
+      tree->type= SEL_ARG::IMPOSSIBLE;
+    }
+    else
+    {
+      /*
+        TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
+        for the cases like int_field > 999999999999999999999999 as well.
+      */
+      tree= 0;
+    }
     goto end;
   } 
   if (err < 0)
@@ -8750,14 +8765,13 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(void)
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset");
 
   file->extra(HA_EXTRA_KEYREAD); /* We need only the key attributes */
-  result= file->ha_index_init(index);
-  result= file->index_last(record);
-  if (result == HA_ERR_END_OF_FILE)
-    DBUG_RETURN(0);
-  if (result)
+  if ((result= file->ha_index_init(index)))
     DBUG_RETURN(result);
   if (quick_prefix_select && quick_prefix_select->reset())
     DBUG_RETURN(1);
+  result= file->index_last(record);
+  if (result == HA_ERR_END_OF_FILE)
+    DBUG_RETURN(0);
   /* Save the prefix of the last group. */
   key_copy(last_prefix, record, index_info, group_prefix_len);
 
@@ -8806,7 +8820,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next()
 #else
   int result;
 #endif
-  int is_last_prefix;
+  int is_last_prefix= 0;
 
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::get_next");
 
@@ -8821,13 +8835,18 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next()
       Check if this is the last group prefix. Notice that at this point
       this->record contains the current prefix in record format.
     */
-    is_last_prefix= key_cmp(index_info->key_part, last_prefix,
-                            group_prefix_len);
-    DBUG_ASSERT(is_last_prefix <= 0);
-    if (result == HA_ERR_KEY_NOT_FOUND)
-      continue;
-    else if (result)
+    if (!result)
+    {
+      is_last_prefix= key_cmp(index_info->key_part, last_prefix,
+                              group_prefix_len);
+      DBUG_ASSERT(is_last_prefix <= 0);
+    }
+    else 
+    {
+      if (result == HA_ERR_KEY_NOT_FOUND)
+        continue;
       break;
+    }
 
     if (have_min)
     {
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index fb59aeea8e7..39d7f8e9b58 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1186,23 +1186,29 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
           goto before_trg_err;
 
         if ((error=table->file->update_row(table->record[1],table->record[0])))
-	{
-	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
+        {
+          if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
           {
             table->file->restore_auto_increment();
             goto ok_or_after_trg_err;
           }
           goto err;
-	}
-        info->updated++;
+        }
+        if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) ||
+            compare_record(table, query_id))
+        {
+          info->updated++;
 
-        if (table->next_number_field)
-          table->file->adjust_next_insert_id_after_explicit_value(table->next_number_field->val_int());
+          if (table->next_number_field)
+            table->file->adjust_next_insert_id_after_explicit_value(
+              table->next_number_field->val_int());
 
-        trg_error= (table->triggers &&
-                    table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                      TRG_ACTION_AFTER, TRUE));
-        info->copied++;
+          trg_error= (table->triggers &&
+                      table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                                        TRG_ACTION_AFTER,
+                                                        TRUE));
+          info->copied++;
+        }
         goto ok_or_after_trg_err;
       }
       else /* DUP_REPLACE */
@@ -2624,7 +2630,7 @@ bool select_insert::send_eof()
   }
 
   if (last_insert_id)
-    thd->insert_id(last_insert_id);		// For binary log
+    thd->insert_id(info.copied ? last_insert_id : 0);		// For binary log
   /* Write to binlog before commiting transaction */
   if (mysql_bin_log.is_open())
   {
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index a362e6c7fd2..ce76c35b33c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1751,7 +1751,6 @@ bool st_lex::can_be_merged()
   }
 
   return (selects_allow_merge &&
-	  select_lex.order_list.elements == 0 &&
 	  select_lex.group_list.elements == 0 &&
 	  select_lex.having == 0 &&
           select_lex.with_sum_func == 0 &&
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c9f0108f6bf..ed108828909 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1505,7 +1505,7 @@ JOIN::exec()
 
   if ((curr_join->select_lex->options & OPTION_SCHEMA_TABLE) &&
       !thd->lex->describe &&
-      get_schema_tables_result(curr_join))
+      get_schema_tables_result(curr_join, PROCESSED_BY_JOIN_EXEC))
   {
     DBUG_VOID_RETURN;
   }
@@ -12374,7 +12374,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   /* Fill schema tables with data before filesort if it's necessary */
   if ((join->select_lex->options & OPTION_SCHEMA_TABLE) &&
       !thd->lex->describe &&
-      get_schema_tables_result(join))
+      get_schema_tables_result(join, PROCESSED_BY_CREATE_SORT_INDEX))
     goto err;
 
   if (table->s->tmp_table)
@@ -12746,15 +12746,15 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length,
 
   for (;order;order=order->next,pos++)
   {
-    pos->field=0; pos->item=0;
-    if (order->item[0]->type() == Item::FIELD_ITEM)
-      pos->field= ((Item_field*) (*order->item))->field;
-    else if (order->item[0]->type() == Item::SUM_FUNC_ITEM &&
-	     !order->item[0]->const_item())
-      pos->field= ((Item_sum*) order->item[0])->get_tmp_table_field();
-    else if (order->item[0]->type() == Item::COPY_STR_ITEM)
+    Item *item= order->item[0]->real_item();
+    pos->field= 0; pos->item= 0;
+    if (item->type() == Item::FIELD_ITEM)
+      pos->field= ((Item_field*) item)->field;
+    else if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item())
+      pos->field= ((Item_sum*) item)->get_tmp_table_field();
+    else if (item->type() == Item::COPY_STR_ITEM)
     {						// Blob patch
-      pos->item= ((Item_copy_string*) (*order->item))->item;
+      pos->item= ((Item_copy_string*) item)->item;
     }
     else
       pos->item= *order->item;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 23059ac545a..659dd49d537 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3938,13 +3938,15 @@ int make_schema_select(THD *thd, SELECT_LEX *sel,
   SYNOPSIS
     get_schema_tables_result()
     join  join which use schema tables
+    executed_place place where I_S table processed
 
   RETURN
     FALSE success
     TRUE  error
 */
 
-bool get_schema_tables_result(JOIN *join)
+bool get_schema_tables_result(JOIN *join,
+                              enum enum_schema_table_state executed_place)
 {
   JOIN_TAB *tmp_join_tab= join->join_tab+join->tables;
   THD *thd= join->thd;
@@ -3964,14 +3966,24 @@ bool get_schema_tables_result(JOIN *join)
       bool is_subselect= (&lex->unit != lex->current_select->master_unit() &&
                           lex->current_select->master_unit()->item);
       /*
-        The schema table is already processed and 
-        the statement is not a subselect.
-        So we don't need to handle this table again.
+        If schema table is already processed and
+        the statement is not a subselect then
+        we don't need to fill this table again.
+        If schema table is already processed and
+        schema_table_state != executed_place then
+        table is already processed and
+        we should skip second data processing.
       */
-      if (table_list->is_schema_table_processed && !is_subselect)
+      if (table_list->schema_table_state &&
+          (!is_subselect || table_list->schema_table_state != executed_place))
         continue;
 
-      if (is_subselect) // is subselect
+      /*
+        if table is used in a subselect and
+        table has been processed earlier with the same
+        'executed_place' value then we should refresh the table.
+      */
+      if (table_list->schema_table_state && is_subselect)
       {
         table_list->table->file->extra(HA_EXTRA_NO_CACHE);
         table_list->table->file->extra(HA_EXTRA_RESET_STATE);
@@ -3988,10 +4000,10 @@ bool get_schema_tables_result(JOIN *join)
       {
         result= 1;
         join->error= 1;
-        table_list->is_schema_table_processed= TRUE;
+        table_list->schema_table_state= executed_place;
         break;
       }
-      table_list->is_schema_table_processed= TRUE;
+      table_list->schema_table_state= executed_place;
     }
   }
   thd->no_warnings_for_error= 0;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b85c617b12d..1db77f8704c 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -26,7 +26,7 @@
 
 /* Return 0 if row hasn't changed */
 
-static bool compare_record(TABLE *table, query_id_t query_id)
+bool compare_record(TABLE *table, query_id_t query_id)
 {
   if (table->s->blob_fields + table->s->varchar_fields == 0)
     return cmp_record(table,record[1]);
@@ -1445,6 +1445,15 @@ int multi_update::do_updates(bool from_send_error)
 
       if (!can_compare_record || compare_record(table, thd->query_id))
       {
+        int error;
+        if ((error= cur_table->view_check_option(thd, ignore)) !=
+            VIEW_CHECK_OK)
+        {
+          if (error == VIEW_CHECK_SKIP)
+            continue;
+          else if (error == VIEW_CHECK_ERROR)
+            goto err;
+        }
 	if ((local_error=table->file->update_row(table->record[1],
 						 table->record[0])))
 	{
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 18ef3eaf29c..fd2d6c89785 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1263,6 +1263,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
 	but it will not be included to SELECT_LEX tree, because it
 	will not be executed
       */
+      table->select_lex->order_list.push_back(&lex->select_lex.order_list);
       goto ok;
     }
 
diff --git a/sql/table.cc b/sql/table.cc
index a6750b2b4d7..e2cb0bea300 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3020,7 +3020,7 @@ void st_table_list::reinit_before_use(THD *thd)
   */
   table= 0;
   /* Reset is_schema_table_processed value(needed for I_S tables */
-  is_schema_table_processed= FALSE;
+  schema_table_state= NOT_PROCESSED;
 
   TABLE_LIST *embedded; /* The table at the current level of nesting. */
   TABLE_LIST *embedding= this; /* The parent nested table reference. */
diff --git a/sql/table.h b/sql/table.h
index 70e64439af5..8f013f3de19 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -287,6 +287,12 @@ struct st_table {
   void reset_item_list(List<Item> *item_list) const;
 };
 
+enum enum_schema_table_state
+{ 
+  NOT_PROCESSED= 0,
+  PROCESSED_BY_CREATE_SORT_INDEX,
+  PROCESSED_BY_JOIN_EXEC
+};
 
 typedef struct st_foreign_key_info
 {
@@ -529,7 +535,6 @@ typedef struct st_table_list
   st_select_lex_unit *derived;		/* SELECT_LEX_UNIT of derived table */
   ST_SCHEMA_TABLE *schema_table;        /* Information_schema table */
   st_select_lex	*schema_select_lex;
-  bool is_schema_table_processed;
   /*
     True when the view field translation table is used to convert
     schema table fields for backwards compatibility with SHOW command.
@@ -638,6 +643,7 @@ typedef struct st_table_list
   */
   bool          prelocking_placeholder;
 
+  enum enum_schema_table_state schema_table_state;
   void calc_md5(char *buffer);
   void set_underlying_merge();
   int view_check_option(THD *thd, bool ignore_failure);