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

MAX(timestamp) returns a wrong result near DST change

    Details

    • Sprint:
      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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: