summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--include/my_time.h3
-rw-r--r--mysql-test/include/type_hrtime.inc3
-rw-r--r--mysql-test/r/cast.result13
-rw-r--r--mysql-test/r/dyncol.result8
-rw-r--r--mysql-test/r/func_time.result36
-rw-r--r--mysql-test/r/old-mode.result80
-rw-r--r--mysql-test/r/temporal_literal.result34
-rw-r--r--mysql-test/r/timezone2.result10
-rw-r--r--mysql-test/r/type_datetime_hires.result6
-rw-r--r--mysql-test/r/type_time.result10
-rw-r--r--mysql-test/r/type_time_hires.result28
-rw-r--r--mysql-test/r/type_timestamp_hires.result14
-rw-r--r--mysql-test/suite/funcs_1/r/innodb_func_view.result38
-rw-r--r--mysql-test/suite/funcs_1/r/memory_func_view.result38
-rw-r--r--mysql-test/suite/funcs_1/r/myisam_func_view.result38
-rw-r--r--mysql-test/suite/funcs_1/views/func_view.inc4
-rw-r--r--mysql-test/t/cast.test3
-rw-r--r--mysql-test/t/dyncol.test2
-rw-r--r--mysql-test/t/func_time.test8
-rw-r--r--mysql-test/t/old-mode.test39
-rw-r--r--mysql-test/t/temporal_literal.test2
-rw-r--r--mysql-test/t/timezone2.test2
-rw-r--r--mysql-test/t/type_time.test4
-rw-r--r--sql-common/my_time.c7
-rw-r--r--sql/field.cc33
-rw-r--r--sql/item.cc67
-rw-r--r--sql/item.h2
-rw-r--r--sql/item_func.h2
-rw-r--r--sql/item_timefunc.cc82
-rw-r--r--sql/nt_servc.cc2
-rw-r--r--sql/sql_time.cc148
-rw-r--r--sql/sql_time.h31
-rw-r--r--tests/mysql_client_test.c16
33 files changed, 604 insertions, 209 deletions
diff --git a/include/my_time.h b/include/my_time.h
index cec1c66a97a..3c45d1d9235 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -77,6 +77,9 @@ extern uchar days_in_month[];
#define MYSQL_TIME_WARN_HAVE_WARNINGS(x) MY_TEST((x) & MYSQL_TIME_WARN_WARNINGS)
#define MYSQL_TIME_WARN_HAVE_NOTES(x) MY_TEST((x) & MYSQL_TIME_WARN_NOTES)
+/* Usefull constants */
+#define SECONDS_IN_24H 86400L
+
/* Limits for the TIME data type */
#define TIME_MAX_HOUR 838
#define TIME_MAX_MINUTE 59
diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc
index cd631f25632..5d847d72195 100644
--- a/mysql-test/include/type_hrtime.inc
+++ b/mysql-test/include/type_hrtime.inc
@@ -1,6 +1,8 @@
--source include/have_innodb.inc
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
+
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
@@ -126,3 +128,4 @@ select * from t2;
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 5e933914f5d..8ae61881c07 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -1,3 +1,4 @@
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select CAST(1-2 AS UNSIGNED);
CAST(1-2 AS UNSIGNED)
18446744073709551615
@@ -62,7 +63,7 @@ cast(12.444 as double)
12.444
select cast(cast("20:01:01" as time) as datetime);
cast(cast("20:01:01" as time) as datetime)
-0000-00-00 20:01:01
+2001-02-03 20:01:01
select cast(cast("8:46:06.23434" AS time) as decimal(32,10));
cast(cast("8:46:06.23434" AS time) as decimal(32,10))
84606.0000000000
@@ -764,7 +765,7 @@ cast(cast("2101-00-01 02:03:04" as datetime) as time)
02:03:04
SELECT CAST(CAST('20:05:05' AS TIME) as date);
CAST(CAST('20:05:05' AS TIME) as date)
-0000-00-00
+2001-02-03
set sql_mode= TRADITIONAL;
select cast("2101-00-01 02:03:04" as datetime);
cast("2101-00-01 02:03:04" as datetime)
@@ -778,9 +779,7 @@ Warnings:
Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04'
SELECT CAST(CAST('20:05:05' AS TIME) as date);
CAST(CAST('20:05:05' AS TIME) as date)
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '20:05:05'
+2001-02-03
set sql_mode=DEFAULT;
create table t1 (f1 time, f2 date, f3 datetime);
insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33');
@@ -790,9 +789,7 @@ cast(f1 as unsigned) cast(f2 as unsigned) cast(f3 as unsigned)
drop table t1;
SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY;
CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '0000-00-00'
+2001-02-04
SET SQL_MODE=ALLOW_INVALID_DATES;
SELECT DATE("foo");
DATE("foo")
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index efe519fe3f9..ca2130cd700 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -697,14 +697,14 @@ column_get(column_create(1, 0), 1 as datetime)
select column_get(column_create(1, "2001021"), 1 as datetime);
column_get(column_create(1, "2001021"), 1 as datetime)
2020-01-02 01:00:00
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime);
column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime)
-0000-00-00 08:46:06
+2001-02-03 08:46:06
select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime);
column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime)
-NULL
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-808:46:06'
+2000-12-31 07:13:53
+SET timestamp=DEFAULT;
set @@sql_mode="allow_invalid_dates";
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime);
column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime)
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index fab0ebe2ba7..a548d5c18f1 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1719,6 +1719,8 @@ create table t1(a time);
insert into t1 values ('00:00:00'),('00:01:00');
select 1 from t1 where 1 < some (select cast(a as datetime) from t1);
1
+1
+1
drop table t1;
select time('10:10:10') > 10;
time('10:10:10') > 10
@@ -1853,9 +1855,11 @@ Warnings:
Warning 1292 Incorrect datetime value: '0'
Warning 1292 Incorrect datetime value: '0'
drop table t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow');
convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow')
NULL
+SET timestamp=DEFAULT;
create table t1 (f1 integer, f2 date);
insert into t1 values (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05'),(6, '2011-05-06');
select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125);
@@ -2330,6 +2334,7 @@ SELECT * FROM t1;
TIMESTAMP('2001-01-01 00:00:00','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10')
2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 AS SELECT
TIMESTAMP('00:00:00','10:10:10'),
TIMESTAMP(TIME('00:00:00'),'10:10:10');
@@ -2339,8 +2344,9 @@ TIMESTAMP('00:00:00','10:10:10') datetime YES NULL
TIMESTAMP(TIME('00:00:00'),'10:10:10') datetime YES NULL
SELECT * FROM t1;
TIMESTAMP('00:00:00','10:10:10') TIMESTAMP(TIME('00:00:00'),'10:10:10')
-NULL NULL
+NULL 2001-02-03 10:10:10
DROP TABLE t1;
+SET timestamp=DEFAULT;
#
# MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
#
@@ -2350,6 +2356,7 @@ NULL
#
# MDEV-4857 Wrong result of HOUR('1 00:00:00')
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
HOUR('1 02:00:00') HOUR('26:00:00')
26 26
@@ -2361,7 +2368,7 @@ HOUR(TIME('1 02:00:00')) HOUR(TIME('26:00:00'))
26 26
SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
DAY(TIME('1 02:00:00')) DAY(TIME('26:00:00'))
-0 0
+4 4
SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
EXTRACT(HOUR FROM '1 02:00:00') EXTRACT(HOUR FROM '26:00:00')
2 2
@@ -2374,6 +2381,7 @@ EXTRACT(HOUR FROM TIME('1 02:00:00')) EXTRACT(HOUR FROM TIME('26:00:00'))
SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
EXTRACT(DAY FROM TIME('1 02:00:00')) EXTRACT(DAY FROM TIME('26:00:00'))
1 1
+SET timestamp=DEFAULT;
#
# MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.'
#
@@ -2382,13 +2390,11 @@ CREATE TABLE t1 (t TIME);
INSERT INTO t1 VALUES ('03:22:30'),('18:30:05');
SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1;
CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00')
-NULL
-NULL
+2014-02-26 06:59:59
+2014-02-26 06:59:59
Warnings:
Warning 1292 Truncated incorrect time value: '1296:00:00'
-Warning 1292 Incorrect datetime value: '838:59:59'
Warning 1292 Truncated incorrect time value: '1296:00:00'
-Warning 1292 Incorrect datetime value: '838:59:59'
SELECT GREATEST(t, CURRENT_DATE()) FROM t1;
GREATEST(t, CURRENT_DATE())
838:59:59
@@ -2446,30 +2452,32 @@ SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
2012-12-21 00:00:00
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 (a DATETIME, b TIME);
INSERT INTO t1 VALUES (NULL, '00:20:12');
SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
IF(1,ADDDATE(IFNULL(a,b),0),1)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
CAST(ADDDATE(IFNULL(a,b),0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1;
CAST(ADDDATE(COALESCE(a,b),0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
-0000-00-00 00:20:12
+2001-02-03 00:20:12
SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1;
IF(1,ADDTIME(IFNULL(a,b),0),1)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1;
CAST(ADDTIME(IFNULL(a,b),0) AS CHAR)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
CAST(ADDTIME(COALESCE(a,b),0) AS CHAR)
-NULL
+2001-02-03 00:20:12
SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR)
-NULL
+2001-02-03 00:20:12
DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/r/old-mode.result b/mysql-test/r/old-mode.result
index eec08d4d5c8..0b10067c19b 100644
--- a/mysql-test/r/old-mode.result
+++ b/mysql-test/r/old-mode.result
@@ -19,3 +19,83 @@ drop table t1,t2;
SHOW PROCESSLIST;
Id User Host db Command Time State Info
<Id> root <Host> test Query <Time> <State> SHOW PROCESSLIST
+#
+# MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with the SQL Standard)
+#
+SELECT CAST(TIME'-10:30:30' AS DATETIME);
+CAST(TIME'-10:30:30' AS DATETIME)
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:30:30'
+SELECT CAST(TIME'10:20:30' AS DATETIME);
+CAST(TIME'10:20:30' AS DATETIME)
+0000-00-00 10:20:30
+SELECT CAST(TIME'830:20:30' AS DATETIME);
+CAST(TIME'830:20:30' AS DATETIME)
+0000-01-03 14:20:30
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT * FROM t1;
+a
+0000-00-00 00:00:00
+0000-00-00 10:20:30
+0000-01-03 14:20:30
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'10:20:30');
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME'830:20:30');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT a, CAST(a AS DATETIME), TO_DAYS(a) FROM t1;
+a CAST(a AS DATETIME) TO_DAYS(a)
+-10:20:30 NULL NULL
+10:20:30 0000-00-00 10:20:30 NULL
+830:20:30 0000-01-03 14:20:30 NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:20:30'
+Warning 1264 Out of range value for column 'a' at row 1
+Warning 1264 Out of range value for column 'a' at row 2
+Warning 1264 Out of range value for column 'a' at row 3
+DROP TABLE t1;
+SELECT TO_DAYS(TIME'-10:20:30');
+TO_DAYS(TIME'-10:20:30')
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-10:20:30'
+SELECT TO_DAYS(TIME'10:20:30');
+TO_DAYS(TIME'10:20:30')
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '10:20:30'
+SELECT TO_DAYS(TIME'830:20:30');
+TO_DAYS(TIME'830:20:30')
+3
+CREATE TABLE t1 (a DATETIME, b TIME);
+INSERT INTO t1 VALUES (NULL, '00:20:12');
+INSERT INTO t1 VALUES (NULL, '-00:20:12');
+SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
+IF(1,ADDDATE(IFNULL(a,b),0),1)
+0000-00-00 00:20:12
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-00:20:12'
+DROP TABLE t1;
diff --git a/mysql-test/r/temporal_literal.result b/mysql-test/r/temporal_literal.result
index ea0421c0940..44525aae761 100644
--- a/mysql-test/r/temporal_literal.result
+++ b/mysql-test/r/temporal_literal.result
@@ -371,49 +371,35 @@ DROP TABLE t1;
#
# TIME literals in no-zero date context
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT TO_DAYS(TIME'00:00:00');
TO_DAYS(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+730884
SELECT TO_SECONDS(TIME'00:00:00');
TO_SECONDS(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+63148377600
SELECT DAYOFYEAR(TIME'00:00:00');
DAYOFYEAR(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+34
SELECT WEEK(TIME'00:00:00');
WEEK(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+4
SELECT YEARWEEK(TIME'00:00:00');
YEARWEEK(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+200104
SELECT WEEKDAY(TIME'00:00:00');
WEEKDAY(TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+5
SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00');
CONVERT_TZ(TIME'00:00:00','+00:00','+01:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 01:00:00
SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR);
DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR)
01:00:00
SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00');
TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+0
+SET timestamp=DEFAULT;
#
# Testing Item_func::fix_fields()
#
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index 0ac4ddca277..096e996bffb 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -315,16 +315,14 @@ End of 5.1 tests
#
# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
+SET timestamp=DEFAULT;
#
# MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ
#
diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result
index 203e45b86cb..61c8001d98e 100644
--- a/mysql-test/r/type_datetime_hires.result
+++ b/mysql-test/r/type_datetime_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a datetime(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -197,7 +198,7 @@ bigint_f5_datetime 20101112111417
varchar_f6_datetime 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6);
select * from t1;
-time4_f0_datetime 0000-00-00 11:14:17
+time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.766
@@ -207,7 +208,7 @@ varchar_f6_datetime 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
-time4_f0_datetime 0000-00-00 11:14:17
+time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.765
@@ -262,6 +263,7 @@ a b
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
CREATE TABLE t1 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index ef02368e375..00a7a9b783e 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -144,12 +144,14 @@ End of 5.0 tests
#
# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
TIMESTAMP(f1,'1')
-NULL
+2001-02-03 23:38:58
DROP TABLE t1;
+SET timestamp=DEFAULT;
End of 5.1 tests
create table t1 (a time);
insert t1 values (-131415);
@@ -177,11 +179,11 @@ drop table t1;
#
# MDEV-4634 Crash in CONVERT_TZ
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5');
CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5')
-NULL
-Warnings:
-Warning 1292 Incorrect datetime value: '00:00:00'
+2001-02-03 07:05:00
+SET timestamp=DEFAULT;
#
# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
#
diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result
index bf54434a04c..2239ecfcaaa 100644
--- a/mysql-test/r/type_time_hires.result
+++ b/mysql-test/r/type_time_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a time(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -194,8 +195,8 @@ f5_time 11:14:17.76543
f6_time 11:14:17.765432
select cast(f0_time as time(4)) time4_f0_time, cast(f1_time as datetime(3)) datetime3_f1_time, cast(f2_time as date) date_f2_time, cast(f4_time as double) double_f3_time, cast(f4_time as decimal(40,5)) decimal5_f4_time, cast(f5_time as signed) bigint_f5_time, cast(f6_time as char(255)) varchar_f6_time from t1;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.7654
decimal5_f4_time 111417.76540
bigint_f5_time 111417
@@ -208,8 +209,8 @@ Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t2;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
@@ -221,13 +222,20 @@ Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17.0000
-datetime3_f1_time 0000-00-00 11:14:17.700
-date_f2_time 0000-00-00
+datetime3_f1_time 2001-02-03 11:14:17.700
+date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
varchar_f6_time 11:14:17.765432
alter table t1 modify time4_f0_time time(0), modify datetime3_f1_time time(1), modify date_f2_time time(2), modify double_f3_time time(3), modify decimal5_f4_time time(4), modify bigint_f5_time time(5), modify varchar_f6_time time(6);
+Warnings:
+Level Note
+Code 1265
+Message Data truncated for column 'datetime3_f1_time' at row 1
+Level Note
+Code 1265
+Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
@@ -238,6 +246,13 @@ bigint_f5_time 11:14:17.00000
varchar_f6_time 11:14:17.765432
delete from t1;
insert t1 select * from t2;
+Warnings:
+Level Note
+Code 1265
+Message Data truncated for column 'datetime3_f1_time' at row 1
+Level Note
+Code 1265
+Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
@@ -301,6 +316,7 @@ a b
04:05:06.000000 04:05:06.789100
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
create table t1 (a time(4) not null, key(a));
insert into t1 values ('1:2:3.001'),('1:2:3'), ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
select * from t1 order by a;
diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result
index cc2cb6a403d..dc1cbb377f9 100644
--- a/mysql-test/r/type_timestamp_hires.result
+++ b/mysql-test/r/type_timestamp_hires.result
@@ -1,3 +1,4 @@
+SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a timestamp(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
@@ -196,12 +197,8 @@ decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_timestamp timestamp(0), modify datetime3_f1_timestamp timestamp(1), modify date_f2_timestamp timestamp(2), modify double_f3_timestamp timestamp(3), modify decimal5_f4_timestamp timestamp(4), modify bigint_f5_timestamp timestamp(5), modify varchar_f6_timestamp timestamp(6);
-Warnings:
-Level Warning
-Code 1265
-Message Data truncated for column 'time4_f0_timestamp' at row 1
select * from t1;
-time4_f0_timestamp 0000-00-00 00:00:00
+time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.766
@@ -210,12 +207,8 @@ bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
-Warnings:
-Level Warning
-Code 1265
-Message Data truncated for column 'time4_f0_timestamp' at row 1
select * from t1;
-time4_f0_timestamp 0000-00-00 00:00:00
+time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.765
@@ -270,6 +263,7 @@ a b
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
+SET timestamp=DEFAULT;
set time_zone='+03:00';
set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456;
create table t1 (a timestamp(5));
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index 9cf63fb355c..2b98bc704d5 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -1,3 +1,4 @@
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1_values
@@ -3936,12 +3937,10 @@ my_time, id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as datetime) AS `CAST(my_time AS DATETIME)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3950,12 +3949,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL) order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
DROP VIEW v1;
@@ -4302,10 +4299,10 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4314,10 +4311,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
DROP VIEW v1;
@@ -5280,3 +5277,4 @@ DROP VIEW v1;
DROP TABLE t1_selects, t1_modes, t1_values;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index 5d12796c46e..43516edc70a 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -1,4 +1,5 @@
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1_values
@@ -3937,12 +3938,10 @@ my_time, id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as datetime) AS `CAST(my_time AS DATETIME)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3951,12 +3950,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL) order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
DROP VIEW v1;
@@ -4303,10 +4300,10 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4315,10 +4312,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
DROP VIEW v1;
@@ -5281,3 +5278,4 @@ DROP VIEW v1;
DROP TABLE t1_selects, t1_modes, t1_values;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index 5d12796c46e..43516edc70a 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -1,4 +1,5 @@
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1_values
@@ -3937,12 +3938,10 @@ my_time, id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as datetime) AS `CAST(my_time AS DATETIME)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3951,12 +3950,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 46 OR select_id IS NULL) order by id;
CAST(my_time AS DATETIME) my_time id
NULL NULL 1
-NULL -838:59:59 2
-0000-01-03 22:59:59 838:59:59 3
-0000-00-00 13:00:00 13:00:00 4
-0000-00-00 10:00:00 10:00:00 5
-Warnings:
-Warning 1292 Truncated incorrect datetime value: '-838:59:59'
+2000-12-30 01:00:01 -838:59:59 2
+2001-03-09 22:59:59 838:59:59 3
+2001-02-03 13:00:00 13:00:00 4
+2001-02-03 10:00:00 10:00:00 5
DROP VIEW v1;
@@ -4303,10 +4300,10 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4315,10 +4312,10 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
-0000-00-00 13:00:00 4
-0000-00-00 10:00:00 5
+2000-12-30 -838:59:59 2
+2001-03-09 838:59:59 3
+2001-02-03 13:00:00 4
+2001-02-03 10:00:00 5
DROP VIEW v1;
@@ -5281,3 +5278,4 @@ DROP VIEW v1;
DROP TABLE t1_selects, t1_modes, t1_values;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/funcs_1/views/func_view.inc b/mysql-test/suite/funcs_1/views/func_view.inc
index 1dba96f6901..c26f21945c3 100644
--- a/mysql-test/suite/funcs_1/views/func_view.inc
+++ b/mysql-test/suite/funcs_1/views/func_view.inc
@@ -151,6 +151,8 @@
#
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
+
--disable_warnings
DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
DROP VIEW IF EXISTS v1;
@@ -1359,3 +1361,5 @@ while ($select_id)
--enable_ps_protocol
DROP TABLE t1_selects, t1_modes, t1_values;
+
+SET timestamp=DEFAULT;
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 008610569e2..b6c37cacd8a 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -2,6 +2,9 @@
# Test of cast function
#
+# For TIME->DATETIME conversion
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
+
select CAST(1-2 AS UNSIGNED);
select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
select CAST('10 ' as unsigned integer);
diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test
index 4fba43b2cce..39070cc90d7 100644
--- a/mysql-test/t/dyncol.test
+++ b/mysql-test/t/dyncol.test
@@ -248,8 +248,10 @@ select column_get(column_create(1, "20010203"), 1 as datetime);
select column_get(column_create(1, 0), 1 as datetime);
select column_get(column_create(1, "2001021"), 1 as datetime);
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime);
select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime);
+SET timestamp=DEFAULT;
set @@sql_mode="allow_invalid_dates";
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime);
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index ee5292a5ba8..c889dec927e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1103,7 +1103,9 @@ drop table t1;
#
# lp:731815 Crash/valgrind warning Item::send with 5.1-micro
#
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow');
+SET timestamp=DEFAULT;
#
# lp:736370 Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micr
@@ -1425,12 +1427,14 @@ SHOW COLUMNS FROM t1;
SELECT * FROM t1;
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 AS SELECT
TIMESTAMP('00:00:00','10:10:10'),
TIMESTAMP(TIME('00:00:00'),'10:10:10');
SHOW COLUMNS FROM t1;
SELECT * FROM t1;
DROP TABLE t1;
+SET timestamp=DEFAULT;
--echo #
--echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
@@ -1440,6 +1444,7 @@ SELECT MAKETIME(0, 0, -0.1);
--echo #
--echo # MDEV-4857 Wrong result of HOUR('1 00:00:00')
--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
@@ -1449,6 +1454,7 @@ SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
+SET timestamp=DEFAULT;
--echo #
--echo # MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.'
@@ -1492,6 +1498,7 @@ SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
DROP TABLE t1;
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1 (a DATETIME, b TIME);
INSERT INTO t1 VALUES (NULL, '00:20:12');
SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
@@ -1503,3 +1510,4 @@ SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1;
SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
DROP TABLE t1;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/t/old-mode.test b/mysql-test/t/old-mode.test
index 182c166ebbc..56916aefd77 100644
--- a/mysql-test/t/old-mode.test
+++ b/mysql-test/t/old-mode.test
@@ -25,3 +25,42 @@ drop table t1,t2;
--replace_result "Writing to net" "NULL"
--replace_regex /localhost[:0-9]*/localhost/
SHOW PROCESSLIST;
+
+--echo #
+--echo # MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with the SQL Standard)
+--echo #
+# Checking that "mysqld --old" exposes the 5.5 conversion behaviour
+SELECT CAST(TIME'-10:30:30' AS DATETIME);
+SELECT CAST(TIME'10:20:30' AS DATETIME);
+SELECT CAST(TIME'830:20:30' AS DATETIME);
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT INTO t1 VALUES (TIME'10:20:30');
+INSERT INTO t1 VALUES (TIME'830:20:30');
+SELECT a, CAST(a AS DATETIME), TO_DAYS(a) FROM t1;
+DROP TABLE t1;
+# Note, it was actually a bug that TO_DAYS('830:20:30') returned NULL
+# for a column, while 3 for an expression. We won't fix this,
+# it's "mysqld --old" anyway.
+SELECT TO_DAYS(TIME'-10:20:30');
+SELECT TO_DAYS(TIME'10:20:30');
+SELECT TO_DAYS(TIME'830:20:30');
+
+# This is to cover Item_temporal_hybrid_func::fix_temporal_type in --old mode:
+CREATE TABLE t1 (a DATETIME, b TIME);
+INSERT INTO t1 VALUES (NULL, '00:20:12');
+INSERT INTO t1 VALUES (NULL, '-00:20:12');
+SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/temporal_literal.test b/mysql-test/t/temporal_literal.test
index a8380fd95da..4bb7eb8d609 100644
--- a/mysql-test/t/temporal_literal.test
+++ b/mysql-test/t/temporal_literal.test
@@ -195,6 +195,7 @@ DROP TABLE t1;
--echo #
--echo # TIME literals in no-zero date context
--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT TO_DAYS(TIME'00:00:00');
SELECT TO_SECONDS(TIME'00:00:00');
SELECT DAYOFYEAR(TIME'00:00:00');
@@ -204,6 +205,7 @@ SELECT WEEKDAY(TIME'00:00:00');
SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00');
SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR);
SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00');
+SET timestamp=DEFAULT;
--echo #
--echo # Testing Item_func::fix_fields()
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 2d61c7631f2..7a38610ad95 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -294,8 +294,10 @@ DROP TABLE t1;
--echo # MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
+SET timestamp=DEFAULT;
--echo #
--echo # MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 9ecd5d22a81..1daeec03a08 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -94,10 +94,12 @@ DROP TABLE t1;
--echo # Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
DROP TABLE t1;
+SET timestamp=DEFAULT;
--echo End of 5.1 tests
@@ -126,7 +128,9 @@ drop table t1;
--echo #
--echo # MDEV-4634 Crash in CONVERT_TZ
--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5');
+SET timestamp=DEFAULT;
--echo #
--echo # MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index 429506f4034..0942544d0be 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -81,6 +81,8 @@ uint calc_days_in_year(uint year)
my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
ulonglong flags, int *was_cut)
{
+ if (ltime->time_type == MYSQL_TIMESTAMP_TIME)
+ return FALSE;
if (not_zero_date)
{
if (((flags & TIME_NO_ZERO_IN_DATE) &&
@@ -917,7 +919,8 @@ my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, uint *error_code)
#endif
tmp= (time_t) (((calc_daynr((uint) t->year, (uint) t->month, (uint) t->day) -
- (long) days_at_timestart)*86400L + (long) t->hour*3600L +
+ (long) days_at_timestart) * SECONDS_IN_24H +
+ (long) t->hour*3600L +
(long) (t->minute*60 + t->second)) + (time_t) my_time_zone -
3600);
@@ -976,7 +979,7 @@ my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, uint *error_code)
/* shift back, if we were dealing with boundary dates */
- tmp+= shift*86400L;
+ tmp+= shift * SECONDS_IN_24H;
/*
This is possible for dates, which slightly exceed boundaries.
diff --git a/sql/field.cc b/sql/field.cc
index 9ec0e4d3089..922c9aba6c5 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4636,14 +4636,24 @@ int Field_timestamp::store_TIME_with_warning(THD *thd, MYSQL_TIME *l_time,
}
+static bool
+copy_or_convert_to_datetime(THD *thd, const MYSQL_TIME *from, MYSQL_TIME *to)
+{
+ if (from->time_type == MYSQL_TIMESTAMP_TIME)
+ return time_to_datetime(thd, from, to);
+ *to= *from;
+ return false;
+}
+
+
int Field_timestamp::store_time_dec(MYSQL_TIME *ltime, uint dec)
{
int unused;
- MYSQL_TIME l_time= *ltime;
ErrConvTime str(ltime);
THD *thd= get_thd();
-
- bool valid= !check_date(&l_time, pack_time(&l_time) != 0,
+ MYSQL_TIME l_time;
+ bool valid= !copy_or_convert_to_datetime(thd, ltime, &l_time) &&
+ !check_date(&l_time, pack_time(&l_time) != 0,
(thd->variables.sql_mode & MODE_NO_ZERO_DATE) |
MODE_NO_ZERO_IN_DATE, &unused);
@@ -5201,15 +5211,28 @@ int Field_temporal_with_date::store(longlong nr, bool unsigned_val)
int Field_temporal_with_date::store_time_dec(MYSQL_TIME *ltime, uint dec)
{
- int error = 0, have_smth_to_conv= 1;
- MYSQL_TIME l_time= *ltime;
+ int error= 0, have_smth_to_conv= 1;
ErrConvTime str(ltime);
+ MYSQL_TIME l_time;
+
+ if (copy_or_convert_to_datetime(get_thd(), ltime, &l_time))
+ {
+ /*
+ Set have_smth_to_conv and error in a way to have
+ store_TIME_with_warning do bzero().
+ */
+ have_smth_to_conv= false;
+ error= MYSQL_TIME_WARN_OUT_OF_RANGE;
+ goto store;
+ }
+
/*
We don't perform range checking here since values stored in TIME
structure always fit into DATETIME range.
*/
have_smth_to_conv= !check_date(&l_time, pack_time(&l_time) != 0,
sql_mode_for_dates(current_thd), &error);
+store:
return store_TIME_with_warning(&l_time, &str, error, have_smth_to_conv);
}
diff --git a/sql/item.cc b/sql/item.cc
index 7901f1186d8..1c81fda9c63 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -234,6 +234,35 @@ bool Item::val_bool()
}
+/**
+ Get date/time/datetime.
+ Optionally extend TIME result to DATETIME.
+*/
+bool Item::get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate)
+{
+ /*
+ Some TIME type items return error when trying to do get_date()
+ without TIME_TIME_ONLY set (e.g. Item_field for Field_time).
+ In the SQL standard time->datetime conversion mode we add TIME_TIME_ONLY.
+ In the legacy time->datetime conversion mode we do not add TIME_TIME_ONLY
+ and leave it to get_date() to check date.
+ */
+ ulonglong time_flag= (field_type() == MYSQL_TYPE_TIME &&
+ !current_thd->variables.old_mode) ? TIME_TIME_ONLY : 0;
+ if (get_date(ltime, fuzzydate | time_flag))
+ return true;
+ if (ltime->time_type == MYSQL_TIMESTAMP_TIME &&
+ !(fuzzydate & TIME_TIME_ONLY))
+ {
+ MYSQL_TIME tmp;
+ if (time_to_datetime_with_warn(current_thd, ltime, &tmp, fuzzydate))
+ return null_value= true;
+ *ltime= tmp;
+ }
+ return false;
+}
+
+
/*
For the items which don't have its own fast val_str_ascii()
implementation we provide a generic slower version,
@@ -8779,6 +8808,25 @@ int stored_field_cmp_to_item(THD *thd, Field *field, Item *item)
{
Item_result res_type=item_cmp_type(field->result_type(),
item->result_type());
+ /*
+ We have to check field->cmp_type() instead of res_type,
+ as result_type() - and thus res_type - can never be TIME_RESULT (yet).
+ */
+ if (field->cmp_type() == TIME_RESULT)
+ {
+ MYSQL_TIME field_time, item_time;
+ if (field->type() == MYSQL_TYPE_TIME)
+ {
+ field->get_time(&field_time);
+ item->get_time(&item_time);
+ }
+ else
+ {
+ field->get_date(&field_time, TIME_INVALID_DATES);
+ item->get_date(&item_time, TIME_INVALID_DATES);
+ }
+ return my_time_compare(&field_time, &item_time);
+ }
if (res_type == STRING_RESULT)
{
char item_buff[MAX_FIELD_WIDTH];
@@ -8829,25 +8877,6 @@ int stored_field_cmp_to_item(THD *thd, Field *field, Item *item)
return my_decimal_cmp(field_val, item_val);
}
/*
- We have to check field->cmp_type() instead of res_type,
- as result_type() - and thus res_type - can never be TIME_RESULT (yet).
- */
- if (field->cmp_type() == TIME_RESULT)
- {
- MYSQL_TIME field_time, item_time;
- if (field->type() == MYSQL_TYPE_TIME)
- {
- field->get_time(&field_time);
- item->get_time(&item_time);
- }
- else
- {
- field->get_date(&field_time, TIME_INVALID_DATES);
- item->get_date(&item_time, TIME_INVALID_DATES);
- }
- return my_time_compare(&field_time, &item_time);
- }
- /*
The patch for Bug#13463415 started using this function for comparing
BIGINTs. That uncovered a bug in Visual Studio 32bit optimized mode.
Prefixing the auto variables with volatile fixes the problem....
diff --git a/sql/item.h b/sql/item.h
index 1faed26e1ee..e3ddf56511e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1075,6 +1075,8 @@ public:
virtual bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
bool get_time(MYSQL_TIME *ltime)
{ return get_date(ltime, TIME_TIME_ONLY | TIME_INVALID_DATES); }
+ // Get date with automatic TIME->DATETIME conversion
+ bool get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate);
bool get_seconds(ulonglong *sec, ulong *sec_part);
virtual bool get_date_result(MYSQL_TIME *ltime, ulonglong fuzzydate)
{ return get_date(ltime,fuzzydate); }
diff --git a/sql/item_func.h b/sql/item_func.h
index c1a92573eec..69abecc5f39 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -160,7 +160,7 @@ public:
void count_decimal_length();
inline bool get_arg0_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
{
- return (null_value=args[0]->get_date(ltime, fuzzy_date));
+ return (null_value=args[0]->get_date_with_conversion(ltime, fuzzy_date));
}
void count_datetime_length(Item **item, uint nitems);
bool count_string_result_length(enum_field_types field_type,
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index b76474e6346..4d261e7a7d9 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1484,27 +1484,52 @@ bool Item_temporal_hybrid_func::fix_temporal_type(MYSQL_TIME *ltime)
{
if (ltime->time_type < 0) /* MYSQL_TIMESTAMP_NONE, MYSQL_TIMESTAMP_ERROR */
return false;
+
+ if (ltime->time_type != MYSQL_TIMESTAMP_TIME)
+ goto date_or_datetime_value;
+
+ /* Convert TIME to DATE or DATETIME */
switch (field_type())
{
+ case MYSQL_TYPE_DATE:
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
+ {
+ MYSQL_TIME tmp;
+ if (time_to_datetime_with_warn(current_thd, ltime, &tmp, 0))
+ return (null_value= true);
+ *ltime= tmp;
+ if (field_type() == MYSQL_TYPE_DATE)
+ datetime_to_date(ltime);
+ return false;
+ }
case MYSQL_TYPE_TIME:
- ltime->year= ltime->month= ltime->day= 0;
- ltime->time_type= MYSQL_TIMESTAMP_TIME;
+ case MYSQL_TYPE_STRING: /* DATE_ADD, ADDTIME can return VARCHAR */
+ return false;
+ default:
+ DBUG_ASSERT(0);
+ return (null_value= true);
+ }
+
+date_or_datetime_value:
+ /* Convert DATE or DATETIME to TIME, DATE, or DATETIME */
+ switch (field_type())
+ {
+ case MYSQL_TYPE_TIME:
+ datetime_to_time(ltime);
return false;
case MYSQL_TYPE_DATETIME:
case MYSQL_TYPE_TIMESTAMP:
- ltime->neg= 0;
- ltime->time_type= MYSQL_TIMESTAMP_DATETIME;
+ date_to_datetime(ltime);
return false;
case MYSQL_TYPE_DATE:
- ltime->neg= 0;
- ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
- ltime->time_type= MYSQL_TIMESTAMP_DATE;
+ datetime_to_date(ltime);
return false;
case MYSQL_TYPE_STRING: /* DATE_ADD, ADDTIME can return VARCHAR */
return false;
default:
DBUG_ASSERT(0);
- return true;
+ return (null_value= true);
}
return false;
}
@@ -2190,8 +2215,10 @@ longlong Item_extract::val_int()
long neg;
int is_time_flag = date_value ? 0 : TIME_TIME_ONLY;
- if (get_arg0_date(&ltime, is_time_flag))
+ // Not using get_arg0_date to avoid automatic TIME to DATETIME conversion
+ if ((null_value= args[0]->get_date(&ltime, is_time_flag)))
return 0;
+
neg= ltime.neg ? -1 : 1;
DBUG_ASSERT(ltime.time_type != MYSQL_TIMESTAMP_TIME || ltime.day == 0);
@@ -2512,26 +2539,7 @@ bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
if (decimals < TIME_SECOND_PART_DIGITS)
my_time_trunc(ltime, decimals);
- /*
- ltime is valid MYSQL_TYPE_TIME (according to fuzzy_date).
- But not every valid TIME value is a valid DATETIME value!
- */
- if (ltime->time_type == MYSQL_TIMESTAMP_TIME)
- {
- if (ltime->neg)
- {
- ErrConvTime str(ltime);
- make_truncated_value_warning(current_thd, Sql_condition::WARN_LEVEL_WARN,
- &str, MYSQL_TIMESTAMP_DATETIME, 0);
- return (null_value= 1);
- }
-
- uint day= ltime->hour/24;
- ltime->hour %= 24;
- ltime->month= day / 31;
- ltime->day= day % 31;
- }
-
+ DBUG_ASSERT(ltime->time_type != MYSQL_TIMESTAMP_TIME);
ltime->time_type= MYSQL_TIMESTAMP_DATETIME;
return 0;
}
@@ -2665,9 +2673,9 @@ bool Item_func_add_time::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
if (!is_time && ltime->neg)
return (null_value= 1);
- days= (long)(seconds/86400L);
+ days= (long) (seconds / SECONDS_IN_24H);
- calc_time_from_sec(ltime, (long)(seconds%86400L), microseconds);
+ calc_time_from_sec(ltime, (long)(seconds % SECONDS_IN_24H), microseconds);
ltime->time_type= is_time ? MYSQL_TIMESTAMP_TIME : MYSQL_TIMESTAMP_DATETIME;
@@ -2850,8 +2858,12 @@ longlong Item_func_timestamp_diff::val_int()
int neg= 1;
null_value= 0;
- if (args[0]->get_date(&ltime1, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE) ||
- args[1]->get_date(&ltime2, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
+ if (args[0]->get_date_with_conversion(&ltime1,
+ TIME_NO_ZERO_DATE |
+ TIME_NO_ZERO_IN_DATE) ||
+ args[1]->get_date_with_conversion(&ltime2,
+ TIME_NO_ZERO_DATE |
+ TIME_NO_ZERO_IN_DATE))
goto null_date;
if (calc_time_diff(&ltime2,&ltime1, 1,
@@ -2921,9 +2933,9 @@ longlong Item_func_timestamp_diff::val_int()
case INTERVAL_MONTH:
return months*neg;
case INTERVAL_WEEK:
- return seconds/86400L/7L*neg;
+ return seconds / SECONDS_IN_24H / 7L * neg;
case INTERVAL_DAY:
- return seconds/86400L*neg;
+ return seconds / SECONDS_IN_24H * neg;
case INTERVAL_HOUR:
return seconds/3600L*neg;
case INTERVAL_MINUTE:
diff --git a/sql/nt_servc.cc b/sql/nt_servc.cc
index d6a8eac7ed5..c81bcef0316 100644
--- a/sql/nt_servc.cc
+++ b/sql/nt_servc.cc
@@ -33,7 +33,7 @@ NTService::NTService()
//time-out variables
nStartTimeOut = 15000;
- nStopTimeOut = 86400000;
+ nStopTimeOut = SECONDS_IN_24H * 1000;
nPauseTimeOut = 5000;
nResumeTimeOut = 5000;
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index c906f9e76d7..06098094505 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -1036,8 +1036,8 @@ null_date:
*/
bool
-calc_time_diff(MYSQL_TIME *l_time1, MYSQL_TIME *l_time2, int l_sign, longlong *seconds_out,
- long *microseconds_out)
+calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
+ int l_sign, longlong *seconds_out, long *microseconds_out)
{
long days;
bool neg;
@@ -1063,7 +1063,7 @@ calc_time_diff(MYSQL_TIME *l_time1, MYSQL_TIME *l_time2, int l_sign, longlong *s
(uint) l_time2->day);
}
- microseconds= ((longlong)days*86400LL +
+ microseconds= ((longlong)days * SECONDS_IN_24H +
(longlong)(l_time1->hour*3600L +
l_time1->minute*60L +
l_time1->second) -
@@ -1133,3 +1133,145 @@ void time_to_daytime_interval(MYSQL_TIME *ltime)
ltime->hour%= 24;
ltime->time_type= MYSQL_TIMESTAMP_NONE;
}
+
+
+/*** Conversion from TIME to DATETIME ***/
+
+/*
+ Simple case: TIME is within normal 24 hours internal.
+ Mix DATE part of ldate and TIME part of ltime together.
+*/
+static void
+mix_date_and_time_simple(MYSQL_TIME *ldate, const MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ldate->time_type == MYSQL_TIMESTAMP_DATE ||
+ ldate->time_type == MYSQL_TIMESTAMP_DATETIME);
+ ldate->hour= ltime->hour;
+ ldate->minute= ltime->minute;
+ ldate->second= ltime->second;
+ ldate->second_part= ltime->second_part;
+ ldate->time_type= MYSQL_TIMESTAMP_DATETIME;
+}
+
+
+/*
+ Complex case: TIME is negative or outside of the 24 hour interval.
+*/
+static void
+mix_date_and_time_complex(MYSQL_TIME *ldate, const MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ldate->time_type == MYSQL_TIMESTAMP_DATE ||
+ ldate->time_type == MYSQL_TIMESTAMP_DATETIME);
+ longlong seconds;
+ long days, useconds;
+ int sign= ltime->neg ? 1 : -1;
+ ldate->neg= calc_time_diff(ldate, ltime, sign, &seconds, &useconds);
+
+ DBUG_ASSERT(!ldate->neg);
+ DBUG_ASSERT(ldate->year > 0);
+
+ days= (long) (seconds / SECONDS_IN_24H);
+ calc_time_from_sec(ldate, seconds % SECONDS_IN_24H, useconds);
+ get_date_from_daynr(days, &ldate->year, &ldate->month, &ldate->day);
+ ldate->time_type= MYSQL_TIMESTAMP_DATETIME;
+}
+
+
+/**
+ Mix a date value and a time value.
+
+ @param IN/OUT ldate Date value.
+ @param ltime Time value.
+*/
+static void
+mix_date_and_time(MYSQL_TIME *to, const MYSQL_TIME *from)
+{
+ if (!from->neg && from->hour < 24)
+ mix_date_and_time_simple(to, from);
+ else
+ mix_date_and_time_complex(to, from);
+}
+
+
+/**
+ Get current date in DATE format
+*/
+static void
+set_current_date(THD *thd, MYSQL_TIME *to)
+{
+ thd->variables.time_zone->gmt_sec_to_TIME(to, thd->query_start());
+ thd->time_zone_used= 1;
+ datetime_to_date(to);
+}
+
+
+/**
+ 5.5 compatible conversion from TIME to DATETIME
+*/
+static bool
+time_to_datetime_old(THD *thd, const MYSQL_TIME *from, MYSQL_TIME *to)
+{
+ DBUG_ASSERT(from->time_type == MYSQL_TIMESTAMP_TIME);
+
+ if (from->neg)
+ return true;
+
+ /* Set the date part */
+ uint day= from->hour / 24;
+ to->day= day % 31;
+ to->month= day / 31;
+ to->year= 0;
+ /* Set the time part */
+ to->hour= from->hour % 24;
+ to->minute= from->minute;
+ to->second= from->second;
+ to->second_part= from->second_part;
+ /* set sign and type */
+ to->neg= 0;
+ to->time_type= MYSQL_TIMESTAMP_DATETIME;
+ return false;
+}
+
+
+/**
+ Convert time to datetime.
+
+ The time value is added to the current datetime value.
+ @param IN ltime Time value to convert from.
+ @param OUT ltime2 Datetime value to convert to.
+*/
+bool
+time_to_datetime(THD *thd, const MYSQL_TIME *from, MYSQL_TIME *to)
+{
+ if (thd->variables.old_mode)
+ return time_to_datetime_old(thd, from, to);
+ set_current_date(thd, to);
+ mix_date_and_time(to, from);
+ return false;
+}
+
+
+bool
+time_to_datetime_with_warn(THD *thd,
+ const MYSQL_TIME *from, MYSQL_TIME *to,
+ ulonglong fuzzydate)
+{
+ int warn= 0;
+ DBUG_ASSERT(from->time_type == MYSQL_TIMESTAMP_TIME);
+ /*
+ After time_to_datetime() we need to do check_date(), as
+ the caller may want TIME_NO_ZERO_DATE or TIME_NO_ZERO_IN_DATE.
+ Note, the SQL standard time->datetime conversion mode always returns
+ a valid date based on CURRENT_DATE. So we need to do check_date()
+ only in the old mode.
+ */
+ if (time_to_datetime(thd, from, to) ||
+ (thd->variables.old_mode && check_date(to, fuzzydate, &warn)))
+ {
+ ErrConvTime str(from);
+ make_truncated_value_warning(thd, Sql_condition::WARN_LEVEL_WARN,
+ &str, MYSQL_TIMESTAMP_DATETIME, 0);
+ return true;
+ }
+ return false;
+} \ No newline at end of file
diff --git a/sql/sql_time.h b/sql/sql_time.h
index e27102b15d3..7513ca7c00a 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -49,6 +49,33 @@ bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime,
ulonglong fuzzydate,
const char *name);
+bool time_to_datetime(THD *thd, const MYSQL_TIME *tm, MYSQL_TIME *dt);
+bool time_to_datetime_with_warn(THD *thd,
+ const MYSQL_TIME *tm, MYSQL_TIME *dt,
+ ulonglong fuzzydate);
+inline void datetime_to_time(MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_DATE ||
+ ltime->time_type == MYSQL_TIMESTAMP_DATETIME);
+ DBUG_ASSERT(ltime->neg == 0);
+ ltime->year= ltime->month= ltime->day= 0;
+ ltime->time_type= MYSQL_TIMESTAMP_TIME;
+}
+inline void datetime_to_date(MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_DATE ||
+ ltime->time_type == MYSQL_TIMESTAMP_DATETIME);
+ DBUG_ASSERT(ltime->neg == 0);
+ ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
+ ltime->time_type= MYSQL_TIMESTAMP_DATE;
+}
+inline void date_to_datetime(MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_DATE ||
+ ltime->time_type == MYSQL_TIMESTAMP_DATETIME);
+ DBUG_ASSERT(ltime->neg == 0);
+ ltime->time_type= MYSQL_TIMESTAMP_DATETIME;
+}
void make_truncated_value_warning(THD *thd,
Sql_condition::enum_warning_level level,
const ErrConv *str_val,
@@ -76,8 +103,8 @@ bool my_TIME_to_str(const MYSQL_TIME *ltime, String *str, uint dec);
/* MYSQL_TIME operations */
bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type,
INTERVAL interval);
-bool calc_time_diff(MYSQL_TIME *l_time1, MYSQL_TIME *l_time2, int l_sign,
- longlong *seconds_out, long *microseconds_out);
+bool calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
+ int l_sign, longlong *seconds_out, long *microseconds_out);
int my_time_compare(const MYSQL_TIME *a, const MYSQL_TIME *b);
void localtime_to_TIME(MYSQL_TIME *to, struct tm *from);
void calc_time_from_sec(MYSQL_TIME *to, long seconds, long microseconds);
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index b3bbebb2433..7fd68fec308 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -5976,6 +5976,10 @@ static void test_bind_date_conv(uint row_count)
MYSQL_TIME tm[4];
ulong second_part;
uint year, month, day, hour, minute, sec;
+ uint now_year= 1990, now_month= 3, now_day= 13;
+
+ rc= mysql_query(mysql, "SET timestamp=UNIX_TIMESTAMP('1990-03-13')");
+ myquery(rc);
stmt= mysql_simple_prepare(mysql, "INSERT INTO test_date VALUES(?, ?, ?, ?)");
check_stmt(stmt);
@@ -6076,9 +6080,15 @@ static void test_bind_date_conv(uint row_count)
i, tm[i].year, tm[i].month, tm[i].day,
tm[i].hour, tm[i].minute, tm[i].second,
tm[i].second_part);
- DIE_UNLESS(tm[i].year == 0 || tm[i].year == year+count);
- DIE_UNLESS(tm[i].month == 0 || tm[i].month == month+count);
- DIE_UNLESS(tm[i].day == 0 || tm[i].day == day+count);
+ DIE_UNLESS(tm[i].year == 0 || tm[i].year == year + count ||
+ (tm[i].year == now_year &&
+ my_bind[i].buffer_type == MYSQL_TYPE_TIME));
+ DIE_UNLESS(tm[i].month == 0 || tm[i].month == month + count ||
+ (tm[i].month == now_month &&
+ my_bind[i].buffer_type == MYSQL_TYPE_TIME));
+ DIE_UNLESS(tm[i].day == 0 || tm[i].day == day + count ||
+ (tm[i].day == now_day &&
+ my_bind[i].buffer_type == MYSQL_TYPE_TIME));
DIE_UNLESS(tm[i].hour == 0 || tm[i].hour == hour+count);
DIE_UNLESS(tm[i].minute == 0 || tm[i].minute == minute+count);