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

XML

Word

Printable

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Data types, Temporal Types
Labels:
None

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.

relates to

MDEV-16422 DST and unix_timestamp(now())

MDEV-16423 DST and timestamp comparison

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

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

MDEV-32148 Inefficient WHERE timestamp_column=datetime_const_expr

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

(1 relates to)

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