Issues
- Running Unit tests against SQLite fails on numerous (22) datetime/UTC related tests.NH-4093Resolved issue: NH-4093Frédéric Delaporte
- AsyncGenerator creates unused private static event handler in SQLite20Driver.NH-4092Resolved issue: NH-4092
- SQL Server CE allocates too much memory with blob and sql prepareNH-4091Resolved issue: NH-4091
- Prepare SQL fails with time parameters and SQL Server 2008+NH-4090Resolved issue: NH-4090Frédéric Delaporte
- insert and update default value for property differ from its documentation.NH-4089
- Dialect.GetCastTypeName is buggyNH-4088Resolved issue: NH-4088Frédéric Delaporte
- Decimal truncation occurs after 5 digitsNH-4087Resolved issue: NH-4087
- TimeType may lose fractional secondsNH-4086Resolved issue: NH-4086Frédéric Delaporte
- SchemaUpdate not working with Views or Synonym TablesNH-4085Resolved issue: NH-4085Alex Zaytsev
- DbTimestamp cause stale update exceptionNH-4084Resolved issue: NH-4084Frédéric Delaporte
- ODBC nvarchar parameter corruptionNH-4083Resolved issue: NH-4083Frédéric Delaporte
- Load virtual assembly for mappingNH-4082Resolved issue: NH-4082
- Load virtual assembly for mappingNH-4081Resolved issue: NH-4081
- Load virtual assembly for mappingNH-4080Resolved issue: NH-4080Alex Zaytsev
- Component mapping without explicit column/table names causes column/table overlapNH-4079
- LINQ fetched collections aren't cachedNH-4078Resolved issue: NH-4078
- Possible race condition in ActionQueue.ExecuteActionsNH-4077Resolved issue: NH-4077Frédéric Delaporte
- Do not resurrect sessionNH-4076Resolved issue: NH-4076Frédéric Delaporte
- Remove code obsolete in 4.xNH-4075Resolved issue: NH-4075Frédéric Delaporte
- Found the unexpected error Probable I/O race condition detected while copying memoryNH-4074Resolved issue: NH-4074
- Replace NHibernate.Web.Example with modern versionNH-4073Resolved issue: NH-4073
- Add parameter of FormattableString as an overload to CreateSQLQueryNH-4072Resolved issue: NH-4072
- Add interface for ignoring specific property names to ConventionModelMapperNH-4071
- Add some missing function overrides for MsSqlCeNH-4070
- Source index the PDB file to enable VS debugger to step into code from githubNH-4069Resolved issue: NH-4069
- Support customized SQL formater for SchemaExportNH-4068
- NuGet package doesn't include pdb fileNH-4067Resolved issue: NH-4067
- Two UsesReflectonOptimizer tests build in release mode are now failing on newer Windows and/or .NET frameworks.NH-4066Resolved issue: NH-4066
- MsSql Dialect + ODBC, mapping an nvarchar(max) using string(10000) causes string truncationNH-4065Resolved issue: NH-4065Frédéric Delaporte
- Unmerge Antrl3.RuntimeNH-4064Resolved issue: NH-4064
- Fix ODBC failing testsNH-4063Resolved issue: NH-4063Frédéric Delaporte
- Properly handle Oracle Unicode support dual modelNH-4062Resolved issue: NH-4062Frédéric Delaporte
- Pointless Take call wrecks Oracle dialect paging.NH-4061
- Exception when linq select query returns nullNH-4060
- Query by types, where Id field has System.Object type, thrown "Invalid join" exceptionNH-4059
- Fix Oracle managed failing testsNH-4058Resolved issue: NH-4058Frédéric Delaporte
- Fix tests for MySqlNH-4057Resolved issue: NH-4057Frédéric Delaporte
- Intercept entity (Interceptor.OnSave) with new Version field valueNH-4056
- Better logging on StaleObjectStateException: exception message with Version infoNH-4055
- HQL multi-table update failure when using a HQL functionNH-4054
- ISQLQuery - SetParameter doesn't work with more than 10 parametersNH-4053
- Collect schema validation exceptionsNH-4052Resolved issue: NH-4052Frédéric Delaporte
- Replace System.Linq.Dynamic with System.Linq.Dynamic.Core in testsNH-4051Resolved issue: NH-4051Alex Zaytsev
- Use Task.Run instead of BeginInvoke in testsNH-4050Resolved issue: NH-4050Frédéric Delaporte
- EmitUtil can be cleaned upNH-4049Resolved issue: NH-4049Alex Zaytsev
- Supports non-deterministic/db-side-only methods in LinqNH-4048
- SQL 2016 DialectNH-4047Resolved issue: NH-4047Alex Zaytsev
- Default length too short for variable length types with SAP Anywhere / ASENH-4046Resolved issue: NH-4046
- ToFuture/ToFutureValue should fall-back on providers without INhQueryProviderNH-4045Resolved issue: NH-4045
- Nhibernate - The same in parameter value will be applied with different name to different future queriesNH-4044Resolved issue: NH-4044
Running Unit tests against SQLite fails on numerous (22) datetime/UTC related tests.
Description
Environment
Attachments
Details
Details
Assignee
Reporter
Labels
Components
Affects versions
Priority
Who's Looking?
Activity
Alex Zaytsev October 13, 2017 at 12:12 AM
Could you please continue the conversation at #1362
Frédéric Delaporte October 12, 2017 at 8:04 PMEdited
it needs to support the edge cases like this for any included types
Only if this can be done in a non hackish, reasonable way. It really looks like an inconsistency in SQLite implementation, that needs to be addressed on its side.
Are there other databases or ADO.Net drivers that do anything like this for non-offset DateTime values?
I have tested the types tests with all the others databases tested with TeamCiry, and with this locally with my French offset, no issues, unlike SQLite.
Since storing DateTime with a UTC marker doesn't really seem like an SQL thing to do, and since all the databases are SQL, then it would probably be best to store all DateTime values as Unspecified, as that was the previous behavior.
I do not believe previous behavior was doing what you write. Previously, the date and time types were not much tested. I think the issue was already there but unnoticed.
Why not forcing the date kind to Unspecified
when setting them for avoiding having some databases storing its offset, since the NHibernate DateTimeType
type is supposed to be offset agnostic.
It fixes all DateTimeType
tests.
But for LocalDateTimeType
(which works anyway without this) and UtcDateTimeType
(which currently fails), this is more debatable. This later type is for explicitly sending UTC datetime to the database. So sending to it unspecified datetime just for dodging what really looks like a SQLite bug sounds wrong to me.
Then on reading, NHibernate will assume all DateTimes be Unspecified as well (ignore anything passed from the database).
Already the case for UTC and local NHibernate types, just left untouched otherwise. For my test I have changed a method common to both reading and writing for forcing dates to Unspecified
when the type is neither UTC nor local, so it was also forcing it back to Unspecified
on reads.
I think this issue needs to be fixed on SQLite side. (It is maybe more a System.Data.SQLite issue.)
At the very worst, maybe do the change I have tested (remove the ternary in this line for always forcing kind) then add an AdjustCommand
override in SQLite driver for forcing all its datetime parameter to Unspecified
even if they were Utc
or Local
. But that would be a crutch for dodging a SQLite issue, crutch which may cause issues if things change on SQLite side.
Nathan Brown October 12, 2017 at 7:08 PMEdited
Hmm. That's interesting... I do think that since SQLite is a supported, tested, database for NHibernate, it needs to support the edge cases like this for any included types.
Are there other databases or ADO.Net drivers that do anything like this for non-offset DateTime values?
Since storing DateTime with a UTC marker doesn't really seem like an SQL thing to do, and since all the databases are SQL, then it would probably be best to store all DateTime values as Unspecified, as that was the previous behavior. Then on reading, NHibernate will assume all DateTimes be Unspecified as well (ignore anything passed from the database).
Usually the goal for having a UTC DateTime column in a database is that the developer is acknowledging that timezone offset wasn't stored, but he promises all the values are in the UTC timezone (for reading) and intends to store them in that timezone.
Frédéric Delaporte October 12, 2017 at 11:39 AMEdited
SQLite default date storage format is ISO8601. There is a gotcha with it: when SQLite receives a local or unspecified kind date, it stores it without any assumption on the time zone. When it receives an UTC one, it stores it with the UTC marker. Why not, sounds rather good.
Trouble arises when reading them back: SQLite converts them all back to local time. So, for dates saved with local or unspecified kind, it keeps them as is. But for UTC dates, it converts them back to local on reading. And worst, it yields them with unspecified kind, leaving us no chance to detect the case and convert them back. (Even with a correct kind, we would not be able to handle converting them back to UTC in all cases, due to ambiguous local time, like 2017-10-29 02:30:00
in France which could be 2017-10-29 01:30:00
UTC or 2017-10-29 00:30:00
UTC, due to time shift backward occurring at 2017-10-29 01:00:00
UTC that day.)
All that terribly looks as an external issue...
The teamcity build has no troubles since its local time zone is UTC.
SQLite has several options about DateTime storage & handling. Its storage format can be changed (some do not handle any time zone indication), and its read kind can be changed (default is Unspecified
, but as written above, with an automatic conversion of UTC dates to local beforehand). That further complicates the issue.
DateTimeFormat
:
ISO8601
: distinguishes between UTC dates and others.JulianDay
: no differences between UTC dates and others, but only stores milliseconds. Avoids the issue, at the cost of a loss of fractional seconds precision.Ticks
: no differences between UTC dates and others, but said as here only for legacy, not recommended by documentation. Avoids the issue.UnixEpoch
: no tested but likely likeJulianDay
but only storing seconds.InvariantCulture
: stores the offset with local date. Even more failures due to version check, dates being read back as unspecified.CurrentCulture
: not tested.
About Ticks
, the documentation writes:
This value is not recommended and is not well supported with LINQ.
Ticks less compatible with 3rd party tools that query the database, and renders the DateTime field unreadable as text without post-processing.
Ticks is mainly present for legacy code support.
So dodging the issue (for tests with a box not using UTC as local time) by using Ticks
sounds not good. It actually causes other tests to start failing.
DateTimeKind
: it would be quite hackish to play with this one, since currently the NHibernate date types completely ignore read DateTime kind. Most data providers yield it as Unspecified
. But indeed it would work to set it to Utc
, if we change the read logic to force the datetime back to unspecified with the non local/utc NHibernate date types. (Without a change to read logic, version checks fail due to read value being specified UTC while db value is not.) This DateTimeKind
only alters reading, not writing. (At least it does not cause written local datetime to be converted to UTC.) So it eliminates this conversion of UTC to local on reading which causes us issues.
All that sounds too hackish to me for doing such changes.
Another option could be to override SQLite driver for switching to Unspecified
all dates parameters, in order to avoid having their offset/utc flag stored when the underlying SQLite date is able of storing it. This is still a hack for compensating SQLite behavior.
Overall I think this is a SQLite issue, NHibernate code should not try to fix it.
So what to do with those tests failing on boxes having a local time other than UTC? It it worth coding some ignore logic for them? Or just let them fail, since it does not hinder TeamCity?
Running the tests against SQLite locally on my machine fails 22 tests, while the same commits are passing on TeamCity.
I noticed that the tests have to do with dates and specifically UTC times. For example:
1) Failed : NHibernate.Test.TypesTest.DateTimeTypeFixture.ReadWrite(Utc) Unexpected value. Expected: 2017-10-13 03:23:31.0094937 But was: 2017-10-12 20:23:31.0094937 at NHibernate.Test.TypesTest.AbstractDateTimeTypeFixture.ReadWrite(DateTimeKind kind) in C:\dev\OSS\nhibernate-core\src\NHibernate.Test\TypesTest\AbstractDateTimeTypeFixture.cs:line 176
My computer is in MST or GMT-7:00 timezone.
Attached are all the failures.