[Django] #34229: "no such column" when combining FilteredRelation and multi-table inheritance models
Django
inheritance models
-------------------------------------+-------------------------------------
Reporter: lufte | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 4.1
layer (models, ORM) | Keywords: filteredrelation
Severity: Normal | such column
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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()
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34229>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
inheritance models
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: filteredrelation | Triage Stage:
such column | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* type: Uncategorized => Bug
* resolution: => duplicate
Comment:
Thanks for the detailed report! I think it has the same root cause as
#33929 and should be marked as a duplicate.
--
Ticket URL: <https://code.djangoproject.com/ticket/34229#comment:1>
Django
inheritance models
-------------------------------------+-------------------------------------
Reporter: Javier Ayres | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
such column |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* status: closed => new
* resolution: duplicate =>
* stage: Unreviewed => Accepted
Comment:
This case is more complicated so we decided to fix it separately, see a
regression test:
{{{#!diff
diff --git a/tests/filtered_relation/models.py
b/tests/filtered_relation/models.py
index d34a86305f..dcd0197447 100644
--- a/tests/filtered_relation/models.py
+++ b/tests/filtered_relation/models.py
@@ -44,6 +44,14 @@ class Borrower(models.Model):
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"
diff --git a/tests/filtered_relation/tests.py
b/tests/filtered_relation/tests.py
index ce75cb01f5..9d38593065 100644
--- a/tests/filtered_relation/tests.py
+++ b/tests/filtered_relation/tests.py
@@ -24,6 +24,7 @@ from .models import (
Currency,
Editor,
ExchangeRate,
+ Library,
RentalSession,
Reservation,
Seller,
@@ -825,6 +826,22 @@ class FilteredRelationAggregationTests(TestCase):
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34229#comment:2>
Django
inheritance models
-------------------------------------+-------------------------------------
Reporter: Javier Ayres | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: filteredrelation | Triage Stage: Accepted
such column |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
After further investigation this issue is fundamentally the same as the
one that we currently error out about when trying to do
{{{#!python
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
[https://github.com/django/django/blob/c813fb327cb1b09542be89c5ceed367826236bc2/tests/filtered_relation/tests.py#L632-L644
test] and
[https://github.com/django/django/blob/c813fb327cb1b09542be89c5ceed367826236bc2/django/db/models/sql/query.py#L1597-L1601
origin])
The rationale behind this classification is that `student__superuser` is
actually an alias for `student__user_ptr__superuser` so the reported case
here
{{{#!python
School.objects.annotate(
superusers=FilteredRelation(
'student',
condition=Q(
student__superuser=True,
),
)
)
}}}
Is an alias for
{{{#!python
School.objects.annotate(
superusers=FilteredRelation(
'student',
condition=Q(
student__user__ptr__superuser=True,
),
)
)
}}}
The latter is
[https://github.com/django/django/blob/c813fb327cb1b09542be89c5ceed367826236bc2/django/db/models/sql/query.py#L1597-L1601
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
{{{#!diff
diff --git a/tests/filtered_relation/tests.py
b/tests/filtered_relation/tests.py
index 9d38593065..b269ed786d 100644
--- a/tests/filtered_relation/tests.py
+++ b/tests/filtered_relation/tests.py
@@ -832,7 +832,8 @@ def test_condition_spans_mti(self):
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
{{{#!python
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'
),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34229#comment:3>
Django
inheritance models
-------------------------------------+-------------------------------------
| Kumar Sen
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: filteredrelation | Triage Stage: Accepted
such column |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Akash Kumar Sen
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34229#comment:4>
Django
inheritance models
-------------------------------------+-------------------------------------
| Kuzibaev
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: filteredrelation | Triage Stage: Accepted
such column |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Turonbek Kuzibaev