Using : i32 for INT8 values in postgres results in the least significant 4 bytes being silently ignored

[42triangles]

Bug Description

Using : i32 in a column name for COUNT can result in a zero in the returned value, where the original value was a different one.

Minimal Reproduction

#[tokio::main]
async fn main() {
    let pool = sqlx::postgres::PgPool::connect("postgres://postgres:[email protected]")
        .await
        .unwrap();

    dbg!(
        sqlx::query!(r#"SELECT COUNT(x) AS "x!: i32" FROM UNNEST(ARRAY[1, 2, 3]) AS x(x)"#)
            .fetch_all(&pool)
            .await
            .unwrap()
    );
    dbg!(
        sqlx::query!(r#"SELECT COUNT(x) AS "x!" FROM UNNEST(ARRAY[1, 2, 3]) AS x(x)"#)
            .fetch_all(&pool)
            .await
            .unwrap()
    );
}

prints:

[src/main.rs:7:5] sqlx::query!(r#"SELECT COUNT(x) AS "x!: i32" FROM UNNEST(ARRAY[1, 2, 3]) AS x(x)"#).fetch_all(&pool).await.unwrap() = [
    Record {
        x: 0,
    },
]
[src/main.rs:13:5] sqlx::query!(r#"SELECT COUNT(x) AS "x!" FROM UNNEST(ARRAY[1, 2, 3]) AS x(x)"#).fetch_all(&pool).await.unwrap() = [
    Record {
        x: 3,
    },
]

Info

• SQLx version: 0.7.4
• SQLx features enabled: runtime-tokio and postgres
• Database server and version: Postgres Debian 15.3-1.pgdg110+1 (from docker.io/postgres)
• Tokio version: 1.37.0
• Operating system: NixOS
• rustc --version: rustc 1.79.0-nightly (c9f8f3438 2024-03-27)

[42triangles]

After I removed the : i32 in the bigger project that caused me to find this; it turns out that the issue is that it returns an INT8 (⇒ i64), and overriding that type silently ignores the least significant 4 bytes.

#[tokio::main]
async fn main() {
    let pool = sqlx::postgres::PgPool::connect("postgres://postgres:[email protected]")
        .await
        .unwrap();

    dbg!(
        sqlx::query!(r#"SELECT CAST(4294967296 AS INT8) AS "x!: i32""#)
            .fetch_all(&pool)
            .await
            .unwrap()
    );
    dbg!(
        sqlx::query!(r#"SELECT CAST(4294967296 AS INT8) AS "x!""#)
            .fetch_all(&pool)
            .await
            .unwrap()
    );
}

outputs

[src/main.rs:7:5] sqlx::query!(r#"SELECT CAST(4294967296 AS INT8) AS "x!: i32""#).fetch_all(&pool).await.unwrap() = [
    Record {
        x: 1,
    },
]
[src/main.rs:13:5] sqlx::query!(r#"SELECT CAST(4294967296 AS INT8) AS "x!""#).fetch_all(&pool).await.unwrap() = [
    Record {
        x: 4294967296,
    },
]

[abonander]

Yeah, this is because Postgres returns integers in big-endian, and decoding an 8 byte integer as i32 is going to read the first (high) 4 bytes of the value.

It could be changed to read the last 4 bytes instead so that it's a valid truncation, although I feel like it should error instead if the truncation is ignoring nonzero bytes.

We should just copy the approach of the MySQL driver and use ByteOrder::read_int (but with BigEndian instead of LittleEndian):

sqlx/sqlx-mysql/src/types/int.rs

Line 93 in 1f6642c

fn int_decode(value: MySqlValueRef<'_>) -> Result<i64, BoxDynError> {

Then use .try_into() to apply a checked conversion to the specific integer type in Decode.