[Django] #28621: WHERE NOT EXISTS (... ~ ...) without extra() ?
Django
------------------------------------------+--------------------------------
Reporter: Дилян Палаузов | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.11
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+--------------------------------
{{{
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Дилян Палаузов | Owner: nobody
Component: Uncategorized | Version: 1.11
Severity: Normal | Resolution: invalid
Keywords: QuerySet.extra | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* status: new => closed
* resolution: => invalid
Comment:
I think you can use the `Exists`
[https://docs.djangoproject.com/en/1.11/ref/models/expressions/#exists-
subqueries expression available since Django 1.11]
{{{#!python
from django.db.models import Exists, F, OuterRef
A.objects.annotate(
not_matches=~Exists(
B.objects.annotate(
tag=OuterRef('tag'),
).filter(tag__regex=F('pattern')),
),
).filter(not_matches=True)
}}}
Please re-open if this doesn't work.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:1>
Django
Reporter: Дилян Палаузов | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.11
Keywords: QuerySet.extra | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Дилян Палаузов):
* status: closed => new
* resolution: invalid =>
Comment:
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'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* type: Uncategorized => Bug
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:3>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Leo Antunes):
* cc: Leo Antunes (added)
Comment:
Another slightly simpler example of the same problem, using a model that
only has a pk:
{{{#!python
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')%2).values('pk')))
}}}
Simply removing "%2" is enough to make it work (though not with the
expected results, of course):
{{{#!python
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')).values('pk')))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:4>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Matthew Schinckel):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:5>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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:
{{{#!python
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())
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:6>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Filip Kilibarda):
Replying to [comment:6 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:
> {{{#!python
> 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:7>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* owner: nobody => felixxm
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Shai Berger):
Replying to [comment:5 Matthew Schinckel]:
> 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.
For anyone else who might be wondering, this refers to #29142
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* has_patch: 0 => 1
* version: 1.11 => master
Comment:
[https://github.com/django/django/pull/11130 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by GitHub <noreply@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"2a431db0f5e91110b4fda05949de1f158a20ec5b" 2a431db]:
{{{
#!CommitTicketReference repository=""
revision="2a431db0f5e91110b4fda05949de1f158a20ec5b"
Fixed #28621 -- Fixed crash of annotations with OuterRef.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Dmitry Mugtasimov):
* cc: Dmitry Mugtasimov (added)
* status: closed => new
* resolution: fixed =>
Comment:
{{{
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'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: new => closed
* resolution: => fixed
Comment:
Please don't re-open already fixed ticket. This fix doesn't qualify for a
backport, hence it will be solved in Django 2.2.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by B Martsberger):
Out of curiosity, why does this fix not qualify for a backport?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Dmitry Mugtasimov):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Dmitry Mugtasimov):
Here is explanation for humans:
https://code.djangoproject.com/ticket/30652#comment:7
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:18>
Django
-------------------------------------+-------------------------------------
Reporter: Дилян Палаузов | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"ed6b14d4591e536985222b61cb8b83908d58140d" ed6b14d4]:
{{{
#!CommitTicketReference repository=""
revision="ed6b14d4591e536985222b61cb8b83908d58140d"
Refs #28621 -- Fixed crash of annotations with nested OuterRef.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:19>