1--disable_warnings 2DROP TABLE IF EXISTS t1; 3--enable_warnings 4SET sql_mode='NO_ENGINE_SUBSTITUTION'; 5# Set Correct timezone to match result 6SET TIME_ZONE="+03:00"; 7CREATE TABLE t1(c1 DATE NULL UNIQUE); 8SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 9INSERT INTO t1 (c1) VALUES(NOW()); 10INSERT INTO t1 (c1) VALUES(ADDTIME(NOW(),'1 01:01:01')); 11INSERT INTO t1 (c1) VALUES(NOW()) ON DUPLICATE KEY UPDATE c1=ADDTIME(NOW(),'2 02:01:01'); 12--sorted_result 13SELECT * FROM t1; 14DROP TABLE t1; 15CREATE TABLE t1(c1 YEAR NULL UNIQUE); 16INSERT INTO t1 (c1) VALUES(1999); 17INSERT INTO t1 (c1) VALUES(2000); 18INSERT INTO t1 (c1) VALUES(1999) ON DUPLICATE KEY UPDATE c1=2011; 19--sorted_result 20SELECT * FROM t1; 21DROP TABLE t1; 22CREATE TABLE t1(c1 TIME NULL UNIQUE); 23SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 24INSERT INTO t1 (c1) VALUES(NOW()); 25INSERT INTO t1 (c1) VALUES(ADDTIME(NOW(),'1 01:01:01')); 26INSERT INTO t1 (c1) VALUES(NOW()) ON DUPLICATE KEY UPDATE c1=ADDTIME(NOW(),'2 02:01:01'); 27--sorted_result 28SELECT * FROM t1; 29DROP TABLE t1; 30CREATE TABLE t1(c1 YEAR NULL UNIQUE); 31INSERT INTO t1 (c1) VALUES(1999); 32INSERT INTO t1 (c1) VALUES(2000); 33INSERT INTO t1 (c1) VALUES(1999) ON DUPLICATE KEY UPDATE c1=2011; 34--sorted_result 35SELECT * FROM t1; 36DROP TABLE t1; 37CREATE TABLE t1(c1 DATETIME NULL UNIQUE); 38SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 39INSERT INTO t1 (c1) VALUES(NOW()); 40INSERT INTO t1 (c1) VALUES(ADDTIME(NOW(),'1 01:01:01')); 41INSERT INTO t1 (c1) VALUES(NOW()) ON DUPLICATE KEY UPDATE c1=ADDTIME(NOW(),'2 02:01:01'); 42--sorted_result 43SELECT * FROM t1; 44DROP TABLE t1; 45CREATE TABLE t1(c1 YEAR NULL UNIQUE); 46INSERT INTO t1 (c1) VALUES(1999); 47INSERT INTO t1 (c1) VALUES(2000); 48INSERT INTO t1 (c1) VALUES(1999) ON DUPLICATE KEY UPDATE c1=2011; 49--sorted_result 50SELECT * FROM t1; 51DROP TABLE t1; 52CREATE TABLE t1(c1 TIMESTAMP NULL UNIQUE); 53SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 54INSERT INTO t1 (c1) VALUES(NOW()); 55INSERT INTO t1 (c1) VALUES(ADDTIME(NOW(),'1 01:01:01')); 56INSERT INTO t1 (c1) VALUES(NOW()) ON DUPLICATE KEY UPDATE c1=ADDTIME(NOW(),'2 02:01:01'); 57--sorted_result 58SELECT * FROM t1; 59DROP TABLE t1; 60CREATE TABLE t1(c1 YEAR NULL UNIQUE); 61INSERT INTO t1 (c1) VALUES(1999); 62INSERT INTO t1 (c1) VALUES(2000); 63INSERT INTO t1 (c1) VALUES(1999) ON DUPLICATE KEY UPDATE c1=2011; 64--sorted_result 65SELECT * FROM t1; 66DROP TABLE t1; 67# Restore timezone to default 68SET TIME_ZONE= @@global.time_zone; 69SET sql_mode=default; 70