query_as, fetch_all with join, LIMIT and bind parameter causes not null overrides to be needed on all fields

[boydjohnson]

I'm using sqlx with postgres and have tested this against postgres:13 and postgres:12 docker images. Edit: sqlx 0.5.5.

My tables look like

CREATE TABLE Users (
    id SERIAL NOT NULL,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE Todos (
    id SERIAL NOT NULL,
    title VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES Users(id)
);

#[derive(Debug, Deserialize, Serialize)]
pub struct TodoFull {
    id: i32,
    title: String,
    user_id: i32,
    username: String,
}

This query compiles

qlx::query_as!(
        TodoFull,
        r#"SELECT t.id, t.title, u.id "user_id!: i32", u.username "username!: String" FROM Todos t INNER JOIN Users u ON t.user_id = u.id LIMIT 1 OFFSET 2"#,
    )
    .fetch_all(conn)
    .await

this query fails to compile

qlx::query_as!(
        TodoFull,
        r#"SELECT t.id, t.title, u.id "user_id!: i32", u.username "username!: String" FROM Todos t INNER JOIN Users u ON t.user_id = u.id LIMIT $1 OFFSET $2"#,
        1,
        2,
    )
    .fetch_all(conn)
    .await

with errors:

expected struct `std::string::String`, found enum `std::option::Option`

expected `i32`, found enum `std::option::Option`

Interestingly, fetch_all without a join on the users query doesn't fail

"SELECT id, username FROM users LIMIT $1 OFFSET $2"

works as expected.

[boydjohnson]

Apparently it was the id and title that were causing the problem. If I have non-null overrides on all fields query_as compiles. Less of a bug, but still kind of annoying.

Compiles:

sqlx::query_as!(
        TodoFull,
        r#"SELECT t.id as "id!", t.title as "title!", u.id "user_id!: i32", u.username "username!: String" FROM Todos t INNER JOIN Users u ON t.user_id = u.id LIMIT $1 OFFSET $2"#,
        1,
        2,
    )
    .fetch_all(conn)
    .await