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

Aperta
  • Qualcosa non va?
#4,298 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
38/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Tranquilla
Stack tecnologico
mysql, rust
Ambito
backend, databases

Direzione di ricerca

The failure is in compile-time checking for query_scalar! against MySQL prepared-statement metadata (NewDecimal for SUM() vs u64 as BIGINT UNSIGNED). Start from how sqlx maps that metadata to Rust types and from the Decode path that raises the DECIMAL mismatch at runtime. Compare with CAST(... AS UNSIGNED) which yields LongLong. Done when incompatible types are rejected at compile time or integral DECIMAL can decode as u64 without panic, matching the reproduction.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug
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

Lingua principale
Rust
Stelle
17.5k
Fork
1.7k
Merge medio
1g 17h
PR unite (30g)
7

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di transact-rs/sqlx

Tutte le issue di transact-rs/sqlx

Issue simili

Altre issue su Rust

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.