sqlite: Nullability of column changes when order by is added to query

[izderadicka]

let mut recs = sqlx::query!(
                r#"
                select id, path, title, album, artist from tags
                order by path
                ;
                "#
            )
            .fetch(&pool);

in above query id ( defined as id INTEGER PRIMARY KEY) is returned as type Option<i64>.
However when order by clause is removed, id type is just i64.

[jonaslimads]

@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 Option<i64>:

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" }

[izderadicka]

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.

[Cightline]

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.

[abonander]

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 order by is causing different bytecode to be generated which is throwing off our heuristics.

The bytecode analysis in 0.4.2 covered fewer patterns and may have more false positives or negatives than this.

[Bondza]

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 ORDER BY clause and one without and the one with ORDER BY compiles ok and other fail with errors:

error[E0308]: mismatched types
  --> src/main.rs:29:22
   |
29 |       let result_err = sqlx::query_as!(
   |  ______________________^
30 | |         Ab,
31 | |         "SELECT
32 | |             b.id,
...  |
36 | |         FROM b JOIN a ON b.a_id = a.id"
37 | |     )
   | |_____^ expected struct `NaiveDate`, found enum `Option`
   |
   = note: expected struct `NaiveDate`
                found enum `Option<NaiveDate>`
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

[kyleerhabor]

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

[abonander]

@Bondza please open a new issue as that is a different database. Please include the output of EXPLAIN (VERBOSE, FORMAT JSON) <query> (or that of a similar query plus relevant schema that reproduces the issue) as that is what is used for nullability detection in Postgres.

[Maykeye]

I'm having similar issue in 0.6.0, but the opposite way: field of left join becomes non-nullable:

     CREATE TABLE [T1]([V] INTEGER NOT NULL);

   let q = sqlx::query!("
    SELECT A.V F1, B.V F2 FROM T1 A 
    LEFT JOIN T1 B ON B.V = 12
    ORDER BY A.V
  ").map(|r| r.F2.unwrap());

Here F2 is i64 if ORDER BY exists and Option without "ORDER BY"

[jgirardet]

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 order by works, limit works but not both together

[bgourlie]

I'm seeing this issue, but instead of being caused by order by it started happening when I added the UNIQUE constraint to other columns on the table.