#
# Test timestamp
#

--source include/word_size.inc

# 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: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';
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 ) );

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.
--disable_ps2_protocol
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';
--enable_ps2_protocol

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 #

--echo #
--echo # Start of 10.4 tests
--echo #

--echo #
--echo # MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning
--echo #

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;


--echo #
--echo # MDEV-17928 Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits
--echo #

SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
DELIMITER $$;
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;
$$
DELIMITER ;$$
--vertical_results
CALL p1;
--horizontal_results
DROP PROCEDURE p1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;

--echo #
--echo # MDEV-13995 MAX(timestamp) returns a wrong result near DST change
--echo #

--echo # Testing Item_func_rollup_const::val_native()

--echo # There is a bug in the below output (MDEV-16612)
--echo # 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);
DELIMITER $$;
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;
$$
DELIMITER ;$$
DROP TABLE t1;

--echo #
--echo # Testing Type_handler_timestamp_common::Item_save_in_field()
--echo # "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;
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;

--echo #
--echo # Testing Field_timestamp::store_native
--echo #

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';
--error ER_TRUNCATED_WRONG_VALUE
UPDATE t1 SET a=b;
--error ER_TRUNCATED_WRONG_VALUE
UPDATE t1 SET a=COALESCE(b);
DROP TABLE t1;
SET sql_mode=DEFAULT;

--echo #
--echo # MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY
--echo #

#double warning
--disable_view_protocol
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;
DROP TABLE t1;
--enable_view_protocol

--echo #
--echo # MDEV-17972 Assertion `is_valid_value_slow()' failed in Datetime::Datetime
--echo #

let $MYSQLD_DATADIR= `select @@datadir`;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
FLUSH TABLES;
--remove_file $MYSQLD_DATADIR/test/t1.MYD
--disable_query_log
# Write a data file with one record:
# 0xFF       - record flags
# 0x77777777 - TIMESTAMP integer part
# 0xFFFFFF   - TIMESTAMP bad fractional part
--eval SELECT CONCAT(0xFF,0x77777777,0xFFFFFF) INTO OUTFILE '$MYSQLD_DATADIR/test/t1.MYD' FIELDS TERMINATED BY '' ESCAPED BY '' LINES TERMINATED BY ''
--eval SELECT HEX(LOAD_FILE('$MYSQLD_DATADIR/test/t1.MYD')) AS MYD
--enable_query_log
SELECT a, CAST(a AS DATETIME) AS dt0, CAST(a AS DATETIME(6)) AS dt6 FROM t1;
DROP TABLE t1;
SET time_zone=DEFAULT;

--echo #
--echo # 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
--echo #

CREATE TABLE t1 (t TIMESTAMP);
INSERT INTO t1 () VALUES (),();
SELECT IF(0,t,NULL) AS f FROM t1 GROUP BY 'foo';
DROP TABLE t1;

--echo #
--echo # MDEV-18145 Assertion `0' failed in Item::val_native upon SELECT subquery with timestamp
--echo #

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);
DROP TABLE t1, t2, t3;

--echo #
--echo # MDEV-18447 Assertion `!is_zero_datetime()' failed in Timestamp_or_zero_datetime::tv
--echo #

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;
DROP TABLE t1;


--echo #
--echo # MDEV-17969 Assertion `name' failed in THD::push_warning_truncated_value_for_field
--echo #

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';
--error ER_TRUNCATED_WRONG_VALUE
CREATE TABLE t2 SELECT 1 AS f FROM t1 GROUP BY FROM_DAYS(d);
DROP TABLE t1;


--echo #
--echo # MDEV-19124 Assertion `0' failed in Item::val_native
--echo #

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;
SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1;
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;
SELECT LAG(d1,1) OVER(ORDER BY d1) FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-18240 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed
--echo #

CREATE TABLE t1 (c1 timestamp);
SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r'));
SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r');
SELECT * FROM t1 HAVING MIN(t1.c1) > 0;
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');
SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT '2000-01-01 10:00:00' UNION SELECT '2000-01-01 10:00:01');
DROP TABLE t1;

--echo #
--echo # MDEV-18595 Assertion `0' failed in Item_cache_timestamp::val_datetime_packed / Predicant_to_list_comparator::cmp_arg
--echo #

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);
DROP TABLE t1;


--echo #
--echo # MDEV-18503 Assertion `native.length() == binlen' failed in Type_handler_timestamp_common::make_sort_key
--echo #

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;
SELECT GREATEST(a,b) AS f FROM t1 ORDER BY 1 DESC;
DROP TABLE t1;
SET sql_mode=DEFAULT;


--echo #
--echo # MDEV-20417 Assertion `(m_ptr == __null) == item->null_value' failed in VDec::VDec(Item*)
--echo #

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;
SELECT LEFT('', DEFAULT(b)-a) FROM t1;
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;
#Enable after fix MDEV-31730
--disable_cursor_protocol
SELECT (SELECT MIN(b) FROM t1) - a FROM t1;
--enable_cursor_protocol
DROP TABLE t1;

--echo #
--echo # MDEV-22734 Assertion `mon > 0 && mon < 13' failed in sec_since_epoch
--echo #
SET time_zone="-02:00";
CREATE TABLE t1(c TIMESTAMP KEY);
SELECT * FROM t1 WHERE c='2010-00-01 00:00:00';
DROP TABLE t1;


--echo #
--echo # MDEV-22854 Garbage returned with SELECT CASE..DEFAULT(timestamp_field_with_now_as_default)
--echo #

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;
DROP TABLE t1;
SET timestamp=DEFAULT;

--echo #
--echo # MDEV-27653 long uniques don't work with unicode collations
--echo #

CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
SET time_zone='+00:00';
INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
SET time_zone='+01:00';
--error ER_DUP_ENTRY
INSERT INTO t1 SELECT MAX(a) FROM t1;
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
SET time_zone='+00:00';
INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
SET time_zone='+01:00';
CHECK TABLE t1;
DROP TABLE t1;
SET time_zone=DEFAULT;

--echo #
--echo # End of 10.4 tests
--echo #

--echo #
--echo # Start of 10.6 tests
--echo #

--echo #
--echo # MDEV-32148 Inefficient WHERE timestamp_column=datetime_expr
--echo #

SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
INSERT INTO t1 VALUES ('2001-01-01 10:20:30'), ('2001-01-01 10:20:31');

--echo #
--echo # Comparison predicates: Bad TIMESTAMP values preserve DATETIME comparison
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-00 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2040-01-01 10:20:30';

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='1001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-00 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2040-01-01 10:20:30';

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10010101102030;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010100000000;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20400101102030;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10010101102030e0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010100000000e0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20400101102030e0;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10010101102030.0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010100000000.0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20400101102030.0;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=DATE_ADD('2020-01-01 10:20:30', INTERVAL 30 YEAR);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(NULL, a)=DATE_ADD('2020-01-01 10:20:30', INTERVAL 30 YEAR);

--echo #
--echo # Comparison predicates: Good TIMESTAMP values switch to TIMESTAMP comparison
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30';

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-00 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 10:20:30';

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010101102030;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=0e0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010101102030e0;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=0.0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=20010101102030.0;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=DATE_ADD('2001-01-01 10:20:00', INTERVAL 30 SECOND);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(NULL, a)=DATE_ADD('2001-01-01 10:20:00', INTERVAL 30 SECOND);

--echo #
--echo # Corner cases: DATETIME values before the supported optimization range
--echo # FROM_UNIXTIME(0)..FROM_UNIXTIME(24*3600-1)
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 23:59:59';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 23:59:59.999999';

# Truncation of the 7th flactional digit produces a NOTE only without --ps
--disable_ps_protocol
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 23:59:59.9999999';
--enable_ps_protocol
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=FROM_UNIXTIME(24*3600-1);

--echo #
--echo # Corner cases: DATETIME values inside the supported optimization range:
--echo # FROM_UNIXTIME(24*3600) .. FROM_UNIXTIME(0x7FFFFFFF-24*3600)
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST(FROM_UNIXTIME(24*3600) AS DATETIME);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-02 00:00:00';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2038-01-18 03:14:07';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST(FROM_UNIXTIME(0x7FFFFFFF-24*3600) AS DATETIME);

--echo #
--echo # Corner cases: DATETIME values after the supported optimization range
--echo # FROM_UNIXTIME(0x7FFFFFFF-24*3600+1) .. FROM_UNIXTIME(0x7FFFFFFF)
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST(FROM_UNIXTIME(0x7FFFFFFF-24*3600+1) AS DATETIME);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2038-01-18 03:14:08';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2038-01-19 03:14:07';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST(FROM_UNIXTIME(0x7FFFFFFF) AS DATETIME);

--echo #
--echo # Corner cases: rounding
--echo #

SET sql_mode=TIME_ROUND_FRACTIONAL;

--echo #
--echo # Not optimized (before the supported range)
--echo #
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 23:59:59.999999';

--echo #
--echo # Optimized
--echo #
--disable_ps_protocol
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'1970-01-01 23:59:59.9999999';
--enable_ps_protocol
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2038-01-18 03:14:07.999999';

--echo #
--echo # Not optimized (after the supported range)
--echo #
--disable_ps_protocol
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'2038-01-18 03:14:07.9999999';
--enable_ps_protocol

SET sql_mode=DEFAULT;

--echo #
--echo # NULLIF: Bad TIMESTAMP values preserve DATETIME comparison
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,TIMESTAMP'1001-01-01 10:20:30');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,TIMESTAMP'2001-01-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,TIMESTAMP'2040-01-01 10:20:30');

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,'1001-01-01 10:20:30');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,'2001-01-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,'2040-01-01 10:20:30');

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,10010101102030);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010100000000);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20400101102030);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,10010101102030e0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010100000000e0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20400101102030e0);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,10010101102030.0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010100000000.0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20400101102030.0);

--echo #
--echo # NULLIF: Good TIMESTAMP values switch to TIMESTAMP comparison
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,TIMESTAMP'0000-00-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,TIMESTAMP'2001-01-01 10:20:30');

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,'0000-00-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,'2001-01-01 10:20:30');

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010101102030);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,0e0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010101102030e0);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,0.0);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NULLIF(a,20010101102030.0);


--echo #
--echo # Subqueries with bad TIMESTAMP values
--echo # It's not clear from the output if the comparison is done as DATETIME or TIMESTAMP
--echo #

CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 VALUES ('1001-01-01 10:20:30');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t2);
DROP TABLE t2;

CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 VALUES ('2001-00-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t2);
DROP TABLE t2;

CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 VALUES ('2040-01-01 10:20:30');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t2);
DROP TABLE t2;

--echo #
--echo # Subqueries with good TIMESTAMP values
--echo # It's not clear from the output if the comparison is done as DATETIME or TIMESTAMP
--echo #

CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t2);
DROP TABLE t2;

CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 VALUES ('2001-01-01 10:20:30');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t2);
DROP TABLE t2;

--echo #
--echo # Trivial equality elimination
--echo # Covers Type_handler_timestamp_common::Item_const_eq()
--echo #

EXPLAIN EXTENDED SELECT * FROM t1
WHERE IF(a='2001-01-01 00:00:00',1,0)=IF(a='2001-01-01 00:00:00',1,0);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a<=>?)=(a<=>?)'
    USING '2001-01-01 00:00:00', '2001-01-01 00:00:00';


--echo #
--echo # Equal field propagation: Good TIMESTAMP values switch to TIMESTAMP comparison
--echo #

EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19 AND a='2001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=20010101102030;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=20010101102030e0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=20010101102030.0;

EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=TIMESTAMP'2001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a='2001-01-01 10:20:30';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=20010101102030;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=20010101102030e0;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=20010101102030.0;

DROP TABLE t1;

SET time_zone=DEFAULT;

--echo #
--echo # End of 10.6 tests
--echo #

--echo #
--echo # MDEV-29225 make explicit_defaults_for_timestamps SESSION variable
--echo #

set explicit_defaults_for_timestamp=OFF;
create table t1 (f1 timestamp, f2 timestamp);
show create table t1;
drop table t1;
set explicit_defaults_for_timestamp=ON;
create table t1 (f1 timestamp, f2 timestamp);
show create table t1;
drop table t1;

--echo #
--echo # MDEV-34069 Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp
--echo #

SET sql_mode='';
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
SELECT * FROM t1;
CREATE TABLE t2 (a DATETIME);
INSERT INTO t2 SELECT a FROM t1;
SELECT * FROM t2;
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
INSERT INTO t2 SELECT a FROM t1;
SELECT * FROM t2;
DROP TABLE t2, t1;
SET time_zone=DEFAULT;
SET sql_mode=DEFAULT;

--echo #
--echo # MDEV-34061 unix_timestamp(coalesce(timestamp_column)) returns NULL on '1970-01-01 00:00:00.000001'
--echo #

SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP(6) NULL);
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.000001');
SELECT unix_timestamp(a) AS c1, unix_timestamp(coalesce(a)) AS c2 FROM t1;
DROP TABLE t1;
SET time_zone=DEFAULT;

--echo #
--echo # MDEV-34088 The TIMESTAMP value of '1970-01-01 00:00:00' can be indirectly inserted in strict mode
--echo #

SET sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('1970-01-01 00:00:00');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.1');
CREATE TABLE t2 (a TIMESTAMP(1)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('1970-01-01 00:00:00.1');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 SELECT a FROM t2;
DROP TABLE t2;
DROP TABLE t1;
SET sql_mode=DEFAULT;


SET sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('1970-01-01 00:00:00');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.1');
CREATE TABLE t2 (a TIMESTAMP(1)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('1970-01-01 00:00:00.1');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 SELECT a FROM t2;
DROP TABLE t2;
DROP TABLE t1;
SET sql_mode=DEFAULT;

SET sql_mode='';
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('1970-01-01 00:00:00');
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.1');
CREATE TABLE t2 (a TIMESTAMP(1)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('1970-01-01 00:00:00.1');
INSERT INTO t1 SELECT a FROM t2;
DROP TABLE t2;
SELECT * FROM t1;
DROP TABLE t1;
SET sql_mode=DEFAULT;

SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('1970-01-01 00:00:00');
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.1');
CREATE TABLE t2 (a TIMESTAMP(1)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('1970-01-01 00:00:00.1');
INSERT INTO t1 SELECT a FROM t2;
DROP TABLE t2;
SELECT * FROM t1;
DROP TABLE t1;
SET sql_mode=DEFAULT;

--echo #
--echo # End of 10.5 tests
--echo #

--echo #
--echo # MDEV-32203 Raise notes when an index cannot be used on data type mismatch
--echo #

SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col TIMESTAMP, KEY(indexed_col));
DELIMITER $$;
FOR i IN 1..31
DO
  INSERT INTO t1 VALUES (MAKEDATE(2023, i));
END FOR;
$$
DELIMITER ;$$
--source unusable_keys_literals.inc
--source unusable_keys_joins.inc
DROP TABLE t1;
SET note_verbosity=DEFAULT;

--echo # End of 10.6 tests


--echo #
--echo # Start of 11.3 tests
--echo #

--echo #
--echo # MDEV-33299 Assertion `(tm->tv_usec % (int) log_10_int[6 - dec]) == 0' failed in void my_timestamp_to_binary(const timeval*, uchar*, uint)
--echo #

CREATE TABLE t1(a TIMESTAMP,KEY(a));
SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23' ;
SELECT * FROM t1 WHERE YEAR(a) <= 2024;
DROP TABLE t1;

CREATE TABLE t1(a TIMESTAMP,KEY(a));
INSERT INTO t1 VALUES ('2023-12-31 23:59:59');
INSERT INTO t1 VALUES ('2024-01-22 10:20:30');
INSERT INTO t1 VALUES ('2024-01-23 10:20:30');
INSERT INTO t1 VALUES ('2024-01-23 23:59:59');
INSERT INTO t1 VALUES ('2024-01-24 00:00:00');
INSERT INTO t1 VALUES ('2024-12-31 23:59:59');
INSERT INTO t1 VALUES ('2025-01-01 00:00:00');
SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
SELECT * FROM t1 WHERE YEAR(a) <= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) <= 2024;
SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
SELECT * FROM t1 WHERE YEAR(a) >= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) >= 2024;
DROP TABLE t1;


CREATE TABLE t1(a TIMESTAMP(3),KEY(a));
INSERT INTO t1 VALUES ('2023-12-31 23:59:59.999');
INSERT INTO t1 VALUES ('2024-01-22 10:20:30.001');
INSERT INTO t1 VALUES ('2024-01-23 10:20:30.002');
INSERT INTO t1 VALUES ('2024-01-23 23:59:59.999');
INSERT INTO t1 VALUES ('2024-01-24 00:00:00.000');
INSERT INTO t1 VALUES ('2024-12-31 23:59:59.999');
INSERT INTO t1 VALUES ('2025-01-01 00:00:00.000');
SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
SELECT * FROM t1 WHERE YEAR(a) <= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) <= 2024;
SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
SELECT * FROM t1 WHERE YEAR(a) >= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) >= 2024;
DROP TABLE t1;


CREATE TABLE t1(a TIMESTAMP(6),KEY(a));
INSERT INTO t1 VALUES ('2023-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('2024-01-22 10:20:30.000001');
INSERT INTO t1 VALUES ('2024-01-23 10:20:30.000002');
INSERT INTO t1 VALUES ('2024-01-23 23:59:59.999999');
INSERT INTO t1 VALUES ('2024-01-24 00:00:00.000000');
INSERT INTO t1 VALUES ('2024-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('2025-01-01 00:00:00.000000');
SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) <= '2024-01-23';
SELECT * FROM t1 WHERE YEAR(a) <= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) <= 2024;
SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE DATE(a) >= '2024-01-24';
SELECT * FROM t1 WHERE YEAR(a) >= 2024;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE YEAR(a) >= 2024;
DROP TABLE t1;


--echo #
--echo # MDEV-34085 Server crash ASAN used-after-poison upon 2nd execution of PS with erroneous timestamp conversion
--echo #

CREATE TABLE t1 (a int, b timestamp);
INSERT INTO t1 VALUES (1,'2000-01-01'),(2,'2001-12-31');
PREPARE stmt FROM 'UPDATE t1 SET a = 0 WHERE 1 IN (SELECT b FROM t1)';
--error ER_TRUNCATED_WRONG_VALUE
EXECUTE stmt;
--error ER_TRUNCATED_WRONG_VALUE
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;

--echo # End of 11.3 tests

--echo #
--echo # MDEV-35677 Assertion `thd->is_error()' failed in virtual bool Field::sp_prepare_and_store_item(THD*, Item**)
--echo #

CREATE FUNCTION f() RETURNS INT DETERMINISTIC RETURN (SELECT a FROM t);
CREATE TABLE t (c TIMESTAMP);
# with view protocol it raises ER_VIEW_INVALID
--disable_view_protocol
--error ER_BAD_FIELD_ERROR
SELECT * FROM t WHERE c=f();
--enable_view_protocol
DROP TABLE t;
DROP FUNCTION f;

--echo # End of 11.4 tests

--echo #
--echo # MDEV-35250 Assertion `dec <= 6' failed in my_timestamp_binary_length
--echo #

SET time_zone='+00:00';
# Disable view protocol to workaround MDEV-29534
--disable_view_protocol
# Check after fix MDEV-31730
--disable_cursor_protocol
SELECT FROM_UNIXTIME('1922-02-10') AS c1, BIT_AND(1) AS c2;
SELECT FROM_UNIXTIME('1922') AS c1, BIT_AND(1) AS c2;
--enable_cursor_protocol
--enable_view_protocol
# Disable PS protocol to workaround CONC-739
--disable_ps_protocol
SELECT FROM_UNIXTIME('1922.1') AS c1, BIT_AND(1) AS c2;
SELECT FROM_UNIXTIME('1922.1') AS c1;
--enable_ps_protocol
SELECT * FROM (SELECT FROM_UNIXTIME('1') AS c1, BIT_AND(1) AS c2) t;
SELECT * FROM (SELECT FROM_UNIXTIME('1.1') AS c1, BIT_AND(1) AS c2) t;
CREATE TABLE t1 (a TEXT, b TEXT);
INSERT INTO t1 SELECT FROM_UNIXTIME('1') AS c1, BIT_AND(1) AS c2;
INSERT INTO t1 SELECT FROM_UNIXTIME('1.1') AS c1, BIT_AND(1) AS c2;
SELECT * FROM t1;
DROP TABLE t1;

SELECT 1 FROM (values(1),(2)) dt GROUP BY from_unixtime('a') WITH ROLLUP;

CREATE TABLE t1 (a TEXT);
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 ORDER BY from_unixtime(a);
DROP TABLE t1;

SET time_zone=DEFAULT;

--echo # End of 11.7 tests