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 NOT NULL, c2 DATE NOT NULL, c3 DATE NOT NULL, PRIMARY KEY(c1,c2,c3)); 8SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 9INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 10INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 11INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'1 01:01:01'),NOW()); 12INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),NOW(),NOW()); 13--error ER_DUP_ENTRY 14INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 15--error ER_DUP_ENTRY 16INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 17SELECT * FROM t1; 18DROP TABLE t1; 19CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 YEAR NOT NULL, PRIMARY KEY(c1,c2,c3)); 20INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 21INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 22INSERT INTO t1 (c1,c2,c3) VALUES(1999,2000,1999); 23INSERT INTO t1 (c1,c2,c3) VALUES(2000,1999,1999); 24--error ER_DUP_ENTRY 25INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 26--error ER_DUP_ENTRY 27INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 28SELECT * FROM t1; 29DROP TABLE t1; 30CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NOT NULL, c3 TIME NOT NULL, PRIMARY KEY(c1,c2,c3)); 31SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 32INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 33INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 34INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'1 01:01:01'),NOW()); 35INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),NOW(),NOW()); 36--error ER_DUP_ENTRY 37INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 38--error ER_DUP_ENTRY 39INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 40SELECT * FROM t1; 41DROP TABLE t1; 42CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 YEAR NOT NULL, PRIMARY KEY(c1,c2,c3)); 43INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 44INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 45INSERT INTO t1 (c1,c2,c3) VALUES(1999,2000,1999); 46INSERT INTO t1 (c1,c2,c3) VALUES(2000,1999,1999); 47--error ER_DUP_ENTRY 48INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 49--error ER_DUP_ENTRY 50INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 51SELECT * FROM t1; 52DROP TABLE t1; 53CREATE TABLE t1(c1 DATETIME NOT NULL, c2 DATETIME NOT NULL, c3 DATETIME NOT NULL, PRIMARY KEY(c1,c2,c3)); 54SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 55INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 56INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 57INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'1 01:01:01'),NOW()); 58INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),NOW(),NOW()); 59--error ER_DUP_ENTRY 60INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 61--error ER_DUP_ENTRY 62INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 63SELECT * FROM t1; 64DROP TABLE t1; 65CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 YEAR NOT NULL, PRIMARY KEY(c1,c2,c3)); 66INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 67INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 68INSERT INTO t1 (c1,c2,c3) VALUES(1999,2000,1999); 69INSERT INTO t1 (c1,c2,c3) VALUES(2000,1999,1999); 70--error ER_DUP_ENTRY 71INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 72--error ER_DUP_ENTRY 73INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 74SELECT * FROM t1; 75DROP TABLE t1; 76CREATE TABLE t1(c1 TIMESTAMP NOT NULL, c2 TIMESTAMP NOT NULL, c3 TIMESTAMP NOT NULL, PRIMARY KEY(c1,c2,c3)); 77SET TIMESTAMP=1171346973; # 2007-02-13 15:09:33 78INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 79INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 80INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'1 01:01:01'),NOW()); 81INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),NOW(),NOW()); 82--error ER_DUP_ENTRY 83INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()); 84--error ER_DUP_ENTRY 85INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),ADDTIME(NOW(),'1 01:01:01')); 86SELECT * FROM t1; 87DROP TABLE t1; 88CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 YEAR NOT NULL, PRIMARY KEY(c1,c2,c3)); 89INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 90INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 91INSERT INTO t1 (c1,c2,c3) VALUES(1999,2000,1999); 92INSERT INTO t1 (c1,c2,c3) VALUES(2000,1999,1999); 93--error ER_DUP_ENTRY 94INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,1999); 95--error ER_DUP_ENTRY 96INSERT INTO t1 (c1,c2,c3) VALUES(1999,1999,2000); 97SELECT * FROM t1; 98DROP TABLE t1; 99# Restore timezone to default 100SET TIME_ZONE= @@global.time_zone; 101SET sql_mode=default; 102