System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: fb9e4b30874d83042e09c2f791d6065fc5e73a4b
Title: Different double value returned for 1.0.109.1 and earlier versions
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Native_Assembly Resolution: Works_As_Designed
Last Modified: 2018-08-26 22:02:59
Version Found In: 1.0.109.1
User Comments:
anonymous added on 2018-08-19 15:59:25:
Following code returns different value on 1.109.1 and previous versions (tested 107, 108, microsoft's sqlite provider)

SQL: SELECT Cast(-1.79E+308 as bit)

C#: dataReader.GetDouble(0)

results:
Expected: -1.7900000000000008E+308d
But was:  -1.7900000000000002E+308d

anonymous added on 2018-08-19 16:37:21:
Ok, I suspect it is a change in sqlite itself, as it also fails with same error for 2.1.1 version of ms provider.

anonymous added on 2018-08-20 16:20:53:
If it will help, old behaviour observed on versions up to 3.19.3
new behaviour observed starting from at least 3.22.0

mistachkin added on 2018-08-20 20:03:53:
The differences here can be accounted for by the limited precision of the double
type.  Values of the double type are approximate.  Given the limitations of MSVC,
there was a change to the SQLite core library that modified how string to double
conversions are performed; however, the resulting double values are still within
the margin of error for the double type.

anonymous added on 2018-08-22 10:59:16:
Agree with that. But after I've changed test to use round-trip format for literal, I still get another value:
SELECT -1.7900000000000002E+308
returns -1.7900000000000004E+308

mistachkin added on 2018-08-26 00:22:24:
Do you have some C# sample code and a database schema that shows the issue?

anonymous added on 2018-08-26 14:39:29:
Yes, you can use this code to test (no database schema required as it doesn't use any tables):

var cmd = conn.CreateCommand();
var value = -1.7900000000000002E+308;
// SELECT CAST(-1.7900000000000002E+308 as real)
cmd.CommandText = FormattableString.Invariant($"SELECT CAST({value:G17} as real)");
using (var rd = cmd.ExecuteReader())
{
	rd.Read();
	var valueFromDB = rd.GetDouble(0);

	// -1.790000000000001E+308d != -1.7900000000000002E+308
	Assert.AreEqual(value, valueFromDB);
}

I use G17 double roundtrip format, but looks like parser cannot restore value from text properly

mistachkin added on 2018-08-26 22:02:59:
Ok.  In this case, using a bound parameter and the actual double value should
permit it to be round-tripped.

When reading the value of the database, using GetDouble should be fine.