[MDEV-30880] TIMESTAMP: leap second causes different query results with range access. Created: 2023-03-19  Updated: 2023-03-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 11.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Observing leap second is not trivial so here's a complete howto:
(Thanks to bar for help and discussion btw!)

Get a time zone with leap second:

./sql/mysql_tzinfo_to_sql  /usr/share/zoneinfo/right/Europe/London right/Europe/London > london1.sql
 
./sql/mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/Europe/London > london2.sql

Load it into server

use mysql;
 
source london1.sql
 
source london2.sql

Restart the server. This is important.

Check that you can observe leap second:

set time_zone='right/Europe/London';
 
select from_unixtime(1483228825), from_unixtime(1483228826);
 
+---------------------------+---------------------------+
 
| from_unixtime(1483228825) | from_unixtime(1483228826) |
 
+---------------------------+---------------------------+
 
| 2016-12-31 23:59:59       | 2016-12-31 23:59:59       |
 
+---------------------------+---------------------------+
 
1 row in set (0.001 sec)

Let's create a table with a TIMESTAMP column and row with the leap second.

CREATE TABLE t1 (
 
  a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 
  key(a)
 
);
 
 
 
set timestamp=1483228823;
 
insert into t1 values ();
 
set timestamp=1483228824;
 
insert into t1 values ();
 
set timestamp=1483228825;
 
insert into t1 values ();
 
set timestamp=1483228826;
 
insert into t1 values ();
 
set timestamp=1483228827;
 
insert into t1 values ();


MariaDB [test]> select * from t1;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
| 2016-12-31 23:59:59 |
 
| 2017-01-01 00:00:00 |
 
+---------------------+
 
5 rows in set (0.009 sec)

Rows 3 and 4 look the same when converted into DATETIME, but internally they are not:

MariaDB [test]> select distinct a from t1;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
| 2016-12-31 23:59:59 |
 
| 2017-01-01 00:00:00 |
 
+---------------------+
 
5 rows in set (0.002 sec)

What happens if one uses range access?

MariaDB [test]> select * from t1 where a<='2016-12-31 23:59:59';
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
+---------------------+
 
3 rows in set (0.002 sec)


MariaDB [test]> explain select * from t1 where a<='2016-12-31 23:59:59';
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL | 3    | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
1 row in set (0.002 sec)


MariaDB [test]> select * from t1 use index() where a<='2016-12-31 23:59:59';
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
| 2016-12-31 23:59:59 |
 
+---------------------+
 
4 rows in set (0.001 sec)



 Comments   
Comment by Sergei Petrunia [ 2023-03-19 ]

For the MDEV-8320 tree this additionally causes:

MariaDB [test]> select * from t1 where year(a)=2016;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
+---------------------+
 
3 rows in set (0.002 sec)


MariaDB [test]> select * from t1 use index() where year(a)=2016;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2016-12-31 23:59:57 |
 
| 2016-12-31 23:59:58 |
 
| 2016-12-31 23:59:59 |
 
| 2016-12-31 23:59:59 |
 
+---------------------+
 
4 rows in set (0.002 sec)

Comment by Julius Goryavsky [ 2023-03-28 ]

gmt_sec_to_TIME() where the tmp->second+= hit correction is made at the end, which allows the code to correctly take into account the time when leap seconds are skipped, however, the time conversion from the string does not take leap seconds into account when directly converts the time string into MYSQL_TIME (or maybe through UTC, i don't known how exactly). As a result, a non-existent second is not included in the sampling interval. But when building the index, both times are converted to the same internal value (of the linear scale of seconds). And by the way, this probably never happens in practice (we didn't have negative leap seconds until at least 2023, although it could happen later this year), but imho the gmt_sec_to_TIME() code will result in SIGFAULT if the first correction in the list is negative (i == 0 but lp->ls_corr <= 0). And the "hit" value will theoretically be calculated incorrectly if lp_corr can be different from +/-1 (but this may not be possible, I may misunderstand the details of this code).

Generated at Thu Feb 08 10:19:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.