MySQL Bugs: #29290: type_datetime.test failure in 5.1

Bug #29290	type_datetime.test failure in 5.1
Submitted:	22 Jun 2007 0:06	Modified:	21 Nov 2007 20:23
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Matthias Leich	CPU Architecture:	Any
Tags:	pbfail

Description:
Infrequent test failure in 5.1.  First appearance in PushBuild is in mysql-5.1-wl3867:

https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=mysql-5.1-wl3867&order=17

Apparently this happens when the test is run at midnight.  In this case, NOW() really is equivalent to CURRENT_DATE(), which the test doesn't like.

The failure looks like:

-------------------------------------------------------
*** r/type_datetime.result	Fri Jun 15 16:56:31 2007
--- r/type_datetime.reject	Sun Jun 17 00:00:00 2007
***************
*** 231,237 ****
  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');
--- 231,237 ----
  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()
! 0	0	0
  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');
***************
*** 297,302 ****
--- 297,303 ----
  f2
  2001-04-15 00:00:00
  SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
+ 1
  1
  drop table t1;
  create table t1 (f1 date);
-------------------------------------------------------

How to repeat:
https://intranet.mysql.com/secure/pushbuild/xref.pl?testname=type_datetime

Suggested fix:
Possible fixes:

Stop running tests in the middle of the night!

Store CURRENT_DATE() in a user variable, sleep for a little bit, and then test NOW() against the stored value.

Check if NOW() ends with 00:00:00; if so, sleep and test again.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36781

ChangeSet@1.2545, 2007-10-31 17:21:29+01:00, mleich@four.local.lan +1 -0
  Fix for
     Bug#29290 type_datetime.test failure in 5.1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36784

ChangeSet@1.2545, 2007-10-31 17:40:29+01:00, mleich@four.local.lan +1 -0
  Fix for
     Bug#29290 type_datetime.test failure in 5.1

Pushed to
mysql-5.0-build
mysql-5.1-build
mysql-6.0-build

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Test case change. No changelog entry needed.