MySQL Bugs: #76523: unrepeatable example in the doc on leap second support

Bug #76523	unrepeatable example in the doc on leap second support
Submitted:	30 Mar 2015 1:26	Modified:	30 Mar 2015 5:38
Reporter:	Peiran Song	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The example in the following documentation page is not repeatable:

http://dev.mysql.com/doc/refman/5.6/en/time-zone-leap-seconds.html

Per the documentation, unix_timestamp 1230768000 corresponds to the leap second 2008-12-31 23:59:60, however, in my test, it corresponds to 2009-01-01 00:00:00. 

From the documentation, it sounds like there is a unix timestamp for the leap second, but it wasn't demonstrated with the example. I also tried 2012-06-30 23:59:60, and upcoming 2015-06-30 23:59:60 and there doesn't seem to be a unix timestamp for either leap second. Not sure if I am missing something. I am wondering, at the leap second, what would now() return? 

How to repeat:
5.6.19

mysql> CREATE TABLE t1 ( a INT,ts TIMESTAMP DEFAULT NOW(),PRIMARY KEY (ts));
Query OK, 0 rows affected (0.08 sec)

mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SET timestamp = 1230767999;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SET timestamp = 1230768000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (2);
Query OK, 1 row affected (0.02 sec)

mysql>  SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;
+------+---------------------+--------------------+
| a    | ts                  | UNIX_TIMESTAMP(ts) |
+------+---------------------+--------------------+
|    1 | 2008-12-31 23:59:59 |         1230767999 |
|    2 | 2009-01-01 00:00:00 |         1230768000 |
+------+---------------------+--------------------+
2 rows in set (0.00 sec)

mysql> select from_unixtime(1230768001);
+---------------------------+
| from_unixtime(1230768001) |
+---------------------------+
| 2009-01-01 00:00:01       |
+---------------------------+

Hello Peiran Song,

Thank you for the report.
I observed similar results(as shown in report) from 5.0.96, 5.1.73, 5.5.44, 5.6.25 and 5.7.8.

Thanks,
Umesh

mysql> select unix_timestamp("2008-12-31 23:59:59");
+---------------------------------------+
| unix_timestamp("2008-12-31 23:59:59") |
+---------------------------------------+
|                            1230767999 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2008-12-31 23:59:60");
+---------------------------------------+
| unix_timestamp("2008-12-31 23:59:60") |
+---------------------------------------+
|                              0.000000 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2008-12-31 23:59:60' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2009-01-01 00:00:00");
+---------------------------------------+
| unix_timestamp("2009-01-01 00:00:00") |
+---------------------------------------+
|                            1230768000 |
+---------------------------------------+
1 row in set (0.00 sec)

We don't document misbehaviour. Misbehaviour is a software bug, and needs to be fixed.

Documentation and product is still not matching in 8.0.42 and 8.4.5.

And I don't think MySQL is handling leap seconds, more than they can be expressed in DATETIME datatype.

UNIX_TIMESTAMP function is closely related to:
unix time - https://en.wikipedia.org/wiki/Unix_time which is without leap seconds

TIMESTAMP datatype is closely related to time_t, when stored:
https://en.cppreference.com/w/c/chrono/time_t - which is also without leap seconds.

So I consider this as issues with the documentation, not in the MySQL Server.

If you can show in any MySQL version and configuration that the document is correct, with the series of SQL commands and results, please do so and close this bug :)