sqlite: Empty column in query result when the value of the first row is NULL

[qwenger]

Hi,

Consider the following sqlite table:

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT
);
INSERT INTO person (name) VALUES (NULL), ('Steven');

Then consider the following queries with datafusion-table-providers:

use std::sync::Arc;
use std::time::Duration;
use datafusion::prelude::SessionContext;
use datafusion_table_providers::{
	common::DatabaseCatalogProvider,
	sql::db_connection_pool::{
		Mode,
		sqlitepool::SqliteConnectionPoolFactory,
	},
};
#[tokio::main]
async fn main() {
    let sqlite_pool = Arc::new(
        SqliteConnectionPoolFactory::new(
            "test.db",
            Mode::File,
            Duration::from_millis(5000),
        ).build().await.unwrap(),
    );
    let catalog_provider = DatabaseCatalogProvider::try_new(sqlite_pool).await.unwrap();
    let ctx = SessionContext::new();
    ctx.register_catalog("sqlite", Arc::new(catalog_provider));
    ctx
        .sql("SELECT * FROM sqlite.main.person WHERE id != 1").await.unwrap()
        .show().await.unwrap();
    ctx
        .sql("SELECT * FROM sqlite.main.person ORDER BY id DESC").await.unwrap()
        .show().await.unwrap();
    ctx
        .sql("SELECT * FROM sqlite.main.person").await.unwrap()
        .show().await.unwrap();
    ctx
        .sql("SELECT * FROM sqlite.main.person OFFSET 1").await.unwrap()
        .show().await.unwrap();
}

Output:

+----+--------+
| id | name   |
+----+--------+
| 2  | Steven |
+----+--------+
+----+--------+
| id | name   |
+----+--------+
| 2  | Steven |
| 1  |        |
+----+--------+
+----+------+
| id | name |
+----+------+
| 1  |      |
| 2  |      |
+----+------+
+----+------+
| id | name |
+----+------+
| 2  |      |
+----+------+

Notice that in the last two query results, name is output as None for id = 2, but should be Some("Steven").

EDIT: I'm aware that this is a particular consequence of sqlite's flexible typing™ (anti-)feature, which means that we probably cannot do much better for column type inference than heuristics based e.g. on the first row + assumption that the data source is sufficiently well-behaved. Alas, the latter doesn't always hold for real-world scenarios (in my use case, there are often NULLs on the first row and even the indicative column type is frequently omitted; I have no control over the source DBs).

EDIT2: There's worse: if the first value is NULL, the inferred type is text. So if the non-NULL values in the column are of another type (say integer), they will always be output as None. Even the above tricks (e.g. filtering by non-NULL or ordering so that the first row value is non-NULL) don't help in that case, which is quite dramatic. Is there something we can do to at least retrieve some data? E.g. one of
A) parsing rusqlite's indicative column decl_type to increase the chances of getting the correct type inferred
B) force-casting all values to string (or binary?)
C) using a compound enum-like type capable of encapsulating any of sqlite's storage classes
...but perhaps that's outside of this library's scope...

[phillipleblanc]

@qwenger yeah, this isn't ideal. I think we could extend the schema inference capabilities for SQLite to sample more rows before coming up with a type list and/or allowing the user to pass in a specific schema if they know it up front.

I also think we could probably use the variant work that Arrow has been working on lately to get the correct type per row, even if it changes per row.