#32861: DecimalField on sqlite should use numeric instead of decimal type
-------------------------------------+-------------------------------------
Reporter: Thiago | Owner: nobody
Bellini Ribeiro |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: sqlite,
Severity: Normal | decimalfield
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hello,
I have a model like this:
{{{
class Product(models.Model):
original_price = models.DecimalField(
max_digits=20,
decimal_places=2,
default=None,
blank=True,
null=True,
)
price = models.DecimalField(
max_digits=20,
decimal_places=2,
)
}}}
I was trying to annotate the discount percentage and order by it like
this:
{{{
Product.objects.annotate(
on_sale_discount=Value(decimal.Decimal(1)) - (F("price") /
Coalesce("original_price", F("price"))),
).order_by(
"on_sale_discount",
)
}}}
Then I noticed that on sqlite (we use sqlite for development and
postgresql for production) the results were not actually coming sorted the
right way.
I checked the sql resulting from this query and noticed that the division
where producing a rounded result when there were no "decimal places" in
the number. For example:
1) One product that had a price of 39.9 and an original price of 50
produced an on_sale_discounbt of 0.798,
2) A product that had a price of 20 and an original price of 30 produced
an on_sale_discount of 0
Even after trying to cast "price" to decimal it didn't matter.
I posted an issue on sqlite forum regarding this:
https://www.sqlite.org/forum/forumpost/60050b3f5eb26eb4?t=h . In that link
you can see all the sql tests that I have made.
In the end, the issue is that DECIMAL on SQLite actually translates to
NUMERIC instead of REAL. This makes DecimalField behaves very differently
than you would expect in this situation (i.e. Postgresql/Mysql/etc would
do the expected thing).
Since this is something that sqlite will not change (look at our
discussion there), maybe it is something that Django could do? Probably by
mapping DecimalField to use a REAL type on sqlite?
--
Ticket URL: <https://code.djangoproject.com/ticket/32861>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.6044bd2087220f26749f7deb91ddfc64%40djangoproject.com.
