id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32861	DecimalField on sqlite should use numeric instead of decimal type	Thiago Bellini Ribeiro	nobody	"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?"	Uncategorized	closed	Database layer (models, ORM)	3.2	Normal	wontfix	sqlite, decimalfield		Unreviewed	0	0	0	0	0	0