Opened 6 years ago

Last modified 20 months ago

#13666 new Bug

Updates with F objects on decimal fields raise MySQL warnings

Reported by: KyleMac Owned by: nobody
Component: Database layer (models, ORM) Version: 1.7
Severity: Normal Keywords:
Cc: josh@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Doing something like the following:

MyModel.objects.filter(whatever).update(price=F('price') - Decimal('1.00'))

raises an exception due to an ignorable warning from MySQL.

This is basically identical to #12293 except that the documentation would have you believe that this should work without any extra work.

Change History (13)

comment:1 Changed 6 years ago by Karen Tracey

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Resolution: worksforme
Status: newclosed

You've left out specifics of your model and the exception you get, so I'm not sure exactly what you are seeing. I don't get any warnings doing what you describe. Given this model:

class Account(models.Model):
    name = models.CharField(max_length=100)
    balance = models.DecimalField(decimal_places=2, max_digits=12, default=Decimal("0"))

    def __unicode__(self):
        return u'%s' %

A statement similar to what you show works fine, even with debug on, and with MySQL backend:

>>> from django.conf import settings
>>> settings.DATABASE_ENGINE
>>> settings.DEBUG
>>> from ttt.models import Account
>>> from decimal import Decimal
>>> Account.objects.create(name='z1',balance='25')
<Account: z1>
>>> Account.objects.create(name='z2',balance='25')
<Account: z2>
>>> from django.db.models import F
>>> Account.objects.filter(name__startswith='z').update(balance=F('balance')-Decimal('5.0'))
>>> Account.objects.get(name='z1').balance
>>> Account.objects.get(name='z2').balance

Nor do I see any exception when 0 rows are updated. So near as I can tell this does work without any extra work.

comment:2 Changed 6 years ago by KyleMac

Resolution: worksforme
Status: closedreopened

The exception I get is:

Data truncated for column 'balance' at row 1

The following raises the exception for me:

Account.objects.create(name='z1', balance='10.00')

However, using "25" and "5.0" from your example does not raise an exception. A rounding error from MySQL perhaps?

comment:3 Changed 6 years ago by KyleMac

Assuming that the field "balance" is a decimal field with 2 decimal places, in raw SQL the following will show the truncation warning:

UPDATE ticket SET balance = 10.00;
UPDATE ticket SET balance = balance - '1.79';

However, the following will work fine:

UPDATE ticket SET balance = 10.00;
UPDATE ticket SET balance = balance - 1.79;

It seems that MySQL doesn't like strings as much as it likes floats and Django converts Decimal() to strings. So

Account.objects.create(name='z1', balance='10.00')
Account.objects.filter(name__startswith='z').update(balance=F('balance') - Decimal('1.79'))


Account.objects.create(name='z1', balance='10.00')
Account.objects.filter(name__startswith='z').update(balance=F('balance') - '1.79')

raise exceptions, but

Account.objects.create(name='z1', balance='10.00')
Account.objects.filter(name__startswith='z').update(balance=F('balance') - float(Decimal('1.79')))

works fine.

comment:4 Changed 6 years ago by Russell Keith-Magee

Component: DocumentationDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted

Ok - I can verify the '1.79' problem; that means this isn't a documentation issue, it's a problem with the MySQL backend handling of Decimals.

comment:5 Changed 6 years ago by Karen Tracey

Might be related to this MySQL bug.

comment:6 Changed 5 years ago by Julien Phalip

Severity: Normal
Type: Bug

comment:7 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:8 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:9 Changed 4 years ago by Aymeric Augustin

Status: reopenednew

comment:10 Changed 3 years ago by anonymous

I recently encountered this exact same issue on MySQL 5.6.10 and Django 1.4.

The following SQL (which is what the Python MySQL adapter generates) fails reliably for me:

UPDATE `main_account`
SET `balance` = `main_userprofile`.`balance` - '0.00007'
WHERE `main_account`.`id` = 1;

However, adding explicit casts to decimal fixes the issue.

UPDATE `main_account`
SET `balance` = `main_userprofile`.`balance` - CAST('0.00007' AS DECIMAL(12, 8))
WHERE `main_account`.`id` = 1;

I assume this is because in the former case MySQL casts to a float, then subtracts, introducing precision issues, while in the latter case we explicitly cast directly to decimal of a certain precision.

I don't know what the solution is from a Django perspective, but thought the information could be useful.

comment:11 Changed 2 years ago by anonymous

Is patching django.db.backends.mysql.django_conversions with cast() the proper way to go?

comment:12 Changed 20 months ago by Josh

Cc: josh@… added
Version: 1.21.7

The Django ORM definitely needs to use CAST with MySQL. Otherwise, this bug may manifest randomly on various architectures.

Read this on MySQL Type Conversion:
"""Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use CAST() so that a value is not converted implicitly to a float-point number:"""'

comment:13 Changed 20 months ago by Josh Smeaton

Wouldn't it make sense to pass the number verbatim rather than stringifying it? Or could that introduce inconsistencies with floats being imprecise? What about the other backends, do they all accept string-like decimal numbers and happily call them decimals?

If we want to go with a wrapping type though, it should be possible (from 1.8/master) to provide an as_mysql() for the Value expression.

Something like:

class Value(..):
    def as_mysql(self, compiler, connection):
        if self.output_field.get_internal_type() == 'DecimalField':
                c = Cast(self, self.output_field)
                return compiler.compile(c)

Where the Cast type above would need to be implemented in some way. The implementation could be thorough and apply to all backends (quite a bit of work), or it could be extremely simple, and only work for Decimal types on MySQL. There's similar logic for Oracle with the Coalesce type and conversions to NCLOB.

I'd be wary about providing the digits and precision arguments to the CAST if they aren't exactly needed. If they can be avoided, then do so. Because there's no nice way (currently) to pass that information.

Note: See TracTickets for help on using tickets.
Back to Top