"no such column" when combining FilteredRelation and multi-table inheritance models

[Javier Ayres]

I have found that a certain queryset that combines multi-table inheritance models, FilteredRelation, and aggregation, can result in a "no such column" database error.

The following models.py and tests.py modules can be placed in the app of a newly created Django project to reproduce the bug. I've successfully reproduced it with Django 3.2.4 (using PostgreSQL) and 4.1.4 (using sqlite3). The tests module includes two tests which are pretty similar, however one of them fails because of this bug, the full stacktrace being:

Traceback (most recent call last):
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: myapp_user.superuser

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

Traceback (most recent call last):
  File "/tmp/dec1389/myapp/tests.py", line 34, in test_fails
    print(
  File "/tmp/env/lib/python3.10/site-packages/django/db/models/query.py", line 370, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
  File "/tmp/env/lib/python3.10/site-packages/django/db/models/query.py", line 394, in __iter__
    self._fetch_all()
  File "/tmp/env/lib/python3.10/site-packages/django/db/models/query.py", line 1867, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/tmp/env/lib/python3.10/site-packages/django/db/models/query.py", line 87, in __iter__
    results = compiler.execute_sql(
  File "/tmp/env/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1398, in execute_sql
    cursor.execute(sql, params)
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/tmp/env/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/env/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: myapp_user.superuser

models.py:

from django.db import models

class School(models.Model):
    name = models.CharField(max_length=100)


class User(models.Model):
    superuser = models.BooleanField(default=True)


class Student(User):
    superstudent = models.BooleanField(default=True)
    school = models.ForeignKey(School, on_delete=models.CASCADE)

tests.py

from django.test import TestCase
from django.db.models import Q, FilteredRelation, Count
from .models import School, Student


class FilteredRelationBugTestCase(TestCase):

    @classmethod
    def setUpClass(cls):
        super().setUpClass()
        p = School.objects.create(name='p1')
        Student.objects.create(school=p, superuser=False, superstudent=False)
        Student.objects.create(school=p, superuser=False, superstudent=True)
        Student.objects.create(school=p, superuser=True, superstudent=False)
        Student.objects.create(school=p, superuser=True, superstudent=True)

    def test_works(self):
        print(
            School.objects.annotate(
                superstudents=FilteredRelation(
                    'student',
                    condition=Q(
                        student__superstudent=True,
                    ),
                ),
                superuser_count=Count(
                    'superstudents',
                    filter=Q(superstudents__superuser=True)
                ),
            ).all()
        )

    def test_fails(self):
        print(
            School.objects.annotate(
                superusers=FilteredRelation(
                    'student',
                    condition=Q(
                        student__superuser=True,
                    ),
                ),
                superstudents_count=Count(
                    'superusers',
                    filter=Q(superusers__superstudent=True)
                ),
            ).all()
        )

[Mariusz Felisiak]

Thanks for the detailed report! I think it has the same root cause as #33929 and should be marked as a duplicate.

[Mariusz Felisiak]

This case is more complicated so we decided to fix it separately, see a regression test:

tests/filtered_relation/models.py

@@ -44 +44 @@
     name = models.CharField(max_length=50, unique=True)
 
 
+class Location(models.Model):
+    small = models.BooleanField(default=False)
+
+
+class Library(Location):
+    editors = models.ManyToManyField(Editor, related_name="libraries")
+
+
 class Reservation(models.Model):
     NEW = "new"
     STOPPED = "stopped"

tests/filtered_relation/tests.py

@@ -24 +24 @@
     Currency,
     Editor,
     ExchangeRate,
+    Library,
     RentalSession,
     Reservation,
     Seller,
@@ -825 +826 @@
             [self.book1],
         )
 
+    def test_condition_spans_mti(self):
+        library = Library.objects.create(small=True)
+        library.editors.add(self.editor_a)
+        self.assertSequenceEqual(
+            Editor.objects.annotate(
+                small_libraries=FilteredRelation(
+                    "libraries", condition=Q(libraries__small=True)
+                ),
+            )
+            .filter(
+                small_libraries__isnull=False,
+            )
+            .order_by("id"),
+            [self.editor_a],
+        )
+
 
 class FilteredRelationAnalyticalAggregationTests(TestCase):
     @classmethod

[Simon Charette]

After further investigation this issue is fundamentally the same as the one that we currently error out about when trying to do

Author.objects.annotate(
    book_editor=FilteredRelation(
        "book",
        condition=Q(book__editor__name__icontains="b"),
    ),
)

Which we currently out with FilteredRelation's condition doesn't support nested relations deeper than the relation_name (got 'book__editor__name__icontains' for 'book'). (see ​test and ​origin)

The rationale behind this classification is that student__superuser is actually an alias for student__user_ptr__superuser so the reported case here

School.objects.annotate(
    superusers=FilteredRelation(
        'student',
        condition=Q(
            student__superuser=True,
        ),
    )
)

Is an alias for

School.objects.annotate(
    superusers=FilteredRelation(
        'student',
        condition=Q(
            student__user__ptr__superuser=True,
        ),
    )
)

The latter is ​caught by the depth check but not the former because the logic is not aware of MTI aliasing

To summarize, we should likely adapt the depth check to consider MTI aliasing to address the bug reported here (so it doesn't reach the db and result in an opaque SQL failure) and we could then consider a new feature to generically support relation__join references in condition (haven't invested much time in figuring out what that would even mean.

I personally don't think that it would make sense to implement the latter as this problem can be circumvented by targeting the relation where the field lives. In the provided test case that means doing

tests/filtered_relation/tests.py

@@ -832 +832 @@
         self.assertSequenceEqual(
             Editor.objects.annotate(
                 small_libraries=FilteredRelation(
-                    "libraries", condition=Q(libraries__small=True)
+                    "libraries__location_ptr",
+                    condition=Q(libraries__location_ptr__small=True),
                 ),
             )
             .filter(

And in the user's reported case that means chaining filtered relations which is supported

School.objects.annotate(
    superstudents=FilteredRelation(
        'student',
        condition=Q(
            student__superstudent=True,
        ),
    )
    superusers=FilteredRelation(
        'superstudents__user_ptr',
        condition=Q(
            superstudents__user_ptr__superuser=True,
        ),
    ),
    superstudents_count=Count(
        'superusers'
    ),
)