Unclear how to use SUM(_) for BigInt columns in Sqlite

[drahnr]

I am slightly puzzled how to use sum(_) in queries and end up with typed results.

   // Note: The following does not work as anticipated for `BigInt` columns,
   // since `Foldable` for `BigInt` requires `Numeric`, which is only supported
   // on `postgres` and `MySQL` backends.
    // Also see <https://github.com/diesel-rs/diesel/discussions/4000>
    let maybe_nonce = SelectDsl::select(
        schema::account_deltas::table,
   diesel::dsl::sum::<diesel::sql_types::BigInt, _>(schema::account_deltas::nonce),
)

     .get_result::<Option<i64>>(conn)
     .optional()?
     .flatten();

which fails since Foldable for BigInt can only be used with Numeric, which is only avail on postgres and MySQL.

Using raw statements also failed, regardless if I use a custom type X or (i64,) or i64 as the accumulating type.

    #[derive(Debug, Default)]
    struct X {
        sum: i64,
    }

    impl FromSql<BigInt, Sqlite> for X {
        fn from_sql(bytes: <Sqlite as Backend>::RawValue<'_>) -> diesel::deserialize::Result<Self> {
            <i64 as FromSql<BigInt, Sqlite>>::from_sql(bytes).map(|v| X { sum: v })
        }

        fn from_nullable_sql(
            bytes: Option<<Sqlite as Backend>::RawValue<'_>>,
        ) -> diesel::deserialize::Result<Self> {
            let maybe = bytes.map(Self::from_sql).transpose()?;
            Ok(maybe.unwrap_or_default())
        }
    }

    let maybe_nonce: Vec<X> =
        diesel::sql_query("SELECT SUM(nonce) FROM account_deltas").load(conn)?;

The sql_query returned type is always Untyped and doesn't fulfil CompatibleType trait bound check.

Is there a way to make this work accumulatin BigInt into an i64?

[weiznich]

which fails since Foldable for BigInt can only be used with Numeric, which is only avail on postgres and MySQL.

That statement is incorrect: https://docs.diesel.rs/master/diesel/sql_types/struct.Numeric.html#impl-FromSql%3CNumeric,+Sqlite%3E-for-BigDecimal

Using raw statements also failed, regardless if I use a custom type X or (i64,) or i64 as the accumulating type.

That's expected as you miss to derive QueryableByName as explicitly called out in the docs of diesel::sql_query

[drahnr]

Reading the first section, this is not clear at all: https://docs.diesel.rs/master/diesel/sql_types/struct.Numeric.html

[weiznich]

Well, seems like that particular part of the documentation is not up to date anymore. I'm happy to accept a PR fixing that.

[drahnr]

I'll do so once I have a working example of accumulating BigInts in a reasonably ergonomic fashion.

[weiznich]

I'm not sure what else are you looking for. I linked to the relevant impl that you want to use here in my first post. There is not much to add here, other than what have been written before (that the statement about SQLite in the documentation of Numeric is not correct anymore and needs to be updated).

[drahnr]

I am a bit confused on how QueryableByName would work when I don't reference a column directly, but a sum(column_name)?

[weiznich]

Having an explicit field name is a requirement for using that derive, so you need to change your query so that the result set has such an explicit field name.

[drahnr]

Using alias!(sum(column_nam) as summed) does not work, and that's the only way I see at this point to introduce an as summed term into the SQL query.

Using the derive(QueryableByName) also assumes a column name / module name in my schema matching the struct field member, but that obviously does not exist. I also don't see a way in the table! macro nor the Select* to create an alias.

Could you please point me where to read up on this, it's anything but easy to discover.

[weiznich]

Please go back and read what I've written again. I wrote field name, not column name. That refers to the name of the field in the result set retuned by database. You can easily change that by just using an alias in your SQL query. If you don't know how to do that please refer to the SQLite documentation: https://system.data.sqlite.org/home/doc/4deb290d232e5850/Doc/Extra/Core/syntax/result-column.html

[drahnr]

Manual implementation of QueryableByName works, using #[derive(QueryableByName)] does not, since there is a dependency on the existence of mod $fieldname which is only satisfied when used with a column name as generated by the cli tool / table!.

The following works:

    #[derive(Debug, PartialEq, Clone)]
    struct X {
        summed: i64,
    }

    impl QueryableByName<Sqlite> for X {
        // Required method
        fn build<'a>(row: &impl NamedRow<'a, Sqlite>) -> diesel::deserialize::Result<Self> {
            let summed =
                NamedRow::<'a, Sqlite>::get::<diesel::sql_types::BigInt, i64>(row, "summed")?;
            Ok(Self { summed })
        }
    }

    let maybe_nonce2 = diesel::sql_query("SELECT SUM(nonce) AS summed from deltas where account_id = ? AND num > ? AND num <= ?")
        .bind::<diesel::sql_types::Blob, _>(&desired_account_id)
        .bind::<diesel::sql_types::BigInt, _>(start)
        .bind::<diesel::sql_types::BigInt, _>(end)
        .get_result::<Option<X>>(conn)
        .optional()?
        .flatten();

Alternatively:

    let maybe_nonce = SelectDsl::select(
        schema::deltas::table,
        diesel::dsl::sum::<diesel::sql_types::BigInt, _>(schema::deltas::nonce),
    )
    .filter(
        schema::account_deltas::account_id
            .eq(desired_account_id)
            .and(schema::deltas::num.gt(num))
            .and(schema::deltas::num.le(num)),
    )
    .get_result::<Option<bigdecimal::BigDecimal>>(conn)
    .optional()?
    .flatten();
    
    let x: i64 = your_conversion(x)?;
    ```

[weiznich]

To add that here again as comment for future visitors:

• You don't need to manually implement QueryableByName, the derive is totally able to handle that. Checkout the documentation for details
• Prefer the other variant using the DSL

[drahnr]

Re reading the documentation, there is indeed an indirect reference, which I missed on the first pass. So #[diesel(sql_type = SomeType)] of #[derive(QueryableByName)] allows you to annotate a specific field with an appropriate SQL type representation, using its FromSql implementation to do the actual deserialization into the given type from the query result. Iiuc that's what you're trying to point at.

[weiznich]

Given that the documentation uses a rather explicit: IF this than that language I disagree about that being an "indirect" reference.
If you feel that this could be clearer I'm looking forward to your PR to improve that particular documentation page.