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

MAX(timestamp) returns a wrong result near DST change

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.4.1
    • Data types, Temporal Types
    • None
    • 10.3.6-1

    Description

      This task is now the main show-stopper for MDEV-4912. So changing priority to Critical.

      I create a table with a TIMESTAMP column and populate it with special TIMESTAMP values near summer/winter time change:

      SET time_zone='Europe/Moscow';
      		 
      CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
      		 
      SET timestamp=1288477526;  -- this is summer time
      		 
      INSERT INTO t1 VALUES (NULL);
      		 
      SET timestamp=1288477526+3599; -- this is winter time
      		 
      INSERT INTO t1 VALUES (null);
      		 
      SELECT a, UNIX_TIMESTAMP(a) FROM t1;


      +---------------------+-------------------+
      		 
      | a                   | UNIX_TIMESTAMP(a) |
      		 
      +---------------------+-------------------+
      		 
      | 2010-10-31 02:25:26 |        1288477526 |
      		 
      | 2010-10-31 02:25:25 |        1288481125 |
      		 
      +---------------------+-------------------+

      Notice, the first record has a smaller UNIX_TIMESTAMP value, however the "YYYY-MM-DD hh:mm:ss" representation looks smaller for the second value.
      This corner case happens because the first timestamp corresponds to summer time, while the second timestamp corresponds to winter time in the given time zone, when the clock went one hour backward.

      Now I want to find the maximum value in the table t1 and store it into another table t2:

      CREATE OR REPLACE TABLE t2 (a TIMESTAMP);
      		 
      INSERT INTO t2 SELECT MAX(a) AS a FROM t1;
      		 
      SELECT a, UNIX_TIMESTAMP(a) FROM t2;


      +---------------------+-------------------+
      		 
      | a                   | UNIX_TIMESTAMP(a) |
      		 
      +---------------------+-------------------+
      		 
      | 2010-10-31 02:25:26 |        1288477526 |
      		 
      +---------------------+-------------------+

      Notice, it erroneously took the record with the bigger "YYYY-MM-DD hh:mm:ss" representation, but with the smaller UNIX_TIMESTAMP value.

      This is wrong. The expected behavior would be to take the record with 1288481125 timestamp value, which is later in time.

      The server code should be changed to handle TIMESTAMP values using my_time_t representation, without conversion to datetime format "YYYY-MM-DD hh:mm:ss".

      Attachments

        Issue Links

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11829 Please add support for datetime with time zone literals (ISO 8601)

          • Major - Major loss of function.
          • Open

          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-16873 Compare two FLOAT expressions as floats rather than doubles

          • Major - Major loss of function.
          • Open
          is blocked by

          Task - A task that needs to be done. MDEV-13997 Change Item_bool_rowready_func2 to cache const items at fix time rather than evaluation time

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17905 Add class Charset

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17906 Class Binary_string

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17907 Class Static_binary_string

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17928 Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14278 Bad result for UNIX_TIMESTAMP(zero_timestamp_field)

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22828 Bad results near DST change: UNIX_TIMESTAMP(timestamp_column)=<integer literal>

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34028 Bad results near DST change with a dynamic SQL TIMESTAMP parameter

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. ODBC-417 Year 2038 issue executing prepared UPDATE statement via ODBC when binding timestamps

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Sergei Golubchik made transition -
            Open In Progress
            		27d 5h 8m 1
            Alexander Barkov made transition -
            In Progress Stalled
            		4d 21h 57m 1
            Alexander Barkov made transition -
            Stalled In Progress
            		99d 21h 53m 2
            Alexander Barkov made transition -
            In Progress In Review
            		34d 2h 43m 2
            Oleksandr Byelkin made transition -
            In Review Stalled
            		265d 22h 49m 2
            Alexander Barkov made transition -
            Stalled Closed
            		3h 17m 1

            People

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