Sqlite Provider InvalidCastException

[MarkZither]

When using the Sqlite Provider i encountered the following error.

An unhandled exception occurred while processing the request.
InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Double'.
Deserialize0693dad1-08a7-4b56-b158-09ed5699f3a7(IDataReader )

DataException: Error parsing column 5 (StartMilliseconds=120 - Int64)
Dapper.SqlMapper.ThrowDataException(Exception ex, int index, IDataReader reader, object value) in SqlMapper.cs, line 3665

This is similar to the final message in the PR #221

It seems to stem from the dynamic datatypes of Sqlite.

[MarkZither]

I seem to have resolved this with a change to the LoadSql method to cast the 2 columns DurationMilliseconds and StartMilliseconds to REAL

snip ...
  SELECT Id as IdString,
         MiniProfilerId as MiniProfilerIdString,
         ParentTimingId as ParentTimingIdString,
         Name,
         CAST(DurationMilliseconds AS REAL) AS DurationMilliseconds,
         CAST(StartMilliseconds AS REAL) AS StartMilliseconds,
         IsRoot,
         Depth,
         CustomTimingsJson
    FROM {MiniProfilerTimingsTable} 
   WHERE MiniProfilerId = @id 
ORDER BY StartMilliseconds;
... snip

Which based on https://stackoverflow.com/questions/39980840/sqlite-using-dapper-error-parsing-column-unable-to-cast-object-of-type-system is a known problem.

I don't fully understand why CAST(StartMilliseconds AS NUMERIC) end up with nulls while CAST(StartMilliseconds AS REAL) works, but I will follow up with a PR.

[mishael-o]

Is there a work around for this?

[NickCraver]

@bricelam Could you chime in on this one please? Any downside to changing to REAL you're aware of?

[bricelam]

Is it possible to call DbDataReader.GetDecimal instead of GetValue (or the indexer)? This would ensure that the values can roundtrip without precision loss.

If you're OK with precision loss, using REAL for the column type is acceptable. This causes the database to cast the values (from 128-bit floating-point TEXT values to 64-bit floating-point REAL values) when the values are inserted.

Alternatively, you could cast the decimal values to double before passing them as parameters. This would have the same effect as using REAL in the database.

NUMERIC doesn't work because it only casts the value if it can do so without precision loss.

[bricelam]

Wait, I may not have the full picture... lol, is decimal even involved?

[bricelam]

Ah, I think I see now. Yes, the column type decimal(15,3) is effectively the same as NUMERIC. This means it will cast from REAL to INTEGER if it can do so without loss. This leads to both INTEGER and REAL values being stored in the database.

Calling DbDataReader.GetValue (or using the indexer) will let the values determine their own types. All of this leads to long values being returned for rows without any decimal places. On the other hand, calling GetDouble would always return a double regardless of the stored type.

I strongly encourage users to only ever use TEXT, INTEGER, REAL and BLOB as column types. There are a handful of awful cases just like this if you don't. (The worst being STRING casting to INTEGER if it can.)

[bricelam]

It's also worth noting that precision, scale, and length on column types have no effect on SQLite.

[MarkZither]

Is my change ok to be accepted?

[meum]

I also ran into this one today, would be great to get the PR merged

[NickCraver]

I happen to be working on this after a long break just this week - this will be in a 4.3 release soon :)

[NickCraver]

Going to close this our since #582 is merged in - look out for a 4.3 release on NuGet soon, but already on MyGet now :)