`query_as!` does not recognize `Option<T>` in some `left join` queries

[demhadais]

Bug Description

If you run a query with a left join and a where condition outside the left join, sqlx yells at you during runtime (not compile time) with the following error:

error occurred while decoding column <col_number>: unexpected null; try decoding as an `Option`

even if you read the column into an Option<T>. However, if you put that condition inside the left join, it works fine.

Minimal Reproduction

Here's a contrived example with two tables, employee and department:

schema.sql:

create table department (
    id serial primary key,
    name text not null
);

create table employee (
    name text primary key,
    department_id integer references department
);

main.rs:

struct QueryResult {
    employee_name: String,
    department_name: Option<String>,
}

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

    sqlx::query!("insert into employee (name) values ('ahmed')")
        .execute(&pool)
        .await
        .unwrap();


    // This works!
    let good_query_result = sqlx::query_as!(
        QueryResult,

        "
        select
            employee.name as employee_name,
            department.name as department_name
        from employee
        left join
            department
            on
                employee.department_id = department.id
                and employee.name = $1
        ",

        "ahmed"
    )
    .fetch_one(&pool)
    .await
    .unwrap();

    // This doesn't
    let bad_query_result = sqlx::query_as!(
        QueryResult,

        "select
            employee.name as employee_name,
            department.name as department_name
        from employee
        left join
            department
            on employee.department_id = department.id
        where employee.name = $1",
        
        "ahmed"
    )
    .fetch_one(&pool)
    .await
    .unwrap();
}
    /*
    thread 'main' panicked at src/main.rs:57:6:
    called `Result::unwrap()` on an `Err` value: ColumnDecode { index: "1", source: UnexpectedNullError }
    */

I believe this is a bug with sqlx because each query returns the expected (and same) result when run in a psql shell.

PS: I know issue #3336 exists, but I don't think it's quite the same.

Info

• SQLx version: 0.8.0
• SQLx features enabled: ["postgres", "macros", "uuid", "runtime-tokio"]
• Database server and version: Postgres 16.3
• Operating system: macos Sonoma 14.5
• rustc --version: 1.80

[demhadais]

This issue seems related but I don't think it's the same.

[kennetpostigo]

I just ran into this, is there a work around?

[kennetpostigo]

Work around is to add as "col_name!" to the problematic fields.

[demhadais]

Work around is to add as "col_name!" to the problematic fields.

The thing is, that tells the compiler that the thing you're referencing is not null, which is not what you want on a left join. In the example I made, doing select employee.name as employee_name, department.name as "department_name!" implies that there will always be a department_name, which is not true in my case.

[musjj]

I'm also having the same issue and it's driving me nuts. For some reason having a where condition transforms all left-joined columns from Option<T> -> T. Is there a reason behind this behavior?

But at least it looks like that you can work around this issue by using and instead of where, at least for simple cases.

It looks like that this is the same issue as #2127 I think.

[InterStella0]

My only workaround for this is to use COALESCE(xxx, NULL) to force it as Option<T> for the offending column

[abonander]

Tentatively closed by #3541