Wrong SQL generated when using exclude() and model inheritance

[John Milner]

I have a Student model that inherits from User, and an Assignment model that has a ForeignKey to User. I want to find all Students who do not have assignments with the name Oops, but the generated SQL references both "user_id" and "id" from the table "auth_user"—and only "id" exists.

models.py:

from django.db import models
from django.contrib.auth.models import User

class Student(User):
    gpa = models.DecimalField(max_digits=5, decimal_places=2)

class Assignment(models.Model):
    user = models.ForeignKey('auth.User')
    name = models.CharField(max_length=123)

Demonstration:

Python 2.7.4 (default, Apr 19 2013, 18:28:01) 
[GCC 4.7.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> import django
>>> django.VERSION
(1, 6, 1, 'final', 0)
>>> 
>>> from myapp.models import *
>>> student = Student.objects.create(gpa=12.34)
>>> assignment = Assignment()
>>> assignment.name = "Hello"
>>> assignment.user = student
>>> assignment.save()
>>> 
>>> q = Student.objects.exclude(assignment__name="Oops")
>>> q[0].gpa
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 132, in __getitem__
    return list(qs)[0]
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 96, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 854, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 220, in iterator
    for row in compiler.results_iter():
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 710, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 781, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 69, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/sqlite3/base.py", line 450, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: no such column: U1.user_id
>>> print q.query
SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", "myapp_student"."user_ptr_id", "myapp_student"."gpa" FROM "myapp_student" INNER JOIN "auth_user" ON ( "myapp_student"."user_ptr_id" = "auth_user"."id" ) WHERE NOT ("myapp_student"."user_ptr_id" IN (SELECT U1."user_id" FROM "auth_user" U1 INNER JOIN "myapp_assignment" U2 ON ( U1."id" = U2."user_id" ) WHERE U2."name" = Oops ))
>>> 

The query, edited for clarity:

SELECT "auth_user"."id",
  <snip>
FROM "myapp_student"
INNER JOIN "auth_user" ON ("myapp_student"."user_ptr_id" = "auth_user"."id")
WHERE NOT ("myapp_student"."user_ptr_id" IN
             (SELECT U1."user_id"
              FROM "auth_user" U1
              INNER JOIN "myapp_assignment" U2 ON (U1."id" = U2."user_id")
              WHERE U2."name" = Oops))

I see the same SQL produced by the latest development commit (34490792f167f7703cf4396b4bb26163a8af6382).

[Tim Graham]

Your models look problematic. Shouldn't the ForeignKey reference Student and not auth.User?

[John Milner]

Thanks for the quick response. You're right, I could change the ForeignKey or use User.objects.exclude(assignment__name="Oops").

However, the general question remains—if I have:

• Model A
• Model X with a ForeignKey to Model A
• Model B that subclasses Model A, using multi-table inheritance

Shouldn't I be able to say B.objects.exclude(x__some_attribute='some value')? If, for example, I use filter() instead of exclude() in this way, I don't get an error.

[Baptiste Mispelon]

I can reproduce the issue using the following models:

class A(models.Model):
    pass


class B(A):
    pass


class X(models.Model):
    a = models.ForeignKey(A)
    foo = models.CharField(max_length=10)

As you note, doing B.objects.filter(x__foo='foo') works whereas B.objects.exclude(x__foo='foo') throws an error:

Traceback (most recent call last):
  File "t.py", line 7, in <module>
    list(B.objects.exclude(x__foo='foo'))
  File "./django/db/models/query.py", line 140, in __iter__
    self._fetch_all()
  File "./django/db/models/query.py", line 962, in _fetch_all
    self._result_cache = list(self.iterator())
  File "./django/db/models/query.py", line 264, in iterator
    for row in compiler.results_iter():
  File "./django/db/models/sql/compiler.py", line 693, in results_iter
    for rows in self.execute_sql(MULTI):
  File "./django/db/models/sql/compiler.py", line 776, in execute_sql
    cursor.execute(sql, params)
  File "./django/db/backends/utils.py", line 77, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "./django/db/backends/utils.py", line 61, in execute
    return self.cursor.execute(sql, params)
  File "./django/db/utils.py", line 93, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "./django/utils/six.py", line 495, in reraise
    raise value.with_traceback(tb)
  File "./django/db/backends/utils.py", line 61, in execute
    return self.cursor.execute(sql, params)
  File "./django/db/backends/sqlite3/base.py", line 494, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: U1.a_id

This appears to be a regression (it works on Django 1.5 but not on 1.6) and bisecting the problem points to this commit: b4492a8ca4a7ae4daa3a6b03c3d7a845fad74931.

[Anssi Kääriäinen]

Proposed fix at ​https://github.com/akaariai/django/compare/ticket_21787. All tests pass and the failure mentioned in this ticket is now fixed.

The names_to_path() method communicates joins generated for each name in path to split_exclude() (and split_exclude() communicates them further into trim_prefix()). In MTI situations names_to_path() didn't add parent table joins to the generated joins.

Looking at the coding there is clear need of cleanup. Both the way joins per name are communicated to trim_prefix() and how trim_prefix() is coded are confusing. It is very hard to see if the coding in trim_prefix() is correct. I remember struggling a lot with trim_prefix() when working with split_exclude() changes. Seems like I need to continue that work later on.

[Anssi Kääriäinen <akaariai@…>]

In 78a2617703bc1dada409f126db5c3db214913ff4:

Fixed #21787 -- regression in MTI .exclude() queries

[Anssi Kääriäinen <akaariai@…>]

In e47b90e48f3532d312afcfaec55b2711b2129c02:

[1.6.x] Fixed #21787 -- regression in MTI .exclude() queries

Backpatch of 78a2617703bc1dada409f126db5c3db214913ff4 from master.