Invalid input type error despite the use of CAST operator in query.

[Geosynopsis]

• asyncpg version: 0.27.0
• PostgreSQL version: 14
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Docker image postgis/postgis:14-3.3-alpine
• Python version: 3.8.13
• Platform: Darwin Kernel Version 21.6.0, arm64
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: No
• Can the issue be reproduced under both asyncio and uvloop?: Didn't try

Getting invalid input for query argument despite using cast. Here's the code example:

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import sessionmaker
import sqlalchemy as sa
import asyncpg

postgres = "postgresql+asyncpg://username:password@localhost:5432/stac_db"
Base = declarative_base()
engine = create_async_engine(postgres, echo=True)

class TestData(Base):
    __tablename__ = 'tests'
    id = sa.Column(sa.Integer, primary_key=True)
    t = sa.Column(sa.VARCHAR(20))

async def setup():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

    async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

    async with async_session() as session:
        async with session.begin():
            session.add_all(
                [
                    TestData(id=1, t='x'),
                    TestData(id=2, t='z')
                ]
            )
        await session.commit()

async def run_sqlalchemy_asyncpg(id: str):
    async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
    async with async_session() as session:
        q = sa.select(TestData).where(TestData.id > sa.cast(id, sa.BIGINT))
        result = await session.execute(q)
        rows = result.scalars().all()
        print(rows)

async def run_asyncpg(id: str):
    conn = await asyncpg.connect("postgresql://username:password@localhost:5432/stac_db")
    rows = await conn.fetchrow('SELECT * FROM tests WHERE id > CAST($1 AS BIGINT)', id)
    print(rows)
    await conn.close()

import asyncio
loop = asyncio.get_event_loop()
try:
    loop.run_until_complete(setup())
    loop.run_until_complete(run_sqlalchemy_asyncpg('0'))
    loop.run_until_complete(run_asyncpg('0'))
finally:
    loop.run_until_complete(engine.dispose())

In both the sqlalchemy and asyncpg cases, we get following error

 <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: '0' (an integer is required (got type str))

[elprans]

PostgreSQL type inference is sometimes weird like that. Try $1::text::bigint.