Filtering a field in_ a list of Enum values breaks PostgreSQL CAST operator

[neilferreira]

Basically just copy/pasting an issue from the sqlalchemy project that exists in this project at the moment. Credit to @jordan-dimov

When I have Python code like this:

Schema:

from enum import Enum
from sqlalchemy import Enum as SQLEnum
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta

Base: DeclarativeMeta = declarative_base()


class EModelState(str, Enum):
    PENDING = "pending"
    STARTING = "starting"


class MyModel(Base):
    state = Column(SQLEnum(EModelState))

Query code:

query.filter(models.MyModel.state.in_(states))

where states is a list of Enum values, it translates to the following SQL:

WHERE models_mymodel.state IN CAST((:state_1_1, :state_1_2) AS emodelstate))]
[parameters: {'state_1_1': 'PENDING', 'state_1_2': 'STARTING'}]

which breaks with:

sqlalchemy.exc.DatabaseError: (common.aurora_data_api.exceptions.DatabaseError) An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: syntax error at or near "CAST"

I tried some things manually in PostgreSQL and noticed that if I do: select CAST('PENDING' AS emodelstate) that works fine and gives me an actual DB Enum value. But if I try to cast more than one value: select CAST(('PENDING', 'STARTING') AS emodelstate) this fials with ERROR: cannot cast type record to emodelstate.

[neilferreira]

Cross-linking original issue sqlalchemy/sqlalchemy#7097