If:
1) SQLiteDataReader is reading multiple rows, and
2) a column is computed (or for some other reason sqlite3_column_decltype returns NULL), and
3) the first rows returned by the query contain NULL in that column
then the data type of that column is incorrectly set to DbType.String for rows that do actually contain data.
SQLiteDataReader.GetSQLiteType only starts from scratch (when auto-detecting the column's data type) on the very first row. If this row contains a null value, then sqlite3_column_type returns TypeAffinity.Null as the column affinity. SQLite3.ColumnType converts this value to "TEXT", and the column's type is set to DbType.String. Subsequent non-null, non-string values in the column do not change the type back to the right value. (A sample program that reproduces this is below. It's a problem in 1.0.45.0; I have not tested with any earlier version.)
I added a workaround for this problem. Replace SQLiteDataReader.cs, lines 912-913 with:
if (typ.Affinity == TypeAffinity.Uninitialized)
{
typ.Type = SQLiteConvert.TypeNameToDbType(_activeStatement._sql.ColumnType(_activeStatement, i, out typ.Affinity));
// If the initial values in this column are null, do not attempt to set the column's type yet.
// Set the affinity back to uninitialized so that we detect the actual type on the first non-null value.
if (typ.Affinity == TypeAffinity.Null)
typ.Affinity = TypeAffinity.Uninitialized;
}
This resets the column's affinity to uninitialised while initial null values are being read. The first non-null value fixes the column's affinity in exactly the same way that the old code used to.
Sample Code:
// ----------
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
namespace ExecuteScalar
{
class Program
{
static void Main(string[] args)
{
string strPath = @"C:\Temp\test.db";
File.Delete(strPath);
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = strPath;
using (SQLiteConnection conn = new SQLiteConnection(builder.ConnectionString))
{
conn.Open();
using (SQLiteCommand cmdCreate = new SQLiteCommand("create table test(id INTEGER NOT NULL, d1 REAL, d2 REAL)", conn))
cmdCreate.ExecuteNonQuery();
using (SQLiteCommand cmdInsert = new SQLiteCommand("insert into test values (1, null, null)", conn))
cmdInsert.ExecuteNonQuery();
using (SQLiteCommand cmdInsert = new SQLiteCommand("insert into test values (2, null, 1.5)", conn))
cmdInsert.ExecuteNonQuery();
using (SQLiteCommand cmdInsert = new SQLiteCommand("insert into test values (3, 2.5, null)", conn))
cmdInsert.ExecuteNonQuery();
using (SQLiteCommand cmdSelect = new SQLiteCommand("select CAST(coalesce(d1, d2) AS REAL) from test order by id", conn))
using (SQLiteDataReader reader = cmdSelect.ExecuteReader())
{
while (reader.Read())
{
object o = reader.GetValue(0);
Console.WriteLine("o = {0}; o.GetType = {1}", o, o.GetType());
}
}
}
}
}
}
// ----------
The program outputs:
o = ; o.GetType = System.DBNull
o = 1.5; o.GetType = System.String
o = 2.5; o.GetType = System.String
It should output (and with the patch above, does output):
o = ; o.GetType = System.DBNull
o = 1.5; o.GetType = System.Double
o = 2.5; o.GetType = System.Double