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

            Transition Time In Source Status Execution Times
            Alexander Barkov made transition -
            Open In Progress
            		1778d 23h 19m 1
            Alexander Barkov made transition -
            In Progress In Review
            		31m 49s 1
            Sergei Golubchik made transition -
            Stalled In Review
            		10d 22h 43m 1
            Sergei Golubchik made transition -
            In Review Stalled
            		556d 3h 28m 2
            Alexander Barkov made transition -
            Stalled In Testing
            		1d 16h 51m 1
            Ramesh Sivaraman made transition -
            In Testing Stalled
            		39d 21h 27m 1
            Sergei Golubchik made transition -
            Stalled Closed
            		4d 15h 9m 1

            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.