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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.6, 10.11, 11.1(EOL), 11.2, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL)
    • 10.5, 10.6, 10.11, 11.2
    • Data types, Temporal Types
    • None

    Description

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

      I create and populate a table with an indexed TIMESTAMP column, the insert values near DST change (one hour fall back):

      SET time_zone='Europe/Moscow';
      CREATE OR REPLACE TABLE t1 (a TIMESTAMP NULL, KEY(a));
       
      # Add special values near DST change (one hour fall back)
      SET @first_second_after_dst_fall_back=1288479600;
      SET timestamp=@first_second_after_dst_fall_back-1800;
      INSERT INTO t1 VALUES (NOW());
      SET timestamp=@first_second_after_dst_fall_back+1800;
      INSERT INTO t1 VALUES (NOW());
       
      # Add some more data
      INSERT INTO t1 VALUES ('2001-01-01 10:20:30'),('2001-01-01 10:20:31');
      INSERT INTO t1 VALUES ('2001-01-01 10:20:32'),('2001-01-01 10:20:33');
      INSERT INTO t1 VALUES ('2001-01-01 10:20:34'),('2001-01-01 10:20:35');
      INSERT INTO t1 VALUES ('2001-01-01 10:20:36'),('2001-01-01 10:20:37');
      INSERT INTO t1 VALUES ('2001-01-01 10:20:38'),('2001-01-01 10:20:39');
      

      Now let's check the values near the DST change

      SELECT UNIX_TIMESTAMP(a), a FROM t1 WHERE a>DATE'2010-01-01';
      

      +-------------------+---------------------+
      | UNIX_TIMESTAMP(a) | a                   |
      +-------------------+---------------------+
      |        1288477800 | 2010-10-31 02:30:00 |
      |        1288481400 | 2010-10-31 02:30:00 |
      +-------------------+---------------------+
      

      Notice, there are two different UNIX_TIMESTAMP values with equal "YYYY-MM-DD hh:mm:ss" representation. So far so good.

      Now let's query these values:

      SELECT UNIX_TIMESTAMP(a), a FROM t1 WHERE a='2010-10-31 02:30:00';
      

      +-------------------+---------------------+
      | UNIX_TIMESTAMP(a) | a                   |
      +-------------------+---------------------+
      |        1288477800 | 2010-10-31 02:30:00 |
      +-------------------+---------------------+
      

      Opps. Only one value was found. Looks wrong.

      Now let's query these values without the index:

      SELECT UNIX_TIMESTAMP(a), a FROM t1 IGNORE KEY(a) WHERE a='2010-10-31 02:30:00';
      

      +-------------------+---------------------+
      | UNIX_TIMESTAMP(a) | a                   |
      +-------------------+---------------------+
      |        1288477800 | 2010-10-31 02:30:00 |
      |        1288481400 | 2010-10-31 02:30:00 |
      +-------------------+---------------------+
      

      Now it looks good, both values where found.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.