TSQLQuery.Next Exception for MySQL when encountering a high precision DATETIME field
Original Reporter info from Mantis: Cuz
-
Reporter name: Ryan Smith
Original Reporter info from Mantis: Cuz
- Reporter name: Ryan Smith
Description:
In MySQL/MariaDB it is possible to specify a high-precision DATETIME field with a value like this:
2019-09-09 09:48:13.999870
When the TSQLQuery.Next tries to read that record, it fails to parse that to TDateTime since it tries to dismantle the time into its constituent parts, and ends up rounding the milliseconds to 1000 (as opposed the correct 999) which then gets passed to EncodeTime() which in turn raises an Exception because milliseconds has to be a fraction of seconds and cannot be 1000 - which is a full second in itself.
See the attached image for the error message itself.
Steps to reproduce:
In any MariaDB/MySQL server which supports high-precision DateTime types (MariaDB 10.3+ will do) on any platform, create a table with such a high-precision datetime field:
CREATE TABLE t(id INT, dt1 DATETIME(6));
Insert some values, ensuring to add some very high millisecond numbers:
INSERT INTO t (id, dt1) VALUES
(1, '2019-09-09 09:48:13.992270')
,(2, '2019-09-09 09:48:13.999870') -- This record (and only this) will be a problem.
,(3, '2019-09-09 09:48:13.998970')
;
Query that table simply with 'SELECT * FROM t;' using a TSQLQuery cursor. When TSQLQuery.Next; is called for the second record, an Exception will be raised.
PS: I'm not posting code to do the actual query-stepping since it's very verbose and completely common and whomever is reading this probably have their own preferred method - Any method will do as long as a TSQLQuery.Next is in there somewhere.
Additional information:
This error is obscure, but happens consistently once triggered by such a DB value.
It's a simple rounding problem, and should be fixed in minutes.
It's somewhat irritating, but not detrimental, one can work around it by forcing the query to output less precise Date-Time values:
SELECT id, CAST(dt1 AS DATETIME);