Fixing sporading builtbot test failures happening at '00:00:00' sharp

Some tests relied on the fact that DATETIME->DATE conversion
always produce a truncation (with a warning). This is not the case
when the SQL statement is executed at current time '00:00:00' sharp.

Adding a new SET TIMESTAMP statements to make sure time is not '00:00:00'.

4 files changed, 17 insertions, 0 deletions

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 1a5a1d1c756..c945591fa07 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -429,6 +429,7 @@ select @a;
 #
 # BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed
 #
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-01 00:00:01');
 create table t1(a date,key(a));
 insert into t1 values ('2012-01-01'),('2012-02-02');
 explain
@@ -440,6 +441,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 1
 drop table t1;
+SET TIMESTAMP=DEFAULT;
 #
 # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
 # MDEV-9972 Least function retuns date in date time format
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 3468ff67b53..26e852f116c 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -57,6 +57,7 @@ select * from t1;
 t
 0000-00-00 00:00:00
 drop table t1;
+SET TIMESTAMP=UNIX_TIMESTAMP('2020-08-11 00:00:01');
 CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime);
 insert into t1 (b,c,d) values(now(),curtime(),now());
 Warnings:
@@ -65,6 +66,7 @@ select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
 date_format(a,"%Y-%m-%d")=b	right(a+0,6)=c+0	a=d+0
 1	1	1
 drop table t1;
+SET TIMESTAMP=DEFAULT;
 CREATE TABLE t1 (a datetime not null);
 insert into t1 values (0);
 select * from t1 where a is null;
@@ -298,8 +300,10 @@ f2	f3
 select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
 f2
 2001-04-15 00:00:00
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:01');
 SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
 1
+SET timestamp=DEFAULT;
 drop table t1;
 create table t1 (f1 date);
 insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index c86bc730392..91f4a8250f6 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -369,12 +369,18 @@ select @a;
 --echo #
 --echo # BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed
 --echo #
+
+# Set timestamp to make sure DATETIME->DATE truncation happens.
+# Otherwise, the warning would disappear at '00:00:00' sharp,
+# and a different execution plan would be chosen.
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-01 00:00:01');
 create table t1(a date,key(a));
 insert into t1 values ('2012-01-01'),('2012-02-02');
 explain
 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 drop table t1;
+SET TIMESTAMP=DEFAULT;
 
 --echo #
 --echo # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 447a5d4b192..f2ef5030a6a 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -32,10 +32,12 @@ drop table t1;
 # Test insert of now() and curtime()
 #
 
+SET TIMESTAMP=UNIX_TIMESTAMP('2020-08-11 00:00:01');
 CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime);
 insert into t1 (b,c,d) values(now(),curtime(),now());
 select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
 drop table t1;
+SET TIMESTAMP=DEFAULT;
 
 #
 # Test of datetime and not null
@@ -201,6 +203,7 @@ drop table t1;
 #
 # Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
 #
+
 create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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');
@@ -214,7 +217,9 @@ select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
 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";
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:01');
 SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
+SET timestamp=DEFAULT;
 drop table t1;
 
 #