#28621 closed Bug (fixed)
Crash in QuerySet.annotate() with OuterRef
Reported by: | Дилян Палаузов | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | QuerySet.extra |
Cc: | Leo Antunes, Dmitry Mugtasimov | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
class A(models.Model): tag = models.CharField() class B(models.Model): pattern = models.CharField()
A single query that retrieves all A.tag, not matching B.pattern:
SELECT a.tag FROM app_a as a WHERE NOT EXISTS (SELECT 1 FROM app_b as b WHERE a.tag ~ b.pattern);
Is it possibe to write a queryset for this, without .extra and .raw?
Change History (19)
comment:1 by , 7 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
comment:2 by , 7 years ago
Resolution: | invalid |
---|---|
Status: | closed → new |
I get error:
In [7]: A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True) --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-7-0a83a61e3d5b> in <module>() ----> 1 A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True) django/db/models/manager.py in manager_method(self, *args, **kwargs) 83 def create_method(name, method): 84 def manager_method(self, *args, **kwargs): ---> 85 return getattr(self.get_queryset(), name)(*args, **kwargs) 86 manager_method.__name__ = method.__name__ 87 manager_method.__doc__ = method.__doc__ django/db/models/query.py in annotate(self, *args, **kwargs) 946 947 for alias, annotation in clone.query.annotations.items(): --> 948 if alias in annotations and annotation.contains_aggregate: 949 if clone._fields is None: 950 clone.query.group_by = True AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'
comment:3 by , 7 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Summary: | WHERE NOT EXISTS (... ~ ...) without extra() ? → Crash in QuerySet.annotate() with OuterRef |
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → Bug |
comment:4 by , 7 years ago
Cc: | added |
---|
Another slightly simpler example of the same problem, using a model that only has a pk:
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')%2).values('pk')))
This generates the related 'ResolvedOuterRef' object has no attribute 'relabeled_clone'
exception. I believe these two issues are related, since they both point at OuterRef()
behaving differently than what one would expect with, e.g., F()
.
Simply removing "%2" is enough to make it work (though not with the expected results, of course):
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')).values('pk')))
follow-up: 9 comment:5 by , 7 years ago
The relabeled_clone
issue is now fixed: I missed that there is also a contains_aggregate
part. I'll have a look at that now.
follow-up: 7 comment:6 by , 6 years ago
I am also getting this error. Is there a workaround?
AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'
My query looks like this:
class ProgramQuerySet(models.QuerySet): def with_remaining_volunteers_needed(self): sessions_query = ( Session.objects .filter(program=OuterRef('id')) .annotate(needed=OuterRef('volunteers_per_session') - Count('volunteer_attendances')) .order_by('-needed') .values('needed')[:1] ) return self.annotate( volunteers_per_session=(F('num_student_seats') * F('student_volunteer_ratio')), needed=Subquery(sessions_query, output_field=models.IntegerField()) )
comment:7 by , 6 years ago
Replying to Ben Davis:
I am also getting this error. Is there a workaround?
AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'
My query looks like this:
class ProgramQuerySet(models.QuerySet): def with_remaining_volunteers_needed(self): sessions_query = ( Session.objects .filter(program=OuterRef('id')) .annotate(needed=OuterRef('volunteers_per_session') - Count('volunteer_attendances')) .order_by('-needed') .values('needed')[:1] ) return self.annotate( volunteers_per_session=(F('num_student_seats') * F('student_volunteer_ratio')), needed=Subquery(sessions_query, output_field=models.IntegerField()) )
I'm having exactly the same issue. Have you found a workaround for this?
See this https://stackoverflow.com/q/47094982/6824752 for a weird workaround
comment:8 by , 6 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:9 by , 6 years ago
Replying to Matthew Schinckel:
The
relabeled_clone
issue is now fixed: I missed that there is also acontains_aggregate
part. I'll have a look at that now.
For anyone else who might be wondering, this refers to #29142
comment:11 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:13 by , 5 years ago
Cc: | added |
---|---|
Resolution: | fixed |
Status: | closed → new |
queryset.annotate( has_role=Exists( JobTitle.objects.annotate( title_vector=SearchVector( OuterRef('title'), config=SIMPLE_PG_SEARCH_CONFIGURATION) ).filter( title_vector=SearchQuery( F('name'), config=SIMPLE_PG_SEARCH_CONFIGURATION) ) ) )
results in
'ResolvedOuterRef' object has no attribute 'contains_aggregate' Request Method: GET Request URL: http://127.0.0.1:8000/admin/jobs/job/ Django Version: 2.2.2 Exception Type: AttributeError Exception Value: 'ResolvedOuterRef' object has no attribute 'contains_aggregate' Exception Location: /usr/local/lib/python3.7/site-packages/django/db/models/expressions.py in <genexpr>, line 213 Python Executable: /usr/local/bin/python Python Version: 3.7.3 Python Path: ['/code', '/code', '/usr/local/lib/python37.zip', '/usr/local/lib/python3.7', '/usr/local/lib/python3.7/lib-dynload', '/usr/local/lib/python3.7/site-packages'] Server time: Mon, 22 Jul 2019 09:22:47 +0000
Environment: Request Method: GET Request URL: http://127.0.0.1:8000/admin/jobs/job/ Django Version: 2.2.2 Python Version: 3.7.3 Installed Applications: ['core', 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django.contrib.sites', 'django.contrib.postgres', 'allauth', 'allauth.account', 'drf_yasg', 'rest_auth', 'rest_auth.registration', 'rest_framework', 'rest_framework.authtoken', 'colorful', 'gm2m', 'explorer', 'allauth.socialaccount', 'accounts', 'questions', 'colleges', 'geo', 'jobs', 'advices', 'referral'] Installed Middleware: ['django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware'] Traceback: File "/usr/local/lib/python3.7/site-packages/django/core/handlers/exception.py" in inner 34. response = get_response(request) File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response 115. response = self.process_exception_by_middleware(e, request) File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response 113. response = wrapped_callback(request, *callback_args, **callback_kwargs) File "/usr/local/lib/python3.7/contextlib.py" in inner 74. return func(*args, **kwds) File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in wrapper 606. return self.admin_site.admin_view(view)(*args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapped_view 142. response = view_func(request, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func 44. response = view_func(request, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/sites.py" in inner 223. return view(request, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapper 45. return bound_method(*args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapped_view 142. response = view_func(request, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in changelist_view 1672. cl = self.get_changelist_instance(request) File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in get_changelist_instance 744. sortable_by, File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/views/main.py" in __init__ 45. self.root_queryset = model_admin.get_queryset(request) File "/code/jobs/admin.py" in get_queryset 76. return queryset.annotate_with_has_role() File "/code/jobs/models/job.py" in annotate_with_has_role 68. config=SIMPLE_PG_SEARCH_CONFIGURATION) File "/usr/local/lib/python3.7/site-packages/django/db/models/manager.py" in manager_method 82. return getattr(self.get_queryset(), name)(*args, **kwargs) File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py" in annotate 1059. if alias in annotations and annotation.contains_aggregate: File "/usr/local/lib/python3.7/site-packages/django/utils/functional.py" in __get__ 80. res = instance.__dict__[self.name] = self.func(instance) File "/usr/local/lib/python3.7/site-packages/django/db/models/expressions.py" in contains_aggregate 213. return any(expr and expr.contains_aggregate for expr in self.get_source_expressions()) File "/usr/local/lib/python3.7/site-packages/django/db/models/expressions.py" in <genexpr> 213. return any(expr and expr.contains_aggregate for expr in self.get_source_expressions()) Exception Type: AttributeError at /admin/jobs/job/ Exception Value: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'
comment:14 by , 5 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Please don't re-open already fixed ticket. This fix doesn't qualify for a backport, hence it will not be solved in Django 2.2.
comment:16 by , 5 years ago
This doesn't qualify for a backport because it is not a regression in the Django 2.2 or a bug in a new feature.
comment:17 by , 5 years ago
The fix does not qualify for backport because the code where it appears was heavily refactored somewhere between 1.11 and 2.2. However the bug remained in 2.2 and the bug qualifies to get fixed. The fix is already implemented, but not released yet. This is how I understood it.
comment:18 by , 5 years ago
Here is explanation for humans: https://code.djangoproject.com/ticket/30652#comment:7
I think you can use the
Exists
expression available since Django 1.11Please re-open if this doesn't work.