Error: could not determine data type of parameter $2

[Lepiloff]

databases==0.4.1
python==3.7
postgresql == PostgreSQL 13.2 (Ubuntu 13.2-1.pgdg18.04+1)

Hi there!
I'm having trouble with using queries in sqlalchemy expression language with func . The code is

    async def get(self, pk: int):
        query = (
            select(
                [
                    func.json_build_object(
                        "id",
                        events.c.id,
                        "title",
                        events.c.title,
                        "location",
                        func.json_agg(
                            func.json_build_object(
                                "city",
                                locations.c.city,
                                "street",
                                locations.c.street,
                                "building",
                                locations.c.building,
                            )
                        ),
                        "location_all_columns_example",
                        func.json_agg(func.json_build_object(
                            *itertools.chain(*[(_.name, _) for _ in locations.c])
                        )),
                        "activity",
                        func.json_agg(
                            func.json_build_object(
                                "name",
                                activities.c.name,
                            )
                        ),
                    )
                ]
            )
                .select_from(events.join(locations).join(activities))
                .where(
                and_(
                    events.c.id == pk,
                    locations.c.id == events.c.location_id,
                    activities.c.id == events.c.activities_id,
                )
            )
                .order_by(desc(events.c.created_at))
                .group_by(events.c.id)  # !!! <- IMPORTANT
        )
        ev = dict(await database.fetch_one(query))
        if ev is None:
            raise HTTPException(status_code=404, detail="Event not found")
        return ev

This expression generate next sql query

SELECT json_build_object(:json_build_object_2, events.id, :json_build_object_3, events.title, :json_build_object_4, json_agg(json_build_object(:json_build_object_5, locations.city, :json_build_object_6, locations.street, :json_build_object_7, locations.building)), :json_build_object_8, json_agg(json_build_object(:json_build_object_9, locations.id, :json_build_object_10, locations.lat, :json_build_object_11, locations.long, :json_build_object_12, locations.city, :json_build_object_13, locations.street, :json_build_object_14, locations.building)), :json_build_object_15, json_agg(json_build_object(:json_build_object_16, activities.name))) AS json_build_object_1 
FROM events JOIN locations ON locations.id = events.location_id JOIN activities ON activities.id = events.activities_id 
WHERE events.id = :id_1 AND locations.id = events.location_id AND activities.id = events.activities_id GROUP BY events.id ORDER BY events.created_at DESC

This causes an error

  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/core.py", line 146, in fetch_one
    return await connection.fetch_one(query, values)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/core.py", line 246, in fetch_one
    return await self._connection.fetch_one(built_query)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/backends/postgres.py", line 168, in fetch_one
    row = await self._connection.fetchrow(query, *args)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 646, in fetchrow
    record_class=record_class,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1632, in _execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1655, in __execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1681, in _do_execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 380, in _get_statement
    ignore_custom_codec=ignore_custom_codec,
  File "asyncpg/protocol/protocol.pyx", line 166, in prepare
asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $2

This code absolutely working with sync query to DB like this
ev = session.execute(query).fetchone()

May be any idea about how to solve this?

[waydegilliam]

Having the same problem too. #128 has a couple suggrestions for this but I don't know how to apply them for nested functions like you have in your query.