Opened 23 months ago

Last modified 12 months ago

#34229 assigned Bug

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

Reported by: Javier Ayres Owned by: Turonbek Kuzibaev
Component: Database layer (models, ORM) Version: 4.1
Severity: Normal Keywords: filteredrelation such column
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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()
        )

Change History (4)

comment:1 by Mariusz Felisiak, 23 months ago

Resolution: duplicate
Status: newclosed
Type: UncategorizedBug

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

comment:2 by Mariusz Felisiak, 19 months ago

Resolution: duplicate
Status: closednew
Triage Stage: UnreviewedAccepted

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

  • tests/filtered_relation/models.py

    diff --git a/tests/filtered_relation/models.py b/tests/filtered_relation/models.py
    index d34a86305f..dcd0197447 100644
    a b class Borrower(models.Model):  
    4444    name = models.CharField(max_length=50, unique=True)
    4545
    4646
     47class Location(models.Model):
     48    small = models.BooleanField(default=False)
     49
     50
     51class Library(Location):
     52    editors = models.ManyToManyField(Editor, related_name="libraries")
     53
     54
    4755class Reservation(models.Model):
    4856    NEW = "new"
    4957    STOPPED = "stopped"
  • tests/filtered_relation/tests.py

    diff --git a/tests/filtered_relation/tests.py b/tests/filtered_relation/tests.py
    index ce75cb01f5..9d38593065 100644
    a b from .models import (  
    2424    Currency,
    2525    Editor,
    2626    ExchangeRate,
     27    Library,
    2728    RentalSession,
    2829    Reservation,
    2930    Seller,
    class FilteredRelationAggregationTests(TestCase):  
    825826            [self.book1],
    826827        )
    827828
     829    def test_condition_spans_mti(self):
     830        library = Library.objects.create(small=True)
     831        library.editors.add(self.editor_a)
     832        self.assertSequenceEqual(
     833            Editor.objects.annotate(
     834                small_libraries=FilteredRelation(
     835                    "libraries", condition=Q(libraries__small=True)
     836                ),
     837            )
     838            .filter(
     839                small_libraries__isnull=False,
     840            )
     841            .order_by("id"),
     842            [self.editor_a],
     843        )
     844
    828845
    829846class FilteredRelationAnalyticalAggregationTests(TestCase):
    830847    @classmethod

comment:3 by Simon Charette, 19 months ago

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

    diff --git a/tests/filtered_relation/tests.py b/tests/filtered_relation/tests.py
    index 9d38593065..b269ed786d 100644
    a b def test_condition_spans_mti(self):  
    832832        self.assertSequenceEqual(
    833833            Editor.objects.annotate(
    834834                small_libraries=FilteredRelation(
    835                     "libraries", condition=Q(libraries__small=True)
     835                    "libraries__location_ptr",
     836                    condition=Q(libraries__location_ptr__small=True),
    836837                ),
    837838            )
    838839            .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'
    ),
)

comment:4 by Turonbek Kuzibaev, 12 months ago

Owner: changed from nobody to Turonbek Kuzibaev
Status: newassigned
Note: See TracTickets for help on using tickets.
Back to Top