1
drop table if exists t1,t2;
2
set time_zone="+03:00";
3
CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
4
CREATE TABLE t2 (a int, t datetime);
6
insert into t1 values(1,NULL);
7
insert into t1 values(2,"2002-03-03");
9
insert into t1 values(3,NULL);
11
insert into t1 (a) values(4);
12
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
14
insert into t1 select * from t2;
16
insert into t1 (a) select a+1 from t2 where a=8;
30
CREATE 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));
31
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
32
SELECT stamp FROM t1 WHERE id="myKey";
35
UPDATE t1 SET value="my value" WHERE id="myKey";
36
SELECT stamp FROM t1 WHERE id="myKey";
39
UPDATE t1 SET id="myKey" WHERE value="my value";
40
SELECT stamp FROM t1 WHERE id="myKey";
44
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
45
insert into t1 values (now());
46
select date_format(a,"%Y %y"),year(a),year(now()) from t1;
47
date_format(a,"%Y %y") year(a) year(now())
50
create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
51
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
64
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
72
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
73
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
74
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
75
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
76
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
77
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
78
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
79
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
80
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
81
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
82
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
83
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
84
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
86
date date_time time_stamp
87
1998-12-31 1998-12-31 23:59:59 1998-12-31 23:59:59
88
1999-01-01 1999-01-01 00:00:00 1999-01-01 00:00:00
89
1999-09-09 1999-09-09 23:59:59 1999-09-09 23:59:59
90
2000-01-01 2000-01-01 00:00:00 2000-01-01 00:00:00
91
2000-02-28 2000-02-28 00:00:00 2000-02-28 00:00:00
92
2000-02-29 2000-02-29 00:00:00 2000-02-29 00:00:00
93
2000-03-01 2000-03-01 00:00:00 2000-03-01 00:00:00
94
2000-12-31 2000-12-31 23:59:59 2000-12-31 23:59:59
95
2001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00
96
2004-12-31 2004-12-31 23:59:59 2004-12-31 23:59:59
97
2005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00
98
2030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00
100
create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
101
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
103
Warning 1265 Data truncated for column 'ix' at row 2
104
Warning 1265 Data truncated for column 'ix' at row 3
105
Warning 1265 Data truncated for column 'ix' at row 4
106
Warning 1265 Data truncated for column 'ix' at row 5
107
Warning 1265 Data truncated for column 'ix' at row 6
108
Warning 1265 Data truncated for column 'ix' at row 7
109
Warning 1265 Data truncated for column 'ix' at row 8
121
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
123
Warning 1265 Data truncated for column 'ix' at row 2
124
Warning 1265 Data truncated for column 'ix' at row 3
125
Warning 1265 Data truncated for column 'ix' at row 4
126
Warning 1265 Data truncated for column 'ix' at row 5
127
Warning 1265 Data truncated for column 'ix' at row 6
128
Warning 1264 Out of range value for column 'ix' at row 7
129
Warning 1264 Out of range value for column 'ix' at row 8
141
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
143
Warning 1265 Data truncated for column 'ix' at row 1
144
Warning 1265 Data truncated for column 'ix' at row 2
150
create 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');
151
SET TIMESTAMP=1000000000;
152
insert into t1 values ();
153
SET TIMESTAMP=1000000001;
154
update t1 set t2=now();
155
SET TIMESTAMP=1000000002;
156
insert into t1 (t1,t3) values (default, default);
159
2003-01-01 00:00:00 2001-09-09 04:46:41 0000-00-00 00:00:00
160
2003-01-01 00:00:00 NULL 0000-00-00 00:00:00
161
show create table t1;
163
t1 CREATE TABLE `t1` (
164
`t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00',
165
`t2` datetime DEFAULT NULL,
166
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
167
) ENGINE=MyISAM DEFAULT CHARSET=latin1
168
show columns from t1;
169
Field Type Null Key Default Extra
170
t1 timestamp NO 2003-01-01 00:00:00
172
t3 timestamp NO 0000-00-00 00:00:00
174
create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00');
175
SET TIMESTAMP=1000000002;
176
insert into t1 values ();
177
SET TIMESTAMP=1000000003;
178
update t1 set t2=now();
179
SET TIMESTAMP=1000000003;
180
insert into t1 (t1,t3) values (default, default);
183
2001-09-09 04:46:42 2001-09-09 04:46:43 0000-00-00 00:00:00
184
2001-09-09 04:46:43 NULL 0000-00-00 00:00:00
185
show create table t1;
187
t1 CREATE TABLE `t1` (
188
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
189
`t2` datetime DEFAULT NULL,
190
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
191
) ENGINE=MyISAM DEFAULT CHARSET=latin1
192
show columns from t1;
193
Field Type Null Key Default Extra
194
t1 timestamp NO CURRENT_TIMESTAMP
196
t3 timestamp NO 0000-00-00 00:00:00
198
create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime);
199
SET TIMESTAMP=1000000004;
200
insert into t1 values ();
203
2003-01-01 00:00:00 NULL
204
SET TIMESTAMP=1000000005;
205
update t1 set t2=now();
206
SET TIMESTAMP=1000000005;
207
insert into t1 (t1) values (default);
210
2001-09-09 04:46:45 2001-09-09 04:46:45
211
2003-01-01 00:00:00 NULL
212
show create table t1;
214
t1 CREATE TABLE `t1` (
215
`t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
216
`t2` datetime DEFAULT NULL
217
) ENGINE=MyISAM DEFAULT CHARSET=latin1
218
show columns from t1;
219
Field Type Null Key Default Extra
220
t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP
223
create table t1 (t1 timestamp not null default now() on update now(), t2 datetime);
224
SET TIMESTAMP=1000000006;
225
insert into t1 values ();
228
2001-09-09 04:46:46 NULL
229
SET TIMESTAMP=1000000007;
230
update t1 set t2=now();
231
SET TIMESTAMP=1000000007;
232
insert into t1 (t1) values (default);
235
2001-09-09 04:46:47 2001-09-09 04:46:47
236
2001-09-09 04:46:47 NULL
237
show create table t1;
239
t1 CREATE TABLE `t1` (
240
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
241
`t2` datetime DEFAULT NULL
242
) ENGINE=MyISAM DEFAULT CHARSET=latin1
243
show columns from t1;
244
Field Type Null Key Default Extra
245
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
248
create 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');
249
SET TIMESTAMP=1000000007;
250
insert into t1 values ();
253
2001-09-09 04:46:47 NULL 0000-00-00 00:00:00
254
SET TIMESTAMP=1000000008;
255
update t1 set t2=now();
256
SET TIMESTAMP=1000000008;
257
insert into t1 (t1,t3) values (default, default);
260
2001-09-09 04:46:48 2001-09-09 04:46:48 0000-00-00 00:00:00
261
2001-09-09 04:46:48 NULL 0000-00-00 00:00:00
262
show create table t1;
264
t1 CREATE TABLE `t1` (
265
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
266
`t2` datetime DEFAULT NULL,
267
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
268
) ENGINE=MyISAM DEFAULT CHARSET=latin1
269
show columns from t1;
270
Field Type Null Key Default Extra
271
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
273
t3 timestamp NO 0000-00-00 00:00:00
275
create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime);
276
SET TIMESTAMP=1000000009;
277
insert into t1 values ();
280
2001-09-09 04:46:49 NULL
281
SET TIMESTAMP=1000000010;
282
update t1 set t2=now();
283
SET TIMESTAMP=1000000011;
284
insert into t1 (t1) values (default);
287
2001-09-09 04:46:50 2001-09-09 04:46:50
288
2001-09-09 04:46:51 NULL
289
show create table t1;
291
t1 CREATE TABLE `t1` (
292
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
293
`t2` datetime DEFAULT NULL
294
) ENGINE=MyISAM DEFAULT CHARSET=latin1
295
show columns from t1;
296
Field Type Null Key Default Extra
297
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
300
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
301
SET TIMESTAMP=1000000012;
302
update t1 set t1= '2004-04-02 00:00:00';
305
2004-04-02 00:00:00 2004-04-01 00:00:00
306
update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
309
2004-04-03 00:00:00 2004-04-01 00:00:00
311
create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
312
insert into t1 values (1, '2004-04-01 00:00:00', 10);
313
SET TIMESTAMP=1000000013;
314
replace into t1 set pk = 1, bulk= 20;
317
1 2001-09-09 04:46:53 20
319
create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
320
insert into t1 values (1, '2004-04-01 00:00:00', 10);
321
SET TIMESTAMP=1000000014;
322
replace into t1 set pk = 1, bulk= 20;
325
1 2003-01-01 00:00:00 20
327
create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
328
insert into t1 values (1, '2004-04-01 00:00:00', 10);
329
SET TIMESTAMP=1000000015;
330
replace into t1 set pk = 1, bulk= 20;
333
1 2001-09-09 04:46:55 20
335
create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp);
336
insert into t1 values ('2004-04-01 00:00:00');
337
SET TIMESTAMP=1000000016;
338
alter table t1 add i int default 10;
341
2004-04-01 00:00:00 10
343
create table t1 (a timestamp null, b timestamp null);
344
show create table t1;
346
t1 CREATE TABLE `t1` (
347
`a` timestamp NULL DEFAULT NULL,
348
`b` timestamp NULL DEFAULT NULL
349
) ENGINE=MyISAM DEFAULT CHARSET=latin1
350
insert into t1 values (NULL, NULL);
351
SET TIMESTAMP=1000000017;
352
insert into t1 values ();
358
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
359
show create table t1;
361
t1 CREATE TABLE `t1` (
362
`a` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
363
`b` timestamp NULL DEFAULT NULL
364
) ENGINE=MyISAM DEFAULT CHARSET=latin1
365
insert into t1 values (NULL, NULL);
366
SET TIMESTAMP=1000000018;
367
insert into t1 values ();
371
2001-09-09 04:46:58 NULL
373
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
374
show create table t1;
376
t1 CREATE TABLE `t1` (
377
`a` timestamp NULL DEFAULT NULL,
378
`b` timestamp NULL DEFAULT '2003-01-01 00:00:00'
379
) ENGINE=MyISAM DEFAULT CHARSET=latin1
380
insert into t1 values (NULL, NULL);
381
insert into t1 values (DEFAULT, DEFAULT);
385
NULL 2003-01-01 00:00:00
387
create table t1 (a bigint, b bigint);
388
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
389
set timestamp=1000000019;
390
alter 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';
393
2001-09-09 04:46:59 2001-09-09 04:46:59
394
2003-01-01 00:00:00 2003-01-02 00:00:00
396
create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
397
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
398
('b', '2004-02-01 00:00:00');
399
select max(t) from t1 group by a;
404
set sql_mode='maxdb';
405
create table t1 (a timestamp, b timestamp);
406
show create table t1;
408
t1 CREATE TABLE "t1" (
409
"a" datetime DEFAULT NULL,
410
"b" datetime DEFAULT NULL
414
create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
415
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
416
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
419
1 0 2001-01-01 01:01:01
420
2 0 2002-02-02 02:02:02
421
3 0 2003-03-03 03:03:03
422
update t1 set b = 2, c = c where a = 2;
425
1 0 2001-01-01 01:01:01
426
2 2 2002-02-02 02:02:02
427
3 0 2003-03-03 03:03:03
428
insert into t1 (a) values (4);
431
1 0 2001-01-01 01:01:01
432
2 2 2002-02-02 02:02:02
433
3 0 2003-03-03 03:03:03
434
4 NULL 2001-09-09 04:46:59
435
update t1 set c = '2004-04-04 04:04:04' where a = 4;
438
1 0 2001-01-01 01:01:01
439
2 2 2002-02-02 02:02:02
440
3 0 2003-03-03 03:03:03
441
4 NULL 2004-04-04 04:04:04
442
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
445
1 0 2001-01-01 01:01:01
446
2 2 2002-02-02 02:02:02
447
3 3 2003-03-03 03:03:03
448
4 NULL 2004-04-04 04:04:04
449
5 NULL 2001-09-09 04:46:59
450
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
451
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
454
1 0 2001-01-01 01:01:01
455
2 2 2002-02-02 02:02:02
456
3 3 2003-03-03 03:03:03
457
4 4 2001-09-09 04:46:59
458
5 NULL 2001-09-09 04:46:59
459
6 NULL 2006-06-06 06:06:06
462
set time_zone= @@global.time_zone;
464
`id` int(11) NOT NULL auto_increment,
465
`username` varchar(80) NOT NULL default '',
466
`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
468
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
470
Field Type Null Key Default Extra
471
id int(11) NO PRI NULL auto_increment
472
username varchar(80) NO
473
posted_on timestamp NO 0000-00-00 00:00:00
474
select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
478
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
479
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
480
f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00');
481
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
482
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
483
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
484
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
485
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
486
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
487
SELECT f1,f2-f3 FROM t1;
497
# Bug #55779: select does not work properly in mysql server
498
# Version "5.1.42 SUSE MySQL RPM"
500
CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a));
501
INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'),
502
('2000-01-01 00:00:01'), ('2000-01-01 00:00:01');
503
SELECT a FROM t1 WHERE a >= 20000101000000;
509
SELECT a FROM t1 WHERE a >= '20000101000000';
517
# Bug#50774: failed to get the correct resultset when timestamp values
518
# are appended with .0
520
CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
521
INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
522
INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
523
INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
524
INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
525
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
530
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a;
535
SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0';
539
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a;
544
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
545
id select_type table type possible_keys key key_len ref rows Extra
546
x x x range x x x x x x
547
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
552
CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) );
553
INSERT INTO t2 VALUES( '2010-02-01 09:31:01' );
554
INSERT INTO t2 VALUES( '2010-02-01 09:31:02' );
555
INSERT INTO t2 VALUES( '2010-02-01 09:31:03' );
556
INSERT INTO t2 VALUES( '2010-02-01 09:31:04' );
557
INSERT INTO t2 VALUES( '2010-02-01 09:31:05' );
558
INSERT INTO t2 VALUES( '2010-02-01 09:31:06' );
559
INSERT INTO t2 VALUES( '2010-02-01 09:31:07' );
560
INSERT INTO t2 VALUES( '2010-02-01 09:31:08' );
561
INSERT INTO t2 VALUES( '2010-02-01 09:31:09' );
562
INSERT INTO t2 VALUES( '2010-02-01 09:31:10' );
563
INSERT INTO t2 VALUES( '2010-02-01 09:31:11' );
564
# The bug would cause the range optimizer's comparison to use an open
565
# interval here. This reveals itself only in the number of reads
569
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
570
id select_type table type possible_keys key key_len ref rows Extra
571
x x x range x x x x x x
572
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
575
SHOW STATUS LIKE 'Handler_read_next';
581
Bug#50888 valgrind warnings in Field_timestamp::val_str
584
CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
585
INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02');
587
SELECT MAX(a) FROM t1;
597
# Bug59330: Incorrect result when comparing an aggregate
598
# function with TIMESTAMP
600
CREATE TABLE t1 (dt DATETIME, ts TIMESTAMP);
601
INSERT INTO t1 VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
602
SELECT MAX(dt), MAX(ts) FROM t1;
604
2011-01-06 12:34:30 2011-01-06 12:34:30
605
SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1;
606
MAX(ts) < '2010-01-01 00:00:00'
608
SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1;
609
MAX(dt) < '2010-01-01 00:00:00'
611
SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1;
612
MAX(ts) > '2010-01-01 00:00:00'
614
SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1;
615
MAX(dt) > '2010-01-01 00:00:00'
617
SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1;
618
MAX(ts) = '2011-01-06 12:34:30'
620
SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
621
MAX(dt) = '2011-01-06 12:34:30'
629
# Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
632
`c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
633
`c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
635
INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
636
SELECT c2-c1 FROM t1;
641
2003-05-16 23:53:29 2000-01-27 23:13:41
642
SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
643
TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'
645
SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
646
TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29')
650
# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
651
# or NO_ZERO_IN_DATE SQL mode is set.
652
DROP TABLE IF EXISTS t1, t2, t3;
653
SET @org_mode=@@sql_mode;
654
#Table creation in strict mode
655
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
656
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
657
ERROR 42000: Invalid default value for 'c1'
658
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
659
ERROR 42000: Invalid default value for 'c1'
660
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
661
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
662
ERROR 42000: Invalid default value for 'c1'
663
#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
664
SET @@sql_mode='NO_ZERO_DATE';
665
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
667
Warning 1264 Out of range value for column 'c1' at row 1
668
CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
670
Warning 1264 Out of range value for column 'c1' at row 1
671
SET @@sql_mode='NO_ZERO_IN_DATE';
672
CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
674
Warning 1264 Out of range value for column 'c1' at row 1
675
DROP TABLE t1, t2, t3;
676
#Table creation with out any SQL modes
678
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
679
CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
680
CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
682
Warning 1264 Out of range value for column 'c1' at row 1
683
DROP TABLE t1, t2, t3;
684
CREATE TABLE t1 (c1 INT);
685
#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
686
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
687
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
688
ERROR 42000: Invalid default value for 'c2'
689
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '0000-00-00';
690
ERROR 42000: Invalid default value for 'c2'
691
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
692
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '2012-02-00';
693
ERROR 42000: Invalid default value for 'c2'
694
#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
695
SET @@sql_mode='NO_ZERO_DATE';
696
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
698
Warning 1264 Out of range value for column 'c2' at row 1
699
ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
701
Warning 1264 Out of range value for column 'c2' at row 1
702
Warning 1264 Out of range value for column 'c3' at row 1
703
SET @@sql_mode='NO_ZERO_IN_DATE';
704
ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
706
Warning 1264 Out of range value for column 'c4' at row 1
708
CREATE TABLE t1 (c1 INT);
709
#Alter table with out any SQL modes
711
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
712
ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
713
ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
715
Warning 1264 Out of range value for column 'c4' at row 1
717
SET @@sql_mode= @org_mode;
718
# END of Test for bug#11747847 - 34280