Code

Opened 4 years ago

Last modified 12 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.2
Severity: Normal Keywords:
Cc: 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.

Attachments (0)

Change History (10)

comment:1 Changed 4 years ago by kmtracey

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to worksforme
  • Status changed from new to closed

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 Changed 4 years ago by KyleMac

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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 Changed 4 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';
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 Changed 4 years ago by russellm

  • Component changed from Documentation to Database layer (models, ORM)
  • Triage Stage changed from Unreviewed to Accepted

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 4 years ago by kmtracey

Might be related to this MySQL bug.

comment:6 Changed 3 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:7 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:8 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:9 Changed 13 months ago by aaugustin

  • Status changed from reopened to new

comment:10 Changed 12 months 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.