Filter using calculated value does not work in Django

Question

I have a model in Django with fields largura and comprimento, and would like to filter it by area:

class Projeto(models.Model):
    largura = models.DecimalField(decimal_places=0, max_digits=4, default=0)
    comprimento = models.DecimalField(decimal_places=0, max_digits=4, default=0)

I tried the following query:

Projeto.objects.annotate(area=F('largura')*F('comprimento')).filter(area__lte=1000)

However, it didn’t work: query returned all the lines of Projeto, including those that have larger area than 1000. What am I doing wrong?

I checked the SQL being generated by the query (formatted for readability):

>>> print Projeto.objects.annotate(area=F('largura')*F('comprimento')).filter(area__lte=1000).query
SELECT "mcve_projeto"."id", "mcve_projeto"."largura", "mcve_projeto"."comprimento",
       ("mcve_projeto"."largura" * "mcve_projeto"."comprimento") AS "area"
FROM "mcve_projeto"
WHERE ("mcve_projeto"."largura" * "mcve_projeto"."comprimento") <= 1000

as well as the database creation SQL (idem):

python manage.py sqlmigrate mcve 0001
BEGIN;
CREATE TABLE "mcve_projeto" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "largura" decimal NOT NULL,
    "comprimento" decimal NOT NULL);

COMMIT;

and created an example in Sqlfiddle (note: I am using Sqlite). It worked normal. Where can be the problem?

Answer 1

Congratulations, you found a bug in the Django pro Sqlite driver!

I played your scenario and had the same behavior:

./manage.py shell
Python 3.4.1 (default, Sep 24 2015, 20:41:10) 
[GCC 4.9.2 20150212 (Red Hat 4.9.2-6)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from area.models import Projeto
>>> for i in Projeto.objects.all(): print(i.largura, i.comprimento, i.largura*i.comprimento)
... 
10 10 100
5 5 25
50 50 2500
101 100 10100
250 250 62500
>>> from django.db.models import F
>>> for i in Projeto.objects.annotate(area=F('largura')*F('comprimento')).filter(area__lte=1000): print(i.largura, i.comprimento)
... 
10 10
5 5
50 50
101 100
250 250
>>> 

Initially I thought the problem was the Sqlite itself, but this test showed that it is OK:

$ sqlite3 db.sqlite3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> 
sqlite> 
sqlite> select * from area_projeto;
1|10|10
2|5|5
3|50|50
4|101|100
5|250|250
sqlite> SELECT area_projeto.id, area_projeto.largura, area_projeto.comprimento, (area_projeto.largura * area_projeto.comprimento) AS area FROM area_projeto WHERE (area_projeto.largura * area_projeto.comprimento) <= 1000;
1|10|10|100
2|5|5|25
sqlite>

So to be sure, I took your problem to Postgresql 9, and it worked perfectly:

$ ./manage.py shell
Python 3.4.1 (default, Sep 24 2015, 20:41:10) 
[GCC 4.9.2 20150212 (Red Hat 4.9.2-6)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from area.models import Projeto
>>> for i in Projeto.objects.all(): print(i.largura, i.comprimento, i.largura*i.comprimento)
... 
10 10 100
5 5 25
50 50 2500
100 100 10000
250 250 62500
>>> from django.db.models import F
>>> for i in Projeto.objects.annotate(area=F('largura')*F('comprimento')).filter(area__lte=1000): print(i.largura, i.comprimento)
... 
10 10
5 5
>>> 

Ah, don’t forget to open a bug report for the guys!