Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15751

CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?)

Details

    Description

      create table t1 as select now();
      		 
      show create table t1;

      returns

      +-------+----------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                           |
      		 
      +-------+----------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `now()` datetime NOT NULL
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
      		 
      +-------+----------------------------------------------------------------------------------------+

      should be timestamp.

      These functions should also return TIMESTAMP:

      • FROM_UNIXTIME()
      • SYSDATE()

      Note, the function LOCALTIMESTAMP() according to the SQL Standard returns TIMESTAMP WITHOUT TIME ZONE, therefore we'll keep its return type as DATETIME.

      This change is (among other things) expected to fix the following problem:

      SET time_zone='Europe/Moscow';
      		 
      CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
      		 
       
      		 
      SET timestamp=1288477526 /*summer time in Moscow*/;
      		 
      INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP()));
      		 
       
      		 
      SET timestamp=1288477526+3599 /*winter time in Moscow*/;
      		 
      INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP()));
      		 
       
      		 
      SELECT a, UNIX_TIMESTAMP(a) FROM t1;
      		 
      DROP TABLE t1;


      +---------------------+-------------------+
      		 
      | a                   | UNIX_TIMESTAMP(a) |
      		 
      +---------------------+-------------------+
      		 
      | 2010-10-31 02:25:26 |        1288477526 |
      		 
      | 2010-10-31 02:25:26 |        1288477526 |
      		 
      | 2010-10-31 02:25:25 |        1288481125 | <-- this value is good
      		 
      | 2010-10-31 02:25:25 |        1288477525 | <-- this value is wrong
      		 
      +---------------------+-------------------+

      Notice, on the second INSERT statement (soon after DST fallback), CURRENT_TIMESTAMP() and COALESCE(CURRENT_TIMESTAMP()) inserted different values because:

      • Insertion of CURRENT_TIMESTAMP() works in TIMESTAMP format due to a hack in Item_func_now_local::save_in_field
      • Insertion of COALESCE(CURRENT_TIMESTAMP()) works in DATETIME format.

      After the change COALESCE(CURRENT_TIMESTAMP()) is expected to insert the same value with CURRENT_TIMESTAMP().

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics

          • Major - Major loss of function.
          • Stalled
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34932 differrent result in 32-bit in MDEV-15751

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35250 Assertion `dec <= 6' failed in my_timestamp_binary_length

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15654 ADDTIME creates invalid timestamp value near DST changes

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34037 DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change

          • Major - Major loss of function.
          • In Review

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34829 LOCALTIME returns a wrong data type

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36126 FROM_UNIXTIME(0) used in WHERE gives warning and results in no data being returned

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            According to the standard, it should return TIMESTAMP WITH TIMEZONE:

            The declared type of a <current timestamp value function> is TIMESTAMP WITH TIME ZONE.

            While, the best name for MariaDB's TIMESTAMP is "TIMESTAMP WITH LOCAL TIME ZONE".

            So perhaps we should implement MDEV-10018 first.

            bar Alexander Barkov added a comment - - edited According to the standard, it should return TIMESTAMP WITH TIMEZONE: The declared type of a <current timestamp value function> is TIMESTAMP WITH TIME ZONE. While, the best name for MariaDB's TIMESTAMP is " TIMESTAMP WITH LOCAL TIME ZONE ". So perhaps we should implement MDEV-10018 first.
            bar Alexander Barkov added a comment -

            Hello serg, please review a patch for this task:
            https://github.com/MariaDB/server/commit/fec3696fef6fb5a0be5f78632c056382f227e778

            bar Alexander Barkov added a comment - Hello serg , please review a patch for this task: https://github.com/MariaDB/server/commit/fec3696fef6fb5a0be5f78632c056382f227e778
            serg Sergei Golubchik added a comment -

            b626f5398dc is ok

            serg Sergei Golubchik added a comment - b626f5398dc is ok
            bar Alexander Barkov added a comment -

            elenst, please find the patch here:

            https://github.com/MariaDB/server/tree/bb-11.7-MDEV-15751-timestamp

            Thanks.

            bar Alexander Barkov added a comment - elenst , please find the patch here: https://github.com/MariaDB/server/tree/bb-11.7-MDEV-15751-timestamp Thanks.
            bar Alexander Barkov added a comment -

            ramesh, I force-pushed the branch. There was a test failure on 32bit platforms.

            bar Alexander Barkov added a comment - ramesh , I force-pushed the branch. There was a test failure on 32bit platforms.
            ramesh Ramesh Sivaraman added a comment -

            bar ack, latest commit will be used for testing.

            ramesh Ramesh Sivaraman added a comment - bar ack, latest commit will be used for testing.
            ramesh Ramesh Sivaraman added a comment -

            okay to push

            ramesh Ramesh Sivaraman added a comment - okay to push

            People

              bar Alexander Barkov
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.