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 ""<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.

"	Bug	closed	Database layer (models, ORM)	dev	Release blocker	fixed		loic@…	Accepted	1	0	0	0	0	0