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

13 files changed, 249 insertions, 25 deletions

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