Description:
I expect the new datetime literal with explicit time zone offset to give a deterministic timestamp (epoch or UTC date time) regardless of time_zone the session has configured (@@time_zone).
In 'CET' time_zone (from the mtr test suite) and 'Europe/Amsterdam' SYSTEM time zone the explicit time zone offset is not usable since it does not deliver expected results.
1) It does not return the matching rows
2) The warning in explain containing the generated query after parsing, does not include the explicit time zone offset, making the timestamp non-deterministic.
How to repeat:
Build MySQL 8.0.22 and start it with './mtr --start 1st' and then connect a mysql client executing the below:
drop table if exists ts;
select "Using time_zone as UTC, to be 'safe' from local time conversion (conversions may still be done but only within UTC)" as 'STATUS';
set time_zone = '+00:00';
select @@version, @@time_zone;
create table ts (id int unsigned not null auto_increment primary key, ts timestamp);
insert into ts (ts) values
('2020-10-24 23:40:00'),
('2020-10-25 00:00:00'),
('2020-10-25 00:20:00'),
('2020-10-25 00:40:00'),
('2020-10-25 01:00:00'),
('2020-10-25 01:20:00'),
('2020-10-25 01:40:00'),
('2020-10-25 02:00:00'),
('2020-10-25 02:20:00');
show create table ts;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
show warnings;
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
show warnings;
set time_zone = 'CET';
select @@version, @@time_zone;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts;
select "id 4 does not match the WHERE clause!";
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
select "Where is id 5 & 6?";
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
show warnings;
select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?";
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
show warnings;
select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?";
set time_zone = '+00:00';
select @@version, @@time_zone;
alter table ts add index (ts);
show create table ts;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
show warnings;
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
show warnings;
set time_zone = 'CET';
select @@version, @@time_zone;
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts;
select "id 4 does not match the WHERE clause!";
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
select "Where is id 5 & 6?";
select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
show warnings;
select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?";
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00';
explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
show warnings;
explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00';
select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?";
drop table ts;
set time_zone = 'CET';
Suggested fix:
I assume that moving between epoch (or UTC time zone) internally (since the timestamp is stored as epoch) and parsed query (which only have INT or DATETIME) there are several cases where the time zone offset is lost or not taken into account. All these needs to be fixed.
Looking at the EXPLAIN ANALYZE output, it seems like both the filter on TIMESTAMP as well as index lookup fails to use the given time zone offset.
This is *not* a duplicate of bug#38455, since we now have given a deterministic time specification, and would expect the correct result (even with presentation not being deterministic for the timestamp column, which is a separate issue).
Description: I expect the new datetime literal with explicit time zone offset to give a deterministic timestamp (epoch or UTC date time) regardless of time_zone the session has configured (@@time_zone). In 'CET' time_zone (from the mtr test suite) and 'Europe/Amsterdam' SYSTEM time zone the explicit time zone offset is not usable since it does not deliver expected results. 1) It does not return the matching rows 2) The warning in explain containing the generated query after parsing, does not include the explicit time zone offset, making the timestamp non-deterministic. How to repeat: Build MySQL 8.0.22 and start it with './mtr --start 1st' and then connect a mysql client executing the below: drop table if exists ts; select "Using time_zone as UTC, to be 'safe' from local time conversion (conversions may still be done but only within UTC)" as 'STATUS'; set time_zone = '+00:00'; select @@version, @@time_zone; create table ts (id int unsigned not null auto_increment primary key, ts timestamp); insert into ts (ts) values ('2020-10-24 23:40:00'), ('2020-10-25 00:00:00'), ('2020-10-25 00:20:00'), ('2020-10-25 00:40:00'), ('2020-10-25 01:00:00'), ('2020-10-25 01:20:00'), ('2020-10-25 01:40:00'), ('2020-10-25 02:00:00'), ('2020-10-25 02:20:00'); show create table ts; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; show warnings; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; show warnings; set time_zone = 'CET'; select @@version, @@time_zone; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts; select "id 4 does not match the WHERE clause!"; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; select "Where is id 5 & 6?"; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; show warnings; select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?"; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; show warnings; select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?"; set time_zone = '+00:00'; select @@version, @@time_zone; alter table ts add index (ts); show create table ts; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; show warnings; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; show warnings; set time_zone = 'CET'; select @@version, @@time_zone; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts; select "id 4 does not match the WHERE clause!"; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; select "Where is id 5 & 6?"; select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; show warnings; select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?"; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00'; explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; show warnings; explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00'; select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?"; drop table ts; set time_zone = 'CET'; Suggested fix: I assume that moving between epoch (or UTC time zone) internally (since the timestamp is stored as epoch) and parsed query (which only have INT or DATETIME) there are several cases where the time zone offset is lost or not taken into account. All these needs to be fixed. Looking at the EXPLAIN ANALYZE output, it seems like both the filter on TIMESTAMP as well as index lookup fails to use the given time zone offset. This is *not* a duplicate of bug#38455, since we now have given a deterministic time specification, and would expect the correct result (even with presentation not being deterministic for the timestamp column, which is a separate issue).