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

use mysql;
source london1.sql
source london2.sql

<restart the server>;

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)


Ok, now we can observe the leap second. 
Let's create a table with a TIMESTAMP column and timestamp of 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)

## on the MDEV-8320 tree:

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)