drop table if exists t1,t2;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
set time_zone="+03:00";
CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (a int, t datetime);
SET TIMESTAMP=1234;
insert into t1 values(1,NULL);
insert into t1 values(2,"2002-03-03");
SET TIMESTAMP=1235;
insert into t1 values(3,NULL);
SET TIMESTAMP=1236;
insert into t1 (a) values(4);
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
SET TIMESTAMP=1237;
insert into t1 select * from t2;
SET TIMESTAMP=1238;
insert into t1 (a) select a+1 from t2 where a=8;
select * from t1;
a	t
1	1970-01-01 03:20:34
2	2002-03-03 00:00:00
3	1970-01-01 03:20:35
4	1970-01-01 03:20:36
5	2002-03-04 00:00:00
6	1970-01-01 03:20:37
7	2002-03-05 00:00:00
8	0000-00-00 00:00:00
9	1970-01-01 03:20:38
drop table t1,t2;
SET TIMESTAMP=1234;
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));
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
UPDATE t1 SET value="my value" WHERE id="myKey";
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
UPDATE t1 SET id="myKey" WHERE value="my value";
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
drop table t1;
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values (now());
select date_format(a,"%Y %y"),year(a),year(now()) from t1;
date_format(a,"%Y %y")	year(a)	year(now())
1970 70	1970	1970
drop table t1;
create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
select ix+0 from t1;
ix+0
19991101000000
19990102030405
19990630232922
19990601000000
19990930232922
19990531232922
19990501000000
19991101000000
19990501000000
truncate table t1;
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
select ix+0 from t1;
ix+0
19991101000000
19990102030405
19990630232922
19990601000000
drop table t1;
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
SELECT * FROM t1;
date	date_time	time_stamp
1998-12-31	1998-12-31 23:59:59	1998-12-31 23:59:59
1999-01-01	1999-01-01 00:00:00	1999-01-01 00:00:00
1999-09-09	1999-09-09 23:59:59	1999-09-09 23:59:59
2000-01-01	2000-01-01 00:00:00	2000-01-01 00:00:00
2000-02-28	2000-02-28 00:00:00	2000-02-28 00:00:00
2000-02-29	2000-02-29 00:00:00	2000-02-29 00:00:00
2000-03-01	2000-03-01 00:00:00	2000-03-01 00:00:00
2000-12-31	2000-12-31 23:59:59	2000-12-31 23:59:59
2001-01-01	2001-01-01 00:00:00	2001-01-01 00:00:00
2004-12-31	2004-12-31 23:59:59	2004-12-31 23:59:59
2005-01-01	2005-01-01 00:00:00	2005-01-01 00:00:00
2030-01-01	2030-01-01 00:00:00	2030-01-01 00:00:00
drop table t1;
create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
Warnings:
Warning	1265	Data truncated for column 'ix' at row 2
Warning	1265	Data truncated for column 'ix' at row 3
Warning	1265	Data truncated for column 'ix' at row 4
Warning	1265	Data truncated for column 'ix' at row 5
Warning	1265	Data truncated for column 'ix' at row 6
Warning	1265	Data truncated for column 'ix' at row 7
Warning	1265	Data truncated for column 'ix' at row 8
select ix+0 from t1;
ix+0
0
0
0
0
0
0
0
0
truncate table t1;
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
Warnings:
Warning	1265	Data truncated for column 'ix' at row 2
Warning	1265	Data truncated for column 'ix' at row 3
Warning	1265	Data truncated for column 'ix' at row 4
Warning	1265	Data truncated for column 'ix' at row 5
Warning	1265	Data truncated for column 'ix' at row 6
Warning	1264	Out of range value for column 'ix' at row 7
Warning	1264	Out of range value for column 'ix' at row 8
select ix+0 from t1;
ix+0
0
0
0
0
0
0
0
0
truncate table t1;
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
Warnings:
Warning	1265	Data truncated for column 'ix' at row 1
Warning	1265	Data truncated for column 'ix' at row 2
select ix+0 from t1;
ix+0
0
20030101000000
drop table t1;
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');
SET TIMESTAMP=1000000000;
insert into t1 values ();
SET TIMESTAMP=1000000001;
update t1 set t2=now();
SET TIMESTAMP=1000000002;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2003-01-01 00:00:00	2001-09-09 04:46:41	0000-00-00 00:00:00
2003-01-01 00:00:00	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00',
  `t2` datetime DEFAULT NULL,
  `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		2003-01-01 00:00:00	
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00');
SET TIMESTAMP=1000000002;
insert into t1 values ();
SET TIMESTAMP=1000000003;
update t1 set t2=now();
SET TIMESTAMP=1000000003;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2001-09-09 04:46:42	2001-09-09 04:46:43	0000-00-00 00:00:00
2001-09-09 04:46:43	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t2` datetime DEFAULT NULL,
  `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime);
SET TIMESTAMP=1000000004;
insert into t1 values ();
select * from t1;
t1	t2
2003-01-01 00:00:00	NULL
SET TIMESTAMP=1000000005;
update t1 set t2=now();
SET TIMESTAMP=1000000005;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:45	2001-09-09 04:46:45
2003-01-01 00:00:00	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `t2` datetime DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		2003-01-01 00:00:00	on update CURRENT_TIMESTAMP
t2	datetime	YES		NULL	
drop table t1;
create table t1 (t1 timestamp not null default now() on update now(), t2 datetime);
SET TIMESTAMP=1000000006;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:46	NULL
SET TIMESTAMP=1000000007;
update t1 set t2=now();
SET TIMESTAMP=1000000007;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:47	2001-09-09 04:46:47
2001-09-09 04:46:47	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` datetime DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
t2	datetime	YES		NULL	
drop table t1;
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');
SET TIMESTAMP=1000000007;
insert into t1 values ();
select * from t1;
t1	t2	t3
2001-09-09 04:46:47	NULL	0000-00-00 00:00:00
SET TIMESTAMP=1000000008;
update t1 set t2=now();
SET TIMESTAMP=1000000008;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2001-09-09 04:46:48	2001-09-09 04:46:48	0000-00-00 00:00:00
2001-09-09 04:46:48	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` datetime DEFAULT NULL,
  `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime);
SET TIMESTAMP=1000000009;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:49	NULL
SET TIMESTAMP=1000000010;
update t1 set t2=now();
SET TIMESTAMP=1000000011;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:50	2001-09-09 04:46:50
2001-09-09 04:46:51	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` datetime DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
t2	datetime	YES		NULL	
truncate table t1;
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
SET TIMESTAMP=1000000012;
update t1 set t1= '2004-04-02 00:00:00';
select * from t1;
t1	t2
2004-04-02 00:00:00	2004-04-01 00:00:00
update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
select * from t1;
t1	t2
2004-04-03 00:00:00	2004-04-01 00:00:00
drop table t1;
create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000013;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:53	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000014;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2003-01-01 00:00:00	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000015;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:55	20
drop table t1;
create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp);
insert into t1 values ('2004-04-01 00:00:00');
SET TIMESTAMP=1000000016;
alter table t1 add i int default 10;
select * from t1;
t1	i
2004-04-01 00:00:00	10
drop table t1;
create table t1 (a timestamp null, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT NULL,
  `b` timestamp NULL DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000017;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
NULL	NULL
drop table t1;
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `b` timestamp NULL DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000018;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
2001-09-09 04:46:58	NULL
drop table t1;
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT NULL,
  `b` timestamp NULL DEFAULT '2003-01-01 00:00:00'
) ENGINE=ENGINE DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
insert into t1 values (DEFAULT, DEFAULT);
select * from t1;
a	b
NULL	NULL
NULL	2003-01-01 00:00:00
drop table t1;
create table t1 (a bigint, b bigint);
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
set timestamp=1000000019;
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';
select * from t1;
a	b
2001-09-09 04:46:59	2001-09-09 04:46:59
2003-01-01 00:00:00	2003-01-02 00:00:00
drop table t1;
create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
('b', '2004-02-01 00:00:00');
select max(t) from t1 group by a;
max(t)
2004-01-01 01:00:00
2004-02-01 00:00:00
drop table t1;
set sql_mode='maxdb';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
Warning	3090	Changing sql mode 'MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS' is deprecated. It will be removed in a future release.
create table t1 (a timestamp, b timestamp);
Warnings:
Warning	3226	With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
Warning	3226	With the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME. The MAXDB SQL mode is deprecated and will be removed in a future release. Please disable the MAXDB SQL mode and use DATETIME instead.
show create table t1;
Table	Create Table
t1	CREATE TABLE "t1" (
  "a" datetime DEFAULT NULL,
  "b" datetime DEFAULT NULL
)
set sql_mode='';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
drop table t1;
create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	0	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
update t1 set b = 2, c = c where a = 2;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
insert into t1 (a) values (4);
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2001-09-09 04:46:59
update t1 set c = '2004-04-04 04:04:04' where a = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
5	NULL	2001-09-09 04:46:59
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	4	2001-09-09 04:46:59
5	NULL	2001-09-09 04:46:59
6	NULL	2006-06-06 06:06:06
drop table t1;
End of 4.1 tests
set time_zone= @@global.time_zone;
CREATE TABLE t1 (
`id` int(11) NOT NULL auto_increment,
`username` varchar(80) NOT NULL default '',
`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
show fields from t1;
Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
username	varchar(80)	NO			
posted_on	timestamp	NO		0000-00-00 00:00:00	
select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
is_nullable
NO
drop table t1;
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00');
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
SELECT f1,f2-f3 FROM t1;
f1	f2-f3
1	0
2	0
3	0
4	0
5	0
DROP TABLE t1;
End of 5.0 tests
#
# Bug #55779: select does not work properly in mysql server
#             Version "5.1.42 SUSE MySQL RPM"
#
CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a));
INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'),
('2000-01-01 00:00:01'), ('2000-01-01 00:00:01');
SELECT a FROM t1 WHERE a >=  20000101000000;
a
2000-01-01 00:00:00
2000-01-01 00:00:00
2000-01-01 00:00:01
2000-01-01 00:00:01
SELECT a FROM t1 WHERE a >= '20000101000000';
a
2000-01-01 00:00:00
2000-01-01 00:00:00
2000-01-01 00:00:01
2000-01-01 00:00:01
DROP TABLE t1;
#
# Bug#50774: failed to get the correct resultset when timestamp values 
# are appended with .0
#
CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
a
2010-02-01 09:31:02
2010-02-01 09:31:03
2010-02-01 09:31:04
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a;
a
2010-02-01 09:31:02
2010-02-01 09:31:03
2010-02-01 09:31:04
SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0';
a
2010-02-01 09:31:01
2010-02-01 09:31:02
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a;
a
2010-02-01 09:31:01
2010-02-01 09:31:02
EXPLAIN
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	NULL	range	x	x	x	x	x	100.00	x
Warnings:
x	x	x
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
a
2010-02-01 09:31:02
2010-02-01 09:31:03
2010-02-01 09:31:04
CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) );
INSERT INTO t2 VALUES( '2010-02-01 09:31:01' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:02' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:03' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:04' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:05' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:06' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:07' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:08' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:09' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:10' );
INSERT INTO t2 VALUES( '2010-02-01 09:31:11' );
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	OK
# The bug would cause the range optimizer's comparison to use an open
# interval here. This reveals itself only in the number of reads 
# performed.
FLUSH STATUS;
EXPLAIN
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
x	x	x	NULL	range	x	x	x	x	x	100.00	x
Warnings:
x	x	x
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
a
2010-02-01 09:31:01
SHOW STATUS LIKE 'Handler_read_next';
Variable_name	Value
Handler_read_next	1
DROP TABLE t1, t2;
End of 5.1 tests

Bug#50888 valgrind warnings in Field_timestamp::val_str

SET TIMESTAMP=0;
CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02');
FLUSH TABLES t1;
SELECT MAX(a) FROM t1;
MAX(a)
2010-03-05 11:08:02
SELECT a FROM t1;
a
2008-02-23 09:23:45
2010-03-05 11:08:02
DROP TABLE t1;
End of Bug#50888
#
# Bug59330: Incorrect result when comparing an aggregate
#           function with TIMESTAMP
#
CREATE TABLE t1 (dt DATETIME, ts TIMESTAMP);
INSERT INTO t1 VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
SELECT MAX(dt), MAX(ts) FROM t1;
MAX(dt)	MAX(ts)
2011-01-06 12:34:30	2011-01-06 12:34:30
SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1;
MAX(ts) < '2010-01-01 00:00:00'
0
SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1;
MAX(dt) < '2010-01-01 00:00:00'
0
SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1;
MAX(ts) > '2010-01-01 00:00:00'
1
SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1;
MAX(dt) > '2010-01-01 00:00:00'
1
SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1;
MAX(ts) = '2011-01-06 12:34:30'
1
SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
MAX(dt) = '2011-01-06 12:34:30'
1
DROP TABLE t1;
End of 5.5 tests
#
# Start of 5.6 tests
#
#
# Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
#
CREATE TABLE t1 (
`c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);
INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
SELECT c2-c1 FROM t1;
c2-c1
-30389003988
SELECT * FROM t1;
c1	c2
2003-05-16 23:53:29	2000-01-27 23:13:41
SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'
-30389003988
SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29')
-30389003988
DROP TABLE t1;
SET sql_mode = default;
#
# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
# or NO_ZERO_IN_DATE SQL mode is set.
DROP TABLE IF EXISTS t1, t2, t3;
SET @org_mode=@@sql_mode;
#Table creation in strict mode
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
ERROR 42000: Invalid default value for 'c1'
#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
SET @@sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
DROP TABLE t1, t2, t3;
#Table creation with out any SQL modes
SET @@sql_mode='';
CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 INT);
#Alter table in strict mode  with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
ERROR 42000: Invalid default value for 'c2'
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '0000-00-00';
ERROR 42000: Invalid default value for 'c2'
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '2012-02-00';
ERROR 42000: Invalid default value for 'c2'
#Alter table with out any SQL modes
SET @@sql_mode='';
ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
Warnings:
Warning	1264	Out of range value for column 'c4' at row 1
DROP TABLE t1;
SET @@sql_mode= @org_mode;
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
# END of Test for bug#11747847 - 34280
#
# End of 5.6 tests
#