Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.3
-
None
-
Suse 15.1 self compiled from 8c0b988073c6537f53bcda0afbba0f4ce20fae34
Description
In short after changing timezone, the select * data differ from the indexed one
SET time_zone = 'UTC'; |
|
CREATE TABLE `l2` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
`dateUTC` date GENERATED ALWAYS AS (cast(`timestamp` as date)) VIRTUAL, |
PRIMARY KEY (`id`), |
KEY `dateUTC` (`dateUTC`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
INSERT INTO `l2` (`id`, `timestamp`) VALUES |
(1, '2020-04-12 23:10:04'), |
(2, '2020-04-12 20:10:04'); |
|
**************************************************
|
SET time_zone = 'Europe/Helsinki'; |
|
select * from l2 where timestamp > '2020-04-13'; |
+----+---------------------+------------+ |
| id | timestamp | dateUTC | |
+----+---------------------+------------+ |
| 1 | 2020-04-13 02:10:04 | 2020-04-13 |
|
+----+---------------------+------------+ |
1 row in set (0.001 sec) |
|
select * from l2 where dateUTC = '2020-04-13'; |
Empty set (0.002 sec) |
|
select * from l2 where dateUTC = '2020-04-12'; |
+----+---------------------+------------+ |
| id | timestamp | dateUTC | |
+----+---------------------+------------+ |
| 1 | 2020-04-13 02:10:04 | 2020-04-13 |
|
| 2 | 2020-04-12 23:10:04 | 2020-04-12 |
|
+----+---------------------+------------+ |
This is related to MDEV-21249
Which fixed only the representational, but the data in the index is still "wrong"