#23797: SQL generated for negated F() expressions is incorrect
----------------------------------------------+--------------------
Reporter: mssnlayam | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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/
--
Ticket URL: <https://code.djangoproject.com/ticket/23797>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.47aa2ec344627ef2b6b0701bae348a04%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
