Opened 14 years ago

Closed 19 months ago

#13666 closed Bug (worksforme)

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

Description

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 (14)

comment:1 by Karen Tracey, 14 years ago

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

comment:2 by KyleMac, 14 years ago

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

comment:3 by KyleMac, 14 years ago

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.

comment:4 by Russell Keith-Magee, 14 years ago

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 by Karen Tracey, 14 years ago

Might be related to this MySQL bug.

comment:6 by Julien Phalip, 13 years ago

Severity: Normal
Type: Bug

comment:7 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:8 by Aymeric Augustin, 12 years ago

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:9 by Aymeric Augustin, 11 years ago

Status: reopenednew

comment:10 by anonymous, 11 years ago

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 by anonymous, 10 years ago

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

comment:12 by Josh, 9 years ago

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

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

comment:13 by Josh Smeaton, 9 years ago

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.

comment:14 by Simon Charette, 19 months ago

Resolution: worksforme
Status: newclosed

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

    diff --git a/tests/model_fields/models.py b/tests/model_fields/models.py
    index 9a64d51766..2a9bcd95cc 100644
    a b  
    1616except ImportError:
    1717    Image = None
    1818
     19class Account(models.Model):
     20    name = models.CharField(max_length=100)
     21    balance = models.DecimalField(decimal_places=2, max_digits=12)
     22
    1923
    2024class Foo(models.Model):
    2125    a = models.CharField(max_length=10)
  • tests/model_fields/test_decimalfield.py

    diff --git a/tests/model_fields/test_decimalfield.py b/tests/model_fields/test_decimalfield.py
    index 912d55af72..a45e634ff4 100644
    a b  
    33
    44from django.core import validators
    55from django.core.exceptions import ValidationError
    6 from django.db import models
     6from django.db import models, connection
    77from django.test import TestCase
    88
    9 from .models import BigD, Foo
     9from .models import Account, BigD, Foo
    1010
    1111
    1212class DecimalFieldTests(TestCase):
    def test_roundtrip_with_trailing_zeros(self):  
    113113        obj = Foo.objects.create(a="bar", d=Decimal("8.320"))
    114114        obj.refresh_from_db()
    115115        self.assertEqual(obj.d.compare_total(Decimal("8.320")), Decimal("0"))
     116
     117    def test_update_difference(self):
     118        acc = Account.objects.create(balance="10.00")
     119        acc.balance = models.F("balance") - "1.79"
     120        acc.save()
     121        acc.refresh_from_db()
     122        self.assertEqual(acc.balance, Decimal("8.21"))
     123        with connection.cursor() as cursor:
     124            cursor.execute("SHOW WARNINGS")
     125            self.assertIsNone(cursor.fetchone())
Note: See TracTickets for help on using tickets.
Back to Top