`column_as()` breaks PostgreSQL enum

[Scooter1337]

Description

column_as() breaks PostgreSQL enum

I am now forced to use 'role' instead of 'user_role'

Steps to Reproduce

1. Create Table which uses a native Postgres enum
2. Query the table with the enum in a join, and use column_as to rename the column
3. Use into_model or into_json

Expected Behavior

Returning the role when using either into_model and into_json

Actual Behavior

into_model && column_as

Err(
    Query(
        SqlxError(
            ColumnDecode {
                index: "\"user_role\"",
                source: "mismatched types; Rust type `core::option::Option<alloc::string::String>` (as SQL type `TEXT`) is not compatible with SQL type `Role`",
            },
        ),
    ),
)

into_json && column_as
(It does not show up in the json)

Object {
            "id": Number(2),
            "membership_uuid": String("jpnaspkni45benpjstcons6q"),
            "name": String("Test"),
            "user_email": String("test@email.com"),
            "user_first_name": String("test3"),
            "user_last_name": String("test3"),
            "uuid": String("exr98cm55qdz5ckllk8vx0ni"),
        },

into_model && column

ManagementMember {
            user_first_name: "test3",
            user_last_name: "test3",
            user_email: "test@email.com",
            role: Viewer,
            membership_uuid: "jpnaspkni45benpjstcons6q",
        },

into_json && column

Object {
            "id": Number(2),
            "membership_uuid": String("jpnaspkni45benpjstcons6q"),
            "name": String("Test"),
            "role": String("VIEWER"),
            "user_email": String("test@email.com"),
            "user_first_name": String("test3"),
            "user_last_name": String("test3"),
            "uuid": String("exr98cm55qdz5ckllk8vx0ni"),
        },

Reproduces How Often

Always

Workarounds

Not using column_as but using column

Reproducible Example

Don't have time to open a PR, here is my faulty query:

#[derive(Debug, Serialize, FromQueryResult, Clone, PartialEq)]
pub struct ManagementMembers {
    pub user_first_name: String,
    pub user_last_name: String,
    pub user_email: String,
    pub user_role: Role,
    pub membership_uuid: String,
}

Management::find()
        .filter(management::Column::Uuid.contains(uuid))
        .column_as(user::Column::FirstName, "user_first_name")
        .column_as(user::Column::LastName, "user_last_name")
        .column_as(user::Column::Email, "user_email")
        .column_as(management_member::Column::Uuid, "membership_uuid")
        .column_as(management_member::Column::Role, "user_role")
        .join(
            sea_orm::JoinType::InnerJoin,
            management::Relation::ManagementMember.def(),
        )
        .join(
            sea_orm::JoinType::InnerJoin,
            management_member::Relation::User.def(),
        )
        .into_model::<ManagementMembers>()
        .all(db)
        .await;

Working query:

#[derive(Debug, Serialize, FromQueryResult, Clone, PartialEq)]
pub struct ManagementMembers {
    pub user_first_name: String,
    pub user_last_name: String,
    pub user_email: String,
    pub role: Role,
    pub membership_uuid: String,
}

Management::find()
        .filter(management::Column::Uuid.contains(uuid))
        .column_as(user::Column::FirstName, "user_first_name")
        .column_as(user::Column::LastName, "user_last_name")
        .column_as(user::Column::Email, "user_email")
        .column_as(management_member::Column::Uuid, "membership_uuid")
        .column(management_member::Column::Role)
        .join(
            sea_orm::JoinType::InnerJoin,
            management::Relation::ManagementMember.def(),
        )
        .join(
            sea_orm::JoinType::InnerJoin,
            management_member::Relation::User.def(),
        )
        .into_model::<ManagementMembers>()
        .all(db)
        .await;

Versions

├── sea-orm v0.12.3
│ ├── sea-orm-macros v0.12.3 (proc-macro)
│ │ ├── sea-bae v0.2.0 (proc-macro)
│ ├── sea-query v0.30.2
│ ├── sea-query-binder v0.5.0
│ │ ├── sea-query v0.30.2 (*)

Ubuntu 22.04.3 LTS x86_64
PostgreSQL v16

[tyt2y3]

I believe this was discussed before, but I could not find the thread. A failing testcase that pinpoints the problem is like half way to fixing the bug.

I am guessing, into_model and into_json does not do the proper casting.

[tyt2y3]

That'll be appreciated!

[jmelo11]

Facing the same issue, into_json breaks the select_as type and cast all fields as String.

[NateAGeek]

I am facing the same issue, but not via into model. I am using SearchAccountsQueryResult::find_by_statement where SearchAccountsQueryResult is a FromQueryResult structure. I am assuming find_by_statement is doing some sort of into_model under the hood.

[NateAGeek]

Here are some more of my findings:
CallStack:

sqlx_postgres::types::str::<impl sqlx_core::types::Type<sqlx_postgres::database::Postgres> for str>::compatible (/sqlx-postgres-0.7.2/src/types/str.rs:15)

<&T as sqlx_core::types::Type<DB>>::compatible (/sqlx-core-0.7.2/src/types/mod.rs:222)

sqlx_postgres::types::str::<impl sqlx_core::types::Type<sqlx_postgres::database::Postgres> for alloc::string::String>::compatible (/sqlx-postgres-0.7.2/src/types/str.rs:52)

<core::option::Option<T> as sqlx_core::types::Type<DB>>::compatible (/sqlx-core-0.7.2/src/types/mod.rs:233)

sqlx_core::row::Row::try_get (/sqlx-core-0.7.2/src/row.rs:120)

<alloc::string::String as sea_orm::executor::query::TryGetable>::try_get_by (/sea-orm-1.0.0/src/executor/query.rs:302)

<hangout_microservices::entities::sea_orm_active_enums::FriendshipStatus as sea_orm::executor::query::TryGetable>::try_get_by (/Users/nateageek/Development/Rust/hangout-microservices/hangout-microservices/src/entities/sea_orm_active_enums.rs:45)

<core::option::Option<T> as sea_orm::executor::query::TryGetable>::try_get_by (/sea-orm-1.0.0/src/executor/query.rs:182)

sea_orm::executor::query::TryGetable::try_get (/sea-orm-1.0.0/src/executor/query.rs:40)

sea_orm::executor::query::QueryResult::try_get (/sea-orm-1.0.0/src/executor/query.rs:95)

<hangout_microservices_search::queries::search_accounts::SearchAccountsQueryResult as sea_orm::entity::model::FromQueryResult>::from_query_result (src/queries/search_accounts.rs:23)

<sea_orm::executor::select::SelectModel<M> as sea_orm::executor::select::SelectorTrait>::from_raw_query_result (/sea-orm-1.0.0/src/executor/select.rs:115)

sea_orm::executor::select::SelectorRaw<S>::all::{{closure}} (/sea-orm-1.0.0/src/executor/select.rs:972)

hangout_microservices_search::queries::search_accounts::search_accounts::{{closure}} (src/queries/search_accounts.rs:85)

hangout_microservices_search::queries::Query::search_accounts::{{closure}} (src/queries/mod.rs:35)

<hangout_microservices_search::queries::Query as async_graphql::resolver_utils::container::ContainerType>::resolve_field::{{closure}}::{{closure}} (src/queries/mod.rs:19)

<hangout_microservices_search::queries::Query as async_graphql::resolver_utils::container::ContainerType>::resolve_field::{{closure}} (src/queries/mod.rs:19)

<core::pin::Pin<P> as core::future::future::Future>::poll (@<core::pin::Pin<P> as core::future::future::Future>::poll:27)

<async_graphql::types::query_root::QueryRoot<T> as async_graphql::resolver_utils::container::ContainerType>::resolve_field::{{closure}} (/async-graphql-7.0.1/src/types/query_root.rs:102)

<core::pin::Pin<P> as core::future::future::Future>::poll (@<core::pin::Pin<P> as core::future::future::Future>::poll:27)

file: sqlx-postgres-0.7.2/src/types/str.rs

fn compatible(ty: &PgTypeInfo) -> bool {
        [
            PgTypeInfo::TEXT,
            PgTypeInfo::NAME,
            PgTypeInfo::BPCHAR,
            PgTypeInfo::VARCHAR,
            PgTypeInfo::UNKNOWN,
        ]
        .contains(ty)
    }

My first impressions is based on watching how it went through the calls. It appears that sea-orm tries to build a custom macro for the Enum type. However, due to the Pg type being alloc::sync::Arc<sqlx_postgres::type_info::PgCustomType, alloc::alloc::Global> it does not have a custom compatibility to support it. So, it fails to decode it and throws an error. This bug has cost me a day of work trying to resolve. I might be able to create a PR. But this is the underlying issue. Although, I did see that the raw data is there as a string. But this also could be a slight issue with the sqlx-postgres.

[NateAGeek]

Ok, I truly do not like this hack. But for right now I am casting my enum into a "TEXT" and returning that in my query result.

.expr(
        Expr::col(<ENUM COLUMN HERE>).cast_as(Alias::new("TEXT")),
    )

My SearchAccountsQueryResult status enum now works, since it is a "TEXT" value and sqlx-postgres can decode and view it as a compatible type for a rust "String", and it will work as I am just returning and comparing enum strings on my frontend. I'm not sure if I should post this as a bug with the sqlx-postgres or here. As, technically we are asking sqlx-postgres to convert a "CustomType" into a "String" from sea_orm DeriveActiveEnum expanded macro... But for right now, here is the hacked solution.

[elichai]

Hi, I've also hit this with the following:

#[derive(DeriveEntityModel)]
#[sea_orm(table_name = "keys")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: KeyId,
    pub owner_id: UserId,
    pub schema: KeyType,
    pub data: Vec<u8>,
}

#[derive(DeriveActiveEnum, EnumIter)]
#[sea_orm(rs_type = "String", db_type = "Enum", enum_name = "keytype")]
pub enum KeyType {
    #[sea_orm(string_value = "not initialized yet")]
    NotInititalizedYet,
    #[sea_orm(string_value = "a")]
    A,
    #[sea_orm(string_value = "b")]
    B,
    #[sea_orm(string_value = "c")]
    C,
}
#[derive(FromQueryResult, DerivePartialModel)]
#[sea_orm(entity = "Entity")]
pub struct KeyMetadata {
    #[sea_orm(from_col = "id")]
    key_id: KeyId,
    #[sea_orm(from_col = "owner_id")]
    owner: UserId,
    #[sea_orm(from_col = "schema")]
    key_type: KeyType,
}

And I'm getting the following error on postgres:

Query Error: error occurred while decoding column "key_type": mismatched types; Rust type `core::option::Option<alloc::string::String>` (as SQL type `TEXT`) is not compatible with SQL type `keytype`

[jackbackes]

@tyt2y3

A failing testcase that pinpoints the problem is like half way to fixing the bug.

Ask and you shall receive: #2368

[NateAGeek]

@tyt2y3

A failing testcase that pinpoints the problem is like half way to fixing the bug.

Ask and you shall receive: #2368

You the man! Thank you for getting this test case setup!

[rschulman]

I am also running into this issue trying to use a defined Postgres enum as a field in a custom response struct. Has anyone had any success in solving this?

[NateAGeek]

@rschulman my solution for casting as "TEXT" seems to work.