diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/dyncol.test | 7 | ||||
| -rw-r--r-- | mysql-test/t/func_group.test | 3 | ||||
| -rw-r--r-- | mysql-test/t/func_sapdb.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/func_time.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/func_time_hires.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/parser_precedence.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/type_datetime.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/type_datetime_hires.test | 68 | ||||
| -rw-r--r-- | mysql-test/t/type_time_hires.test | 9 |
9 files changed, 39 insertions, 86 deletions
diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index 51609d56686..5ff6d42450b 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -110,6 +110,7 @@ select column_get(column_create(1, "-1212" AS char), 1 as int); select column_get(column_create(1, "2011-04-05" AS date), 1 as int); select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int); select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as int); +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as int); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as int); select column_get(column_create(1, NULL AS int), 1 as int); --echo #column gett truncation & warnings @@ -145,6 +146,7 @@ select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8 select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8); select column_get(column_create(1, "8:46:06.23434" AS time(0)), 1 as char charset utf8); select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as char charset utf8); +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as char charset utf8); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(0)), 1 as char charset utf8); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as char charset utf8); @@ -169,6 +171,7 @@ select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 select column_get(column_create(1, "2011-04-05" AS date), 1 as double); select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double); select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as double); +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as double); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double); select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as double); # The replace result is needed for windows. @@ -197,6 +200,7 @@ select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)); select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)); select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as decimal(32,6)); +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as decimal(32,6)); select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime), 1 as decimal(32,6)); select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime(6)), 1 as decimal(32,6)); select column_get(column_create(1, "2011-04-05 8:46:06.12345678" AS datetime(6)), 1 as decimal(32,8)); @@ -245,6 +249,7 @@ select column_get(column_create(1, 0), 1 as datetime); select column_get(column_create(1, "2001021"), 1 as datetime); select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime); +select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime); set @@sql_mode="allow_invalid_dates"; select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime); @@ -318,7 +323,9 @@ select column_get(column_create(1, "0" AS CHAR), 1 as date); select column_get(column_create(1, 20010203101112.121314 as double), 1 as time); select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time); select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time); +select column_get(column_create(1, 8080102 as unsigned int), 1 as time); select column_get(column_create(1, 20010203101112 as int), 1 as time); +select column_get(column_create(1, -8080102 as int), 1 as time); select column_get(column_create(1, "20010203101112" as char), 1 as time); select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time); select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time); diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 19f87fae3d2..742b0955dca 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 2fe61ac5529..1bfd5bd08ab 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())); @@ -68,10 +71,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); @@ -948,6 +951,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 ae185aef0b7..4dcd51a85ba 100644 --- a/mysql-test/t/func_time_hires.test +++ b/mysql-test/t/func_time_hires.test @@ -36,7 +36,7 @@ drop table t1; select sec_to_time(3020399.99999), sec_to_time(3020399.999999), sec_to_time(3020399.9999999); select sec_to_time(-3020399.99999), sec_to_time(-3020399.999999), sec_to_time(-3020399.9999999); select 20010101000203.000000004 + interval 1 day; -select 20010101000203.00004 + interval 1 day; +select 20010101000203.4 + interval 1 day; # # precision of expressions # @@ -68,6 +68,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 43dafc371dc..4e091e14b30 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,18 +432,18 @@ 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 # --echo # Bug#59173: Failure to handle DATE(TIME) values where Year, Month or 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)); |