id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
21787	Wrong SQL generated when using exclude() and model inheritance	John Milner	nobody	"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:
{{{#!python
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:
{{{#!sql
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)."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	inheritance exclude sql orm	david@…	Accepted	0	0	0	0	0	0