diff options
Diffstat (limited to 'mysql-test/main/type_datetime_hires.result')
| -rw-r--r-- | mysql-test/main/type_datetime_hires.result | 354 |
1 files changed, 354 insertions, 0 deletions
diff --git a/mysql-test/main/type_datetime_hires.result b/mysql-test/main/type_datetime_hires.result new file mode 100644 index 00000000000..38e2c2a5ac8 --- /dev/null +++ b/mysql-test/main/type_datetime_hires.result @@ -0,0 +1,354 @@ +SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30'); +drop table if exists t1, t2, t3; +create table t1 (a datetime(7)); +ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6 +create table t1 (a datetime(3), key(a)); +insert t1 values ('2010-12-11 00:20:03.1234'); +insert t1 values ('2010-12-11 15:47:11.1234'); +insert t1 values (20101211010203.45678); +insert t1 values (20101211030405.789e0); +insert ignore t1 values (99991231235959e1); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1; +a +0000-00-00 00:00:00.000 +2010-12-11 00:20:03.123 +2010-12-11 01:02:03.456 +2010-12-11 03:04:05.789 +2010-12-11 15:47:11.123 +select truncate(a, 6) from t1; +truncate(a, 6) +0.000000 +20101211002003.120000 +20101211010203.457031 +20101211030405.790000 +20101211154711.120000 +select a DIV 1 from t1; +a DIV 1 +0 +20101211002003 +20101211010203 +20101211030405 +20101211154711 +select group_concat(distinct a) from t1; +group_concat(distinct a) +0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123 +alter table t1 engine=innodb; +select * from t1 order by a; +a +0000-00-00 00:00:00.000 +2010-12-11 00:20:03.123 +2010-12-11 01:02:03.456 +2010-12-11 03:04:05.789 +2010-12-11 15:47:11.123 +select * from t1 order by a+0; +a +0000-00-00 00:00:00.000 +2010-12-11 00:20:03.123 +2010-12-11 01:02:03.456 +2010-12-11 03:04:05.789 +2010-12-11 15:47:11.123 +drop table t1; +create table t1 (a datetime(4)) engine=innodb; +insert t1 values ('2010-12-11 01:02:03.456789'); +select * from t1; +a +2010-12-11 01:02:03.4567 +select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; +extract(microsecond from a + interval 100 microsecond) +456800 +select a from t1 where a>'2010-11-12 01:02:03.456' group by a; +a +2010-12-11 01:02:03.4567 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(4) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show columns from t1; +Field Type Null Key Default Extra +a datetime(4) YES NULL +select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1'; +table_name t1 +column_name a +column_default NULL +is_nullable YES +data_type datetime +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 4 +character_set_name NULL +collation_name NULL +column_type datetime(4) +column_key +extra +select a, a+interval 9876543 microsecond from t1; +a a+interval 9876543 microsecond +2010-12-11 01:02:03.4567 2010-12-11 01:02:13.333243 +update t1 set a=a+interval 9876543 microsecond; +select * from t1; +a +2010-12-11 01:02:13.3332 +select a, a + interval 2 year from t1; +a a + interval 2 year +2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332 +insert ignore t1 select a + interval 2 year from t1; +select * from t1; +a +2010-12-11 01:02:13.3332 +2012-12-11 01:02:13.3332 +delete from t1 where a < 20110101; +select * from t1; +a +2012-12-11 01:02:13.3332 +create table t2 select * from t1; +create table t3 like t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` datetime(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t3; +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(19,4) DEFAULT NULL, + `a-1` decimal(19,4) DEFAULT NULL, + `a*1` decimal(19,4) DEFAULT NULL, + `a/2` decimal(22,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(40,4) DEFAULT NULL, + `avg(a)` decimal(22,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 double) double_f3_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 +double_f3_datetime 20101112111417.766 +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.766 +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.766 +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 2001-02-03 11:14:17 +datetime3_f1_datetime 2010-11-12 11:14:17.7 +date_f2_datetime 2010-11-12 00:00:00.00 +double_f3_datetime 2010-11-12 11:14:17.766 +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 2001-02-03 11:14:17 +datetime3_f1_datetime 2010-11-12 11:14:17.7 +date_f2_datetime 2010-11-12 00:00:00.00 +double_f3_datetime 2010-11-12 11:14:17.765 +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'); +select * from t1; +a b +2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 +create procedure bar(a int, c datetime(5)) +begin +declare b datetime(4); +set b = c + interval a microsecond; +insert t1 values (b, c + interval a microsecond); +end| +call bar(1111111, '2011-01-02 3:4:5.123456'); +select * from t1; +a b +2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 +2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 +drop procedure foo; +drop procedure bar; +create function xyz(s char(20)) returns datetime(4) +return addtime('2010-10-10 10:10:10.101010', s); +select xyz('1:1:1.010101'); +xyz('1:1:1.010101') +2010-10-10 11:11:11.1111 +drop function xyz; +create view v1 as select * from t1 group by a,b; +select * from v1; +a b +2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 +2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 +show columns from v1; +Field Type Null Key Default Extra +a datetime(6) YES NULL +b datetime(6) YES NULL +create table t2 select * from v1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` datetime(6) DEFAULT NULL, + `b` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t2; +a b +2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 +2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 +drop view v1; +drop table t1, t2; +SET timestamp=DEFAULT; +CREATE TABLE t1 ( +taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p01 VALUES LESS THAN (732920), +PARTITION p02 VALUES LESS THAN (732950), +PARTITION p03 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES +('2006-09-27 21:50:01.123456',0), +('2006-09-27 21:50:01.123456',1), +('2006-09-27 21:50:01.123456',2), +('2006-09-28 21:50:01.123456',3), +('2006-09-29 21:50:01.123456',4), +('2006-09-29 21:50:01.123456',5), +('2006-09-30 21:50:01.123456',6), +('2006-10-01 21:50:01.123456',7), +('2006-10-02 21:50:01.123456',8), +('2006-10-02 21:50:01.123456',9); +SELECT id,to_days(taken) FROM t1 order by 2; +id to_days(taken) +0 732946 +1 732946 +2 732946 +3 732947 +5 732948 +4 732948 +6 732949 +7 732950 +8 732951 +9 732951 +CREATE TABLE t2 ( +taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) +PARTITION BY RANGE (extract(microsecond from taken)) +( +PARTITION p01 VALUES LESS THAN (123000), +PARTITION p02 VALUES LESS THAN (500000), +PARTITION p03 VALUES LESS THAN MAXVALUE); +INSERT INTO t2 VALUES +('2006-09-27 21:50:01',0), +('2006-09-27 21:50:01.1',1), +('2006-09-27 21:50:01.12',2), +('2006-09-28 21:50:01.123',3), +('2006-09-29 21:50:01.1234',4), +('2006-09-29 21:50:01.12345',5), +('2006-09-30 21:50:01.123456',6), +('2006-10-01 21:50:01.56',7), +('2006-10-02 21:50:01.567',8), +('2006-10-02 21:50:01.5678',9); +select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2'); +table_name partition_name partition_method partition_expression partition_description table_rows +t1 p01 RANGE to_days(`taken`) 732920 0 +t1 p02 RANGE to_days(`taken`) 732950 7 +t1 p03 RANGE to_days(`taken`) MAXVALUE 3 +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 (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; +select * from t1; +a b +2010-01-02 03:04:05 2010-01-02 03:04:05.000000 +alter table t1 modify b datetime, modify a datetime(6); +select * from t1; +a b +2010-01-02 03:04:05.000000 2010-01-02 03:04:05 +drop table t1; +# +# MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query +# +SET @@time_zone='+00:00'; +CREATE TABLE t1 (a DATETIME(4) NOT NULL); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00'); +SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1; +UNIX_TIMESTAMP(a) +NULL +978307200.0000 +DROP TABLE t1; +SET @@time_zone=DEFAULT; |