diff options
author | Sergei Golubchik <sergii@pisem.net> | 2011-06-07 18:13:02 +0200 |
---|---|---|
committer | Sergei Golubchik <sergii@pisem.net> | 2011-06-07 18:13:02 +0200 |
commit | 9b98cae4cc44fa39813675b361b7aa65d129b29d (patch) | |
tree | 7f4baeea245f91006e88c62508c2859ed9390c2d /mysql-test/r/type_datetime_hires.result | |
parent | 29af1aefe914da6aca73498c077f7e37a36d7de4 (diff) | |
parent | 4d128777dde904c5f0adab9b093e854c9c580d41 (diff) | |
download | mariadb-git-9b98cae4cc44fa39813675b361b7aa65d129b29d.tar.gz |
merge with 5.1-micro
Diffstat (limited to 'mysql-test/r/type_datetime_hires.result')
-rw-r--r-- | mysql-test/r/type_datetime_hires.result | 225 |
1 files changed, 99 insertions, 126 deletions
diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result index b0ea2e871fb..d80d4365ee8 100644 --- a/mysql-test/r/type_datetime_hires.result +++ b/mysql-test/r/type_datetime_hires.result @@ -115,7 +115,106 @@ 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 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.8 +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.800 +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'); @@ -228,132 +327,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; |