Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3867

from_unixtime() not giving expected output for a given time zone

    XMLWordPrintable

Details

    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.

      Attachments

        Issue Links

          Activity

            People

              leonid.fedorov Leonid Fedorov
              tntnatbry Gagan Goel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.