View Issue Details

IDProjectCategoryView StatusLast Update
0037849PackagesDatabasepublic2020-10-09 20:19
ReporterRyan Smith Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status newResolutionopen 
PlatformAnyOSAny 
Product Version2.0.10 
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:
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 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:
SELECT id, CAST(dt1 AS DATETIME);
TagsNo tags attached.
Fixed in Revision
LazTarget
Widgetset
Attached Files

Activities

Ryan Smith

2020-10-01 03:40

reporter  

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

LacaK

2020-10-01 07:37

developer   ~0126013

Simplest fix would be replace:
1047 Result := ComposeDateTime(Result, EncodeTime(EH, EN, ES, EMS));
with:
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(...)
from:
> Result := Round(MSecs);
to:
> Result := Trunc(MSecs);

(That's line 1008 in my current version of the file mysqlconn.inc)

Bart Broersma

2020-10-01 12:50

developer   ~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.

LacaK

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 ?

LacaK

2020-10-07 19:11

developer   ~0126129

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

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

Juha Manninen

2020-10-09 20:19

developer   ~0126192

Somebody with commit rights to FPC sources please apply.

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: dateutil.inc.diff
2020-10-07 19:11 LacaK File Added: mysqlconn.inc.patch
2020-10-09 20:19 Juha Manninen Note Added: 0126192