[query_as!] Incorrect nullability inferred on left join leading to a runtime exception

[jetaggart]

Bug Description

When doing a left join, sqlx infers a column as non-nullable vs an option.

Minimal Reproduction

#[derive(Clone)]
pub struct PlantWithUserDetails {
    pub id: Uuid,
    pub in_garden_at: Option<DateTime<Utc>>,
}

// ...
        let res  = sqlx::query_as!(PlantWithUserDetails, r#"
            SELECT plants.*, up.created_at as in_garden_at
            FROM plants
            LEFT JOIN user_plants up on plants.id = up.plant_id and up.user_id = $1
            WHERE plants.id = $2
        "#, id, user_id)
            .fetch_one(&self.db)
            .await?;

Leads to:

error[E0308]: mismatched types
  --> src/plant/plant_repo.rs:62:20
   |
62 |           let res  = sqlx::query_as!(PlantWithUserDetails, r#"
   |  ____________________^
63 | |             SELECT plants.*, up.created_at as in_garden_at
64 | |             FROM plants
65 | |             LEFT JOIN user_plants up on plants.id = up.plant_id and up.user_id = $1
66 | |             WHERE plants.id = $2
67 | |         "#, user_id, id)
   | |________________________^ expected enum `std::option::Option`, found struct `DateTime`
   |
   = note: expected enum `std::option::Option<DateTime<_>>`
            found struct `DateTime<_>`

If I change my struct to match:

#[derive(Clone)]
pub struct PlantWithUserDetails {
    pub id: Uuid,
    pub in_garden_at: DateTime<Utc>,
}

I end up with a runtime exception:

occurred while decoding column 4: unexpected null; try decoding as an `Option`

Caused by:
    unexpected null; try decoding as an `Option`

Info

• SQLx version: 0.6.2
• SQLx features enabled: ["runtime-tokio-native-tls", "postgres", "uuid", "chrono", "offline"]
• Database server and version: Postgres 14
• Operating system: macos
• rustc --version: rustc 1.64.0 (a55dd71d5 2022-09-19)

[abonander]

Please provide the relevant database schema as well as the output of EXPLAIN (VERBOSE, FORMAT JSON) <query>

[jetaggart]

create table plants
(
    id         uuid                     default gen_random_uuid() not null
        primary key,
    name       text                                               not null
        constraint plants_name_uniq
            unique,
    created_at timestamp with time zone default now()             not null,
    updated_at timestamp with time zone                           not null
);

create table user_plants
(
    id         uuid                     default gen_random_uuid() not null
        primary key,
    user_id    text                                               not null,
    plant_id   uuid                                               not null
        references plants,
    created_at timestamp with time zone default now()             not null,
    updated_at timestamp with time zone                           not null,
    unique (user_id, plant_id)
);

[
  {
    "Plan": {
      "Node Type": "Nested Loop",
      "Parallel Aware": false,
      "Async Capable": false,
      "Join Type": "Left",
      "Startup Cost": 0.00,
      "Total Cost": 2.35,
      "Plan Rows": 1,
      "Plan Width": 72,
      "Output": ["plants.id", "plants.name", "plants.created_at", "plants.updated_at", "up.created_at"],
      "Inner Unique": true,
      "Join Filter": "(plants.id = up.plant_id)",
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Relation Name": "plants",
          "Schema": "public",
          "Alias": "plants",
          "Startup Cost": 0.00,
          "Total Cost": 1.29,
          "Plan Rows": 1,
          "Plan Width": 64,
          "Output": ["plants.id", "plants.name", "plants.created_at", "plants.updated_at"],
          "Filter": "(plants.id = '11c1229c-c71c-41e6-8b55-771437202390'::uuid)"
        },
        {
          "Node Type": "Seq Scan",
          "Parent Relationship": "Inner",
          "Parallel Aware": false,
          "Async Capable": false,
          "Relation Name": "user_plants",
          "Schema": "public",
          "Alias": "up",
          "Startup Cost": 0.00,
          "Total Cost": 1.04,
          "Plan Rows": 1,
          "Plan Width": 24,
          "Output": ["up.id", "up.user_id", "up.plant_id", "up.created_at", "up.updated_at"],
          "Filter": "((up.plant_id = '11c1229c-c71c-41e6-8b55-771437202390'::uuid) AND (up.user_id = '11c1229c-c71c-41e6-8b55-771437202390'::text))"
        }
      ]
    }
  }
]

[gitmalong]

Can confirm the bug still exists in sqlx v0.6.2 with rustc 1.67.0-nightly.

Workaround attempt with query_as!(Option<T>, "..") fails with

comparison operators cannot be chained
use `::<...>` instead of `<...>` to specify lifetime, type, or const arguments
or use `(...)` if you meant to specify fn arguments

[Charles-Johnson]

I had a similar query that worked before adding an ORDER BY clause:

        sqlx::query!(
            "SELECT nt.id, user_id, title, subtitle
            FROM notification_types nt
                LEFT JOIN opted_out_notification_types
                    ON notification_type = nt.id AND opted_out_notification_types.user_id = $1
                ORDER BY title
            ",
            user_id
        ).fetch_all(&mut conn)
        .await
        .context("Trying to fetch all notification types")?
        .into_iter()
        .map(|r| NotificationTypeRow {
            id: r.id,
            title: r.title,
            subtitle: r.subtitle,
            opted_in: r.user_id.is_none(),
        })

The above failed to compile because user_id inferred to be i64 instead of Option<i64>

I've got a workaround by overriding the type but I rather not rely on this too much

        sqlx::query!(
            r#"SELECT nt.id, user_id as "user_id?", title, subtitle
            FROM notification_types nt
                LEFT JOIN opted_out_notification_types
                    ON notification_type = nt.id AND opted_out_notification_types.user_id = $1
                ORDER BY title
            "#,
            user_id
        )
        .fetch_all(&mut conn)
        .await
        .context("Trying to fetch all notification types")?
        .into_iter()
        .map(|r| NotificationTypeRow {
            id: r.id,
            title: r.title,
            subtitle: r.subtitle,
            opted_in: r.user_id.is_none(),
        })

[jayy-lmao]

query_as!(Option<T>, "..")

I think with query_as! you can't be passing in an Option<T> or Vec<T> in the first position. That's what results in this error. You'd have to pass something like let foo: Option<T> = query_as!(T,...). Not sure if this helps.

[Palmik]

Can confirm sqlx is inferring non-Option for left joins in 0.7 as well. As a workaround, you can use SELECT foo as "foo?".

[musjj]

I'm also having this issue and it seems to be caused by the WHERE condition.

For example, the column in this simple left join is correctly inferred as Option<T>:

SELECT
    foo.id,
    bar.foo_id -- Option<T>
FROM
    foo
    LEFT JOIN bar ON bar.foo_id = foo.id

But adding a WHERE condition causes it to be inferred as T:

SELECT
    foo.id,
    bar.foo_id -- T
FROM
    foo
    LEFT JOIN bar ON bar.foo_id = foo.id
WHERE
    foo.id = $1

Strangely, using AND does not cause this issue, so you can kind of use it as a hacky workaround:

SELECT
    foo.id,
    bar.foo_id -- Option<T>
FROM
    foo
    LEFT JOIN bar ON bar.foo_id = foo.id
    AND foo.id = $1

[abonander]

Tentatively closed by #3541