DecimalField on sqlite should use numeric instead of decimal type

[Thiago Bellini Ribeiro]

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?

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

[Carlton Gibson]

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

If there's a way forward we can re-open.