is_in and is_not_in don't use CAST when comparing enums.

[0xAndoroid]

Description

.is_in(...) and .is_not_in(...) don't use CAST when comparing enums.

Steps to Reproduce

1. Create a table in Postgres, that has a field with custom type as enum.
2. Make a query to that table, having a condition of this enum field IN vector.

Expected Behavior

The same as with .eq, .ne operators. Resulting query should look something like this.

-- For .eq operator, it generates correctly
WHERE "table"."enum_field" = CAST($1 AS enum_type)
-- For .is_in operator, it generates a different query without CAST
WHERE "table"."enum_field" IN (CAST($1 AS enum_type), CAST($2 AS enum_type))

Actual Behavior

Generated query looks something like this

WHERE "table"."enum_field" IN ($1, $2)

PgDatabaseError is returned.

{severity: Error, code: "42883", message: "operator does not exist: enum_type <> text", ...}

Reproduces How Often

Always, I assume. But the bug was spoted when in subquery.

Workarounds

Well, it is possible to replace .is_in operator with

Condition::any()
    .add(table::Column::EnumField.eq(EnumType::Option1)
    .add(table::Column::EnumField.eq(EnumType::Option2)

This thing works just fine. The generated query looks like this

WHERE
      "table"."enum_field" = CAST($1 AS enum_type) 
      OR
      "table"."enum_field" = CAST($2 AS enum_type)

Reproducible Example

I can do it by request, if anybody fails to reproduce the issue.

Versions

sea-orm v0.11.0
postgres
macos Ventura

[ProbablyClem]

Hey, I looked it up a bit and it seems to me that this issue actually belongs to the sea-query project.
I don't see any duplicate of this issue in the other repos so I think you should close this one and open it again over-there.
I hope it'll help to get it solve faster.

[SteelAlloy]

The bug actually comes from sea-orm, not sea-query (SeaQL/sea-query#667 (comment))

EDIT: I've also encountered the bug with gte : `"operator does not exist: some_enum >= text".

[RageCPP]

Is there any update on this issue?

[0xAndoroid]

Is there any update on this issue?

No, consider using a workaround that I described in the text.

[Expurple]

I've also encountered the bug with gte

@oganexon, it looks like ord operators were intentionally left out by the maintainers

[SteelAlloy]

I've also encountered the bug with gte

@oganexon, it looks like ord operators were intentionally left out by the maintainers

I can see that, but is there a valid reason for it? Comparing the values of the same enum is an SQL-supported operation that makes sense in many cases.

For example, let's imagine a permission enum with ADMIN, MODERATOR, and USER. We might want to check whether such column is >= (greater than or equal to) MODERATOR.

[tyt2y3]

For example, let's imagine a permission enum with ADMIN, MODERATOR, and USER. We might want to check whether such column is >= (greater than or equal to) MODERATOR.

That sounds like a potential footgun, it only makes sense if the enum is backed by an integer, but does > mean more privileges or less?

[Expurple]

does > mean more privileges or less?

It depends on whether the enum was defined as 'ADMIN', 'MODERATOR', 'USER' or as 'USER', 'MODERATOR', 'ADMIN'. I agree that this isn't very obvious, but I personally don't want sea-orm to be so opinionated about its preferred SQL subset

[tyt2y3]

Fair point, may be we can add that