#
# Test of SYSTEM time zone handling ( for my_system_gmt_sec()).
# This script must be run with TZ=CET
--source include/have_met_timezone.inc

# Initialization
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

# The following is because of daylight saving time
--replace_result CEST CET
show variables like "system_time_zone";

#
# Test unix timestamp
#
select @a:=FROM_UNIXTIME(1);
select unix_timestamp(@a);

#
# Test of some values, including some with daylight saving time
#

CREATE TABLE t1 (ts int);
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 03:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 02:59:59'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:00:00'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:59:59'));
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01'));

SELECT ts,from_unixtime(ts) FROM t1;
DROP TABLE t1;


#
# Test of warning for spring time-gap values for system time zone
#
CREATE TABLE t1 (ts timestamp);
INSERT IGNORE INTO t1 (ts) VALUES ('2003-03-30 01:59:59'),
                                  ('2003-03-30 02:59:59'),
                           ('2003-03-30 03:00:00');
DROP TABLE t1;

# End of 4.1 tests

#
# Bug #27970159: DUPLICATE ENTRY FOR KEY '<GROUP_KEY>' ERROR
#

CREATE TABLE t1 (c1 TIMESTAMP);
SET TIME_ZONE = '+00:00';
SET explicit_defaults_for_timestamp=OFF;

INSERT INTO t1 VALUES('2019-10-27 00:47:42'), ('2019-10-27 00:47:42');
INSERT INTO t1 VALUES('2019-10-27 01:47:42'), ('2019-10-27 01:47:42');

SET TIME_ZONE = 'SYSTEM';

SELECT DISTINCT c1 FROM t1;

--skip_if_hypergraph   # Chooses a different query plan, which causes different grouping.
SELECT COUNT(*) FROM t1 GROUP BY c1;

SET sql_mode='';
--skip_if_hypergraph   # Chooses a different query plan, which causes different grouping.
SELECT COUNT(*) FROM t1 GROUP BY c1;
SET sql_mode=DEFAULT;

#Cleanup
SET explicit_defaults_for_timestamp=ON;
DROP TABLE t1;

--echo #
--echo # WL#10828: TIMESTAMP/DATETIME VALUES CAN INCLUDE TIMEZONE DETAILS
--echo #

SET time_zone = '+01:00';

--echo # Test of the format

SELECT TIMESTAMP'2015-01-01 10:10:10+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.1+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.01+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.001+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.0001+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.00001+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.000001+05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.0000001+05:30';

SELECT TIMESTAMP'2015-01-01 10:10:10-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.1-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.01-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.001-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.0001-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.00001-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.000001-05:30';
SELECT TIMESTAMP'2015-01-01 10:10:10.0000001-05:30';

--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+5:30';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+5:030';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+005:30';

SELECT TIMESTAMP'2001-01-01 00:00:00+02:00';
SELECT TIMESTAMP( '2001-01-01 00:00:00+02:00' );

--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+05:300';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10.0+05:300';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10.0+05:30xxx';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10.0+25:30';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+05';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+05:';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+0';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+1';
--echo # No extra space allowed before +/-
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10 +05:30';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+14:01';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10-14:01';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+zx:00';
--error ER_WRONG_VALUE
SELECT TIMESTAMP'2015-01-01 10:10:10+00:00zx';


--echo #
--echo # The TIMESTAMP type.
--echo #
SET time_zone = '+02:00';

CREATE TABLE t1 ( a TIMESTAMP DEFAULT '1995-05-05 00:00:00+05:30' );
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
DROP TABLE t1;

SET time_zone = '+01:00';
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a TIMESTAMP NOT NULL
) AUTO_INCREMENT = 1;

INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10' );
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+05:30' );

--echo # The valid timezone displacement range as prescribed by the
--echo # sql standard is -14:00 to +14:00.
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+14:00' );
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10-14:00' );

--echo # Requirement F2.
SET @a = '2015-01-01 10:10:10+02:00';
PREPARE stmt1 FROM "INSERT INTO t1( a ) VALUES ( ? )";
EXECUTE stmt1 USING @a;

SELECT * FROM t1;

--echo # To see values stored in SE use UTC timezone.
SET time_zone = '+00:00';
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10' );
SELECT * FROM t1;

SET time_zone = '+03:00';
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+02:00' );
SELECT * FROM t1;

--echo # Make sure comparisions work correctly for TIMESTAMP.
SET time_zone = '+05:30';
SELECT * FROM t1;
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+03:30';
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+02:00';
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+05:30';

SET time_zone = '+00:00';
SELECT * FROM t1;
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+03:30';
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+02:00';
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+05:30';

SET time_zone = DEFAULT;

DROP TABLE t1;

SET time_zone = '+01:00';

CREATE TABLE t1 ( a TIMESTAMP(1) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.1+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.1';
DROP TABLE t1;

CREATE TABLE t1 ( a TIMESTAMP(2) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.01+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.01';
DROP TABLE t1;

CREATE TABLE t1 ( a TIMESTAMP(3) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.001+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.001';
DROP TABLE t1;

CREATE TABLE t1 ( a TIMESTAMP(4) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.0001+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.0001';
DROP TABLE t1;

CREATE TABLE t1 ( a TIMESTAMP(5) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.00001+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.00001';
DROP TABLE t1;

CREATE TABLE t1 ( a TIMESTAMP(6) );
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.000001+05:30' );
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.000001';
DROP TABLE t1;


--echo #
--echo # The DATETIME type.
--echo #

SET time_zone = '+02:00';

CREATE TABLE t2 ( a DATETIME DEFAULT '1995-05-05 00:00:00+05:30' );
INSERT INTO t2 VALUES ();
SELECT * FROM t2;
DROP TABLE t2;

CREATE TABLE t2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a DATETIME NOT NULL
) AUTO_INCREMENT = 1;

INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10' );
SELECT * FROM t2;

--echo # No effect of time_zone on the stored values of DATETIME datatype.
SET time_zone = '+05:30';
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+05:30' );
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+00:00' );
SELECT * FROM t2;

SET time_zone = '+02:00';
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+03:00' );
SELECT * FROM t2;

--echo # Make sure comparisions work correctly for DATETIME.
SET time_zone = '+05:30';
SELECT * FROM t2;
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+03:30';
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+02:00';
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+05:30';

SET time_zone = '+00:00';
SELECT * FROM t2;
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+03:30';
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+02:00';
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+05:30';


SET time_zone = DEFAULT;

--echo # Negative test case in Item_func_add_time::val_datetime.
--echo # Second argument shouldn't have datetime + timezone info.
SELECT ADDTIME( '2001-01-01 00:00:00+02:00', '2001-01-01 00:00:00+02:00' );

SET time_zone = '+05:30';

SELECT CAST( '2001-01-01 00:00:00+02:00' AS DATETIME );

SELECT ADDTIME( TIMESTAMP'2001-01-01 00:00:00+02:00', '00:00:00.123456' );
SELECT CONVERT_TZ( '2015-01-01 10:10:10','+00:00','+05:00' );
SELECT CONVERT_TZ( '2015-01-01 10:10:10+02:00','+00:00','+05:00' );

SELECT TO_DAYS( '2001-01-01 00:00:00+07:00' );
SELECT TO_DAYS( '2001-01-01 00:00:00-07:00' );
SELECT DATEDIFF( '2007-12-31 23:59:59+02:00', '2007-12-30  00:00:00' );
SELECT TIMEDIFF( '2000:01:01 00:00:00+02:00',
                 '2000:01:01 00:00:00.000001+01:00' );
SELECT DATE_ADD( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND );
SELECT DATE_SUB( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND );
SELECT DATE_FORMAT( '2009-10-10 23:59:59+02:00', '%W %M %Y' );
SELECT TIME( '2003-12-31 23:59:59+02:00' );
SELECT TIMESTAMP( '2003-12-31 23:59:59+02:00', '12:00:00' );
SELECT TIMESTAMPADD(MINUTE, 1, '2003-01-02 23:59:59+02:00' );
SELECT TIMESTAMPDIFF(MONTH, '2003-02-01 23:59:59+02:00', '2003-05-01' );
SELECT TO_SECONDS( '2009-11-29 23:59:59+02:00' );
SELECT UNIX_TIMESTAMP( '2015-11-13 23:59:59+02:00' );

--echo # Change of time_zone should give different results.
SET time_zone = '+00:00';
SELECT TIMESTAMP'2001-01-01 00:00:00+02:00';
SELECT TIMESTAMP( '2001-01-01 00:00:00+02:00' );
SELECT CAST( '2001-01-01 00:00:00+02:00' AS DATETIME);

SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00+02:00', '00:00:00.123456' );

SELECT CONVERT_TZ( '2015-01-01 10:10:10', '+00:00', '+05:00' );
SELECT CONVERT_TZ( '2015-01-01 10:10:10+02:00', '+00:00', '+05:00' );

SELECT TO_DAYS( '2001-01-01 00:00:00+07:00' );
SELECT TO_DAYS( '2001-01-01 00:00:00-07:00' );
SELECT DATEDIFF( '2007-12-31 23:59:59+02:00', '2007-12-30  00:00:00' );
SELECT TIMEDIFF( '2000:01:01 00:00:00+02:00',
                 '2000:01:01 00:00:00.000001+01:00' );
SELECT DATE_ADD( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND);
SELECT DATE_SUB( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND);
SELECT DATE_FORMAT( '2009-10-10 23:59:59+02:00', '%W %M %Y' );
SELECT TIME( '2003-12-31 23:59:59+02:00' );
SELECT TIMESTAMP( '2003-12-31 23:59:59+02:00', '12:00:00' );
SELECT TIMESTAMPADD(MINUTE, 1, '2003-01-02 23:59:59+02:00' );
SELECT TIMESTAMPDIFF(MONTH, '2003-02-01 23:59:59+02:00', '2003-05-01' );
SELECT TO_SECONDS( '2009-11-29 23:59:59+02:00' );
SELECT UNIX_TIMESTAMP( '2015-11-13 23:59:59+02:00' );

DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(1) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.1+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.1';
DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(2) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.01+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.01';
DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(3) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.001+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.001';
DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(4) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.0001+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.0001';
DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(5) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.00001+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.00001';
DROP TABLE t2;

CREATE TABLE t2 ( a DATETIME(6) );
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.000001+05:30' );
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.000001';
DROP TABLE t2;

SET time_zone = DEFAULT;

--echo #
--echo # Bug#31239157: NO BOUNDS CHECK FOR TIMESTAMPS WITH TIME ZONE
--echo # DISPLACEMENT
--echo #

--echo # This is needed to check the below boundary case when time zone
--echo # adjustment causes a value to be outside the DATETIME range.
SET time_zone = CET;

CREATE TABLE t1 ( a TIMESTAMP );
CREATE TABLE t2 ( a TIMESTAMP(6) );

--replace_result '1901-12-13 21:45:52' X '2038-01-19 04:14:08' X
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:08+00:00' );
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07+00:00' );
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07.000001+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07.999999+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000000+00:00' );
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 01:00:00.000000+00:00' );
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:01.000000+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000001+00:00' );
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.999999+00:00' );

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08+00:00' );

--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t1 VALUES ( TIMESTAMP'9999-12-31 23:59:59.999999+00:00' );

--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t1 VALUES ( '9999-12-31 23:59:59.999999+00:00' );


--replace_result '2038-01-19 04:14:08.000000' X '1901-12-13 21:45:52.000000' X
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:08+00:00' );
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07+00:00' );
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07.000001+00:00' );
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07.999999+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000000+00:00' );
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 01:00:00.000000+00:00' );
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:01.000000+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000001+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.999999+00:00' );

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t2 VALUES ( '2038-01-19 03:14:08+00:00' );

--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t2 VALUES ( TIMESTAMP'9999-12-31 23:59:59.999999+00:00' );

--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
INSERT INTO t2 VALUES ( '9999-12-31 23:59:59.999999+00:00' );

SET time_zone = UTC;

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08+00:00' );

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08' );

SET sql_mode = '';

--echo # Tests that we can handle years which are negative wrt. the epoch.
INSERT INTO t1 VALUES ( '1969-12-31 23:59:59' );

SET sql_mode = DEFAULT;

SELECT * FROM t1;
SELECT * FROM t2;

SET time_zone = DEFAULT;

DROP TABLE t1, t2;

--echo #
--echo # Bug#30786762: FAILED ASSERTION FOR DATES WITH ZERO OR ABOVE TIMESTAMP
--echo # LIMIT
--echo #

SET sql_mode = regexp_replace(@@sql_mode, 'NO_ZERO_IN_DATE', '');

--error ER_TRUNCATED_WRONG_VALUE
SELECT TIMESTAMP '2020-00-01 08:00:00.123456+00:00';
--error ER_TRUNCATED_WRONG_VALUE
SELECT TIMESTAMP '2020-01-00 08:00:00.123456+00:00';

CREATE TABLE t1 ( a DATETIME(6) );

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( '2020-00-01 00:00:00.123456+00:00' );
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ( '2020-01-00 00:00:00.123456+00:00' );

--error ER_TRUNCATED_WRONG_VALUE
SELECT * FROM t1 WHERE a = '2020-00-01 00:00:00.123456+00:00';
--error ER_TRUNCATED_WRONG_VALUE
SELECT * FROM t1 WHERE a = '2020-01-00 00:00:00.123456+00:00';

DROP TABLE t1;

SET sql_mode = DEFAULT;