Exception when negating Q object in annotate

[karyon]

any of the following

UserProfile.objects.annotate(is_member=ExpressionWrapper(~Q(groups__name="Group name"), output_field=BooleanField()))

UserProfile.objects.annotate(is_member=Case(When(~models.Q(groups__name='group name'), then=Value(False)), default=Value(True), output_field=models.BooleanField()))

UserProfile.objects.annotate(is_member=Case(When(~Q(groups__name="Group name"), then=Value(False)), default=Value(True), output_field=BooleanField()))

gives the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query.py", line 794, in annotate
    obj.query.add_annotation(annotation, alias, is_summary=False)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/sql/query.py", line 977, in add_annotation
    summarize=is_summary)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/expressions.py", line 779, in resolve_expression
    c.cases[pos] = case.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/expressions.py", line 713, in resolve_expression
    c.condition = c.condition.resolve_expression(query, allow_joins, reuse, summarize, False)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query_utils.py", line 88, in resolve_expression
    clause, _ = query._add_q(self, reuse, allow_joins=allow_joins)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/sql/query.py", line 1328, in _add_q
    current_negated=current_negated, connector=connector, allow_joins=allow_joins)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/sql/query.py", line 1177, in build_filter
    can_reuse, e.names_with_path)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/sql/query.py", line 1570, in split_exclude
    if alias in can_reuse:
TypeError: argument of type 'NoneType' is not iterable

removing the ~ "fixes" this.

see ​https://github.com/fsr-itse/EvaP for full code (a custom UserProfile is in evaluation.models), but it looks like this also happens with the default User model.

[Josh Smeaton]

UserProfile.objects.annotate(is_member=ExpressionWrapper(~Q(groups__name="Group name"), output_field=BooleanField()))

Isn't really supported. Q objects are for filters or case expressions only. They aren't designed to be used as annotations, even though they share similar APIs (for Case/When support).

UserProfile.objects.annotate(is_member=Case(When(~Q(groups__name="Group name"), then=Value(False)), default=Value(True), output_field=BooleanField()))

Probably should be supported. Negating a when clause seems like something that should be supported, but I haven't yet investigated why this is throwing errors. For the moment, as a workaround, you can stop negating the clause and switch the "then" and "default" values to then=True, default=False I think.

Accepting based on the fact that negated Q objects don't work with case expressions though.

[B Martsberger]

I am seeing this issue as well and the suggested workaround:

For the moment, as a workaround, you can stop negating the clause and switch the "then" and "default" values to then=True, default=False I think.

Doesn't work for more complex Q objects. For example, a negated Q & a non-negated Q.

queryset = SomeModel.objects.annotate(value=Case(When(Q(some__field=some_value) & ~Q(another__field=another_value))))

[Anssi Kääriäinen]

I know the reason for this, but can't work on a patch right now. The reason is that _add_q() calls build_filter() with in_negated=True, and build_filter() automatically pushes negated multijoin filters to subqueries. This is correct when constructing a Where clause, but incorrect for Case(When()).

To fix this in 1.8 we need to add a new kwarg to _add_q(), something like disable_split_exclude, and add a similar kwarg to build_filter(). When Q() objects are added to a query outside of filter() handling, we set this flag to True (this is done in Q().resolve_expression()). This should hopefully fix this issue.

For 1.9 this will be fixed by ​https://github.com/django/django/pull/4385. We can of course add the 1.8 version to master, too, if pull 4385 hasn't been committed by the time we fix this issue.

We need to also make sure Q() objects added for Case(When()) do not regenerate aliases. They should use an existing alias, and multiple Q() objects in the same annotation should use a single alias. So, for example:

.annotate(v=Case(When(friends__age=123), then=V(1)), When(friends__age=321), then(V(2)))

should generate just one join to friends. If a join for friends pre-exists, then that join should be reused.

I think I can try to work on this next week, but can't guarantee anything.

[Tim Graham]

​PR

[Tim Graham]

Patch looks okay to me, except that tests don't pass on Oracle.

[Tim Graham <timograham@…>]

In bc87061a:

Fixed #24705 -- Fixed negated Q objects in expressions.

Avoided split_exclude() for Q when used as an expression.

[Tim Graham <timograham@…>]

In db65660:

[1.8.x] Fixed #24705 -- Fixed negated Q objects in expressions.

Avoided split_exclude() for Q when used as an expression.

Backport of bc87061a3c7c8d6b4d2469f35cc78683c6cff647 from master