[Django] #32861: DecimalField on sqlite should use numeric instead of decimal type

Django

unread,

Jun 18, 2021, 11:32:34 AM6/18/21

to django-...@googlegroups.com

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.

Django

unread,

Jun 18, 2021, 5:24:27 PM6/18/21

to django-...@googlegroups.com

#32861: DecimalField on sqlite should use numeric instead of decimal type
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Thiago Bellini Ribeiro):

Also, note that the documentation here on
https://docs.djangoproject.com/en/3.2/ref/models/fields/#decimalfield and
the note regarding sqlite issues on
https://docs.djangoproject.com/en/3.2/ref/databases/#sqlite-decimal-
handling specified that Decimal is converted to REAL on sqlite.

By this note, or either it is wrong or the implementation needs to change
to really convert it to REAL, since as I described, DECIMAL actually maps
to NUMERIC and not REAL.

--
Ticket URL: <https://code.djangoproject.com/ticket/32861#comment:1>

Django

unread,

Jun 22, 2021, 2:48:43 AM6/22/21

to django-...@googlegroups.com

#32861: DecimalField on sqlite should use numeric instead of decimal type
-------------------------------------+-------------------------------------

Reporter: Thiago Bellini | Owner: nobody
Ribeiro |

Type: Uncategorized | Status: closed

Component: Database layer | Version: 3.2
(models, ORM) |

Severity: Normal | Resolution: wontfix

Keywords: sqlite, | Triage Stage:
decimalfield | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* status: new => closed
* resolution: => wontfix

Comment:

Hi.

I suspect we can't do anything here for backwards compatibility reasons
but can I ask you to open a discussion on the DevelopersMailingList about
this?

Maybe there's a good workaround, either for your own model (custom field?)
or a way of defining the annotation so you get the result your need. 🤔
(But the issue tracker isn't the best place to discuss that.)