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

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5
Component/s: Data types
Labels:
None

Description

Before repeating the problem, make sure that you have timezone information loaded into MariaDB server, so this statement does not produce errors or warnings:

SET time_zone='Europe/Moscow';

I create and populate a table with a TIMESTAMP column as follows:

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+3600; -- 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:26 |        1288481126 |

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

Now I want to get the first record:

SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288477526;

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

| a                   | UNIX_TIMESTAMP(a) |

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

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

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

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

Looks wrong. Expect to get one record.

EXPLAIN EXTENDED for this query reports that the UNIX_TIMESTAMP(a)=1288477526 was removed from the condition:

EXPLAIN EXTENDED SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288477526;

SHOW WARNINGS;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 | Using where |

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

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

| Level | Code | Message                                                                                                                                                      |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a`,unix_timestamp(`test`.`t1`.`a`) AS `UNIX_TIMESTAMP(a)` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2010-10-31 02:25:26' |

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

Now I want to get the second record:

SELECT a,UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288481126;

Empty set (0.001 sec)

Looks wrong. Expect one record.

EXPLAIN EXTENDED for this query shows that the condition was erroneously calculated as always-FALSE:

EXPLAIN EXTENDED SELECT *, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288481126;

SHOW WARNINGS;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |

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

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |

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

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

| Level | Code | Message                                                                                                       |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a`,unix_timestamp(`test`.`t1`.`a`) AS `UNIX_TIMESTAMP(a)` from `test`.`t1` where 0 |

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

Attachments

Issue Links

relates to

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

Closed

MDEV-16422 DST and unix_timestamp(now())

Confirmed

MDEV-16423 DST and timestamp comparison

Confirmed

MDEV-19121 optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist

Confirmed

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

Closed

MDEV-22734 Assertion `mon > 0 && mon < 13' failed in sec_since_epoch

Closed

MDEV-32148 Inefficient WHERE timestamp_column=datetime_const_expr

Closed

MDEV-32152 Wrong results near DST for `WHERE indexed_timestamp_column='2010-10-31 02:30:00'`

Open

(3 relates to)

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2020-06-08 04:23

Updated:: 2023-09-12 12:48

Git Integration

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