Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
1.4.0, 1.5.3
-
None
Description
Output of from_unixtime() is incorrect when a time zone value is set, and it does not match InnoDB. Here are steps to reproduce:
MariaDB [test]> set time_zone='-5:00'; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from idatatypetestm; |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| cdecimal10 | from_unixtime(0) | from_unixtime(CDECIMAL10) | timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) | |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| -9999999 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999998 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999997 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999996 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999995 | 1969-12-31 19:00:00 | NULL | NULL | |
| 9999995 | 1969-12-31 19:00:00 | 1970-04-26 12:46:35 | 9999995 |
|
| 9999996 | 1969-12-31 19:00:00 | 1970-04-26 12:46:36 | 9999996 |
|
| 9999997 | 1969-12-31 19:00:00 | 1970-04-26 12:46:37 | 9999997 |
|
| 9999998 | 1969-12-31 19:00:00 | 1970-04-26 12:46:38 | 9999998 |
|
| 9999999 | 1969-12-31 19:00:00 | 1970-04-26 12:46:39 | 9999999 |
|
| 0 | 1969-12-31 19:00:00 | 1969-12-31 19:00:00 | 0 |
|
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
11 rows in set (0.000 sec) |
|
|
MariaDB [test]> select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from tpch1.datatypetestm; |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| cdecimal10 | from_unixtime(0) | from_unixtime(CDECIMAL10) | timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) | |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| -9999999 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999998 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999997 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999996 | 1969-12-31 19:00:00 | NULL | NULL | |
| -9999995 | 1969-12-31 19:00:00 | NULL | NULL | |
| 9999995 | 1969-12-31 19:00:00 | 1970-04-26 17:46:35 | 10017995 |
|
| 9999996 | 1969-12-31 19:00:00 | 1970-04-26 17:46:36 | 10017996 |
|
| 9999997 | 1969-12-31 19:00:00 | 1970-04-26 17:46:37 | 10017997 |
|
| 9999998 | 1969-12-31 19:00:00 | 1970-04-26 17:46:38 | 10017998 |
|
| 9999999 | 1969-12-31 19:00:00 | 1970-04-26 17:46:39 | 10017999 |
|
| 0 | 1969-12-31 19:00:00 | 1970-01-01 00:00:00 | 18000 |
|
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
11 rows in set (0.008 sec) |
|
|
MariaDB [test]> set time_zone='+0:00'; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from idatatypetestm; |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| cdecimal10 | from_unixtime(0) | from_unixtime(CDECIMAL10) | timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) | |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| -9999999 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999998 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999997 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999996 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999995 | 1970-01-01 00:00:00 | NULL | NULL | |
| 9999995 | 1970-01-01 00:00:00 | 1970-04-26 17:46:35 | 9999995 |
|
| 9999996 | 1970-01-01 00:00:00 | 1970-04-26 17:46:36 | 9999996 |
|
| 9999997 | 1970-01-01 00:00:00 | 1970-04-26 17:46:37 | 9999997 |
|
| 9999998 | 1970-01-01 00:00:00 | 1970-04-26 17:46:38 | 9999998 |
|
| 9999999 | 1970-01-01 00:00:00 | 1970-04-26 17:46:39 | 9999999 |
|
| 0 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 0 |
|
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
11 rows in set (0.000 sec) |
|
|
MariaDB [test]> select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from tpch1.datatypetestm; |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| cdecimal10 | from_unixtime(0) | from_unixtime(CDECIMAL10) | timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) | |
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
| -9999999 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999998 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999997 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999996 | 1970-01-01 00:00:00 | NULL | NULL | |
| -9999995 | 1970-01-01 00:00:00 | NULL | NULL | |
| 9999995 | 1970-01-01 00:00:00 | 1970-04-26 17:46:35 | 9999995 |
|
| 9999996 | 1970-01-01 00:00:00 | 1970-04-26 17:46:36 | 9999996 |
|
| 9999997 | 1970-01-01 00:00:00 | 1970-04-26 17:46:37 | 9999997 |
|
| 9999998 | 1970-01-01 00:00:00 | 1970-04-26 17:46:38 | 9999998 |
|
| 9999999 | 1970-01-01 00:00:00 | 1970-04-26 17:46:39 | 9999999 |
|
| 0 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 0 |
|
+------------+---------------------+---------------------------+--------------------------------------------------------------------+ |
11 rows in set (0.008 sec) |
|
Here, test.idatatypetestm as an innodb table, and tpch1.datatypetestm is a columnstore table.
As we can see, from_unixtime() for CS does not give the expected output when the time_zone is '-5:00'.
Other temporal functions that depend on the value of time_zone might also be affected.
