System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 8bdf96a1c0d88edc3b73ac4f984abd0d7013b16c
Title: SQLiteDataReader returns incorrect values
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Not_A_Bug
Last Modified: 2016-10-30 20:33:02
Version Found In: 1.0.102.0
User Comments:
anonymous added on 2016-10-30 17:10:38:
It's a core feature of SQLite to allow storing values of any type in any column. However, SQLiteDataReader doesn't always correctly handle this. See failed tests below.


SQLiteConnection connection = ...;

new SQLiteCommand("CREATE TABLE test_1 (col INTEGER);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_1 VALUES ('my string value');", connection).ExecuteNonQuery();
Assert.AreEqual("my string value", new SQLiteCommand("SELECT col FROM test_1;", connection).ExecuteScalar());
// FAIL - Expected: "my string value" - Actual: (long)0

new SQLiteCommand("CREATE TABLE test_2 (col REAL);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_2 VALUES ('my string value');", connection).ExecuteNonQuery();
Assert.AreEqual("my string value", new SQLiteCommand("SELECT col FROM test_2;", connection).ExecuteScalar());
// FAIL - Expected: "my string value" - Actual: (double)0

new SQLiteCommand("CREATE TABLE test_3 (col BLOB);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_3 VALUES (-1);", connection).ExecuteNonQuery();
Assert.AreEqual((long)-1, new SQLiteCommand("SELECT col FROM test_3;", connection).ExecuteScalar());
// FAIL - Expected: ((long)-1) - Actual: byte[] { 0x2D, 0x31 }


This also affects the SQLiteDataReader functions GetValue(int i), GetValues(), this[int i], and this[string name].

mistachkin added on 2016-10-30 18:57:50:
If you want to be able to store data of the "wrong type" in a column, you can
disable the type sanity checking using one of several connection flags, e.g.:

/// <summary>
/// When returning column values, always return them as though they were
/// plain text (i.e. no numeric, date/time, or other conversions should
/// be attempted).
/// </summary>
GetAllAsText = 0x100,

/// <summary>
/// When returning column values as a <see cref="String" />, skip
/// verifying their affinity.
/// </summary>
NoVerifyTextAffinity = 0x200000000,

/// <summary>
/// When returning column values, skip verifying their affinity.
/// </summary>
NoVerifyTypeAffinity = 0x4000000000,

anonymous added on 2016-10-30 19:52:34:
Ah, OK. Sorry for this false bug report then.

mistachkin added on 2016-10-30 20:33:02:
No problem.  I may need to enhance the documentation to make these kinds of data
type affinity issues a bit easier to deal with.

Thanks for the report.