Type mixup when deserializing from DB despite successful compilation

[Ploppz]

Setup

Versions

• Rust: rustc 1.82.0 (f6e511eec 2024-10-15)
• Diesel: 2.2.7
• Database: postgres
• Operating System Arch Linux

Feature Flags

• diesel: "postgres", "serde_json", "chrono", "r2d2"

Problem Description

What are you trying to accomplish?

This is a reduced part of my application code, where I try to represent an optional left-join in a query struct. So having Option<OtherQueryableTableStruct> as a field in a one Queryable table struct. Then in the case of actually performing this left join (first case in test case which passes), we select the fields of the other joined table in a nested tuple. But when the left join is not performed and we only want the first table isolated, we just pass sql::<Nullable<(the column types of the joined table)>>("NULL").
In production I noticed error {"code":500,"message":"Error deserializing field 'exported': Unsupported JSONB encoding version"} (exported being a bool both in Diesel schema, queryable struct, postgres table).

After reducing to reproducible example, it change slightly but still shows that it mixes up the types.

What is the expected output?

I expect it to be able to query the joined table into the struct.
The result would be that extra_field is Some

What is the actual output?

The first query succeeds but the second query fails:

Normal Query OK
ERROR: Error deserializing field 'status': Received less than 8 bytes while decoding an i64. Was an Integer expression accidentally marked as BigInt?

It may seem like the nested tuple makes Diesel mix up which type it asks for which column? Or am I doing something wrong?
But I would have expected Diesel to catch type discrepancies at compile time.

Steps to reproduce

Cargo.toml

[package]
name = "diesel-test"
version = "0.1.0"
edition = "2021"

[dependencies]
diesel = { version = "2.2.7", features = ["postgres", "serde_json", "chrono", "r2d2", "numeric", "uuid"] }
chrono = { version = "0.4.9", features = ["serde"]}
serde_json = "1.0"

main.rs

#![allow(dead_code)]
use chrono::{DateTime, Utc};
use diesel::dsl::sql;
use diesel::prelude::*;
use diesel::sql_types::*;

#[derive(Debug, Queryable)]
struct TestMeasurement {
    id: i64,
    extra_info: Option<TestExtraInfo>, // joined table
    timestamp: Option<DateTime<Utc>>,
    json_field: Option<serde_json::Value>,
    status: String,
    archived_at: Option<DateTime<Utc>>,
    reference_id: Option<i64>,
    flag: bool,
}

#[derive(Debug, Queryable)]
struct TestExtraInfo {
    id: i64,
    measurement_id: i64,
    created_at: Option<DateTime<Utc>>,
}

diesel::table! {
    test_measurement (id) {
        id -> Int8,
        timestamp -> Nullable<Timestamptz>,
        json_field -> Nullable<Jsonb>,  // JSONB field
        status -> Text,
        archived_at -> Nullable<Timestamptz>,
        reference_id -> Nullable<Int8>,
        flag -> Bool,
    }
}

diesel::table! {
    test_extra_info (id) {
        id -> Int8,
        measurement_id -> Int8,
        created_at -> Nullable<Timestamptz>,
    }
}
diesel::joinable!(test_extra_info -> test_measurement (measurement_id));
diesel::allow_tables_to_appear_in_same_query!(test_extra_info, test_measurement);

fn main() {
    let pool = establish_connection(None).unwrap();
    let mut conn = pool.get().unwrap();

    // Create tables
    diesel::sql_query(
        "CREATE TEMP TABLE test_measurement (
            id BIGINT PRIMARY KEY,
            timestamp TIMESTAMPTZ NULL,
            json_field JSONB NULL,
            status TEXT NOT NULL,
            archived_at TIMESTAMPTZ NULL,
            reference_id BIGINT NULL,
            flag BOOLEAN NOT NULL
        );",
    )
    .execute(&mut conn)
    .unwrap();

    diesel::sql_query(
        "CREATE TEMP TABLE test_extra_info (
            id BIGINT PRIMARY KEY,
            measurement_id BIGINT NOT NULL REFERENCES test_measurement(id),
            created_at TIMESTAMPTZ NULL
        );",
    )
    .execute(&mut conn)
    .unwrap();

    // Insert test data
    diesel::sql_query(
        "INSERT INTO test_measurement (id, timestamp, json_field, status, archived_at, reference_id, flag) VALUES 
        (1, NOW(), '{\"flag\":true}'::jsonb, 'Active', NOW(), 5, false), 
        (2, NOW(), '{\"flag\":false}'::jsonb, 'Inactive', NOW(), 5, false)"
    ).execute(&mut conn).unwrap();

    diesel::sql_query(
        "INSERT INTO test_extra_info (id, measurement_id, created_at) VALUES
        (1, 1, NOW()),
        (2, 2, NULL);",
    )
    .execute(&mut conn)
    .unwrap();

    // **Normal Query - Should Work**
    test_measurement::table
        .left_join(
            test_extra_info::table.on(test_extra_info::measurement_id.eq(test_measurement::id)),
        )
        .select((
            test_measurement::id,
            (
                test_extra_info::id,
                test_extra_info::measurement_id,
                test_extra_info::created_at,
            )
                .nullable(),
            test_measurement::timestamp,
            test_measurement::json_field, // JSONB field
            test_measurement::status,
            test_measurement::archived_at,
            test_measurement::reference_id,
            test_measurement::flag,
        ))
        .load::<TestMeasurement>(&mut conn)
        .unwrap();
    println!("Normal Query OK");

    // **Query With NULL Placeholder Instead of Join**
    let null_result = test_measurement::table
        .select((
            test_measurement::id,
            sql::<Nullable<(Int8, Int8, Nullable<Timestamptz>)>>("NULL"),
            test_measurement::timestamp,
            test_measurement::json_field, // JSONB field
            test_measurement::status,
            test_measurement::archived_at,
            test_measurement::reference_id,
            test_measurement::flag,
        ))
        .load::<TestMeasurement>(&mut conn);

    match null_result {
        Ok(_) => println!("Query with NULL OK"),
        Err(e) => println!("ERROR: {}", e),
    }
}

use diesel::r2d2::{ConnectionManager, Pool, PoolError};
use std::env;

fn establish_connection(
    db_name: Option<&str>,
) -> std::result::Result<Pool<ConnectionManager<PgConnection>>, PoolError> {
    let mut pg_port = env::var("PGPORT").unwrap_or_else(|_| "".to_string());
    let mut pg_host = env::var("PGHOST").unwrap_or_else(|_| "".to_string());
    let pg_database = if let Some(db_name) = db_name {
        db_name.to_string()
    } else {
        env::var("PGDATABASE").expect("PGDATABASE environment variable is not set")
    };
    let pg_user = env::var("PGUSER").expect("PGUSER environment variable is not set");
    let pg_password = env::var("PGPASSWORD").unwrap_or_else(|_| "".to_string());

    if pg_port.is_empty() {
        pg_port = "5432".to_string();
    }

    if pg_host.is_empty() {
        pg_host = "localhost".to_string();
    }

    let database_url = format!(
        "postgres://{username}:{password}@{host}:{port}/{database}",
        username = pg_user,
        password = pg_password,
        host = pg_host,
        port = pg_port,
        database = pg_database
    );

    let manager = ConnectionManager::<PgConnection>::new(database_url);
    Pool::builder().build(manager)
}

Output:

Normal Query OK
ERROR: Error deserializing field 'status': Received less than 8 bytes while decoding an i64. Was an Integer expression accidentally marked as BigInt?

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate (except serde_json and chrono in this case but probably without as well)

[weiznich]

I think that's expected behaviour as your sql function call claims to return a tuple of values (vie the generic parameters), but really only returns a single null value. That breaks the contract given by that function and what is expect by the Queryable derive.

[Ploppz]

I see, apologies for the oversight indeed an sql call is naturally the entrance of type-unsafety. I thought NULL could be an appropriate value of a Nullable tuple. But I guess in postgres there are not tuples in that sense... so what would an acceptable "null" value of type Nullable<(Int8, Int8, Nullable<Timestamptz>)> look like in SQL or how to construct such a value if possible?

[weiznich]

You likely want to use something like sql("null, null, null"), there if you want to use the sql function at all. You can also use the IntoSql trait to create three nullable values without using the sql function.

[Ploppz]

Thanks. I'm trying to use IntoSql but can't quite figure it out

            (
                None::<i64>.into_sql::<Nullable<Int8>>(),
                None::<i64>.into_sql::<Nullable<Int8>>(),
                None::<DateTime<Utc>>.into_sql::<Nullable<Timestamptz>>(),
            )
                .nullable(),

Does not work (just the typical CompatibleTypes<TestMeasurement, Pg> is not satisfied) (with or without .nullable())

Also tried:

            None::<(i64, i64, Option<DateTime<Utc>>)>
                .into_sql::<Nullable<(Int8, Int8, Nullable<Timestamptz>)>>(),

            (None, None, None).into_sql::<Nullable<(Int8, Int8, Nullable<Timestamptz>)>>(),