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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.3(EOL), 10.4(EOL)
    • 10.5
    • Data types
    • 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

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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