Opened 2 years ago
Last modified 13 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 , 2 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Type: | Uncategorized → Bug |
comment:2 by , 19 months ago
Resolution: | duplicate |
---|---|
Status: | closed → new |
Triage Stage: | Unreviewed → Accepted |
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): 44 44 name = models.CharField(max_length=50, unique=True) 45 45 46 46 47 class Location(models.Model): 48 small = models.BooleanField(default=False) 49 50 51 class Library(Location): 52 editors = models.ManyToManyField(Editor, related_name="libraries") 53 54 47 55 class Reservation(models.Model): 48 56 NEW = "new" 49 57 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 ( 24 24 Currency, 25 25 Editor, 26 26 ExchangeRate, 27 Library, 27 28 RentalSession, 28 29 Reservation, 29 30 Seller, … … class FilteredRelationAggregationTests(TestCase): 825 826 [self.book1], 826 827 ) 827 828 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 828 845 829 846 class FilteredRelationAnalyticalAggregationTests(TestCase): 830 847 @classmethod
comment:3 by , 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): 832 832 self.assertSequenceEqual( 833 833 Editor.objects.annotate( 834 834 small_libraries=FilteredRelation( 835 "libraries", condition=Q(libraries__small=True) 835 "libraries__location_ptr", 836 condition=Q(libraries__location_ptr__small=True), 836 837 ), 837 838 ) 838 839 .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 , 13 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Thanks for the detailed report! I think it has the same root cause as #33929 and should be marked as a duplicate.