[bug?] Models on nested FilteredRelation are not joined

173 views

Skip to first unread message

Damiano Porta

unread,

Jul 2, 2021, 8:58:15 AM7/2/21

to Django developers (Contributions to Django itself)

Hello,

i have just upgraded to Django 3.2 to use nested FilteredRelation, but i have noticed strange behaviors, this is the code:

from django.db.models import FilteredRelation, Q
from django.db import models

class A(models.Model):
    foo = models.IntegerField()

class B(models.Model):
    value = models.IntegerField()

class C(models.Model):
    a = models.ForeignKey('A', on_delete=models.CASCADE)
b = models.ForeignKey('B', on_delete=models.CASCADE)

>>> qs = A.objects.annotate(bla=FilteredRelation('c', condition=Q(c__b__value=1))).select_related('bla')
>>> str(qs.query)
'SELECT "base_a"."id", "base_a"."foo", bla."id", bla."a_id", bla."b_id" FROM "base_a" LEFT OUTER JOIN "base_c" bla ON ("base_a"."id" = bla."a_id" AND ("base_b"."value" = 1))'

PROBLEM 1:

It is not joining the model B. As you can see on the query there is "base_b" without the join.

PROBLEM 2:

There is another strange behavior, if i "execute" the queryset i get this:

>>> qs
Traceback (most recent call last):
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: ERRORE: elemento FROM per la tabella "t4" mancante
LINE 1: ... "base_c" bla ON ("base_a"."id" = bla."a_id" AND (T4."value"...
                                                             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/models/query.py", line 256, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/models/query.py", line 262, in __len__
    self._fetch_all()
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1169, in execute_sql
    cursor.execute(sql, params)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/damiano/.virtualenvs/myproj/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ERRORE: elemento FROM per la tabella "t4" mancante
LINE 1: ... "base_c" bla ON ("base_a"."id" = bla."a_id" AND (T4."value"...
                                                             ^

there is no T4 on the previous qs.query, but i try again and the sql output is magically changed. (why?)

>>> str(qs.query)
'SELECT "base_a"."id", "base_a"."foo", bla."id", bla."a_id", bla."b_id" FROM "base_a" LEFT OUTER JOIN "base_c" bla ON ("base_a"."id" = bla."a_id" AND (T4."value" = 1))'

NOTE:

I also tested the code changing:

.annotate(bla=FilteredRelation('c', condition=Q(c__b__value=1)))

with

.annotate(bla=FilteredRelation('c__b', condition=Q(c__b__value=1)))

In this case it works but it adds the B fields on the SELECT... i do not need them i just need to add that model on the condition without having its fields, I just need fields of A and fields of C