SQLx fails to infer subquery is not nullable and fails to infer type (with SQLite)

Bug Description

Using a subquery with COUNT, the result is always be a numerical value, if there are no rows it is zero. SQLx however infers the result is Option<()>, and when forcing with as `something: i64` it still infers Option<i64>.

Workaround

Forcing non-nullability with as `something!: i64`.

Minimal Reproduction

migrations/[...]schema.up.sql

CREATE TABLE user (
    id INTEGER PRIMARY KEY NOT NULL
);

CREATE TABLE vote (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES user(id)
        ON DELETE CASCADE
);

src/main.rs

struct User {
    id: i64,
    votes: i64,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = sqlx::SqlitePool::connect("sqlite://db.sqlite").await?;

    let id = 1;
    sqlx::query_as!(
        User,
        "SELECT user.*, (SELECT COUNT(vote.id) FROM vote WHERE vote.user_id \
        = user.id) as votes FROM user WHERE user.id = ?",
        id
    )
    .fetch_one(&pool)
    .await?;

    Ok(())
}

Generates the error:

the trait `From<std::option::Option<()>>` is not implemented for `i64`, which is required by `std::option::Option<()>: Into<_>`

And forcing the type with: as `votes: i64`

Only changes the error to:

 the trait `From<Option<i64>>` is not implemented for `i64`, which is required by `Option<i64>: Into<_>`

The only working option is to use: as `votes!: i64`

I've found a similar issue https://github.com/launchbadge/sqlx/issues/1634 which explains there are some limitations due to the query plan the database provides. I'm not too familiar with checking database query plans but it seems this should not be ambiguous as a query, as COUNT will always return a number, even if 0.

I tried running EXPLAIN QUERY PLAN on that query to see if I could gather some insight but to be honest I'm clueless about the result:

QUERY PLAN
|--SEARCH user USING PRIMARY KEY (id=?)
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN vote

I'd be willing to give a try fixing this but likely need to be pointed in the general direction of how to tackle this.

Info

SQLx version: 0.7.4
SQLx features enabled: "runtime-tokio", "sqlite"
Database server and version: sqlite 3.37.2
Operating system: Pop-OS (Ubuntu based linux)
rustc --version: 1.80.0-nightly (b1ec1bd65 2024-05-18)

launchbadge / sqlx