Opened 4 years ago

Last modified 7 weeks 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: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Given the following models..

Code highlighting:

  class Owner(models.Model):
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/", 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/", line 140, in __iter__
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/models/", 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/", 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/", 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/", line 762, in execute_sql
    cursor.execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/backends/", 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/", line 61, in execute
    return self.cursor.execute(sql, params)
  File "/Users/foobar/python-environments/358426a27f281ff5/lib/python2.7/site-packages/django/db/", 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/", 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/", line 489, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: no such column:

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 (6)

comment:1 Changed 4 years ago by Piper Merriam

This bug is also confirmed present on the mysql backend.

comment:2 Changed 4 years ago by Piper Merriam

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

comment:3 Changed 4 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 4 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 4 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 17 months ago by Tim Graham (previous) (diff)

comment:6 Changed 7 weeks ago by Tim Graham

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

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