[MDEV-13995] MAX(timestamp) returns a wrong result near DST change - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.4.1
Component/s: Data types, Temporal Types
Labels:
None

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

blocks

MDEV-4912 Data type plugin API version 1

Closed

MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

Closed

MDEV-11829 Please add support for datetime with time zone literals (ISO6801)

Open

MDEV-15654 ADDTIME creates invalid timestamp value near DST changes

Closed

MDEV-16873 Compare two FLOAT expressions as floats rather than doubles

Open

is blocked by

MDEV-13997 Change Item_bool_rowready_func2 to cache const items at fix time rather than evaluation time

Closed

MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler

Closed

MDEV-17905 Add class Charset

Closed

MDEV-17906 Class Binary_string

Closed

MDEV-17907 Class Static_binary_string

Closed

MDEV-17928 Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits

Closed

relates to

MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

Open

MDEV-14278 Bad result for UNIX_TIMESTAMP(zero_timestamp_field)

Open

MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query

Closed

MDEV-22828 Bad results near DST change: UNIX_TIMESTAMP(timestamp_column)=<integer literal>

Open

ODBC-417 Year 2038 issue executing prepared UPDATE statement via ODBC when binding timestamps

Closed

(6 is blocked by, 5 relates to)

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-10-04 09:52

Updated:: 2020-06-10 13:38

Resolved:: 2018-12-10 15:43

Git Integration

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