Create 'main' test directory and move 't' and 'r' there

1 files changed, 608 insertions, 0 deletions

diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test
new file mode 100644
index 00000000000..6d81a86331a
--- /dev/null
+++ b/mysql-test/main/type_timestamp.test
@@ -0,0 +1,608 @@
+#
+# Test timestamp
+#
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+# Set timezone to GMT-3, to make it possible to use "interval 3 hour"
+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;
+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";
+UPDATE t1 SET value="my value" WHERE id="myKey";
+SELECT stamp FROM t1 WHERE id="myKey";
+UPDATE t1 SET id="myKey" WHERE value="my value";
+SELECT stamp FROM t1 WHERE id="myKey";
+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;
+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;
+truncate table t1;
+insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
+select ix+0 from t1;
+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);
+# The following will get you an different answer on 64 bit machines
+#INSERT INTO t1 VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
+SELECT * FROM t1;
+drop table t1;
+
+#
+# Let us check if we properly treat wrong datetimes and produce proper warnings
+# (for both strings and numbers)
+#
+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);
+select ix+0 from t1;
+truncate table t1;
+insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
+select ix+0 from t1;
+truncate table t1;
+insert ignore into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
+select ix+0 from t1;
+drop table t1;
+
+#
+# Test for TIMESTAMP column with default now() and on update now() clauses
+#
+
+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;
+
+# Let us test TIMESTAMP auto-update behaviour
+# Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and
+# behaviour of DEFAULT literal for such fields
+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;
+show create table t1;
+show columns from t1;
+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;
+show create table t1;
+show columns from t1;
+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;
+SET TIMESTAMP=1000000005;
+update t1 set t2=now();
+SET TIMESTAMP=1000000005;
+insert into t1 (t1) values (default);
+select * from t1;
+show create table t1;
+show columns from t1;
+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;
+SET TIMESTAMP=1000000007;
+update t1 set t2=now();
+SET TIMESTAMP=1000000007;
+insert into t1 (t1) values (default);
+select * from t1;
+show create table t1;
+show columns from t1;
+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;
+SET TIMESTAMP=1000000008;
+update t1 set t2=now();
+SET TIMESTAMP=1000000008;
+insert into t1 (t1,t3) values (default, default);
+select * from t1;
+show create table t1;
+show columns from t1;
+drop table t1;
+
+# Let us test if CURRENT_TIMESTAMP also works well as default value
+# (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just
+# for demonstartion.)
+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;
+SET TIMESTAMP=1000000010;
+update t1 set t2=now();
+SET TIMESTAMP=1000000011;
+insert into t1 (t1) values (default);
+select * from t1;
+show create table t1;
+show columns from t1;
+truncate table t1;
+
+# 
+# Let us test some cases when auto-set should be disabled or influence
+# on server behavior in some other way.
+#
+
+# Update statement that explicitly sets field should not auto-set it. 
+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;
+# The same for multi updates
+update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
+select * from t1;
+drop table t1;
+
+# Now let us test replace it should behave exactly like delete+insert
+# Case where optimization is possible DEFAULT = ON UPDATE
+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;
+drop table t1;
+# Case in which there should not be optimisation
+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;
+drop table t1;
+# Other similar case
+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;
+drop table t1;
+
+# Let us test alter now
+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;
+drop table t1;
+
+#
+# Test for TIMESTAMP columns which are able to store NULLs
+#
+
+# Unlike for default TIMESTAMP fields we don't interpret first field
+# in this table as TIMESTAMP with DEFAULT NOW() ON UPDATE NOW() properties.
+create table t1 (a timestamp null, b timestamp null);
+show create table t1;
+insert into t1 values (NULL, NULL);
+SET TIMESTAMP=1000000017;
+insert into t1 values ();
+select * from t1;
+drop table t1;
+
+# But explicit auto-set properties still should be OK.
+create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
+show create table t1;
+insert into t1 values (NULL, NULL);
+SET TIMESTAMP=1000000018;
+insert into t1 values ();
+select * from t1;
+drop table t1;
+
+# It is also OK to specify NULL as default explicitly for such fields.
+# This is also a test for bug #2464, DEFAULT keyword in INSERT statement
+# should return default value for column.
+
+create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
+show create table t1;
+insert into t1 values (NULL, NULL);
+insert into t1 values (DEFAULT, DEFAULT);
+select * from t1;
+drop table t1;
+
+#
+# Let us test behavior of ALTER TABLE when it converts columns 
+# containing NULL to TIMESTAMP columns.
+#
+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;
+drop table t1;
+
+#
+# Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
+# using GROUP BY in @@new=1 mode.
+#
+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;
+drop table t1;
+
+#
+# Test for bug #7418 "TIMESTAMP not always converted to DATETIME in MAXDB
+# mode". TIMESTAMP columns should be converted DATETIME columns in MAXDB
+# mode regardless of whether a display width is given.
+#
+set sql_mode='maxdb';
+create table t1 (a timestamp, b timestamp(5));
+show create table t1;
+# restore default mode
+set sql_mode='';
+drop table t1;
+
+#
+# Bug#7806 - insert on duplicate key and auto-update of timestamp
+#
+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;
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+insert into t1 (a) values (4);
+select * from t1;
+update t1 set c = '2004-04-04 04:04:04' where a = 4;
+select * from t1;
+insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
+select * from t1;
+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;
+drop table t1;
+
+--echo End of 4.1 tests
+
+# Restore timezone to default
+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;
+select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
+drop table t1;
+
+#
+# Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME()
+#
+
+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;
+DROP TABLE t1;
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # Bug #55779: select does not work properly in mysql server
+--echo #             Version "5.1.42 SUSE MySQL RPM"
+--echo #
+
+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;
+SELECT a FROM t1 WHERE a >= '20000101000000';
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#50774: failed to get the correct resultset when timestamp values 
+--echo # are appended with .0
+--echo #
+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';
+SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a;
+SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0';
+SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a;
+
+--replace_column 1 x 2 x 3 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
+SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
+
+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' );
+
+--echo # The bug would cause the range optimizer's comparison to use an open
+--echo # interval here. This reveals itself only in the number of reads 
+--echo # performed.
+FLUSH STATUS;
+--replace_column 1 x 2 x 3 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
+SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
+SHOW STATUS LIKE 'Handler_read_next';
+
+DROP TABLE t1, t2;
+
+--echo End of 5.1 tests
+
+--echo #
+--echo # lp:923429 Crash in decimal_cmp on using UNIX_TIMESTAMP with a wrongly formatted timestamp
+--echo #
+SELECT UNIX_TIMESTAMP('abc') > 0;
+SELECT UNIX_TIMESTAMP('abc');
+
+--echo
+--echo Bug#50888 valgrind warnings in Field_timestamp::val_str
+--echo
+
+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;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+--echo End of Bug#50888
+
+--echo #
+--echo # Bug59330: Incorrect result when comparing an aggregate
+--echo #           function with TIMESTAMP
+--echo #
+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;
+SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1;
+SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-9413 "datetime >= coalesce(c1(NULL))" doesn't return expected NULL
+--echo #
+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;
+DROP TABLE t1;
+
+--echo End of 5.5 tests
+
+--echo #
+--echo # MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL
+--echo #
+
+SET time_zone='+02:00';
+create table t1(value timestamp not null);
+set @a:=0;
+delimiter //;
+create function f1 () returns timestamp
+begin
+  set @a = @a + 1;
+  return NULL;
+end//
+delimiter ;//
+set timestamp=12340;
+insert t1 values (f1());
+select @a, value from t1;
+set timestamp=12350;
+update t1 set value = f1();
+select @a, value from t1;
+drop table t1;
+drop function f1;
+set timestamp=0;
+
+# Verify no regressions to TIMESTAMP NULL
+create table t1(value timestamp null);
+set @a:=0;
+delimiter //;
+create function f1 () returns timestamp
+begin
+  set @a = @a + 1;
+  return NULL;
+end//
+delimiter ;//
+set timestamp=12340;
+insert t1 values (f1());
+select @a, value from t1;
+set timestamp=12350;
+update t1 set value = f1();
+select @a, value from t1;
+drop table t1;
+drop function f1;
+set timestamp=0;
+SET time_zone=DEFAULT;
+
+--echo #
+--echo # MDEV-7778 impossible create copy of table, if table contain default value for timestamp field
+--echo #
+
+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;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+SET sql_mode=DEFAULT;
+
+--echo #
+--echo # MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT
+--echo #
+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;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+
+--echo End of 10.0 tests
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-7831 Bad warning for DATE_ADD(timestamp_column, INTERVAL 10 SECOND) 
+--echo #
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+SELECT DATE_ADD(a, INTERVAL 10 SECOND) FROM t1;
+DROP TABLE t1;
+
+
+let type=TIMESTAMP;
+let defval='0000-00-00 00:00:00';
+--source include/type_temporal_zero_default.inc
+
+--echo #
+--echo # MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
+--echo #
+--let $TYPE=TIMESTAMP
+--source include/equal_fields_propagation_datetime.inc
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-11333     MDEV-11333 Expect "Impossible where condition" for WHERE timestamp_field>=DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR)
+--echo #
+
+SELECT DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR);
+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);
+EXPLAIN SELECT * FROM t1 WHERE a >= COALESCE(DATE_ADD(TIMESTAMP'9999-01-01 00:00:00',INTERVAL 1000 YEAR));
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11482 Incorrect result for (time_expr BETWEEN timestamp_exp1 AND timestamp_expr2)
+--echo #
+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;
+DROP TABLE t1;
+SET @@timestamp=DEFAULT;
+
+--echo #
+--echo # MDEV-12582 Wrong data type for CREATE..SELECT MAX(COALESCE(timestamp_column))
+--echo #
+
+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;
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.3 tests
+--echo #