Sqlite mismatched types error

[mrmykey]

sqlx 6.1 fails when running SQLite returning query with a float which ends with 0. Works with 1.1, fails with 1.0

// CREATE TABLE data_table(id INTEGER PRIMARY KEY AUTOINCREMENT, number REAL);
#[derive(sqlx::FromRow)]
struct DataTable { id: i64, number: f64 }

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect("sqlite:app.db").await?;
    let sql = "insert into data_table (number) values (?) returning id, number";
    let _result = sqlx::query_as::<_, DataTable>(sql).bind(1.0).fetch_one(&pool).await?;
    Ok(())
}

Error: ColumnDecode { index: "\"number\"", source: "mismatched types; Rust type `f64` (as SQL type `REAL`) is not compatible with SQL type `INTEGER`" }

[abonander]

This looks to be the same as #1596 which I thought was fixed already, but I am able to reproduce this.

[abonander]

Added a followup to my original bug report on the SQLite forum: https://sqlite.org/forum/forumpost/712251ee03

[predmijat]

I think I've got bitten by this one too:

"mismatched types; Rust type `f32` (as SQL type `REAL`) is not compatible with SQL type `INTEGER`"

after inserting 5.0 with RETURNING *.

Last post on https://sqlite.org/forum/forumpost/712251ee03 is from 2022-09-13 with no response, am I doing something wrong?

sqlx 0.6 sqlite3 3.39.5

[drager]

Just hit this bug using latest sqlx :(

[fortunewang]

I create a wapper of f64 to avoid this problem temporarily:

use sqlx::Sqlite;
use std::fmt::{self, Debug, Display};

#[derive(Clone, Copy, PartialEq)]
pub struct Float(pub f64);

impl Debug for Float {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        fmt::Debug::fmt(&self.0, f)
    }
}

impl Display for Float {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        fmt::Display::fmt(&self.0, f)
    }
}

impl sqlx::Type<Sqlite> for Float {
    fn type_info() -> <Sqlite as sqlx::Database>::TypeInfo {
        <f64 as sqlx::Type<Sqlite>>::type_info()
    }

    fn compatible(ty: &<Sqlite as sqlx::Database>::TypeInfo) -> bool {
        use sqlx::TypeInfo;

        match ty.name() {
            "INTEGER" => true,
            _ => <f64 as sqlx::Type<Sqlite>>::compatible(ty),
        }
    }
}

impl<'q> sqlx::Encode<'q, Sqlite> for Float {
    fn encode_by_ref(
        &self,
        args: &mut <Sqlite as sqlx::database::HasArguments<'q>>::ArgumentBuffer,
    ) -> sqlx::encode::IsNull {
        args.push(sqlx::sqlite::SqliteArgumentValue::Double(self.0));
        sqlx::encode::IsNull::No
    }
}

impl<'r> sqlx::Decode<'r, Sqlite> for Float {
    fn decode(
        value: <Sqlite as sqlx::database::HasValueRef<'r>>::ValueRef,
    ) -> Result<Self, sqlx::error::BoxDynError> {
        use sqlx::{TypeInfo, ValueRef};

        match value.type_info().name() {
            "INTEGER" => Ok(Self(
                <i32 as sqlx::Decode<'_, Sqlite>>::decode(value)? as f64
            )),
            _ => Ok(Self(<f64 as sqlx::Decode<'_, Sqlite>>::decode(value)?)),
        }
    }
}

impl serde::ser::Serialize for Float {
    fn serialize<S>(&self, serializer: S) -> Result<S::Ok, S::Error>
    where
        S: serde::Serializer,
    {
        serializer.serialize_f64(self.0)
    }
}

[pbalaga]

An easier workaround I got working is returning the number as real explicitly from sqlite:

insert into data_table (number) values (?) returning id, cast(number as real) as number

[abonander]

This should be fixed in SQLite itself now: https://sqlite.org/src/info/4711fb69547f4f17

[abonander]

Actually I just saw my own comment suggesting that this may have regressed.