diff options
Diffstat (limited to 'mysql-test/main/type_timestamp_round.result')
| -rw-r--r-- | mysql-test/main/type_timestamp_round.result | 191 |
1 files changed, 191 insertions, 0 deletions
diff --git a/mysql-test/main/type_timestamp_round.result b/mysql-test/main/type_timestamp_round.result new file mode 100644 index 00000000000..7931aa0ff5b --- /dev/null +++ b/mysql-test/main/type_timestamp_round.result @@ -0,0 +1,191 @@ +SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', ''); +SET @default_sql_mode=@@sql_mode; +# +# TIMESTAMP: SET +# +CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, b TIMESTAMP(4) NULL DEFAULT NULL); +INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999'); +UPDATE t1 SET a=b; +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, b VARCHAR(64)); +INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999'); +INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999999'); +UPDATE t1 SET a=b; +Warnings: +Note 1265 Data truncated for column 'a' at row 2 +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +2001-01-01 00:00:00.000 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, b DECIMAL(38,10)); +INSERT INTO t1 VALUES(NULL,20001231235959.9999); +INSERT INTO t1 VALUES(NULL,20001231235959.9999999); +UPDATE t1 SET a=b; +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +2001-01-01 00:00:00.000 +DROP TABLE t1; +# +# TIMESTAMP: ALTER +# +CREATE TABLE t1 (a TIMESTAMP(4) NULL DEFAULT NULL); +INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); +ALTER TABLE t1 MODIFY a TIMESTAMP(3) NULL DEFAULT NULL; +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); +INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999'); +ALTER TABLE t1 MODIFY a TIMESTAMP(3) NULL DEFAULT NULL; +Warnings: +Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +2001-01-01 00:00:00.000 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(38,10)); +INSERT INTO t1 VALUES(20001231235959.9999); +INSERT INTO t1 VALUES(20001231235959.9999999); +ALTER TABLE t1 MODIFY a TIMESTAMP(3) NULL DEFAULT NULL; +SELECT a FROM t1; +a +2001-01-01 00:00:00.000 +2001-01-01 00:00:00.000 +DROP TABLE t1; +# +# Corner case: +# ALTER TIMESTAMP to a shorter TIMESTAMP +# All values round, maximum possible value truncates. +# +SET time_zone='+00:00'; +CREATE TABLE t1 (ID INT, a TIMESTAMP(6), comment VARCHAR(64)); +INSERT INTO t1 VALUES (0, '2038-01-18 23:59:59.999999', 'Should round'); +INSERT INTO t1 VALUES (1, '2038-01-19 03:14:06.999999', 'Should round'); +INSERT INTO t1 VALUES (2, '2038-01-19 03:14:07.999999', 'Should truncate'); +ALTER TABLE t1 MODIFY a TIMESTAMP(5); +Warnings: +Warning 1264 Out of range value for column 'a' at row 3 +SELECT * FROM t1; +ID a comment +0 2038-01-19 00:00:00.00000 Should round +1 2038-01-19 03:14:07.00000 Should round +2 2038-01-19 03:14:07.99999 Should truncate +DROP TABLE t1; +SET time_zone=DEFAULT; +# +# NOW +# +SET time_zone='+00:00'; +SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999'); +CREATE OR REPLACE TABLE t1 (id SERIAL, a TIMESTAMP(4)); +INSERT INTO t1 (a) VALUES (now(6)); +INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6)); +INSERT INTO t1 (a) VALUES (CURRENT_TIME(6)); +SELECT * FROM t1; +id a +1 2011-01-01 00:00:00.0000 +2 2011-01-01 00:00:00.0000 +3 2011-01-01 00:00:00.0000 +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; +# +# DATETIME to TIMESTAMP conversion with DST change +# +SET sql_mode=IF(@@version LIKE '%MariaDB%', +'STRICT_ALL_TABLES,TIME_ROUND_FRACTIONAL', +'STRICT_ALL_TABLES'); +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */); +INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap */); +ERROR 22007: Incorrect datetime value: '2010-03-28 01:59:59.9' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1; +a +2010-03-28 01:59:59 +DROP TABLE t1; +SET time_zone=DEFAULT; +SET sql_mode=@default_sql_mode; +SET sql_mode=IF(@@version LIKE '%MariaDB%','TIME_ROUND_FRACTIONAL',''); +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */); +INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap, then gets fixed to the first second of the summer time */); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 +SELECT a, UNIX_TIMESTAMP(a) FROM t1; +a UNIX_TIMESTAMP(a) +2010-03-28 01:59:59 1269730799 +2010-03-28 03:00:00 1269730800 +DROP TABLE t1; +SET time_zone=DEFAULT; +SET sql_mode=@default_sql_mode; +# +# Comparing non-temporal to TIMESTAMP +# +CREATE TABLE t1 (a VARCHAR(64)); +INSERT t1 VALUES ('2001-01-01 23:59:59.9999999'); +CREATE TABLE t2 (a TIMESTAMP); +INSERT INTO t2 VALUES ('2001-01-02 00:00:00'); +SELECT * FROM t1,t2 WHERE t1.a=t2.a; +a a +2001-01-01 23:59:59.9999999 2001-01-02 00:00:00 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' +SELECT * FROM t1,t2 WHERE CONCAT(t1.a)=t2.a; +a a +2001-01-01 23:59:59.9999999 2001-01-02 00:00:00 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' +SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a; +a a +2001-01-01 23:59:59.9999999 2001-01-02 00:00:00 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' +DROP TABLE t1,t2; +CREATE TABLE t1 (a DECIMAL(32,7)); +INSERT t1 VALUES (20010101235959.9999999); +CREATE TABLE t2 (a TIMESTAMP); +INSERT INTO t2 VALUES ('2001-01-02 00:00:00'); +SELECT * FROM t1,t2 WHERE t1.a=t2.a; +a a +20010101235959.9999999 2001-01-02 00:00:00 +SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a; +a a +20010101235959.9999999 2001-01-02 00:00:00 +DROP TABLE t1,t2; +# +# MDEV-13995 MAX(timestamp) returns a wrong result near DST change +# +# Test Field_timestamp::store_native() +# +SET sql_mode=@default_sql_mode; +SET time_zone='+00:00'; +CREATE TABLE t1 (ts0 TIMESTAMP, ts1 TIMESTAMP(1)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', '2001-01-01 10:20:30.9'); +SELECT * FROM t1; +ts0 ts1 +2001-01-01 10:20:30 2001-01-01 10:20:30.9 +# This should round +UPDATE t1 SET ts0=COALESCE(ts1); +SELECT * FROM t1; +ts0 ts1 +2001-01-01 10:20:31 2001-01-01 10:20:30.9 +# Corner case +UPDATE t1 SET ts1=FROM_UNIXTIME(2147483647.9); +UPDATE t1 SET ts0=COALESCE(ts1); +Warnings: +Warning 1264 Out of range value for column 'ts0' at row 1 +SELECT * FROM t1; +ts0 ts1 +2038-01-19 03:14:07 2038-01-19 03:14:07.9 +DROP TABLE t1; +SET time_zone=DEFAULT; |