#22429: Wrong SQL generated when using ~Q and F
----------------------------------------------+--------------------
Reporter: sipp11 | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I found this problem on 1.6.2 and also on 1.8.0alpha (master as of today
commit b82f30785ff0f9fedf38fc79624a9064a903de4a)
How to reproduce:
create school app. Then add models and run a query.
models.py
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
class School(models.Model):
school_id = models.CharField('School ID', max_length=10,
unique=True)
class Student(models.Model):
school = models.ForeignKey(School, related_name='students')
prefix = models.CharField('Prefix', max_length=3, blank=True,
default='')
student_id = models.CharField('Student ID', max_length=15)
class Classroom(models.Model):
school = models.ForeignKey(School, related_name='classrooms')
students = models.ManyToManyField(Student, related_name='classroom',
blank=True)
}}}
}}}
Then go to django shell
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
>>> Student.objects.filter(~Q(classroom__school=F('school')))
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/query.py",
line 116, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/query.py",
line 141, in __iter__
self._fetch_all()
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/query.py",
line 961, in _fetch_all
self._result_cache = list(self.iterator())
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/query.py",
line 265, in iterator
for row in compiler.results_iter():
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/sql/compiler.py",
line 698, in results_iter
for rows in self.execute_sql(MULTI):
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/models/sql/compiler.py",
line 784, in execute_sql
cursor.execute(sql, params)
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/backends/utils.py",
line 74, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/backends/utils.py",
line 59, in execute
return self.cursor.execute(sql, params)
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/utils.py", line
94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/backends/utils.py",
line 59, in execute
return self.cursor.execute(sql, params)
File "/Users/sipp11/.virtualenvs/psis/lib/python2.7/site-
packages/Django-1.8.dev20140413012625-py2.7.egg/django/db/backends/sqlite3/base.py",
line 480, in execute
return Database.Cursor.execute(self, query, params)
OperationalError: no such column: U1.student_id
>>>
}}}
}}}
Apparently SQL was missing U1 declaration.
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
>>> print Student.objects.filter(~Q(classroom__school=F('school'))).query
SELECT "school_student"."id", "school_student"."school_id",
"school_student"."prefix", "school_student"."student_id"
FROM "school_student"
WHERE NOT ("school_student"."id" IN (
SELECT U1."student_id" AS "student_id"
FROM "school_student" U0
INNER JOIN "school_classroom_students" U2 ON ( U0."id" =
U2."student_id" )
INNER JOIN "school_classroom" U3 ON ( U2."classroom_id" = U3."id" )
WHERE U3."school_id" = (U0."school_id"))
)
}}}
}}}
Above is information I got from testing with master (1.8.0alpha)
This is another trackback from 1.6.2, https://dpaste.de/Yzja, which is in
pretty much the same.
--
Ticket URL: <https://code.djangoproject.com/ticket/22429>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.7bc6a8bc110a66dbd08013bb2a3d668c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
