Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4, 10.3.38, 10.5.19, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
-
None
-
Debian Buster and Debian Bullseye
Description
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);
|