[Django] #21787: Problem with exclude() SQL when inheriting from User

Django

unread,

Jan 15, 2014, 10:14:17 PM1/15/14

to django-...@googlegroups.com

#21787: Problem with exclude() SQL when inheriting from User
-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6
(models, ORM) | Keywords: auth_user user
Severity: Normal | inheritance exclude sql orm user_id
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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).

--
Ticket URL: <https://code.djangoproject.com/ticket/21787>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 16, 2014, 8:20:28 AM1/16/14

to django-...@googlegroups.com

#21787: Problem with exclude() SQL when inheriting from User
-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/21787#comment:1>

Django

unread,

Jan 16, 2014, 11:47:21 AM1/16/14

to django-...@googlegroups.com

#21787: Problem with exclude() SQL when inheriting from User
-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

Comment (by jmilner):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/21787#comment:2>

Django

unread,

Jan 19, 2014, 10:27:02 PM1/19/14

to django-...@googlegroups.com

#21787: Wrong SQL generated when using exclude() and model inheritance

-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by bmispelon):

* keywords: auth_user user inheritance exclude sql orm user_id =>
inheritance exclude sql orm
* version: 1.6 => master
* stage: Unreviewed => Accepted

Comment:

I can reproduce the issue using the following models:
{{{#!python
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/21787#comment:3>

Django

unread,

Jan 20, 2014, 11:35:26 AM1/20/14

to django-...@googlegroups.com

#21787: Wrong SQL generated when using exclude() and model inheritance

-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody
Type: Bug | Status: new

Comment (by akaariai):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/21787#comment:4>

Django

unread,

Jan 24, 2014, 3:16:48 AM1/24/14

to django-...@googlegroups.com

#21787: Wrong SQL generated when using exclude() and model inheritance

-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: master

(models, ORM) | Resolution: fixed

Changes (by Anssi Kääriäinen <akaariai@…>):

* status: new => closed
* resolution: => fixed

Comment:

In [changeset:"78a2617703bc1dada409f126db5c3db214913ff4"]:
{{{
#!CommitTicketReference repository=""
revision="78a2617703bc1dada409f126db5c3db214913ff4"
Fixed #21787 -- regression in MTI .exclude() queries
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/21787#comment:5>

Django

unread,

Jan 24, 2014, 3:19:18 AM1/24/14

to django-...@googlegroups.com

#21787: Wrong SQL generated when using exclude() and model inheritance

-------------------------------------+-------------------------------------
Reporter: jmilner | Owner: nobody

Comment (by Anssi Kääriäinen <akaariai@…>):

In [changeset:"e47b90e48f3532d312afcfaec55b2711b2129c02"]:
{{{
#!CommitTicketReference repository=""
revision="e47b90e48f3532d312afcfaec55b2711b2129c02"
[1.6.x] Fixed #21787 -- regression in MTI .exclude() queries