[MCOL-3867] from_unixtime() not giving expected output for a given time zone Created: 2020-03-04  Updated: 2023-11-21  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc, regression suite
Affects Version/s: 1.4.0, 1.5.3
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Gagan Goel (Inactive) Assignee: Leonid Fedorov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MCOL-3869 Use the server time_zone system varia... Closed
PartOf
is part of MCOL-3594 Failed tests in the 001 suite in 1.4 Closed
is part of MCOL-3869 Use the server time_zone system varia... Closed
Epic Link: Time, Timestamp and Timezone

 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.

 Comments   
Comment by David Hall (Inactive) [ 2020-03-04 ]

The problem is that Columnstore uses the system setting for timezone. MariaDB has it's own timezone settings.
In this case, if the test is run on a system that has a timezone with daylight savings, the results differ than if run on a UTC machine such as AWS.
From previous work, the MariaDB timezone setting is available in PrimProc and ExeMgr, so this can be used to make Columnstore match MariaDB. Then, by setting the timezone during the test, results will be consistent.

Comment by Kirill Perov [ 2023-11-11 ]

replay:

create table idatatypetestm(cdecimal10 decimal(10));
create table datatypetestm(cdecimal10 decimal(10)) engine=columnstore;
insert idatatypetestm values(-9999995),(9999995),(0);
insert datatypetestm values(-9999995),(9999995),(0);

set time_zone='-5:00';
select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from idatatypetestm;
select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from datatypetestm;

set time_zone='+0:00';
select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from idatatypetestm;
select cdecimal10, from_unixtime(0), from_unixtime(CDECIMAL10), timestampdiff(second, from_unixtime(0), from_unixtime(CDECIMAL10)) from datatypetestm;

diff:

9999995 1969-12-31 19:00:00 1970-04-26 12:46:35
9999995 1969-12-31 19:00:00 1970-04-26 17:46:35
Generated at Thu Feb 08 02:46:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.