sqlx: 0.6.0: RETURNING causes value to have an extra `Option` wrapped

I have defined a NOT NULL column:

CREATE TABLE IF NOT EXISTS foo(
    ip INTEGER NOT NULL,
    client_id BLOB,
    PRIMARY KEY(ip)
);

After the update to 0.6.0, wherever regular SELECT queries happen the type is correctly not optional:

        sqlx::query!(
            "SELECT ip 
            FROM 
                foo 
            WHERE 
                client_id = ?1
            LIMIT 1",
            id
        )
        .fetch_optional(pool)
        .await?
        .map(|cur| cur.ip as u32)

However, if I use RETURNING the type has an extra Option wrap and fails to compile now:

        sqlx::query!(
            r#"
            UPDATE foo
           ....
            RETURNING ip
            "#,
        )
        .fetch_optional(conn)
        .await?
        .map(|cur| IpAddr::V4(Ipv4Addr::from(cur.ip as u32))))

error[E0605]: non-primitive cast: `Option<i64>` as `u32`
   --> libs/ip-manager/src/sqlite.rs:700:47
    |
700 |                 ip: IpAddr::V4(Ipv4Addr::from(cur.ip as u32)),
    |                                               ^^^^^^^^^^^^^ an `as` expression can only be used to convert between primitive types or to coerce to a specific trait object

edit: using sqlite backend

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 1
Comments: 18 (16 by maintainers)

Most upvoted comments

Yep, I just double checked that after todays rebase to get a fix for an unrelated CI failure.

Thanks for the good reproduction steps. Those make life so much easier.

tyrelr on Jul 15, 2022

Thanks for the update, @leshow. I gave this a look this afternoon and was able to construct an update query with incorrect nullibility. I believe I found the root-cause and a fix within PR #1960 (or at least, the start of one).

tyrelr on Jul 11, 2022

@leshow

Again, I would like to PR this, just point me in the right direction.

Unfortunately I can’t dig into the root cause right now. But if you want to dig deeper, this is the workflow I had settled into for most of my prior changes:

add a test case to tests/sqlite/describe.rs reproducing the wrong nullability for that ‘shape’ of query (probably possible even with the existing tables)
run sqlx’s sqlite unit tests to reproduce.
- If you enable logging, you should be able to see every ‘execution path’ leading to a result. I recommend running ONLY the test function you add if you use the logging. It is a lot to read through if you run every single test function.
Combine the following 3 pieces of information to try to step backwards into where the query analyzer went wrong:

the logging output above (which includes the bytecode, and each possible result (column types/nullability, plus the order of operations which produced it)
the sqlite opcode reference page
the output for “EXPLAIN {the query}”, using something like DB Browser. (technically this is part of the log output… but it’s easier to read with formatting)

I typically end up adding temporary debug statements into where it looks like the issue is (unless it’s obvious), before actually fixing anything. The actual code to fix will be somewhere within: sqlx-core/src/sqlite/connection/explain.rs

Hopefully this actually helps you…

tyrelr on Jul 8, 2022

And just to explain this further, even if I use fetch_one the Record’s field is internally still Record { ip: Option<i64> }. I believe there is an issue with RETURNING in the sqlite backend, as it looks like the postgres example does not have this issue https://github.com/launchbadge/sqlx/blob/061fdcabd72896d9bc3abb4ea4af6712a04bc0a8/examples/postgres/todos/src/main.rs#L45

Again, I would like to PR this, just point me in the right direction.

leshow on Jun 22, 2022