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
23797	SQL generated for negated F() expressions is incorrect	Suriya Subramanian	Jacob Walls	"Consider the following model definition.

{{{
#!python
from django.db import models

class Rectangle(models.Model):
    length = models.IntegerField(null=True)
    width = models.IntegerField(null=True)
}}}

We make the following queries: Q1) Rectangles that are squares. Q2a) Rectangles that are not squares (length != width). Q2b) Rectangles that are not squares (width != length). Queries Q2a and Q2b semantically mean the same. However, the ORM generates different SQL for these two queries.

{{{
#!python
import django
django.setup()

from django.db.models import F
from testapp.models import Rectangle

print '--- Q1: Get the rectangles that are squares'
print Rectangle.objects.filter(length=F('width')).values('pk').query

print '--- Q2a: Get the rectangles that are not squares'
print Rectangle.objects.exclude(length=F('width')).values('pk').query

print '--- Q2b: Get the rectangles that are not squares'
print Rectangle.objects.exclude(width=F('length')).values('pk').query
}}}

The generated SQL is

{{{
#!sql
--- Q1: Get the rectangles that are squares
SELECT ""testapp_rectangle"".""id"" FROM ""testapp_rectangle"" WHERE ""testapp_rectangle"".""length"" = (""testapp_rectangle"".""width"")
--- Q2a: Get the rectangles that are not squares
SELECT ""testapp_rectangle"".""id"" FROM ""testapp_rectangle"" WHERE NOT (""testapp_rectangle"".""length"" = (""testapp_rectangle"".""width"") AND ""testapp_rectangle"".""length"" IS NOT NULL)
--- Q2b: Get the rectangles that are not squares
SELECT ""testapp_rectangle"".""id"" FROM ""testapp_rectangle"" WHERE NOT (""testapp_rectangle"".""width"" = (""testapp_rectangle"".""length"") AND ""testapp_rectangle"".""width"" IS NOT NULL)
}}}

Note the asymmetry between Q2a and Q2b. These queries will return different results.

Reddit user /u/charettes set up this useful SQL fiddle with the above mentioned schema and test values: http://sqlfiddle.com/#!12/c8283/4 Here's my reddit post on this issue: http://www.reddit.com/r/django/comments/2lxjcc/unintuitive_behavior_of_f_expression_with_exclude/"	Bug	closed	Database layer (models, ORM)	1.7	Normal	fixed		Simon Charette	Accepted	1	0	0	0	0	0