Sqlite: InvalidCastException if the first value in a column is NULL followed by any other value #642
Description
It seems like Dapper uses the first value in a column to decide what type the rest of the values will have.
If the first value is NULL, this fails spectacularly.
I have set up a repository demonstrating this bug:
https://github.com/CheeseSucker/DapperSqliteBug
Tested dapper versions: 1.50.0, 1.50.2, 1.50.3-beta1
Relevant code:
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.Execute("CREATE TABLE MyTable (MyValue INTEGER)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (NULL)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");
// This is fine
var result1 = connection.Query<MyModel>("SELECT * FROM MyTable");
// This is also fine
var result2 = connection.Query<MyModel>("SELECT * FROM MyTable ORDER BY MyValue IS NULL ASC");
// This will fail because NULL is the first value in the column
var result3 = connection.Query<MyModel>("SELECT * FROM MyTable ORDER BY MyValue IS NULL DESC");
// InvalidCastException has been encountered before this line
connection.Close();
}class MyModel
{
public long MyValue;
}