[mysql] `query_scalar!` allows `u64` for `SUM()` return type at compile time, fails at runtime with `DECIMAL` vs `BIGINT UNSIGNED` mismatch

[lucasbasquerotto]

I have found these related issues/pull requests

Nothing found related to the mysql error:

mismatched types; Rust type `u64` (as SQL type `BIGINT UNSIGNED`) is not compatible with SQL type `DECIMAL`

Description

When using query_scalar! with MySQL, aggregate functions like SUM() return DECIMAL according to the prepared statement metadata. However, query_scalar! does not reject incompatible Rust types at compile time, it only fails at runtime when the Decode impl triggers a type mismatch.

In the column metadata generated by sqlx prepare I see:

{
    "type": "NewDecimal",
    "flags": "BINARY",
    "collation": 63,
    "max_size": 32
}

Error

ColumnDecode {
    index: "0",
    source: "mismatched types; Rust type `u64` (as SQL type `BIGINT UNSIGNED`) is not compatible with SQL type `DECIMAL`"
}

Expected behavior

Either:

A) Reject incompatible types at compile time (a different, compatible type should be used).

B) Support transparent conversion at runtime. If DECIMAL represents an integral value that fits in u64, SQLx could decode it as u64 directly. This would avoid the runtime panic. If that can't be known at compile-time, option A sould be used.

Reproduction steps

use sqlx::mysql::MySqlPoolOptions;

#[tokio::main]
async fn main() {
    let pool = MySqlPoolOptions::new()
        .connect("mysql://root:password@localhost/test")
        .await
        .unwrap();

    // This compiles fine but panics at runtime:
    let result: u64 = sqlx::query_scalar!(
        "SELECT SUM(LENGTH(name)) FROM users"
    )
    .fetch_one(&pool)
    .await
    .unwrap();

    println!("{result}");
}

Using SELECT CAST(SUM(LENGTH(name)) AS UNSIGNED) runs correctly (the metadata becomes "type": "LongLong", "flags": "UNSIGNED | BINARY"), but the actual issue is the lack of compile-time checking for the case that causes an error.

SQLx version

0.9.0

Enabled SQLx features

["rust_decimal", "mysql", "runtime-tokio", "time"]

Database server and version

MySQL 8.0

Operating system

Amazon Linux AL2023 (in a Docker scratch image, built by docker rust:1.95.0 debian-based image)

Rust version

1.95.0