Updates with F objects on decimal fields raise MySQL warnings

[KyleMac]

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.

[Karen Tracey]

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' % self.name

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
'mysql'
>>> settings.DEBUG
True
>>> 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'))
2L
>>> Account.objects.get(name='z1').balance
Decimal("20.00")
>>> Account.objects.get(name='z2').balance
Decimal("20.00")
>>>

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

[KyleMac]

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')
Account.objects.filter(name__startswith='z').update(balance=F('balance')-Decimal('1.79'))

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

[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';
SHOW WARNINGS;

However, the following will work fine:

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

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

or

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.

[Russell Keith-Magee]

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.

[Karen Tracey]

Might be related to ​this MySQL bug.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

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

[anonymous]

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

[Josh]

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:"""'

​http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

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

[Simon Charette]

I'm not able to reproduce anymore on MySQL 8 strict mode (minimum version planned for 4.2) so they must have sorted out their decimal arithmetic over the years so even if the Cast could be added by CombinedExpression or even by MySQL's combine_expression I don't think it's worth doing at this point.

Closing as worksforme as demonstrated by this test.

tests/model_fields/models.py

@@ -16 +16 @@
 except ImportError:
     Image = None
 
+class Account(models.Model):
+    name = models.CharField(max_length=100)
+    balance = models.DecimalField(decimal_places=2, max_digits=12)
+
 
 class Foo(models.Model):
     a = models.CharField(max_length=10)

tests/model_fields/test_decimalfield.py

@@ -3 +3 @@
 
 from django.core import validators
 from django.core.exceptions import ValidationError
-from django.db import models
+from django.db import models, connection
 from django.test import TestCase
 
-from .models import BigD, Foo
+from .models import Account, BigD, Foo
 
 
 class DecimalFieldTests(TestCase):
@@ -113 +113 @@
         obj = Foo.objects.create(a="bar", d=Decimal("8.320"))
         obj.refresh_from_db()
         self.assertEqual(obj.d.compare_total(Decimal("8.320")), Decimal("0"))
+
+    def test_update_difference(self):
+        acc = Account.objects.create(balance="10.00")
+        acc.balance = models.F("balance") - "1.79"
+        acc.save()
+        acc.refresh_from_db()
+        self.assertEqual(acc.balance, Decimal("8.21"))
+        with connection.cursor() as cursor:
+            cursor.execute("SHOW WARNINGS")
+            self.assertIsNone(cursor.fetchone())