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;