sqlx
sqlx copied to clipboard
launchbadge
LEFT JOIN with json_build_object field not working with null values.
Bug Description
When I try to do a LEFT JOIN where there might be no rows, SQLX doesn't take the field as nullable. Even when the field in the struct to decode to is nullable. I tried using AS "last_message?" but still sqlx says the field is not nullable.
Minimal Reproduction
My struct is
#[serde(rename_all = "camelCase")]
#[derivative(Default)]
pub struct Room {
pub id: String,
pub name: String,
pub owner_id: String,
pub image: Option<String>,
pub owner_username: String,
#[serde(default)]
pub owner: Option<PartialUser>,
#[serde(default)]
pub receiver: Option<PartialUser>,
pub to_username: Option<String>,
pub is_direct: bool,
pub owner_name: Option<String>,
pub last_message: Option<SimpleWsMessage>, ///The issue is here
#[derivative(Default(value = "chrono::offset::Utc::now()"))]
pub created_at: DateTime<Utc>,
#[derivative(Default(value = "chrono::offset::Utc::now()"))]
pub last_update: DateTime<Utc>,
pub member_ids: Vec<String>,
pub is_deleted: bool,
}
My query is,
let result : Vec<Room>= sqlx::query_as(
r#"
SELECT
ROOM.* as room,
json_build_object(
'id', owner.id,
'username', owner.username,
'name', owner.name,
'pfp', owner.pfp,
'isAuthorized', owner.is_authorized,
'createdAt', owner.created_at,
'lastUpdate', owner.last_update,
'lastLogon', owner.last_logon,
'localeId', owner.locale_id,
'role', owner.role
) AS owner,
json_build_object(
'id', receiver.id,
'username', receiver.username,
'name', owner.name,
'pfp', owner.pfp,
'isAuthorized', owner.is_authorized,
'createdAt', owner.created_at,
'lastUpdate', owner.last_update,
'lastLogon', owner.last_logon,
'localeId', owner.locale_id,
'role', owner.role
) AS receiver,
json_build_object(
'uuid', latest_message.uuid,
'content', latest_message.content,
'authorUsername', latest_message.author_username,
'messageType', latest_message.message_type
) AS "last_message?" ///Tried overriding nullability
FROM
ROOM
INNER JOIN
chatter AS owner ON room.owner_id = owner.id
LEFT JOIN
chatter AS receiver ON room.to_username = receiver.username
LEFT JOIN (
SELECT
DISTINCT ON (room) room,
uuid,
content,
message_type,
author_username
FROM
message
ORDER BY
room, timestamp DESC
) AS latest_message ON latest_message.room = ROOM.id
WHERE owner_id = $1 OR $1 = ANY(member_ids) AND is_deleted = false ORDER BY room.last_update DESC LIMIT 10;
"#
)
.bind(user_id)
.fetch_all(pool)
.await?;
error occurred while decoding column "last_message": unexpected null; try decoding as an `Option`
CASE
WHEN latest_message.uuid IS NULL THEN NULL
ELSE json_build_object(
'uuid', latest_message.uuid,
'content', latest_message.content,
'authorUsername', latest_message.author_username,
'messageType', latest_message.message_type
)
END AS "last_message"
Doing this gives the same 'unexpected null' error too.
Info
- SQLx version: 0.7
- SQLx features enabled: ["uuid", "postgres"]
- Database server and version: Postgres 16
- Operating system: Debian 12
-
rustc --version: rustc 1.75.0 (82e1608df 2023-12-21)
This gives error no column found for name: last_message
json_build_object(
'uuid', latest_message.uuid,
'content', latest_message.content,
'authorUsername', latest_message.author_username,
'messageType', latest_message.message_type
) AS "last_message?"
DOES WORK
CASE
WHEN latest_message.uuid IS NULL THEN NULL
ELSE json_build_object(
'uuid', latest_message.uuid,
'content', latest_message.content,
'authorUsername', latest_message.author_username,
'messageType', latest_message.message_type
)
END AS "last_message"
This gives correct results in psql query from terminal. But gives this error in sqlx
error occurred while decoding column "last_message": unexpected null; try decoding as an Option``
This is confusing since last_message is an Option in my struct and the result from the db is correct.
