Opened 12 years ago

Closed 12 years ago

Last modified 12 years ago

#20216 closed Bug (invalid)

filtering over an Sum on a BooleanField uses BooleanField's get_db_prep_lookup on the value

Reported by: Pavel Anossov Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

With a model like this:

class Test(models.Model):
    active = models.BooleanField()
    group = models.IntegerField()

this queryset:

Test.objects.values('group').annotate(s=Sum('active')).filter(s__gt=123456)

As well as this one, and one with any other value

Test.objects.values('group').annotate(s=Sum('active')).filter(s__gt='wharrgabl')

Generates this SQL and params

SELECT "test_test"."group", SUM("test_test"."active") AS "s" FROM "test_test" GROUP BY "test_test"."group" HAVING SUM("test_test"."active") > ? LIMIT 21

(True,)

That is, any value is converted to bool by BooleanField's get_db_prep_lookup called from here.

Change History (3)

comment:1 by Aymeric Augustin, 12 years ago

Resolution: invalid
Status: newclosed

In general, aggregation only works when the result of the aggregate has the same type as the original value.

Arithmetic on booleans doesn't make sense, even though Python attemps to perform it:

>>> bool(True - True)
False
>>> bool(True / False)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ZeroDivisionError: integer division or modulo by zero

Specifically, your code won't work on databases where true and false are stored as 't' and 'f'.

comment:2 by Pavel Anossov, 12 years ago

This is just somewhat inconsistent — the docs say only numeric fields are allowed in Avg, but it is not enforced, and Avg works on BooleanFields because the result is always a FloatField. A mention in the Sum docs or an exception instead of undefined behaviour would be nice.

comment:3 by Aymeric Augustin, 12 years ago

It's an accident of duck-typing that Avg works on boolean fields, and as said in my first comment, it certainly doesn't work on all databases.

Note: See TracTickets for help on using tickets.
Back to Top