tortoise / tortoise/tortoise-orm

ValuesQuery execution fails when using select_related()

Open
#1,529 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Describe the bug
When trying to join multiple tables and select only specific fields then the ValuesQuery fails.

To Reproduce
Version:

    from tortoise import __version__
    print(f"Tortoise ORM version: {__version__}")

Tortoise ORM version: 0.20.0

The models:

class A(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=10)
    rel = fields.ReverseRelation["models.B"]

class B(Model):
    a = fields.OneToOneField('models.A', related_name='rel')
    id = fields.IntField(pk=True)
    score = fields.IntField()

Create test data:

a = await A.create(name="test")
b = await B.create(score=1, a=a)

The query:

query = A.all().select_related("B").values('name', 'rel__score')

query.sql() generates the proper SQL query:

    raw_sql = query.sql()
    print(raw_sql)

SELECT "a"."name" "name","b"."score" "rel__score" FROM "a" LEFT OUTER JOIN "b" ON "a"."id"="b"."a_id"

Executing the raw SQL works:

    conn = Tortoise.get_connection("default")
    result = await conn.execute_query(raw_sql)
    print(result)

(1, [<Record name='test' rel__score=1>])

Using _execute() works as well:

    result = await query._execute()
    print(result)

[{'name': 'test', 'rel__score': 1}]

Executing the query fails:

    result = await query
    print(result)

asyncpg.exceptions.UndefinedColumnError: column "score" does not exist

Expected behavior
await query should not fail as the query is correct.

Additional context
I played a bit with the ValuesQuery class in queryset.py and added some prints to see where things go wrong.

Added some prints the __await__ :

    def __await__(self) -> Generator[Any, None, Union[List[Dict[str, Any]], Dict[str, Any]]]:
        if self._db is None:
            self._db = self._choose_db()  # type: ignore

        print("==============================")
        print(self.query)
        
        self._make_query()

        print(self.query)
        print("==============================")

        return self._execute().__await__()  # pylint: disable=E1101

The result is:

==============================
SELECT "a"."name" "name","b"."score" "rel__score" FROM "a" LEFT OUTER JOIN "b" ON "a"."id"="b"."a_id"
SELECT "name" "name","score" "rel__score" FROM "a"
==============================

From the looks of it self._make_query() leaves out the table joins for some reason in ValuesQuery.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in queryset.py with ValuesQuery.await and _make_query(), then run the reproduction against the PostgreSQL backend. Compare the query before and after _make_query(); done means awaiting the ValuesQuery preserves the joins and returns the same values as _execute() and the raw SQL.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.