diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2011-06-06 20:28:15 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2011-06-06 20:28:15 +0200 |
| commit | 4d128777dde904c5f0adab9b093e854c9c580d41 (patch) | |
| tree | 36875e84e65be596def46c5d7ce621e60abcbdae | |
| parent | c1a92f9caeb368021d5ffbe0df237ded29692c1a (diff) | |
| download | mariadb-git-4d128777dde904c5f0adab9b093e854c9c580d41.tar.gz | |
revert a suggested "optimization" that introduced a bug
compilation error in mysys/my_getsystime.c fixed
some redundant code removed
sec_to_time, time_to_sec, from_unixtime, unix_timestamp, @@timestamp now
use decimal, not double for numbers with a fractional part.
purge_master_logs_before_date() fixed
many bugs in corner cases fixed
mysys/my_getsystime.c:
compilation failure fixed
sql/sql_parse.cc:
don't cut corners. it backfires.
67 files changed, 1556 insertions, 1110 deletions
diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index ec337e0e198..1083976b1a0 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -1256,7 +1256,7 @@ static my_time_t convert_str_to_timestamp(const char* str) int was_cut; MYSQL_TIME l_time; long dummy_my_timezone; - my_bool dummy_in_dst_time_gap; + uint dummy_in_dst_time_gap; /* We require a total specification (date AND time) */ if (str_to_datetime(str, (uint) strlen(str), &l_time, 0, &was_cut) != MYSQL_TIMESTAMP_DATETIME || was_cut) diff --git a/include/my_time.h b/include/my_time.h index 8ebca27e88d..79742d5e283 100644 --- a/include/my_time.h +++ b/include/my_time.h @@ -47,7 +47,7 @@ typedef long my_time_t; #define TIMESTAMP_MAX_YEAR 2038 #define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1) #define TIMESTAMP_MAX_VALUE INT_MAX32 -#define TIMESTAMP_MIN_VALUE 1 +#define TIMESTAMP_MIN_VALUE 0 /* two-digit years < this are 20..; >= this are 19.. */ #define YY_PART_YEAR 70 @@ -72,8 +72,7 @@ typedef long my_time_t; #define TIME_MAX_SECOND_PART 999999 #define TIME_SECOND_PART_FACTOR (TIME_MAX_SECOND_PART+1) #define TIME_SECOND_PART_DIGITS 6 -#define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + \ - TIME_MAX_SECOND + TIME_MAX_SECOND_PART/(double)TIME_SECOND_PART_FACTOR) +#define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + TIME_MAX_SECOND) #define TIME_MAX_VALUE_SECONDS (TIME_MAX_HOUR * 3600L + \ TIME_MAX_MINUTE * 60L + TIME_MAX_SECOND) @@ -84,9 +83,10 @@ str_to_time(const char *str, uint length, MYSQL_TIME *l_time, int *warning); enum enum_mysql_timestamp_type str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, uint flags, int *was_cut); -longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, +longlong number_to_datetime(longlong nr, ulong sec_part, MYSQL_TIME *time_res, uint flags, int *was_cut); -int number_to_time(double nr, MYSQL_TIME *ltime, int *was_cut); +int number_to_time(my_bool neg, longlong nr, ulong sec_part, + MYSQL_TIME *ltime, int *was_cut); ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *); ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *); ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *); @@ -129,8 +129,7 @@ static inline my_bool validate_timestamp_range(const MYSQL_TIME *t) } my_time_t -my_system_gmt_sec(const MYSQL_TIME *t, long *my_timezone, - my_bool *in_dst_time_gap); +my_system_gmt_sec(const MYSQL_TIME *t, long *my_timezone, uint *error_code); void set_zero_time(MYSQL_TIME *tm, enum enum_mysql_timestamp_type time_type); diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c index 8c2f277df2c..b4082418d3c 100644 --- a/libmysql/libmysql.c +++ b/libmysql/libmysql.c @@ -3720,7 +3720,8 @@ static void fetch_long_with_conversion(MYSQL_BIND *param, MYSQL_FIELD *field, case MYSQL_TYPE_DATETIME: { int error; - value= number_to_datetime(value, (MYSQL_TIME *) buffer, TIME_FUZZY_DATE, + value= number_to_datetime(value, 0, + (MYSQL_TIME *) buffer, TIME_FUZZY_DATE, &error); *param->error= test(error); break; diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc index 05281814827..bb430e04e1d 100644 --- a/mysql-test/include/type_hrtime.inc +++ b/mysql-test/include/type_hrtime.inc @@ -53,9 +53,38 @@ create table t3 like t1; show create table t2; show create table t3; +drop table t2, t3; + +# math, aggregation +insert t1 values ('2010-12-13 14:15:16.222222'); +select a, a+0, a-1, a*1, a/2 from t1; +select max(a), min(a), sum(a), avg(a) from t1; +create table t2 select a, a+0, a-1, a*1, a/2 from t1; +create table t3 select max(a), min(a), sum(a), avg(a) from t1; +show create table t2; +show create table t3; drop table t1, t2, t3; +# insert, alter with conversion +--vertical_results +eval create table t1 (f0_$type $type(0), f1_$type $type(1), f2_$type $type(2), f3_$type $type(3), f4_$type $type(4), f5_$type $type(5), f6_$type $type(6)); +insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); +select * from t1; +eval select cast(f0_$type as time(4)) time4_f0_$type, cast(f1_$type as datetime(3)) datetime3_f1_$type, cast(f2_$type as date) date_f2_$type, cast(f4_$type as decimal(40,5)) decimal5_f4_$type, cast(f5_$type as signed) bigint_f5_$type, cast(f6_$type as char(255)) varchar_f6_$type from t1; +eval create table t2 (time4_f0_$type time(4), datetime3_f1_$type datetime(3), date_f2_$type date, double_f3_$type double, decimal5_f4_$type decimal(40,5), bigint_f5_$type bigint, varchar_f6_$type varchar(255)); +insert t2 select * from t1; +select * from t2; +eval alter table t1 change f0_$type time4_f0_$type time(4), change f1_$type datetime3_f1_$type datetime(3), change f2_$type date_f2_$type date, change f3_$type double_f3_$type double, change f4_$type decimal5_f4_$type decimal(40,5), change f5_$type bigint_f5_$type bigint, change f6_$type varchar_f6_$type varchar(255); +select * from t1; +eval alter table t1 modify time4_f0_$type $type(0), modify datetime3_f1_$type $type(1), modify date_f2_$type $type(2), modify double_f3_$type $type(3), modify decimal5_f4_$type $type(4), modify bigint_f5_$type $type(5), modify varchar_f6_$type $type(6); +select * from t1; +delete from t1; +insert t1 select * from t2; +select * from t1; +drop table t1, t2; +--horizontal_results + # # SP # diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index dd346cb94dc..b91fce2f7f4 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -507,7 +507,7 @@ f1 f2 f3 Warnings: Warning 1292 Truncated incorrect datetime value: '2003-01-02 10:11:12.0012ABCD' Warning 1292 Truncated incorrect time value: '-01:01:01.01 GGG' -Warning 1292 Truncated incorrect time value: '1997-12-31 23:59:59.01XXXX' +Warning 1292 Truncated incorrect datetime value: '1997-12-31 23:59:59.01XXXX' select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; f1 f2 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 606f879b47f..27fb8e60d6e 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1530,7 +1530,8 @@ insert into t1 values (02,2002,20020101,"2002-01-01 23:59:59"), (60,2060,20600101,"2060-01-01 11:11:11"), (70,1970,19700101,"1970-11-11 22:22:22"), -(NULL,NULL,NULL,NULL); +(NULL,NULL,NULL,NULL), +(71,1971,19710101,"1971-11-11 22:22:22"); select min(f1),max(f1) from t1; min(f1) max(f1) 70 60 @@ -1553,36 +1554,49 @@ a b gt lt eq 60 98 1 0 0 70 98 0 1 0 NULL 98 NULL NULL 0 +71 98 0 1 0 98 00 0 1 0 00 00 0 0 1 02 00 1 0 0 60 00 1 0 0 70 00 0 1 0 NULL 00 NULL NULL 0 +71 00 0 1 0 98 02 0 1 0 00 02 0 1 0 02 02 0 0 1 60 02 1 0 0 70 02 0 1 0 NULL 02 NULL NULL 0 +71 02 0 1 0 98 60 0 1 0 00 60 0 1 0 02 60 0 1 0 60 60 0 0 1 70 60 0 1 0 NULL 60 NULL NULL 0 +71 60 0 1 0 98 70 1 0 0 00 70 1 0 0 02 70 1 0 0 60 70 1 0 0 70 70 0 0 1 NULL 70 NULL NULL 0 +71 70 1 0 0 98 NULL NULL NULL 0 00 NULL NULL NULL 0 02 NULL NULL NULL 0 60 NULL NULL NULL 0 70 NULL NULL NULL 0 NULL NULL NULL NULL 1 +71 NULL NULL NULL 0 +98 71 1 0 0 +00 71 1 0 0 +02 71 1 0 0 +60 71 1 0 0 +70 71 0 1 0 +NULL 71 NULL NULL 0 +71 71 0 0 1 select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt, a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq from t1 a, t1 b; @@ -1593,36 +1607,49 @@ a b gt lt eq 60 1998 1 0 0 70 1998 0 1 0 NULL 1998 NULL NULL 0 +71 1998 0 1 0 98 2000 0 1 0 00 2000 0 0 1 02 2000 1 0 0 60 2000 1 0 0 70 2000 0 1 0 NULL 2000 NULL NULL 0 +71 2000 0 1 0 98 2002 0 1 0 00 2002 0 1 0 02 2002 0 0 1 60 2002 1 0 0 70 2002 0 1 0 NULL 2002 NULL NULL 0 +71 2002 0 1 0 98 2060 0 1 0 00 2060 0 1 0 02 2060 0 1 0 60 2060 0 0 1 70 2060 0 1 0 NULL 2060 NULL NULL 0 +71 2060 0 1 0 98 1970 1 0 0 00 1970 1 0 0 02 1970 1 0 0 60 1970 1 0 0 70 1970 0 0 1 NULL 1970 NULL NULL 0 +71 1970 1 0 0 98 NULL NULL NULL 0 00 NULL NULL NULL 0 02 NULL NULL NULL 0 60 NULL NULL NULL 0 70 NULL NULL NULL 0 NULL NULL NULL NULL 1 +71 NULL NULL NULL 0 +98 1971 1 0 0 +00 1971 1 0 0 +02 1971 1 0 0 +60 1971 1 0 0 +70 1971 0 1 0 +NULL 1971 NULL NULL 0 +71 1971 0 0 1 select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt, a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq from t1 a, t1 b; @@ -1633,36 +1660,49 @@ a b gt lt eq 60 1998-01-01 1 0 0 70 1998-01-01 0 1 0 NULL 1998-01-01 NULL NULL 0 +71 1998-01-01 0 1 0 98 2000-01-01 0 1 0 00 2000-01-01 0 1 0 02 2000-01-01 1 0 0 60 2000-01-01 1 0 0 70 2000-01-01 0 1 0 NULL 2000-01-01 NULL NULL 0 +71 2000-01-01 0 1 0 98 2002-01-01 0 1 0 00 2002-01-01 0 1 0 02 2002-01-01 0 1 0 60 2002-01-01 1 0 0 70 2002-01-01 0 1 0 NULL 2002-01-01 NULL NULL 0 +71 2002-01-01 0 1 0 98 2060-01-01 0 1 0 00 2060-01-01 0 1 0 02 2060-01-01 0 1 0 60 2060-01-01 0 1 0 70 2060-01-01 0 1 0 NULL 2060-01-01 NULL NULL 0 +71 2060-01-01 0 1 0 98 1970-01-01 1 0 0 00 1970-01-01 1 0 0 02 1970-01-01 1 0 0 60 1970-01-01 1 0 0 70 1970-01-01 0 1 0 NULL 1970-01-01 NULL NULL 0 +71 1970-01-01 1 0 0 98 NULL NULL NULL 0 00 NULL NULL NULL 0 02 NULL NULL NULL 0 60 NULL NULL NULL 0 70 NULL NULL NULL 0 NULL NULL NULL NULL 1 +71 NULL NULL NULL 0 +98 1971-01-01 1 0 0 +00 1971-01-01 1 0 0 +02 1971-01-01 1 0 0 +60 1971-01-01 1 0 0 +70 1971-01-01 0 1 0 +NULL 1971-01-01 NULL NULL 0 +71 1971-01-01 0 1 0 select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt, a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq from t1 a, t1 b; @@ -1673,36 +1713,49 @@ a b gt lt eq 60 1998-01-01 00:00:00 1 0 0 70 1998-01-01 00:00:00 0 1 0 NULL 1998-01-01 00:00:00 NULL NULL 0 +71 1998-01-01 00:00:00 0 1 0 98 2000-01-01 00:00:01 0 1 0 00 2000-01-01 00:00:01 0 1 0 02 2000-01-01 00:00:01 1 0 0 60 2000-01-01 00:00:01 1 0 0 70 2000-01-01 00:00:01 0 1 0 NULL 2000-01-01 00:00:01 NULL NULL 0 +71 2000-01-01 00:00:01 0 1 0 98 2002-01-01 23:59:59 0 1 0 00 2002-01-01 23:59:59 0 1 0 02 2002-01-01 23:59:59 0 1 0 60 2002-01-01 23:59:59 1 0 0 70 2002-01-01 23:59:59 0 1 0 NULL 2002-01-01 23:59:59 NULL NULL 0 +71 2002-01-01 23:59:59 0 1 0 98 2060-01-01 11:11:11 0 1 0 00 2060-01-01 11:11:11 0 1 0 02 2060-01-01 11:11:11 0 1 0 60 2060-01-01 11:11:11 0 1 0 70 2060-01-01 11:11:11 0 1 0 NULL 2060-01-01 11:11:11 NULL NULL 0 +71 2060-01-01 11:11:11 0 1 0 98 1970-11-11 22:22:22 1 0 0 00 1970-11-11 22:22:22 1 0 0 02 1970-11-11 22:22:22 1 0 0 60 1970-11-11 22:22:22 1 0 0 70 1970-11-11 22:22:22 0 1 0 NULL 1970-11-11 22:22:22 NULL NULL 0 +71 1970-11-11 22:22:22 1 0 0 98 NULL NULL NULL 0 00 NULL NULL NULL 0 02 NULL NULL NULL 0 60 NULL NULL NULL 0 70 NULL NULL NULL 0 NULL NULL NULL NULL 1 +71 NULL NULL NULL 0 +98 1971-11-11 22:22:22 1 0 0 +00 1971-11-11 22:22:22 1 0 0 +02 1971-11-11 22:22:22 1 0 0 +60 1971-11-11 22:22:22 1 0 0 +70 1971-11-11 22:22:22 0 1 0 +NULL 1971-11-11 22:22:22 NULL NULL 0 +71 1971-11-11 22:22:22 0 1 0 select *, f1 = f2 from t1; f1 f2 f3 f4 f1 = f2 98 1998 1998-01-01 1998-01-01 00:00:00 1 @@ -1711,6 +1764,7 @@ f1 f2 f3 f4 f1 = f2 60 2060 2060-01-01 2060-01-01 11:11:11 1 70 1970 1970-01-01 1970-11-11 22:22:22 1 NULL NULL NULL NULL NULL +71 1971 1971-01-01 1971-11-11 22:22:22 1 drop table t1; # # Bug #54465: assert: field_types == 0 || field_types[field_pos] == diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 1edf7fcf613..aeb7142b4d4 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -479,7 +479,7 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); a 1972-02-06 Warnings: -Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 +Warning 1292 Incorrect datetime value: '19772-07-29' DROP TABLE t1,t2,t3,t4; CREATE TABLE t1 (id int not null); INSERT INTO t1 VALUES (1),(2); diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index ca79f53ff5b..feb92da3321 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -184,8 +184,8 @@ microsecond("1997-12-31 23:59:59.000001") 1 create table t1 select makedate(1997,1) as f1, -addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2, -addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3, +addtime(cast("1997-12-31 23:59:59.000001" as datetime(6)), "1 1:1:1.000002") as f2, +addtime(cast("23:59:59.999999" as time(6)) , "1 1:1:1.000002") as f3, timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, maketime(10,11,12) as f6, diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 3194e618bb8..dc84678ff8e 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -11,19 +11,33 @@ now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(no 0 0 0 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; from_unixtime(unix_timestamp("1994-03-02 10:11:12")) from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s") from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0 -1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112 +1994-03-02 10:11:12.000000 1994-03-02 10:11:12 19940302101112.000000 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) -02:30:01 23001 54742 00:04:57.423510 +02:30:01 23001 54742.000000 00:04:57.423510 select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); sec_to_time(9001.1) time_to_sec('15:12:22.123456') time_to_sec(15.5566778899) 02:30:01.1 54742.123456 15.556677 -Warnings: -Warning 1292 Truncated incorrect time value: '15.5566778899' select sec_to_time(time_to_sec('-838:59:59')); sec_to_time(time_to_sec('-838:59:59')) --838:59:59 +-838:59:59.000000 +select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); +sec_to_time('9001.1') sec_to_time('1234567890123.123') +02:30:01.100000 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1234567890123.123' +select sec_to_time(90011e-1), sec_to_time(1234567890123e30); +sec_to_time(90011e-1) sec_to_time(1234567890123e30) +02:30:01.100000 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1.234567890123e+42' +select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999'); +sec_to_time(1234567890123) sec_to_time('99999999999999999999999999999') +838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1234567890123' +Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' select now()-curdate()*1000000-curtime(); now()-curdate()*1000000-curtime() 0 @@ -142,18 +156,18 @@ Saturday 5 select monthname("1972-03-04"),monthname("1972-03-04")+0; monthname("1972-03-04") monthname("1972-03-04")+0 March 3 -select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 -select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 -select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 -select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 +select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') +00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 +select time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') +01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 +select time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') +13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 +select time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') +01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w'); date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w') 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6 @@ -548,10 +562,10 @@ select @a:=FROM_UNIXTIME(1); 1970-01-01 03:00:01 select unix_timestamp(@a); unix_timestamp(@a) -1 +1.000000 select unix_timestamp('1969-12-01 19:00:01'); unix_timestamp('1969-12-01 19:00:01') -0 +0.000000 select from_unixtime(-1); from_unixtime(-1) NULL @@ -572,31 +586,31 @@ unix_timestamp(from_unixtime(2147483648)) NULL select unix_timestamp('2039-01-20 01:00:00'); unix_timestamp('2039-01-20 01:00:00') -0 +0.000000 select unix_timestamp('1968-01-20 01:00:00'); unix_timestamp('1968-01-20 01:00:00') -0 +0.000000 select unix_timestamp('2038-02-10 01:00:00'); unix_timestamp('2038-02-10 01:00:00') -0 +0.000000 select unix_timestamp('1969-11-20 01:00:00'); unix_timestamp('1969-11-20 01:00:00') -0 +0.000000 select unix_timestamp('2038-01-20 01:00:00'); unix_timestamp('2038-01-20 01:00:00') -0 +0.000000 select unix_timestamp('1969-12-30 01:00:00'); unix_timestamp('1969-12-30 01:00:00') -0 +0.000000 select unix_timestamp('2038-01-17 12:00:00'); unix_timestamp('2038-01-17 12:00:00') -2147331600 +2147331600.000000 select unix_timestamp('1970-01-01 03:00:01'); unix_timestamp('1970-01-01 03:00:01') -1 +1.000000 select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') -0 +0.000000 SELECT CHARSET(DAYNAME(19700101)); CHARSET(DAYNAME(19700101)) latin1 @@ -944,10 +958,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `now() - now()` double(17,0) NOT NULL DEFAULT '0', - `curtime() - curtime()` double(17,0) NOT NULL DEFAULT '0', - `sec_to_time(1) + 0` double(17,0) DEFAULT NULL, - `from_unixtime(1) + 0` double(17,0) DEFAULT NULL + `now() - now()` decimal(20,0) NOT NULL DEFAULT '0', + `curtime() - curtime()` decimal(11,0) NOT NULL DEFAULT '0', + `sec_to_time(1) + 0` decimal(11,0) DEFAULT NULL, + `from_unixtime(1) + 0` decimal(20,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SELECT SEC_TO_TIME(3300000); @@ -1036,7 +1050,7 @@ SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED)); SEC_TO_TIME(CAST(-1 AS UNSIGNED)) 838:59:59 Warnings: -Warning 1292 Truncated incorrect time value: '1.84467440737096e+19' +Warning 1292 Truncated incorrect time value: '18446744073709551615' SET NAMES latin1; SET character_set_results = NULL; SHOW VARIABLES LIKE 'character_set_results'; @@ -1084,7 +1098,9 @@ NULL select isnull(week(now() + 0)), isnull(week(now() + 0.2)), week(20061108), week(20061108.01), week(20061108085411.000002); isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002) -0 0 45 45 45 +0 0 45 NULL 45 +Warnings: +Warning 1292 Truncated incorrect datetime value: '20061108.01' End of 4.1 tests explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2; @@ -1419,6 +1435,16 @@ time('-1 02:03:04') + interval 2 day select time('-1 02:03:04') - interval 2 day; time('-1 02:03:04') - interval 2 day -74:03:04 +select time('10 02:03:04') + interval 30 day; +time('10 02:03:04') + interval 30 day +NULL +Warnings: +Warning 1441 Datetime function: time field overflow +select time('10 02:03:04') + interval 1 year; +time('10 02:03:04') + interval 1 year +NULL +Warnings: +Warning 1441 Datetime function: time field overflow select cast('131415.123e0' as time); cast('131415.123e0' as time) NULL @@ -1461,8 +1487,6 @@ select * from t1 where f1 > time('-23:00:06'); f1 2000-09-12 00:00:00 2007-04-25 05:08:49 -Warnings: -Warning 1292 Incorrect datetime value: '-23:00:06' for column 'f1' at row 1 drop table t1; select maketime(20,61,10)+0; maketime(20,61,10)+0 @@ -1512,7 +1536,7 @@ select least(1, f1) from t1; least(1, f1) 0000-00-00 00:00:00 Warnings: -Warning 1292 Incorrect datetime value: '1' for column 'f1' at row 1 +Warning 1292 Truncated incorrect datetime value: '1' drop table t1; select now() > coalesce(time('21:43:24'), date('2010-05-03')); now() > coalesce(time('21:43:24'), date('2010-05-03')) @@ -1537,6 +1561,8 @@ insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00 select least(greatest(f3, f2, f4), f5) from t1; least(greatest(f3, f2, f4), f5) 0000-00-00 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '0' drop table t1; select day(coalesce(null)); day(coalesce(null)) diff --git a/mysql-test/r/func_time_hires.result b/mysql-test/r/func_time_hires.result index 1528bd81a6c..6aed828f5bd 100644 --- a/mysql-test/r/func_time_hires.result +++ b/mysql-test/r/func_time_hires.result @@ -14,10 +14,10 @@ current_time(3) 01:01:01.123 current_timestamp(4) 2011-01-01 01:01:01.1234 localtime(5) 2011-01-01 01:01:01.12345 localtimestamp(6) 2011-01-01 01:01:01.123456 -time_to_sec('12:34:56') 45296 -time_to_sec('12:34:56.789') 45296.789 +time_to_sec('12:34:56') 45296.000000 +time_to_sec('12:34:56.789') 45296.789000 select sec_to_time(time_to_sec('1:2:3')), sec_to_time(time_to_sec('2:3:4.567890')); -sec_to_time(time_to_sec('1:2:3')) 01:02:03 +sec_to_time(time_to_sec('1:2:3')) 01:02:03.000000 sec_to_time(time_to_sec('2:3:4.567890')) 02:03:04.567890 select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222)); time_to_sec(sec_to_time(11111)) 11111 @@ -47,7 +47,7 @@ t1 CREATE TABLE `t1` ( `localtime(5)` datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000', `localtimestamp(6)` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', `time_to_sec(123456)` bigint(17) DEFAULT NULL, - `time_to_sec('12:34:56.789')` double DEFAULT NULL + `time_to_sec('12:34:56.789')` decimal(22,6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; sec_to_time(12345) 03:25:45 @@ -62,33 +62,27 @@ current_timestamp(4) 2011-01-01 01:01:01.1234 localtime(5) 2011-01-01 01:01:01.12345 localtimestamp(6) 2011-01-01 01:01:01.123456 time_to_sec(123456) 45296 -time_to_sec('12:34:56.789') 45296.789 +time_to_sec('12:34:56.789') 45296.789000 drop table t1; select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));; -unix_timestamp('2011-01-01 01:01:01') 1293832861 -unix_timestamp('2011-01-01 01:01:01.123456') 1293832861.12346 +unix_timestamp('2011-01-01 01:01:01') 1293832861.000000 +unix_timestamp('2011-01-01 01:01:01.123456') 1293832861.123456 unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))) 1293832861 -unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4))) 1293832861.1235 +unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4))) 1293832861.1234 select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));; -from_unixtime(unix_timestamp('2011/1/1 1:1:1')) 2011-01-01 01:01:01 +from_unixtime(unix_timestamp('2011/1/1 1:1:1')) 2011-01-01 01:01:01.000000 from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')) 2011-01-01 01:01:01.123456 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))) 2011-01-01 01:01:01 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4)))) 2011-01-01 01:01:01.1234 select sec_to_time(3020399.99999), sec_to_time(3020399.999999), sec_to_time(3020399.9999999); sec_to_time(3020399.99999) sec_to_time(3020399.999999) sec_to_time(3020399.9999999) -838:59:59.99998 838:59:59.999999 838:59:59.999999 -Warnings: -Warning 1292 Truncated incorrect time value: '3020399.9999999' +838:59:59.99999 838:59:59.999999 838:59:59.999999 select sec_to_time(-3020399.99999), sec_to_time(-3020399.999999), sec_to_time(-3020399.9999999); sec_to_time(-3020399.99999) sec_to_time(-3020399.999999) sec_to_time(-3020399.9999999) --838:59:59.99998 -838:59:59.999999 -838:59:59.999999 -Warnings: -Warning 1292 Truncated incorrect time value: '-3020399.9999999' +-838:59:59.99999 -838:59:59.999999 -838:59:59.999999 select 20010101000203.000000004 + interval 1 day; 20010101000203.000000004 + interval 1 day 2001-01-02 00:02:03.000000 -Warnings: -Warning 1292 Truncated incorrect datetime value: '20010101000203.000000004' select 20010101000203.4 + interval 1 day; 20010101000203.4 + interval 1 day 2001-01-02 00:02:03.4 @@ -157,6 +151,14 @@ t4 12:13:14.1234 t5 12:13:14.12345 t6 12:13:14.123456 drop table t1; +explain extended select cast(cast(@a as datetime(4)) as time(0)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select cast(cast((@a) as datetime(4)) as time) AS `cast(cast(@a as datetime(4)) as time(0))` +select cast(cast(@a as time(2)) as time(6)); +cast(cast(@a as time(2)) as time(6)) +12:13:14.120000 select CAST(@a AS DATETIME(7)); ERROR 42000: Too big precision 7 specified for '(@a)'. Maximum is 6. SELECT CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00'); diff --git a/mysql-test/r/func_timestamp.result b/mysql-test/r/func_timestamp.result index 495fedea9e6..18fcbd947e7 100644 --- a/mysql-test/r/func_timestamp.result +++ b/mysql-test/r/func_timestamp.result @@ -7,7 +7,7 @@ SELECT CONCAT(Jahr,'-',Monat,'-',Tag,' ',Zeit) AS Date, UNIX_TIMESTAMP(CONCAT(Jahr,'-',Monat,'-',Tag,' ',Zeit)) AS Unix FROM t1; Date Unix -1998-9-16 09:26:00 905927160 -1998-9-16 09:26:00 905927160 +1998-9-16 09:26:00 905927160.000000 +1998-9-16 09:26:00 905927160.000000 drop table t1; set time_zone= @@global.time_zone; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index e209193a2c0..09365152f85 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -212,7 +212,7 @@ DROP TABLE t1; End of 5.0 tests select cast('01:01:01' as time), cast('01:01:01' as time(2)); Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def cast('01:01:01' as time) 11 9 8 Y 128 0 63 -def cast('01:01:01' as time(2)) 11 12 11 Y 128 2 63 +def cast('01:01:01' as time) 11 10 8 Y 128 0 63 +def cast('01:01:01' as time(2)) 11 13 11 Y 128 2 63 cast('01:01:01' as time) cast('01:01:01' as time(2)) 01:01:01 01:01:01.00 diff --git a/mysql-test/r/parser_precedence.result b/mysql-test/r/parser_precedence.result index cf301ec677b..979084d0346 100644 --- a/mysql-test/r/parser_precedence.result +++ b/mysql-test/r/parser_precedence.result @@ -1,4 +1,5 @@ drop table if exists t1_30237_bool; +set sql_mode=NO_UNSIGNED_SUBTRACTION; create table t1_30237_bool(A boolean, B boolean, C boolean); insert into t1_30237_bool values (FALSE, FALSE, FALSE), diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 2fa07c5de6e..c7c31fffc9f 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -64,7 +64,7 @@ def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 def test t9 t9 c15 c15 7 19 19 N 9377 0 63 -def test t9 t9 c16 c16 11 9 8 Y 128 0 63 +def test t9 t9 c16 c16 11 10 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 @@ -1793,8 +1793,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longblob, - `const10` double NOT NULL DEFAULT '0', - `param10` double DEFAULT NULL, + `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1823,8 +1823,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63 -def test t5 t5 const10 const10 5 49 9 N 32769 31 63 -def test t5 t5 param10 param10 5 23 9 Y 32768 31 63 +def test t5 t5 const10 const10 246 24 16 N 1 6 63 +def test t5 t5 param10 param10 246 67 40 Y 0 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1850,8 +1850,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861 -param10 662680861 +const10 662680861.000000 +param10 662680861.000000000000000000000000000000 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 06062193951..1709d63d16e 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -64,7 +64,7 @@ def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 def test t9 t9 c15 c15 7 19 19 N 9377 0 63 -def test t9 t9 c16 c16 11 9 8 Y 128 0 63 +def test t9 t9 c16 c16 11 10 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 @@ -1776,8 +1776,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longblob, - `const10` double NOT NULL DEFAULT '0', - `param10` double DEFAULT NULL, + `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1806,8 +1806,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63 -def test t5 t5 const10 const10 5 49 9 N 32769 31 63 -def test t5 t5 param10 param10 5 23 9 Y 32768 31 63 +def test t5 t5 const10 const10 246 24 16 N 1 6 63 +def test t5 t5 param10 param10 246 67 40 Y 0 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1833,8 +1833,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861 -param10 662680861 +const10 662680861.000000 +param10 662680861.000000000000000000000000000000 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 1819a9e649b..1b099c214b1 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -65,7 +65,7 @@ def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 def test t9 t9 c15 c15 7 19 19 N 9377 0 63 -def test t9 t9 c16 c16 11 9 8 Y 128 0 63 +def test t9 t9 c16 c16 11 10 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 @@ -1777,8 +1777,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longblob, - `const10` double NOT NULL DEFAULT '0', - `param10` double DEFAULT NULL, + `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1807,8 +1807,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63 -def test t5 t5 const10 const10 5 49 9 N 32769 31 63 -def test t5 t5 param10 param10 5 23 9 Y 32768 31 63 +def test t5 t5 const10 const10 246 24 16 N 1 6 63 +def test t5 t5 param10 param10 246 67 40 Y 0 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1834,8 +1834,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861 -param10 662680861 +const10 662680861.000000 +param10 662680861.000000000000000000000000000000 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 4a8e0adf46f..20023d0b589 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -107,7 +107,7 @@ def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 def test t9 t9 c15 c15 7 19 19 N 9377 0 63 -def test t9 t9 c16 c16 11 9 8 Y 128 0 63 +def test t9 t9 c16 c16 11 10 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 @@ -1713,8 +1713,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longblob, - `const10` double NOT NULL DEFAULT '0', - `param10` double DEFAULT NULL, + `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1743,8 +1743,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63 -def test t5 t5 const10 const10 5 49 9 N 32769 31 63 -def test t5 t5 param10 param10 5 23 9 Y 32768 31 63 +def test t5 t5 const10 const10 246 24 16 N 1 6 63 +def test t5 t5 param10 param10 246 67 40 Y 0 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1770,8 +1770,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861 -param10 662680861 +const10 662680861.000000 +param10 662680861.000000000000000000000000000000 const11 1991 param11 1991 const12 NULL @@ -3461,7 +3461,7 @@ def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 def test t9 t9 c15 c15 7 19 19 N 9377 0 63 -def test t9 t9 c16 c16 11 9 8 Y 128 0 63 +def test t9 t9 c16 c16 11 10 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 @@ -5067,8 +5067,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longblob, - `const10` double NOT NULL DEFAULT '0', - `param10` double DEFAULT NULL, + `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -5097,8 +5097,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63 -def test t5 t5 const10 const10 5 49 9 N 32769 31 63 -def test t5 t5 param10 param10 5 23 9 Y 32768 31 63 +def test t5 t5 const10 const10 246 24 16 N 1 6 63 +def test t5 t5 param10 param10 246 67 40 Y 0 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -5124,8 +5124,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861 -param10 662680861 +const10 662680861.000000 +param10 662680861.000000000000000000000000000000 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 2817bec2198..2aaaac3349a 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -991,20 +991,17 @@ SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0 +Warning 1292 Incorrect datetime value: '20050327 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 0 +Warning 1292 Incorrect datetime value: '20050328 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0 +Warning 1292 Incorrect datetime value: '20050327 invalid' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index a472772a7e9..5aad036491b 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1104,13 +1104,10 @@ INSERT INTO t1 VALUES EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where -Warnings: -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 0 +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; item started price A1 2005-11-01 08:00:00 1000.000 @@ -1122,7 +1119,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 0 +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; item started price A1 2005-11-01 08:00:00 1000.000 @@ -1572,7 +1569,7 @@ str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 1 Warnings: -Warning 1292 Truncated incorrect date value: '' +Warning 1292 Incorrect datetime value: '' SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND str_to_date('2007-20-00', '%Y-%m-%d') <= ''; str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND @@ -1585,7 +1582,7 @@ SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 Warnings: -Warning 1292 Truncated incorrect date value: '' +Warning 1292 Incorrect datetime value: '' SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' NULL diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 69733162818..afa0d85fba8 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2115,8 +2115,8 @@ INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12 SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time Warnings: -Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 0 -Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 0 +Warning 1292 Incorrect datetime value: 'wrong-date-value' +Warning 1292 Incorrect datetime value: 'wrong-date-value' SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time DROP TABLE t1,t2; @@ -3265,7 +3265,7 @@ f1 f2 4 2005-10-01 5 2005-12-30 Warnings: -Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 +Warning 1292 Truncated incorrect date value: '2005-09-3a' select * from t1 where f2 <= '2005-09-31' order by f2; f1 f2 1 2005-01-01 @@ -3276,7 +3276,7 @@ f1 f2 1 2005-01-01 2 2005-09-01 Warnings: -Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 +Warning 1292 Truncated incorrect date value: '2005-09-3a' drop table t1; create table t1 (f1 int, f2 int); insert into t1 values (1, 30), (2, 20), (3, 10); @@ -3754,15 +3754,11 @@ AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t1 range ts ts 4 NULL 1 Using where -Warnings: -Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 AND t1.ts BETWEEN t2.dt1 AND t2.dt2 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; a ts a dt1 dt2 30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 -Warnings: -Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 DROP TABLE t1,t2; create table t1 (a bigint unsigned); insert into t1 values @@ -4093,28 +4089,28 @@ str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' and '2007/10/20 00:00:00 GMT' 1 Warnings: -Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT' -Warning 1292 Truncated incorrect date value: '2007/10/20 00:00:00 GMT' +Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' +Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 1 Warnings: -Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' +Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT-6' select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6'; str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6' 1 Warnings: -Warning 1292 Truncated incorrect date value: '2007/10/20 00:00:00 GMT-6' +Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT-6' select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 0 Warnings: -Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' +Warning 1292 Incorrect datetime value: '2007/10/2000:00:00 GMT-6' select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 1 Warnings: -Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' +Warning 1292 Truncated incorrect datetime value: '2007-10-1 00:00:00 GMT-6' select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 1 @@ -4134,7 +4130,7 @@ select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 1 Warnings: -Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' +Warning 1292 Truncated incorrect date value: '2007-10-01 x12:34:56 GMT-6' select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 1 @@ -4190,7 +4186,7 @@ select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 1 Warnings: -Warning 1292 Truncated incorrect date value: '' +Warning 1292 Incorrect datetime value: '' select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 0 @@ -4204,22 +4200,22 @@ select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 0 Warnings: -Warning 1292 Truncated incorrect datetime value: '' +Warning 1292 Incorrect datetime value: '' select str_to_date('1','%Y-%m-%d') = '1'; str_to_date('1','%Y-%m-%d') = '1' 0 Warnings: -Warning 1292 Truncated incorrect date value: '1' +Warning 1292 Incorrect datetime value: '1' select str_to_date('1','%Y-%m-%d') = '1'; str_to_date('1','%Y-%m-%d') = '1' 0 Warnings: -Warning 1292 Truncated incorrect date value: '1' +Warning 1292 Incorrect datetime value: '1' select str_to_date('','%Y-%m-%d') = ''; str_to_date('','%Y-%m-%d') = '' 1 Warnings: -Warning 1292 Truncated incorrect date value: '' +Warning 1292 Incorrect datetime value: '' select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01'; str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01' 1 diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result index 1223fff36c6..0f6dac5efd5 100644 --- a/mysql-test/r/timezone.result +++ b/mysql-test/r/timezone.result @@ -7,7 +7,7 @@ select @a:=FROM_UNIXTIME(1); 1970-01-01 01:00:01 select unix_timestamp(@a); unix_timestamp(@a) -1 +1.000000 CREATE TABLE t1 (ts int); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00')); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00')); @@ -44,4 +44,4 @@ unix_timestamp('1970-01-01 01:00:01'), unix_timestamp('2038-01-19 04:14:07'), unix_timestamp('2038-01-19 04:14:08'); unix_timestamp('1970-01-01 01:00:00') unix_timestamp('1970-01-01 01:00:01') unix_timestamp('2038-01-19 04:14:07') unix_timestamp('2038-01-19 04:14:08') -0 1 2147483647 0 +0.000000 1.000000 2147483647.000000 0.000000 diff --git a/mysql-test/r/timezone4.result b/mysql-test/r/timezone4.result index 28028bea657..ad0672890a2 100644 --- a/mysql-test/r/timezone4.result +++ b/mysql-test/r/timezone4.result @@ -3,4 +3,4 @@ from_unixtime(0) 1969-12-31 14:00:00 select unix_timestamp('1969-12-31 14:00:01'); unix_timestamp('1969-12-31 14:00:01') -1 +1.000000 diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 1e6f2de86db..164587db010 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -132,10 +132,10 @@ select year(@d), month(@d), day(@d), cast(@d as date); year(@d) month(@d) day(@d) cast(@d as date) NULL NULL NULL NULL Warnings: -Warning 1292 Incorrect datetime value: '1311' -Warning 1292 Incorrect datetime value: '1311' -Warning 1292 Incorrect datetime value: '1311' -Warning 1292 Incorrect datetime value: '1311' +Warning 1292 Truncated incorrect datetime value: '1311' +Warning 1292 Truncated incorrect datetime value: '1311' +Warning 1292 Truncated incorrect datetime value: '1311' +Warning 1292 Truncated incorrect datetime value: '1311' create table t1 (d date , dt datetime , ts timestamp); insert into t1 values (9912101,9912101,9912101); Warnings: @@ -208,10 +208,14 @@ SELECT * FROM t1 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1 SET SQL_MODE=DEFAULT; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 8868a4b1af8..5c1925bd5e4 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -185,14 +185,14 @@ DROP TABLE t1; SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) 20060810.000000 -SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); -CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) AS DECIMAL(20,6)) 20060810101112.000000 -SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); -CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) 20060810101112.000014 -SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); -CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) +SELECT CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6)); +CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6)) 101112.098700 set @org_mode=@@sql_mode; create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); @@ -361,7 +361,7 @@ greatest(cast('01-01-01' as date), '01-01-02') + 0 20010102 select least(cast('01-01-01' as datetime), '01-01-02') + 0; least(cast('01-01-01' as datetime), '01-01-02') + 0 -20010101000000 +20010101000000.000000 select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed); cast(least(cast('01-01-01' as datetime), '01-01-02') as signed) 20010101000000 @@ -399,7 +399,7 @@ if(@bug28261 = f1, '', @bug28261:= f1) 2001-01-01 2002-02-02 Warnings: -Warning 1292 Incorrect date value: '' for column 'f1' at row 1 +Warning 1292 Incorrect datetime value: '' select if(@bug28261 = f1, '', @bug28261:= f1) from t1; if(@bug28261 = f1, '', @bug28261:= f1) 2001-01-01 @@ -423,11 +423,11 @@ f1 2001-01-01 00:00:00 2002-02-02 00:00:00 Warnings: -Warning 1292 Incorrect datetime value: '2002010' for column 'f1' at row 0 +Warning 1292 Truncated incorrect datetime value: '2002010' select * from t1 where f1 between 20020101 and 2007010100000; f1 Warnings: -Warning 1292 Incorrect datetime value: '2007010100000' for column 'f1' at row 0 +Warning 1292 Truncated incorrect datetime value: '2007010100000' drop table t1; # # Bug#27216: functions with parameters of different date types may @@ -495,9 +495,9 @@ insert into t1 set f1 = '45:44:44'; insert into t1 set f1 = '15:44:44'; select * from t1 where (convert(f1,datetime)) != 1; f1 +45:44:44 15:44:44 Warnings: -Warning 1292 Truncated incorrect datetime value: '45:44:44' Warning 1292 Truncated incorrect datetime value: '1' drop table t1; create table t1 (a tinyint); @@ -620,21 +620,21 @@ ERROR 42000: Invalid default value for 'da' create table t1 (t time default '916:00:00 a'); ERROR 42000: Invalid default value for 't' set @@sql_mode= @org_mode; -SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME) AS DECIMAL(30,7)); -CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME) AS DECIMAL(30,7)) +SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); +CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME(6)) AS DECIMAL(30,7)) 20060810101112.0123450 Warnings: Warning 1292 Truncated incorrect datetime value: '2006-08-10 10:11:12.0123450' -SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME) AS DECIMAL(30,7)); -CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME) AS DECIMAL(30,7)) +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); +CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME(6)) AS DECIMAL(30,7)) 20060810101112.0123450 Warnings: Warning 1292 Truncated incorrect datetime value: '00000002006-000008-0000010 000010:0000011:00000012.0123450' -SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME) AS DECIMAL(30,7)); -CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME) AS DECIMAL(30,7)) +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME(6)) AS DECIMAL(30,7)); +CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME(6)) AS DECIMAL(30,7)) 20060810101112.0123450 -SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7)); -CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7)) +SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime(6)) AS DECIMAL(30,7)); +CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime(6)) AS DECIMAL(30,7)) 20080729104251.1234560 Warnings: Warning 1292 Truncated incorrect datetime value: '2008-07-29T10:42:51.1234567' diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result index 78e634c9e99..1b734b0d3c3 100644 --- a/mysql-test/r/type_datetime_hires.result +++ b/mysql-test/r/type_datetime_hires.result @@ -105,7 +105,105 @@ Table Create Table t3 CREATE TABLE `t3` ( `a` datetime(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2, t3; +insert t1 values ('2010-12-13 14:15:16.222222'); +select a, a+0, a-1, a*1, a/2 from t1; +a a+0 a-1 a*1 a/2 +2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000 +2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000 +select max(a), min(a), sum(a), avg(a) from t1; +max(a) min(a) sum(a) avg(a) +2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000 +create table t2 select a, a+0, a-1, a*1, a/2 from t1; +create table t3 select max(a), min(a), sum(a), avg(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` datetime(4) DEFAULT NULL, + `a+0` decimal(25,4) DEFAULT NULL, + `a-1` decimal(25,4) DEFAULT NULL, + `a*1` decimal(25,4) DEFAULT NULL, + `a/2` decimal(28,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `max(a)` datetime(4) DEFAULT NULL, + `min(a)` datetime(4) DEFAULT NULL, + `sum(a)` decimal(46,4) DEFAULT NULL, + `avg(a)` decimal(28,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; +create table t1 (f0_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(6)); +insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); +select * from t1; +f0_datetime 2010-11-12 11:14:17 +f1_datetime 2010-11-12 11:14:17.7 +f2_datetime 2010-11-12 11:14:17.76 +f3_datetime 2010-11-12 11:14:17.765 +f4_datetime 2010-11-12 11:14:17.7654 +f5_datetime 2010-11-12 11:14:17.76543 +f6_datetime 2010-11-12 11:14:17.765432 +select cast(f0_datetime as time(4)) time4_f0_datetime, cast(f1_datetime as datetime(3)) datetime3_f1_datetime, cast(f2_datetime as date) date_f2_datetime, cast(f4_datetime as decimal(40,5)) decimal5_f4_datetime, cast(f5_datetime as signed) bigint_f5_datetime, cast(f6_datetime as char(255)) varchar_f6_datetime from t1; +time4_f0_datetime 11:14:17.0000 +datetime3_f1_datetime 2010-11-12 11:14:17.700 +date_f2_datetime 2010-11-12 +decimal5_f4_datetime 20101112111417.76540 +bigint_f5_datetime 20101112111417 +varchar_f6_datetime 2010-11-12 11:14:17.765432 +create table t2 (time4_f0_datetime time(4), datetime3_f1_datetime datetime(3), date_f2_datetime date, double_f3_datetime double, decimal5_f4_datetime decimal(40,5), bigint_f5_datetime bigint, varchar_f6_datetime varchar(255)); +insert t2 select * from t1; +Warnings: +Level Note +Code 1265 +Message Data truncated for column 'time4_f0_datetime' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'date_f2_datetime' at row 1 +select * from t2; +time4_f0_datetime 11:14:17.0000 +datetime3_f1_datetime 2010-11-12 11:14:17.700 +date_f2_datetime 2010-11-12 +double_f3_datetime 20101112111417.8 +decimal5_f4_datetime 20101112111417.76540 +bigint_f5_datetime 20101112111417 +varchar_f6_datetime 2010-11-12 11:14:17.765432 +alter table t1 change f0_datetime time4_f0_datetime time(4), change f1_datetime datetime3_f1_datetime datetime(3), change f2_datetime date_f2_datetime date, change f3_datetime double_f3_datetime double, change f4_datetime decimal5_f4_datetime decimal(40,5), change f5_datetime bigint_f5_datetime bigint, change f6_datetime varchar_f6_datetime varchar(255); +Warnings: +Level Note +Code 1265 +Message Data truncated for column 'time4_f0_datetime' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'date_f2_datetime' at row 1 +select * from t1; +time4_f0_datetime 11:14:17.0000 +datetime3_f1_datetime 2010-11-12 11:14:17.700 +date_f2_datetime 2010-11-12 +double_f3_datetime 20101112111417.8 +decimal5_f4_datetime 20101112111417.76540 +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 +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 +decimal5_f4_datetime 2010-11-12 11:14:17.7654 +bigint_f5_datetime 2010-11-12 11:14:17.00000 +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 +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 +decimal5_f4_datetime 2010-11-12 11:14:17.7654 +bigint_f5_datetime 2010-11-12 11:14:17.00000 +varchar_f6_datetime 2010-11-12 11:14:17.765432 +drop table t1, t2; create table t1 (a datetime(6), b datetime(6)); create procedure foo(x datetime, y datetime(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); @@ -218,132 +316,6 @@ t2 p01 RANGE extract(microsecond from taken) 123000 3 t2 p02 RANGE extract(microsecond from taken) 500000 4 t2 p03 RANGE extract(microsecond from taken) MAXVALUE 3 drop table t1, t2; -create table t1 ( -q_date date, -q_time time, -q_time5 time(5), -q_datetime datetime, -q_datetime1 datetime(1), -q_datetime3 datetime(3), -q_datetime5 datetime(5), -q_timestamp timestamp, -q_timestamp2 timestamp(2), -q_timestamp4 timestamp(4), -q_timestamp6 timestamp(6), -q_varchar50 varchar(50), -q_varchar60 varchar(60), -q_varchar70 varchar(70), -q_varchar80 varchar(80)); -create table t2 ( -date_datetime datetime, -time_datetime datetime, -time5_varchar100 varchar(100), -datetime_time time, -datetime1_date date, -datetime3_timestamp timestamp, -datetime5_varchar100 varchar(100), -timestamp_datetime datetime, -timestamp2_date date, -timestamp4_time time, -timestamp6_varchar100 varchar(100), -varchar50_date date, -varchar60_datetime datetime, -varchar70_time time, -varchar80_timestamp timestamp); -insert t1 values ('2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', -'2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); -Warnings: -Note 1265 Data truncated for column 'q_date' at row 1 -Note 1265 Data truncated for column 'q_time' at row 1 -Note 1265 Data truncated for column 'q_time5' at row 1 -select * from t1;; -q_date 2010-11-12 -q_time 11:14:17 -q_time5 11:14:17.76543 -q_datetime 2010-11-12 11:14:17 -q_datetime1 2010-11-12 11:14:17.7 -q_datetime3 2010-11-12 11:14:17.765 -q_datetime5 2010-11-12 11:14:17.76543 -q_timestamp 2010-11-12 11:14:17 -q_timestamp2 2010-11-12 11:14:17.76 -q_timestamp4 2010-11-12 11:14:17.7654 -q_timestamp6 2010-11-12 11:14:17.765432 -q_varchar50 2010-11-12 11:14:17.765432 -q_varchar60 2010-11-12 11:14:17.765432 -q_varchar70 2010-11-12 11:14:17.765432 -q_varchar80 2010-11-12 11:14:17.765432 -insert t2 select * from t1; -Warnings: -Warning 1265 Data truncated for column 'time_datetime' at row 1 -Note 1265 Data truncated for column 'datetime_time' at row 1 -Note 1265 Data truncated for column 'datetime1_date' at row 1 -Note 1265 Data truncated for column 'timestamp2_date' at row 1 -Note 1265 Data truncated for column 'timestamp4_time' at row 1 -Note 1265 Data truncated for column 'varchar50_date' at row 1 -Note 1265 Data truncated for column 'varchar70_time' at row 1 -select * from t2;; -date_datetime 2010-11-12 00:00:00 -time_datetime 0000-00-00 00:00:00 -time5_varchar100 11:14:17.76543 -datetime_time 11:14:17 -datetime1_date 2010-11-12 -datetime3_timestamp 2010-11-12 11:14:17 -datetime5_varchar100 2010-11-12 11:14:17.76543 -timestamp_datetime 2010-11-12 11:14:17 -timestamp2_date 2010-11-12 -timestamp4_time 11:14:17 -timestamp6_varchar100 2010-11-12 11:14:17.765432 -varchar50_date 2010-11-12 -varchar60_datetime 2010-11-12 11:14:17 -varchar70_time 11:14:17 -varchar80_timestamp 2010-11-12 11:14:17 -alter table t1 -change q_date date_datetime datetime, -change q_time time_datetime datetime, -change q_time5 time5_varchar100 varchar(100), -change q_datetime datetime_time time, -change q_datetime1 datetime1_date date, -change q_datetime3 datetime3_timestamp timestamp, -change q_datetime5 datetime5_varchar100 varchar(100), -change q_timestamp timestamp_datetime datetime, -change q_timestamp2 timestamp2_date date, -change q_timestamp4 timestamp4_time time, -change q_timestamp6 timestamp6_varchar100 varchar(100), -change q_varchar50 varchar50_date date, -change q_varchar60 varchar60_datetime datetime, -change q_varchar70 varchar70_time time, -change q_varchar80 varchar80_timestamp timestamp; -Warnings: -Warning 1265 Data truncated for column 'time_datetime' at row 1 -Note 1265 Data truncated for column 'datetime_time' at row 1 -Note 1265 Data truncated for column 'datetime1_date' at row 1 -Note 1265 Data truncated for column 'timestamp2_date' at row 1 -Note 1265 Data truncated for column 'timestamp4_time' at row 1 -Note 1265 Data truncated for column 'varchar50_date' at row 1 -Note 1265 Data truncated for column 'varchar70_time' at row 1 -select * from t1;; -date_datetime 2010-11-12 00:00:00 -time_datetime 0000-00-00 00:00:00 -time5_varchar100 11:14:17.76543 -datetime_time 11:14:17 -datetime1_date 2010-11-12 -datetime3_timestamp 2010-11-12 11:14:17 -datetime5_varchar100 2010-11-12 11:14:17.76543 -timestamp_datetime 2010-11-12 11:14:17 -timestamp2_date 2010-11-12 -timestamp4_time 11:14:17 -timestamp6_varchar100 2010-11-12 11:14:17.765432 -varchar50_date 2010-11-12 -varchar60_datetime 2010-11-12 11:14:17 -varchar70_time 11:14:17 -varchar80_timestamp 2010-11-12 11:14:17 -drop table t1, t2; create table t1 (a datetime, b datetime(6)); insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912'); update t1 set b=a; diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result index eb21d1773ff..13975beeb2c 100644 --- a/mysql-test/r/type_time_hires.result +++ b/mysql-test/r/type_time_hires.result @@ -94,14 +94,15 @@ a 01:02:13.3332 insert t1 select a + interval 2 year from t1; Warnings: -Note 1265 Data truncated for column 'a' at row 1 +Warning 1441 Datetime function: time field overflow select * from t1; a 01:02:13.3332 -01:02:13.3332 +NULL delete from t1 where a < 20110101; select * from t1; a +NULL create table t2 select * from t1; create table t3 like t1; show create table t2; @@ -114,7 +115,123 @@ Table Create Table t3 CREATE TABLE `t3` ( `a` time(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2, t3; +insert t1 values ('2010-12-13 14:15:16.222222'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +select a, a+0, a-1, a*1, a/2 from t1; +a a+0 a-1 a*1 a/2 +NULL NULL NULL NULL NULL +14:15:16.2222 141516.2222 141515.2222 141516.2222 70758.11110000 +select max(a), min(a), sum(a), avg(a) from t1; +max(a) min(a) sum(a) avg(a) +14:15:16.2222 14:15:16.2222 141516.2222 141516.22220000 +create table t2 select a, a+0, a-1, a*1, a/2 from t1; +create table t3 select max(a), min(a), sum(a), avg(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` time(4) DEFAULT NULL, + `a+0` decimal(16,4) DEFAULT NULL, + `a-1` decimal(16,4) DEFAULT NULL, + `a*1` decimal(16,4) DEFAULT NULL, + `a/2` decimal(19,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `max(a)` time(4) DEFAULT NULL, + `min(a)` time(4) DEFAULT NULL, + `sum(a)` decimal(37,4) DEFAULT NULL, + `avg(a)` decimal(19,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; +create table t1 (f0_time time(0), f1_time time(1), f2_time time(2), f3_time time(3), f4_time time(4), f5_time time(5), f6_time time(6)); +insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); +Warnings: +Level Note +Code 1265 +Message Data truncated for column 'f0_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f1_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f2_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f3_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f4_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f5_time' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'f6_time' at row 1 +select * from t1; +f0_time 11:14:17 +f1_time 11:14:17.7 +f2_time 11:14:17.76 +f3_time 11:14:17.765 +f4_time 11:14:17.7654 +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 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 +decimal5_f4_time 111417.76540 +bigint_f5_time 111417 +varchar_f6_time 11:14:17.765432 +create table t2 (time4_f0_time time(4), datetime3_f1_time datetime(3), date_f2_time date, double_f3_time double, decimal5_f4_time decimal(40,5), bigint_f5_time bigint, varchar_f6_time varchar(255)); +insert t2 select * from t1; +Warnings: +Level Note +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 +double_f3_time 111417.765 +decimal5_f4_time 111417.76540 +bigint_f5_time 111417 +varchar_f6_time 11:14:17.765432 +alter table t1 change f0_time time4_f0_time time(4), change f1_time datetime3_f1_time datetime(3), change f2_time date_f2_time date, change f3_time double_f3_time double, change f4_time decimal5_f4_time decimal(40,5), change f5_time bigint_f5_time bigint, change f6_time varchar_f6_time varchar(255); +Warnings: +Level Note +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 +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); +select * from t1; +time4_f0_time 11:14:17 +datetime3_f1_time 11:14:17.7 +date_f2_time 00:00:00.00 +double_f3_time 11:14:17.764 +decimal5_f4_time 11:14:17.7654 +bigint_f5_time 11:14:17.00000 +varchar_f6_time 11:14:17.765432 +delete from t1; +insert t1 select * from t2; +select * from t1; +time4_f0_time 11:14:17 +datetime3_f1_time 11:14:17.7 +date_f2_time 00:00:00.00 +double_f3_time 11:14:17.764 +decimal5_f4_time 11:14:17.7654 +bigint_f5_time 11:14:17.00000 +varchar_f6_time 11:14:17.765432 +drop table t1, t2; create table t1 (a time(6), b time(6)); create procedure foo(x time, y time(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); @@ -169,18 +286,37 @@ a b 04:05:06.000000 04:05:06.789100 drop view v1; drop table t1, t2; -create table t1 (a time(4) not null); -insert into t1 values ('-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; +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; +a +-00:00:01.1000 +-00:00:01.1000 +-00:00:01.0900 +-00:00:01.0000 +-00:00:01.0000 +-00:00:01.0000 +-00:00:00.9000 +-00:00:00.8000 +-00:00:00.7000 +-00:00:00.6000 +01:02:03.0000 +01:02:03.0010 +select * from t1 order by a desc; a +01:02:03.0010 +01:02:03.0000 -00:00:00.6000 -00:00:00.7000 -00:00:00.8000 -00:00:00.9000 -00:00:01.0000 --00:00:01.1000 -00:00:01.0000 --00:00:01.1000 -00:00:01.0000 -00:00:01.0900 +-00:00:01.1000 +-00:00:01.1000 drop table t1; +select cast(1e-6 as time(6)); +cast(1e-6 as time(6)) +00:00:00.000001 diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index 75b6b60e4d5..d1702325633 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -105,7 +105,113 @@ Table Create Table t3 CREATE TABLE `t3` ( `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2, t3; +insert t1 values ('2010-12-13 14:15:16.222222'); +select a, a+0, a-1, a*1, a/2 from t1; +a a+0 a-1 a*1 a/2 +2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000 +2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000 +select max(a), min(a), sum(a), avg(a) from t1; +max(a) min(a) sum(a) avg(a) +2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000 +create table t2 select a, a+0, a-1, a*1, a/2 from t1; +create table t3 select max(a), min(a), sum(a), avg(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000', + `a+0` decimal(25,4) NOT NULL DEFAULT '0.0000', + `a-1` decimal(25,4) NOT NULL DEFAULT '0.0000', + `a*1` decimal(25,4) NOT NULL DEFAULT '0.0000', + `a/2` decimal(28,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `max(a)` timestamp(4) NULL DEFAULT NULL, + `min(a)` timestamp(4) NULL DEFAULT NULL, + `sum(a)` decimal(46,4) DEFAULT NULL, + `avg(a)` decimal(28,8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; +create table t1 (f0_timestamp timestamp(0), f1_timestamp timestamp(1), f2_timestamp timestamp(2), f3_timestamp timestamp(3), f4_timestamp timestamp(4), f5_timestamp timestamp(5), f6_timestamp timestamp(6)); +insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); +select * from t1; +f0_timestamp 2010-11-12 11:14:17 +f1_timestamp 2010-11-12 11:14:17.7 +f2_timestamp 2010-11-12 11:14:17.76 +f3_timestamp 2010-11-12 11:14:17.765 +f4_timestamp 2010-11-12 11:14:17.7654 +f5_timestamp 2010-11-12 11:14:17.76543 +f6_timestamp 2010-11-12 11:14:17.765432 +select cast(f0_timestamp as time(4)) time4_f0_timestamp, cast(f1_timestamp as datetime(3)) datetime3_f1_timestamp, cast(f2_timestamp as date) date_f2_timestamp, cast(f4_timestamp as decimal(40,5)) decimal5_f4_timestamp, cast(f5_timestamp as signed) bigint_f5_timestamp, cast(f6_timestamp as char(255)) varchar_f6_timestamp from t1; +time4_f0_timestamp 11:14:17.0000 +datetime3_f1_timestamp 2010-11-12 11:14:17.700 +date_f2_timestamp 2010-11-12 +decimal5_f4_timestamp 20101112111417.76540 +bigint_f5_timestamp 20101112111417 +varchar_f6_timestamp 2010-11-12 11:14:17.765432 +create table t2 (time4_f0_timestamp time(4), datetime3_f1_timestamp datetime(3), date_f2_timestamp date, double_f3_timestamp double, decimal5_f4_timestamp decimal(40,5), bigint_f5_timestamp bigint, varchar_f6_timestamp varchar(255)); +insert t2 select * from t1; +Warnings: +Level Note +Code 1265 +Message Data truncated for column 'time4_f0_timestamp' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'date_f2_timestamp' at row 1 +select * from t2; +time4_f0_timestamp 11:14:17.0000 +datetime3_f1_timestamp 2010-11-12 11:14:17.700 +date_f2_timestamp 2010-11-12 +double_f3_timestamp 20101112111417.8 +decimal5_f4_timestamp 20101112111417.76540 +bigint_f5_timestamp 20101112111417 +varchar_f6_timestamp 2010-11-12 11:14:17.765432 +alter table t1 change f0_timestamp time4_f0_timestamp time(4), change f1_timestamp datetime3_f1_timestamp datetime(3), change f2_timestamp date_f2_timestamp date, change f3_timestamp double_f3_timestamp double, change f4_timestamp decimal5_f4_timestamp decimal(40,5), change f5_timestamp bigint_f5_timestamp bigint, change f6_timestamp varchar_f6_timestamp varchar(255); +Warnings: +Level Note +Code 1265 +Message Data truncated for column 'time4_f0_timestamp' at row 1 +Level Note +Code 1265 +Message Data truncated for column 'date_f2_timestamp' at row 1 +select * from t1; +time4_f0_timestamp 11:14:17.0000 +datetime3_f1_timestamp 2010-11-12 11:14:17.700 +date_f2_timestamp 2010-11-12 +double_f3_timestamp 20101112111417.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 +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 +decimal5_f4_timestamp 2010-11-12 11:14:17.7654 +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 +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 +decimal5_f4_timestamp 2010-11-12 11:14:17.7654 +bigint_f5_timestamp 2010-11-12 11:14:17.00000 +varchar_f6_timestamp 2010-11-12 11:14:17.765432 +drop table t1, t2; create table t1 (a timestamp(6), b timestamp(6)); create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 9b2672328a4..d2ad407bc78 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2516,7 +2516,7 @@ INSERT INTO t1 VALUES (0); SET SQL_MODE='STRICT_ALL_TABLES'; CREATE TABLE t2 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; -ERROR 22007: Truncated incorrect datetime value: '' +ERROR 22007: Incorrect datetime value: '' DROP TABLE t1; SET SQL_MODE=DEFAULT; # diff --git a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result index c510b372d99..19eb2aef6c1 100644 --- a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result +++ b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result @@ -140,7 +140,7 @@ create table t3 select 1 union select UUID(); create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3); create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); Warnings: -Warning 1292 Truncated incorrect date value: '3' +Warning 1292 Truncated incorrect datetime value: '3' insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4); create procedure foo() begin diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 72a78f612a2..b00bd84a3a6 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1063,7 +1063,8 @@ insert into t1 values (02,2002,20020101,"2002-01-01 23:59:59"), (60,2060,20600101,"2060-01-01 11:11:11"), (70,1970,19700101,"1970-11-11 22:22:22"), - (NULL,NULL,NULL,NULL); + (NULL,NULL,NULL,NULL), + (71,1971,19710101,"1971-11-11 22:22:22"); select min(f1),max(f1) from t1; select min(f2),max(f2) from t1; select min(f3),max(f3) from t1; diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index 1292c475732..5c31cee403a 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -91,8 +91,8 @@ select microsecond("1997-12-31 23:59:59.000001"); create table t1 select makedate(1997,1) as f1, - addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2, - addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3, + addtime(cast("1997-12-31 23:59:59.000001" as datetime(6)), "1 1:1:1.000002") as f2, + addtime(cast("23:59:59.999999" as time(6)) , "1 1:1:1.000002") as f3, timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, maketime(10,11,12) as f6, diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 6570cf031a1..fea3d77003e 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -17,6 +17,9 @@ select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); select sec_to_time(time_to_sec('-838:59:59')); +select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); +select sec_to_time(90011e-1), sec_to_time(1234567890123e30); +select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999'); select now()-curdate()*1000000-curtime(); select strcmp(current_timestamp(),concat(current_date()," ",current_time())); select strcmp(localtime(),concat(current_date()," ",current_time())); @@ -67,10 +70,10 @@ select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); select dayname("1962-03-03"),dayname("1962-03-03")+0; select monthname("1972-03-04"),monthname("1972-03-04")+0; -select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +select time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +select time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +select time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w'); select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w'); select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); @@ -882,6 +885,9 @@ select time(' 1 02:03:04') - interval 2 day; select time('-1 02:03:04') + interval 2 day; select time('-1 02:03:04') - interval 2 day; +select time('10 02:03:04') + interval 30 day; +select time('10 02:03:04') + interval 1 year; + # specially constructed queries to reach obscure places in the code # not touched by the more "normal" queries (and to increase the coverage) select cast('131415.123e0' as time); diff --git a/mysql-test/t/func_time_hires.test b/mysql-test/t/func_time_hires.test index b9cb6a92e9e..9d2dbfbaeb5 100644 --- a/mysql-test/t/func_time_hires.test +++ b/mysql-test/t/func_time_hires.test @@ -65,6 +65,8 @@ create table t1 select CAST(@a AS DATETIME) as dauto, show create table t1; --query_vertical select * from t1 drop table t1; +explain extended select cast(cast(@a as datetime(4)) as time(0)); +select cast(cast(@a as time(2)) as time(6)); --error ER_TOO_BIG_PRECISION select CAST(@a AS DATETIME(7)); diff --git a/mysql-test/t/parser_precedence.test b/mysql-test/t/parser_precedence.test index 484c8759779..7b69bc9c6da 100644 --- a/mysql-test/t/parser_precedence.test +++ b/mysql-test/t/parser_precedence.test @@ -3,6 +3,8 @@ drop table if exists t1_30237_bool; --enable_warnings +set sql_mode=NO_UNSIGNED_SUBTRACTION; + create table t1_30237_bool(A boolean, B boolean, C boolean); insert into t1_30237_bool values diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index d4fa6bed186..1c0e34fad48 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -137,9 +137,9 @@ DROP TABLE t1; # Bug 19491 (CAST DATE AS DECIMAL returns incorrect result # SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); -SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); -SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); -SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6)); # @@ -432,17 +432,17 @@ set @@sql_mode= @org_mode; # Bug #42146 - DATETIME fractional seconds parse error # # show we trucate microseconds from the right -- special case: leftmost is 0 -SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME) AS DECIMAL(30,7)); +SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); # show that we ignore leading zeroes for all other fields -SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME) AS DECIMAL(30,7)); +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); # once more with feeling (but no warnings) -SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME) AS DECIMAL(30,7)); +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME(6)) AS DECIMAL(30,7)); # # Bug #38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE # # show we truncate microseconds from the right -SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7)); +SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime(6)) AS DECIMAL(30,7)); --echo End of 5.1 tests diff --git a/mysql-test/t/type_datetime_hires.test b/mysql-test/t/type_datetime_hires.test index d4ed0e342e6..613f94d52f2 100644 --- a/mysql-test/t/type_datetime_hires.test +++ b/mysql-test/t/type_datetime_hires.test @@ -61,74 +61,6 @@ select table_name,partition_name,partition_method,partition_expression,partition drop table t1, t2; -# -# insert ... select with conversion -# -create table t1 ( - q_date date, - q_time time, - q_time5 time(5), - q_datetime datetime, - q_datetime1 datetime(1), - q_datetime3 datetime(3), - q_datetime5 datetime(5), - q_timestamp timestamp, - q_timestamp2 timestamp(2), - q_timestamp4 timestamp(4), - q_timestamp6 timestamp(6), - q_varchar50 varchar(50), - q_varchar60 varchar(60), - q_varchar70 varchar(70), - q_varchar80 varchar(80)); - -create table t2 ( - date_datetime datetime, - time_datetime datetime, - time5_varchar100 varchar(100), - datetime_time time, - datetime1_date date, - datetime3_timestamp timestamp, - datetime5_varchar100 varchar(100), - timestamp_datetime datetime, - timestamp2_date date, - timestamp4_time time, - timestamp6_varchar100 varchar(100), - varchar50_date date, - varchar60_datetime datetime, - varchar70_time time, - varchar80_timestamp timestamp); - -insert t1 values ('2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', - '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); ---query_vertical select * from t1; -insert t2 select * from t1; ---query_vertical select * from t2; -alter table t1 - change q_date date_datetime datetime, - change q_time time_datetime datetime, - change q_time5 time5_varchar100 varchar(100), - change q_datetime datetime_time time, - change q_datetime1 datetime1_date date, - change q_datetime3 datetime3_timestamp timestamp, - change q_datetime5 datetime5_varchar100 varchar(100), - change q_timestamp timestamp_datetime datetime, - change q_timestamp2 timestamp2_date date, - change q_timestamp4 timestamp4_time time, - change q_timestamp6 timestamp6_varchar100 varchar(100), - change q_varchar50 varchar50_date date, - change q_varchar60 varchar60_datetime datetime, - change q_varchar70 varchar70_time time, - change q_varchar80 varchar80_timestamp timestamp; ---query_vertical select * from t1; - -drop table t1, t2; - create table t1 (a datetime, b datetime(6)); insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912'); update t1 set b=a; diff --git a/mysql-test/t/type_time_hires.test b/mysql-test/t/type_time_hires.test index c9939289e43..92ad79523c3 100644 --- a/mysql-test/t/type_time_hires.test +++ b/mysql-test/t/type_time_hires.test @@ -2,7 +2,10 @@ let type=time; --source include/type_hrtime.inc -create table t1 (a time(4) not null); -insert into t1 values ('-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; +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; +select * from t1 order by a desc; drop table t1; + +select cast(1e-6 as time(6)); diff --git a/mysys/my_getsystime.c b/mysys/my_getsystime.c index 1e713ea26c3..70325eaa230 100644 --- a/mysys/my_getsystime.c +++ b/mysys/my_getsystime.c @@ -86,7 +86,7 @@ my_hrtime_t my_hrtime() #elif defined(HAVE_CLOCK_GETTIME) struct timespec tp; clock_gettime(CLOCK_REALTIME, &tp); - return tp.tv_sec*1000000ULL+tp.tv_nsec/1000ULL; + hrtime.val= tp.tv_sec*1000000ULL+tp.tv_nsec/1000ULL; #else struct timeval t; /* The following loop is here because gettimeofday may fail on some systems */ diff --git a/sql-common/my_time.c b/sql-common/my_time.c index 264fc5b957b..6958c8e3517 100644 --- a/sql-common/my_time.c +++ b/sql-common/my_time.c @@ -18,6 +18,7 @@ #include <m_ctype.h> /* Windows version of localtime_r() is declared in my_ptrhead.h */ #include <my_pthread.h> +#include <mysqld_error.h> ulonglong log_10_int[20]= { @@ -730,7 +731,7 @@ void my_init_time(void) time_t seconds; struct tm *l_time,tm_tmp; MYSQL_TIME my_time; - my_bool not_used; + uint not_used; seconds= (time_t) time((time_t*) 0); localtime_r(&seconds,&tm_tmp); @@ -811,7 +812,11 @@ long calc_daynr(uint year,uint month,uint day) t - time value to be converted my_timezone - pointer to long where offset of system time zone from UTC will be stored for caching - in_dst_time_gap - set to true if time falls into spring time-gap + error_code - 0, if the conversion was successful; + ER_WARN_DATA_OUT_OF_RANGE, if t contains datetime value + which is out of TIMESTAMP range; + ER_WARN_INVALID_TIMESTAMP, if t represents value which + doesn't exists (falls into the spring time-gap). NOTES The idea is to cache the time zone offset from UTC (including daylight @@ -825,8 +830,7 @@ long calc_daynr(uint year,uint month,uint day) Time in UTC seconds since Unix Epoch representation. */ my_time_t -my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, - my_bool *in_dst_time_gap) +my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, uint *error_code) { uint loop; time_t tmp= 0; @@ -843,7 +847,11 @@ my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, memcpy(&tmp_time, t_src, sizeof(MYSQL_TIME)); if (!validate_timestamp_range(t)) + { + *error_code= ER_WARN_DATA_OUT_OF_RANGE; return 0; + } + *error_code= 0; /* Calculate the gmt time based on current time and timezone @@ -989,7 +997,7 @@ my_system_gmt_sec(const MYSQL_TIME *t_src, long *my_timezone, else if (diff == -3600) tmp-=t->minute*60 + t->second; /* Move to previous hour */ - *in_dst_time_gap= 1; + *error_code= ER_WARN_INVALID_TIMESTAMP; } *my_timezone= current_timezone; @@ -1130,7 +1138,7 @@ int my_TIME_to_str(const MYSQL_TIME *l_time, char *to, int digits) Datetime value in YYYYMMDDHHMMSS format. */ -longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, +longlong number_to_datetime(longlong nr, ulong sec_part, MYSQL_TIME *time_res, uint flags, int *was_cut) { long part1,part2; @@ -1139,7 +1147,7 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, bzero((char*) time_res, sizeof(*time_res)); time_res->time_type=MYSQL_TIMESTAMP_DATE; - if (nr == LL(0) || nr >= LL(10000101000000)) + if (nr == 0 || nr >= 10000101000000LL || sec_part) { time_res->time_type=MYSQL_TIMESTAMP_DATETIME; goto ok; @@ -1189,11 +1197,13 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, time_res->hour= (int) (part2/10000L); part2%=10000L; time_res->minute=(int) part2 / 100; time_res->second=(int) part2 % 100; + time_res->second_part= sec_part; if (time_res->year <= 9999 && time_res->month <= 12 && time_res->day <= 31 && time_res->hour <= 23 && time_res->minute <= 59 && time_res->second <= 59 && - !check_date(time_res, (nr != 0), flags, was_cut)) + sec_part <= TIME_MAX_SECOND_PART && + !check_date(time_res, nr || sec_part, flags, was_cut)) return nr; /* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */ @@ -1206,7 +1216,7 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, } /* - Convert a double to a MYSQL_TIME struct. + Convert a pair of integers to a MYSQL_TIME struct. @param[in] nr a number to convert @param[out] ltime Date to check. @@ -1223,28 +1233,27 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, 0 time value is valid, but was possibly truncated 1 time value is invalid */ -int number_to_time(double nr, MYSQL_TIME *ltime, int *was_cut) +int number_to_time(my_bool neg, longlong nr, ulong sec_part, + MYSQL_TIME *ltime, int *was_cut) { - ulong tmp; *was_cut= 0; ltime->year= ltime->month= ltime->day= 0; ltime->time_type= MYSQL_TIMESTAMP_TIME; - if ((ltime->neg= nr < 0)) - nr= -nr; + ltime->neg= neg; if (nr > TIME_MAX_VALUE) { nr= TIME_MAX_VALUE; + sec_part= TIME_MAX_SECOND_PART; *was_cut= MYSQL_TIME_WARN_OUT_OF_RANGE; } - tmp=(ulong)floor(nr); - ltime->hour = tmp/100/100; - ltime->minute= tmp/100%100; - ltime->second= tmp%100; - ltime->second_part= (ulong)((nr-tmp)*TIME_SECOND_PART_FACTOR); + ltime->hour = nr/100/100; + ltime->minute= nr/100%100; + ltime->second= nr%100; + ltime->second_part= sec_part; - if (ltime->minute < 60 && ltime->second < 60) + if (ltime->minute < 60 && ltime->second < 60 && sec_part <= TIME_MAX_SECOND_PART) return 0; *was_cut= MYSQL_TIME_WARN_TRUNCATED; diff --git a/sql/event_data_objects.cc b/sql/event_data_objects.cc index da502b9e639..5ec09e9b0d3 100644 --- a/sql/event_data_objects.cc +++ b/sql/event_data_objects.cc @@ -464,7 +464,7 @@ Event_queue_element::load_from_row(THD *thd, TABLE *table) DBUG_RETURN(TRUE); starts_null= table->field[ET_FIELD_STARTS]->is_null(); - my_bool not_used= FALSE; + uint not_used; if (!starts_null) { table->field[ET_FIELD_STARTS]->get_date(&time, TIME_NO_ZERO_DATE); @@ -646,7 +646,7 @@ add_interval(MYSQL_TIME *ltime, const Time_zone *time_zone, if (date_add_interval(ltime, scale, interval)) return 0; - my_bool not_used; + uint not_used; return time_zone->TIME_to_gmt_sec(ltime, ¬_used); } diff --git a/sql/event_db_repository.cc b/sql/event_db_repository.cc index bf17c94df3e..678a20aaa47 100644 --- a/sql/event_db_repository.cc +++ b/sql/event_db_repository.cc @@ -280,7 +280,7 @@ mysql_event_fill_row(THD *thd, my_tz_OFFSET0->gmt_sec_to_TIME(&time, et->starts); fields[ET_FIELD_STARTS]->set_notnull(); - fields[ET_FIELD_STARTS]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_STARTS]->store_time(&time); } if (!et->ends_null) @@ -289,7 +289,7 @@ mysql_event_fill_row(THD *thd, my_tz_OFFSET0->gmt_sec_to_TIME(&time, et->ends); fields[ET_FIELD_ENDS]->set_notnull(); - fields[ET_FIELD_ENDS]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_ENDS]->store_time(&time); } } else if (et->execute_at) @@ -308,8 +308,7 @@ mysql_event_fill_row(THD *thd, my_tz_OFFSET0->gmt_sec_to_TIME(&time, et->execute_at); fields[ET_FIELD_EXECUTE_AT]->set_notnull(); - fields[ET_FIELD_EXECUTE_AT]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_EXECUTE_AT]->store_time(&time); } else { @@ -1077,8 +1076,7 @@ update_timing_fields_for_event(THD *thd, my_tz_OFFSET0->gmt_sec_to_TIME(&time, last_executed); fields[ET_FIELD_LAST_EXECUTED]->set_notnull(); - fields[ET_FIELD_LAST_EXECUTED]->store_time(&time, - MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_LAST_EXECUTED]->store_time(&time); } if (update_status) { diff --git a/sql/field.cc b/sql/field.cc index 87fd7317e9b..ccf8ae14b9a 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -80,7 +80,7 @@ const char field_separator=','; #define FIELDTYPE_TEAR_FROM (MYSQL_TYPE_BIT + 1) #define FIELDTYPE_TEAR_TO (MYSQL_TYPE_NEWDECIMAL - 1) #define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (255 - FIELDTYPE_TEAR_TO)) -inline int field_type2index (enum_field_types field_type) +static inline int field_type2index (enum_field_types field_type) { return (field_type < FIELDTYPE_TEAR_FROM ? field_type : @@ -1740,11 +1740,11 @@ bool Field::get_date(MYSQL_TIME *ltime,uint fuzzydate) Needs to be changed if/when we want to support different time formats. */ -int Field::store_time(MYSQL_TIME *ltime, timestamp_type type_arg) +int Field::store_time_dec(MYSQL_TIME *ltime, uint dec) { ASSERT_COLUMN_MARKED_FOR_WRITE; char buff[MAX_DATE_STRING_REP_LENGTH]; - uint length= (uint) my_TIME_to_str(ltime, buff, decimals()); + uint length= (uint) my_TIME_to_str(ltime, buff, dec); return store(buff, length, &my_charset_bin); } @@ -2734,10 +2734,10 @@ int Field_new_decimal::store_decimal(const my_decimal *decimal_value) } -int Field_new_decimal::store_time(MYSQL_TIME *ltime, timestamp_type t_type) +int Field_new_decimal::store_time_dec(MYSQL_TIME *ltime, uint dec) { - my_decimal decimal_value; - return store_value(date2my_decimal(ltime, &decimal_value)); + my_decimal decimal_value; + return store_value(date2my_decimal(ltime, &decimal_value)); } @@ -2942,6 +2942,15 @@ Field_new_decimal::unpack(uchar* to, const uchar *from, uint param_data) return from+len; } +int Field_num::store_time_dec(MYSQL_TIME *ltime, uint dec) +{ + longlong v= TIME_to_ulonglong(ltime); + if (ltime->neg == 0) + return store(v, true); + return store(-v, false); +} + + /**************************************************************************** ** tiny int ****************************************************************************/ @@ -4187,6 +4196,12 @@ int Field_real::store_decimal(const my_decimal *dm) return store(dbl); } +int Field_real::store_time_dec(MYSQL_TIME *ltime, uint dec) +{ + return store(TIME_to_double(ltime)); +} + + double Field_double::val_real(void) { ASSERT_COLUMN_MARKED_FOR_READ; @@ -4236,6 +4251,15 @@ my_decimal *Field_real::val_decimal(my_decimal *decimal_value) } +bool Field_real::get_date(MYSQL_TIME *ltime,uint fuzzydate) +{ + ASSERT_COLUMN_MARKED_FOR_READ; + double nr= val_real(); + return double_to_datetime_with_warn(nr, ltime, fuzzydate, + MYSQL_TYPE_SET, field_name); +} + + String *Field_double::val_str(String *val_buffer, String *val_ptr __attribute__((unused))) { @@ -4500,6 +4524,8 @@ int Field_timestamp::store_TIME_with_warning(THD *thd, MYSQL_TIME *l_time, { uint conversion_error; timestamp= TIME_to_timestamp(thd, l_time, &conversion_error); + if (timestamp == 0 && l_time->second_part == 0) + conversion_error= ER_WARN_DATA_OUT_OF_RANGE; if (conversion_error) { set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, conversion_error, @@ -4516,7 +4542,7 @@ int Field_timestamp::store_TIME_with_warning(THD *thd, MYSQL_TIME *l_time, return error; } -int Field_timestamp::store_time(MYSQL_TIME *ltime,timestamp_type time_type) +int Field_timestamp::store_time_dec(MYSQL_TIME *ltime, uint dec) { THD *thd= table->in_use; int unused; @@ -4558,10 +4584,10 @@ int Field_timestamp::store(double nr) if (nr < 0 || nr > LONGLONG_MAX) nr= LONGLONG_MAX; longlong tmp= number_to_datetime((longlong) floor(nr), + (nr-floor(nr))*TIME_SECOND_PART_FACTOR, &l_time, (thd->variables.sql_mode & MODE_NO_ZERO_DATE) | MODE_NO_ZERO_IN_DATE, &error); - l_time.second_part= (ulong)((nr-floor(nr))*TIME_SECOND_PART_FACTOR); return store_TIME_with_warning(thd, &l_time, &str, error, tmp != -1); } @@ -4574,7 +4600,7 @@ int Field_timestamp::store(longlong nr, bool unsigned_val) THD *thd= table->in_use; /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */ - longlong tmp= number_to_datetime(nr, &l_time, (thd->variables.sql_mode & + longlong tmp= number_to_datetime(nr, 0, &l_time, (thd->variables.sql_mode & MODE_NO_ZERO_DATE) | MODE_NO_ZERO_IN_DATE, &error); return store_TIME_with_warning(thd, &l_time, &str, error, tmp != LL(-1)); @@ -4873,14 +4899,35 @@ String *Field_timestamp_hires::val_str(String *val_buffer, String *val_ptr) } +my_decimal *Field_timestamp_hires::val_decimal(my_decimal *d) +{ + MYSQL_TIME ltime; + get_date(<ime, 0); + longlong intg= TIME_to_ulonglong(<ime); + return seconds2my_decimal(ltime.neg, intg, ltime.second_part, d); +} + int Field_timestamp_hires::store_decimal(const my_decimal *d) { - char buff[DECIMAL_MAX_STR_LENGTH+1]; - String str(buff, sizeof(buff), &my_charset_bin); - my_decimal2string(E_DEC_FATAL_ERROR, d, - MAX_DATETIME_COMPRESSED_WIDTH + MAX_DATETIME_PRECISION, - 6, '0', &str); - return store(str.ptr(), str.length(), str.charset()); + ulonglong nr; + ulong sec_part; + int error; + MYSQL_TIME ltime; + longlong tmp; + THD *thd= table->in_use; + Lazy_string_decimal str(d); + + if (my_decimal2seconds(d, &nr, &sec_part)) + { + tmp= -1; + error= 2; + } + else + tmp= number_to_datetime(nr, sec_part, <ime, TIME_NO_ZERO_IN_DATE | + (thd->variables.sql_mode & + MODE_NO_ZERO_DATE), &error); + + return store_TIME_with_warning(thd, <ime, &str, error, tmp != -1); } int Field_timestamp_hires::set_time() @@ -5026,13 +5073,14 @@ int Field_temporal::store(double nr) if (nr < 0 || nr > LONGLONG_MAX) nr= LONGLONG_MAX; - longlong tmp= number_to_datetime((longlong) floor(nr), <ime, - (TIME_FUZZY_DATE | + longlong tmp= number_to_datetime((longlong) floor(nr), + ((nr-floor(nr))*TIME_SECOND_PART_FACTOR), + <ime, + (TIME_FUZZY_DATE | (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES))), &error); - ltime.second_part= (ulong)((nr-floor(nr))*TIME_SECOND_PART_FACTOR); return store_TIME_with_warning(<ime, &str, error, tmp != -1); } @@ -5045,7 +5093,7 @@ int Field_temporal::store(longlong nr, bool unsigned_val) THD *thd= table->in_use; Lazy_string_num str(nr); - tmp= number_to_datetime(nr, <ime, (TIME_FUZZY_DATE | + tmp= number_to_datetime(nr, 0, <ime, (TIME_FUZZY_DATE | (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | @@ -5055,7 +5103,7 @@ int Field_temporal::store(longlong nr, bool unsigned_val) } -int Field_temporal::store_time(MYSQL_TIME *ltime,timestamp_type time_type) +int Field_temporal::store_time_dec(MYSQL_TIME *ltime, uint dec) { int error = 0, have_smth_to_conv= 1; MYSQL_TIME l_time= *ltime; @@ -5064,21 +5112,24 @@ int Field_temporal::store_time(MYSQL_TIME *ltime,timestamp_type time_type) We don't perform range checking here since values stored in TIME structure always fit into DATETIME range. */ - if (time_type == MYSQL_TIMESTAMP_DATE || - time_type == MYSQL_TIMESTAMP_DATETIME) - { - have_smth_to_conv= !check_date(&l_time, pack_time(&l_time) != 0, - (TIME_FUZZY_DATE | - (current_thd->variables.sql_mode & - (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | - MODE_INVALID_DATES))), &error); - } - else + have_smth_to_conv= !check_date(&l_time, pack_time(&l_time) != 0, + (TIME_FUZZY_DATE | + (current_thd->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error); + return store_TIME_with_warning(&l_time, &str, error, have_smth_to_conv); +} + +my_decimal *Field_temporal::val_decimal(my_decimal *d) +{ + MYSQL_TIME ltime; + if (get_date(<ime, TIME_FUZZY_DATE)) { - error= 1; - have_smth_to_conv= 0; + bzero(<ime, sizeof(ltime)); + ltime.time_type= mysql_type_to_time_type(type()); } - return store_TIME_with_warning(&l_time, &str, error, have_smth_to_conv); + longlong intg= TIME_to_ulonglong(<ime); + return seconds2my_decimal(ltime.neg, intg, ltime.second_part, d); } /**************************************************************************** @@ -5109,7 +5160,7 @@ int Field_time::store(const char *from,uint len,CHARSET_INFO *cs) } -int Field_time::store_time(MYSQL_TIME *ltime, timestamp_type time_type) +int Field_time::store_time_dec(MYSQL_TIME *ltime, uint dec) { MYSQL_TIME l_time= *ltime; Lazy_string_time str(ltime); @@ -5125,7 +5176,12 @@ int Field_time::store(double nr) MYSQL_TIME ltime; Lazy_string_double str(nr); int was_cut; - int have_smth_to_conv= !number_to_time(nr, <ime, &was_cut); + bool neg= nr < 0; + if (neg) + nr= -nr; + int have_smth_to_conv= !number_to_time(neg, nr, + (nr - trunc(nr)) * TIME_SECOND_PART_FACTOR, + <ime, &was_cut); return store_TIME_with_warning(<ime, &str, was_cut, have_smth_to_conv); } @@ -5136,7 +5192,8 @@ int Field_time::store(longlong nr, bool unsigned_val) MYSQL_TIME ltime; Lazy_string_num str(nr); int was_cut; - int have_smth_to_conv= !number_to_time((double)nr, <ime, &was_cut); + int have_smth_to_conv= !number_to_time(nr < 0, nr < 0 ? -nr : nr, + 0, <ime, &was_cut); return store_TIME_with_warning(<ime, &str, was_cut, have_smth_to_conv); } @@ -5248,17 +5305,42 @@ void Field_time::sql_type(String &res) const res.set_ascii(STRING_WITH_LEN("time")); } +static const longlong t_shift= ((TIME_MAX_VALUE_SECONDS+1)*TIME_SECOND_PART_FACTOR); void Field_time_hires::store_TIME(MYSQL_TIME *ltime) { - ulonglong packed= sec_part_shift(pack_time(ltime), dec); + ulonglong packed= sec_part_shift(pack_time(ltime), dec) + + sec_part_shift(t_shift, dec); store_bigendian(packed, ptr, Field_time_hires::pack_length()); } +int Field_time_hires::store_decimal(const my_decimal *d) +{ + ulonglong nr; + ulong sec_part; + Lazy_string_decimal str(d); + MYSQL_TIME ltime; + int was_cut; + bool neg= my_decimal2seconds(d, &nr, &sec_part); + + int have_smth_to_conv= !number_to_time(neg, nr, sec_part, <ime, &was_cut); + + return store_TIME_with_warning(<ime, &str, was_cut, have_smth_to_conv); +} + uint32 Field_time_hires::pack_length() const { return time_hires_bytes[dec]; } +longlong Field_time_hires::val_int(void) +{ + ASSERT_COLUMN_MARKED_FOR_READ; + MYSQL_TIME ltime; + Field_time_hires::get_date(<ime, TIME_TIME_ONLY); + longlong val= TIME_to_ulonglong_time(<ime); + return ltime.neg ? -val : val; +} + double Field_time_hires::val_real(void) { ASSERT_COLUMN_MARKED_FOR_READ; @@ -5284,20 +5366,18 @@ bool Field_time_hires::get_date(MYSQL_TIME *ltime, uint fuzzydate) uint32 len= pack_length(); longlong packed= read_bigendian(ptr, len); - /* sign extension */ - longlong mask= 1LL << (len*8 - 1); - if (packed & mask) - packed|= ~(mask-1); + if (packed) + packed= sec_part_unshift(packed - sec_part_shift(t_shift, dec), dec); - unpack_time(sec_part_unshift(packed, dec), ltime); + unpack_time(packed, ltime); /* unpack_time() returns MYSQL_TIMESTAMP_DATETIME. - To get MYSQL_TIMESTAMP_TIME we few some adjustments + To get MYSQL_TIMESTAMP_TIME we few adjustments */ ltime->time_type= MYSQL_TIMESTAMP_TIME; ltime->hour+= (ltime->month*32+ltime->day)*24; ltime->month= ltime->day= 0; - return fuzzydate & (TIME_FUZZY_DATE|TIME_TIME_ONLY) ? 0 : 1; + return fuzzydate & (TIME_FUZZY_DATE | TIME_TIME_ONLY) ? 0 : 1; } @@ -5411,6 +5491,17 @@ int Field_year::store(longlong nr, bool unsigned_val) } +int Field_year::store_time_dec(MYSQL_TIME *ltime, uint dec) +{ + Lazy_string_time str(ltime); + if (Field_year::store(ltime->year, 0)) + return 1; + + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, + &str, ltime->time_type, 1); + return 0; +} + bool Field_year::send_binary(Protocol *protocol) { ASSERT_COLUMN_MARKED_FOR_READ; @@ -5448,6 +5539,16 @@ String *Field_year::val_str(String *val_buffer, } +bool Field_year::get_date(MYSQL_TIME *ltime,uint fuzzydate) +{ + int tmp= (int) ptr[0]; + if (tmp || field_length != 4) + tmp+= 1900; + return int_to_datetime_with_warn(tmp, ltime, fuzzydate, + MYSQL_TYPE_YEAR, field_name); +} + + void Field_year::sql_type(String &res) const { CHARSET_INFO *cs=res.charset(); @@ -5780,12 +5881,27 @@ void Field_datetime_hires::store_TIME(MYSQL_TIME *ltime) int Field_datetime_hires::store_decimal(const my_decimal *d) { - char buff[DECIMAL_MAX_STR_LENGTH+1]; - String str(buff, sizeof(buff), &my_charset_bin); - my_decimal2string(E_DEC_FATAL_ERROR, d, - MAX_DATETIME_COMPRESSED_WIDTH + MAX_DATETIME_PRECISION, - 6, '0', &str); - return store(str.ptr(), str.length(), str.charset()); + ulonglong nr; + ulong sec_part; + int error; + MYSQL_TIME ltime; + longlong tmp; + THD *thd= table->in_use; + Lazy_string_decimal str(d); + + if (my_decimal2seconds(d, &nr, &sec_part)) + { + tmp= -1; + error= 2; + } + else + tmp= number_to_datetime(nr, sec_part, <ime, (TIME_FUZZY_DATE | + (thd->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | + MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error); + + return store_TIME_with_warning(<ime, &str, error, tmp != -1); } bool Field_datetime_hires::send_binary(Protocol *protocol) @@ -9740,7 +9856,7 @@ uint32 Field_blob::max_display_length() 0 otherwise */ -bool +void Field::set_warning(MYSQL_ERROR::enum_warning_level level, uint code, int cuted_increment) { @@ -9748,15 +9864,13 @@ Field::set_warning(MYSQL_ERROR::enum_warning_level level, uint code, If this field was created only for type conversion purposes it will have table == NULL. */ - THD *thd= table->in_use; + THD *thd= table ? table->in_use : current_thd; if (thd->count_cuted_fields) { thd->cuted_fields+= cuted_increment; push_warning_printf(thd, level, code, ER(code), field_name, thd->row_count); - return 0; } - return level >= MYSQL_ERROR::WARN_LEVEL_WARN; } @@ -9783,9 +9897,9 @@ void Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, timestamp_type ts_type, int cuted_increment) { THD *thd= table->in_use; - if ((thd->really_abort_on_warning() && - level >= MYSQL_ERROR::WARN_LEVEL_WARN) || - set_warning(level, code, cuted_increment)) + if (thd->really_abort_on_warning() && level >= MYSQL_ERROR::WARN_LEVEL_WARN) make_truncated_value_warning(thd, level, str, ts_type, field_name); + else + set_warning(level, code, cuted_increment); } diff --git a/sql/field.h b/sql/field.h index a976d8b5ce5..e2b922d89b7 100644 --- a/sql/field.h +++ b/sql/field.h @@ -115,7 +115,9 @@ public: virtual int store(double nr)=0; virtual int store(longlong nr, bool unsigned_val)=0; virtual int store_decimal(const my_decimal *d)=0; - virtual int store_time(MYSQL_TIME *ltime, timestamp_type t_type); + virtual int store_time_dec(MYSQL_TIME *ltime, uint dec); + int store_time(MYSQL_TIME *ltime) + { return store_time_dec(ltime, TIME_SECOND_PART_DIGITS); } int store(const char *to, uint length, CHARSET_INFO *cs, enum_check_fields check_level); virtual double val_real(void)=0; @@ -441,7 +443,7 @@ public: { return DERIVATION_IMPLICIT; } virtual void set_derivation(enum Derivation derivation_arg) { } virtual int set_time() { return 1; } - bool set_warning(MYSQL_ERROR::enum_warning_level, unsigned int code, + void set_warning(MYSQL_ERROR::enum_warning_level, unsigned int code, int cuted_increment); void set_datetime_warning(MYSQL_ERROR::enum_warning_level, uint code, const Lazy_string *str, timestamp_type ts_type, @@ -529,49 +531,26 @@ private: { return 0; } protected: - /* - Helper function to pack()/unpack() int32 values - */ - static void handle_int32(uchar *to, const uchar *from) + uchar *pack_int(uchar *to, const uchar *from, size_t size) { - int32 val; - val = sint4korr(from); - int4store(to, val); + memcpy(to, from, size); + return to + size; } - /* - Helper function to pack()/unpack() int64 values - */ - static void handle_int64(uchar* to, const uchar *from) + const uchar *unpack_int(uchar* to, const uchar *from, size_t size) { - int64 val; - val = sint8korr(from); - int8store(to, val); + memcpy(to, from, size); + return from + size; } uchar *pack_int32(uchar *to, const uchar *from) - { - handle_int32(to, from); - return to + sizeof(int32); - } - + { return pack_int(to, from, 4); } const uchar *unpack_int32(uchar* to, const uchar *from) - { - handle_int32(to, from); - return from + sizeof(int32); - } - + { return unpack_int(to, from, 4); } uchar *pack_int64(uchar* to, const uchar *from) - { - handle_int64(to, from); - return to + sizeof(int64); - } - + { return pack_int(to, from, 8); } const uchar *unpack_int64(uchar* to, const uchar *from) - { - handle_int64(to, from); - return from + sizeof(int64); - } + { return unpack_int(to, from, 8); } bool field_flags_are_binary() { @@ -589,7 +568,7 @@ public: uchar null_bit_arg, utype unireg_check_arg, const char *field_name_arg, uint8 dec_arg, bool zero_arg, bool unsigned_arg); - Item_result result_type () const { return REAL_RESULT; } + enum Item_result result_type () const { return INT_RESULT; } void prepend_zeros(String *value); void add_zerofill_and_unsigned(String &res) const; friend class Create_field; @@ -600,6 +579,7 @@ public: int store_decimal(const my_decimal *); my_decimal *val_decimal(my_decimal *); uint is_equal(Create_field *new_field); + int store_time_dec(MYSQL_TIME *ltime, uint dec); int check_int(CHARSET_INFO *cs, const char *str, int length, const char *int_end, int error); bool get_int(CHARSET_INFO *cs, const char *from, uint len, @@ -668,7 +648,10 @@ public: field_name_arg, dec_arg, zero_arg, unsigned_arg), not_fixed(dec_arg >= NOT_FIXED_DEC) {} + Item_result result_type () const { return REAL_RESULT; } int store_decimal(const my_decimal *); + int store_time_dec(MYSQL_TIME *ltime, uint dec); + bool get_date(MYSQL_TIME *ltime,uint fuzzydate); my_decimal *val_decimal(my_decimal *); int truncate(double *nr, double max_length); uint32 max_display_length() { return field_length; } @@ -742,7 +725,7 @@ public: int store(const char *to, uint length, CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); - int store_time(MYSQL_TIME *ltime, timestamp_type t_type); + int store_time_dec(MYSQL_TIME *ltime, uint dec); int store_decimal(const my_decimal *); double val_real(void); longlong val_int(void); @@ -775,7 +758,6 @@ public: unireg_check_arg, field_name_arg, 0, zero_arg,unsigned_arg) {} - enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return MYSQL_TYPE_TINY;} enum ha_base_keytype key_type() const { return unsigned_flag ? HA_KEYTYPE_BINARY : HA_KEYTYPE_INT8; } @@ -822,7 +804,6 @@ public: :Field_num((uchar*) 0, len_arg, maybe_null_arg ? (uchar*) "": 0,0, NONE, field_name_arg, 0, 0, unsigned_arg) {} - enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return MYSQL_TYPE_SHORT;} enum ha_base_keytype key_type() const { return unsigned_flag ? HA_KEYTYPE_USHORT_INT : HA_KEYTYPE_SHORT_INT;} @@ -867,7 +848,6 @@ public: unireg_check_arg, field_name_arg, 0, zero_arg,unsigned_arg) {} - enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return MYSQL_TYPE_INT24;} enum ha_base_keytype key_type() const { return unsigned_flag ? HA_KEYTYPE_UINT24 : HA_KEYTYPE_INT24; } @@ -912,7 +892,6 @@ public: :Field_num((uchar*) 0, len_arg, maybe_null_arg ? (uchar*) "": 0,0, NONE, field_name_arg,0,0,unsigned_arg) {} - enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return MYSQL_TYPE_LONG;} enum ha_base_keytype key_type() const { return unsigned_flag ? HA_KEYTYPE_ULONG_INT : HA_KEYTYPE_LONG_INT; } @@ -958,7 +937,6 @@ public: :Field_num((uchar*) 0, len_arg, maybe_null_arg ? (uchar*) "": 0,0, NONE, field_name_arg,0,0,unsigned_arg) {} - enum Item_result result_type () const { return INT_RESULT; } enum_field_types type() const { return MYSQL_TYPE_LONGLONG;} enum ha_base_keytype key_type() const { return unsigned_flag ? HA_KEYTYPE_ULONGLONG : HA_KEYTYPE_LONGLONG; } @@ -1101,6 +1079,7 @@ public: class Field_timestamp :public Field_str { +protected: int store_TIME_with_warning(THD *, MYSQL_TIME *, const Lazy_string *, bool, bool); public: @@ -1116,7 +1095,7 @@ public: int store(const char *to,uint length,CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); - int store_time(MYSQL_TIME *ltime, timestamp_type type); + int store_time_dec(MYSQL_TIME *ltime, uint dec); double val_real(void); longlong val_int(void); String *val_str(String*,String *); @@ -1175,6 +1154,7 @@ public: my_time_t get_timestamp(ulong *sec_part) const; void store_TIME(my_time_t timestamp, ulong sec_part); int store_decimal(const my_decimal *d); + my_decimal* val_decimal(my_decimal*); double val_real(void); String *val_str(String*,String *); bool send_binary(Protocol *protocol); @@ -1205,9 +1185,11 @@ public: int store(const char *to,uint length,CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); + int store_time_dec(MYSQL_TIME *ltime, uint dec); double val_real(void); longlong val_int(void); String *val_str(String*,String *); + bool get_date(MYSQL_TIME *ltime,uint fuzzydate); bool send_binary(Protocol *protocol); void sql_type(String &str) const; }; @@ -1229,7 +1211,8 @@ public: int store(const char *to,uint length,CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); - int store_time(MYSQL_TIME *ltime, timestamp_type type); + int store_time_dec(MYSQL_TIME *ltime, uint dec); + my_decimal *val_decimal(my_decimal*); }; class Field_date :public Field_temporal { @@ -1310,7 +1293,7 @@ public: {} enum_field_types type() const { return MYSQL_TYPE_TIME;} enum ha_base_keytype key_type() const { return HA_KEYTYPE_INT24; } - int store_time(MYSQL_TIME *ltime, timestamp_type type); + int store_time_dec(MYSQL_TIME *ltime, uint dec); int store(const char *to,uint length,CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); @@ -1343,7 +1326,8 @@ public: } enum ha_base_keytype key_type() const { return HA_KEYTYPE_BINARY; } uint decimals() const { return dec; } - longlong val_int(void) { return (longlong)floor(val_real()); } + int store_decimal(const my_decimal *d); + longlong val_int(void); double val_real(void); String *val_str(String*,String *); bool get_date(MYSQL_TIME *ltime, uint fuzzydate); @@ -1409,9 +1393,9 @@ public: DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS); } enum ha_base_keytype key_type() const { return HA_KEYTYPE_BINARY; } - int store_decimal(const my_decimal *d); uint decimals() const { return dec; } void make_field(Send_field *field); + int store_decimal(const my_decimal *d); double val_real(void); longlong val_int(void); String *val_str(String*,String *); diff --git a/sql/field_conv.cc b/sql/field_conv.cc index 2d08cd0694b..6ca48f909a5 100644 --- a/sql/field_conv.cc +++ b/sql/field_conv.cc @@ -369,7 +369,7 @@ static void do_field_temporal(Copy_field *copy) { MYSQL_TIME ltime; copy->from_field->get_date(<ime, TIME_FUZZY_DATE); - copy->to_field->store_time(<ime, ltime.time_type); + copy->to_field->store_time_dec(<ime, copy->from_field->decimals()); } @@ -664,7 +664,25 @@ Copy_field::get_copy_func(Field *to,Field *from) if (to->result_type() == DECIMAL_RESULT) return do_field_decimal; if (to->cmp_type() == TIME_RESULT) - return do_field_temporal; + { + if (from->cmp_type() == TIME_RESULT) + return do_field_temporal; + if (from->result_type() == STRING_RESULT) + return do_field_string; + if (from->result_type() == INT_RESULT) + return do_field_int; + if (from->result_type() == DECIMAL_RESULT) + return do_field_decimal; + return do_field_real; + } + if (from->cmp_type() == TIME_RESULT) + { + if (to->result_type() == STRING_RESULT) + return do_field_string; + if (to->result_type() == INT_RESULT) + return do_field_int; + return do_field_real; + } // Check if identical fields if (from->result_type() == STRING_RESULT) { @@ -677,15 +695,7 @@ Copy_field::get_copy_func(Field *to,Field *from) to->type() == MYSQL_TYPE_VARCHAR && !to->has_charset()) return do_field_varbinary_pre50; - /* - If we are copying date or datetime's we have to check the dates - if we don't allow 'all' dates. - */ - if (to->real_type() != from->real_type() || - (((to->table->in_use->variables.sql_mode & - (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) && - to->type() == MYSQL_TYPE_DATE) || - to->type() == MYSQL_TYPE_DATETIME)) + if (to->real_type() != from->real_type()) { if (from->real_type() == MYSQL_TYPE_ENUM || from->real_type() == MYSQL_TYPE_SET) @@ -818,7 +828,22 @@ int field_conv(Field *to,Field *from) ((Field_enum *)(to))->store_type(0); return 0; } - else if ((from->result_type() == STRING_RESULT && + if (from->result_type() == REAL_RESULT) + return to->store(from->val_real()); + if (from->result_type() == DECIMAL_RESULT) + { + my_decimal buff; + return to->store_decimal(from->val_decimal(&buff)); + } + if (from->cmp_type() == TIME_RESULT) + { + MYSQL_TIME ltime; + if (from->get_date(<ime, TIME_FUZZY_DATE)) + return to->reset(); + else + return to->store_time_dec(<ime, from->decimals()); + } + if ((from->result_type() == STRING_RESULT && (to->result_type() == STRING_RESULT || (from->real_type() != MYSQL_TYPE_ENUM && from->real_type() != MYSQL_TYPE_SET))) || @@ -835,13 +860,5 @@ int field_conv(Field *to,Field *from) */ return to->store(result.c_ptr_quick(),result.length(),from->charset()); } - else if (from->result_type() == REAL_RESULT) - return to->store(from->val_real()); - else if (from->result_type() == DECIMAL_RESULT) - { - my_decimal buff; - return to->store_decimal(from->val_decimal(&buff)); - } - else - return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG)); + return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG)); } diff --git a/sql/filesort.cc b/sql/filesort.cc index 4c096dad4d9..920048d4503 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -844,7 +844,7 @@ static void make_sortkey(register SORTPARAM *param, case INT_RESULT: case TIME_RESULT: { - longlong value; + longlong UNINIT_VAR(value); if (sort_field->result_type == INT_RESULT) value= item->val_int_result(); else diff --git a/sql/item.cc b/sql/item.cc index 6c74f509915..002621acb5a 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -329,7 +329,7 @@ int Item::save_time_in_field(Field *field) if (get_time(<ime)) return set_field_to_null_with_conversions(field, 0); field->set_notnull(); - return field->store_time(<ime, MYSQL_TIMESTAMP_TIME); + return field->store_time_dec(<ime, decimals); } @@ -339,7 +339,7 @@ int Item::save_date_in_field(Field *field) if (get_date(<ime, TIME_FUZZY_DATE)) return set_field_to_null_with_conversions(field, 0); field->set_notnull(); - return field->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); + return field->store_time_dec(<ime, decimals); } @@ -984,27 +984,59 @@ bool Item::get_date(MYSQL_TIME *ltime,uint fuzzydate) { if (field_type() == MYSQL_TYPE_TIME) fuzzydate|= TIME_TIME_ONLY; - if (result_type() != INT_RESULT || fuzzydate & TIME_TIME_ONLY) + + Item_result res_type= result_type(); + + enum_field_types f_type= MYSQL_TYPE_SET; // a.k.a. use fuzzydate flags + + switch (res_type) { + case INT_RESULT: + { + longlong value= val_int(); + if (field_type() == MYSQL_TYPE_YEAR) + { + f_type= MYSQL_TYPE_YEAR; + if (value < 70) + value+= 2000; + else if (value <= 1900) + value+= 1900; + } + if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate, + f_type, field_name_or_null())) + goto err; + break; + } + case REAL_RESULT: + { + double value= val_real(); + if (null_value || double_to_datetime_with_warn(value, ltime, fuzzydate, + f_type, field_name_or_null())) + goto err; + break; + } + case DECIMAL_RESULT: + { + my_decimal value, *res; + if (!(res= val_decimal(&value)) || + decimal_to_datetime_with_warn(res, ltime, fuzzydate, + f_type, field_name_or_null())) + goto err; + break; + } + case STRING_RESULT: { char buff[40]; String tmp(buff,sizeof(buff), &my_charset_bin),*res; if (!(res=val_str(&tmp)) || - str_to_datetime_with_warn(res->ptr(), res->length(), ltime, - fuzzydate) <= MYSQL_TIMESTAMP_ERROR) + str_to_datetime_with_warn(res->ptr(), res->length(), + ltime, fuzzydate) <= MYSQL_TIMESTAMP_ERROR) goto err; + break; } - else - { - longlong value= val_int(); - int was_cut; - if (number_to_datetime(value, ltime, fuzzydate, &was_cut) == LL(-1)) - { - Lazy_string_num str(value); - make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, - &str, MYSQL_TIMESTAMP_NONE, NullS); - goto err; - } + default: + DBUG_ASSERT(0); } + return 0; err: @@ -1012,17 +1044,27 @@ err: return 1; } -/** - Get time of first argument. - - As a extra convenience the time structure is reset on error! -*/ - bool Item::get_time(MYSQL_TIME *ltime) { return get_date(ltime, TIME_TIME_ONLY | TIME_FUZZY_DATE); } +bool Item::get_seconds(ulonglong *sec, ulong *sec_part) +{ + if (result_type() == INT_RESULT) + { // optimize for an important special case + longlong val= val_int(); + bool neg= val < 0 && !unsigned_flag; + *sec= neg ? -val : val; + *sec_part= 0; + return neg; + } + my_decimal tmp, *dec= val_decimal(&tmp); + if (!dec) + return 0; + return my_decimal2seconds(dec, sec, sec_part); +} + CHARSET_INFO *Item::default_charset() { return current_thd->variables.collation_connection; @@ -2951,7 +2993,7 @@ int Item_param::save_in_field(Field *field, bool no_conversions) case DECIMAL_VALUE: return field->store_decimal(&decimal_value); case TIME_VALUE: - field->store_time(&value.time, value.time.time_type); + field->store_time_dec(&value.time, decimals); return 0; case STRING_VALUE: case LONG_DATA_VALUE: @@ -5247,7 +5289,7 @@ void Item_datetime::set(longlong packed) int Item_datetime::save_in_field(Field *field, bool no_conversions) { field->set_notnull(); - return field->store_time(<ime, ltime.time_type); + return field->store_time_dec(<ime, decimals); } longlong Item_datetime::val_int() @@ -7194,30 +7236,22 @@ longlong Item_cache_int::val_int() bool Item_cache_int::get_date(MYSQL_TIME *ltime, uint fuzzydate) { + Lazy_string_num str(value); + if (!value_cached && !cache_value()) - goto err; + { + bzero((char*) ltime,sizeof(*ltime)); + return 1; + } if (cmp_type() == TIME_RESULT) { unpack_time(value, ltime); ltime->time_type= mysql_type_to_time_type(field_type()); + return 0; } - else - { - int was_cut; - if (number_to_datetime(value, ltime, fuzzydate, &was_cut) == -1LL) - { - Lazy_string_num str(value); - make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, - &str, MYSQL_TIMESTAMP_NONE, NullS); - goto err; - } - } - return 0; -err: - bzero((char*) ltime,sizeof(*ltime)); - return 1; + return Item::get_date(ltime, fuzzydate); } int Item_cache_int::save_in_field(Field *field, bool no_conversions) @@ -7232,7 +7266,7 @@ int Item_cache_int::save_in_field(Field *field, bool no_conversions) MYSQL_TIME ltime; unpack_time(value, <ime); ltime.time_type= mysql_type_to_time_type(field_type()); - error= field->store_time(<ime, ltime.time_type); + error= field->store_time_dec(<ime, decimals); } else error= field->store(value, unsigned_flag); diff --git a/sql/item.h b/sql/item.h index 16f0857958d..c2b39216e85 100644 --- a/sql/item.h +++ b/sql/item.h @@ -815,6 +815,7 @@ public: Item **ref, bool skip_registered); virtual bool get_date(MYSQL_TIME *ltime,uint fuzzydate); bool get_time(MYSQL_TIME *ltime); + bool get_seconds(ulonglong *sec, ulong *sec_part); virtual bool get_date_result(MYSQL_TIME *ltime,uint fuzzydate) { return get_date(ltime,fuzzydate); } /* diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index e99835083a6..c244d46133f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -73,6 +73,30 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems) } +/** + find an temporal type (item) that others will be converted to + for the purpose of comparison. + + this is the type that will be used in warnings like + "Incorrect <<TYPE>> value". +*/ +Item *find_date_time_item(Item **args, uint nargs, uint col) +{ + Item *date_arg= 0, **arg, **arg_end; + for (arg= args, arg_end= args + nargs; arg != arg_end ; arg++) + { + Item *item= arg[0]->element_index(col); + if (item->cmp_type() != TIME_RESULT) + continue; + if (item->field_type() == MYSQL_TYPE_DATETIME) + return item; + if (!date_arg) + date_arg= item; + } + return date_arg; +} + + /* Compare row signature of two expressions @@ -204,7 +228,7 @@ static uint collect_cmp_types(Item **items, uint nitems, bool skip_nulls= FALSE) { uint i; uint found_types; - Item_result left_result= items[0]->result_type(); + Item_result left_result= items[0]->cmp_type(); DBUG_ASSERT(nitems > 1); found_types= 0; for (i= 1; i < nitems ; i++) @@ -212,11 +236,11 @@ static uint collect_cmp_types(Item **items, uint nitems, bool skip_nulls= FALSE) if (skip_nulls && items[i]->type() == Item::NULL_ITEM) continue; // Skip NULL constant items if ((left_result == ROW_RESULT || - items[i]->result_type() == ROW_RESULT) && + items[i]->cmp_type() == ROW_RESULT) && cmp_row_type(items[0], items[i])) return 0; found_types|= 1<< (uint)item_cmp_type(left_result, - items[i]->result_type()); + items[i]->cmp_type()); } /* Even if all right-hand items are NULLs and we are skipping them all, we need @@ -677,36 +701,6 @@ bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type, /** - @brief Convert date provided in a string to the int representation. - - @param[in] thd thread handle - @param[in] str a string to convert - @param[in] warn_type type of the timestamp for issuing the warning - @param[in] warn_name field name for issuing the warning - @param[out] error_arg could not extract a DATE or DATETIME - - @details Convert date provided in the string str to the int - representation. If the string contains wrong date or doesn't - contain it at all then a warning is issued. The warn_type and - the warn_name arguments are used as the name and the type of the - field when issuing the warning. - - @return - converted value. 0 on error and on zero-dates -- check 'failure' -*/ -static ulonglong get_date_from_str(THD *thd, String *str, - timestamp_type warn_type, - const char *warn_name, bool *error_arg) -{ - MYSQL_TIME l_time; - *error_arg= get_mysql_time_from_str(thd, str, warn_type, warn_name, &l_time); - - if (*error_arg) - return 0; - return pack_time(&l_time); -} - -/** Prepare the comparator (set the comparison function) for comparing items *a1 and *a2 in the context of 'type'. @@ -838,100 +832,28 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, { longlong UNINIT_VAR(value); Item *item= **item_arg; - enum_field_types f_type= warn_item->field_type(); + enum_field_types f_type= item->cmp_type() == TIME_RESULT ? + item->field_type() : warn_item->field_type(); - switch (item->cmp_type()) { - case TIME_RESULT: - /* if it's our Item_cache_int, as created below, we simply use the value */ - if (item->result_type() == INT_RESULT) - { - value= item->val_int(); - cache_arg= 0; - } - else - { - MYSQL_TIME buf; - if (item->get_date_result(&buf, TIME_FUZZY_DATE | TIME_INVALID_DATES)) - DBUG_ASSERT(item->null_value); - else - value= pack_time(&buf); - f_type= item->field_type(); // for Item_cache_int below. - } - break; - case INT_RESULT: + if (item->result_type() == INT_RESULT && item->cmp_type() == TIME_RESULT) + { + /* it's our Item_cache_int, as created below */ value= item->val_int(); - - if (item->field_type() == MYSQL_TYPE_YEAR) - { - Item *real_item= item->real_item(); - if (!(real_item->type() == Item::FIELD_ITEM && - ((Item_field *)real_item)->field->type() == MYSQL_TYPE_YEAR && - ((Item_field *)real_item)->field->field_length == 4)) - { - if (value < 70) - value+= 100; - if (value <= 1900) - value+= 1900; - } - value*= 13ULL * 32ULL * 24ULL * 60ULL * 60ULL * 1000000ULL; - } + } + else + { + MYSQL_TIME ltime; + uint fuzzydate= TIME_FUZZY_DATE | TIME_INVALID_DATES; + if (f_type == MYSQL_TYPE_TIME) + fuzzydate|= TIME_TIME_ONLY; + if (item->get_date(<ime, fuzzydate)) + value= 0; /* invalid date */ else - { - MYSQL_TIME buf; - int was_cut; - longlong res; - - if (f_type == MYSQL_TYPE_TIME) - res= number_to_time((double)value, &buf, &was_cut); - else - res= number_to_datetime(value, &buf, TIME_INVALID_DATES|TIME_FUZZY_DATE, - &was_cut); - if (res == -1) - { - const Lazy_string_num str(value); - make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, &str, - mysql_type_to_time_type(f_type), - warn_item->field_name_or_null()); - value= 0; - } - else - value= pack_time(&buf); - } - break; - case STRING_RESULT: - case DECIMAL_RESULT: - case REAL_RESULT: - { - char strbuf[MAX_DATETIME_FULL_WIDTH]; - String buf(strbuf, sizeof(strbuf), &my_charset_bin), *str; - if ((str= item->val_str(&buf))) - { - /* - Convert strings to the integer DATE/DATETIME representation. - Even if both dates provided in strings we can't compare them directly as - strings as there is no warranty that they are correct and do not miss - some insignificant zeros. - */ - bool error; - value= (longlong) get_date_from_str(thd, str, - mysql_type_to_time_type(f_type), - warn_item->field_name_or_null(), - &error); - /* - If str did not contain a valid date according to the current - SQL_MODE, get_date_from_str() has already thrown a warning, - and we don't want to throw NULL on invalid date (see 5.2.6 - "SQL modes" in the manual), so we're done here. - */ - } - break; - } - case ROW_RESULT: - DBUG_ASSERT(0); + value= pack_time(<ime); } if ((*is_null= item->null_value)) return ~(ulonglong) 0; - if (cache_arg && item->const_item()) + if (cache_arg && item->const_item() && item->type() != Item::CACHE_ITEM) { /* cache the packed datetime value in the Item_cache object. @@ -940,8 +862,8 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, But we create it to have field_type() == MYSQL_TYPE_TIME (or MYSQL_TIMESTAMP_DATE or MYSQL_TYPE_DATETIME), and thus it will have cmp_type() == TIME_RESULT. - As no other item can have this combination of cmp_type() and result_type(), - it allows us to identify our cache items, see 'case TIME_RESULT:' above. + As no other item can have this combination of cmp_type() and + result_type(), it allows us to identify our cache items. */ Item_cache_int *cache= new Item_cache_int(f_type); cache->store(item, value); @@ -2007,18 +1929,7 @@ void Item_func_between::fix_length_and_dec() strings as. */ if (cmp_type == TIME_RESULT) - { - for (int i= 0; i < 3; i++) - { - if (args[i]->cmp_type() == TIME_RESULT) - { - if (args[i]->field_type() != MYSQL_TYPE_TIME || - (args[i]->field_type() == MYSQL_TYPE_TIME && compare_as_dates==0)) - compare_as_dates= args[i]; - continue; - } - } - } + compare_as_dates= find_date_time_item(args, 3, 0); /* See the comment about the similar block in Item_bool_func2 */ if (args[0]->real_item()->type() == FIELD_ITEM && @@ -2780,16 +2691,17 @@ void Item_func_case::fix_length_and_dec() if (!(found_types= collect_cmp_types(agg, nagg))) return; - for (i= 0; i <= (uint)DECIMAL_RESULT; i++) + for (i= 0; i <= (uint)TIME_RESULT; i++) { if (found_types & (1 << i) && !cmp_items[i]) { DBUG_ASSERT((Item_result)i != ROW_RESULT); + DBUG_ASSERT((Item_result)i != TIME_RESULT); if ((Item_result)i == STRING_RESULT && agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV, 1)) return; if (!(cmp_items[i]= - cmp_item::get_comparator((Item_result)i, + cmp_item::get_comparator((Item_result)i, 0, cmp_collation.collation))) return; } @@ -2870,7 +2782,7 @@ void Item_func_case::cleanup() uint i; DBUG_ENTER("Item_func_case::cleanup"); Item_func::cleanup(); - for (i= 0; i <= (uint)DECIMAL_RESULT; i++) + for (i= 0; i <= (uint)TIME_RESULT; i++) { delete cmp_items[i]; cmp_items[i]= 0; @@ -3300,7 +3212,7 @@ uchar *in_decimal::get_value(Item *item) } -cmp_item* cmp_item::get_comparator(Item_result type, +cmp_item* cmp_item::get_comparator(Item_result type, Item *warn_item, CHARSET_INFO *cs) { switch (type) { @@ -3315,7 +3227,8 @@ cmp_item* cmp_item::get_comparator(Item_result type, case DECIMAL_RESULT: return new cmp_item_decimal; case TIME_RESULT: - DBUG_ASSERT(0); + DBUG_ASSERT(warn_item); + return new cmp_item_datetime(warn_item); break; } return 0; // to satisfy compiler :) @@ -3379,7 +3292,7 @@ void cmp_item_row::store_value(Item *item) { if (!comparators[i]) if (!(comparators[i]= - cmp_item::get_comparator(item->element_index(i)->result_type(), + cmp_item::get_comparator(item->element_index(i)->result_type(), 0, item->element_index(i)->collation.collation))) break; // new failed comparators[i]->store_value(item->element_index(i)); @@ -3580,20 +3493,17 @@ static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y) (uchar *) y->ptr(),y->length(), 0); } - void Item_func_in::fix_length_and_dec() { Item **arg, **arg_end; bool const_itm= 1; THD *thd= current_thd; - bool datetime_found= FALSE; /* TRUE <=> arguments values will be compared as DATETIMEs. */ - bool compare_as_datetime= FALSE; Item *date_arg= 0; uint found_types= 0; uint type_cnt= 0, i; Item_result cmp_type= STRING_RESULT; - left_result_type= args[0]->result_type(); + left_result_type= args[0]->cmp_type(); if (!(found_types= collect_cmp_types(args, arg_count, true))) return; @@ -3605,7 +3515,7 @@ void Item_func_in::fix_length_and_dec() break; } } - for (i= 0; i <= (uint)DECIMAL_RESULT; i++) + for (i= 0; i <= (uint)TIME_RESULT; i++) { if (found_types & 1 << i) { @@ -3620,16 +3530,12 @@ void Item_func_in::fix_length_and_dec() agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1)) return; arg_types_compatible= TRUE; - } - if (type_cnt == 1) - { - /* - When comparing rows create the row comparator object beforehand to ease - the DATETIME comparison detection procedure. - */ + if (cmp_type == ROW_RESULT) { + uint cols= args[0]->cols(); cmp_item_row *cmp= 0; + if (const_itm && !nulls_in_row()) { array= new in_row(arg_count-1, 0); @@ -3641,66 +3547,20 @@ void Item_func_in::fix_length_and_dec() return; cmp_items[ROW_RESULT]= cmp; } - cmp->n= args[0]->cols(); + cmp->n= cols; cmp->alloc_comparators(); - } - /* All DATE/DATETIME fields/functions has the STRING result type. */ - if (cmp_type == STRING_RESULT || cmp_type == ROW_RESULT) - { - uint col, cols= args[0]->cols(); - for (col= 0; col < cols; col++) + for (uint col= 0; col < cols; col++) { - bool skip_column= FALSE; - /* - Check that all items to be compared has the STRING result type and at - least one of them is a DATE/DATETIME item. - */ - for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++) + date_arg= find_date_time_item(args, arg_count, col); + if (date_arg) { - Item *itm= ((cmp_type == STRING_RESULT) ? arg[0] : - arg[0]->element_index(col)); - if (itm->result_type() != STRING_RESULT) - { - skip_column= TRUE; - break; - } - else if (itm->cmp_type() == TIME_RESULT) - { - datetime_found= TRUE; - /* - Internally all DATE/DATETIME values are converted to the DATETIME - type. So try to find a DATETIME item to issue correct warnings. - */ - if (!date_arg) - date_arg= itm; - else if (itm->field_type() == MYSQL_TYPE_DATETIME) - { - date_arg= itm; - /* All arguments are already checked to have the STRING result. */ - if (cmp_type == STRING_RESULT) - break; - } - } - } - if (skip_column) - continue; - if (datetime_found) - { - if (cmp_type == ROW_RESULT) - { - cmp_item **cmp= 0; - if (array) - cmp= ((in_row*)array)->tmp.comparators + col; - else - cmp= ((cmp_item_row*)cmp_items[ROW_RESULT])->comparators + col; - *cmp= new cmp_item_datetime(date_arg); - /* Reset variables for the next column. */ - date_arg= 0; - datetime_found= FALSE; - } + cmp_item **cmp= 0; + if (array) + cmp= ((in_row*)array)->tmp.comparators + col; else - compare_as_datetime= TRUE; + cmp= ((cmp_item_row*)cmp_items[ROW_RESULT])->comparators + col; + *cmp= new cmp_item_datetime(date_arg); } } } @@ -3711,61 +3571,57 @@ void Item_func_in::fix_length_and_dec() */ if (type_cnt == 1 && const_itm && !nulls_in_row()) { - if (compare_as_datetime) - array= new in_datetime(date_arg, arg_count - 1); - else + /* + IN must compare INT columns and constants as int values (the same + way as equality does). + So we must check here if the column on the left and all the constant + values on the right can be compared as integers and adjust the + comparison type accordingly. + + See the comment about the similar block in Item_bool_func2 + */ + if (args[0]->real_item()->type() == FIELD_ITEM && + !thd->is_context_analysis_only() && cmp_type != INT_RESULT) { - /* - IN must compare INT columns and constants as int values (the same - way as equality does). - So we must check here if the column on the left and all the constant - values on the right can be compared as integers and adjust the - comparison type accordingly. - - See the comment about the similar block in Item_bool_func2 - */ - if (args[0]->real_item()->type() == FIELD_ITEM && - !thd->is_context_analysis_only() && cmp_type != INT_RESULT) + Item_field *field_item= (Item_field*) (args[0]->real_item()); + if (field_item->cmp_type() == INT_RESULT) { - Item_field *field_item= (Item_field*) (args[0]->real_item()); - if (field_item->cmp_type() == INT_RESULT) + bool all_converted= TRUE; + for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++) { - bool all_converted= TRUE; - for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++) - { - if (!convert_constant_item (thd, field_item, &arg[0])) - all_converted= FALSE; - } - if (all_converted) - cmp_type= INT_RESULT; + if (!convert_constant_item (thd, field_item, &arg[0])) + all_converted= FALSE; } + if (all_converted) + cmp_type= INT_RESULT; } - switch (cmp_type) { - case STRING_RESULT: - array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, - cmp_collation.collation); - break; - case INT_RESULT: - array= new in_longlong(arg_count-1); - break; - case REAL_RESULT: - array= new in_double(arg_count-1); - break; - case ROW_RESULT: - /* - The row comparator was created at the beginning but only DATETIME - items comparators were initialized. Call store_value() to setup - others. - */ - ((in_row*)array)->tmp.store_value(args[0]); - break; - case DECIMAL_RESULT: - array= new in_decimal(arg_count - 1); - break; - case TIME_RESULT: - DBUG_ASSERT(0); - break; - } + } + switch (cmp_type) { + case STRING_RESULT: + array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, + cmp_collation.collation); + break; + case INT_RESULT: + array= new in_longlong(arg_count-1); + break; + case REAL_RESULT: + array= new in_double(arg_count-1); + break; + case ROW_RESULT: + /* + The row comparator was created at the beginning but only DATETIME + items comparators were initialized. Call store_value() to setup + others. + */ + ((in_row*)array)->tmp.store_value(args[0]); + break; + case DECIMAL_RESULT: + array= new in_decimal(arg_count - 1); + break; + case TIME_RESULT: + date_arg= find_date_time_item(args, arg_count, 0); + array= new in_datetime(date_arg, arg_count - 1); + break; } if (array && !(thd->is_fatal_error)) // If not EOM { @@ -3786,23 +3642,21 @@ void Item_func_in::fix_length_and_dec() } else { - if (compare_as_datetime) - cmp_items[STRING_RESULT]= new cmp_item_datetime(date_arg); - else + for (i= 0; i <= (uint) TIME_RESULT; i++) { - for (i= 0; i <= (uint) DECIMAL_RESULT; i++) + if (found_types & (1 << i) && !cmp_items[i]) { - if (found_types & (1 << i) && !cmp_items[i]) - { - if ((Item_result)i == STRING_RESULT && - agg_arg_charsets(cmp_collation, args, arg_count, - MY_COLL_CMP_CONV, 1)) - return; - if (!cmp_items[i] && !(cmp_items[i]= - cmp_item::get_comparator((Item_result)i, - cmp_collation.collation))) - return; - } + if ((Item_result)i == STRING_RESULT && + agg_arg_charsets(cmp_collation, args, arg_count, + MY_COLL_CMP_CONV, 1)) + return; + if ((Item_result)i == TIME_RESULT) + date_arg= find_date_time_item(args, arg_count, 0); + + if (!cmp_items[i] && !(cmp_items[i]= + cmp_item::get_comparator((Item_result)i, date_arg, + cmp_collation.collation))) + return; } } } @@ -3870,7 +3724,7 @@ longlong Item_func_in::val_int() have_null= TRUE; continue; } - Item_result cmp_type= item_cmp_type(left_result_type, args[i]->result_type()); + Item_result cmp_type= item_cmp_type(left_result_type, args[i]->cmp_type()); in_item= cmp_items[(uint)cmp_type]; DBUG_ASSERT(in_item); if (!(value_added_map & (1 << (uint)cmp_type))) @@ -5398,7 +5252,7 @@ longlong Item_equal::val_int() void Item_equal::fix_length_and_dec() { Item *item= get_first(); - eval_item= cmp_item::get_comparator(item->result_type(), + eval_item= cmp_item::get_comparator(item->result_type(), 0, item->collation.collation); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index d86ae7422c6..6ec070bcc57 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -950,7 +950,8 @@ public: virtual int cmp(Item *item)= 0; // for optimized IN with row virtual int compare(cmp_item *item)= 0; - static cmp_item* get_comparator(Item_result type, CHARSET_INFO *cs); + static cmp_item* get_comparator(Item_result type, Item * warn_item, + CHARSET_INFO *cs); virtual cmp_item *make_same()= 0; virtual void store_value_by_template(cmp_item *tmpl, Item *item) { @@ -1146,7 +1147,7 @@ class Item_func_case :public Item_func Item_result cmp_type; DTCollation cmp_collation; enum_field_types cached_field_type; - cmp_item *cmp_items[5]; /* For all result types */ + cmp_item *cmp_items[6]; /* For all result types */ cmp_item *case_item; public: Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg) @@ -1236,7 +1237,7 @@ public: Item_int_func::cleanup(); delete array; array= 0; - for (i= 0; i <= (uint)DECIMAL_RESULT + 1; i++) + for (i= 0; i <= (uint)TIME_RESULT; i++) { delete cmp_items[i]; cmp_items[i]= 0; diff --git a/sql/item_func.cc b/sql/item_func.cc index 5a8e8a4defd..b3eb991b747 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -664,8 +664,8 @@ void Item_num_op::find_num_type(void) DBUG_ENTER("Item_num_op::find_num_type"); DBUG_PRINT("info", ("name %s", func_name())); DBUG_ASSERT(arg_count == 2); - Item_result r0= args[0]->result_type(); - Item_result r1= args[1]->result_type(); + Item_result r0= args[0]->cast_to_int_type(); + Item_result r1= args[1]->cast_to_int_type(); if (r0 == REAL_RESULT || r1 == REAL_RESULT || r0 == STRING_RESULT || r1 ==STRING_RESULT) @@ -674,7 +674,8 @@ void Item_num_op::find_num_type(void) max_length= float_length(decimals); hybrid_type= REAL_RESULT; } - else if (r0 == DECIMAL_RESULT || r1 == DECIMAL_RESULT) + else if (r0 == DECIMAL_RESULT || r1 == DECIMAL_RESULT || + r0 == TIME_RESULT || r1 == TIME_RESULT) { hybrid_type= DECIMAL_RESULT; result_precision(); @@ -705,7 +706,7 @@ void Item_func_num1::find_num_type() { DBUG_ENTER("Item_func_num1::find_num_type"); DBUG_PRINT("info", ("name %s", func_name())); - switch (hybrid_type= args[0]->result_type()) { + switch (hybrid_type= args[0]->cast_to_int_type()) { case INT_RESULT: unsigned_flag= args[0]->unsigned_flag; break; @@ -714,9 +715,10 @@ void Item_func_num1::find_num_type() hybrid_type= REAL_RESULT; max_length= float_length(decimals); break; + case TIME_RESULT: + hybrid_type= DECIMAL_RESULT; case DECIMAL_RESULT: break; - case TIME_RESULT: case ROW_RESULT: DBUG_ASSERT(0); } @@ -1815,7 +1817,7 @@ void Item_func_int_val::find_num_type() { DBUG_ENTER("Item_func_int_val::find_num_type"); DBUG_PRINT("info", ("name %s", func_name())); - switch(hybrid_type= args[0]->result_type()) + switch(hybrid_type= args[0]->cast_to_int_type()) { case STRING_RESULT: case REAL_RESULT: @@ -1823,6 +1825,7 @@ void Item_func_int_val::find_num_type() max_length= float_length(decimals); break; case INT_RESULT: + case TIME_RESULT: case DECIMAL_RESULT: /* -2 because in most high position can't be used any digit for longlong @@ -1840,7 +1843,6 @@ void Item_func_int_val::find_num_type() } break; case ROW_RESULT: - case TIME_RESULT: DBUG_ASSERT(0); } DBUG_PRINT("info", ("Type: %s", @@ -2240,16 +2242,9 @@ void Item_func_min_max::fix_length_and_dec() if (args[i]->maybe_null) maybe_null= 1; cmp_type= item_cmp_type(cmp_type,args[i]->result_type()); - if (args[i]->cmp_type() == TIME_RESULT) - { - if (!compare_as_dates || args[i]->field_type() == MYSQL_TYPE_DATETIME) - compare_as_dates= args[i]; - } } if (cmp_type == STRING_RESULT) - { agg_arg_charsets(collation, args, arg_count, MY_COLL_CMP_CONV, 1); - } else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT)) max_length= my_decimal_precision_to_length_no_truncation(max_int_part + decimals, decimals, @@ -2257,8 +2252,15 @@ void Item_func_min_max::fix_length_and_dec() else if (cmp_type == REAL_RESULT) max_length= float_length(decimals); + compare_as_dates= find_date_time_item(args, arg_count, 0); if (compare_as_dates) + { cached_field_type= compare_as_dates->field_type(); + if (mysql_type_to_time_type(cached_field_type) == MYSQL_TIMESTAMP_DATE) + decimals= 0; + else + set_if_smaller(decimals, TIME_SECOND_PART_DIGITS); + } else cached_field_type= agg_field_type(args, arg_count); } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index d2fd0c4b0eb..e38e1d237b6 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -500,7 +500,7 @@ Field *Item_sum::create_tmp_field(bool group, TABLE *table, field= Field_new_decimal::create_from_item(this); break; case ROW_RESULT: - default: + case TIME_RESULT: // This case should never be choosen DBUG_ASSERT(0); return 0; @@ -624,7 +624,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) max_length= item->max_length; break; case ROW_RESULT: - default: + case TIME_RESULT: DBUG_ASSERT(0); }; setup_hybrid(args[0], NULL); @@ -761,13 +761,14 @@ void Item_sum_sum::fix_length_and_dec() DBUG_ENTER("Item_sum_sum::fix_length_and_dec"); maybe_null=null_value=1; decimals= args[0]->decimals; - switch (args[0]->result_type()) { + switch (args[0]->cast_to_int_type()) { case REAL_RESULT: case STRING_RESULT: hybrid_type= REAL_RESULT; sum= 0.0; break; case INT_RESULT: + case TIME_RESULT: case DECIMAL_RESULT: { /* SUM result can't be longer than length(arg) + length(MAX_ROWS) */ @@ -781,7 +782,6 @@ void Item_sum_sum::fix_length_and_dec() break; } case ROW_RESULT: - default: DBUG_ASSERT(0); } DBUG_PRINT("info", ("Type: %s (%d, %d)", @@ -970,7 +970,7 @@ void Item_sum_distinct::fix_length_and_dec() table_field_type= MYSQL_TYPE_NEWDECIMAL; break; case ROW_RESULT: - default: + case TIME_RESULT: DBUG_ASSERT(0); } val.traits->fix_length_and_dec(this, args[0]); @@ -1421,7 +1421,7 @@ void Item_sum_variance::fix_length_and_dec() break; } case ROW_RESULT: - default: + case TIME_RESULT: DBUG_ASSERT(0); } DBUG_PRINT("info", ("Type: REAL_RESULT (%d, %d)", max_length, (int)decimals)); @@ -1832,7 +1832,7 @@ void Item_sum_hybrid::reset_field() break; } case ROW_RESULT: - default: + case TIME_RESULT: DBUG_ASSERT(0); } } diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 18fde7abce3..4174e8083e6 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -46,65 +46,6 @@ static bool make_datetime(MYSQL_TIME *ltime, String *str, uint decimals) /* - Convert seconds to MYSQL_TIME value with overflow checking - - SYNOPSIS: - sec_to_time() - seconds number of seconds - ltime output MYSQL_TIME value - - DESCRIPTION - If the 'seconds' argument is inside MYSQL_TIME data range, convert it to a - corresponding value. - Otherwise, truncate the resulting value to the nearest endpoint, and - produce a warning message. - - RETURN - 1 if the value was truncated during conversion - 0 otherwise -*/ - -bool Item_func_sec_to_time::sec_to_time(double seconds, MYSQL_TIME *ltime) -{ - Lazy_string_double str(seconds); - uint sec; - const double max_sec_val= TIME_MAX_VALUE_SECONDS + - TIME_MAX_SECOND_PART/(double)TIME_SECOND_PART_FACTOR; - - bzero((char *)ltime, sizeof(*ltime)); - - ltime->time_type= MYSQL_TIMESTAMP_TIME; - - if (seconds < 0) - { - ltime->neg= 1; - if (seconds < -max_sec_val) - goto overflow; - seconds= -seconds; - } - else if (seconds > max_sec_val) - goto overflow; - - sec= (uint) ((ulonglong) seconds % 3600); - ltime->hour= (uint) (seconds/3600); - ltime->minute= sec/60; - ltime->second= sec % 60; - ltime->second_part= (ulong)((seconds - floor(seconds))*TIME_SECOND_PART_FACTOR); - - return 0; - -overflow: - /* use check_time_range() to set ltime to the max value depending on dec */ - int unused; - ltime->hour= TIME_MAX_HOUR+1; - check_time_range(ltime, decimals, &unused); - make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, - &str, MYSQL_TIMESTAMP_TIME, NullS); - return 1; -} - - -/* Date formats corresponding to compound %r and %T conversion specifiers Note: We should init at least first element of "positions" array @@ -1225,14 +1166,15 @@ longlong Item_func_unix_timestamp::int_op() } -double Item_func_unix_timestamp::real_op() +my_decimal *Item_func_unix_timestamp::decimal_op(my_decimal* buf) { ulong second_part; my_time_t seconds; if (get_timestamp_value(&seconds, &second_part)) return 0; - return seconds + second_part/(double)TIME_SECOND_PART_FACTOR; + return seconds2my_decimal(seconds < 0, seconds < 0 ? -seconds : seconds, + second_part, buf); } @@ -1240,21 +1182,23 @@ longlong Item_func_time_to_sec::int_op() { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - longlong seconds; - (void) get_arg0_time(<ime); - seconds=ltime.hour*3600L+ltime.minute*60+ltime.second; + if (get_arg0_time(<ime)) + return 0; + + longlong seconds=ltime.hour*3600L+ltime.minute*60+ltime.second; return ltime.neg ? -seconds : seconds; } -double Item_func_time_to_sec::real_op() +my_decimal *Item_func_time_to_sec::decimal_op(my_decimal* buf) { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - double seconds; - (void) get_arg0_time(<ime); - seconds=ltime.hour*3600L+ltime.minute*60+ltime.second+ltime.second_part/1e6; - return ltime.neg ? -seconds : seconds; + if (get_arg0_time(<ime)) + return 0; + + longlong seconds= ltime.hour*3600L+ltime.minute*60+ltime.second; + return seconds2my_decimal(ltime.neg, seconds, ltime.second_part, buf); } @@ -1471,6 +1415,7 @@ void Item_func_curdate::fix_length_and_dec() ltime.hour= ltime.minute= ltime.second= 0; ltime.time_type= MYSQL_TIMESTAMP_DATE; Item_datefunc::fix_length_and_dec(); + maybe_null= false; } /** @@ -1639,17 +1584,45 @@ bool Item_func_sysdate_local::get_date(MYSQL_TIME *res, return 0; } - bool Item_func_sec_to_time::get_date(MYSQL_TIME *ltime, uint fuzzy_date) { DBUG_ASSERT(fixed == 1); - double arg_val= args[0]->val_real(); - + bool sign; + ulonglong sec; + ulong sec_part; + + bzero((char *)ltime, sizeof(*ltime)); + ltime->time_type= MYSQL_TIMESTAMP_TIME; + + sign= args[0]->get_seconds(&sec, &sec_part); + if ((null_value= args[0]->null_value)) return 1; - sec_to_time(arg_val, ltime); + ltime->neg= sign; + if (sec > TIME_MAX_VALUE_SECONDS) + goto overflow; + + DBUG_ASSERT(sec_part <= TIME_MAX_SECOND_PART); + ltime->hour= (uint) (sec/3600); + ltime->minute= (uint) (sec % 3600) /60; + ltime->second= (uint) sec % 60; + ltime->second_part= sec_part; + + return 0; + +overflow: + /* use check_time_range() to set ltime to the max value depending on dec */ + int unused; + char buf[100]; + String tmp(buf, sizeof(buf), &my_charset_bin), *err= args[0]->val_str(&tmp); + + ltime->hour= TIME_MAX_HOUR+1; + check_time_range(ltime, decimals, &unused); + make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + err->ptr(), err->length(), + MYSQL_TIMESTAMP_TIME, NullS); return 0; } @@ -1831,7 +1804,6 @@ null_date: void Item_func_from_unixtime::fix_length_and_dec() { thd= current_thd; - maybe_null= 1; thd->time_zone_used= 1; decimals= args[0]->decimals; Item_temporal_func::fix_length_and_dec(); @@ -1841,13 +1813,21 @@ void Item_func_from_unixtime::fix_length_and_dec() bool Item_func_from_unixtime::get_date(MYSQL_TIME *ltime, uint fuzzy_date __attribute__((unused))) { - double tmp= args[0]->val_real(); - if (args[0]->null_value || tmp < 0 || tmp > TIMESTAMP_MAX_VALUE) + bool sign; + ulonglong sec; + ulong sec_part; + + bzero((char *)ltime, sizeof(*ltime)); + ltime->time_type= MYSQL_TIMESTAMP_TIME; + + sign= args[0]->get_seconds(&sec, &sec_part); + + if (args[0]->null_value || sign || sec > TIMESTAMP_MAX_VALUE) return (null_value= 1); - thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)tmp); + thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)sec); - ltime->second_part= (ulong)((tmp - floor(tmp))*TIME_SECOND_PART_FACTOR); + ltime->second_part= sec_part; return (null_value= 0); } @@ -1855,7 +1835,6 @@ bool Item_func_from_unixtime::get_date(MYSQL_TIME *ltime, void Item_func_convert_tz::fix_length_and_dec() { - maybe_null= 1; decimals= args[0]->decimals; Item_temporal_func::fix_length_and_dec(); } @@ -1888,7 +1867,7 @@ bool Item_func_convert_tz::get_date(MYSQL_TIME *ltime, } { - my_bool not_used; + uint not_used; my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, ¬_used); ulong sec_part= ltime->second_part; /* my_time_tmp is guranteed to be in the allowed range */ @@ -1913,7 +1892,6 @@ void Item_func_convert_tz::cleanup() void Item_date_add_interval::fix_length_and_dec() { enum_field_types arg0_field_type; - maybe_null=1; /* The field type for the result of an Item_date function is defined as @@ -2149,10 +2127,17 @@ bool Item_char_typecast::eq(const Item *item, bool binary_cmp) const void Item_temporal_typecast::print(String *str, enum_query_type query_type) { + char buf[32]; str->append(STRING_WITH_LEN("cast(")); args[0]->print(str, query_type); str->append(STRING_WITH_LEN(" as ")); str->append(cast_type()); + if (decimals) + { + str->append('('); + str->append(llstr(decimals, buf)); + str->append(')'); + } str->append(')'); } @@ -2300,6 +2285,8 @@ bool Item_time_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date) { if (get_arg0_time(ltime)) return 1; + if (decimals < TIME_SECOND_PART_DIGITS) + ltime->second_part= sec_part_truncate(ltime->second_part, decimals); /* MYSQL_TIMESTAMP_TIME value can have non-zero day part, which we should not lose. @@ -2325,16 +2312,28 @@ bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date) if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY)) return 1; + if (decimals < TIME_SECOND_PART_DIGITS) + ltime->second_part= sec_part_truncate(ltime->second_part, 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 && ltime->hour >= 24) + if (ltime->time_type == MYSQL_TIMESTAMP_TIME) { - Lazy_string_time str(ltime); - make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, - &str, MYSQL_TIMESTAMP_DATETIME, 0); - return (null_value= 1); + if (ltime->neg) + { + Lazy_string_time str(ltime); + make_truncated_value_warning(current_thd, MYSQL_ERROR::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; } ltime->time_type= MYSQL_TIMESTAMP_DATETIME; @@ -2384,7 +2383,6 @@ void Item_func_add_time::fix_length_and_dec() { enum_field_types arg0_field_type; decimals= max(args[0]->decimals, args[1]->decimals); - maybe_null= 1; /* The field type for the result of an Item_func_add_time function is defined @@ -2653,7 +2651,7 @@ longlong Item_func_microsecond::val_int() { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - if (!get_arg0_time(<ime)) + if (!get_arg0_date(<ime, TIME_FUZZY_DATE)) return ltime.second_part; return 0; } @@ -2938,7 +2936,6 @@ get_date_time_result_type(const char *format, uint length) void Item_func_str_to_date::fix_length_and_dec() { - maybe_null= 1; cached_field_type= MYSQL_TYPE_DATETIME; decimals= NOT_FIXED_DEC; if ((const_item= args[1]->const_item())) diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index d50b0c20716..3b1f69d5383 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -306,12 +306,11 @@ public: { if (arg_count) decimals= args[0]->decimals; - if (decimals != NOT_FIXED_DEC) - set_if_smaller(decimals, TIME_SECOND_PART_DIGITS); + set_if_smaller(decimals, TIME_SECOND_PART_DIGITS); max_length=17 + (decimals ? decimals + 1 : 0); } - void find_num_type() { hybrid_type= decimals ? REAL_RESULT : INT_RESULT; } - my_decimal *decimal_op(my_decimal* buf) { DBUG_ASSERT(0); return 0; } + void find_num_type() { hybrid_type= decimals ? DECIMAL_RESULT : INT_RESULT; } + double real_op() { DBUG_ASSERT(0); return 0; } String *str_op(String *str) { DBUG_ASSERT(0); return 0; } }; @@ -338,7 +337,7 @@ public: return !has_timestamp_args(); } longlong int_op(); - double real_op(); + my_decimal *decimal_op(my_decimal* buf); }; @@ -354,7 +353,7 @@ public: } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} longlong int_op(); - double real_op(); + my_decimal *decimal_op(my_decimal* buf); }; @@ -383,6 +382,7 @@ public: { MAX_DATETIME_WIDTH, MAX_DATETIME_WIDTH, MAX_DATE_WIDTH, MAX_DATETIME_WIDTH, MIN_TIME_WIDTH }; + maybe_null= true; max_length= max_time_type_width[mysql_type_to_time_type(field_type())+2]; if (decimals) { @@ -403,9 +403,6 @@ public: Item_datefunc() :Item_temporal_func() { } Item_datefunc(Item *a) :Item_temporal_func(a) { } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } - const char *func_name() const { return "date"; } - bool get_date(MYSQL_TIME *res, uint fuzzy_date) - { return get_arg0_date(res, fuzzy_date); } }; @@ -432,6 +429,7 @@ public: { store_now_in_TIME(<ime); Item_timefunc::fix_length_and_dec(); + maybe_null= false; } bool get_date(MYSQL_TIME *res, uint fuzzy_date); /* @@ -504,6 +502,7 @@ public: { store_now_in_TIME(<ime); Item_temporal_func::fix_length_and_dec(); + maybe_null= false; } bool get_date(MYSQL_TIME *res, uint fuzzy_date); virtual void store_now_in_TIME(MYSQL_TIME *now_time)=0; @@ -623,13 +622,12 @@ class Item_func_convert_tz :public Item_temporal_func class Item_func_sec_to_time :public Item_timefunc { - bool sec_to_time(double seconds, MYSQL_TIME *ltime); + bool sec_to_time(longlong seconds, ulong sec_part, MYSQL_TIME *ltime); public: Item_func_sec_to_time(Item *item) :Item_timefunc(item) {} bool get_date(MYSQL_TIME *res, uint fuzzy_date); void fix_length_and_dec() { - maybe_null=1; decimals= args[0]->decimals; Item_timefunc::fix_length_and_dec(); } @@ -699,7 +697,6 @@ public: void print(String *str, enum_query_type query_type); void fix_length_and_dec() { - maybe_null= 1; if (decimals == NOT_FIXED_DEC) decimals= args[0]->decimals; Item_temporal_func::fix_length_and_dec(); @@ -746,12 +743,6 @@ public: Item_func_makedate(Item *a,Item *b) :Item_temporal_func(a,b) {} const char *func_name() const { return "makedate"; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } - void fix_length_and_dec() - { - /* It returns NULL when the second argument is less or equal to 0 */ - maybe_null= 1; - Item_temporal_func::fix_length_and_dec(); - } bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); }; @@ -782,7 +773,6 @@ public: { decimals= max(args[0]->decimals, args[1]->decimals); Item_timefunc::fix_length_and_dec(); - maybe_null= 1; } bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); }; @@ -792,9 +782,7 @@ class Item_func_maketime :public Item_timefunc public: Item_func_maketime(Item *a, Item *b, Item *c) :Item_timefunc(a, b, c) - { - maybe_null= TRUE; - } + {} const char *func_name() const { return "maketime"; } bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); }; @@ -877,9 +865,4 @@ public: Item_func_last_day(Item *a) :Item_datefunc(a) {} const char *func_name() const { return "last_day"; } bool get_date(MYSQL_TIME *res, uint fuzzy_date); - void fix_length_and_dec() - { - maybe_null=1; - Item_datefunc::fix_length_and_dec(); - } }; diff --git a/sql/log.cc b/sql/log.cc index dea93fa1f46..2899fd721e5 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -598,11 +598,11 @@ bool Log_to_csv_event_handler:: /* fill in query_time field */ calc_time_from_sec(&t, query_time, query_time_micro); - if (table->field[2]->store_time(&t, MYSQL_TIMESTAMP_TIME)) + if (table->field[2]->store_time(&t)) goto err; /* lock_time */ calc_time_from_sec(&t, lock_time, lock_time_micro); - if (table->field[3]->store_time(&t, MYSQL_TIMESTAMP_TIME)) + if (table->field[3]->store_time(&t)) goto err; /* rows_sent */ if (table->field[4]->store((longlong) thd->sent_row_count, TRUE)) diff --git a/sql/my_decimal.cc b/sql/my_decimal.cc index 3aa01880b83..e062867e1c0 100644 --- a/sql/my_decimal.cc +++ b/sql/my_decimal.cc @@ -16,6 +16,10 @@ #include "mysql_priv.h" #include <time.h> +#define DIG_BASE 1000000000 +#define DIG_PER_DEC1 9 +#define ROUND_UP(X) (((X)+DIG_PER_DEC1-1)/DIG_PER_DEC1) + #ifndef MYSQL_CLIENT /** @@ -208,20 +212,69 @@ int str2my_decimal(uint mask, const char *from, uint length, } +/** + converts a decimal into a pair of integers - for integer and fractional parts + + special version, for decimals representing number of seconds. + integer part cannot be larger that 1e18 (otherwise it's an overflow). + fractional part is microseconds. +*/ +bool my_decimal2seconds(const my_decimal *d, ulonglong *sec, ulong *microsec) +{ + int pos; + + if (d->intg) + { + pos= (d->intg-1)/DIG_PER_DEC1; + *sec= d->buf[pos]; + if (pos > 0) + *sec+= static_cast<longlong>(d->buf[pos-1]) * DIG_BASE; + } + else + { + *sec=0; + pos= -1; + } + + *microsec= d->frac ? static_cast<longlong>(d->buf[pos+1]) / (DIG_BASE/1000000) : 0; + + if (pos > 1) + { + for (int i=0; i < pos-1; i++) + if (d->buf[i]) + { + *sec= LONGLONG_MAX; + break; + } + } + return d->sign(); +} + + +/** + converts a pair of integers (seconds, microseconds) into a decimal +*/ +my_decimal *seconds2my_decimal(bool sign, + ulonglong sec, ulong microsec, my_decimal *d) +{ + d->init(); + longlong2decimal(sec, d); // cannot fail + if (microsec) + { + d->buf[(d->intg-1) / DIG_PER_DEC1 + 1]= microsec * (DIG_BASE/1000000); + d->frac= 6; + } + ((decimal_t *)d)->sign= sign; + return d; +} + + my_decimal *date2my_decimal(MYSQL_TIME *ltime, my_decimal *dec) { - longlong date; - date = (ltime->year*100L + ltime->month)*100L + ltime->day; + longlong date= (ltime->year*100L + ltime->month)*100L + ltime->day; if (ltime->time_type > MYSQL_TIMESTAMP_DATE) date= ((date*100L + ltime->hour)*100L+ ltime->minute)*100L + ltime->second; - if (int2my_decimal(E_DEC_FATAL_ERROR, ltime->neg ? -date : date, FALSE, dec)) - return dec; - if (ltime->second_part) - { - dec->buf[(dec->intg-1) / 9 + 1]= ltime->second_part * 1000; - dec->frac= 6; - } - return dec; + return seconds2my_decimal(ltime->neg, date, ltime->second_part, dec); } @@ -235,13 +288,9 @@ void my_decimal_trim(ulong *precision, uint *scale) } } - #ifndef DBUG_OFF /* routines for debugging print */ -#define DIG_PER_DEC1 9 -#define ROUND_UP(X) (((X)+DIG_PER_DEC1-1)/DIG_PER_DEC1) - /* print decimal */ void print_decimal(const my_decimal *dec) diff --git a/sql/my_decimal.h b/sql/my_decimal.h index a5077f397e3..37ce146b281 100644 --- a/sql/my_decimal.h +++ b/sql/my_decimal.h @@ -294,6 +294,11 @@ int my_decimal2string(uint mask, const my_decimal *d, uint fixed_prec, uint fixed_dec, char filler, String *str); #endif +bool my_decimal2seconds(const my_decimal *d, ulonglong *sec, ulong *microsec); + +my_decimal *seconds2my_decimal(bool sign, ulonglong sec, ulong microsec, + my_decimal *d); + inline int my_decimal2int(uint mask, const my_decimal *d, my_bool unsigned_flag, longlong *l) diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 035f546f37a..f984668e69f 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -781,6 +781,18 @@ mysql_type_to_time_type(enum enum_field_types mysql_type) #include "sql_profile.h" #include "sql_partition.h" +class Lazy_string_decimal: public Lazy_string +{ + const my_decimal *d; +public: + Lazy_string_decimal(const my_decimal *d_arg) + : Lazy_string(), d(d_arg) {} + void copy(String *dst) const { + my_decimal2string(E_DEC_FATAL_ERROR, d, + 0, 0, ' ', dst); + } +}; + class user_var_entry; class Security_context; enum enum_var_type @@ -1883,6 +1895,7 @@ void flush_thread_cache(); /* item_func.cc */ extern bool check_reserved_words(LEX_STRING *name); extern enum_field_types agg_field_type(Item **items, uint nitems); +Item *find_date_time_item(Item **args, uint nargs, uint col); /* strfunc.cc */ ulonglong find_set(TYPELIB *lib, const char *x, uint length, CHARSET_INFO *cs, @@ -2235,6 +2248,18 @@ void make_truncated_value_warning(THD *thd, MYSQL_ERROR::enum_warning_level leve const Lazy_string *str_val, timestamp_type time_type, const char *field_name); +bool double_to_datetime_with_warn(double value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name); +bool decimal_to_datetime_with_warn(const my_decimal *value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name); +bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name); static inline void make_truncated_value_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, const char *str_val, diff --git a/sql/set_var.cc b/sql/set_var.cc index 376364bab60..d66a72fcf2b 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -2715,15 +2715,19 @@ int set_var_collation_client::update(THD *thd) bool sys_var_timestamp::check(THD *thd, set_var *var) { - double val= var->value->val_real(); - if (val < 0 || val > MY_TIME_T_MAX) + ulonglong sec; + ulong sec_part; + if (var->value->get_seconds(&sec, &sec_part)) + return TRUE; + + if (sec > TIMESTAMP_MAX_VALUE) { my_message(ER_UNKNOWN_ERROR, "This version of MySQL doesn't support dates later than 2038", MYF(0)); return TRUE; } - var->save_result.ulonglong_value= hrtime_from_time(var->value->val_real()); + var->save_result.ulonglong_value= hrtime_from_time(sec)+sec_part; return FALSE; } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index c45c4b7dd83..590f705d20e 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2332,11 +2332,7 @@ mysql_execute_command(THD *thd) goto error; } it= new Item_func_unix_timestamp(it); - /* - it is OK only emulate fix_fieds, because we need only - value of constant - */ - it->quick_fix_field(); + it->fix_fields(thd, &it); res = purge_master_logs_before_date(thd, (ulong)it->val_int()); break; } diff --git a/sql/sql_show.cc b/sql/sql_show.cc index c5df62c09cf..32a4dc66681 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -3804,21 +3804,21 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t) file->stats.create_time); - table->field[14]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[14]->store_time(&time); table->field[14]->set_notnull(); } if (file->stats.update_time) { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t) file->stats.update_time); - table->field[15]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[15]->store_time(&time); table->field[15]->set_notnull(); } if (file->stats.check_time) { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t) file->stats.check_time); - table->field[16]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[16]->store_time(&time); table->field[16]->set_notnull(); } if (file->ha_table_flags() & (ulong) HA_HAS_CHECKSUM) @@ -4286,10 +4286,10 @@ bool store_schema_proc(THD *thd, TABLE *table, TABLE *proc_table, bzero((char *)&time, sizeof(time)); ((Field_timestamp *) proc_table->field[12])->get_time(&time); - table->field[15]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[15]->store_time(&time); bzero((char *)&time, sizeof(time)); ((Field_timestamp *) proc_table->field[13])->get_time(&time); - table->field[16]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[16]->store_time(&time); copy_field_as_string(table->field[17], proc_table->field[14]); copy_field_as_string(table->field[18], proc_table->field[15]); table->field[19]->store(definer.ptr(), definer.length(), cs); @@ -4925,21 +4925,21 @@ static void store_schema_partitions_record(THD *thd, TABLE *schema_table, { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t)stat_info.create_time); - table->field[18]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[18]->store_time(&time); table->field[18]->set_notnull(); } if (stat_info.update_time) { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t)stat_info.update_time); - table->field[19]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[19]->store_time(&time); table->field[19]->set_notnull(); } if (stat_info.check_time) { thd->variables.time_zone->gmt_sec_to_TIME(&time, (my_time_t)stat_info.check_time); - table->field[20]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + table->field[20]->store_time(&time); table->field[20]->set_notnull(); } if (file->ha_table_flags() & (ulong) HA_HAS_CHECKSUM) @@ -5321,15 +5321,13 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) /* starts & ends . STARTS is always set - see sql_yacc.yy */ et.time_zone->gmt_sec_to_TIME(&time, et.starts); sch_table->field[ISE_STARTS]->set_notnull(); - sch_table->field[ISE_STARTS]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_STARTS]->store_time(&time); if (!et.ends_null) { et.time_zone->gmt_sec_to_TIME(&time, et.ends); sch_table->field[ISE_ENDS]->set_notnull(); - sch_table->field[ISE_ENDS]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_ENDS]->store_time(&time); } } else @@ -5339,8 +5337,7 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) et.time_zone->gmt_sec_to_TIME(&time, et.execute_at); sch_table->field[ISE_EXECUTE_AT]->set_notnull(); - sch_table->field[ISE_EXECUTE_AT]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_EXECUTE_AT]->store_time(&time); } /* status */ @@ -5370,21 +5367,19 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) sch_table->field[ISE_ON_COMPLETION]-> store(STRING_WITH_LEN("PRESERVE"), scs); - number_to_datetime(et.created, &time, 0, ¬_used); + number_to_datetime(et.created, 0, &time, 0, ¬_used); DBUG_ASSERT(not_used==0); - sch_table->field[ISE_CREATED]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_CREATED]->store_time(&time); - number_to_datetime(et.modified, &time, 0, ¬_used); + number_to_datetime(et.modified, 0, &time, 0, ¬_used); DBUG_ASSERT(not_used==0); - sch_table->field[ISE_LAST_ALTERED]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_LAST_ALTERED]->store_time(&time); if (et.last_executed) { et.time_zone->gmt_sec_to_TIME(&time, et.last_executed); sch_table->field[ISE_LAST_EXECUTED]->set_notnull(); - sch_table->field[ISE_LAST_EXECUTED]-> - store_time(&time, MYSQL_TIMESTAMP_DATETIME); + sch_table->field[ISE_LAST_EXECUTED]->store_time(&time); } sch_table->field[ISE_EVENT_COMMENT]-> diff --git a/sql/time.cc b/sql/time.cc index ba81fcc86c2..4285e02fb65 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -242,6 +242,109 @@ str_to_datetime_with_warn(const char *str, uint length, MYSQL_TIME *l_time, } +/** + converts a pair of numbers (integer part, microseconds) to MYSQL_TIME + + @param neg sign of the time value + @param nr integer part of the number to convert + @param sec_part microsecond part of the number + @param ltime converted value will be written here + @param fuzzydate conversion flags (TIME_FUZZY_DATE, etc) + @param str original number, as a Lazy_string. For the warning + @param f_type how to treat the number as. Possible values + MYSQL_TYPE_TIME, MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME + or MYSQL_TYPE_SET (which means auto-set to TIME or + DATETIME depending on fuzzydate) + @param name field name for the warning message; NULL if not a field + + @returns 0 for success, 1 for a failure +*/ +static bool number_to_time_with_warn(bool neg, ulonglong nr, ulong sec_part, + MYSQL_TIME *ltime, ulong fuzzydate, + const Lazy_string *str, + enum_field_types f_type, + const char *name) +{ + int was_cut; + longlong res; + + if (f_type == MYSQL_TYPE_SET) + f_type= fuzzydate & TIME_TIME_ONLY ? MYSQL_TYPE_TIME + : MYSQL_TYPE_DATETIME; + + switch (f_type) { + case MYSQL_TYPE_YEAR: + bzero(ltime, sizeof(*ltime)); + ltime->year= nr; + ltime->time_type= MYSQL_TIMESTAMP_DATE; + if (nr > 9999 || (fuzzydate & (TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))) + res= -1; + break; + case MYSQL_TYPE_TIME: + res= number_to_time(neg, nr, sec_part, ltime, &was_cut); + break; + default: + res= number_to_datetime(nr, sec_part, ltime, fuzzydate, &was_cut); + break; + } + if (res >= 0) + return 0; + + make_truncated_value_warning(current_thd, + MYSQL_ERROR::WARN_LEVEL_WARN, str, + mysql_type_to_time_type(f_type), + name); + return 1; +} + + +bool double_to_datetime_with_warn(double value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name) +{ + const Lazy_string_double str(value); + ulonglong nr; + ulong sec_part; + bool neg= value < 0; + + if (neg) + value= -value; + + nr = value > LONGLONG_MAX ? LONGLONG_MAX + : static_cast<ulonglong>(trunc(value)); + sec_part= (ulong)((value - nr)*TIME_SECOND_PART_FACTOR); + return number_to_time_with_warn(neg, nr, sec_part, ltime, fuzzydate, + &str, f_type, name); +} + + +bool decimal_to_datetime_with_warn(const my_decimal *value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name) +{ + const Lazy_string_decimal str(value); + ulonglong nr; + ulong sec_part; + bool neg= my_decimal2seconds(value, &nr, &sec_part); + return number_to_time_with_warn(neg, nr, sec_part, ltime, fuzzydate, + &str, f_type, name); +} + + +bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime, + ulong fuzzydate, + enum_field_types f_type, + const char *name) +{ + const Lazy_string_num str(value); + bool neg= value < 0; + return number_to_time_with_warn(neg, neg ? -value : value, 0, ltime, + fuzzydate, &str, f_type, name); +} + + /* Convert a datetime from broken-down MYSQL_TIME representation to corresponding TIMESTAMP value. @@ -263,23 +366,8 @@ str_to_datetime_with_warn(const char *str, uint length, MYSQL_TIME *l_time, */ my_time_t TIME_to_timestamp(THD *thd, const MYSQL_TIME *t, uint *error_code) { - my_time_t timestamp; - my_bool in_dst_time_gap= 0; - - *error_code= 0; thd->time_zone_used= 1; - - timestamp= thd->variables.time_zone->TIME_to_gmt_sec(t, &in_dst_time_gap); - if (timestamp) - { - if (in_dst_time_gap) - *error_code= ER_WARN_INVALID_TIMESTAMP; - return timestamp; - } - - /* If we are here we have range error. */ - *error_code= ER_WARN_DATA_OUT_OF_RANGE; - return 0; + return thd->variables.time_zone->TIME_to_gmt_sec(t, error_code); } @@ -757,6 +845,7 @@ void make_truncated_value_warning(THD *thd, MYSQL_ERROR::enum_warning_level leve (((((X)->day * 24LL + (X)->hour) * 60LL + \ (X)->minute) * 60LL + (X)->second)*1000000LL + \ (X)->second_part) +#define GET_PART(X, N) X % N ## LL; X/= N ## LL bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type, INTERVAL interval) { @@ -782,7 +871,7 @@ bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type, INTERVAL inter case INTERVAL_DAY: { longlong usec, daynr; - my_bool neg= ltime->neg; + my_bool neg= 0; enum enum_mysql_timestamp_type time_type= ltime->time_type; if (time_type != MYSQL_TIMESTAMP_TIME) @@ -790,17 +879,31 @@ bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type, INTERVAL inter usec= COMBINE(ltime) + sign*COMBINE(&interval); - unpack_time(usec, ltime); - ltime->time_type= time_type; + if (usec < 0) + { + neg= 1; + usec= -usec; + } + + ltime->second_part= GET_PART(usec, 1000000); + ltime->second= GET_PART(usec, 60); + ltime->minute= GET_PART(usec, 60); ltime->neg^= neg; if (time_type == MYSQL_TIMESTAMP_TIME) - break; + { + if (usec > TIME_MAX_HOUR) + goto invalid_date; + ltime->hour= usec; + ltime->day= 0; + return 0; + } if (int_type != INTERVAL_DAY) ltime->time_type= MYSQL_TIMESTAMP_DATETIME; // Return full date - daynr= usec/1000000/24/60/60; + ltime->hour= GET_PART(usec, 24); + daynr= usec; /* Day number from year 0 to 9999-12-31 */ if ((ulonglong) daynr > MAX_DAY_NUMBER) @@ -846,13 +949,15 @@ bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type, INTERVAL inter goto null_date; } - return 0; // Ok + if (ltime->time_type != MYSQL_TIMESTAMP_TIME) + return 0; // Ok invalid_date: push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_DATETIME_FUNCTION_OVERFLOW, ER(ER_DATETIME_FUNCTION_OVERFLOW), - "datetime"); + ltime->time_type == MYSQL_TIMESTAMP_TIME ? + "time" : "datetime"); null_date: return 1; } diff --git a/sql/tztime.cc b/sql/tztime.cc index 7ebb8eb392a..9921f9db0d4 100644 --- a/sql/tztime.cc +++ b/sql/tztime.cc @@ -815,9 +815,11 @@ sec_since_epoch(int year, int mon, int mday, int hour, int min ,int sec) TIME_to_gmt_sec() t - pointer to structure for broken down represenatation sp - pointer to struct with time zone description - in_dst_time_gap - pointer to bool which is set to true if datetime - value passed doesn't really exist (i.e. falls into - spring time-gap) and is not touched otherwise. + error_code - 0, if the conversion was successful; + ER_WARN_DATA_OUT_OF_RANGE, if t contains datetime value + which is out of TIMESTAMP range; + ER_WARN_INVALID_TIMESTAMP, if t represents value which + doesn't exists (falls into the spring time-gap). DESCRIPTION This is mktime analog for MySQL. It is essentially different @@ -880,8 +882,7 @@ sec_since_epoch(int year, int mon, int mday, int hour, int min ,int sec) 0 in case of error. */ static my_time_t -TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, - my_bool *in_dst_time_gap) +TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, uint *error_code) { my_time_t local_t; uint saved_seconds; @@ -891,8 +892,12 @@ TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, DBUG_ENTER("TIME_to_gmt_sec"); if (!validate_timestamp_range(t)) + { + *error_code= ER_WARN_DATA_OUT_OF_RANGE; DBUG_RETURN(0); + } + *error_code= 0; /* We need this for correct leap seconds handling */ if (t->second < SECS_PER_MIN) @@ -936,6 +941,7 @@ TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, This means that source time can't be represented as my_time_t due to limited my_time_t range. */ + *error_code= ER_WARN_DATA_OUT_OF_RANGE; DBUG_RETURN(0); } @@ -952,6 +958,7 @@ TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, if (local_t > (my_time_t) (TIMESTAMP_MAX_VALUE - shift * SECS_PER_DAY + sp->revtis[i].rt_offset - saved_seconds)) { + *error_code= ER_WARN_DATA_OUT_OF_RANGE; DBUG_RETURN(0); /* my_time_t overflow */ } local_t+= shift * SECS_PER_DAY; @@ -965,7 +972,7 @@ TIME_to_gmt_sec(const MYSQL_TIME *t, const TIME_ZONE_INFO *sp, Now we are returning my_time_t value corresponding to the beginning of the gap. */ - *in_dst_time_gap= 1; + *error_code= ER_WARN_INVALID_TIMESTAMP; local_t= sp->revts[i] - sp->revtis[i].rt_offset + saved_seconds; } else @@ -1007,8 +1014,7 @@ class Time_zone_system : public Time_zone { public: Time_zone_system() {} /* Remove gcc warning */ - virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, - my_bool *in_dst_time_gap) const; + virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const; virtual void gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const; virtual const String * get_name() const; }; @@ -1022,9 +1028,11 @@ public: TIME_to_gmt_sec() t - pointer to MYSQL_TIME structure with local time in broken-down representation. - in_dst_time_gap - pointer to bool which is set to true if datetime - value passed doesn't really exist (i.e. falls into - spring time-gap) and is not touched otherwise. + error_code - 0, if the conversion was successful; + ER_WARN_DATA_OUT_OF_RANGE, if t contains datetime value + which is out of TIMESTAMP range; + ER_WARN_INVALID_TIMESTAMP, if t represents value which + doesn't exists (falls into the spring time-gap). DESCRIPTION This method uses system function (localtime_r()) for conversion @@ -1040,10 +1048,10 @@ public: Corresponding my_time_t value or 0 in case of error */ my_time_t -Time_zone_system::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) const +Time_zone_system::TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const { long not_used; - return my_system_gmt_sec(t, ¬_used, in_dst_time_gap); + return my_system_gmt_sec(t, ¬_used, error_code); } @@ -1103,7 +1111,7 @@ class Time_zone_utc : public Time_zone public: Time_zone_utc() {} /* Remove gcc warning */ virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, - my_bool *in_dst_time_gap) const; + uint *error_code) const; virtual void gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const; virtual const String * get_name() const; }; @@ -1112,14 +1120,6 @@ public: /* Convert UTC time from MYSQL_TIME representation to its my_time_t representation. - SYNOPSIS - TIME_to_gmt_sec() - t - pointer to MYSQL_TIME structure with local time - in broken-down representation. - in_dst_time_gap - pointer to bool which is set to true if datetime - value passed doesn't really exist (i.e. falls into - spring time-gap) and is not touched otherwise. - DESCRIPTION Since Time_zone_utc is used only internally for my_time_t -> TIME conversions, this function of Time_zone interface is not implemented for @@ -1129,10 +1129,11 @@ public: 0 */ my_time_t -Time_zone_utc::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) const +Time_zone_utc::TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const { /* Should be never called */ DBUG_ASSERT(0); + *error_code= ER_WARN_DATA_OUT_OF_RANGE; return 0; } @@ -1192,8 +1193,7 @@ class Time_zone_db : public Time_zone { public: Time_zone_db(TIME_ZONE_INFO *tz_info_arg, const String * tz_name_arg); - virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, - my_bool *in_dst_time_gap) const; + virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const; virtual void gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const; virtual const String * get_name() const; private: @@ -1230,9 +1230,11 @@ Time_zone_db::Time_zone_db(TIME_ZONE_INFO *tz_info_arg, TIME_to_gmt_sec() t - pointer to MYSQL_TIME structure with local time in broken-down representation. - in_dst_time_gap - pointer to bool which is set to true if datetime - value passed doesn't really exist (i.e. falls into - spring time-gap) and is not touched otherwise. + error_code - 0, if the conversion was successful; + ER_WARN_DATA_OUT_OF_RANGE, if t contains datetime value + which is out of TIMESTAMP range; + ER_WARN_INVALID_TIMESTAMP, if t represents value which + doesn't exists (falls into the spring time-gap). DESCRIPTION Please see ::TIME_to_gmt_sec for function description and @@ -1242,9 +1244,9 @@ Time_zone_db::Time_zone_db(TIME_ZONE_INFO *tz_info_arg, Corresponding my_time_t value or 0 in case of error */ my_time_t -Time_zone_db::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) const +Time_zone_db::TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const { - return ::TIME_to_gmt_sec(t, tz_info, in_dst_time_gap); + return ::TIME_to_gmt_sec(t, tz_info, error_code); } @@ -1290,7 +1292,7 @@ class Time_zone_offset : public Time_zone public: Time_zone_offset(long tz_offset_arg); virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, - my_bool *in_dst_time_gap) const; + uint *error_code) const; virtual void gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const; virtual const String * get_name() const; /* @@ -1332,17 +1334,17 @@ Time_zone_offset::Time_zone_offset(long tz_offset_arg): TIME_to_gmt_sec() t - pointer to MYSQL_TIME structure with local time in broken-down representation. - in_dst_time_gap - pointer to bool which should be set to true if - datetime value passed doesn't really exist - (i.e. falls into spring time-gap) and is not - touched otherwise. - It is not really used in this class. + error_code - 0, if the conversion was successful; + ER_WARN_DATA_OUT_OF_RANGE, if t contains datetime value + which is out of TIMESTAMP range; + ER_WARN_INVALID_TIMESTAMP, if t represents value which + doesn't exists (falls into the spring time-gap). RETURN VALUE Corresponding my_time_t value or 0 in case of error */ my_time_t -Time_zone_offset::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) const +Time_zone_offset::TIME_to_gmt_sec(const MYSQL_TIME *t, uint *error_code) const { my_time_t local_t; int shift= 0; @@ -1352,7 +1354,11 @@ Time_zone_offset::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) us to make all validation checks here. */ if (!validate_timestamp_range(t)) + { + *error_code= ER_WARN_DATA_OUT_OF_RANGE; return 0; + } + *error_code= 0; /* Do a temporary shift of the boundary dates to avoid @@ -1376,6 +1382,7 @@ Time_zone_offset::TIME_to_gmt_sec(const MYSQL_TIME *t, my_bool *in_dst_time_gap) return local_t; /* range error*/ + *error_code= ER_WARN_DATA_OUT_OF_RANGE; return 0; } @@ -2733,7 +2740,7 @@ main(int argc, char **argv) for (time_tmp.second=0; time_tmp.second<60; time_tmp.second+=25) { long not_used; - my_bool not_used_2; + uint not_used_2; t= (time_t)my_system_gmt_sec(&time_tmp, ¬_used, ¬_used_2); t1= (time_t)TIME_to_gmt_sec(&time_tmp, &tz_info, ¬_used_2); if (t != t1) diff --git a/sql/tztime.h b/sql/tztime.h index 9bf103519c4..306f76dfece 100644 --- a/sql/tztime.h +++ b/sql/tztime.h @@ -33,11 +33,11 @@ public: /** Converts local time in broken down MYSQL_TIME representation to my_time_t (UTC seconds since Epoch) represenation. - Returns 0 in case of error. Sets in_dst_time_gap to true if date provided - falls into spring time-gap (or lefts it untouched otherwise). + Returns 0 in case of error. May set error_code to ER_WARN_DATA_OUT_OF_RANGE + or ER_WARN_INVALID_TIMESTAMP, see TIME_to_timestamp()) */ virtual my_time_t TIME_to_gmt_sec(const MYSQL_TIME *t, - my_bool *in_dst_time_gap) const = 0; + uint *error_code) const = 0; /** Converts time in my_time_t representation to local time in broken down MYSQL_TIME representation. diff --git a/sql/unireg.h b/sql/unireg.h index 50facf51d02..4f3a376ccf9 100644 --- a/sql/unireg.h +++ b/sql/unireg.h @@ -72,7 +72,7 @@ #define MAX_BIT_FIELD_LENGTH 64 /* Max length in bits for bit fields */ #define MAX_DATE_WIDTH 10 /* YYYY-MM-DD */ -#define MIN_TIME_WIDTH 9 /* HHH:MM:SS */ +#define MIN_TIME_WIDTH 10 /* -HHH:MM:SS */ #define MAX_TIME_WIDTH 16 /* -DDDDDD HH:MM:SS */ #define MAX_TIME_FULL_WIDTH 23 /* -DDDDDD HH:MM:SS.###### */ #define MAX_DATETIME_FULL_WIDTH 29 /* YYYY-MM-DD HH:MM:SS.###### AM */ diff --git a/storage/innodb_plugin/handler/i_s.cc b/storage/innodb_plugin/handler/i_s.cc index 9ad2d656365..8060c9af2a8 100644 --- a/storage/innodb_plugin/handler/i_s.cc +++ b/storage/innodb_plugin/handler/i_s.cc @@ -161,7 +161,7 @@ field_store_time_t( my_time.time_type = MYSQL_TIMESTAMP_DATETIME; #endif - return(field->store_time(&my_time, MYSQL_TIMESTAMP_DATETIME)); + return(field->store_time(&my_time)); } /*******************************************************************//** diff --git a/strings/decimal.c b/strings/decimal.c index bda296ce832..8cc5bd82bca 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -30,7 +30,7 @@ integer that determines the number of significant digits in a particular radix R, where R is either 2 or 10. S is a non-negative integer. Every value of an exact numeric type of scale S is of the - form n*10^{-S}, where n is an integer such that -R^P <= n <= R^P. + form n*10^{-S}, where n is an integer such that -R^P <= n <= R^P. [...] diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 856b3c913d4..f3dbd3493f8 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -12501,7 +12501,7 @@ static void test_datetime_ranges() rc= mysql_stmt_execute(stmt); check_execute(stmt, rc); - DIE_UNLESS(mysql_warning_count(mysql) != 6); + DIE_UNLESS(mysql_warning_count(mysql) == 6); verify_col_data("t1", "year", "0000-00-00 00:00:00"); verify_col_data("t1", "month", "0000-00-00 00:00:00"); @@ -12532,7 +12532,7 @@ static void test_datetime_ranges() rc= mysql_stmt_execute(stmt); check_execute(stmt, rc); - DIE_UNLESS(mysql_warning_count(mysql) != 3); + DIE_UNLESS(mysql_warning_count(mysql) == 3); verify_col_data("t1", "year", "0000-00-00 00:00:00"); verify_col_data("t1", "month", "0000-00-00 00:00:00"); |