I'm having the following problem using Django with MySQL 5.5.22.

Given a table with columns id, level and a 2x2 matrix stored as a11, a12, a21, a22, I have this row:

id   a11   a12   a21   a22   level
324  3     2     5     3     2

Given a queryset qs, I do the following update:

qs.update(
    a11=(b12 * a21 - b11 * a22) * F('a11') + (b11 * a12 - b12 * a11) * F('a21'),
    a12=(b12 * a21 - b11 * a22) * F('a12') + (b11 * a12 - b12 * a11) * F('a22'),
    a21=(b22 * a21 - b21 * a22) * F('a11') + (b21 * a12 - b22 * a11) * F('a21'),
    a22=(b22 * a21 - b21 * a22) * F('a12') + (b21 * a12 - b22 * a11) * F('a22'),
    level=(F('level') - 1)
    )

For which django generates the following query (got it from db.connection.queries, remove the where clause for brevity):

UPDATE `storage` 
SET 
`a21` = (3 * `storage`.`a11`) + (-1 * `storage`.`a21`), 
`a22` = (3 * `storage`.`a12`) + (-1 * `storage`.`a22`), 
`level` = `storage`.`level` - -1, 
`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`), 
`a12` = (2 * `storage`.`a12`) + (-1 * `storage`.`a22`) 

And my row looks like this after that:

id   a11   a12   a21   a22   level
324  2     1     4     3     1

For any row, a12*a21 - a11*a22 = 1 is supposed to be True, and according to that, the row was supposed to be:

id   a11   a12   a21   a22   level
324  1     1     4     3     1

This is what I get on SQLite, with Django generating the same query, and it took me a lot of time to figure that MySQL was doing something different. From the query, it seems like when updating interdepent multiple rows, MySQL doesn't treat it as a single atomic operation, and as columns are updated, they affect the values dependent on them. I confirmed this seems to be what happens by the following code on the Python prompt:

>>> a11, a12, a2
Edit
Report