1drop table if exists t1,t2; 2set time_zone="+03:00"; 3CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4CREATE TABLE t2 (a int, t datetime); 5SET TIMESTAMP=1234; 6insert into t1 values(1,NULL); 7insert into t1 values(2,"2002-03-03"); 8SET TIMESTAMP=1235; 9insert into t1 values(3,NULL); 10SET TIMESTAMP=1236; 11insert into t1 (a) values(4); 12insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00"); 13SET TIMESTAMP=1237; 14insert into t1 select * from t2; 15SET TIMESTAMP=1238; 16insert into t1 (a) select a+1 from t2 where a=8; 17select * from t1; 18a t 191 1970-01-01 03:20:34 202 2002-03-03 00:00:00 213 1970-01-01 03:20:35 224 1970-01-01 03:20:36 235 2002-03-04 00:00:00 246 1970-01-01 03:20:37 257 2002-03-05 00:00:00 268 0000-00-00 00:00:00 279 1970-01-01 03:20:38 28drop table t1,t2; 29SET TIMESTAMP=1234; 30CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)); 31INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00"); 32SELECT stamp FROM t1 WHERE id="myKey"; 33stamp 341999-04-02 00:00:00 35UPDATE t1 SET value="my value" WHERE id="myKey"; 36SELECT stamp FROM t1 WHERE id="myKey"; 37stamp 381999-04-02 00:00:00 39UPDATE t1 SET id="myKey" WHERE value="my value"; 40SELECT stamp FROM t1 WHERE id="myKey"; 41stamp 421999-04-02 00:00:00 43drop table t1; 44create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 45insert into t1 values (now()); 46select date_format(a,"%Y %y"),year(a),year(now()) from t1; 47date_format(a,"%Y %y") year(a) year(now()) 481970 70 1970 1970 49drop table t1; 50create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 51insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); 52select ix+0 from t1; 53ix+0 5419991101000000 5519990102030405 5619990630232922 5719990601000000 5819990930232922 5919990531232922 6019990501000000 6119991101000000 6219990501000000 63truncate table t1; 64insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"); 65select ix+0 from t1; 66ix+0 6719991101000000 6819990102030405 6919990630232922 7019990601000000 71drop table t1; 72CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 73INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); 74INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000); 75INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959); 76INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000); 77INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000); 78INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000); 79INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000); 80INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959); 81INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000); 82INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959); 83INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000); 84INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000); 85SELECT * FROM t1; 86date date_time time_stamp 871998-12-31 1998-12-31 23:59:59 1998-12-31 23:59:59 881999-01-01 1999-01-01 00:00:00 1999-01-01 00:00:00 891999-09-09 1999-09-09 23:59:59 1999-09-09 23:59:59 902000-01-01 2000-01-01 00:00:00 2000-01-01 00:00:00 912000-02-28 2000-02-28 00:00:00 2000-02-28 00:00:00 922000-02-29 2000-02-29 00:00:00 2000-02-29 00:00:00 932000-03-01 2000-03-01 00:00:00 2000-03-01 00:00:00 942000-12-31 2000-12-31 23:59:59 2000-12-31 23:59:59 952001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00 962004-12-31 2004-12-31 23:59:59 2004-12-31 23:59:59 972005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00 982030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00 99drop table t1; 100create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 101insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); 102Warnings: 103Warning 1265 Data truncated for column 'ix' at row 2 104Warning 1265 Data truncated for column 'ix' at row 3 105Warning 1265 Data truncated for column 'ix' at row 4 106Warning 1265 Data truncated for column 'ix' at row 5 107Warning 1265 Data truncated for column 'ix' at row 6 108Warning 1265 Data truncated for column 'ix' at row 7 109Warning 1265 Data truncated for column 'ix' at row 8 110select ix+0 from t1; 111ix+0 1120 1130 1140 1150 1160 1170 1180 1190 120truncate table t1; 121insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000"); 122Warnings: 123Warning 1265 Data truncated for column 'ix' at row 2 124Warning 1265 Data truncated for column 'ix' at row 3 125Warning 1265 Data truncated for column 'ix' at row 4 126Warning 1265 Data truncated for column 'ix' at row 5 127Warning 1265 Data truncated for column 'ix' at row 6 128Warning 1265 Data truncated for column 'ix' at row 7 129Warning 1265 Data truncated for column 'ix' at row 8 130select ix+0 from t1; 131ix+0 1320 1330 1340 1350 1360 1370 1380 1390 140truncate table t1; 141insert ignore into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); 142Warnings: 143Warning 1265 Data truncated for column 'ix' at row 1 144Warning 1265 Data truncated for column 'ix' at row 2 145select ix+0 from t1; 146ix+0 1470 14820030101000000 149drop table t1; 150create table t1 (t1 timestamp, t2 timestamp default now()); 151drop table t1; 152create table t1 (t1 timestamp, t2 timestamp on update now()); 153drop table t1; 154create table t1 (t1 timestamp, t2 timestamp default now() on update now()); 155drop table t1; 156create table t1 (t1 timestamp default now(), t2 timestamp on update now()); 157drop table t1; 158create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); 159drop table t1; 160create table t1 (t1 timestamp not null default '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); 161SET TIMESTAMP=1000000000; 162insert into t1 values (); 163SET TIMESTAMP=1000000001; 164update t1 set t2=now(); 165SET TIMESTAMP=1000000002; 166insert into t1 (t1,t3) values (default, default); 167select * from t1; 168t1 t2 t3 1692003-01-01 00:00:00 2001-09-09 04:46:41 0000-00-00 00:00:00 1702003-01-01 00:00:00 NULL 0000-00-00 00:00:00 171show create table t1; 172Table Create Table 173t1 CREATE TABLE `t1` ( 174 `t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00', 175 `t2` datetime DEFAULT NULL, 176 `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 177) ENGINE=MyISAM DEFAULT CHARSET=latin1 178show columns from t1; 179Field Type Null Key Default Extra 180t1 timestamp NO 2003-01-01 00:00:00 181t2 datetime YES NULL 182t3 timestamp NO 0000-00-00 00:00:00 183drop table t1; 184create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); 185SET TIMESTAMP=1000000002; 186insert into t1 values (); 187SET TIMESTAMP=1000000003; 188update t1 set t2=now(); 189SET TIMESTAMP=1000000003; 190insert into t1 (t1,t3) values (default, default); 191select * from t1; 192t1 t2 t3 1932001-09-09 04:46:42 2001-09-09 04:46:43 0000-00-00 00:00:00 1942001-09-09 04:46:43 NULL 0000-00-00 00:00:00 195show create table t1; 196Table Create Table 197t1 CREATE TABLE `t1` ( 198 `t1` timestamp NOT NULL DEFAULT current_timestamp(), 199 `t2` datetime DEFAULT NULL, 200 `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 201) ENGINE=MyISAM DEFAULT CHARSET=latin1 202show columns from t1; 203Field Type Null Key Default Extra 204t1 timestamp NO current_timestamp() 205t2 datetime YES NULL 206t3 timestamp NO 0000-00-00 00:00:00 207drop table t1; 208create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime); 209SET TIMESTAMP=1000000004; 210insert into t1 values (); 211select * from t1; 212t1 t2 2132003-01-01 00:00:00 NULL 214SET TIMESTAMP=1000000005; 215update t1 set t2=now(); 216SET TIMESTAMP=1000000005; 217insert into t1 (t1) values (default); 218select * from t1; 219t1 t2 2202001-09-09 04:46:45 2001-09-09 04:46:45 2212003-01-01 00:00:00 NULL 222show create table t1; 223Table Create Table 224t1 CREATE TABLE `t1` ( 225 `t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' ON UPDATE current_timestamp(), 226 `t2` datetime DEFAULT NULL 227) ENGINE=MyISAM DEFAULT CHARSET=latin1 228show columns from t1; 229Field Type Null Key Default Extra 230t1 timestamp NO 2003-01-01 00:00:00 on update current_timestamp() 231t2 datetime YES NULL 232drop table t1; 233create table t1 (t1 timestamp not null default now() on update now(), t2 datetime); 234SET TIMESTAMP=1000000006; 235insert into t1 values (); 236select * from t1; 237t1 t2 2382001-09-09 04:46:46 NULL 239SET TIMESTAMP=1000000007; 240update t1 set t2=now(); 241SET TIMESTAMP=1000000007; 242insert into t1 (t1) values (default); 243select * from t1; 244t1 t2 2452001-09-09 04:46:47 2001-09-09 04:46:47 2462001-09-09 04:46:47 NULL 247show create table t1; 248Table Create Table 249t1 CREATE TABLE `t1` ( 250 `t1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 251 `t2` datetime DEFAULT NULL 252) ENGINE=MyISAM DEFAULT CHARSET=latin1 253show columns from t1; 254Field Type Null Key Default Extra 255t1 timestamp NO current_timestamp() on update current_timestamp() 256t2 datetime YES NULL 257drop table t1; 258create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); 259SET TIMESTAMP=1000000007; 260insert into t1 values (); 261select * from t1; 262t1 t2 t3 2632001-09-09 04:46:47 NULL 0000-00-00 00:00:00 264SET TIMESTAMP=1000000008; 265update t1 set t2=now(); 266SET TIMESTAMP=1000000008; 267insert into t1 (t1,t3) values (default, default); 268select * from t1; 269t1 t2 t3 2702001-09-09 04:46:48 2001-09-09 04:46:48 0000-00-00 00:00:00 2712001-09-09 04:46:48 NULL 0000-00-00 00:00:00 272show create table t1; 273Table Create Table 274t1 CREATE TABLE `t1` ( 275 `t1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 276 `t2` datetime DEFAULT NULL, 277 `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 278) ENGINE=MyISAM DEFAULT CHARSET=latin1 279show columns from t1; 280Field Type Null Key Default Extra 281t1 timestamp NO current_timestamp() on update current_timestamp() 282t2 datetime YES NULL 283t3 timestamp NO 0000-00-00 00:00:00 284drop table t1; 285create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime); 286SET TIMESTAMP=1000000009; 287insert into t1 values (); 288select * from t1; 289t1 t2 2902001-09-09 04:46:49 NULL 291SET TIMESTAMP=1000000010; 292update t1 set t2=now(); 293SET TIMESTAMP=1000000011; 294insert into t1 (t1) values (default); 295select * from t1; 296t1 t2 2972001-09-09 04:46:50 2001-09-09 04:46:50 2982001-09-09 04:46:51 NULL 299show create table t1; 300Table Create Table 301t1 CREATE TABLE `t1` ( 302 `t1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 303 `t2` datetime DEFAULT NULL 304) ENGINE=MyISAM DEFAULT CHARSET=latin1 305show columns from t1; 306Field Type Null Key Default Extra 307t1 timestamp NO current_timestamp() on update current_timestamp() 308t2 datetime YES NULL 309truncate table t1; 310insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); 311SET TIMESTAMP=1000000012; 312update t1 set t1= '2004-04-02 00:00:00'; 313select * from t1; 314t1 t2 3152004-04-02 00:00:00 2004-04-01 00:00:00 316update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00'; 317select * from t1; 318t1 t2 3192004-04-03 00:00:00 2004-04-01 00:00:00 320drop table t1; 321create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); 322insert into t1 values (1, '2004-04-01 00:00:00', 10); 323SET TIMESTAMP=1000000013; 324replace into t1 set pk = 1, bulk= 20; 325select * from t1; 326pk t1 bulk 3271 2001-09-09 04:46:53 20 328drop table t1; 329create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int); 330insert into t1 values (1, '2004-04-01 00:00:00', 10); 331SET TIMESTAMP=1000000014; 332replace into t1 set pk = 1, bulk= 20; 333select * from t1; 334pk t1 bulk 3351 2003-01-01 00:00:00 20 336drop table t1; 337create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); 338insert into t1 values (1, '2004-04-01 00:00:00', 10); 339SET TIMESTAMP=1000000015; 340replace into t1 set pk = 1, bulk= 20; 341select * from t1; 342pk t1 bulk 3431 2001-09-09 04:46:55 20 344drop table t1; 345create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp); 346insert into t1 values ('2004-04-01 00:00:00'); 347SET TIMESTAMP=1000000016; 348alter table t1 add i int default 10; 349select * from t1; 350t1 i 3512004-04-01 00:00:00 10 352drop table t1; 353create table t1 (a timestamp null, b timestamp null); 354show create table t1; 355Table Create Table 356t1 CREATE TABLE `t1` ( 357 `a` timestamp NULL DEFAULT NULL, 358 `b` timestamp NULL DEFAULT NULL 359) ENGINE=MyISAM DEFAULT CHARSET=latin1 360insert into t1 values (NULL, NULL); 361SET TIMESTAMP=1000000017; 362insert into t1 values (); 363select * from t1; 364a b 365NULL NULL 366NULL NULL 367drop table t1; 368create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null); 369show create table t1; 370Table Create Table 371t1 CREATE TABLE `t1` ( 372 `a` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 373 `b` timestamp NULL DEFAULT NULL 374) ENGINE=MyISAM DEFAULT CHARSET=latin1 375insert into t1 values (NULL, NULL); 376SET TIMESTAMP=1000000018; 377insert into t1 values (); 378select * from t1; 379a b 380NULL NULL 3812001-09-09 04:46:58 NULL 382drop table t1; 383create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00'); 384show create table t1; 385Table Create Table 386t1 CREATE TABLE `t1` ( 387 `a` timestamp NULL DEFAULT NULL, 388 `b` timestamp NULL DEFAULT '2003-01-01 00:00:00' 389) ENGINE=MyISAM DEFAULT CHARSET=latin1 390insert into t1 values (NULL, NULL); 391insert into t1 values (DEFAULT, DEFAULT); 392select * from t1; 393a b 394NULL NULL 395NULL 2003-01-01 00:00:00 396drop table t1; 397create table t1 (a bigint, b bigint); 398insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); 399set timestamp=1000000019; 400alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT '0000-00-00 00:00:0'; 401select * from t1; 402a b 4032001-09-09 04:46:59 2001-09-09 04:46:59 4042003-01-01 00:00:00 2003-01-02 00:00:00 405drop table t1; 406create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 407insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), 408('b', '2004-02-01 00:00:00'); 409select max(t) from t1 group by a; 410max(t) 4112004-01-01 01:00:00 4122004-02-01 00:00:00 413drop table t1; 414set sql_mode='maxdb'; 415create table t1 (a timestamp, b timestamp(5)); 416show create table t1; 417Table Create Table 418t1 CREATE TABLE "t1" ( 419 "a" datetime DEFAULT NULL, 420 "b" datetime(5) DEFAULT NULL 421) 422set sql_mode=''; 423drop table t1; 424create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 425insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), 426(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); 427select * from t1; 428a b c 4291 0 2001-01-01 01:01:01 4302 0 2002-02-02 02:02:02 4313 0 2003-03-03 03:03:03 432update t1 set b = 2, c = c where a = 2; 433select * from t1; 434a b c 4351 0 2001-01-01 01:01:01 4362 2 2002-02-02 02:02:02 4373 0 2003-03-03 03:03:03 438insert into t1 (a) values (4); 439select * from t1; 440a b c 4411 0 2001-01-01 01:01:01 4422 2 2002-02-02 02:02:02 4433 0 2003-03-03 03:03:03 4444 NULL 2001-09-09 04:46:59 445update t1 set c = '2004-04-04 04:04:04' where a = 4; 446select * from t1; 447a b c 4481 0 2001-01-01 01:01:01 4492 2 2002-02-02 02:02:02 4503 0 2003-03-03 03:03:03 4514 NULL 2004-04-04 04:04:04 452insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c; 453select * from t1; 454a b c 4551 0 2001-01-01 01:01:01 4562 2 2002-02-02 02:02:02 4573 3 2003-03-03 03:03:03 4584 NULL 2004-04-04 04:04:04 4595 NULL 2001-09-09 04:46:59 460insert into t1 (a, c) values (4, '2004-04-04 00:00:00'), 461(6, '2006-06-06 06:06:06') on duplicate key update b = 4; 462select * from t1; 463a b c 4641 0 2001-01-01 01:01:01 4652 2 2002-02-02 02:02:02 4663 3 2003-03-03 03:03:03 4674 4 2001-09-09 04:46:59 4685 NULL 2001-09-09 04:46:59 4696 NULL 2006-06-06 06:06:06 470drop table t1; 471End of 4.1 tests 472set time_zone= @@global.time_zone; 473CREATE TABLE t1 ( 474`id` int(11) NOT NULL auto_increment, 475`username` varchar(80) NOT NULL default '', 476`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00', 477PRIMARY KEY (`id`) 478) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; 479show fields from t1; 480Field Type Null Key Default Extra 481id int(11) NO PRI NULL auto_increment 482username varchar(80) NO 483posted_on timestamp NO 0000-00-00 00:00:00 484select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on'; 485is_nullable 486NO 487drop table t1; 488CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 489f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 490f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00'); 491INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); 492INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); 493INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); 494INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999')); 495Warnings: 496Warning 1292 Truncated incorrect unixtime value: '9999999999' 497INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL)); 498UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1; 499Warnings: 500Warning 1292 Truncated incorrect unixtime value: '9999999999' 501SELECT f1,f2-f3 FROM t1; 502f1 f2-f3 5031 0 5042 0 5053 0 5064 0 5075 0 508DROP TABLE t1; 509End of 5.0 tests 510# 511# Bug #55779: select does not work properly in mysql server 512# Version "5.1.42 SUSE MySQL RPM" 513# 514CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a)); 515INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'), 516('2000-01-01 00:00:01'), ('2000-01-01 00:00:01'); 517SELECT a FROM t1 WHERE a >= 20000101000000; 518a 5192000-01-01 00:00:00 5202000-01-01 00:00:00 5212000-01-01 00:00:01 5222000-01-01 00:00:01 523SELECT a FROM t1 WHERE a >= '20000101000000'; 524a 5252000-01-01 00:00:00 5262000-01-01 00:00:00 5272000-01-01 00:00:01 5282000-01-01 00:00:01 529DROP TABLE t1; 530# 531# Bug#50774: failed to get the correct resultset when timestamp values 532# are appended with .0 533# 534CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) ); 535INSERT INTO t1 VALUES( '2010-02-01 09:30:01' ); 536INSERT INTO t1 VALUES( '2010-02-01 09:30:02' ); 537INSERT INTO t1 VALUES( '2010-02-01 09:30:03' ); 538INSERT INTO t1 VALUES( '2010-02-01 09:30:04' ); 539INSERT INTO t1 VALUES( '2010-02-01 09:31:01' ); 540INSERT INTO t1 VALUES( '2010-02-01 09:31:02' ); 541INSERT INTO t1 VALUES( '2010-02-01 09:31:03' ); 542INSERT INTO t1 VALUES( '2010-02-01 09:31:04' ); 543SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0'; 544a 5452010-02-01 09:31:02 5462010-02-01 09:31:03 5472010-02-01 09:31:04 548SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a; 549a 5502010-02-01 09:31:02 5512010-02-01 09:31:03 5522010-02-01 09:31:04 553SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0'; 554a 5552010-02-01 09:30:01 5562010-02-01 09:30:02 5572010-02-01 09:30:03 5582010-02-01 09:30:04 5592010-02-01 09:31:01 5602010-02-01 09:31:02 561SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a; 562a 5632010-02-01 09:30:01 5642010-02-01 09:30:02 5652010-02-01 09:30:03 5662010-02-01 09:30:04 5672010-02-01 09:31:01 5682010-02-01 09:31:02 569EXPLAIN 570SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0'; 571id select_type table type possible_keys key key_len ref rows Extra 572x x x range x x x x x x 573SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0'; 574a 5752010-02-01 09:31:02 5762010-02-01 09:31:03 5772010-02-01 09:31:04 578CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) ); 579INSERT INTO t2 VALUES( '2010-02-01 09:31:01' ); 580INSERT INTO t2 VALUES( '2010-02-01 09:31:02' ); 581INSERT INTO t2 VALUES( '2010-02-01 09:31:03' ); 582INSERT INTO t2 VALUES( '2010-02-01 09:31:04' ); 583INSERT INTO t2 VALUES( '2010-02-01 09:31:05' ); 584INSERT INTO t2 VALUES( '2010-02-01 09:31:06' ); 585INSERT INTO t2 VALUES( '2010-02-01 09:31:07' ); 586INSERT INTO t2 VALUES( '2010-02-01 09:31:08' ); 587INSERT INTO t2 VALUES( '2010-02-01 09:31:09' ); 588INSERT INTO t2 VALUES( '2010-02-01 09:31:10' ); 589INSERT INTO t2 VALUES( '2010-02-01 09:31:11' ); 590# The bug would cause the range optimizer's comparison to use an open 591# interval here. This reveals itself only in the number of reads 592# performed. 593FLUSH STATUS; 594EXPLAIN 595SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0'; 596id select_type table type possible_keys key key_len ref rows Extra 597x x x range x x x x x x 598SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0'; 599a 6002010-02-01 09:31:01 601SHOW STATUS LIKE 'Handler_read_next'; 602Variable_name Value 603Handler_read_next 1 604DROP TABLE t1, t2; 605End of 5.1 tests 606# 607# lp:923429 Crash in decimal_cmp on using UNIX_TIMESTAMP with a wrongly formatted timestamp 608# 609SELECT UNIX_TIMESTAMP('abc') > 0; 610UNIX_TIMESTAMP('abc') > 0 611NULL 612Warnings: 613Warning 1292 Incorrect datetime value: 'abc' 614SELECT UNIX_TIMESTAMP('abc'); 615UNIX_TIMESTAMP('abc') 616NULL 617Warnings: 618Warning 1292 Incorrect datetime value: 'abc' 619 620Bug#50888 valgrind warnings in Field_timestamp::val_str 621 622SET TIMESTAMP=0; 623CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 624INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02'); 625FLUSH TABLES t1; 626SELECT MAX(a) FROM t1; 627MAX(a) 6282010-03-05 11:08:02 629SELECT a FROM t1; 630a 6312008-02-23 09:23:45 6322010-03-05 11:08:02 633DROP TABLE t1; 634End of Bug#50888 635# 636# Bug59330: Incorrect result when comparing an aggregate 637# function with TIMESTAMP 638# 639CREATE TABLE t1 (dt DATETIME, ts TIMESTAMP); 640INSERT INTO t1 VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30'); 641SELECT MAX(dt), MAX(ts) FROM t1; 642MAX(dt) MAX(ts) 6432011-01-06 12:34:30 2011-01-06 12:34:30 644SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1; 645MAX(ts) < '2010-01-01 00:00:00' 6460 647SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1; 648MAX(dt) < '2010-01-01 00:00:00' 6490 650SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1; 651MAX(ts) > '2010-01-01 00:00:00' 6521 653SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1; 654MAX(dt) > '2010-01-01 00:00:00' 6551 656SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1; 657MAX(ts) = '2011-01-06 12:34:30' 6581 659SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1; 660MAX(dt) = '2011-01-06 12:34:30' 6611 662DROP TABLE t1; 663# 664# MDEV-9413 "datetime >= coalesce(c1(NULL))" doesn't return expected NULL 665# 666CREATE TABLE t1(c1 TIMESTAMP(6) NULL DEFAULT NULL); 667INSERT INTO t1 VALUES(NULL); 668SELECT c1, '2016-06-13 20:00:00.000003' >= COALESCE( c1 ) FROM t1; 669c1 '2016-06-13 20:00:00.000003' >= COALESCE( c1 ) 670NULL NULL 671DROP TABLE t1; 672End of 5.5 tests 673# 674# MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL 675# 676SET time_zone='+02:00'; 677create table t1(value timestamp not null); 678set @a:=0; 679create function f1 () returns timestamp 680begin 681set @a = @a + 1; 682return NULL; 683end// 684set timestamp=12340; 685insert t1 values (f1()); 686select @a, value from t1; 687@a value 6881 1970-01-01 05:25:40 689set timestamp=12350; 690update t1 set value = f1(); 691select @a, value from t1; 692@a value 6932 1970-01-01 05:25:50 694drop table t1; 695drop function f1; 696set timestamp=0; 697create table t1(value timestamp null); 698set @a:=0; 699create function f1 () returns timestamp 700begin 701set @a = @a + 1; 702return NULL; 703end// 704set timestamp=12340; 705insert t1 values (f1()); 706select @a, value from t1; 707@a value 7081 NULL 709set timestamp=12350; 710update t1 set value = f1(); 711select @a, value from t1; 712@a value 7132 NULL 714drop table t1; 715drop function f1; 716set timestamp=0; 717SET time_zone=DEFAULT; 718# 719# MDEV-7778 impossible create copy of table, if table contain default value for timestamp field 720# 721SET sql_mode="NO_ZERO_DATE"; 722CREATE TABLE t1 ( 723ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 724); 725CREATE TABLE t2 AS SELECT * from t1 LIMIT 0; 726SHOW CREATE TABLE t1; 727Table Create Table 728t1 CREATE TABLE `t1` ( 729 `ts` timestamp NOT NULL DEFAULT current_timestamp() 730) ENGINE=MyISAM DEFAULT CHARSET=latin1 731SHOW CREATE TABLE t2; 732Table Create Table 733t2 CREATE TABLE `t2` ( 734 `ts` timestamp NOT NULL DEFAULT current_timestamp() 735) ENGINE=MyISAM DEFAULT CHARSET=latin1 736DROP TABLE t1,t2; 737SET sql_mode=DEFAULT; 738# 739# MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT 740# 741CREATE TABLE t1 ( 742vc VARCHAR(10) NOT NULL DEFAULT 'test', 743ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP 744); 745CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0; 746SHOW CREATE TABLE t1; 747Table Create Table 748t1 CREATE TABLE `t1` ( 749 `vc` varchar(10) NOT NULL DEFAULT 'test', 750 `ts` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp() 751) ENGINE=MyISAM DEFAULT CHARSET=latin1 752SHOW CREATE TABLE t2; 753Table Create Table 754t2 CREATE TABLE `t2` ( 755 `vc` varchar(10) NOT NULL DEFAULT 'test', 756 `ts` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp() 757) ENGINE=MyISAM DEFAULT CHARSET=latin1 758DROP TABLE t1,t2; 759End of 10.0 tests 760# 761# Start of 10.1 tests 762# 763# 764# MDEV-7831 Bad warning for DATE_ADD(timestamp_column, INTERVAL 10 SECOND) 765# 766CREATE TABLE t1 (a TIMESTAMP); 767INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); 768SELECT DATE_ADD(a, INTERVAL 10 SECOND) FROM t1; 769DATE_ADD(a, INTERVAL 10 SECOND) 770NULL 771Warnings: 772Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 773DROP TABLE t1; 774# 775# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value 776# 777SET sql_mode=DEFAULT; 778CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00'); 779SET sql_mode=TRADITIONAL; 780INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); 781ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1 782INSERT INTO t1 VALUES (); 783ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a' 784INSERT INTO t1 VALUES (DEFAULT); 785ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a' 786DROP TABLE t1; 787SET sql_mode=DEFAULT; 788CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); 789CREATE TABLE t2 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); 790INSERT INTO t2 VALUES ('0000-00-00 00:00:00'); 791SET sql_mode=TRADITIONAL; 792INSERT INTO t1 (a) SELECT a FROM t2; 793ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b' 794DROP TABLE t1, t2; 795SET sql_mode=DEFAULT; 796CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00', b TIMESTAMP DEFAULT '0000-00-00 00:00:00'); 797INSERT INTO t1 VALUES (DEFAULT,DEFAULT);; 798SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1; 799DELETE FROM t1; 800SET sql_mode=TRADITIONAL; 801LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a); 802ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b' 803DROP TABLE t1; 804SET sql_mode=DEFAULT; 805CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');; 806SET sql_mode='NO_ZERO_DATE'; 807ALTER TABLE t1 ADD b INT NOT NULL; 808ERROR 42000: Invalid default value for 'a' 809DROP TABLE t1; 810SET sql_mode=DEFAULT; 811# 812# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value 813# 814# 815# MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field 816# 817SET sql_mode=DEFAULT; 818CREATE TABLE t1 (a TIMESTAMP);; 819INSERT INTO t1 VALUES (0); 820SET sql_mode='TRADITIONAL'; 821CREATE TABLE t2 AS SELECT * FROM t1; 822ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t2`.`a` at row 1 823DROP TABLE t1; 824# 825# End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field 826# 827# 828# MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' 829# 830CREATE TABLE t1 (a TIMESTAMP);; 831INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); 832SELECT * FROM t1 WHERE a='2001-01-01 00:00:00x'; 833a 8342001-01-01 00:00:00 835Warnings: 836Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' 837SELECT * FROM t1 WHERE LENGTH(a) != 20; 838a 8392001-01-01 00:00:00 8402001-01-01 00:00:01 841SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; 842a 8432001-01-01 00:00:00 844Warnings: 845Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' 846EXPLAIN EXTENDED 847SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; 848id select_type table type possible_keys key key_len ref rows filtered Extra 8491 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 850Warnings: 851Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' 852Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' 853EXPLAIN EXTENDED 854SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x'; 855id select_type table type possible_keys key key_len ref rows filtered Extra 8561 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 857Warnings: 858Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' 859Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) <> 30 + rand() 860DROP TABLE t1; 861CREATE TABLE t1 (a TIMESTAMP);; 862INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); 863SELECT * FROM t1 WHERE LENGTH(a)=19; 864a 8652001-01-01 00:00:00 8662001-01-01 00:00:01 867SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; 868a 8692001-01-01 00:00:00 870EXPLAIN EXTENDED 871SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; 872id select_type table type possible_keys key key_len ref rows filtered Extra 8731 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 874Warnings: 875Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' 876EXPLAIN EXTENDED 877SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; 878id select_type table type possible_keys key key_len ref rows filtered Extra 8791 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 880Warnings: 881Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 19 + rand() 882EXPLAIN EXTENDED 883SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; 884id select_type table type possible_keys key key_len ref rows filtered Extra 8851 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 886Warnings: 887Warning 1292 Incorrect datetime value: ' garbage ' 888Warning 1292 Incorrect datetime value: ' garbage ' 889Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where octet_length(`test`.`t1`.`a`) = 30 + rand() and `test`.`t1`.`a` = ' garbage ' 890DROP TABLE t1; 891CREATE TABLE t1 (a TIMESTAMP);; 892INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); 893SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000'; 894a 8952001-01-01 00:00:00 896SELECT * FROM t1 WHERE LENGTH(a)=19; 897a 8982001-01-01 00:00:00 8992001-01-01 00:00:01 900SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 901a 9022001-01-01 00:00:00 903EXPLAIN EXTENDED 904SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 905id select_type table type possible_keys key key_len ref rows filtered Extra 9061 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 907Warnings: 908Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' 909EXPLAIN EXTENDED 910SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 911id select_type table type possible_keys key key_len ref rows filtered Extra 9121 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 913Warnings: 914Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 30 + rand() 915DROP TABLE t1; 916CREATE TABLE t1 (a TIMESTAMP(6));; 917INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000'); 918SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000'; 919a 9202001-01-01 00:00:00.000000 921SELECT * FROM t1 WHERE LENGTH(a)=26; 922a 9232001-01-01 00:00:00.000000 9242001-01-01 00:00:01.000000 925SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 926a 9272001-01-01 00:00:00.000000 928EXPLAIN EXTENDED 929SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 930id select_type table type possible_keys key key_len ref rows filtered Extra 9311 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 932Warnings: 933Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' 934EXPLAIN EXTENDED 935SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000'; 936id select_type table type possible_keys key key_len ref rows filtered Extra 9371 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 938Warnings: 939Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00.000000')) = 40 + rand() 940DROP TABLE t1; 941SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 942CREATE TABLE t1 (a TIMESTAMP);; 943INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); 944SELECT * FROM t1 WHERE a=TIME'00:00:00'; 945a 9462001-01-01 00:00:00 947SELECT * FROM t1 WHERE LENGTH(a)=19; 948a 9492001-01-01 00:00:00 9502001-01-01 00:00:01 951SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; 952a 9532001-01-01 00:00:00 954EXPLAIN EXTENDED 955SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; 956id select_type table type possible_keys key key_len ref rows filtered Extra 9571 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 958Warnings: 959Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' 960EXPLAIN EXTENDED 961SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; 962id select_type table type possible_keys key key_len ref rows filtered Extra 9631 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 964Warnings: 965Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 40 + rand() 966DROP TABLE t1; 967# 968# End of 10.1 tests 969# 970# 971# Start of 10.3 tests 972# 973# 974# MDEV-11333 MDEV-11333 Expect "Impossible where condition" for WHERE timestamp_field>=DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR) 975# 976SELECT DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR); 977DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR) 978NULL 979Warnings: 980Warning 1441 Datetime function: datetime field overflow 981CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY(a)) ENGINE=MyISAM; 982INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 983INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 984INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 985INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 986INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 987INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03'); 988EXPLAIN SELECT * FROM t1 WHERE a >= DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR); 989id select_type table type possible_keys key key_len ref rows Extra 9901 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 991Warnings: 992Warning 1441 Datetime function: datetime field overflow 993EXPLAIN SELECT * FROM t1 WHERE a >= COALESCE(DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR)); 994id select_type table type possible_keys key key_len ref rows Extra 9951 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 996Warnings: 997Warning 1441 Datetime function: datetime field overflow 998DROP TABLE t1; 999# 1000# MDEV-11482 Incorrect result for (time_expr BETWEEN timestamp_exp1 AND timestamp_expr2) 1001# 1002SET @@sql_mode=DEFAULT; 1003SET @@timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 1004CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP); 1005INSERT INTO t1 VALUES ('2001-01-01 00:00:00','2001-01-01 23:59:59'); 1006SELECT * FROM t1 WHERE TIME'10:20:30' BETWEEN a and b; 1007a b 10082001-01-01 00:00:00 2001-01-01 23:59:59 1009DROP TABLE t1; 1010SET @@timestamp=DEFAULT; 1011# 1012# MDEV-12582 Wrong data type for CREATE..SELECT MAX(COALESCE(timestamp_column)) 1013# 1014CREATE TABLE t1 (a TIMESTAMP); 1015CREATE TABLE t2 AS SELECT 1016MAX(a), 1017COALESCE(a), 1018COALESCE(MAX(a)), 1019MAX(COALESCE(a)) 1020FROM t1; 1021SHOW CREATE TABLE t2; 1022Table Create Table 1023t2 CREATE TABLE `t2` ( 1024 `MAX(a)` timestamp NULL DEFAULT NULL, 1025 `COALESCE(a)` timestamp NULL DEFAULT NULL, 1026 `COALESCE(MAX(a))` timestamp NULL DEFAULT NULL, 1027 `MAX(COALESCE(a))` timestamp NULL DEFAULT NULL 1028) ENGINE=MyISAM DEFAULT CHARSET=latin1 1029DROP TABLE t2; 1030DROP TABLE t1; 1031# 1032# End of 10.3 tests 1033# 1034# 1035# Start of 10.4 tests 1036# 1037# 1038# MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning 1039# 1040CREATE TABLE t1 (b BIT(20)); 1041CREATE TABLE t2 (t TIMESTAMP); 1042INSERT IGNORE INTO t1 VALUES (b'000001001100000'); 1043INSERT INTO t2 SELECT * FROM t1; 1044DROP TABLE t1, t2; 1045CREATE TABLE t1 (a TIMESTAMP); 1046INSERT INTO t1 SELECT CAST(20010101 AS UNSIGNED); 1047DROP TABLE t1; 1048# 1049# MDEV-17928 Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits 1050# 1051SET time_zone='+00:00'; 1052SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456'); 1053CREATE PROCEDURE p1() 1054BEGIN 1055DECLARE ts10 TIMESTAMP(1) DEFAULT NOW(); 1056DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6); 1057DECLARE dt10 DATETIME(1) DEFAULT NOW(); 1058DECLARE dt16 DATETIME(1) DEFAULT NOW(6); 1059DECLARE vts10 VARCHAR(32) DEFAULT ts10; 1060DECLARE vts16 VARCHAR(32) DEFAULT ts16; 1061DECLARE vdt10 VARCHAR(32) DEFAULT dt10; 1062DECLARE vdt16 VARCHAR(32) DEFAULT dt16; 1063DECLARE tts10 TEXT(32) DEFAULT ts10; 1064DECLARE tts16 TEXT(32) DEFAULT ts16; 1065DECLARE tdt10 TEXT(32) DEFAULT dt10; 1066DECLARE tdt16 TEXT(32) DEFAULT dt16; 1067SELECT vts10, vts16, vdt10, vdt16; 1068SELECT tts10, tts16, tdt10, tdt16; 1069END; 1070$$ 1071CALL p1; 1072vts10 2001-01-01 10:20:30.0 1073vts16 2001-01-01 10:20:30.1 1074vdt10 2001-01-01 10:20:30.0 1075vdt16 2001-01-01 10:20:30.1 1076tts10 2001-01-01 10:20:30.0 1077tts16 2001-01-01 10:20:30.1 1078tdt10 2001-01-01 10:20:30.0 1079tdt16 2001-01-01 10:20:30.1 1080DROP PROCEDURE p1; 1081SET timestamp=DEFAULT; 1082SET time_zone=DEFAULT; 1083# 1084# MDEV-13995 MAX(timestamp) returns a wrong result near DST change 1085# 1086# Testing Item_func_rollup_const::val_native() 1087# There is a bug in the below output (MDEV-16612) 1088# Please remove this comment when MDEV-16612 is fixed and results are re-recorded 1089CREATE TABLE t1 (id INT); 1090INSERT INTO t1 VALUES (1),(2); 1091BEGIN NOT ATOMIC 1092DECLARE v TIMESTAMP DEFAULT '2001-01-01 10:20:30'; -- "v" will be wrapped into Item_func_rollup_const 1093SELECT id, v AS v, COUNT(*) FROM t1 GROUP BY id,v WITH ROLLUP; 1094END; 1095$$ 1096id v COUNT(*) 10971 2001-01-01 10:20:30 1 10981 2001-01-01 10:20:30 1 10992 2001-01-01 10:20:30 1 11002 2001-01-01 10:20:30 1 1101NULL 2001-01-01 10:20:30 2 1102DROP TABLE t1; 1103# 1104# Testing Type_handler_timestamp_common::Item_save_in_field() 1105# "txt" is expected to have three fractional digits 1106SET time_zone='+00:00'; 1107SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456'); 1108CREATE TABLE t1 (ts1 TIMESTAMP(1) NOT NULL, ts2 TIMESTAMP(3) NOT NULL, txt TEXT); 1109INSERT INTO t1 VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00',COALESCE(ts1,ts2)); 1110INSERT INTO t1 VALUES (NOW(),NOW(),COALESCE(ts1,ts2)); 1111INSERT INTO t1 VALUES (NOW(1),NOW(3),COALESCE(ts1,ts2)); 1112SELECT * FROM t1; 1113ts1 ts2 txt 11140000-00-00 00:00:00.0 0000-00-00 00:00:00.000 0000-00-00 00:00:00.000 11152001-01-01 10:20:30.0 2001-01-01 10:20:30.000 2001-01-01 10:20:30.000 11162001-01-01 10:20:30.1 2001-01-01 10:20:30.123 2001-01-01 10:20:30.100 1117DROP TABLE t1; 1118SET timestamp=DEFAULT; 1119SET time_zone=DEFAULT; 1120# 1121# Testing Field_timestamp::store_native 1122# 1123SET sql_mode=''; 1124CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP); 1125INSERT INTO t1 VALUES ('0000-00-00 00:00:00','0000-00-00 00:00:00'); 1126SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; 1127UPDATE t1 SET a=b; 1128ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1 1129UPDATE t1 SET a=COALESCE(b); 1130ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1 1131DROP TABLE t1; 1132SET sql_mode=DEFAULT; 1133# 1134# MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY 1135# 1136CREATE TABLE t1 (a INT, b TIMESTAMP) ENGINE=MyISAM; 1137INSERT INTO t1 VALUES (1,'2018-06-19 00:00:00'); 1138SELECT NULLIF(b, 'N/A') AS f, MAX(a) FROM t1 GROUP BY f; 1139f MAX(a) 11402018-06-19 00:00:00 1 1141Warnings: 1142Warning 1292 Truncated incorrect datetime value: 'N/A' 1143DROP TABLE t1; 1144# 1145# MDEV-17972 Assertion `is_valid_value_slow()' failed in Datetime::Datetime 1146# 1147SET time_zone='+00:00'; 1148CREATE TABLE t1 (a TIMESTAMP(6)) ENGINE=MyISAM; 1149INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); 1150FLUSH TABLES; 1151MYD 1152FF77777777FFFFFF 1153SELECT a, CAST(a AS DATETIME) AS dt0, CAST(a AS DATETIME(6)) AS dt6 FROM t1; 1154a dt0 dt6 11552033-07-07 03:01:11.999999 2033-07-07 03:01:11 2033-07-07 03:01:11.999999 1156DROP TABLE t1; 1157SET time_zone=DEFAULT; 1158# 1159# MDEV-18072 Assertion `is_null() == item->null_value || conv' failed in Timestamp_or_zero_datetime_native_null::Timestamp_or_zero_datetime_native_null upon query with GROUP BY 1160# 1161CREATE TABLE t1 (t TIMESTAMP); 1162INSERT INTO t1 () VALUES (),(); 1163SELECT IF(0,t,NULL) AS f FROM t1 GROUP BY 'foo'; 1164f 1165NULL 1166DROP TABLE t1; 1167# 1168# MDEV-18145 Assertion `0' failed in Item::val_native upon SELECT subquery with timestamp 1169# 1170CREATE TABLE t1 (a INT) ENGINE=MyISAM; 1171CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; 1172INSERT INTO t2 VALUES (1),(2); 1173CREATE TABLE t3 (pk INT PRIMARY KEY, b TIMESTAMP) ENGINE=MyISAM; 1174SELECT ( SELECT b FROM t1 LIMIT 1 ) AS sq FROM t2 LEFT JOIN t3 USING (pk); 1175sq 1176NULL 1177NULL 1178DROP TABLE t1, t2, t3; 1179# 1180# MDEV-18447 Assertion `!is_zero_datetime()' failed in Timestamp_or_zero_datetime::tv 1181# 1182CREATE TABLE t1 (a TIMESTAMP DEFAULT 0, b TIMESTAMP DEFAULT 0, c TIME DEFAULT 0); 1183INSERT INTO t1 VALUES (0,0,0); 1184SELECT c IN (GREATEST(a,b)) FROM t1; 1185c IN (GREATEST(a,b)) 11860 1187DROP TABLE t1; 1188# 1189# MDEV-17969 Assertion `name' failed in THD::push_warning_truncated_value_for_field 1190# 1191CREATE TABLE t1 (d DATE); 1192INSERT INTO t1 VALUES ('2018-01-01'),('2019-01-01'); 1193SET SESSION SQL_MODE= 'STRICT_ALL_TABLES,NO_ZERO_DATE'; 1194CREATE TABLE t2 SELECT 1 AS f FROM t1 GROUP BY FROM_DAYS(d); 1195ERROR 22007: Truncated incorrect date value: '0000-00-00' 1196DROP TABLE t1; 1197# 1198# MDEV-19124 Assertion `0' failed in Item::val_native 1199# 1200CREATE TABLE t1 (d1 TIMESTAMP(5)); 1201INSERT INTO t1 VALUES ('2018-10-14 15:31:01'); 1202SELECT LEAD(d1,1) OVER(ORDER BY d1) FROM t1; 1203LEAD(d1,1) OVER(ORDER BY d1) 1204NULL 1205SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1; 1206LAG(d1,1) OVER(ORDER BY d1) 1207NULL 1208INSERT INTO t1 VALUES ('2018-10-14 15:31:02'); 1209INSERT INTO t1 VALUES ('2018-10-14 15:31:03'); 1210SELECT LEAD(d1,1) OVER(ORDER BY d1) FROM t1; 1211LEAD(d1,1) OVER(ORDER BY d1) 12122018-10-14 15:31:02.00000 12132018-10-14 15:31:03.00000 1214NULL 1215SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1; 1216LAG(d1,1) OVER(ORDER BY d1) 1217NULL 12182018-10-14 15:31:01.00000 12192018-10-14 15:31:02.00000 1220DROP TABLE t1; 1221# 1222# MDEV-18240 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed 1223# 1224CREATE TABLE t1 (c1 timestamp); 1225SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r')); 1226k1 1227Warnings: 1228Warning 1292 Truncated incorrect datetime value: 'r' 1229SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); 1230c1 1231Warnings: 1232Warning 1292 Truncated incorrect datetime value: 'r' 1233DROP TABLE t1; 1234CREATE TABLE t1 (c1 timestamp); 1235INSERT INTO t1 VALUES ('2010-01-01 00:00:00'); 1236SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT '2010-01-01 10:00:00' UNION SELECT '2001-01-01 10:00:01'); 1237c1 1238SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT '2000-01-01 10:00:00' UNION SELECT '2000-01-01 10:00:01'); 1239c1 12402010-01-01 00:00:00 1241DROP TABLE t1; 1242# 1243# MDEV-18595 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed / Predicant_to_list_comparator::cmp_arg 1244# 1245CREATE TABLE t1 (t TIMESTAMP DEFAULT '1971-01-01 00:00:00', f INT); 1246INSERT INTO t1 VALUES ('1978-05-25 22:25:03',1),('2000-01-01 00:00:00',2); 1247SELECT * FROM t1 WHERE f IN (DEFAULT(t),1); 1248t f 12491978-05-25 22:25:03 1 1250Warnings: 1251Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`f` at row 1 1252Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`f` at row 2 1253DROP TABLE t1; 1254# 1255# MDEV-18503 Assertion `native.length() == binlen' failed in Type_handler_timestamp_common::make_sort_key 1256# 1257SET sql_mode=''; 1258CREATE TABLE t1 (a TIMESTAMP(3) DEFAULT 0, b TIMESTAMP); 1259INSERT INTO t1 (b) VALUES ('2012-12-12 12:12:12'),('1988-08-26 12:12:12'); 1260SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1; 1261f 12621988-08-26 12:12:12.000 12632012-12-12 12:12:12.000 1264SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1 DESC; 1265f 12662012-12-12 12:12:12.000 12671988-08-26 12:12:12.000 1268DROP TABLE t1; 1269SET sql_mode=DEFAULT; 1270# 1271# MDEV-20417 Assertion `(m_ptr == __null) == item->null_value' failed in VDec::VDec(Item*) 1272# 1273CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; 1274INSERT IGNORE INTO t1 VALUES ('2001-01-01','2002-01-01'),('2003-01-01','2004-01-01'); 1275SELECT * FROM t1 WHERE DEFAULT(b) - a; 1276a b 1277Warnings: 1278Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated 1279Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated 1280SELECT LEFT('', DEFAULT(b)-a) FROM t1; 1281LEFT('', DEFAULT(b)-a) 1282 1283 1284Warnings: 1285Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated 1286Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated 1287DROP TABLE t1; 1288CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM; 1289INSERT IGNORE INTO t1 (a) VALUES ('2001-01-01'),('2003-01-01'); 1290SELECT * FROM t1 WHERE (SELECT MIN(b) FROM t1) - a; 1291a b 1292Warnings: 1293Warning 1916 Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated 1294Warning 1916 Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated 1295SELECT (SELECT MIN(b) FROM t1) - a FROM t1; 1296(SELECT MIN(b) FROM t1) - a 1297-20010101000000.0000 1298-20030101000000.0000 1299DROP TABLE t1; 1300# 1301# MDEV-22734 Assertion `mon > 0 && mon < 13' failed in sec_since_epoch 1302# 1303SET time_zone="-02:00"; 1304CREATE TABLE t1(c TIMESTAMP KEY); 1305SELECT * FROM t1 WHERE c='2010-00-01 00:00:00'; 1306c 1307Warnings: 1308Warning 1292 Incorrect datetime value: '2010-00-01 00:00:00' 1309DROP TABLE t1; 1310# 1311# MDEV-22854 Garbage returned with SELECT CASE..DEFAULT(timestamp_field_with_now_as_default) 1312# 1313SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.456789'); 1314CREATE TABLE t1 (a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP); 1315INSERT INTO t1 VALUES ('2019-02-23 11:31:04'),('2023-02-09 00:00:00'); 1316SELECT CASE WHEN a THEN DEFAULT(a) END FROM t1; 1317CASE WHEN a THEN DEFAULT(a) END 13182001-01-01 10:20:30.456 13192001-01-01 10:20:30.456 1320DROP TABLE t1; 1321SET timestamp=DEFAULT; 1322# 1323# End of 10.4 tests 1324# 1325