Filtering on aggregate annotations with a Decimal value doesn't work

[elmopl@…]

When I run

$ python manage.py version
1.4
$ python manage.py shell
[...]
>>> for r in StoreBill.objects.annotate( amount = Sum('parts__amount') ).filter( date = '2012-03-28' ): r.amount
... 
Decimal('1.05')
>>> for r in StoreBill.objects.annotate( amount = Sum('parts__amount') ).filter( date = '2012-03-28', amount = Decimal("1.05") ): a = r.amount
... 
>>> print connection.queries[-1]
{'time': '0.002', 'sql': u'SELECT "spendings_storebill"."id", "spendings_storebill"."name", "spendings_storebill"."date", SUM("spendings_cost"."amount") AS "amount" FROM "spendings_storebill" LEFT OUTER JOIN "spendings_storebillpart" ON ("spendings_storebill"."id" = "spendings_storebillpart"."bill_id") LEFT OUTER JOIN "spendings_cost" ON ("spendings_storebillpart"."cost_ptr_id" = "spendings_cost"."id") WHERE ("spendings_storebill"."date" = 2012-03-28 ) GROUP BY "spendings_storebill"."id", "spendings_storebill"."name", "spendings_storebill"."date" HAVING SUM("spendings_cost"."amount") = 1.05 '}

As you can see from first query there is a result matching second one, yet I get no results for latter. If I run the generated query I get expected result (left only "amount" column):

sqlite> SELECT  SUM("spendings_cost"."amount") AS "amount" FROM "spendings_storebill" LEFT OUTER JOIN "spendings_storebillpart" ON ("spendings_storebill"."id" = "spendings_storebillpart"."bill_id") LEFT OUTER JOIN "spendings_cost" ON ("spendings_storebillpart"."cost_ptr_id" = "spendings_cost"."id") GROUP BY "spendings_storebill"."id", "spendings_storebill"."name", "spendings_storebill"."date" HAVING SUM("spendings_cost"."amount") = 1.05  LIMIT 21;
1.05

[zbyte64]

I believe I have experienced the same issue and it only seems to apply to Django 1.4 with sqlite.

from django.db import models

class Transaction(models.Model):
    tag = models.CharField(max_length=5)
    amount = models.DecimalField(max_digits=19, decimal_places=4)

def test_transaction():
    Transaction.objects.all().delete()
    Transaction.objects.create(amount=5, tag='a')
    Transaction.objects.create(amount=4, tag='a')
    Transaction.objects.create(amount=1, tag='b')
    Transaction.objects.create(amount='1.5', tag='b')
    Transaction.objects.create(amount=1, tag='c')
    
    qs = Transaction.objects.values('tag').annotate(sumamount=models.Sum('amount'))
    print qs #displays 3 results
    
    qs = Transaction.objects.values('tag').annotate(sumamount=models.Sum('amount')).filter(sumamount__gt=3)
    print qs #displays 0 results when using sqlite + Django 1.4, displays 1 result when using mysql or Django 1.3.1

[anonymous]

I was able to replicate that on just sqlite3 (C API), so it is not a bug with Django or driver.
Marking as invalid, as the bug is outside Django itself.

[fengb]

Per ​http://article.gmane.org/gmane.comp.db.sqlite.general/75696 , SUM values should not have affinity so typecasting Decimal into a string will never return real values.

I think changing the typecast of Decimal into float is "good enough". Internally, sqlite3 already represents these values as floats, and it would fix this bug. Otherwise, we would need separate casting engines depending on whether SUM is used.

[Rigel Di Scala]

#22117 was just closed as a duplicate of this ticket.

[dylan@…]

Replying to dylan@…:
As noted in #22117 this is still an issue: https://code.djangoproject.com/ticket/22117#comment:1

[Shai Berger]

#22128 asked for the limitation to be documented; I closed it as a duplicate of this. If this bug is wontfix'd, we should then add appropriate warnings on the SQLite-specific notes.

[Michael Tänzer]

On sqlite the SUM() of a decimal column doesn't have a NUMERIC type so when comparing it to a string literal (which a Decimal gets converted to in django) it is not compared as NUMERIC as may be expected but as something else (maybe string?).

I added a CAST to NUMERIC type when using a SQL function that is expected to return a Decimal.

All tests pass under SQLite.

[Tim Graham <timograham@…>]

In 3bbaf84d:

Fixed #18247 -- Added cast to NUMERIC for Decimals on sqlite

On sqlite the SUM() of a decimal column doesn't have a NUMERIC type so
when comparing it to a string literal (which a Decimal gets converted to
in Django) it is not compared as expected.

[Tuomas Suutari]

Shouldn't this be backported to the current LTS release (1.8) too? The commit seems to cherry-pick cleanly to stable/1.8.x branch.

[Tim Graham <timograham@…>]

In 068d7568:

Refs #18247 -- Fixed SQLite QuerySet filtering on decimal result of Least and Greatest.

[Tim Graham <timograham@…>]

In 8484cf4c:

[1.11.x] Refs #18247 -- Fixed SQLite QuerySet filtering on decimal result of Least and Greatest.

Backport of 068d75688f28f9a1530b771ae1d625f41e98cd82 from master

[Tim Graham <timograham@…>]

In c3c6c92d:

Refs #18247 -- Fixed filtering on CombinedExpression(output_field=DecimalField()) annotation on SQLite.