launchbadge
`query_as!` does not recognize `Option<T>` in some `left join` queries
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 https://github.com/launchbadge/sqlx/issues/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
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.
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 https://github.com/launchbadge/sqlx/issues/2127 I think.
My only workaround for this is to use COALESCE(xxx, NULL) to force it as Option<T> for the offending column
