Nested Type structs result in "implementation of sqlx::Decode is not general enough"

[MirrorBytes]

Bug Description

I was testing nested Type structs for an upcoming project, and ran into a situation "similar" to #1031.

implementation of `sqlx::Decode` is not general enough
`Comment` must implement `sqlx::Decode<'0, Postgres>`, for any lifetime `'0`...
...but it actually implements `sqlx::Decode<'1, Postgres>`, for some specific lifetime `'1`

Minimal Reproduction

use serde::{ Serialize, Deserialize };
use sqlx::{
    FromRow, Type,
    types::Uuid,
    postgres::{ PgHasArrayType, PgTypeInfo },
};

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct Board {
    pub(crate) id: Uuid,
    pub name: String,
    #[sqlx(default)]
    pub panels: Vec<Panel>,
}

#[derive(Debug, Serialize, Deserialize, FromRow, Type)]
pub struct Panel {
    id: Uuid,
    board_id: Uuid,
    pub name: Option<String>,
    #[sqlx(default)]
    comments: Vec<Comment>,
}

impl PgHasArrayType for Panel {
    fn array_type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("_panel")
    }
}

#[derive(Debug, Serialize, Deserialize, FromRow, Type)]
pub struct Comment {
    id: Uuid,
    panel_id: Uuid,
    pub body: String,
}

impl PgHasArrayType for Comment {
    fn array_type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("_comment")
    }
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    Ok(())
}

Info

• SQLx version: 0.7.4
• SQLx features enabled: ["postgres", "uuid", "runtime-tokio", "tls-rustls"]
• Database server and version: PostgreSQL 16.2
• Operating system: WSL2
• rustc --version: 1.79.0

[MirrorBytes]

The workaround in place is to remove the derive Type from Comment and Vec<Comment> from Panel then query comments separately, but it's more taxing on the SQL server than a left outer join.

I'm likely implementing the code wrong with lifetimes. Any valid feedback to resolve said issue will be handy for future implementations.

[MirrorBytes]

I figured I'd add the SQL to replicate the desired outcome:

SQL to setup test database:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE board (
    id UUID NOT NULL default uuid_generate_v4() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE panel (
    id UUID NOT NULL default uuid_generate_v4() PRIMARY KEY,
    board_id UUID NOT NULL,
    name text NOT NULL,
    CONSTRAINT fk_board
        FOREIGN KEY(board_id)
        REFERENCES board(id)
        ON DELETE CASCADE
);

CREATE TABLE comment (
    id UUID NOT NULL default uuid_generate_v4() PRIMARY KEY,
    board_id UUID NOT NULL,
    panel_id UUID NULL,
    body text NOT NULL,
    CONSTRAINT fk_board
        FOREIGN KEY(board_id)
        REFERENCES board(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_panel
        FOREIGN KEY(panel_id)
        REFERENCES panel(id)
        ON DELETE CASCADE
);

SQL to test use case:

INSERT INTO board (id, name) VALUES ('8ee4b31f-b678-496e-945c-719319428740', 'Test Board');

INSERT INTO panel (id, board_id, name) VALUES ('e9c28883-4eec-4f15-b3db-1e704b4cd18f', '8ee4b31f-b678-496e-945c-719319428740', 'Test Panel');

INSERT INTO comment (board_id, panel_id, body) VALUES ('8ee4b31f-b678-496e-945c-719319428740', 'e9c28883-4eec-4f15-b3db-1e704b4cd18f', 'Test Comment');

SELECT
	board.*,
	array_agg(pa) AS "panels"
FROM board
JOIN (
	SELECT
		panel.*,
		array_agg(co) AS "comments"
	FROM panel
	LEFT OUTER JOIN comment as co
		ON panel.id = co.panel_id
	GROUP BY panel.id
) pa ON pa.board_id = board.id
WHERE board.id = '8ee4b31f-b678-496e-945c-719319428740'
GROUP BY board.id;

The above SQL should return the board with an array of panels with a nested array of comments for the panels. There's some additional logic to be applied (hence the additional board_id column in comments), but the SQL runs successfully with the desired result.

I can add test code for Rust as well if necessary.

[MirrorBytes]

I fixed this by cloning the main branch into a vendors directory outside of the project, setting the dependency path to it, then removing the impl PgHasArrayType for both panel and comment structs. I'm assuming the fix #2940 + the array changes I made to the SQL queries have fixed this issue for nesting Type structs and using JOIN to reconcile returned data.