Filter using calculated value does not work in Django

Question

Filter using calculated value does not work in Django

Navigation

#1 by (3 votes)

5

I have a template in Django with fields largura and comprimento , and I 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 did not work: the query returned all lines of Projeto , even those that have an area greater than 1000 . What am I doing wrong?

I checked the SQL that was 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 I created an example in SQLFiddle (note: I'm using SQLite). It worked normal. Where might the problem be?

sql sqlite django

asked by anonymous 07.04.2016 / 15:42

1 answer

Max of a numeric field returning NA Sort sequence of numbers in database with PHP

score 3 · Accepted Answer

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

I reproduce 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 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
>>>

Oh, do not forget to open a Bug Report for guys!