Merge mysql.com:/d2/hf/mrg/mysql-5.0-opt

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


mysql-test/r/alter_table.result:
  Auto merged
mysql-test/r/distinct.result:
  Auto merged
mysql-test/r/heap.result:
  Auto merged
mysql-test/r/ps_2myisam.result:
  Auto merged
mysql-test/r/ps_3innodb.result:
  Auto merged
mysql-test/r/ps_4heap.result:
  Auto merged
mysql-test/r/ps_5merge.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
BitKeeper/deleted/.del-CMakeLists.txt~1:
  Auto merged
BitKeeper/deleted/.del-ps_6bdb.result:
  Auto merged
mysql-test/t/alter_table.test:
  Auto merged
mysys/my_error.c:
  Auto merged
sql/field.cc:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/key.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/table.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/include/mix1.inc:
  merging
mysql-test/r/group_by.result:
  SCCS merged
mysql-test/r/innodb_mysql.result:
  merging
mysql-test/r/join.result:
  merging
mysql-test/r/subselect.result:
  merging
mysql-test/r/type_datetime.result:
  SCCS merged
mysql-test/r/windows.result:
  SCCS merged
mysql-test/t/group_by.test:
  SCCS merged
mysql-test/t/join.test:
  merging
mysql-test/t/subselect.test:
  merging
mysql-test/t/type_datetime.test:
  merging
mysql-test/t/windows.test:
  SCCS merged
sql/item_timefunc.cc:
  merging
sql/sql_base.cc:
  SCCS merged
storage/innobase/handler/ha_innodb.cc:
  merging

33 files changed, 1078 insertions, 150 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index f0d143c4570..aea1b6e0b58 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -168,6 +168,35 @@ connection default;
 drop table t1;
 disconnect con1;
 
+#
+# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields
+# used in partial unique indices.
+#
+
+CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
 --echo End of 4.1 tests
 
 
@@ -415,7 +444,54 @@ DROP TABLE t1;
 
 --source include/innodb_rollback_on_timeout.inc
 
+-- source include/have_innodb.inc
+
+#
+# Bug #27650: INSERT fails after multi-row INSERT of the form:
+# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
+#
+
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+truncate table t1;
+
+insert into t1 (id, c) values (NULL, 'a');
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+drop table t1;
+
 --echo End of 5.0 tests
+
 #
 # Test of behaviour with CREATE ... SELECT
 #
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 09290d760ce..195d1061664 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -1171,7 +1171,7 @@ execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 ######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -1180,7 +1180,7 @@ select 'true' as found from t9
 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
   and c17= @arg00 ;
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 20538694218..8bf8c95c6fb 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -633,7 +633,7 @@ EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
 WHERE ADDDATE(a,1) = '2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
 INSERT INTO t2 VALUES (0xf6);
 INSERT INTO t2 VALUES ('oe');
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index c7464bb21d2..7068b62993b 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1035,6 +1035,18 @@ 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 = '';
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+create table t1(f1 int, f2 int);
+select * from t1 group by f1;
+ERROR 42000: 'test.t1.f2' isn't in GROUP BY
+select * from t1 group by f2;
+ERROR 42000: 'test.t1.f1' isn't in GROUP BY
+select * from t1 group by f1, f2;
+f1	f2
+select t1.f1,t.* from t1, t1 t group by 1;
+ERROR 42000: 'test.t.f1' isn't in GROUP BY
+drop table t1;
+SET SQL_MODE = '';
 CREATE TABLE t1 (a INT, b INT,
 PRIMARY KEY (a),
 KEY i2(a,b));
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index e720c38ec8d..895cb88a018 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -112,6 +112,39 @@ c1
 Before and after comparison
 0
 drop table t1;
+CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1	cnt
+1a	1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1	cnt
+1a	2
+DROP TABLE t1;
+CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1	cnt
+1a	1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1	cnt
+1a	2
+DROP TABLE t1;
+CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1	cnt
+1a	1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1	cnt
+1a	2
+DROP TABLE t1;
 End of 4.1 tests
 create table t1m (a int) engine = MEMORY;
 create table t1i (a int);
@@ -387,7 +420,45 @@ a
 2
 5
 drop table t1;
-End of 5.0 tests
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+select * from t1;
+id	c	counter
+2	a	2
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+select * from t1;
+id	c	counter
+2	a	2
+3	b	1
+truncate table t1;
+insert into t1 (id, c) values (NULL, 'a');
+select * from t1;
+id	c	counter
+1	a	1
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+select * from t1;
+id	c	counter
+1	a	1
+3	b	2
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+select * from t1;
+id	c	counter
+3	b	2
+4	a	2
+drop table t1;
 CREATE TABLE t1 (a int, b int);
 insert into t1 values (1,1),(1,2);
 CREATE TABLE t2 (primary key (a)) select * from t1;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 0a45aa2eb9b..840a92dcce2 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -764,6 +764,49 @@ natural join t5;
 y	c	b	a	z
 1	3	2	1	4
 drop table t1, t2, t3, t4, t5;
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+USING (Test_ID);
+DESCRIBE tv1;
+Field	Type	Null	Key	Default	Extra
+Name	varchar(50)	YES		NULL	
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+Field	Type	Null	Key	Default	Extra
+Name	varchar(50)	YES		NULL	
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+create table t1 (a int, b int);
+insert into t1 values 
+(NULL, 1),
+(NULL, 2),
+(NULL, 3),
+(NULL, 4);
+create table t2 (a int not null, primary key(a));
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t3 (a int not null, primary key(a));
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+flush status;
+select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+a	b	a	a
+explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+We expect rnd_next=5, and read_key must be 0 because of short-cutting:
+show status like 'Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	5
+drop table t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2 (a int, b int, filler char(100), key(a), key(b));
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index d18bb8dc434..2ce35dae092 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -3077,7 +3077,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3090,7 +3090,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index e6ff668760c..70181ecccdc 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -3060,7 +3060,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3073,7 +3073,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 046992806cc..19be5a2707e 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -3061,7 +3061,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3074,7 +3074,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index bbdb80df5d4..ebdc5c8c9fd 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2997,7 +2997,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3010,7 +3010,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6018,7 +6018,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6031,7 +6031,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 20d38ba84bd..2cffb698fc0 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -3060,7 +3060,7 @@ found
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3073,7 +3073,7 @@ and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 866701d2204..66283f16120 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -947,25 +947,19 @@ COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
+Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050328 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050328 invalid'
+Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 0
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
+Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 8263350fe58..1fff29cd72c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -394,13 +394,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	43	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)
+Note	1003	select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')
 EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	t1	index	NULL	PRIMARY	43	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note	1003	select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
 SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 date
 2002-08-03
@@ -4012,6 +4012,29 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
 ERROR HY000: Invalid use of group function
 SET @@sql_mode=default;
 DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT);
+INSERT INTO t1 values (1000),(1001),(1002);
+SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT COUNT(1) FROM DUAL;
+COUNT(1)
+1
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT 
+SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
+FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT t1.a as XXA, 
+SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
+FROM t1;
+ERROR HY000: Invalid use of group function
+DROP TABLE t1,t2;
+End of 5.0 tests.
 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/type_datetime.result b/mysql-test/r/type_datetime.result
index 045a6cd1c0a..95bd3061fa6 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -192,6 +192,80 @@ CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMA
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
 101112.098700
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+curdate() < now()	f1 < now()	cast(f1 as date) < now()
+1	1	1
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+f1	f3
+2001-02-05	2001-02-05 01:01:01
+2001-03-10	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+f1	f3
+2001-02-05	2001-02-05 01:01:01
+2001-03-10	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+f1	f2
+2001-02-05	2001-02-05 00:00:00
+2001-03-10	2001-03-09 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+1
+1
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+f1 > f2, f1 = f2, f1 < f2
+from t1;
+f1	f2	UNIX_TIMESTAMP(f2)	UNIX_TIMESTAMP(f1)	f1 > f2	f1 = f2	f1 < f2
+2001-01-01	2001-01-01 01:01:01	978300061	978296400	0	0	1
+2001-02-05	2001-02-05 00:00:00	981320400	981320400	0	1	0
+2001-03-10	2001-03-09 01:01:01	984088861	984171600	1	0	0
+2001-04-15	2001-04-15 00:00:00	987282000	987282000	0	1	0
+2001-05-20	2001-05-20 01:01:01	990309661	990306000	0	0	1
+drop table t1;
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+f2
+2001-02-05 00:00:00
+2001-03-09 01:01:01
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+f1	f2	f3
+2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
+2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+cast(f3 as date);
+f1	f2	f3
+2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
+2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+f2
+2001-01-01 01:01:01
+2001-02-05 00:00:00
+2001-03-09 01:01:01
+select f2, f3 from t1 where '01-03-10' between f2 and f3;
+f2	f3
+2001-03-09 01:01:01	2001-03-10 01:01:01
+select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
+f2
+2001-04-15 00:00:00
+SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
+1
+drop table t1;
 set @org_mode=@@sql_mode;
 create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
 Warnings:
diff --git a/mysql-test/r/windows.result b/mysql-test/r/windows.result
index 9f3828bff61..adb0048b90e 100644
--- a/mysql-test/r/windows.result
+++ b/mysql-test/r/windows.result
@@ -12,6 +12,13 @@ Warnings:
 Warning	0	DATA DIRECTORY option ignored
 Warning	0	INDEX DIRECTORY option ignored
 drop table t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1);
+EXPLAIN SELECT * FROM t1 WHERE b =  (SELECT max(2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+End of 5.0 tests.
 create procedure proc_1() install plugin my_plug soname '\\root\\some_plugin.dll';
 call proc_1();
 ERROR HY000: No paths allowed for shared library
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index fb9c09d4763..4e21568377f 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -752,6 +752,20 @@ SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
    HAVING SUM(t1_inner.b)+t1_outer.b > 5);
 DROP TABLE t1;
 SET SQL_MODE = '';
+#
+# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
+#
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+create table t1(f1 int, f2 int);
+--error 1055
+select * from t1 group by f1;
+--error 1055
+select * from t1 group by f2;
+select * from t1 group by f1, f2;
+--error 1055
+select t1.f1,t.* from t1, t1 t group by 1;
+drop table t1;
+SET SQL_MODE = '';
 
 #
 # Bug #21174: Index degrades sort performance and 
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 72d78dd7074..6c9270f76be 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -593,6 +593,46 @@ drop table t1, t2, t3, t4, t5;
 # End of tests for WL#2486 - natural/using join
 
 #
+# BUG#25106: A USING clause in combination with a VIEW results in column 
+#            aliases ignored
+#
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+ USING (Test_ID);
+DESCRIBE tv1;
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+
+
+# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
+create table t1 (a int, b int);
+insert into t1 values 
+  (NULL, 1),
+  (NULL, 2),
+  (NULL, 3),
+  (NULL, 4);
+
+create table t2 (a int not null, primary key(a));
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t3 (a int not null, primary key(a));
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+flush status;
+select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
+show status like 'Handler_read%'; 
+drop table t1, t2, t3;
+
+#
 # BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
 #
 create table t1 (a int); 
@@ -631,21 +671,5 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than
 
 
 drop table t1, t2;
-# BUG#25106: A USING clause in combination with a VIEW results in column 
-#            aliases ignored
-#
-CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
-CREATE TABLE t2 (Test_ID INTEGER);
-CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
-
-CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
- USING (Test_ID);
-DESCRIBE tv1;
-CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
- ON v1.Test_ID = t2.Test_ID;
-DESCRIBE tv2;
-
-DROP VIEW v1;
-DROP TABLE t1,t2,tv1,tv2;
 
 --echo End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3b7cc2abb73..0648c3ebda0 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2819,6 +2819,62 @@ SELECT tt.a, MAX(
   FROM t1 as tt GROUP BY tt.a;
 
 DROP TABLE t1;
+#
+# Bug #27348: SET FUNCTION used in  a subquery from WHERE condition 
+#  
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
+
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+SELECT a FROM t1 t0
+  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode='ansi';
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+--error 1111
+SELECT a FROM t1 t0
+  WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode=default;
+
+DROP TABLE t1;
+
+#
+# Bug #27363: nested aggregates in outer, subquery / sum(select
+# count(outer))
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
+SELECT COUNT(1) FROM DUAL;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 
+  SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
+FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT t1.a as XXA, 
+   SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
+FROM t1;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.0 tests.
 
 #
 # Bug #27348: SET FUNCTION used in  a subquery from WHERE condition 
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index b4c10408b37..eeed14acf15 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -142,6 +142,46 @@ SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 
 #
+# Bug#27590: Wrong DATE/DATETIME comparison.  
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+  f1 > f2, f1 = f2, f1 < f2
+  from t1;
+drop table t1;
+
+#
+# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+  cast(f3 as date);
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+select f2, f3 from t1 where '01-03-10' between f2 and f3;
+select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
+SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
+drop table t1;
+
+#
 # Test of storing datetime into date fields
 #
 
diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test
index efdf0963b80..c28df20f090 100644
--- a/mysql-test/t/windows.test
+++ b/mysql-test/t/windows.test
@@ -30,6 +30,14 @@ drop table t1;
 # End of 4.1 tests
 
 #
+# Bug #27811: The variable 'join_tab' is being used without being defined
+#
+CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1);
+EXPLAIN SELECT * FROM t1 WHERE b =  (SELECT max(2));
+
+--echo End of 5.0 tests.
+
+#
 # Bug #20665: All commands supported in Stored Procedures should work in
 # Prepared Statements
 #
diff --git a/sql/field.cc b/sql/field.cc
index 82f8283ba56..db2a45af726 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -6395,6 +6395,15 @@ uint Field_string::max_packed_col_length(uint max_length)
   return (max_length > 255 ? 2 : 1)+max_length;
 }
 
+uint Field_string::get_key_image(char *buff, uint length, imagetype type_arg)
+{
+  uint bytes = my_charpos(field_charset, ptr, ptr + field_length,
+                          length / field_charset->mbmaxlen);
+  memcpy(buff, ptr, bytes);
+  if (bytes < length)
+    bzero(buff + bytes, length - bytes);
+  return bytes;
+}
 
 Field *Field_string::new_field(MEM_ROOT *root, struct st_table *new_table,
                                bool keep_type)
@@ -6853,9 +6862,7 @@ uint Field_varstring::max_packed_col_length(uint max_length)
   return (max_length > 255 ? 2 : 1)+max_length;
 }
 
-
-void Field_varstring::get_key_image(char *buff, uint length,
-                                    imagetype type_arg)
+uint Field_varstring::get_key_image(char *buff, uint length, imagetype type)
 {
   uint f_length=  length_bytes == 1 ? (uint) (uchar) *ptr : uint2korr(ptr);
   uint local_char_length= length / field_charset->mbmaxlen;
@@ -6874,6 +6881,7 @@ void Field_varstring::get_key_image(char *buff, uint length,
     */
     bzero(buff+HA_KEY_BLOB_LENGTH+f_length, (length-f_length));
   }
+  return HA_KEY_BLOB_LENGTH+f_length;
 }
 
 
@@ -7278,7 +7286,7 @@ int Field_blob::cmp_binary(const char *a_ptr, const char *b_ptr,
 
 /* The following is used only when comparing a key */
 
-void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_blob::get_key_image(char *buff,uint length, imagetype type_arg)
 {
   uint32 blob_length= get_length(ptr);
   char *blob;
@@ -7290,16 +7298,17 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
     MBR mbr;
     Geometry_buffer buffer;
     Geometry *gobj;
+    const uint image_length= SIZEOF_STORED_DOUBLE*4;
 
     if (blob_length < SRID_SIZE)
     {
-      bzero(buff, SIZEOF_STORED_DOUBLE*4);
-      return;
+      bzero(buff, image_length);
+      return image_length;
     }
     get_ptr(&blob);
     gobj= Geometry::construct(&buffer, blob, blob_length);
     if (!gobj || gobj->get_mbr(&mbr, &dummy))
-      bzero(buff, SIZEOF_STORED_DOUBLE*4);
+      bzero(buff, image_length);
     else
     {
       float8store(buff,    mbr.xmin);
@@ -7307,7 +7316,7 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
       float8store(buff+16, mbr.ymin);
       float8store(buff+24, mbr.ymax);
     }
-    return;
+    return image_length;
   }
 #endif /*HAVE_SPATIAL*/
 
@@ -7328,6 +7337,7 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
   }
   int2store(buff,length);
   memcpy(buff+HA_KEY_BLOB_LENGTH, blob, length);
+  return HA_KEY_BLOB_LENGTH+length;
 }
 
 
@@ -7613,7 +7623,7 @@ uint Field_blob::max_packed_col_length(uint max_length)
 
 #ifdef HAVE_SPATIAL
 
-void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_geom::get_key_image(char *buff, uint length, imagetype type)
 {
   char *blob;
   const char *dummy;
@@ -7621,16 +7631,17 @@ void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
   ulong blob_length= get_length(ptr);
   Geometry_buffer buffer;
   Geometry *gobj;
+  const uint image_length= SIZEOF_STORED_DOUBLE*4;
 
   if (blob_length < SRID_SIZE)
   {
-    bzero(buff, SIZEOF_STORED_DOUBLE*4);
-    return;
+    bzero(buff, image_length);
+    return image_length;
   }
   get_ptr(&blob);
   gobj= Geometry::construct(&buffer, blob, blob_length);
   if (!gobj || gobj->get_mbr(&mbr, &dummy))
-    bzero(buff, SIZEOF_STORED_DOUBLE*4);
+    bzero(buff, image_length);
   else
   {
     float8store(buff, mbr.xmin);
@@ -7638,6 +7649,7 @@ void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
     float8store(buff + 16, mbr.ymin);
     float8store(buff + 24, mbr.ymax);
   }
+  return image_length;
 }
 
 
@@ -8421,7 +8433,7 @@ int Field_bit::cmp_offset(uint row_offset)
 }
 
 
-void Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
 {
   if (bit_len)
   {
@@ -8429,7 +8441,9 @@ void Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
     *buff++= bits;
     length--;
   }
-  memcpy(buff, ptr, min(length, bytes_in_rec));
+  uint data_length = min(length, bytes_in_rec);
+  memcpy(buff, ptr, data_length);
+  return data_length + 1;
 }
 
 
diff --git a/sql/field.h b/sql/field.h
index 441ff9079c1..b1be522e260 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -276,8 +276,39 @@ public:
     { memcpy(buff,ptr,length); }
   inline void set_image(char *buff,uint length, CHARSET_INFO *cs)
     { memcpy(ptr,buff,length); }
-  virtual void get_key_image(char *buff, uint length, imagetype type_arg)
-    { get_image(buff,length, &my_charset_bin); }
+
+
+  /*
+    Copy a field part into an output buffer.
+
+    SYNOPSIS
+      Field::get_key_image()
+      buff   [out] output buffer
+      length       output buffer size
+      type         itMBR for geometry blobs, otherwise itRAW
+
+    DESCRIPTION
+      This function makes a copy of field part of size equal to or
+      less than "length" parameter value.
+      For fields of string types (CHAR, VARCHAR, TEXT) the rest of buffer
+      is padded by zero byte.
+
+    NOTES
+      For variable length character fields (i.e. UTF-8) the "length"
+      parameter means a number of output buffer bytes as if all field
+      characters have maximal possible size (mbmaxlen). In the other words,
+      "length" parameter is a number of characters multiplied by
+      field_charset->mbmaxlen.
+
+    RETURN
+      Number of copied bytes (excluding padded zero bytes -- see above).
+  */
+
+  virtual uint get_key_image(char *buff, uint length, imagetype type)
+  {
+    get_image(buff, length, &my_charset_bin);
+    return length;
+  }
   virtual void set_key_image(char *buff,uint length)
     { set_image(buff,length, &my_charset_bin); }
   inline longlong val_int_offset(uint row_offset)
@@ -1133,6 +1164,7 @@ public:
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
   Field *new_field(MEM_ROOT *root, struct st_table *new_table, bool keep_type);
+  virtual uint get_key_image(char *buff,uint length, imagetype type);
 };
 
 
@@ -1185,7 +1217,7 @@ public:
     return cmp_max(a, b, ~0L);
   }
   void sort_string(char *buff,uint length);
-  void get_key_image(char *buff,uint length, imagetype type);
+  uint get_key_image(char *buff,uint length, imagetype type);
   void set_key_image(char *buff,uint length);
   void sql_type(String &str) const;
   char *pack(char *to, const char *from, uint max_length=~(uint) 0);
@@ -1297,7 +1329,7 @@ public:
       store_length(length);
       memcpy_fixed(ptr+packlength,&data,sizeof(char*));
     }
-  void get_key_image(char *buff,uint length, imagetype type);
+  uint get_key_image(char *buff,uint length, imagetype type);
   void set_key_image(char *buff,uint length);
   void sql_type(String &str) const;
   inline bool copy()
@@ -1354,7 +1386,7 @@ public:
   int  store(double nr);
   int  store(longlong nr, bool unsigned_val);
   int  store_decimal(const my_decimal *);
-  void get_key_image(char *buff,uint length,imagetype type);
+  uint get_key_image(char *buff,uint length,imagetype type);
   uint size_of() const { return sizeof(*this); }
   int  reset(void) { return !maybe_null() || Field_blob::reset(); }
 };
@@ -1478,7 +1510,7 @@ public:
   { return cmp_binary((char *) a, (char *) b); }
   int key_cmp(const byte *str, uint length);
   int cmp_offset(uint row_offset);
-  void get_key_image(char *buff, uint length, imagetype type);
+  uint get_key_image(char *buff, uint length, imagetype type);
   void set_key_image(char *buff, uint length)
   { Field_bit::store(buff, length, &my_charset_bin); }
   void sort_string(char *buff, uint length)
diff --git a/sql/item.cc b/sql/item.cc
index bd5e0ae1a8f..f339bad78e4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3576,9 +3576,13 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
             select->inner_refs_list.push_back(rf);
             rf->in_sum_func= thd->lex->in_sum_func;
           }
+          /*
+            A reference is resolved to a nest level that's outer or the same as
+            the nest level of the enclosing set function : adjust the value of
+            max_arg_level for the function if it's needed.
+          */
           if (thd->lex->in_sum_func &&
-              thd->lex->in_sum_func->nest_level == 
-              thd->lex->current_select->nest_level)
+              thd->lex->in_sum_func->nest_level >= select->nest_level)
           {
             Item::Type ref_type= (*reference)->type();
             set_if_bigger(thd->lex->in_sum_func->max_arg_level,
@@ -4237,6 +4241,21 @@ enum_field_types Item::field_type() const
 }
 
 
+bool Item::is_datetime()
+{
+  switch (field_type())
+  {
+    case MYSQL_TYPE_DATE:
+    case MYSQL_TYPE_DATETIME:
+    case MYSQL_TYPE_TIMESTAMP:
+      return TRUE;
+    default:
+      break;
+  }
+  return FALSE;
+}
+
+
 /*
   Create a field to hold a string value from an item
 
@@ -5283,6 +5302,16 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
         thd->change_item_tree(reference, fld);
         mark_as_dependent(thd, last_checked_context->select_lex,
                           thd->lex->current_select, this, fld);
+        /*
+          A reference is resolved to a nest level that's outer or the same as
+          the nest level of the enclosing set function : adjust the value of
+          max_arg_level for the function if it's needed.
+        */
+        if (thd->lex->in_sum_func &&
+            thd->lex->in_sum_func->nest_level >= 
+            last_checked_context->select_lex->nest_level)
+          set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                        last_checked_context->select_lex->nest_level);
         return FALSE;
       }
       if (ref == 0)
@@ -5296,6 +5325,16 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
       DBUG_ASSERT(*ref && (*ref)->fixed);
       mark_as_dependent(thd, last_checked_context->select_lex,
                         context->select_lex, this, this);
+      /*
+        A reference is resolved to a nest level that's outer or the same as
+        the nest level of the enclosing set function : adjust the value of
+        max_arg_level for the function if it's needed.
+      */
+      if (thd->lex->in_sum_func &&
+          thd->lex->in_sum_func->nest_level >= 
+          last_checked_context->select_lex->nest_level)
+        set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                      last_checked_context->select_lex->nest_level);
     }
   }
 
@@ -6228,6 +6267,14 @@ void Item_cache_int::store(Item *item)
 }
 
 
+void Item_cache_int::store(Item *item, longlong val_arg)
+{
+  value= val_arg;
+  null_value= item->null_value;
+  unsigned_flag= item->unsigned_flag;
+}
+
+
 String *Item_cache_int::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item.h b/sql/item.h
index 747d79afda9..8b57f831cbd 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -942,6 +942,7 @@ public:
     representation is more precise than the string one).
   */
   virtual bool result_as_longlong() { return FALSE; }
+  bool is_datetime();
 };
 
 
@@ -2553,11 +2554,13 @@ public:
   Item_cache_int(): Item_cache(), value(0) {}
 
   void store(Item *item);
+  void store(Item *item, longlong val_arg);
   double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
   longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
   String* val_str(String *str);
   my_decimal *val_decimal(my_decimal *);
   enum Item_result result_type() const { return INT_RESULT; }
+  bool result_as_longlong() { return TRUE; }
 };
 
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 54b78cdf51c..982179a3bb2 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -436,7 +436,9 @@ void Item_bool_func2::fix_length_and_dec()
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !(arg_real_item->is_datetime() &&
+            args[1]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[1]))
         {
@@ -451,7 +453,9 @@ void Item_bool_func2::fix_length_and_dec()
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !(arg_real_item->is_datetime() &&
+            args[0]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[0]))
         {
@@ -570,6 +574,334 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
 }
 
 
+/*
+  Convert date provided in a string to the int representation.
+
+  SYNOPSIS
+    get_date_from_str()
+    thd              Thread handle
+    str              a string to convert
+    warn_type        type of the timestamp for issuing the warning
+    warn_name        field name for issuing the warning
+    error_arg  [out] TRUE if string isn't a DATETIME or clipping occur
+
+  DESCRIPTION
+    Convert date provided in the string str to the int representation.
+    if the string contains wrong date or doesn't contain it at all
+    then the warning is issued and TRUE returned in the error_arg argument.
+    The warn_type and the warn_name arguments are used as the name and the
+    type of the field when issuing the warning.
+
+  RETURN
+    converted value.
+*/
+
+static ulonglong
+get_date_from_str(THD *thd, String *str, timestamp_type warn_type,
+                  char *warn_name, bool *error_arg)
+{
+  ulonglong value= 0;
+  int error;
+  MYSQL_TIME l_time;
+  enum_mysql_timestamp_type ret;
+  *error_arg= TRUE;
+
+  ret= str_to_datetime(str->ptr(), str->length(), &l_time,
+                       (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+                        (thd->variables.sql_mode &
+                         (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+                       &error);
+  if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE))
+  {
+    value= TIME_to_ulonglong_datetime(&l_time);
+    *error_arg= FALSE;
+  }
+
+  if (error || *error_arg)
+  {
+    make_truncated_value_warning(thd, str->ptr(), str->length(), warn_type,
+                                 warn_name);
+    *error_arg= TRUE;
+  }
+  return value;
+}
+
+
+/*
+  Check whether compare_datetime() can be used to compare items.
+
+  SYNOPSIS
+    Arg_comparator::can_compare_as_dates()
+    a, b          [in]  items to be compared
+    const_value   [out] converted value of the string constant, if any
+
+  DESCRIPTION
+    Check several cases when the DATE/DATETIME comparator should be used.
+    The following cases are checked:
+      1. Both a and b is a DATE/DATETIME field/function returning string or
+         int result.
+      2. Only a or b is a DATE/DATETIME field/function returning string or
+         int result and the other item (b or a) is an item with string result.
+         If the second item is a constant one then it's checked to be
+         convertible to the DATE/DATETIME type. If the constant can't be
+         converted to a DATE/DATETIME then the compare_datetime() comparator
+         isn't used and the warning about wrong DATE/DATETIME value is issued.
+      In all other cases (date-[int|real|decimal]/[int|real|decimal]-date)
+      the comparison is handled by other comparators.
+    If the datetime comparator can be used and one the operands of the
+    comparison is a string constant that was successfully converted to a
+    DATE/DATETIME type then the result of the conversion is returned in the
+    const_value if it is provided.  If there is no constant or
+    compare_datetime() isn't applicable then the *const_value remains
+    unchanged.
+
+  RETURN
+    the found type of date comparison
+*/
+
+enum Arg_comparator::enum_date_cmp_type
+Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
+{
+  enum enum_date_cmp_type cmp_type= CMP_DATE_DFLT;
+  Item *str_arg= 0, *date_arg= 0;
+
+  if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM)
+    return CMP_DATE_DFLT;
+
+  if (a->is_datetime())
+  {
+    if (b->is_datetime())
+      cmp_type= CMP_DATE_WITH_DATE;
+    else if (b->result_type() == STRING_RESULT)
+    {
+      cmp_type= CMP_DATE_WITH_STR;
+      date_arg= a;
+      str_arg= b;
+    }
+  }
+  else if (b->is_datetime() && a->result_type() == STRING_RESULT)
+  {
+    cmp_type= CMP_STR_WITH_DATE;
+    date_arg= b;
+    str_arg= a;
+  }
+
+  if (cmp_type != CMP_DATE_DFLT)
+  {
+    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+    {
+      THD *thd= current_thd;
+      ulonglong value;
+      bool error;
+      String tmp, *str_val= 0;
+      timestamp_type t_type= (date_arg->field_type() == MYSQL_TYPE_DATE ?
+                              MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
+
+      str_val= str_arg->val_str(&tmp);
+      if (str_arg->null_value)
+        return CMP_DATE_DFLT;
+      value= get_date_from_str(thd, str_val, t_type, date_arg->name, &error);
+      if (error)
+        return CMP_DATE_DFLT;
+      if (const_value)
+        *const_value= value;
+    }
+  }
+  return cmp_type;
+}
+
+
+int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
+                                        Item **a1, Item **a2,
+                                        Item_result type)
+{
+  enum enum_date_cmp_type cmp_type;
+  ulonglong const_value;
+  a= a1;
+  b= a2;
+
+  if ((cmp_type= can_compare_as_dates(*a, *b, &const_value)))
+  {
+    thd= current_thd;
+    owner= owner_arg;
+    a_type= (*a)->field_type();
+    b_type= (*b)->field_type();
+    a_cache= 0;
+    b_cache= 0;
+
+    if (cmp_type != CMP_DATE_WITH_DATE &&
+        ((*b)->const_item() || (*a)->const_item()))
+    {
+      Item_cache_int *cache= new Item_cache_int();
+      /* Mark the cache as non-const to prevent re-caching. */
+      cache->set_used_tables(1);
+      if (!(*a)->is_datetime())
+      {
+        cache->store((*a), const_value);
+        a_cache= cache;
+        a= (Item **)&a_cache;
+      }
+      else
+      {
+        cache->store((*b), const_value);
+        b_cache= cache;
+        b= (Item **)&b_cache;
+      }
+    }
+    is_nulls_eq= test(owner && owner->functype() == Item_func::EQUAL_FUNC);
+    func= &Arg_comparator::compare_datetime;
+    return 0;
+  }
+  return set_compare_func(owner_arg, type);
+}
+
+
+void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1)
+{
+  thd= current_thd;
+  /* A caller will handle null values by itself. */
+  owner= NULL;
+  a= a1;
+  b= b1;
+  a_type= (*a)->field_type();
+  b_type= (*b)->field_type();
+  a_cache= 0;
+  b_cache= 0;
+  is_nulls_eq= FALSE;
+  func= &Arg_comparator::compare_datetime;
+}
+
+/*
+  Retrieves correct DATETIME value from given item.
+
+  SYNOPSIS
+    get_datetime_value()
+    thd                 thread handle
+    item_arg   [in/out] item to retrieve DATETIME value from
+    cache_arg  [in/out] pointer to place to store the caching item to
+    warn_item  [in]     item for issuing the conversion warning
+    is_null    [out]    TRUE <=> the item_arg is null
+
+  DESCRIPTION
+    Retrieves the correct DATETIME value from given item for comparison by the
+    compare_datetime() function.
+    If item's result can be compared as longlong then its int value is used
+    and its string value is used otherwise. Strings are always parsed and
+    converted to int values by the get_date_from_str() function.
+    This allows us to compare correctly string dates with missed insignificant
+    zeros. If an item is a constant one then its value is cached and it isn't
+    get parsed again. An Item_cache_int object is used for caching values. It
+    seamlessly substitutes the original item.  The cache item is marked as
+    non-constant to prevent re-caching it again.  In order to compare
+    correctly DATE and DATETIME items the result of the former are treated as
+    a DATETIME with zero time (00:00:00).
+
+  RETURN
+    obtained value
+*/
+
+static ulonglong
+get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+                   Item *warn_item, bool *is_null)
+{
+  ulonglong value= 0;
+  String buf, *str= 0;
+  Item *item= **item_arg;
+
+  if (item->result_as_longlong())
+  {
+    value= item->val_int();
+    *is_null= item->null_value;
+    if (item->field_type() == MYSQL_TYPE_DATE)
+      value*= 1000000L;
+  }
+  else
+  {
+    str= item->val_str(&buf);
+    *is_null= item->null_value;
+  }
+  if (*is_null)
+    return -1;
+  /*
+    Convert strings to the integer DATE/DATETIME representation.
+    Even if both dates provided in strings we can't compare them directly as
+    strings as there is no warranty that they are correct and do not miss
+    some insignificant zeros.
+  */
+  if (str)
+  {
+    bool error;
+    enum_field_types f_type= warn_item->field_type();
+    timestamp_type t_type= f_type ==
+      MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+    value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
+  }
+  if (item->const_item())
+  {
+    Item_cache_int *cache= new Item_cache_int();
+    /* Mark the cache as non-const to prevent re-caching. */
+    cache->set_used_tables(1);
+    cache->store(item, value);
+    *cache_arg= cache;
+    *item_arg= cache_arg;
+  }
+  return value;
+}
+
+/*
+  Compare items values as dates.
+
+  SYNOPSIS
+    Arg_comparator::compare_datetime()
+
+  DESCRIPTION
+    Compare items values as DATE/DATETIME for both EQUAL_FUNC and from other
+    comparison functions. The correct DATETIME values are obtained
+    with help of the get_datetime_value() function.
+
+  RETURN
+    If is_nulls_eq is TRUE:
+       1    if items are equal or both are null
+       0    otherwise
+    If is_nulls_eq is FALSE:
+      -1   a < b or one of items is null
+       0   a == b
+       1   a > b
+*/
+
+int Arg_comparator::compare_datetime()
+{
+  bool is_null= FALSE;
+  ulonglong a_value, b_value;
+
+  /* Get DATE/DATETIME value of the 'a' item. */
+  a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null);
+  if (!is_nulls_eq && is_null)
+  {
+    if (owner)
+      owner->null_value= 1;
+    return -1;
+  }
+
+  /* Get DATE/DATETIME value of the 'b' item. */
+  b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null);
+  if (is_null)
+  {
+    if (owner)
+      owner->null_value= is_nulls_eq ? 0 : 1;
+    return is_nulls_eq ? 1 : -1;
+  }
+
+  if (owner)
+    owner->null_value= 0;
+
+  /* Compare values. */
+  if (is_nulls_eq)
+    return (a_value == b_value);
+  return a_value < b_value ? -1 : (a_value > b_value ? 1 : 0);
+}
+
+
 int Arg_comparator::compare_string()
 {
   String *res1,*res2;
@@ -1419,8 +1751,11 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref)
 
 void Item_func_between::fix_length_and_dec()
 {
-   max_length= 1;
-   THD *thd= current_thd;
+  max_length= 1;
+  THD *thd= current_thd;
+  int i;
+  bool datetime_found= FALSE;
+  compare_as_dates= TRUE;
 
   /*
     As some compare functions are generated after sql_yacc,
@@ -1435,26 +1770,29 @@ void Item_func_between::fix_length_and_dec()
    return;
 
   /*
-    Make a special case of compare with date/time and longlong fields.
-    They are compared as integers, so for const item this time-consuming
-    conversion can be done only once, not for every single comparison
+    Detect the comparison of DATE/DATETIME items.
+    At least one of items should be a DATE/DATETIME item and other items
+    should return the STRING result.
   */
-  if (args[0]->real_item()->type() == FIELD_ITEM &&
-      thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
-      thd->lex->sql_command != SQLCOM_SHOW_CREATE)
+  for (i= 0; i < 3; i++)
   {
-    Field *field=((Item_field*) (args[0]->real_item()))->field;
-    if (field->can_be_compared_as_longlong())
+    if (args[i]->is_datetime())
     {
-      /*
-        The following can't be recoded with || as convert_constant_item
-        changes the argument
-      */
-      if (convert_constant_item(thd, field,&args[1]))
-	cmp_type=INT_RESULT;			// Works for all types.
-      if (convert_constant_item(thd, field,&args[2]))
-	cmp_type=INT_RESULT;			// Works for all types.
+      datetime_found= TRUE;
+      continue;
     }
+    if (args[i]->result_type() == STRING_RESULT)
+      continue;
+    compare_as_dates= FALSE;
+    break;
+  }
+  if (!datetime_found)
+    compare_as_dates= FALSE;
+
+  if (compare_as_dates)
+  {
+    ge_cmp.set_datetime_cmp_func(args, args + 1);
+    le_cmp.set_datetime_cmp_func(args, args + 2);
   }
 }
 
@@ -1462,7 +1800,27 @@ void Item_func_between::fix_length_and_dec()
 longlong Item_func_between::val_int()
 {						// ANSI BETWEEN
   DBUG_ASSERT(fixed == 1);
-  if (cmp_type == STRING_RESULT)
+  if (compare_as_dates)
+  {
+    int ge_res, le_res;
+
+    ge_res= ge_cmp.compare();
+    if ((null_value= args[0]->null_value))
+      return 0;
+    le_res= le_cmp.compare();
+
+    if (!args[1]->null_value && !args[2]->null_value)
+      return (longlong) ((ge_res >= 0 && le_res <=0) != negated);
+    else if (args[1]->null_value)
+    {
+      null_value= le_res > 0;			// not null if false range.
+    }
+    else
+    {
+      null_value= ge_res < 0;
+    }
+  }
+  else if (cmp_type == STRING_RESULT)
   {
     String *value,*a,*b;
     value=args[0]->val_str(&value0);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index f35b6a126ed..7aede7d2954 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -35,12 +35,19 @@ class Arg_comparator: public Sql_alloc
   Item_bool_func2 *owner;
   Arg_comparator *comparators;   // used only for compare_row()
   double precision;
-
+  /* Fields used in DATE/DATETIME comparison. */
+  THD *thd;
+  enum_field_types a_type, b_type; // Types of a and b items
+  Item *a_cache, *b_cache;         // Cached values of a and b items
+  bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
+  enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
+                            CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
 public:
   DTCollation cmp_collation;
 
-  Arg_comparator() {};
-  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
+  Arg_comparator(): thd(0), a_cache(0), b_cache(0) {};
+  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
+    a_cache(0), b_cache(0) {};
 
   int set_compare_func(Item_bool_func2 *owner, Item_result type);
   inline int set_compare_func(Item_bool_func2 *owner_arg)
@@ -48,14 +55,10 @@ public:
     return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
                                                      (*b)->result_type()));
   }
-  inline int set_cmp_func(Item_bool_func2 *owner_arg,
+  int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2,
-			  Item_result type)
-  {
-    a= a1;
-    b= a2;
-    return set_compare_func(owner_arg, type);
-  }
+			  Item_result type);
+
   inline int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2)
   {
@@ -83,7 +86,12 @@ public:
   int compare_e_row();           // compare args[0] & args[1]
   int compare_real_fixed();
   int compare_e_real_fixed();
+  int compare_datetime();        // compare args[0] & args[1] as DATETIMEs
+
+  static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
+                                                      ulonglong *const_val_arg);
 
+  void set_datetime_cmp_func(Item **a1, Item **b1);
   static arg_cmp_func comparator_matrix [5][2];
 
   friend class Item_func;
@@ -574,8 +582,12 @@ class Item_func_between :public Item_func_opt_neg
 public:
   Item_result cmp_type;
   String value0,value1,value2;
+  /* TRUE <=> arguments will be compared as dates. */
+  bool compare_as_dates;
+  /* Comparators used for DATE/DATETIME comparison. */
+  Arg_comparator ge_cmp, le_cmp;
   Item_func_between(Item *a, Item *b, Item *c)
-    :Item_func_opt_neg(a, b, c) {}
+    :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) {}
   longlong val_int();
   optimize_type select_optimize() const { return OPTIMIZE_KEY; }
   enum Functype functype() const   { return BETWEEN; }
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index f217a6ea953..2eced347650 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -175,13 +175,25 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
                MYF(0));
     return TRUE;
   }
-  if (in_sum_func && in_sum_func->nest_level == nest_level)
+  if (in_sum_func)
   {
     /*
       If the set function is nested adjust the value of
       max_sum_func_level for the nesting set function.
+      We take into account only enclosed set functions that are to be 
+      aggregated on the same level or above of the nest level of 
+      the enclosing set function.
+      But we must always pass up the max_sum_func_level because it is
+      the maximum nested level of all directly and indirectly enclosed
+      set functions. We must do that even for set functions that are
+      aggregated inside of their enclosing set function's nest level
+      because the enclosing function may contain another enclosing
+      function that is to be aggregated outside or on the same level
+      as its parent's nest level.
     */
-    set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+    if (in_sum_func->nest_level >= aggr_level)
+      set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+    set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
   }
   update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
diff --git a/sql/key.cc b/sql/key.cc
index faa7bf1f04b..19861cee134 100644
--- a/sql/key.cc
+++ b/sql/key.cc
@@ -139,29 +139,22 @@ void key_copy(byte *to_key, byte *from_record, KEY *key_info, uint key_length)
         key_length--;
       }
     }
-    if (key_part->key_part_flag & HA_BLOB_PART)
-    {
-      char *pos;
-      ulong blob_length= ((Field_blob*) key_part->field)->get_length();
-      key_length-= HA_KEY_BLOB_LENGTH;
-      ((Field_blob*) key_part->field)->get_ptr(&pos);
-      length=min(key_length, key_part->length);
-      set_if_smaller(blob_length, length);
-      int2store(to_key, (uint) blob_length);
-      to_key+= HA_KEY_BLOB_LENGTH;			// Skip length info
-      memcpy(to_key, pos, blob_length);
-    }
-    else if (key_part->key_part_flag & HA_VAR_LENGTH_PART)
+    if (key_part->key_part_flag & HA_BLOB_PART ||
+        key_part->key_part_flag & HA_VAR_LENGTH_PART)
     {
       key_length-= HA_KEY_BLOB_LENGTH;
       length= min(key_length, key_part->length);
-      key_part->field->get_key_image((char *) to_key, length, Field::itRAW);
+      key_part->field->get_key_image((char*) to_key, length, Field::itRAW);
       to_key+= HA_KEY_BLOB_LENGTH;
     }
     else
     {
       length= min(key_length, key_part->length);
-      memcpy(to_key, from_record + key_part->offset, (size_t) length);
+      Field *field= key_part->field;
+      CHARSET_INFO *cs= field->charset();
+      uint bytes= field->get_key_image((char*) to_key, length, Field::itRAW);
+      if (bytes < length)
+        cs->cset->fill(cs, (char*) to_key + bytes, length - bytes, ' ');
     }
     to_key+= length;
     key_length-= length;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0f428da1fdb..f26d6e5777b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5575,6 +5575,7 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
   */
   arena= thd->activate_stmt_arena_if_needed(&backup);
 
+  thd->lex->current_select->cur_pos_in_select_list= 0;
   while (wild_num && (item= it++))
   {
     if (item->type() == Item::FIELD_ITEM &&
@@ -5616,7 +5617,10 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
       }
       wild_num--;
     }
+    else
+      thd->lex->current_select->cur_pos_in_select_list++;
   }
+  thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS;
   if (arena)
   {
     /* make * substituting permanent */
@@ -6099,6 +6103,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
       }
       else
         thd->used_tables|= item->used_tables();
+      thd->lex->current_select->cur_pos_in_select_list++;
     }
     /*
       In case of stored tables, all fields are considered as used,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 14423c949e5..ca127297481 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5615,8 +5615,9 @@ static void add_not_null_conds(JOIN *join)
   for (uint i=join->const_tables ; i < join->tables ; i++)
   {
     JOIN_TAB *tab=join->join_tab+i;
-    if ((tab->type == JT_REF || tab->type == JT_REF_OR_NULL) &&
-         !tab->table->maybe_null)
+    if ((tab->type == JT_REF || tab->type == JT_EQ_REF || 
+         tab->type == JT_REF_OR_NULL) &&
+        !tab->table->maybe_null)
     {
       for (uint keypart= 0; keypart < tab->ref.key_parts; keypart++)
       {
@@ -8857,17 +8858,13 @@ static bool
 test_if_equality_guarantees_uniqueness(Item *l, Item *r)
 {
   return r->const_item() &&
-    /* elements must be of the same result type */
-    (r->result_type() == l->result_type() ||
-    /* or dates compared to longs */
-     (((l->type() == Item::FIELD_ITEM &&
-        ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
-       (l->type() == Item::FUNC_ITEM &&
-        ((Item_func *)l)->result_as_longlong())) &&
-      r->result_type() == INT_RESULT))
-    /* and must have the same collation if compared as strings */
-    && (l->result_type() != STRING_RESULT ||
-        l->collation.collation == r->collation.collation);
+    /* elements must be compared as dates */
+     (Arg_comparator::can_compare_as_dates(l, r, 0) ||
+      /* or of the same result type */
+      (r->result_type() == l->result_type() &&
+       /* and must have the same collation if compared as strings */
+       (l->result_type() != STRING_RESULT ||
+        l->collation.collation == r->collation.collation)));
 }
 
 /*
diff --git a/sql/table.cc b/sql/table.cc
index 39bdbb4cbb9..9be98eb14b9 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3552,7 +3552,16 @@ const char *Field_iterator_table::name()
 
 Item *Field_iterator_table::create_item(THD *thd)
 {
-  return new Item_field(thd, &thd->lex->current_select->context, *ptr);
+  SELECT_LEX *select= thd->lex->current_select;
+
+  Item_field *item= new Item_field(thd, &select->context, *ptr);
+  if (item && thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      !thd->lex->in_sum_func && select->cur_pos_in_select_list != UNDEF_POS)
+  {
+    select->non_agg_fields.push_back(item);
+    item->marker= select->cur_pos_in_select_list;
+  }
+  return item;
 }
 
 
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 1932f775a3d..035d70bfb3c 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -3520,26 +3520,28 @@ no_commit:
 			/* This call will update the counter according to the
 			value that was inserted in the table */
 
-			dict_table_autoinc_update(prebuilt->table, auto_inc);
-		}
-	}
-
-	/* A REPLACE command and LOAD DATA INFILE REPLACE handle a duplicate
-	key error themselves, and we must update the autoinc counter if we are
-	performing those statements. */
-
-	if (error == DB_DUPLICATE_KEY && auto_inc_used
-		&& (user_thd->lex->sql_command == SQLCOM_REPLACE
-			|| user_thd->lex->sql_command == SQLCOM_REPLACE_SELECT
-			|| (user_thd->lex->sql_command == SQLCOM_LOAD
-				&& user_thd->lex->duplicates == DUP_REPLACE))) {
-
-		auto_inc = table->next_number_field->val_int();
-
-		if (auto_inc != 0) {
-			dict_table_autoinc_update(prebuilt->table, auto_inc);
-		}
-	}
+            		dict_table_autoinc_update(prebuilt->table, auto_inc);
+          	}
+        }
+
+        /* A REPLACE command and LOAD DATA INFILE REPLACE handle a duplicate
+        key error themselves, and we must update the autoinc counter if we are
+        performing those statements. */
+
+        if (error == DB_DUPLICATE_KEY && auto_inc_used
+            && (user_thd->lex->sql_command == SQLCOM_REPLACE
+                || user_thd->lex->sql_command == SQLCOM_REPLACE_SELECT
+                || (user_thd->lex->sql_command == SQLCOM_INSERT
+                    && user_thd->lex->duplicates == DUP_UPDATE)
+                || (user_thd->lex->sql_command == SQLCOM_LOAD
+                    && user_thd->lex->duplicates == DUP_REPLACE))) {
+
+                auto_inc = table->next_number_field->val_int();
+
+                if (auto_inc != 0) {
+                        dict_table_autoinc_update(prebuilt->table, auto_inc);
+                }
+        }
 
 	innodb_srv_conc_exit_innodb(prebuilt->trx);
 
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 534f7a51e05..ae144b207cd 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -9055,7 +9055,9 @@ static void test_ts()
   int        rc, field_count;
   char       name;
   char query[MAX_TEST_QUERY_LENGTH];
-
+  const char *queries [3]= {"SELECT a, b, c FROM test_ts WHERE %c=?",
+                            "SELECT a, b, c FROM test_ts WHERE %c=?",
+                            "SELECT a, b, c FROM test_ts WHERE %c=CAST(? AS DATE)"};
   myheader("test_ts");
 
   rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_ts");
@@ -9127,7 +9129,7 @@ static void test_ts()
   {
     int row_count= 0;
 
-    sprintf(query, "SELECT a, b, c FROM test_ts WHERE %c=?", name);
+    sprintf(query, queries[field_count], name);
 
     if (!opt_silent)
       fprintf(stdout, "\n  %s", query);