Unable to retrieve array column due to SQL type mismatch: as SQL type `...[]` is not compatible with SQL type `_...`

[vegardgs-ksat]

Bug Description

In postgres, when utilizing arrays of a custom type, the driver rejects retrieving a column from a row if the PgTypeInfo for PgHasArrayType is implemented using the [] syntax. The underlying implementation in postgres stores this array implementation on custom types using a underscore prefix. This does not occur when reading the array out through the query macro.

I get the following failure:

thread 'main' panicked at /Users/vegardgs/.cargo/registry/src/index.crates.io-6f17d22bba15001f/sqlx-core-0.7.4/src/row.rs:72:37:
called `Result::unwrap()` on an `Err` value: ColumnDecode { index: "\"tags\"", source: "mismatched types; Rust type `alloc::vec::Vec<sqlx_postgres_array::Tag>` (as SQL type `tag[]`) is not compatible with SQL type `_tag`" }
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

It was quite surprising behavior, as I was unaware of the actual type name of the postgres enum array - I've always referred to it through the [] syntax. I neither saw any traces of this scenario mentioned in the Types nor PgHasArrayType documentation.

Is this expected behavior? Could this scenario be attempted auto-detected for postgres? Or perhaps only a clarification in the documentation of postgres enums when implementing PgHasArrayType?

Minimal Reproduction

use sqlx::postgres::{PgHasArrayType, PgTypeInfo};
use sqlx::Row;

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "tag", rename_all = "UPPERCASE")]
enum Tag {
    Test,
    Internal,
}

impl PgHasArrayType for Tag {
    fn array_type_info() -> PgTypeInfo {
        // NOTE: Changing `tag[]` to `_tag` resolves the issue.
        PgTypeInfo::with_name("tag[]")
    }
}

#[tokio::main]
async fn main() -> sqlx::Result<()> {
    dotenvy::dotenv().ok();
    let database = sqlx::PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;

    sqlx::query!(
        r#"
    INSERT INTO Items (tags)
    VALUES
        ($1),
        ($2)
        "#,
        &vec![Tag::Test] as _,
        &vec![Tag::Internal, Tag::Test] as _,
    )
    .execute(&database)
    .await?;

    let by_macro: Vec<Vec<Tag>> = sqlx::query!(
        r#"
    SELECT tags as "tags: Vec<Tag>" from Items
        "#
    )
    .fetch_all(&database)
    .await?
    .into_iter()
    .map(|r| r.tags)
    .collect();
    assert!(by_macro.len() >= 2);

    let by_query: Vec<Vec<Tag>> = sqlx::query("SELECT tags FROM Items")
        .fetch_all(&database)
        .await?
        .into_iter()
        // NOTE: This fails
        .map(|r| r.get("tags"))
        .collect();
    assert!(by_query.len() >= 2);

    Ok(())
}

[dependencies]
dotenvy = "0.15.7"
sqlx = { version = "0.7.4", features = ["postgres", "runtime-tokio"] }
tokio = { version = "1.37.0", features = ["macros", "rt", "rt-multi-thread"] }

CREATE TYPE tag as ENUM ('TEST', 'INTERNAL');

CREATE TABLE Items (
    tags tag[] default '{}'::tag[] NOT NULL
);

This is the debug print of the column info for the select query:

   PgColumn {
        ordinal: 0,
        name: tags,
        type_info: PgTypeInfo(
            Custom(
                PgCustomType {
                    oid: Oid(
                        16392,
                    ),
                    name: _tag,
                    kind: Array(
                        PgTypeInfo(
                            Custom(
                                PgCustomType {
                                    oid: Oid(
                                        16393,
                                    ),
                                    name: tag,
                                    kind: Enum(
                                        [
                                            "TEST",
                                            "INTERNAL",
                                        ],
                                    ),
                                },
                            ),
                        ),
                    ),
                },
            ),
        ),
        relation_id: Some(
            16397,
        ),
        relation_attribute_no: Some(
            1,
        ),
    }

Info

• SQLx version: 0.7.4
• SQLx features enabled: postgres, runtime-tokio
• Database server and version: Postgres 16.2
• Operating system: macOS Sonomoa (version 14.4.1)
• rustc --version: rustc 1.77.1 (7cf61ebde 2024-03-27)

[NickUfer]

Got the same problem with the requirement of an underscore infront of the actual type name... don't know why this is necessary and would be good to know why.

Edit: Found the answer + one message above + some below: https://github.com/launchbadge/sqlx/issues/1004#issuecomment-1095002178

It's Postgres' default naming convention for array types.