Different behavior for Django DecimalField on sQlite vs MySQL

Question

I've just put online a website which was developed on sqlite3, but when deploying I needed to switch to MySQL.

Everything went well except for the products price because on sqlite and MySQL DecimalField apparently doesn't behave the same.

Consider the following model:

class ProductBase(models.Model):
    title   = models.CharField('Title', max_length=250)
    price   = models.DecimalField('Price', max_digits=9, decimal_places=2, default=0)

In development, sqlite will allow the price to be "0" or "100" for example.

However when transferred to MySQL, the backend will also allow "0" or "100" inputs, but when the object instance is saved it's transformed automatically into "0.00" or "100.00".

I know the ORM is an abstraction level and that it has its limits due to discrepancies among database backends features.. that's why I'm asking here if it's a limitation related bug or really a bug .. before opening a new ticket..

Answer 1

SQLite's DECIMAL datatype, which is actually a NUMERIC in the database, does not allow specifying a size or precision; it is stored as either an INTEGER, a REAL, or a TEXT depending on criteria as given in the documentation. Precision information is not available to the backend, and so is not preserved.

MySQL's NUMERIC datatype is a true arbitrary-length fixed point type, and comes back from the database with full precision information.