sqlx
sqlx copied to clipboard
launchbadge
sqlx::query!(..) inferring wrong `Option` NULLable-types
Environment:
- PostgreSQL 14.1
-
sqlx = { version = "0.5", features = [ "runtime-async-std-native-tls", "postgres", "offline", "time" ] }
I don't think this issue is related to OS-specifics, so please let me know, if you need more information here.
Description
My DB schema:
CREATE TABLE department (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE person (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
department_id INT references department(id) NOT NULL
);
CREATE TABLE training (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE person_training (
trainee_person_id INT references person(id) NOT NULL,
training_id INT references training(id) NOT NULL,
PRIMARY KEY (trainee_person_id, training_id),
advisor_person_id INT references person(id) NULL,
progress REAL NOT NULL
);
CREATE TABLE person_training_log (
id SERIAL PRIMARY KEY,
trainee_person_id INT references person(id) NOT NULL,
training_id INT references training(id) NOT NULL,
author_person_id INT references person(id) NOT NULL,
comment TEXT NOT NULL,
created TIMESTAMP NOT NULL,
updated TIMESTAMP NOT NULL
);
I need to make a query with a subselect and some joins, where (in my opinion) sqlx has a bug. At first here is a query which is smaller, but part of the larger query, which doesn't work. This smaller one here works and also has a left join and subselect. sqlx correctly understands, that both select fields may be NULL, so great.
let trainings = query!(r#"
SELECT
ptl_last.created AS last_log_created,
ptl_last.comment AS last_log_comment
FROM
person_training pt
LEFT JOIN (
SELECT
ptl.trainee_person_id,
ptl.training_id,
ptl.created,
ptl.comment,
ROW_NUMBER() OVER(
PARTITION BY
ptl.trainee_person_id, ptl.training_id
ORDER BY
ptl.created
DESC
) as i
FROM
person_training_log ptl
) ptl_last
ON
ptl_last.i = 1 AND
ptl_last.training_id = pt.training_id AND
ptl_last.trainee_person_id = pt.trainee_person_id
WHERE
pt.trainee_person_id = 9
"#).fetch_all(&self.config.db_pool).await.unwrap();
if let Some(created) = trainings[0].last_log_created {
// ok compiles, it's an Option
}
Now when I make the actual query it fails:
let trainings = query!(r#"
SELECT
t.id AS training_id,
t.name AS training_name,
pt.progress AS training_progress,
p_a.name AS advisor_name,
ptl_last.created AS last_log_created,
ptl_last.comment AS last_log_comment,
p_ptl_last_a.name AS last_log_author_name
FROM
person_training pt
INNER JOIN
training t
ON
pt.training_id = t.id
LEFT JOIN
person p_a
ON
p_a.id = pt.advisor_person_id
LEFT JOIN (
SELECT
ptl.trainee_person_id,
ptl.training_id,
ptl.created,
ptl.author_person_id,
ptl.comment,
ROW_NUMBER() OVER(
PARTITION BY
ptl.trainee_person_id, ptl.training_id
ORDER BY
ptl.created
DESC
) as i
FROM
person_training_log ptl
) ptl_last
ON
ptl_last.i = 1 AND
ptl_last.training_id = pt.training_id AND
ptl_last.trainee_person_id = pt.trainee_person_id
LEFT JOIN
person p_ptl_last_a
ON
p_ptl_last_a.id = ptl_last.author_person_id
WHERE
pt.trainee_person_id = $1
"#, person_id)
.fetch_all(&self.config.db_pool)
.await
.map_err(sqlx_error_into_status)?;
if let Some(created) = trainings[0].last_log_created {
// 312 | if let Some(created) = trainings[0].last_log_created {
// | ^^^^^^^^^^^^^ ----------------------------- this expression has type `PrimitiveDateTime`
// | |
// | expected struct `PrimitiveDateTime`, found enum `Option`
// |
// = note: expected struct `PrimitiveDateTime`
// found enum `Option<_>`
}
It's almost the same context: field of a left-joined table (which should always be consider nullable I guess?). And indeed .await returns Err(..) with message "error occurred while decoding column 4: unexpected null; try decoding as an Option".
Checking generated meta data, sqlx doesn't get the nullable fields correctly.
sqlx-data.json:
{
"a05641face9dea9a20ab8d564a723f893ffb3dc8b490cd00c0843694558caf39": {
"describe": {
"columns": [
{
"name": "last_log_created",
"ordinal": 0,
"type_info": "Timestamp"
},
{
"name": "last_log_comment",
"ordinal": 1,
"type_info": "Text"
}
],
"nullable": [
true,
true
],
"parameters": {
"Left": []
}
},
"query": "\n SELECT\n ptl_last.created AS last_log_created,\n ptl_last.comment AS last_log_comment\n FROM\n person_training pt\n LEFT JOIN (\n SELECT\n ptl.trainee_person_id,\n ptl.training_id,\n ptl.created,\n ptl.comment,\n ROW_NUMBER() OVER(\n PARTITION BY\n ptl.trainee_person_id, ptl.training_id\n ORDER BY\n ptl.created\n DESC\n ) as i\n FROM\n person_training_log ptl\n ) ptl_last\n ON\n ptl_last.i = 1 AND\n ptl_last.training_id = pt.training_id AND\n ptl_last.trainee_person_id = pt.trainee_person_id\n WHERE\n pt.trainee_person_id = 9\n "
},
"209ab5928f26319c8c7780d6ea469563086d7e09ba7d766e3441c2bbbff189ff": {
"describe": {
"columns": [
{
"name": "training_id",
"ordinal": 0,
"type_info": "Int4"
},
{
"name": "training_name",
"ordinal": 1,
"type_info": "Text"
},
{
"name": "training_progress",
"ordinal": 2,
"type_info": "Float4"
},
{
"name": "advisor_name",
"ordinal": 3,
"type_info": "Text"
},
{
"name": "last_log_created",
"ordinal": 4,
"type_info": "Timestamp"
},
{
"name": "last_log_comment",
"ordinal": 5,
"type_info": "Text"
},
{
"name": "last_log_author_name",
"ordinal": 6,
"type_info": "Text"
}
],
"nullable": [
false,
false,
false,
false,
false,
false,
false
],
"parameters": {
"Left": [
"Int4"
]
}
},
"query": "\n SELECT\n t.id AS training_id,\n t.name AS training_name,\n pt.progress AS training_progress,\n p_a.name\t\t\t\t\tAS advisor_name,\n ptl_last.created AS last_log_created,\n ptl_last.comment AS last_log_comment,\n p_ptl_last_a.name AS last_log_author_name\n FROM\n person_training pt\n INNER JOIN\n training t\n ON\n pt.training_id = t.id\n LEFT JOIN\n person p_a\n ON\n p_a.id = pt.advisor_person_id\n LEFT JOIN (\n SELECT\n ptl.trainee_person_id,\n ptl.training_id,\n ptl.created,\n ptl.author_person_id,\n ptl.comment,\n ROW_NUMBER() OVER(\n PARTITION BY\n ptl.trainee_person_id, ptl.training_id\n ORDER BY\n ptl.created\n DESC\n ) as i\n FROM\n person_training_log ptl\n ) ptl_last\n ON\n ptl_last.i = 1 AND\n ptl_last.training_id = pt.training_id AND\n ptl_last.trainee_person_id = pt.trainee_person_id\n LEFT JOIN\n person p_ptl_last_a\n ON\n p_ptl_last_a.id = ptl_last.author_person_id\n WHERE\n pt.trainee_person_id = $1\n "
}
}
Is it possible to have a workaround maybe without offline queries in this case, but still use the feature for the rest of the project?
Thank you Philipp
I have a similar error with simpler setup. When using the macro (query_as!) the null-ability is not correct. When using the function (query_as) the null-ability is correct. The second one can be used as an workaround in my case.
- sqlite3 (
3.38.5 2022-05-06 15:25:27 78d9c993d404cdfaa7fdd2973fa1052e3da9f66215cff9c5540ebe55c407alt1) -
sqlx = { version = "0.5", features = ["runtime-tokio-rustls", "postgres", "sqlite", "offline"]}
My database schema:
CREATE TABLE tblmedia(
id INTEGER PRIMARY KEY NOT NULL,
title VARCHAR NOT NULL,
raiting INTEGER,
file_name VARCHAR NOT NULL,
media_type TEXT NOT NULL
);
CREATE TABLE tblbook(
id INTEGER PRIMARY KEY NOT NULL,
author VARCHAR,
FOREIGN KEY (id) REFERENCES tblmedia(id) ON DELETE CASCADE
);
CREATE VIEW book AS
SELECT
m.id as id,
m.title as title,
b.author as author,
m.raiting as raiting,
m.file_name as file_name
FROM tblbook b
LEFT JOIN tblmedia m ON m.id = b.id;
The struct:
#[derive(Debug, PartialEq, Eq, sqlx::FromRow)]
pub struct Book {
pub id: i64,
pub author: Option<String>,
pub title: String,
pub raiting: Option<i64>,
pub file_name: String,
}
The SQL queries
// Does work
sqlx::query_as(r#"SELECT id, author, title, raiting, file_name FROM book;"#)
.fetch_all(connection)
.await;
// Does not work
sqlx::query_as!(
Book,
r#"SELECT id, author, title, raiting, file_name FROM book"#
)
.fetch_all(connection)
.await;
The error
error[E0308]: mismatched types
--> src/database/book.rs:33:9
|
33 | / sqlx::query_as!(
34 | | Book,
35 | | r#"SELECT id, author, title, raiting, file_name FROM book"#
36 | | )
| |_________^ expected struct `String`, found enum `Option`
|
= note: expected struct `String`
found enum `Option<String>`
@DaAitch @liolin would both of you please provide the output of EXPLAIN (VERBOSE, FORMAT JSON) <query> as that is what the Postgres driver uses for nullability analysis.
@abonander , when I execute EXPLAIN (VERBOSE, FORMAT JSON) SELECT id, author, title, raiting, file_name FROM book; I get the following output:
[
{
"Plan": {
"Node Type": "Hash Join",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 23.73,
"Total Cost": 49.77,
"Plan Rows": 1270,
"Plan Width": 104,
"Output": ["m.id", "b.author", "m.title", "m.raiting", "m.file_name"],
"Inner Unique": true,
"Hash Cond": "(b.id = m.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "tblbook",
"Schema": "public",
"Alias": "b",
"Startup Cost": 0.00,
"Total Cost": 22.70,
"Plan Rows": 1270,
"Plan Width": 36,
"Output": ["b.id", "b.author"]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 16.10,
"Total Cost": 16.10,
"Plan Rows": 610,
"Plan Width": 72,
"Output": ["m.id", "m.title", "m.raiting", "m.file_name"],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "tblmedia",
"Schema": "public",
"Alias": "m",
"Startup Cost": 0.00,
"Total Cost": 16.10,
"Plan Rows": 610,
"Plan Width": 72,
"Output": ["m.id", "m.title", "m.raiting", "m.file_name"]
}
]
}
]
}
}
]
That's actually not a false positive, look back at the view you created:
CREATE VIEW book AS
SELECT
m.id as id,
m.title as title,
b.author as author,
m.raiting as raiting,
m.file_name as file_name
FROM tblbook b
LEFT JOIN tblmedia m ON m.id = b.id;
Most of those columns are coming from the m table brought in by a left join, which means they may not be present.
Since you're selecting from tblbook which is foreign-keyed to tblmedia, you would always expect there to be a corresponding tblmedia row if there is a tblbook row, which would be INNER JOIN, not LEFT JOIN.
Ahh. Thank you for pointing out my mistake. And I'm sorry for bothering you with this false positive.
Running into something similar:
CREATE TABLE Donations (
id INTEGER PRIMARY KEY,
user TEXT NOT NULL,
amount REAL NOT NULL,
message TEXT,
timestamp REAL NOT NULL
);
For the query I have:
sqlx::query_as!(DonationRow,"SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ?", limit)
And the struct:
#[derive(Debug)]
#[allow(dead_code)]
struct DonationRow {
id: i64,
user: String,
amount: f64,
message: Option<String>,
timestamp: f64,
}
And the error:
error[E0308]: mismatched types
--> src/main.rs:50:55
|
50 | Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
| _______________________________________________________^
51 | | DonationRow,
52 | | "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | | limit
54 | | ),
| |_____________^ expected `i64`, found enum `Option`
|
= note: expected type `i64`
found enum `Option<i64>`
= note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)
error[E0308]: mismatched types
--> src/main.rs:50:55
|
50 | Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
| _______________________________________________________^
51 | | DonationRow,
52 | | "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | | limit
54 | | ),
| |_____________^ expected struct `String`, found enum `Option`
|
= note: expected struct `String`
found enum `Option<String>`
= note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)
error[E0308]: mismatched types
--> src/main.rs:50:55
|
50 | Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
| _______________________________________________________^
51 | | DonationRow,
52 | | "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | | limit
54 | | ),
| |_____________^ expected `f64`, found enum `Option`
|
= note: expected type `f64`
found enum `Option<f64>`
= note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)
I had this same problem. I ended up resolving it by explicitly marking the joined-in column as optional col as "col?".
I had this same problem. I ended up resolving it by explicitly marking the joined-in column as optional
col as "col?".
@0rvar
This was very helpful and worked for me, thanks. My question is, is this SQL, Postgres, or SQLX syntax? Is this documented anywhere?
This is very useful but there's no way I would have ever figured that out if I hadn't seen your comment.
@Sharpiro I think you meant to @ someone else.
However, I believe this issue is resolved in sqlx 0.7. That has been my experience in any case. Try upgrading and see if that helps
@Sharpiro I think you meant to @ someone else.
However, I believe this issue is resolved in sqlx 0.7. That has been my experience in any case. Try upgrading and see if that helps
Ahh sorry I meant to tag @martijnarts
But I'm not really saying there is a problem, I'm just curious about the col as "col?" syntax
I feel sorry for opening an issue and then not actively participating for a long while.
For what I understood sqlx forwards SQL to the specific db server and get back the meta data for the query, which is a genius idea. If there are Option<..>-types where you don't expect them or the other way round, the DB seems to have taken into account more than you, or it can't understand some constraints in the SQL, so you need to put more information into the SQL, like mentioned in the comments before, so this is no issue anymore.
Thanks for clarifying.
How does it work with enums?
I have last_message.message_type AS "message_type?: MessageType"
but I get unexpected null; try decoding as an Option
