Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Escaping UUID field in .fetch_related #151

Closed
nsidnev opened this issue Jul 4, 2019 · 3 comments · Fixed by #182
Closed

Escaping UUID field in .fetch_related #151

nsidnev opened this issue Jul 4, 2019 · 3 comments · Fixed by #182
Labels
bug Something isn't working

Comments

@nsidnev
Copy link
Contributor

nsidnev commented Jul 4, 2019

Describe the bug
The primary key UUID (maybe not only UUID) is not escaped when fetching external entities for the model using .fetch_related.

To Reproduce

import asyncio
import logging
import uuid

import asyncpg
from tortoise import Tortoise, fields, models

logging.basicConfig(level=logging.DEBUG)

POSTGRES_URL = "postgres://postgres:postgres@172.20.0.2/postgres"

MODEL_A_ID = uuid.uuid4()

QUERY = """
SELECT "sq0"."_backward_relation_key" "_backward_relation_key","modelb"."id" "id"
FROM "modelb"
JOIN (
    SELECT "modela_id" "_backward_relation_key","modelb_id" "_forward_relation_key"
    FROM "modelb_modela"
    WHERE "modela_id" IN ($1)
) "sq0" ON "sq0"."_forward_relation_key"="modelb"."id"
"""


class ModelA(models.Model):
    id = fields.UUIDField(pk=True)


class ModelB(models.Model):
    id = fields.UUIDField(pk=True)
    a_models = fields.ManyToManyField("models.ModelA", related_name="b_models")


async def main():
    await Tortoise.init(db_url=POSTGRES_URL, modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

    a = await ModelA.create(id=MODEL_A_ID)
    b = await ModelB.create(id=uuid.uuid4())
    await b.a_models.add(a)

    conn = await asyncpg.connect(POSTGRES_URL)
    logging.info("asyncpg result %s", await conn.fetch(QUERY, MODEL_A_ID))
	await conn.close()

    a = await ModelA.get(id=MODEL_A_ID)
    await a.fetch_related("b_models")

    await Tortoise.close_connections()


asyncio.run(main())

Expected behavior
Escaping the UUID value in a query as in the asyncpg example.

Additional context
Possibly related to the #81 issue and #122 PR.

Traceback 1 - PostgreSQL Syntax Error

DEBUG:asyncio:Using selector: EpollSelector
INFO:tortoise:Tortoise-ORM startup
    connections: {'default': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'port': 5432, 'database': 'postgres', 'host': '172.20.0.2', 'user': 'postgres', 'password': 'postgres'}}}
    apps: {'models': {'models': ['__main__'], 'default_connection': 'default'}}
DEBUG:db_client:Created connection <asyncpg.connection.Connection object at 0x7fd621f828b8> with params: {'host': '172.20.0.2', 'port': 5432, 'user': 'postgres', 'database': 'postgres'}
DEBUG:db_client:CREATE TABLE IF NOT EXISTS "modela" ("id" UUID NOT NULL PRIMARY KEY); CREATE TABLE IF NOT EXISTS "modelb" ("id" UUID NOT NULL PRIMARY KEY); CREATE TABLE IF NOT EXISTS "modelb_modela" ("modelb_id" UUID NOT NULL REFERENCES "modelb" (id) ON DELETE CASCADE,"modela_id" UUID NOT NULL REFERENCES "modela" (id) ON DELETE CASCADE);
DEBUG:db_client:INSERT INTO "modela" ("id") VALUES ($1): ['075c03a6-b777-4044-876e-d5877478318d']
DEBUG:db_client:INSERT INTO "modelb" ("id") VALUES ($1): ['8a5ad9d6-fc30-4aba-a41b-377595cddb5f']
DEBUG:db_client:SELECT "modelb_id","modela_id" FROM "modelb_modela" WHERE "modelb_id"='8a5ad9d6-fc30-4aba-a41b-377595cddb5f' AND "modela_id"='075c03a6-b777-4044-876e-d5877478318d'
DEBUG:db_client:INSERT INTO "modelb_modela" ("modela_id","modelb_id") VALUES ('075c03a6-b777-4044-876e-d5877478318d','8a5ad9d6-fc30-4aba-a41b-377595cddb5f')
INFO:root:asyncpg result [<Record _backward_relation_key=UUID('075c03a6-b777-4044-876e-d5877478318d') id=UUID('8a5ad9d6-fc30-4aba-a41b-377595cddb5f')>]
DEBUG:db_client:SELECT "id" FROM "modela" WHERE "id"='075c03a6-b777-4044-876e-d5877478318d' LIMIT 2
DEBUG:db_client:SELECT "sq0"."_backward_relation_key" "_backward_relation_key","modelb"."id" "id" FROM "modelb" JOIN (SELECT "modela_id" "_backward_relation_key","modelb_id" "_forward_relation_key" FROM "modelb_modela" WHERE "modela_id" IN (075c03a6-b777-4044-876e-d5877478318d)) "sq0" ON "sq0"."_forward_relation_key"="modelb"."id"
Traceback (most recent call last):
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 63, in translate_exceptions_
    return await func(self, *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 30, in retry_connection_
    return await func(self, *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 174, in execute_query
    return await connection.fetch(query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 421, in fetch
    return await self._execute(query, args, 0, timeout)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1414, in _execute
    query, args, limit, timeout, return_status=return_status)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1422, in __execute
    return await self._do_execute(query, executor, timeout)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1434, in _do_execute
    stmt = await self._get_statement(query, None)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 329, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "c03a6"

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "main.py", line 50, in <module>
    asyncio.run(main())
  File "/usr/lib64/python3.7/asyncio/runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "/usr/lib64/python3.7/asyncio/base_events.py", line 584, in run_until_complete
    return future.result()
  File "main.py", line 47, in main
    await a.fetch_related("b_models")
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/models.py", line 422, in fetch_related
    await db.executor_class(model=self.__class__, db=db).fetch_for_list([self], *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 284, in fetch_for_list
    await self._execute_prefetch_queries(instance_list)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 267, in _execute_prefetch_queries
    await self._do_prefetch(instance_list, field, related_query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 259, in _do_prefetch
    return await self._prefetch_m2m_relation(instance_id_list, field, related_query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 201, in _prefetch_m2m_relation
    raw_results = await self.db.execute_query(query.get_sql())
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 65, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: syntax error at or near "c03a6"

Traceback 2 - Column Does Not Exist Error

DEBUG:asyncio:Using selector: EpollSelector
INFO:tortoise:Tortoise-ORM startup
    connections: {'default': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'port': 5432, 'database': 'postgres', 'host': '172.20.0.2', 'user': 'postgres', 'password': 'postgres'}}}
    apps: {'models': {'models': ['__main__'], 'default_connection': 'default'}}
DEBUG:db_client:Created connection <asyncpg.connection.Connection object at 0x7f2d08f017e0> with params: {'host': '172.20.0.2', 'port': 5432, 'user': 'postgres', 'database': 'postgres'}
DEBUG:db_client:CREATE TABLE IF NOT EXISTS "modela" ("id" UUID NOT NULL PRIMARY KEY); CREATE TABLE IF NOT EXISTS "modelb" ("id" UUID NOT NULL PRIMARY KEY); CREATE TABLE IF NOT EXISTS "modelb_modela" ("modelb_id" UUID NOT NULL REFERENCES "modelb" (id) ON DELETE CASCADE,"modela_id" UUID NOT NULL REFERENCES "modela" (id) ON DELETE CASCADE);
DEBUG:db_client:INSERT INTO "modela" ("id") VALUES ($1): ['c555b12e-7124-41e4-bc83-b63cbd070b19']
DEBUG:db_client:INSERT INTO "modelb" ("id") VALUES ($1): ['98144713-654c-41bc-bf27-0bdd62372da4']
DEBUG:db_client:SELECT "modelb_id","modela_id" FROM "modelb_modela" WHERE "modelb_id"='98144713-654c-41bc-bf27-0bdd62372da4' AND "modela_id"='c555b12e-7124-41e4-bc83-b63cbd070b19'
DEBUG:db_client:INSERT INTO "modelb_modela" ("modela_id","modelb_id") VALUES ('c555b12e-7124-41e4-bc83-b63cbd070b19','98144713-654c-41bc-bf27-0bdd62372da4')
INFO:root:asyncpg result [<Record _backward_relation_key=UUID('c555b12e-7124-41e4-bc83-b63cbd070b19') id=UUID('98144713-654c-41bc-bf27-0bdd62372da4')>]
DEBUG:db_client:SELECT "id" FROM "modela" WHERE "id"='c555b12e-7124-41e4-bc83-b63cbd070b19' LIMIT 2
DEBUG:db_client:SELECT "sq0"."_backward_relation_key" "_backward_relation_key","modelb"."id" "id" FROM "modelb" JOIN (SELECT "modela_id" "_backward_relation_key","modelb_id" "_forward_relation_key" FROM "modelb_modela" WHERE "modela_id" IN (c555b12e-7124-41e4-bc83-b63cbd070b19)) "sq0" ON "sq0"."_forward_relation_key"="modelb"."id"
Traceback (most recent call last):
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 63, in translate_exceptions_
    return await func(self, *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 30, in retry_connection_
    return await func(self, *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 174, in execute_query
    return await connection.fetch(query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 421, in fetch
    return await self._execute(query, args, 0, timeout)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1414, in _execute
    query, args, limit, timeout, return_status=return_status)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1422, in __execute
    return await self._do_execute(query, executor, timeout)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 1434, in _do_execute
    stmt = await self._get_statement(query, None)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/asyncpg/connection.py", line 329, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.UndefinedColumnError: column "c555b12e" does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "main.py", line 51, in <module>
    asyncio.run(main())
  File "/usr/lib64/python3.7/asyncio/runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "/usr/lib64/python3.7/asyncio/base_events.py", line 584, in run_until_complete
    return future.result()
  File "main.py", line 46, in main
    await a.fetch_related("b_models")
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/models.py", line 422, in fetch_related
    await db.executor_class(model=self.__class__, db=db).fetch_for_list([self], *args)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 284, in fetch_for_list
    await self._execute_prefetch_queries(instance_list)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 267, in _execute_prefetch_queries
    await self._do_prefetch(instance_list, field, related_query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 259, in _do_prefetch
    return await self._prefetch_m2m_relation(instance_id_list, field, related_query)
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/base/executor.py", line 201, in _prefetch_m2m_relation
    raw_results = await self.db.execute_query(query.get_sql())
  File "/home/nik/.virtualenvs/tortoise-pk-error/lib/python3.7/site-packages/tortoise/backends/asyncpg/client.py", line 65, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: column "c555b12e" does not exist

Python version: CPython 3.7.3
Installed packages:
aenum==2.1.2
aiosqlite==0.10.0
asyncpg==0.18.3
ciso8601==2.1.1
PyPika==0.28.0
tortoise-orm==0.12.2
@grigi
Copy link
Member

grigi commented Jul 4, 2019

Thank you for your bug report.

Yes, as per your guess, this is definitely related to #81
I have helped adding parameter support to pypika, but so far only implemented them for INSERT statements.
Need to fix them up for all SELECT/UPDATE/DELETE statements as well.

Actually a bit surprised that UUID behaves so badly 🤔 It could be related to PostgreSQL natively supporting UUID as 128-bit binaries, and the other DB's using text.

@grigi grigi added the bug Something isn't working label Jul 4, 2019
@grigi grigi mentioned this issue Jul 10, 2019
Open
72 tasks
This was referenced Sep 3, 2019
Merged
Merged
@grigi grigi closed this as completed in #182 Sep 5, 2019
grigi added a commit that referenced this issue Sep 5, 2019
@grigi
Fix non int PK (#182)
a81a7d7 
Fixes #151

* Fixes FK use cases of UUIDField as it works differently than int-autonumbers:
  * Is pre-generated, not post-generated.
  * Requires escaping/encoding differently for different DB backends.
* Fixed M2M cases with UUIDFields.
* Fixed select_related with UUIDFields.
@nsidnev
Copy link
Contributor Author

nsidnev commented Sep 5, 2019

Thanks. Your fix worked!

@grigi
Copy link
Member

grigi commented Sep 5, 2019

A pleasure! Got there in the end!
Thank you for pointing out the flaw 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix non int pk tortoise/tortoise-orm
2 participants
@grigi @nsidnev