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 , aggregate functions should not have affinity so typecasting Decimal into a string will never have matching values.

I think changing the typecast of Decimal into float is "good enough". Internally, sqlite3 already represents these values as floats so I don't think there would be too detrimental to make it slightly more explicit. Otherwise, we would need separate casting engines depending on whether aggregation 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.