|
The following preliminary INSERTs are only needed if your timezone tables are empty. The failure is reproducible with these as well as with properly created TZ data.
|
Create timezone data
|
insert into mysql.time_zone_name values ('EET',385),('GMT', 490);
|
insert into mysql.time_zone_transition values (385,1553994000,0),(385,1572138000,1);
|
insert into mysql.time_zone_transition_type values (385,0,10800,1,'EEST'),(385,1,7200,0,'EET'),(490,0,0,0,'GMT');
|
insert into mysql.time_zone values (385,'N'),(490,'N');
|
|
Test case, requires timezone data
|
set time_zone= EET;
|
drop table if exists t1;
|
create table t1 (
|
a int(11) NOT NULL,
|
s timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
e timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PERIOD FOR `se` (`s`, `e`)
|
);
|
insert into t1 values (1,'2019-01-01','2020-01-01');
|
--error ER_TRUNCATED_WRONG_VALUE
|
delete from t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00';
|
select * from t1;
|
DELETE is expected to return an error, but the behavior should be the same as currently happens on UPDATE:
MariaDB [test]> update t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00' set a = 2;
|
ERROR 1292 (22007): Incorrect datetime value: '2019-03-31 03:30:00' for column `test`.`t1`.`s` at row 1
|
MariaDB [test]> select * from t1;
|
+---+---------------------+---------------------+
|
| a | s | e |
|
+---+---------------------+---------------------+
|
| 1 | 2019-01-01 00:00:00 | 2020-01-01 00:00:00 |
|
+---+---------------------+---------------------+
|
1 row in set (0.001 sec)
|
Instead, first DELETE returns a wrong error:
|
10.4 503fd2115
|
MariaDB [test]> delete from t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00';
|
ERROR 1030 (HY000): Got error 1 "Operation not permitted" from storage engine MyISAM
|
And if you run SELECT after that, the table is empty, data has been lost:
MariaDB [test]> select * from t1;
|
Empty set (0.000 sec)
|
The wrong error part is reproducible with at least MyISAM, InnoDB, Aria.
The data loss happens with MyISAM and Aria, not InnoDB.
Reproducible on 10.4 and 10.5.
|