Open rewbycraft opened 5 months ago
I've run into this bug as well
I see this quite often and end up using as "col_name!"
as a workaround.
My understanding is that the type-checking is done on the query plan and the plan is based off the actual query executed internally in Postgres, which Postgres can mess with for optimisation.
In this case as you can see in the query plan, it's flipped your left join into a right join.
"Join Type": "Right",
While I defer to Postgres' judgement that this is indeed more optimal, this also makes the type of result columns that logically can never hold a NULL value, nullable.
This in turn throws off the type checking, causing this issue.
Not too familiar with this side of Postgres and what options are available but I wonder if the query sent up for type-checking can pass a flag or option telling Postgres not to mess with it and construct the query plan for the query as-is.
Bug Description
I found I had to use the
col as "col?"
trick to force nullability at runtime otherwisesqlx::query_as!()
produces "unexpected null; try decoding as anOption
when multiple (left) joins are used". The documentation forsqlx::query_as!()
requests a bug report be filed in such cases.Minimal Reproduction
The application in question tracks the weekly top 50 of radio stations. The two tables needed to reproduce this are as follows:
The query involved:
The fields
prev_position
anddelta
are null if the track was not present in the previous week's top50. Then looking at thenullability
field in the files produced bycargo sqlx prepare
I have also noticed that it produces afalse
for theprev_position
field and anull
for thedelta
field.For completeness sake, here is the struct I'm using
query_as!
to decode the rows into:The query plan:
Info
["postgres", "runtime-tokio", "migrate", "chrono"]
rustc --version
:rustc 1.77.2 (25ef9e3d8 2024-04-09)