[#MDEV-31280] Inconsistent query results with datetime columns and indexes

There appears to be inconsistent results from queries using a datetime column depending on whether the column has an index (both regular and UNIQUE index act the same). See the following sample (run under 10.3.38 and 10.5.19)

SET @@session.sql_mode='';

CREATE TABLE test (id int unsigned AUTO_INCREMENT, starttime datetime, filesize int unsigned, PRIMARY KEY (id), KEY (starttime));

INSERT INTO test (starttime, filesize) VALUES ('2023-01-01T00:00:00Z', '9999');

This issues a warning due to the unsupported 'Z' in the datetime, but the query still inserts the data. If sql_mode is left as the default, the query errors out.

SELECT * FROM test WHERE starttime = '2023-01-01T00:00:00Z';

+----+---------------------+----------+

| id | starttime           | filesize |

+----+---------------------+----------+

|  1 | 2023-01-01 00:00:00 |     9999 |

+----+---------------------+----------+

(warning issued)

If we try to update the row using the same datetime format, instead of the data getting updated and receiving a warning, nothing changes, and there are no warnings.

UPDATE test SET filesize = 8888 WHERE starttime = '2023-01-01T00:00:00Z';

If we drop the index on the starttime column and then try to update, the data will change, and we receive a warning.

ALTER TABLE test DROP INDEX starttime;

UPDATE test SET filesize = 8888 WHERE starttime = '2023-01-01T00:00:00Z';

(warning issued)

SELECT * FROM test WHERE starttime = '2023-01-01T00:00:00Z';

+----+---------------------+----------+

| id | starttime           | filesize |

+----+---------------------+----------+

|  1 | 2023-01-01 00:00:00 |     8888 |

+----+---------------------+----------+

(warning issued)

There's something weird going on with the implicit casting of the string to datetime format when an index is present. As a bonus, if you explicitly cast the string beforehand, it will work even with the index.

UPDATE test SET filesize = 7777 WHERE starttime = CAST('2023-01-01T00:00:00Z' AS datetime);

[MDEV-31280] Inconsistent query results with datetime columns and indexes Created: 2023-05-15 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data Manipulation - Update
Affects Version/s:	10.4, 10.3.38, 10.5.19, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s:	10.4, 10.5, 10.6, 10.11

[MDEV-31280] Inconsistent query results with datetime columns and indexes Created: 2023-05-15 Updated: 2023-11-28