How Do I SUM() in SQLite?

[PhasecoreX]

I'm trying to get both a count of rows and a sum of a column from a table in SQLite. Ideally, I'd like to do this in one call, but for now I can't even get the sum portion to work.

For count, I have this:

            let result: i64 = the_table::dsl::the_table
                .select(dsl::count(the_table::id))
                .first(connection)?;

This seems to work just fine. For sum, I was trying this:

            let sum: Option<i64> = the_table::dsl::the_table
                .select(dsl::sum(the_table::total))
                .first(connection)?;

The column total is defined as a BIGINT NOT NULL column, which ends up as BigInt (i64) in the schema.rs. What I am not understanding is the error I get:

rustc: cannot deserialize a value of the database type `diesel::sql_types::Numeric` as `i64`
double check your type mappings via the documentation of `diesel::sql_types::Numeric`
the trait `diesel::deserialize::FromSql<diesel::sql_types::BigInt, diesel::sqlite::Sqlite>` is implemented for `i64`
for that trait implementation, expected `diesel::sql_types::BigInt`, found `diesel::sql_types::Numeric`

Why does it want to use diesel::sql_types::Numeric when the column is BigInt? On top of that, I don't even think that SQLite uses Numeric, as the docs say to use Double. But I don't really know how to get sum to use that instead (or more preferably, BigInt/i64).

Any help on this would be greatly appreciated!

[weiznich]

Why does it want to use diesel::sql_types::Numeric when the column is BigInt?

Because that's how this function is defined my most database implementations. SQLite is a bit a special case there,it's technically not 100% correct to use that implementation, but on the other hand SQLite really does not have a hard type system at all and will just accept that we ask for a numeric value there.

On top of that, I don't even think that SQLite uses Numeric, as the docs say to use Double.

That's not true, see the SQLite documentation for details (Point 3)

To answer the original question: With diesel you always need to use the right rust type for your SQL types. That's checked at compile time and you get such an error if the types do not match. In this case you need to use a type that compatible with Numeric. You can find out supported types based on the documentation (as already pointed out in the error message).

[PhasecoreX]

On top of that, I don't even think that SQLite uses Numeric, as the docs say to use Double.

By that, I had meant that the documentation mentioned "This type is only supported on PostgreSQL and MySQL. On SQLite, Double should be used instead." I was confused by that.

But, I have added BigDecimal to my project and also added the numeric feature for diesel, so now this works just fine:

            let (count, sum): (i64, Option<BigDecimal>) = the_table::dsl::the_table
                .select((dsl::count(the_table::id), dsl::sum(the_table::total)))
                .first(connection)?;

Thank you for your help!

[weiznich]

By that, I had meant that the documentation mentioned "This type is only supported on PostgreSQL and MySQL. On SQLite, Double should be used instead." I was confused by that.

That sounds like the documentation is not 100% correct there. Mind submitting a PR that softens the wording there to "Double should be preferred if possible"