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

    • 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

            Activity

              People

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

                Dates

                Created:
                Updated:

                  Git Integration