launchbadge
[query_as!] Incorrect nullability inferred on left join leading to a runtime exception
Bug Description
When doing a left join, sqlx infers a column as non-nullable vs an option.
Minimal Reproduction
#[derive(Clone)]
pub struct PlantWithUserDetails {
pub id: Uuid,
pub in_garden_at: Option<DateTime<Utc>>,
}
// ...
let res = sqlx::query_as!(PlantWithUserDetails, r#"
SELECT plants.*, up.created_at as in_garden_at
FROM plants
LEFT JOIN user_plants up on plants.id = up.plant_id and up.user_id = $1
WHERE plants.id = $2
"#, id, user_id)
.fetch_one(&self.db)
.await?;
Leads to:
error[E0308]: mismatched types
--> src/plant/plant_repo.rs:62:20
|
62 | let res = sqlx::query_as!(PlantWithUserDetails, r#"
| ____________________^
63 | | SELECT plants.*, up.created_at as in_garden_at
64 | | FROM plants
65 | | LEFT JOIN user_plants up on plants.id = up.plant_id and up.user_id = $1
66 | | WHERE plants.id = $2
67 | | "#, user_id, id)
| |________________________^ expected enum `std::option::Option`, found struct `DateTime`
|
= note: expected enum `std::option::Option<DateTime<_>>`
found struct `DateTime<_>`
If I change my struct to match:
#[derive(Clone)]
pub struct PlantWithUserDetails {
pub id: Uuid,
pub in_garden_at: DateTime<Utc>,
}
I end up with a runtime exception:
occurred while decoding column 4: unexpected null; try decoding as an `Option`
Caused by:
unexpected null; try decoding as an `Option`
Info
- SQLx version: 0.6.2
- SQLx features enabled: ["runtime-tokio-native-tls", "postgres", "uuid", "chrono", "offline"]
- Database server and version: Postgres 14
- Operating system: macos
-
rustc --version: rustc 1.64.0 (a55dd71d5 2022-09-19)
Please provide the relevant database schema as well as the output of EXPLAIN (VERBOSE, FORMAT JSON) <query>
create table plants
(
id uuid default gen_random_uuid() not null
primary key,
name text not null
constraint plants_name_uniq
unique,
created_at timestamp with time zone default now() not null,
updated_at timestamp with time zone not null
);
create table user_plants
(
id uuid default gen_random_uuid() not null
primary key,
user_id text not null,
plant_id uuid not null
references plants,
created_at timestamp with time zone default now() not null,
updated_at timestamp with time zone not null,
unique (user_id, plant_id)
);
[
{
"Plan": {
"Node Type": "Nested Loop",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 0.00,
"Total Cost": 2.35,
"Plan Rows": 1,
"Plan Width": 72,
"Output": ["plants.id", "plants.name", "plants.created_at", "plants.updated_at", "up.created_at"],
"Inner Unique": true,
"Join Filter": "(plants.id = up.plant_id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "plants",
"Schema": "public",
"Alias": "plants",
"Startup Cost": 0.00,
"Total Cost": 1.29,
"Plan Rows": 1,
"Plan Width": 64,
"Output": ["plants.id", "plants.name", "plants.created_at", "plants.updated_at"],
"Filter": "(plants.id = '11c1229c-c71c-41e6-8b55-771437202390'::uuid)"
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "user_plants",
"Schema": "public",
"Alias": "up",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 1,
"Plan Width": 24,
"Output": ["up.id", "up.user_id", "up.plant_id", "up.created_at", "up.updated_at"],
"Filter": "((up.plant_id = '11c1229c-c71c-41e6-8b55-771437202390'::uuid) AND (up.user_id = '11c1229c-c71c-41e6-8b55-771437202390'::text))"
}
]
}
}
]
Can confirm the bug still exists in sqlx v0.6.2 with rustc 1.67.0-nightly.
Workaround attempt with query_as!(Option<T>, "..") fails with
comparison operators cannot be chained
use `::<...>` instead of `<...>` to specify lifetime, type, or const arguments
or use `(...)` if you meant to specify fn arguments
I had a similar query that worked before adding an ORDER BY clause:
sqlx::query!(
"SELECT nt.id, user_id, title, subtitle
FROM notification_types nt
LEFT JOIN opted_out_notification_types
ON notification_type = nt.id AND opted_out_notification_types.user_id = $1
ORDER BY title
",
user_id
).fetch_all(&mut conn)
.await
.context("Trying to fetch all notification types")?
.into_iter()
.map(|r| NotificationTypeRow {
id: r.id,
title: r.title,
subtitle: r.subtitle,
opted_in: r.user_id.is_none(),
})
The above failed to compile because user_id inferred to be i64 instead of Option<i64>
I've got a workaround by overriding the type but I rather not rely on this too much
sqlx::query!(
r#"SELECT nt.id, user_id as "user_id?", title, subtitle
FROM notification_types nt
LEFT JOIN opted_out_notification_types
ON notification_type = nt.id AND opted_out_notification_types.user_id = $1
ORDER BY title
"#,
user_id
)
.fetch_all(&mut conn)
.await
.context("Trying to fetch all notification types")?
.into_iter()
.map(|r| NotificationTypeRow {
id: r.id,
title: r.title,
subtitle: r.subtitle,
opted_in: r.user_id.is_none(),
})
query_as!(Option<T>, "..")
I think with query_as! you can't be passing in an Option<T> or Vec<T> in the first position. That's what results in this error. You'd have to pass something like let foo: Option<T> = query_as!(T,...). Not sure if this helps.
Can confirm sqlx is inferring non-Option for left joins in 0.7 as well. As a workaround, you can use SELECT foo as "foo?".
I'm also having this issue and it seems to be caused by the WHERE condition.
For example, the column in this simple left join is correctly inferred as Option<T>:
SELECT
foo.id,
bar.foo_id -- Option<T>
FROM
foo
LEFT JOIN bar ON bar.foo_id = foo.id
But adding a WHERE condition causes it to be inferred as T:
SELECT
foo.id,
bar.foo_id -- T
FROM
foo
LEFT JOIN bar ON bar.foo_id = foo.id
WHERE
foo.id = $1
Strangely, using AND does not cause this issue, so you can kind of use it as a hacky workaround:
SELECT
foo.id,
bar.foo_id -- Option<T>
FROM
foo
LEFT JOIN bar ON bar.foo_id = foo.id
AND foo.id = $1
