Unrelated fields alters the nullability of queries in sqlx prepare

[SorenHolstHansen]

Bug Description

I am trying to do a sql query with quite a lot of joins. The migration file and the query looks like this (Don't consider the naming too much I have removed all unneeded fields, and renamed some stuff).

CREATE TABLE workspace (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL
);

CREATE TABLE users (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid()
);

CREATE TABLE box (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by_id UUID NOT NULL,
    type TEXT NOT NULL,
    workspace_id UUID NOT NULL REFERENCES workspace(id)
);

CREATE TABLE warehouse (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by_id UUID NOT NULL,
    box_id UUID NOT NULL REFERENCES box(id),
    workspace_id UUID NOT NULL REFERENCES workspace(id)
);

CREATE TABLE container (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by_id UUID NOT NULL,
    workspace_id UUID NOT NULL REFERENCES workspace(id),
    box_id UUID NOT NULL REFERENCES box(id)
);

CREATE TABLE data_set (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by_id UUID NOT NULL,
    collection_id UUID NOT NULL,
    workspace_id UUID NOT NULL REFERENCES workspace(id)
);

CREATE TABLE platform (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by_id UUID NOT NULL,
    data_set_id UUID REFERENCES data_set(id),
    workspace_id UUID NOT NULL REFERENCES workspace(id)
);

CREATE TABLE data_set_metadata (
    data_set_id UUID NOT NULL REFERENCES data_set(id),
    owner_id UUID REFERENCES users(id),
    workspace_id UUID NOT NULL REFERENCES workspace(id),

    CONSTRAINT data_set_metadata_pkey PRIMARY KEY (data_set_id)
);

CREATE TABLE container_data_set_bridge (
    container_id UUID NOT NULL REFERENCES container(id),
    data_set_id UUID NOT NULL REFERENCES data_set(id),
    workspace_id UUID NOT NULL REFERENCES workspace(id),

    CONSTRAINT container_data_set_bridge_pkey PRIMARY KEY (container_id,data_set_id)
);

ALTER TABLE warehouse ADD COLUMN connector_version TEXT NOT NULL;

And then my program just looks like this

use sqlx::{postgres::PgPoolOptions, types::Uuid};

#[derive(Debug)]
struct QueryResult {
    pub data_set_id: Uuid,
    pub reference_id: Option<Uuid>,
    pub workspace_name: String,
    pub warehouse_type: String,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    dotenvy::dotenv().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL").unwrap())
        .await?;

    let a = sqlx::query_as!(
        QueryResult,
        r#"
        SELECT
            da.data_set_id,
            CASE
                WHEN container.container_id IS NOT NULL THEN container.container_id
                WHEN platform.id IS NOT NULL THEN platform.id
                ELSE NULL
            END AS reference_id,
            w.title as workspace_name,
            b.type as warehouse_type
        FROM data_set_metadata AS da
        INNER JOIN data_set AS ds
            ON da.data_set_id = ds.id
        INNER JOIN workspace AS w
            ON da.workspace_id = w.id
        INNER JOIN warehouse AS wm
            ON da.workspace_id = wm.workspace_id
        INNER JOIN box AS b
            ON wm.box_id = b.id
        LEFT JOIN container_data_set_bridge AS container
            ON container.data_set_id = ds.id
        LEFT JOIN platform
            ON platform.data_set_id = ds.id
        "#
    )
    .fetch_all(&pool)
    .await?;
    dbg!(a);

    Ok(())
}

And my dependencies

dotenvy = "0.15.7"
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "postgres", "uuid"] }
tokio = { version = "1.35.1", features = ["full"] }
uuid = "1.6.1"

Now running cargo sqlx prepare will throw the following errors

error[E0277]: the trait bound `Uuid: From<Option<Uuid>>` is not satisfied
  --> src/main.rs:20:13
   |
20 |       let a = sqlx::query_as!(
   |  _____________^
21 | |         QueryResult,
22 | |         r#"
23 | |         SELECT
...  |
45 | |         "#
46 | |     )
   | |_____^ the trait `From<Option<Uuid>>` is not implemented for `Uuid`
   |
   = help: the following other types implement trait `From<T>`:
             <Uuid as From<Hyphenated>>
             <Uuid as From<sqlx::types::uuid::fmt::Simple>>
             <Uuid as From<Urn>>
             <Uuid as From<Braced>>
   = note: required for `Option<Uuid>` to implement `Into<Uuid>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `std::string::String: From<Option<std::string::String>>` is not satisfied
  --> src/main.rs:20:13
   |
20 |       let a = sqlx::query_as!(
   |  _____________^
21 | |         QueryResult,
22 | |         r#"
23 | |         SELECT
...  |
45 | |         "#
46 | |     )
   | |_____^ the trait `From<Option<std::string::String>>` is not implemented for `std::string::String`
   |
   = help: the following other types implement trait `From<T>`:
             <std::string::String as From<char>>
             <std::string::String as From<Box<str>>>
             <std::string::String as From<Uuid>>
             <std::string::String as From<Cow<'a, str>>>
             <std::string::String as From<url::Url>>
             <std::string::String as From<PgCiText>>
             <std::string::String as From<&str>>
             <std::string::String as From<&mut str>>
             <std::string::String as From<&std::string::String>>
   = note: required for `Option<std::string::String>` to implement `Into<std::string::String>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

For more information about this error, try `rustc --explain E0277`.
error: could not compile `sqlx_error` (bin "sqlx_error") due to 2 previous errors
error: `cargo check` failed with status: exit status: 101

However any of the following, seemingly unrelated changes, to the migration file will fix the error

• Removing the ALTER TABLE warehouse ADD COLUMN connector_version TEXT NOT NULL;
• Removing all the created_by_id fields from all the tables
• Removing the owner_id field from the data_set_metadata table

The prepared file has the following "nullable" field

"nullable": [
  true,
  null,
  true,
  true
]

And doing any of the above fixes changes that to

"nullable": [
  false,
  null,
  false,
  false
]

What is going on here? It seems like the changes are completely unrelated to the query.

Minimal Reproduction

A minimal repo case can be found in this repo: https://github.com/SorenHolstHansen/sqlx_error

Info

• SQLx version: 0.7.3
• SQLx features enabled: runtime-tokio-rustls, postgres, uuid
• Database server and version: Postgres 14.9
• Operating system: MacOS
• rustc --version: 1.74.1

[mkarbo]

Any updates on this?