[#MDEV-32152] Wrong results near DST for `WHERE indexed_timestamp

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.

[MDEV-32152] Wrong results near DST for `WHERE indexed_timestamp_column='2010-10-31 02:30:00'` Created: 2023-09-12 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data types, Temporal Types
Affects Version/s:	10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.1, 11.2, 11.3