Rust type core::option::Option<alloc::string::String> is not compatible with SQL type NULL

[lalithsuresh]

Bug Description

When using the AnyConnection driver to access an SQLite DB, I am unable to read rows with NULL values. It appears sqlx parses the column type as NULL.

Example:

Rust type `core::option::Option<alloc::string::String>` is not compatible with SQL type `NULL`

Minimal Reproduction

A small code snippet or a link to a Github repo or Gist, with instructions on reproducing the bug.

Test to reproduce the problem:

➜  /tmp sqlite3 x.sqlite
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> create table t1(c1 varchar, c2 varchar);
sqlite> insert into t1(c1) values ('xyz');
sqlite> select * from t1;
xyz|
sqlite>

#[test]
fn test() {
    task::block_on(async {
        sqlx::any::install_default_drivers();
        let mut conn =  AnyConnection::connect("sqlite:///tmp/x.sqlite").await.unwrap();
        let results = sqlx::query("select * from t1").fetch_all(&mut conn).await.unwrap();
        let row = results.get(0).unwrap();
        let c1: Option<String> = row.get(0); // works
        let c2: Option<String> = row.get(1); // doesn't work
        println!("{:?} {:?}", c1, c2);
    });
}

Running the test yields:

thread 'db::test' panicked at 'called `Result::unwrap()` on an `Err` value: ColumnDecode { index: "1", source: "mismatched types; Rust type `core::option::Option<alloc::string::String>` is not compatible with SQL type `NULL`" }', /Users/foo/.cargo/registry/src/gh.neting.cc-1ecc6299db9ec823/sqlx-core-0.7.0-alpha.1/src/row.rs:74:37

Info

• SQLx version: 0.7.0-alpha.1
• SQLx features enabled: "runtime-async-std-native-tls", "sqlite", "postgres", "any"
• Database server and version: SQLite 3.37.0
• Operating system: macOS Monterey 12.6.1
• rustc --version: rustc 1.67.1 (d5a82bbd2 2023-02-07)

[abonander]

The fix is probably to add ty.is_null() || here: https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/types/mod.rs#L203

[lalithsuresh]

@abonander is there a workaround for now? I tried to override the types and that yielded the same error as wwell (sqlx::query(r#"select c1, c2 as "c2?: String" from t1"#).fetch_all(&mut conn).await.unwrap();)

Happy to test the fix you proposed too.

[gz]

The fix is probably to add ty.is_null() || here: https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/types/mod.rs#L203

FWIW I tried this and it doesn't resolve the issue.

[mathiversen]

I ran into the same issue for Option<i64>

[pxp9]

I have an idea, this wont solve the issue but it is a possible workaround.

You should do a manual impl FromRow for the type you want.

You should call try_get() in the Option if you get an error to decode discard it calling ok()

[pxp9]

The fix is probably to add ty.is_null() || here: https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/types/mod.rs#L203

Also it is needed to change impl<T> Decode for Option<T> NULL SQL type should be decoded as None variant

Edit:

It is already implemented in this code

[pxp9]

I have been researching about the issue,

and I think it should be is this impl

https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/any/row.rs#L49-L67

[pxp9]

okay i have found the issue,

The fix is probably to add ty.is_null() || here: https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/types/mod.rs#L203

This fix does not work because type Info for AnyTypeInfo is bug for NULL type , because is_null() method returns always false while it should be returning true when self.kind is Null variant.

[pxp9]

impl TypeInfo for AnyTypeInfo {
    fn is_null(&self) -> bool {
        self.kind == Null 
    }

    fn name(&self) -> &str {
        use AnyTypeInfoKind::*;

        match self.kind {
            Bool => "BOOLEAN",
            SmallInt => "SMALLINT",
            Integer => "INTEGER",
            BigInt => "BIGINT",
            Real => "REAL",
            Double => "DOUBLE",
            Text => "TEXT",
            Blob => "BLOB",
            Null => "NULL",
        }
    }
}

and

impl<T: Type<DB>, DB: Database> Type<DB> for Option<T> {
    fn type_info() -> DB::TypeInfo {
        <T as Type<DB>>::type_info()
    }

    fn compatible(ty: &DB::TypeInfo) -> bool {
        ty.is_null() || <T as Type<DB>>::compatible(ty)
    }
}

should solve the issue.

[pxp9]

After fixing this i got this issue,

Err value: ColumnDecode { index: ""error_message"", source: "expected TEXT, got Null" }

[pxp9]

The new issue is fixed changing is_null() implementation from this trait.

https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/any/value.rs#L71-L97

 fn is_null(&self) -> bool {
        matches!(self.kind, AnyValueKind::Null)
    }

[pxp9]

Also is broken this one

https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/any/value.rs#L99-L125