diff options
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(<ime, is_time_flag)) + // Not using get_arg0_date to avoid automatic TIME to DATETIME conversion + if ((null_value= args[0]->get_date(<ime, 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(<ime1, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE) || - args[1]->get_date(<ime2, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE)) + if (args[0]->get_date_with_conversion(<ime1, + TIME_NO_ZERO_DATE | + TIME_NO_ZERO_IN_DATE) || + args[1]->get_date_with_conversion(<ime2, + TIME_NO_ZERO_DATE | + TIME_NO_ZERO_IN_DATE)) goto null_date; if (calc_time_diff(<ime2,<ime1, 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); |