path: root/mysql-test/main/timezone2.test

# This script tests our own time zone support functions

# Preparing playground
--disable_warnings
drop table if exists t1, t2;
drop function if exists f1;
--enable_warnings


# 
# Let us first check +HH:MM style timezones
#
create table t1 (ts timestamp);

set time_zone='+00:00';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
insert into t1 (ts) values ('2003-03-30 02:30:00');

set time_zone='+10:30';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
insert into t1 (ts) values ('2003-03-30 02:30:00');

set time_zone='-10:00';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
insert into t1 (ts) values ('2003-03-30 02:30:00');

# Here we will get different results
select * from t1;

drop table t1;


# 
# Let us try DB specified time zones
#
select Name from mysql.time_zone_name where Name in 
  ('UTC','Universal','MET','Europe/Moscow','leap/Europe/Moscow');

create table t1 (i int, ts timestamp);

set time_zone='MET';

# We check common date time value and non existent or ambiguios values
# Normal value without DST
insert into t1 (i, ts) values
  (unix_timestamp('2003-03-01 00:00:00'),'2003-03-01 00:00:00');
# Values around and in spring time-gap
insert into t1 (i, ts) values
  (unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'),
  (unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'),
  (unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00');
# Values around and in spring time-gap
insert into t1 (i, ts) values
  (unix_timestamp(20030330015959),20030330015959),
  (unix_timestamp(20030330023000),20030330023000),
  (unix_timestamp(20030330030000),20030330030000);
# Normal value with DST
insert into t1 (i, ts) values
  (unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
# Ambiguos values (also check for determenism)
insert into t1 (i, ts) values
  (unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'),
  (unix_timestamp('2003-10-26 02:00:00'),'2003-10-26 02:00:00'),
  (unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59'),
  (unix_timestamp('2003-10-26 04:00:00'),'2003-10-26 04:00:00'),
  (unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59');

set time_zone='UTC';

select * from t1;

truncate table t1;

# Simple check for 'Europe/Moscow' time zone just for showing that it works
set time_zone='Europe/Moscow';
insert into t1 (i, ts) values
  (unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
  (unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
  (unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
  (unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
select * from t1;
truncate table t1;


#
# Check for time zone with leap seconds
# Values in ts column must be the same but values in i column should
# differ from corresponding values for Europe/Moscow a bit.
#
set time_zone='leap/Europe/Moscow';
insert into t1 (i, ts) values
  (unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
  (unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
  (unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
  (unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
select * from t1;
truncate table t1;
# Let us test leap jump
insert into t1 (i, ts) values
  (unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'),
  (unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00');
select * from t1;
# Additional 60ieth second!
select from_unixtime(362793609);

drop table t1;


# 
# Let us test range for TIMESTAMP
#
create table t1 (ts timestamp);
set time_zone='UTC';
insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'),
                      ('1970-01-01 00:00:00'),('1970-01-01 00:00:01'),
                      ('2038-01-19 03:14:07'),('2038-01-19 03:14:08');
select * from t1;
truncate table t1;
# MET time zone has range shifted by one hour
set time_zone='MET';
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'),
                      ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'),
                      ('2038-01-19 04:14:07'),('2038-01-19 04:14:08');
select * from t1;
truncate table t1;
# same for +01:30 time zone
set time_zone='+01:30';
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'),
                      ('1970-01-01 01:30:00'),('1970-01-01 01:30:01'),
                      ('2038-01-19 04:44:07'),('2038-01-19 04:44:08');
select * from t1;

drop table t1;


# 
# Test of show variables
#
show variables like 'time_zone';
set time_zone = default;
show variables like 'time_zone';


# 
# Let us try some invalid time zone specifications
#
--error 1298
set time_zone= '0';
--error 1298
set time_zone= '0:0';
--error 1298
set time_zone= '-20:00';
--error 1298
set time_zone= '+20:00';
--error 1298
set time_zone= 'Some/Unknown/Time/Zone';


# Let us check that aliases for time zones work and they are 
# case-insensitive
select convert_tz(now(),'UTC', 'Universal') = now();
select convert_tz(now(),'utc', 'UTC') = now();


# 
# Let us test CONVERT_TZ function (may be func_time.test is better place).
#
select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC'); 
select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC'); 
select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC'); 
select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');

# Let us test variable time zone argument
create table t1 (tz varchar(3));
insert into t1 (tz) values ('MET'), ('UTC');
select tz, convert_tz('2003-12-31 00:00:00',tz,'UTC'), convert_tz('2003-12-31 00:00:00','UTC',tz) from t1 order by tz;
drop table t1;

# Parameters to CONVERT_TZ() what should give NULL
select convert_tz('2003-12-31 04:00:00', NULL, 'UTC');
select convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC');
select convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone');
select convert_tz('2003-12-31 04:00:00', 'MET', NULL);
select convert_tz( NULL, 'MET', 'UTC');

#
# Test for bug #4508 "CONVERT_TZ() function with new time zone as param
# crashes server." (Was caused by improperly worked mechanism of time zone
# dynamical loading).
#
create table t1 (ts timestamp);
set timestamp=1000000000;
insert into t1 (ts) values (now());
select convert_tz(ts, @@time_zone, 'Japan') from t1;
drop table t1;

#
# Test for bug #7705 "CONVERT_TZ() crashes with subquery/WHERE on index
# column". Queries in which one of time zone arguments of CONVERT_TZ() is
# determined as constant only at val() stage (not at fix_fields() stage),
# should not crash server.
#
select convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone) from (select 'UTC' as custTimeZone) as tmp;

#
# Test for bug #7899 "CREATE TABLE .. SELECT .. and CONVERT_TZ() function
# does not work well together". The following statement should return only
# one NULL row and not result of full join.
#
create table t1 select convert_tz(NULL, NULL, NULL);
select * from t1;
drop table t1;

# End of 4.1 tests

#
# Test for bug #11081 "Using a CONVERT_TZ function in a stored function
# or trigger fails".
#
SET @old_log_bin_trust_function_creators = @@global.log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;

create table t1 (ldt datetime, udt datetime);
create function f1(i datetime) returns datetime
  return convert_tz(i, 'UTC', 'Europe/Moscow');
create trigger t1_bi before insert on t1 for each row
  set new.udt:= convert_tz(new.ldt, 'Europe/Moscow', 'UTC');
# This should work without errors
insert into t1 (ldt) values ('2006-04-19 16:30:00');
select * from t1;
# This should work without errors as well
select ldt, f1(udt) as ldt2 from t1;
drop table t1;
drop function f1;

SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;

# End of 5.0 tests


#
# BUG#9953: CONVERT_TZ requires mysql.time_zone_name to be locked
# BUG#19339: CONVERT_TZ(): overly aggressive in locking time_zone_name
# table
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (t TIMESTAMP);
INSERT INTO t1 VALUES (NULL), (NULL);

LOCK TABLES t1 WRITE;

# The following two queries should not return error that time zone
# tables aren't locked.  We use IS NULL below to supress timestamp
# result.
SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');

UNLOCK TABLES;

DROP TABLE t1;

--echo #
--echo # Bug #55424: convert_tz crashes when fed invalid data
--echo #

CREATE TABLE t1 (a SET('x') NOT NULL);
INSERT INTO t1 VALUES ('');
SELECT CONVERT_TZ(1, a, 1) FROM t1;
SELECT CONVERT_TZ(1, 1, a) FROM t1;
DROP TABLE t1;

--echo End of 5.1 tests


--echo #
--echo # Start of 5.3 tests
--echo #

--echo #
--echo # MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
--echo #

SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
SET timestamp=DEFAULT;

--echo #
--echo # MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ
--echo #
SELECT CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ); 


--echo #
--echo # End of 5.3 tests
--echo #

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

--echo #
--echo # MDEV-17203 Move fractional second truncation from Item_xxx_typecast::get_date() to Time and Datetime constructors
--echo # (an addition for the test for MDEV-4653)

SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SET old_mode=ZERO_DATE_TIME_CAST;
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
SET old_mode=DEFAULT;
SET timestamp=DEFAULT;

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

# MAX()
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)      /*summer time in Moscow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1;
SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1;
CREATE TABLE t2 (a TIMESTAMP);
INSERT INTO t2 SELECT MAX(a) AS a FROM t1;
SELECT a, UNIX_TIMESTAMP(a) FROM t2;
DROP TABLE t2;
DROP TABLE t1;


# Comparison
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
CREATE TABLE t2 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)      /*summer time in Moscow*/);
INSERT INTO t2 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(t1.a), UNIX_TIMESTAMP(t2.a) FROM t1,t2;
SELECT * FROM t1,t2 WHERE t1.a < t2.a;
DROP TABLE t1,t2;


# SP variable comparison
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE a,b TIMESTAMP;
  SET time_zone='+00:00';
  SET a=FROM_UNIXTIME(1288477526);
  SET b=FROM_UNIXTIME(1288481125);
  SELECT a < b;
  SET time_zone='Europe/Moscow';
  SELECT a < b;
END;
$$
DELIMITER ;$$


# SP function comparison
DELIMITER $$;
CREATE OR REPLACE FUNCTION f1(uts INT) RETURNS TIMESTAMP
BEGIN
  DECLARE ts TIMESTAMP;
  DECLARE tz VARCHAR(64) DEFAULT @@time_zone;
  SET time_zone='+00:00';
  SET ts=FROM_UNIXTIME(uts);
  SET time_zone=tz;
  RETURN ts;
END;
$$
DELIMITER ;$$
SET time_zone='+00:00';
SELECT f1(1288477526) < f1(1288481125);
SET time_zone='Europe/Moscow';
SELECT f1(1288477526) < f1(1288481125);
DROP FUNCTION f1;


# LEAST()
CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP);
SET time_zone='+00:00';
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/,
                       FROM_UNIXTIME(1288481125) /*winter time in Moscow*/);
SELECT *, LEAST(a,b) FROM t1;
SET time_zone='Europe/Moscow';
SELECT *, LEAST(a,b) FROM t1;
SELECT UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(LEAST(a,b)) FROM t1;
DROP TABLE t1;


# BETWEEN
CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP,c TIMESTAMP);
SET time_zone='+00:00';
INSERT INTO t1 VALUES (
  FROM_UNIXTIME(1288477526) /*summer time in Moscow*/,
  FROM_UNIXTIME(1288481125) /*winter time in Moscow*/,
  FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SELECT b BETWEEN a AND c FROM t1;
SET time_zone='Europe/Moscow';
SELECT b BETWEEN a AND c FROM t1;
DROP TABLE t1;


# ORDER BY
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481125) /*winter time in Moscow*/);
SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
DROP TABLE t1;


# GROUP BY
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1 GROUP BY a;
DROP TABLE t1;


# CASE
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1;
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1;
DROP TABLE t1;


# IN
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP,c TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126),FROM_UNIXTIME(1288481127));
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;
DROP TABLE t1;

# Comparison and IN in combination with a subquery (with one row)

SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));

SET time_zone='Europe/Moscow';
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
DROP TABLE t1;

# Comparison and IN in combinarion with a subquery (with multiple rows)
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000000),FROM_UNIXTIME(1200000000));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000001),FROM_UNIXTIME(1200000001));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000000),FROM_UNIXTIME(1400000000));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000001),FROM_UNIXTIME(1400000001));
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));

SET time_zone='Europe/Moscow';
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
DROP TABLE t1;

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

SET time_zone='+00:00';
CREATE TABLE t1 (a INT, ts TIMESTAMP) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1, FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
CREATE TABLE t2 AS SELECT ts, COALESCE(ts) AS cts FROM t1 GROUP BY cts;
SELECT ts, cts, UNIX_TIMESTAMP(ts) AS uts, UNIX_TIMESTAMP(cts) AS ucts FROM t2;
DROP TABLE t1,t2;
SET time_zone=DEFAULT;

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