diff options
Diffstat (limited to 'mysql-test/main/type_timestamp.result')
| -rw-r--r-- | mysql-test/main/type_timestamp.result | 1016 |
1 files changed, 1016 insertions, 0 deletions
diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result new file mode 100644 index 00000000000..b0405bc4ad7 --- /dev/null +++ b/mysql-test/main/type_timestamp.result @@ -0,0 +1,1016 @@ +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')); +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' ); +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 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 '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 '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 ' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00' and <cache>(octet_length(TIMESTAMP'0000-00-00 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 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 +# |