Postgres query with where in (subquery) wants to return every field as Option

[regularfellow]

When selecting rows using WHERE IN (SUBQUERY) sqlx erroneously(?) tries to return the rows with every field as nullable:

https://github.com/regularfellow/todos-example/blob/c74f96319e15c2b181f140c0f7e497a5ccd8ac05/src/main.rs#L19-L22

struct Todo {
    id: i64,
    description: String,
    done: bool,
}

sqlx::query_as!(
    Todo,
    "SELECT * FROM todos WHERE id IN (SELECT todo_id FROM todo_things)"
)

➜  todos-example git:(master) cargo check
    Checking sqlx-example-postgres-connected-todos v0.1.0 (/home/github/todos-example)
error[E0308]: mismatched types
  --> src/main.rs:19:17
   |
19 |       let todos = sqlx::query_as!(
   |  _________________^
20 | |         Todo,
21 | |         "SELECT * FROM todos WHERE id IN (SELECT todo_id FROM todo_things)"
22 | |     )
   | |_____^ expected `i64`, found enum `Option`
   |
   = note: expected type `i64`
              found enum `Option<i64>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0308]: mismatched types
  --> src/main.rs:19:17
   |
19 |       let todos = sqlx::query_as!(
   |  _________________^
20 | |         Todo,
21 | |         "SELECT * FROM todos WHERE id IN (SELECT todo_id FROM todo_things)"
22 | |     )
   | |_____^ expected struct `String`, found enum `Option`
   |
   = note: expected struct `String`
                found enum `Option<String>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0308]: mismatched types
  --> src/main.rs:19:17
   |
19 |       let todos = sqlx::query_as!(
   |  _________________^
20 | |         Todo,
21 | |         "SELECT * FROM todos WHERE id IN (SELECT todo_id FROM todo_things)"
22 | |     )
   | |_____^ expected `bool`, found enum `Option`
   |
   = note: expected type `bool`
              found enum `Option<bool>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

For more information about this error, try `rustc --explain E0308`.
error: could not compile `sqlx-example-postgres-connected-todos` due to 3 previous errors

Example repo can be found from https://github.com/regularfellow/todos-example if helpful.

[jplatte]

It's quite common for Postgres not to report nullability of columns directly unfortunately. See #367 and #1126 as some examples.

[abonander]

Alternatively, try this query, which should produce the same results but generate a query plan that's much friendlier to the heuristics:

SELECT todos.*
FROM todo_things
INNER JOIN todos ON todo_things.todo_id = todos.id