Opened 3 years ago

Closed 4 weeks ago

#18247 closed Bug (fixed)

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

Reported by: elmopl@… Owned by: NEOatNHNG
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: decimal filter annotate having
Cc: zbyte64, dylan@…, NEOatNHNG Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (14)

comment:1 Changed 3 years ago by zbyte64

  • Cc zbyte64 added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Type changed from Uncategorized to Bug

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

comment:2 Changed 3 years ago by anonymous

  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:3 Changed 3 years ago by fengb

  • Resolution invalid deleted
  • Status changed from closed to reopened

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.

Last edited 3 years ago by fengb (previous) (diff)

comment:4 Changed 3 years ago by aaugustin

  • Triage Stage changed from Unreviewed to Design decision needed

comment:5 Changed 2 years ago by aaugustin

  • Status changed from reopened to new

comment:6 Changed 2 years ago by aaugustin

  • Triage Stage changed from Design decision needed to Accepted

comment:7 Changed 17 months ago by zr

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

comment:8 follow-up: Changed 17 months ago by dylan@…

  • Cc dylan@… added
  • Version changed from 1.4 to master

comment:9 in reply to: ↑ 8 Changed 17 months ago by dylan@…

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

comment:10 Changed 17 months ago by shai

#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.

comment:11 Changed 2 months ago by NEOatNHNG

  • Cc NEOatNHNG added
  • Keywords annotate having added
  • Owner changed from nobody to NEOatNHNG
  • Status changed from new to assigned

comment:12 Changed 8 weeks ago by NEOatNHNG

  • Has patch set

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.

comment:13 Changed 4 weeks ago by timgraham

  • Summary changed from filter and Decimal equality doesn't work to Filtering on aggregate annotations with a Decimal value doesn't work
  • Triage Stage changed from Accepted to Ready for checkin

comment:14 Changed 4 weeks ago by Tim Graham <timograham@…>

  • Resolution set to fixed
  • Status changed from assigned to closed

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.

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