sqlx prepare fails with error "unsupported type NULL of column #2"

[yzernik]

I have a query that in my master branch works fine, but when I try to modify the query to add some additional clauses, the sqlx prepare command fails with:

$ cargo sqlx prepare --database-url sqlite://db.sqlite --check
   Compiling squeakroad v0.0.2 (/home/yzernik/work/squeakroad)
error: unsupported type NULL of column #2 ("amount_change_sat")
    --> src/models.rs:2597:39
     |
2597 |           let account_balance_changes = sqlx::query!("
     |  _______________________________________^
2598 | | SELECT * FROM
2599 | | (select orders.seller_user_id as user_id, orders.seller_credit_sat as amount_change_sat, 'received_order' as event_type, orders.public_id...
2600 | | from
...    |
2628 | | OFFSET ?
2629 | | ;", limit, offset)
     | |__________________^
     |
     = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: unsupported type NULL of column #3 ("event_type")
    --> src/models.rs:2597:39
     |
2597 |           let account_balance_changes = sqlx::query!("
     |  _______________________________________^
2598 | | SELECT * FROM
2599 | | (select orders.seller_user_id as user_id, orders.seller_credit_sat as amount_change_sat, 'received_order' as event_type, orders.public_id...
2600 | | from
...    |
2628 | | OFFSET ?
2629 | | ;", limit, offset)
     | |__________________^
     |
     = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

This is the query, and this is the pull request that is failing.

Related to #1350

[tyrelr]

Thanks for the schema & query, I've added a [partial] fix into PR #1960 fixing a smaller reproducer. That fix doesn't seem to work for your query though, so there are still issues remaining after that.

[rongcuid]

I also have a query that fails for this exact error. I can't isolate a minimum example, but this is my project commit:

rongcuid/gqlforum-rs@33b238a

Hopefully it is not too large. The same query sql/topic_by_id.sql works in SQLite console.

[rongcuid]

I got the same error also after factoring out the WITH clause into a view. The view's column type seems to be NULL for the post_number column. Maybe this is the issue? SQLite doesn't seem to give types for view columns.

[yzernik]

@rongcuid my app is also using SQLite. I wonder if sqlx has more difficulty checking types when the db is SQLite?

[tyrelr]

I know next to nothing about the other sqlx database backends. But sqlite does have some tricky calculations/trade-offs to do when it attempts to do type checking.

Long story short... sqlite does not appear to have any high-level API to find query result types/nullability without running the query. So sqlx inspects a low-level query plan from sqlite instead, sqlx then infers/traces through that query plan to determine the types. Unrecognized operations can cause type information to be lost. When that type information is lost, it can cause sqlx to not know which type a column is (ie. column type NULL).

I'm out of time this week. But I am guessing that the issue with @rongcuid's query is due to the row_number() function being unrecognized. But tat's just a hunch, if you dig in you may find I'm mistaken, or even find that there are multiple issues at play.

[yzernik]

@tyrelr I noticed that the prepare command fails for certain queries when I have an empty database.

In my app buyers put ratings on orders. When I run prepare when there are no ratings, it fails:

$ cargo sqlx prepare --database-url sqlite://db.sqlite --check
   Compiling squeakroad v0.0.2 (/home/yzernik/work/squeakroad)
error: unsupported type NULL of column #1 ("weighted_average")
    --> src/models.rs:1959:28
     |
1959 |           let seller_infos = sqlx::query!(
     |  ____________________________^
1960 | |                     "
1961 | |         SELECT weighted_average, total_amount_sold_sat, users.email
1962 | |         FROM
...    |
1991 | |          total_amount_sold_sat DESC
1992 | |             ;")
     | |_______________^
     |
     = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `squeakroad` due to previous error
error: `cargo check` failed with status: exit status: 101

But after I put at least one rating in the database, it works again:

$ cargo sqlx prepare --database-url sqlite://db.sqlite --check
   Compiling squeakroad v0.0.2 (/home/yzernik/work/squeakroad)
    Finished dev [unoptimized + debuginfo] target(s) in 41.98s

[tyrelr]

That's interesting, @yzernik.

Nothing on the macro side inspects actual data rows within the tables, as far as I'm aware. Sqlx retrieves the declared table column types, and the query plan, and all calculations are based on those inputs. Based on that, I wouldn't expect the presence of data within a table to impact the outcome of the check. But I believe if the non-empty database has statistics, i suppose the query optimizer COULD select a different query plan, and that alternate query plan may be lucky enough to avoid this bug?

[yzernik]

I realized that I can use "query" instead of "query!" for the queries where type inference fails as a workaround for now. Is it expected that these queries will work in v0.7?

[davidcornu]

Just ran into the same sort of error using query! against sqlite with a column that was the result of a join against a common table expression.

I realized that I can use "query" instead of "query!" for the queries where type inference fails as a workaround for now. Is it expected that these queries will work in v0.7?

I was still able to use query! by using a override (https://docs.rs/sqlx/latest/sqlx/macro.query.html#overrides-cheatsheet) on the affected column.

[sasacocic]

I'm running into the same issue I believe. I have a small reproducible example using SQLite

Here's some relavent info

cargo.toml

sqlx = { version = "0.6.2", features = [ "sqlite", "runtime-actix-native-tls" ]}
actix-web = "4.2.1"

main.rs

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    // .env file with DATABASE_URL=sqlite:sqlite.db
    let pool = SqlitePool::connect("sqlite.db").await.unwrap();

    let mut conn = pool.acquire().await.unwrap();

    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS Ads (
            company,
            position,
            remote,
            apply_link,
            description,
            paid
        )
    "#
    )
    .execute(&mut conn)
    .await
    .unwrap();

   // insert data here if you wish

    let res = sqlx::query!(r#"select company, position from Ads"#)
        .fetch_all(&pool)
        .await
        .unwrap();
   Ok(())
}

You'll get these errors

error: unsupported type NULL of column #1 ("company")
   --> src/main.rs:123:15
    |
123 |     let res = sqlx::query!(r#"select company, position from Ads"#)
    |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    |
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: unsupported type NULL of column #2 ("position")
   --> src/main.rs:123:15
    |
123 |     let res = sqlx::query!(r#"select company, position from Ads"#)
    |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    |
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

glad to provide any more information if needed.