TypeInfo missing on Column but available on SqliteValueRef.

[L020Isry8fuLjSL7r0Gmxw]

note: This started out as an issue, but in the process of proving I had tried everything on my own I discovered a work around. Now it is just interesting rather than a problem.

I was trying to run some adhoc queries using the sqlite driver and could not find type information when query did not involve a table. However if I get use the raw SqliteValueRef then I can get type information.

This is not a problem for me because I can read the types from the try_get_raw approach.

I searched the issues and it sounds like my problem could be related to #1246 or #1524. Basically when people on sqlite are running into column type is null but expect X, it could be the result of checking one column type API and not the other one.

minimal example

sqlite3 test.sqlite 'create table numbers(x INTEGER NOT NULL, y INTEGER)'
sqlite3 test.sqlite 'insert into numbers(x, y) values (4,4)'

Cargo.toml

[package]
name = "sqlx-bug"
version = "0.1.0"
edition = "2021"

[dependencies]
sqlx = { version = "0.5.11", features = [ "sqlite", "macros", "runtime-tokio-rustls" ] }
tokio = { version = "1.6", features = ["full"] }

src/main.rs

use sqlx::{Row, SqlitePool, Column, TypeInfo, ValueRef};

#[tokio::main]
async fn main() {
    let pool = SqlitePool::connect("./test.sqlite").await.unwrap();

    let row = sqlx::query("select 4").fetch_one(&pool).await.unwrap();
    let column = row.column(0);
    println!("reported type: {:?}", column.type_info());
    println!("{:?}", row.try_get::<String, _>(0));
    println!("{:?}", row.try_get::<i32, _>(0));

    let value = row.try_get_raw(&0).unwrap();
    if !value.is_null() {
        let ty = value.type_info();
        println!("reported type: {:?}", ty);
    }

    let row = sqlx::query("select x from numbers").fetch_one(&pool).await.unwrap();
    let column = row.column(0);
    println!("reported type: {:?}", column.type_info());

    let row = sqlx::query("select y from numbers").fetch_one(&pool).await.unwrap();
    let column = row.column(0);
    println!("reported type: {:?}", column.type_info());
}

Output

reported type: SqliteTypeInfo(Null)
Err(ColumnDecode { index: "0", source: "mismatched types; Rust type `alloc::string::String` (as SQL type `TEXT`) is not compatible with SQL type `INTEGER`" })
Ok(4)
reported type: SqliteTypeInfo(Int)
reported type: SqliteTypeInfo(Int64)
reported type: SqliteTypeInfo(Int64)