-
Notifications
You must be signed in to change notification settings - Fork 1.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
sqlite: Nullability of column changes when order by is added to query #1126
Comments
@izderadicka What is your DB and schema, if you are able to share? I've tested the code below with MySQL and sqlx v0.5.1 and the returned type was not CREATE TABLE tags (
id INTEGER PRIMARY KEY,
path VARCHAR(100) NOT NULL,
title VARCHAR(100) NOT NULL,
album VARCHAR(100) NOT NULL,
artist VARCHAR(100) NOT NULL
);
INSERT INTO tags VALUES(1, "abc", "Title 1", "Album 1", "Artist 1"),
(2, "abc", "Title 2", "Album 2", "Artist 2"); use futures::TryStreamExt;
use sqlx::MySqlPool;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let pool = MySqlPool::connect("mysql://....").await?;
let mut recs = sqlx::query!(
r#"
select id, path, title, album, artist from tags
order by path
;
"#
)
.fetch(&pool);
while let Some(row) = recs.try_next().await? {
println!("{:?}", row);
}
Ok(())
}
// Record { id: 1, path: "abc", title: "Title 1", album: "Album 1", artist: "Artist 1" }
// Record { id: 2, path: "abc", title: "Title 2", album: "Album 2", artist: "Artist 2" } |
CREATE TABLE tags (
id INTEGER PRIMARY KEY,
path TEXT NOT NULL UNIQUE,
title TEXT,
artist TEXT,
composer TEXT,
album TEXT,
year TEXT,
comment TEXT,
description TEXT,
genre TEXT,
duration INTEGER DEFAULT 0,
bitrate INTEGER NOT NULL,
num_chapters INTEGER DEFAULT 0,
ts NUMERIC DEFAULT CURRENT_TIMESTAMP
); Also the version of sqlx is same 0.5.1. |
I just upgraded to 0.5.1 and I'm getting similar issues. let potential_drivers = match sqlx::query_as!(Driver, "SELECT * FROM drivers WHERE uid IN (SELECT DISTINCT driver_uid FROM assigned_vendors WHERE vendor_uid IN ($1))", &vendor_uids.join(",")).fetch_all(&mut *tx).await
{
Ok(pd) => pd,
Err(e) =>
{
//etc..
}
}; After downgrading to 0.4.2 everything works again. |
To determine nullability of columns in SQLite, SQLx will actually pull the generated bytecode for the prepared statement and step through it to find any expressions that could produce a null value. What's likely happening here is the addition of the The bytecode analysis in 0.4.2 covered fewer patterns and may have more false positives or negatives than this. |
I think I'm experiencing the same or a similar problem with PostgreSQL. I created a small example to reproduce the problem here https://github.com/Bondza/sqlxrepro. The example has two queries, one with an
|
A temporary solution to this problem is to force the column to be non-nullable (aka dropping the reliance on inference). For example, SELECT
b.id AS "id!",
FROM
b
JOIN a
ON b.a_id = a.id |
@Bondza please open a new issue as that is a different database. Please include the output of |
I'm having similar issue in 0.6.0, but the opposite way: field of left join becomes non-nullable:
Here F2 is |
Hi, another case with sqlite/sqlx 0.6.2 with ORDER BY an LIMIT. pub struct Maison {
pub id: i64,
pub adresse: String,
pub taille: i64,
pub piscine: Option<bool>
} sqlx::query_as!(Maison, r#"SELECT * from maison ORDER BY taille"#).fetch_all(&p.pool) // works
sqlx::query_as!(Maison, r#"SELECT * from maison ORDER BY taille LIMIT 2"#).fetch_all(&p.pool) // works
sqlx::query_as!(Maison, r#"SELECT * from maison ORDER BY taille LIMIT 2"#).fetch_all(&p.pool)
// fails with mismatched types expected `i64`, found enum `Option`
// and expected struct `String`, found enum `Option` So |
I'm seeing this issue, but instead of being caused by |
in above query id ( defined as
id INTEGER PRIMARY KEY
) is returned as typeOption<i64>
.However when order by clause is removed, id type is just i64.
The text was updated successfully, but these errors were encountered: