View Issue Details

IDProjectCategoryView StatusLast Update
0037849FPCDatabasepublic2021-01-24 13:42
ReporterRyan Smith Assigned ToMichael Van Canneyt  
Status resolvedResolutionfixed 
Fixed in Version3.3.1 
Summary0037849: TSQLQuery.Next Exception for MySQL when encountering a high precision DATETIME field
DescriptionIn 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 ReproduceIn 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:

Insert some values, ensuring to add some very high millisecond numbers:
 (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 InformationThis 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:
TagsNo tags attached.
Fixed in Revision48381
Attached Files


Ryan Smith

2020-10-01 03:40


image.png (5,765 bytes)   
image.png (5,765 bytes)   


2020-10-01 07:37

developer   ~0126013

Simplest fix would be replace:
1047 Result := ComposeDateTime(Result, EncodeTime(EH, EN, ES, EMS));
1047 Result := ComposeDateTime(Result, TDateTime(EH*3600000+EN*60000+ES*1000+EMS)/MSecsPerDay);
Drawback is that internal representation of TDateTime values is handled directly

Ryan Smith

2020-10-01 12:25

reporter   ~0126019

I believe the correct fix to avoid the mentioned drawback, is to simply change the Result line in TConnectionName.StrToMSecs(...)
> Result := Round(MSecs);
> Result := Trunc(MSecs);

(That's line 1008 in my current version of the file

Bart Broersma

2020-10-01 12:50

reporter   ~0126020

Trunc(MSecs) would turn 998.90 ms into 998 ms, which is obviously wrong.

Ryan Smith

2020-10-01 13:08

reporter   ~0126022

I can agree with that - Accuracy should always be the goal.
Then the optimal solution should be to compute the full time in floating point space, such as LacaK suggested, or to improve EncodeTime(); to be accepting of 1000 ms rounded values.

Something has to give, we can't have it both ways.


2020-10-01 18:50

developer   ~0126025

There is EncodeTimeInterval function in DateUtils. But it still checks for MSec<1000 (Only Hours are allowed to be > 24).
May be, we can relax this function to allow MSec<=1000 ?


2020-10-07 19:11

developer   ~0126129

Attached patchs for and
IMO this is simplest solution. (353 bytes)   
@@ -1885,7 +1885,7 @@

 function TryEncodeTimeInterval(Hour, Min, Sec, MSec: word; out Time: TDateTime): boolean;
- Result:= (Min<60) and (Sec<60) and (MSec<1000);
+ Result:= (Min<60) and (Sec<60) and (MSec<=1000);
  If Result then
 end; (353 bytes) (526 bytes)   
Index: src/sqldb/mysql/
--- src/sqldb/mysql/	(revision 47062)
+++ src/sqldb/mysql/	(working copy)
@@ -1044,7 +1044,7 @@
     Result := 0
     Result := EncodeDate(EY, EM, ED);
-  Result := ComposeDateTime(Result, EncodeTime(EH, EN, ES, EMS));
+  Result := ComposeDateTime(Result, EncodeTimeInterval(EH, EN, ES, EMS));
 function TConnectionName.InternalStrToTime(C: pchar; Len: integer): TDateTime; (526 bytes)   

Juha Manninen

2020-10-09 20:19

reporter   ~0126192

Somebody with commit rights to FPC sources please apply.

Michael Van Canneyt

2021-01-24 13:42

administrator   ~0128555

Last edited: 2021-01-24 13:42

View 2 revisions

Applied as is, I think to allow 1000 msecs in a timeperiod is OK.

Issue History

Date Modified Username Field Change
2020-10-01 03:40 Ryan Smith New Issue
2020-10-01 03:40 Ryan Smith File Added: image.png
2020-10-01 07:37 LacaK Note Added: 0126013
2020-10-01 12:25 Ryan Smith Note Added: 0126019
2020-10-01 12:50 Bart Broersma Note Added: 0126020
2020-10-01 13:08 Ryan Smith Note Added: 0126022
2020-10-01 18:50 LacaK Note Added: 0126025
2020-10-07 19:11 LacaK Note Added: 0126129
2020-10-07 19:11 LacaK File Added:
2020-10-07 19:11 LacaK File Added:
2020-10-09 20:19 Juha Manninen Note Added: 0126192
2021-01-22 14:55 Maxim Ganetsky Project Packages => FPC
2021-01-22 22:55 Michael Van Canneyt Assigned To => Michael Van Canneyt
2021-01-22 22:55 Michael Van Canneyt Status new => assigned
2021-01-24 13:42 Michael Van Canneyt Status assigned => resolved
2021-01-24 13:42 Michael Van Canneyt Resolution open => fixed
2021-01-24 13:42 Michael Van Canneyt Fixed in Version => 3.3.1
2021-01-24 13:42 Michael Van Canneyt Fixed in Revision => 48381
2021-01-24 13:42 Michael Van Canneyt FPCTarget => 3.2.2
2021-01-24 13:42 Michael Van Canneyt Note Added: 0128555
2021-01-24 13:42 Michael Van Canneyt Note Edited: 0128555 View Revisions