Rust Diesel中使用i64接收sum计算的BigInt结果时出现解码错误的问题排查与解决

问题重现 #

我在用Rust Diesel框架编写账单统计代码时，尝试用i64接收sum(amount)的计算结果，代码如下：

pub fn get_bill_book_account_sum(request: &BillAccountRequest) -> Result<Vec<(i64, i32)>, diesel::result::Error>{
    use crate::diesel::GroupByDsl;
    use crate::model::diesel::fortune::fortune_schema::bill_record as bill_record_table;
    let source_query = bill_record_table::table
        .group_by(bill_record_table::account_id)
        .select((diesel::dsl::sql::<diesel::sql_types::BigInt>("sum(amount)"),bill_record_table::account_id))
        .filter(bill_record_table::dsl::bill_book_id.eq(request.bill_book_id));
    let result = source_query.load::<(i64,i32)>(&get_connection());
    return result;
}

运行时触发panic，错误信息：

thread 'rocket-worker-thread' panicked at 'Received more than 8 bytes decoding i64. Was an expression of a different type misidentified as BigInteger?', /Users/xiaoqiangjiang/.cargo/registry/src/mirrors.tuna.tsinghua.edu.cn-df7c3c540f42cdbd/diesel-1.4.8/src/type_impls/integers.rs:68:9

对应的数据库表定义（Diesel生成）：

table! {
    bill_record (id) {
        id -> Int8,
        created_time -> Int8,
        updated_time -> Int8,
        deleted -> Int4,
        user_id -> Int8,
        bill_book_id -> Int8,
        remark -> Nullable<Varchar>,
        amount -> Int8,
        bill_book_contents_id -> Int8,
        account_id -> Int4,
    }
}

问题原因 #

核心问题是数据库中sum(amount)的实际返回类型和你指定的BigInt（对应Rust的i64）不匹配：

• 你的amount字段是Int8（对应PostgreSQL的bigint/Rust的i64），但数据库的sum函数为了避免溢出，会自动将返回类型提升为更大的数值类型（比如PostgreSQL中sum(bigint)会返回numeric类型）。
• 你用diesel::sql_types::BigInt告诉Diesel要解码成i64，但实际收到的是numeric类型的数据，它的字节数远超过8，因此触发解码panic。

修复方案 #

这里提供三种可行的修复方式，你可以根据需求选择：

方案1：用Diesel的Numeric类型接收后转换 #

如果不确定sum结果是否会超出i64范围，可以先接收为Numeric类型，再安全转换为i64：

1. 首先在Cargo.toml中添加相关特性和依赖：

diesel = { version = "1.4.8", features = ["postgres", "numeric"] }
bigdecimal = "0.20.0"

2. 修改代码：

use diesel::sql_types::Numeric;
use bigdecimal::BigDecimal;

pub fn get_bill_book_account_sum(request: &BillAccountRequest) -> Result<Vec<(i64, i32)>, diesel::result::Error>{
    use crate::diesel::GroupByDsl;
    use crate::model::diesel::fortune::fortune_schema::bill_record as bill_record_table;
    let source_query = bill_record_table::table
        .group_by(bill_record_table::account_id)
        .select((diesel::dsl::sql::<Numeric>("sum(amount)"),bill_record_table::account_id))
        .filter(bill_record_table::dsl::bill_book_id.eq(request.bill_book_id));
    
    // 先加载为(BigDecimal, i32)，再转换为(i64, i32)
    let raw_result: Vec<(BigDecimal, i32)> = source_query.load(&get_connection())?;
    let result = raw_result.into_iter()
        .map(|(sum, account_id)| {
            sum.try_into()
                .map_err(|e| diesel::result::Error::DeserializationError(Box::new(e)))
                .map(|sum_i64| (sum_i64, account_id))
        })
        .collect::<Result<_, _>>();
    result
}

方案2：强制数据库将sum结果转为bigint #

如果你确定sum结果不会超出i64的范围，可以直接在SQL中强制转换类型：

pub fn get_bill_book_account_sum(request: &BillAccountRequest) -> Result<Vec<(i64, i32)>, diesel::result::Error>{
    use crate::diesel::GroupByDsl;
    use crate::model::diesel::fortune::fortune_schema::bill_record as bill_record_table;
    let source_query = bill_record_table::table
        .group_by(bill_record_table::account_id)
        // 用cast把sum结果转成bigint（PostgreSQL语法，其他数据库需调整）
        .select((diesel::dsl::sql::<diesel::sql_types::BigInt>("sum(amount)::bigint"),bill_record_table::account_id))
        .filter(bill_record_table::dsl::bill_book_id.eq(request.bill_book_id));
    let result = source_query.load::<(i64,i32)>(&get_connection());
    return result;
}

注意：不同数据库的类型转换语法有差异，比如MySQL需要用cast(sum(amount) as signed)。

方案3：使用Diesel内置的sum函数（推荐） #

Diesel提供了类型安全的聚合函数，能自动处理类型匹配问题，避免手写SQL的类型错误：

pub fn get_bill_book_account_sum(request: &BillAccountRequest) -> Result<Vec<(i64, i32)>, diesel::result::Error>{
    use crate::diesel::GroupByDsl;
    use diesel::dsl::sum; // 引入sum函数
    use crate::model::diesel::fortune::fortune_schema::bill_record as bill_record_table;
    let source_query = bill_record_table::table
        .group_by(bill_record_table::account_id)
        // 使用Diesel的sum函数，自动匹配类型
        .select((sum(bill_record_table::amount), bill_record_table::account_id))
        .filter(bill_record_table::dsl::bill_book_id.eq(request.bill_book_id));
    let result = source_query.load::<(i64,i32)>(&get_connection());
    return result;
}

这种方式最安全，因为Diesel会根据amount的类型自动推导sum的返回类型，完全避免手动指定SQL类型的错误。

内容的提问来源于stack exchange，提问作者Dolphin