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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
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 (ISO 8601)

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

MDEV-34028 Bad results near DST change with a dynamic SQL TIMESTAMP parameter

Open

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

Closed

(6 is blocked by, 6 relates to)

Activity

Ascending order - Click to sort in descending order

View 9 older comments

Alexander Barkov added a comment - 2017-11-06 03:51 - edited

IN is affected:

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (a TIMESTAMP, b TIMESTAMP,c TIMESTAMP);

INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126),FROM_UNIXTIME(1288481127));

SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;

SET time_zone='Europe/Moscow';

SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;

+-------------------+-------------------+---+

| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |

+-------------------+-------------------+---+

|        1288477526 |        1288481126 | 0 |

+-------------------+-------------------+---+

+-------------------+-------------------+---+

| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |

+-------------------+-------------------+---+

|        1288477526 |        1288481126 | 1 |

+-------------------+-------------------+---+

The second result is wrong. Both SELECT queries are expected to return 0.

Alexander Barkov added a comment - 2017-11-06 03:51 - edited IN is affected: SET time_zone= '+00:00' ; CREATE OR REPLACE TABLE t1 (a TIMESTAMP , b TIMESTAMP ,c TIMESTAMP ); INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126),FROM_UNIXTIME(1288481127)); SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1; SET time_zone= 'Europe/Moscow' ; SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1; +-------------------+-------------------+---+ | UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x | +-------------------+-------------------+---+ | 1288477526 | 1288481126 | 0 | +-------------------+-------------------+---+ +-------------------+-------------------+---+ | UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x | +-------------------+-------------------+---+ | 1288477526 | 1288481126 | 1 | +-------------------+-------------------+---+ The second result is wrong. Both SELECT queries are expected to return 0 .

Alexander Barkov added a comment - 2018-06-28 07:13 - edited

The same problem is repeatable with subqueries involved in comparison or IN.

This script correctly returns empty set in all three SELECT queries:

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (a TIMESTAMP, b TIMESTAMP);

INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));

SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);

SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);

SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));

Now if I change the time zone, all three queries return the row:

SET time_zone='Europe/Moscow';

SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);

SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);

SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));

MariaDB [test]> SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);

+---------------------+---------------------+

| a                   | b                   |

+---------------------+---------------------+

| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |

+---------------------+---------------------+

+---------------------+---------------------+

| a                   | b                   |

+---------------------+---------------------+

| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |

+---------------------+---------------------+

+---------------------+---------------------+

| a                   | b                   |

+---------------------+---------------------+

| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |

+---------------------+---------------------+

Alexander Barkov added a comment - 2018-06-28 07:13 - edited The same problem is repeatable with subqueries involved in comparison or IN. This script correctly returns empty set in all three SELECT queries: SET time_zone= '+00:00' ; CREATE OR REPLACE TABLE t1 (a TIMESTAMP , b TIMESTAMP ); INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126)); SELECT * FROM t1 WHERE a = ( SELECT MAX (b) FROM t1); SELECT * FROM t1 WHERE a = ( SELECT MIN (b) FROM t1); SELECT * FROM t1 WHERE a IN (( SELECT MAX (b) FROM t1), ( SELECT MIN (b) FROM t1)); Now if I change the time zone, all three queries return the row: SET time_zone= 'Europe/Moscow' ; SELECT * FROM t1 WHERE a = ( SELECT MAX (b) FROM t1); SELECT * FROM t1 WHERE a = ( SELECT MIN (b) FROM t1); SELECT * FROM t1 WHERE a IN (( SELECT MAX (b) FROM t1), ( SELECT MIN (b) FROM t1)); MariaDB [test]> SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +---------------------+---------------------+ | a | b | +---------------------+---------------------+ | 2010-10-31 02:25:26 | 2010-10-31 02:25:26 | +---------------------+---------------------+ +---------------------+---------------------+ | a | b | +---------------------+---------------------+ | 2010-10-31 02:25:26 | 2010-10-31 02:25:26 | +---------------------+---------------------+ +---------------------+---------------------+ | a | b | +---------------------+---------------------+ | 2010-10-31 02:25:26 | 2010-10-31 02:25:26 | +---------------------+---------------------+

Oleksandr Byelkin added a comment - 2018-11-29 17:39

Ideas we discussed:
1. special type for raw native data
2. probably union to have no problem with short types with fixed length like int, double, etc)
3. using raw is not good as it easy can be mixed with row (better native)

Oleksandr Byelkin added a comment - 2018-11-29 17:39 Ideas we discussed: 1. special type for raw native data 2. probably union to have no problem with short types with fixed length like int, double, etc) 3. using raw is not good as it easy can be mixed with row (better native)

Alexander Barkov added a comment - 2018-12-10 08:22

sanja, please review a new patch version:
The patch is here:
https://github.com/MariaDB/server/commits/bb-10.4-mdev13995
https://github.com/MariaDB/server/commit/d9b70161d4b84c50ea15dafbcdbf61a326b018c1

Alexander Barkov added a comment - 2018-12-10 08:22 sanja , please review a new patch version: The patch is here: https://github.com/MariaDB/server/commits/bb-10.4-mdev13995 https://github.com/MariaDB/server/commit/d9b70161d4b84c50ea15dafbcdbf61a326b018c1

Oleksandr Byelkin added a comment - 2018-12-10 12:25

OK to push!

Oleksandr Byelkin added a comment - 2018-12-10 12:25 OK to push!

MariaDB Server

MAX(timestamp) returns a wrong result near DST change

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration