Opened 20 months ago

Last modified 20 months 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

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

comment:1 Changed 20 months ago by pipermerriam

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

This bug is also confirmed present on the mysql backend.

comment:2 Changed 20 months ago by pipermerriam

  • Component changed from Uncategorized to Database layer (models, ORM)
  • Type changed from Uncategorized to Bug

comment:3 Changed 20 months ago by jonaskoelker

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

comment:4 Changed 20 months ago by bmispelon

  • Triage Stage changed from Unreviewed to Accepted

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 20 months ago by akaariai

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 #18762 is at least close to this, if not duplicate. I haven't done enough research to be certain.

Last edited 20 months ago by akaariai (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top