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 22429 Wrong SQL generated when using ~Q and F sipp11 superemily "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 """", line 1, in 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. " Bug closed Database layer (models, ORM) dev Release blocker fixed loic@… Accepted 1 0 0 0 0 0