Opened 5 years ago

Last modified 13 days ago

#21703 new Bug

`exclude` query with `F` object across relationship fails

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Given the following models..

Code highlighting:

  class Owner(models.Model):
    pass
 
 
class Thing(models.Model):
    owner = models.ForeignKey(Owner, related_name='things')
 
    date = models.DateField()
 
 
class Error(models.Model):
    owner = models.ForeignKey(Owner, related_name='errors')
 
    date = models.DateField()
 

The following exclude query fails.

Code highlighting:

  >>> Error.objects.exclude(owner__things__date=F('date'))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/query.py", line 115, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/query.py", line 140, in __iter__
    self._fetch_all()
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/query.py", line 962, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/query.py", line 264, in iterator
    for row in compiler.results_iter():
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 681, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 762, in execute_sql
    cursor.execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/backends/utils.py", line 77, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/backends/utils.py", line 61, in execute
    return self.cursor.execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/utils.py", line 93, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/backends/utils.py", line 61, in execute
    return self.cursor.execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 489, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: no such column: U1.id

The same query using a filter works as expected. This bug is present in 1.5.5, 1.6.1 and 1.7.dev20131229181725

Change History (9)

comment:1 Changed 5 years ago by Piper Merriam

This bug is also confirmed present on the mysql backend.

comment:2 Changed 5 years ago by Piper Merriam

Component: UncategorizedDatabase layer (models, ORM)
Type: UncategorizedBug

comment:3 Changed 5 years ago by Jonas Kölker

This seems to be related to, if not the same as, Bug #18726 (see especially ticket:18726#comment:8).

comment:4 Changed 5 years ago by Baptiste Mispelon

Triage Stage: UnreviewedAccepted

I can reproduce this too.

I'm not sure if it's the same bug as #18726 (the error messages are different) but it might be related.

#21787 is another ticket where filter() works but exclude() breaks (this one is actually a regression which I traced back to b4492a8ca4a7ae4daa3a6b03c3d7a845fad74931).

comment:5 Changed 5 years ago by Anssi Kääriäinen

This one is complex to solve. In the query F('date') must refer the outer query's date field, not anything in the inner query. The way split_exclude() is implemented is to add .filter(ownre__things__date=F('date')) in the inner query, then trimming prefixes. We could add the F() into the outer query and pass that value to the inner query. Unfortunately that doesn't work if the F() object references something in the outer instead of inner query (for example F('owner__things__somecol')). In that case we must add the F() to the outer query. This gets even more complicated if you use F('date') + F('owner__things__somecol'). Neither inner nor outer query addition works for the whole expression, the first F() needs to refer to outer, the second to inner query.

So, we need bigger changes into the ORM. I think we need to teach setup_joins() to auto-refer to the right query based on prefix, and rework how split_exclude() works. I don't have good ideas of how to actually achieve all this. It seems implementing this is going to need a lot of work.

There are other complex cases to be solved for .split_exclude() - for example .exclude(owner__things__date__gte=date1, owner__things__date__lte=date2) doesn't work currently, and I suspect there are similar problems if you use complex boolean trees where some parts refer to outer query, some parts to inner. The most complex case is to have references to two different m2m joins, something like: .exclude(owner__things__date__gte=date1, creator__things__date__lte=date2). I am not even sure how to write that in SQL using subqueries...

It seems #18726 is at least close to this, if not duplicate. I haven't done enough research to be certain.

Last edited 3 years ago by Tim Graham (previous) (diff)

comment:6 Changed 19 months ago by Tim Graham

I closed #28551 as a duplicate (same problem query across a many-to-many relation).

comment:7 Changed 3 months ago by Simon Charette

The way split_exclude() is implemented is to add filter(owner__things__date=F('date')) in the inner query, ...

I wonder if this could be fixed by making split_exclude() add filter(owner__things__date=OuterRef('date')) in the inner query instead.

comment:8 Changed 3 months ago by Simon Charette

I confirmed this is fixed by relying on OuterRef

  • django/db/models/sql/query.py

    diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
    index 7d991b6b84..458ecaa02a 100644
    a b from django.core.exceptions import ( 
    1919from django.db import DEFAULT_DB_ALIAS, NotSupportedError, connections
    2020from django.db.models.aggregates import Count
    2121from django.db.models.constants import LOOKUP_SEP
    22 from django.db.models.expressions import Col, F, Ref, SimpleCol
     22from django.db.models.expressions import (
     23    Col, F, OuterRef, Ref, SimpleCol, Subquery,
     24)
    2325from django.db.models.fields import Field
    2426from django.db.models.fields.related_lookups import MultiColSource
    2527from django.db.models.lookups import Lookup
    class Query: 
    16301632        saner null handling, and is easier for the backend's optimizer to
    16311633        handle.
    16321634        """
     1635        if isinstance(filter_expr[1], F):
     1636            filter_expr = (filter_expr[0], OuterRef(filter_expr[1].name))
    16331637        # Generate the inner query.
    16341638        query = Query(self.model)
    16351639        query.add_filter(filter_expr)
    class Query: 
    16621666            query.where.add(lookup, AND)
    16631667            query.external_aliases.add(alias)
    16641668
     1669        from django.db import models
     1670        queryset = models.QuerySet(self.model, query)
    16651671        condition, needed_inner = self.build_filter(
    1666             ('%s__in' % trimmed_prefix, query),
     1672            ('%s__in' % trimmed_prefix, Subquery(queryset)),
    16671673            current_negated=True, branch_negated=True, can_reuse=can_reuse)
    16681674        if contains_louter:
    16691675            or_null_condition, _ = self.build_filter(

But this makes Query depend on QuerySet which we want to avoid. I have always thought Subquery wrapping of QuerySet instances was unnecessary and that OuterRef ought to be resolved by Query anyway so I'll give it a try.

Last edited 3 months ago by Tim Graham (previous) (diff)

comment:9 Changed 13 days ago by Simon Charette

Needs tests: set
Note: See TracTickets for help on using tickets.
Back to Top