bzr commit into mysql-5.1-bugteam branch (Matthias.Leich:2731) Bug#41776

[Matthias Leich]

#At file:///work2/5.1/mysql-5.1-bugteam-work2/

 2731 Matthias Leich	2009-01-09
      Fix for Bug#41776 type_date.test may fail if run around midnight
      + minor improvements
modified:
  mysql-test/r/type_date.result
  mysql-test/t/type_date.test

=== modified file 'mysql-test/r/type_date.result'
--- a/mysql-test/r/type_date.result	2008-01-10 10:37:54 +0000
+++ b/mysql-test/r/type_date.result	2009-01-09 20:48:42 +0000
@@ -65,12 +65,15 @@ CREATE TABLE t1(AFIELD INT);
 INSERT INTO t1 VALUES(1);
 CREATE TABLE t2(GMT  VARCHAR(32));
 INSERT INTO t2 VALUES('GMT-0800');
-SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)) FROM t1, t2 GROUP BY t1.AFIELD;
-DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT))
+SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
+FROM t1, t2 GROUP BY t1.AFIELD;
+DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
 Wed, 06 March 2002 10:11:12 GMT-0800
 INSERT INTO t1 VALUES(1);
-SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12",  CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)) FROM t1,t2 GROUP BY t1.AFIELD;
-DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT))	DATE_FORMAT("2002-03-06 10:11:12",  CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT))
+SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)),
+DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
+FROM t1,t2 GROUP BY t1.AFIELD;
+DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))	DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
 Wed, 06 March 2002 10:11:12 GMT-0800	Wed, 06 March 2002 10:11:12 GMT-0800
 drop table t1,t2;
 CREATE TABLE t1 (f1 time default NULL, f2 time default NULL);
@@ -155,7 +158,7 @@ str_to_date( '', a )
 0000-00-00 00:00:00
 NULL
 DROP TABLE t1;
-CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b));
+CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b));
 INSERT INTO t1 VALUES (DATE(NOW()), 1);
 SELECT COUNT(*) FROM t1 WHERE a = NOW();
 COUNT(*)

=== modified file 'mysql-test/t/type_date.test'
--- a/mysql-test/t/type_date.test	2008-01-10 10:37:54 +0000
+++ b/mysql-test/t/type_date.test	2009-01-09 20:48:42 +0000
@@ -62,7 +62,7 @@ drop table t1;
 # Test of date and not null
 #
 
-create table t1 (date date);  
+create table t1 (date date);
 insert into t1 values ("2000-08-10"),("2000-08-11");
 select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1;
 drop table t1;
@@ -75,9 +75,12 @@ CREATE TABLE t1(AFIELD INT);
 INSERT INTO t1 VALUES(1);
 CREATE TABLE t2(GMT  VARCHAR(32));
 INSERT INTO t2 VALUES('GMT-0800');
-SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)) FROM t1, t2 GROUP BY t1.AFIELD;
+SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
+FROM t1, t2 GROUP BY t1.AFIELD;
 INSERT INTO t1 VALUES(1);
-SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12",  CONCAT('%a, %d %M %Y %H:%i:%s ' ,  t2.GMT)) FROM t1,t2 GROUP BY t1.AFIELD;
+SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)),
+       DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
+FROM t1,t2 GROUP BY t1.AFIELD;
 drop table t1,t2;
 
 #
@@ -90,7 +93,7 @@ SELECT DATE_FORMAT(f1, "%l.%i %p") , DAT
 DROP TABLE t1;
 
 #
-# Bug 4937: different date -> string conversion when using SELECT ... UNION
+# Bug4937: different date -> string conversion when using SELECT ... UNION
 # and INSERT ... SELECT ... UNION
 #
 
@@ -141,7 +144,7 @@ select * from t1;
 drop table t1;
 
 #
-# Bug #30942: select str_to_date from derived table returns varying results
+# Bug#30942: select str_to_date from derived table returns varying results
 #
 CREATE TABLE t1 (
   a INT
@@ -155,10 +158,22 @@ DROP TABLE t1;
 
 
 #
-# Bug #31221: Optimizer incorrectly identifies impossible WHERE clause
+# Bug#31221: Optimizer incorrectly identifies impossible WHERE clause
 #
 
-CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b));
+CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b));
+## The current sub test could fail (difference to expected result) if we
+## have just reached midnight.
+## (Bug#41776 type_date.test may fail if run around midnight)
+## Therefore we sleep a bit if we are too close to midnight.
+## The complete test itself needs in average less than 1 second.
+## Therefore a time_distance to midnight of 5 seconds should be sufficient.
+if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`)
+{
+   # We are here when CURTIME() is between '23:59:56' and '23:59:59'.
+   # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'.
+   --real_sleep 5
+}
 INSERT INTO t1 VALUES (DATE(NOW()), 1);
 SELECT COUNT(*) FROM t1 WHERE a = NOW();
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
@@ -174,7 +189,7 @@ EXPLAIN SELECT COUNT(*) FROM t1 WHERE a
 DROP TABLE t1;
 
 #
-# Bug #28687: Search fails on '0000-00-00' date after sql_mode change
+# Bug#28687: Search fails on '0000-00-00' date after sql_mode change
 #
 
 CREATE TABLE t1 (a DATE);