path: root/mysql-test/main/type_timestamp.result

drop table if exists t1,t2;
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	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 ignore 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, t2 timestamp default now());
drop table t1;
create table t1 (t1 timestamp, t2 timestamp on update now());
drop table t1;
create table t1 (t1 timestamp, t2 timestamp default now() on update now());
drop table t1;
create table t1 (t1 timestamp default now(), t2 timestamp on update now());
drop table t1;
create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now());
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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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=MyISAM 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';
create table t1 (a timestamp, b timestamp(5));
show create table t1;
Table	Create Table
t1	CREATE TABLE "t1" (
  "a" datetime DEFAULT NULL,
  "b" datetime(5) DEFAULT NULL
)
set sql_mode='';
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'));
Warnings:
Warning	1292	Truncated incorrect unixtime value: '9999999999'
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
Warnings:
Warning	1292	Truncated incorrect unixtime value: '9999999999'
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:30:01' );
INSERT INTO t1 VALUES( '2010-02-01 09:30:02' );
INSERT INTO t1 VALUES( '2010-02-01 09:30:03' );
INSERT INTO t1 VALUES( '2010-02-01 09:30:04' );
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' );
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:30:01
2010-02-01 09:30:02
2010-02-01 09:30:03
2010-02-01 09:30:04
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:30:01
2010-02-01 09:30:02
2010-02-01 09:30:03
2010-02-01 09:30:04
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	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	range	x	x	x	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' );
# 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	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	range	x	x	x	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
#
# lp:923429 Crash in decimal_cmp on using UNIX_TIMESTAMP with a wrongly formatted timestamp
#
SELECT UNIX_TIMESTAMP('abc') > 0;
UNIX_TIMESTAMP('abc') > 0
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'abc'
SELECT UNIX_TIMESTAMP('abc');
UNIX_TIMESTAMP('abc')
NULL
Warnings:
Warning	1292	Incorrect datetime value: 'abc'

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;
#
# MDEV-9413 "datetime >= coalesce(c1(NULL))" doesn't return expected NULL
#
CREATE TABLE t1(c1 TIMESTAMP(6) NULL DEFAULT NULL);
INSERT INTO t1 VALUES(NULL);
SELECT c1, '2016-06-13 20:00:00.000003' >= COALESCE( c1 ) FROM t1;
c1	'2016-06-13 20:00:00.000003' >= COALESCE( c1 )
NULL	NULL
DROP TABLE t1;
End of 5.5 tests
#
# MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL
#
SET time_zone='+02:00';
create table t1(value timestamp not null);
set @a:=0;
create function f1 () returns timestamp
begin
set @a = @a + 1;
return NULL;
end//
set timestamp=12340;
insert t1 values (f1());
select @a, value from t1;
@a	value
1	1970-01-01 05:25:40
set timestamp=12350;
update t1 set value = f1();
select @a, value from t1;
@a	value
2	1970-01-01 05:25:50
drop table t1;
drop function f1;
set timestamp=0;
create table t1(value timestamp null);
set @a:=0;
create function f1 () returns timestamp
begin
set @a = @a + 1;
return NULL;
end//
set timestamp=12340;
insert t1 values (f1());
select @a, value from t1;
@a	value
1	NULL
set timestamp=12350;
update t1 set value = f1();
select @a, value from t1;
@a	value
2	NULL
drop table t1;
drop function f1;
set timestamp=0;
SET time_zone=DEFAULT;
#
# MDEV-7778 impossible create copy of table, if table contain default value for timestamp field
#
SET sql_mode="NO_ZERO_DATE";
CREATE TABLE t1 (
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t2 AS SELECT * from t1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ts` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ts` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1,t2;
SET sql_mode=DEFAULT;
#
# MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT
#
CREATE TABLE t1 (
vc VARCHAR(10) NOT NULL DEFAULT 'test',
ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `vc` varchar(10) NOT NULL DEFAULT 'test',
  `ts` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `vc` varchar(10) NOT NULL DEFAULT 'test',
  `ts` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1,t2;
End of 10.0 tests
#
# Start of 10.1 tests
#
#
# MDEV-7831 Bad warning for DATE_ADD(timestamp_column, INTERVAL 10 SECOND) 
#
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
SELECT DATE_ADD(a, INTERVAL 10 SECOND) FROM t1;
DATE_ADD(a, INTERVAL 10 SECOND)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
DROP TABLE t1;
#
# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
#
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
SET sql_mode=TRADITIONAL;
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1
INSERT INTO t1 VALUES ();
ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
INSERT INTO t1 VALUES (DEFAULT);
ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'a'
DROP TABLE t1;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
SET sql_mode=TRADITIONAL;
INSERT INTO t1 (a) SELECT a FROM t2;
ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
DROP TABLE t1, t2;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00', b TIMESTAMP DEFAULT '0000-00-00 00:00:00');
INSERT INTO t1 VALUES (DEFAULT,DEFAULT);;
SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1;
DELETE FROM t1;
SET sql_mode=TRADITIONAL;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a);
ERROR 22007: Incorrect default value '0000-00-00 00:00:00' for column 'b'
DROP TABLE t1;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');;
SET sql_mode='NO_ZERO_DATE';
ALTER TABLE t1 ADD b INT NOT NULL;
ERROR 42000: Invalid default value for 'a'
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
#
#
# MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field
#
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a TIMESTAMP);;
INSERT INTO t1 VALUES (0);
SET sql_mode='TRADITIONAL';
CREATE TABLE t2 AS SELECT * FROM t1;
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t2`.`a` at row 1
DROP TABLE t1;
#
# End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field
#
#
# MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
#
CREATE TABLE t1 (a TIMESTAMP);;
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00x';
a
2001-01-01 00:00:00
Warnings:
Warning	1292	Truncated incorrect datetime value: '2001-01-01 00:00:00x'
SELECT * FROM t1 WHERE LENGTH(a) != 20;
a
2001-01-01 00:00:00
2001-01-01 00:00:01
SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x';
a
2001-01-01 00:00:00
Warnings:
Warning	1292	Truncated incorrect datetime value: '2001-01-01 00:00:00x'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Warning	1292	Truncated incorrect datetime value: '2001-01-01 00:00:00x'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Warning	1292	Truncated incorrect datetime value: '2001-01-01 00:00:00x'
Note	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()
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP);;
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=19;
a
2001-01-01 00:00:00
2001-01-01 00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00';
a
2001-01-01 00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	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()
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Warning	1292	Incorrect datetime value: ' garbage '
Warning	1292	Incorrect datetime value: ' garbage '
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where octet_length(`test`.`t1`.`a`) = 30 + rand() and `test`.`t1`.`a` = ' garbage '
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP);;
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000';
a
2001-01-01 00:00:00
SELECT * FROM t1 WHERE LENGTH(a)=19;
a
2001-01-01 00:00:00
2001-01-01 00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
a
2001-01-01 00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	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()
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP(6));;
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000';
a
2001-01-01 00:00:00.000000
SELECT * FROM t1 WHERE LENGTH(a)=26;
a
2001-01-01 00:00:00.000000
2001-01-01 00:00:01.000000
SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
a
2001-01-01 00:00:00.000000
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	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()
DROP TABLE t1;
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
CREATE TABLE t1 (a TIMESTAMP);;
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
SELECT * FROM t1 WHERE a=TIME'00:00:00';
a
2001-01-01 00:00:00
SELECT * FROM t1 WHERE LENGTH(a)=19;
a
2001-01-01 00:00:00
2001-01-01 00:00:01
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00';
a
2001-01-01 00:00:00
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	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()
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.3 tests
#
#
# MDEV-11333     MDEV-11333 Expect "Impossible where condition" for WHERE timestamp_field>=DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR)
#
SELECT DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR);
DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
INSERT INTO t1 VALUES('2001-01-01'),('2002-02-02'),('2003-03-03');
EXPLAIN SELECT * FROM t1 WHERE a >= DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
Warnings:
Warning	1441	Datetime function: datetime field overflow
EXPLAIN SELECT * FROM t1 WHERE a >= COALESCE(DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
Warnings:
Warning	1441	Datetime function: datetime field overflow
DROP TABLE t1;
#
# MDEV-11482 Incorrect result for (time_expr BETWEEN timestamp_exp1 AND timestamp_expr2)
#
SET @@sql_mode=DEFAULT;
SET @@timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00','2001-01-01 23:59:59');
SELECT * FROM t1 WHERE TIME'10:20:30' BETWEEN a and b;
a	b
2001-01-01 00:00:00	2001-01-01 23:59:59
DROP TABLE t1;
SET @@timestamp=DEFAULT;
#
# MDEV-12582 Wrong data type for CREATE..SELECT MAX(COALESCE(timestamp_column))
#
CREATE TABLE t1 (a TIMESTAMP);
CREATE TABLE t2 AS SELECT
MAX(a),
COALESCE(a),
COALESCE(MAX(a)),
MAX(COALESCE(a))
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `MAX(a)` timestamp NULL DEFAULT NULL,
  `COALESCE(a)` timestamp NULL DEFAULT NULL,
  `COALESCE(MAX(a))` timestamp NULL DEFAULT NULL,
  `MAX(COALESCE(a))` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
#
# End of 10.3 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning
#
CREATE TABLE t1 (b BIT(20));
CREATE TABLE t2 (t TIMESTAMP);
INSERT IGNORE INTO t1 VALUES (b'000001001100000');
INSERT INTO t2 SELECT * FROM t1;
DROP TABLE t1, t2;
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 SELECT CAST(20010101 AS UNSIGNED);
DROP TABLE t1;
#
# MDEV-17928 Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits
#
SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
CREATE PROCEDURE p1()
BEGIN
DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
DECLARE dt10 DATETIME(1) DEFAULT NOW();
DECLARE dt16 DATETIME(1) DEFAULT NOW(6);
DECLARE vts10 VARCHAR(32) DEFAULT ts10;
DECLARE vts16 VARCHAR(32) DEFAULT ts16;
DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
DECLARE vdt16 VARCHAR(32) DEFAULT dt16;
DECLARE tts10 TEXT(32) DEFAULT ts10;
DECLARE tts16 TEXT(32) DEFAULT ts16;
DECLARE tdt10 TEXT(32) DEFAULT dt10;
DECLARE tdt16 TEXT(32) DEFAULT dt16;
SELECT vts10, vts16, vdt10, vdt16;
SELECT tts10, tts16, tdt10, tdt16;
END;
$$
CALL p1;
vts10	2001-01-01 10:20:30.0
vts16	2001-01-01 10:20:30.1
vdt10	2001-01-01 10:20:30.0
vdt16	2001-01-01 10:20:30.1
tts10	2001-01-01 10:20:30.0
tts16	2001-01-01 10:20:30.1
tdt10	2001-01-01 10:20:30.0
tdt16	2001-01-01 10:20:30.1
DROP PROCEDURE p1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# MDEV-13995 MAX(timestamp) returns a wrong result near DST change
#
# Testing Item_func_rollup_const::val_native()
# There is a bug in the below output (MDEV-16612)
# Please remove this comment when MDEV-16612 is fixed and results are re-recorded
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1),(2);
BEGIN NOT ATOMIC
DECLARE v TIMESTAMP DEFAULT '2001-01-01 10:20:30'; -- "v" will be wrapped into Item_func_rollup_const
SELECT id, v AS v, COUNT(*) FROM t1 GROUP BY id,v WITH ROLLUP;
END;
$$
id	v	COUNT(*)
1	2001-01-01 10:20:30	1
1	2001-01-01 10:20:30	1
2	2001-01-01 10:20:30	1
2	2001-01-01 10:20:30	1
NULL	2001-01-01 10:20:30	2
DROP TABLE t1;
#
# Testing Type_handler_timestamp_common::Item_save_in_field()
# "txt" is expected to have three fractional digits
SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
CREATE TABLE t1 (ts1 TIMESTAMP(1) NOT NULL, ts2 TIMESTAMP(3) NOT NULL, txt TEXT);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00',COALESCE(ts1,ts2));
INSERT INTO t1 VALUES (NOW(),NOW(),COALESCE(ts1,ts2));
INSERT INTO t1 VALUES (NOW(1),NOW(3),COALESCE(ts1,ts2));
SELECT * FROM t1;
ts1	ts2	txt
0000-00-00 00:00:00.0	0000-00-00 00:00:00.000	0000-00-00 00:00:00.000
2001-01-01 10:20:30.0	2001-01-01 10:20:30.000	2001-01-01 10:20:30.000
2001-01-01 10:20:30.1	2001-01-01 10:20:30.123	2001-01-01 10:20:30.100
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# Testing Field_timestamp::store_native
#
SET sql_mode='';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00','0000-00-00 00:00:00');
SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
UPDATE t1 SET a=b;
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1
UPDATE t1 SET a=COALESCE(b);
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t1`.`a` at row 1
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY
#
CREATE TABLE t1 (a INT, b TIMESTAMP) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2018-06-19 00:00:00');
SELECT NULLIF(b, 'N/A') AS f, MAX(a) FROM t1 GROUP BY f;
f	MAX(a)
2018-06-19 00:00:00	1
Warnings:
Warning	1292	Truncated incorrect datetime value: 'N/A'
DROP TABLE t1;
#
# MDEV-17972 Assertion `is_valid_value_slow()' failed in Datetime::Datetime
#
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP(6)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
FLUSH TABLES;
MYD
FF77777777FFFFFF
SELECT a, CAST(a AS DATETIME) AS dt0, CAST(a AS DATETIME(6)) AS dt6 FROM t1;
a	dt0	dt6
2033-07-07 03:01:11.999999	2033-07-07 03:01:11	2033-07-07 03:01:11.999999
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# 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
#
CREATE TABLE t1 (t TIMESTAMP);
INSERT INTO t1 () VALUES (),();
SELECT IF(0,t,NULL) AS f FROM t1 GROUP BY 'foo';
f
NULL
DROP TABLE t1;
#
# MDEV-18145 Assertion `0' failed in Item::val_native upon SELECT subquery with timestamp
#
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2);
CREATE TABLE t3 (pk INT PRIMARY KEY, b TIMESTAMP) ENGINE=MyISAM;
SELECT ( SELECT b FROM t1 LIMIT 1 ) AS sq FROM t2 LEFT JOIN t3 USING (pk);
sq
NULL
NULL
DROP TABLE t1, t2, t3;
#
# MDEV-18447 Assertion `!is_zero_datetime()' failed in Timestamp_or_zero_datetime::tv
#
CREATE TABLE t1 (a TIMESTAMP DEFAULT 0, b TIMESTAMP DEFAULT 0, c TIME DEFAULT 0);
INSERT INTO t1 VALUES (0,0,0);
SELECT c IN (GREATEST(a,b)) FROM t1;
c IN (GREATEST(a,b))
0
DROP TABLE t1;
#
# MDEV-17969 Assertion `name' failed in THD::push_warning_truncated_value_for_field
#
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('2018-01-01'),('2019-01-01');
SET SESSION SQL_MODE= 'STRICT_ALL_TABLES,NO_ZERO_DATE';
CREATE TABLE t2 SELECT 1 AS f FROM t1 GROUP BY FROM_DAYS(d);
ERROR 22007: Truncated incorrect date value: '0000-00-00'
DROP TABLE t1;
#
# MDEV-19124 Assertion `0' failed in Item::val_native
#
CREATE TABLE t1 (d1 TIMESTAMP(5));
INSERT INTO t1 VALUES ('2018-10-14 15:31:01');
SELECT LEAD(d1,1) OVER(ORDER BY d1) FROM t1;
LEAD(d1,1) OVER(ORDER BY d1)
NULL
SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1;
LAG(d1,1) OVER(ORDER BY d1)
NULL
INSERT INTO t1 VALUES ('2018-10-14 15:31:02');
INSERT INTO t1 VALUES ('2018-10-14 15:31:03');
SELECT LEAD(d1,1) OVER(ORDER BY d1) FROM t1;
LEAD(d1,1) OVER(ORDER BY d1)
2018-10-14 15:31:02.00000
2018-10-14 15:31:03.00000
NULL
SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1;
LAG(d1,1) OVER(ORDER BY d1)
NULL
2018-10-14 15:31:01.00000
2018-10-14 15:31:02.00000
DROP TABLE t1;
#
# MDEV-18240 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed
#
CREATE TABLE t1 (c1 timestamp);
SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r'));
k1
Warnings:
Warning	1292	Truncated incorrect datetime value: 'r'
SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r');
c1
Warnings:
Warning	1292	Truncated incorrect datetime value: 'r'
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp);
INSERT INTO t1 VALUES ('2010-01-01 00:00:00');
SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT '2010-01-01 10:00:00' UNION SELECT '2001-01-01 10:00:01');
c1
SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT '2000-01-01 10:00:00' UNION SELECT '2000-01-01 10:00:01');
c1
2010-01-01 00:00:00
DROP TABLE t1;
#
# MDEV-18595 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed / Predicant_to_list_comparator::cmp_arg
#
CREATE TABLE t1 (t TIMESTAMP DEFAULT '1971-01-01 00:00:00', f INT);
INSERT INTO t1 VALUES ('1978-05-25 22:25:03',1),('2000-01-01 00:00:00',2);
SELECT * FROM t1 WHERE f IN (DEFAULT(t),1);
t	f
1978-05-25 22:25:03	1
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`f` at row 1
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`f` at row 2
DROP TABLE t1;
#
# MDEV-18503 Assertion `native.length() == binlen' failed in Type_handler_timestamp_common::make_sort_key
#
SET sql_mode='';
CREATE TABLE t1 (a TIMESTAMP(3) DEFAULT 0, b TIMESTAMP);
INSERT INTO t1 (b) VALUES ('2012-12-12 12:12:12'),('1988-08-26 12:12:12');
SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1;
f
1988-08-26 12:12:12.000
2012-12-12 12:12:12.000
SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1 DESC;
f
2012-12-12 12:12:12.000
1988-08-26 12:12:12.000
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# MDEV-20417 Assertion `(m_ptr == __null) == item->null_value' failed in VDec::VDec(Item*)
#
CREATE TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES ('2001-01-01','2002-01-01'),('2003-01-01','2004-01-01');
SELECT * FROM t1 WHERE DEFAULT(b) - a;
a	b
Warnings:
Warning	1916	Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated
Warning	1916	Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated
SELECT LEFT('', DEFAULT(b)-a) FROM t1;
LEFT('', DEFAULT(b)-a)


Warnings:
Warning	1916	Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated
Warning	1916	Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated
DROP TABLE t1;
CREATE  TABLE t1 (a TIMESTAMP(4), b TIMESTAMP DEFAULT 0) ENGINE=MyISAM;
INSERT IGNORE INTO t1 (a) VALUES ('2001-01-01'),('2003-01-01');
SELECT * FROM t1 WHERE (SELECT MIN(b) FROM t1) - a;
a	b
Warnings:
Warning	1916	Got overflow when converting '-20010101000000' to UNSIGNED INT. Value truncated
Warning	1916	Got overflow when converting '-20030101000000' to UNSIGNED INT. Value truncated
SELECT (SELECT MIN(b) FROM t1) - a FROM t1;
(SELECT MIN(b) FROM t1) - a
-20010101000000.0000
-20030101000000.0000
DROP TABLE t1;
#
# MDEV-22734 Assertion `mon > 0 && mon < 13' failed in sec_since_epoch
#
SET time_zone="-02:00";
CREATE TABLE t1(c TIMESTAMP KEY);
SELECT * FROM t1 WHERE c='2010-00-01 00:00:00';
c
Warnings:
Warning	1292	Incorrect datetime value: '2010-00-01 00:00:00'
DROP TABLE t1;
#
# MDEV-22854 Garbage returned with SELECT CASE..DEFAULT(timestamp_field_with_now_as_default)
#
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.456789');
CREATE TABLE t1 (a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES ('2019-02-23 11:31:04'),('2023-02-09 00:00:00');
SELECT CASE WHEN a THEN DEFAULT(a) END FROM t1;
CASE WHEN a THEN DEFAULT(a) END
2001-01-01 10:20:30.456
2001-01-01 10:20:30.456
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# End of 10.4 tests
#