MySQL Bugs: #15028: Update multiple tables updates more rows than expected

Bug #15028	Update multiple tables updates more rows than expected
Submitted:	17 Nov 2005 18:21	Modified:	9 Dec 2005 0:53
Reporter:	Dan Julson
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, 5.0.15, 4.1.14/5.0.13	OS:	Linux (Linux, Windows, Solaris)
Assigned to:	Evgeny Potemkin	Target Version:

Description:
First of all, the reason for the two different releases and two different operating
systems is that we tested the scenario on two different machines with the same results. 
The machine running Solaris 9 has MySQL 4.1.14 and the machine running Mandrake has MySQL
5.0.13.  We also tried this using MyISAM and InnoDB storage engines for each table with
the same results.

I have two tables X and Z. Table X has three fields - x int, y int, z int. 
Table X
x y z
1 0 10
1 0 20

Table Z has two fields - x int, z int.
Table Z
x z
1 null
3 null

I set up the update statement as follows:
update Z, X set Z.z = X.z, X.y = 1 where isnull(Z.z) and X.x = Z.x and X.y <> 1;

What I think should happen in this case is table X's first row's y value should be updated
to 1 and table Z's z value should be updated to 10 yielding table result sets as follows
and a command line return value like 'Rows matched: 2  Changed: 2  Warnings: 0'.

Table X
x y z
1 1 10
1 0 20

Table Z
x z
1 10
3 null

What happened in this case is both of table X's rows had their y values updated to 1,
which I would not expect to happen because of the isnull(Z.z) in the where clause.  The
command line return statement was 'Rows matched: 3  Changed: 3  Warnings: 0'.  The
following are the Select * statements after the update is run. 

Table X
x y z
1 1 10
1 1 20

Table Z
x z
1 10
3 null

When I change the tables around within the update statement such that the update statement
is the following,
update X, Z set Z.z = X.z, X.y = 1 where isnull(Z.z) and X.x = Z.x and X.y <> 1; I get a
result set that is similar, but the mysql command line return is this: 'Rows matched: 4 
Changed: 3  Warnings: 0'.  It is also puzzling that by just changing the order of the
tables changes the number of rows matched.  I would think the command line result would be
the same no matter what order the tables were in. 

Changing the order of the set statements makes no difference in the results either.

How to repeat:
CREATE table X (x int, y int, z int);
CREATE table Z (x int, z int);
INSERT Into X Values (1, 0, 10), (1, 0, 20);
INSERT Into Z Values (1, null), (3, null);
UPDATE Z, X SET Z.z = X.z, X.y = 1 WHERE ISNULL(Z.z) AND X.x = Z.x AND X.y <> 1;

To reset tables;
UPDATE X SET y = 0;
UPDATE Z SET z = null where x=1;

Thank you for a detailed problem report. I've got the same results on 5.0.15-nt:

mysql> delimiter ;
mysql> CREATE table X (x int, y int, z int);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE table Z (x int, z int);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT Into X Values (1, 0, 10), (1, 0, 20);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT Into Z Values (1, null), (3, null);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE Z, X SET Z.z = X.z, X.y = 1 WHERE ISNULL(Z.z) AND X.x = Z.x AND X.y <> 1;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from X;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 |    1 |   10 |
|    1 |    1 |   20 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from Z;
+------+------+
| x    | z    |
+------+------+
|    1 |   10 |
|    3 | NULL |
+------+------+
2 rows in set (0.01 sec)

mysql> UPDATE X SET y = 0;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE Z SET z = null where x=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from X;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 |    0 |   10 |
|    1 |    0 |   20 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from Z;
+------+------+
| x    | z    |
+------+------+
|    1 | NULL |
|    3 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> UPDATE X,Z SET Z.z = X.z, X.y = 1 WHERE ISNULL(Z.z) AND X.x = Z.x AND X.y <> 1;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 4  Changed: 3  Warnings: 0

This change in "Rows matched" is a small bug, from my point of view. At least, it needs
some explanation.

As for the results of update, 3 rows is OK really. Your UPDATE gets 2 rows from X, both
with 1 corresponding row from Z. Look at the select results on the same initial data:

mysql> select X.y, X.x, Z.x, Z.z from Z, X WHERE ISNULL(Z.z) AND X.x = Z.x AND X.y <> 1;
+------+------+------+------+
| y    | x    | x    | z    |
+------+------+------+------+
|    0 |    1 |    1 | NULL |
|    0 |    1 |    1 | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select X.y, X.x, Z.x, Z.z from X, Z WHERE ISNULL(Z.z) AND X.x = Z.x AND X.y <> 1;
+------+------+------+------+
| y    | x    | x    | z    |
+------+------+------+------+
|    0 |    1 |    1 | NULL |
|    0 |    1 |    1 | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

So, your expected result is wrong, according to the result of this select.

What do you think abot these?

Valeriy,

Thanks much for your response.  If all is true, then  Z.z should have been updated again
to the value that was held in the second row of the X table.  Thus the following result
set should have occurred.

mysql> select * from X;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 |    1 |   10 |
|    1 |    1 |   20 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from Z;
+------+------+
| x    | z    |
+------+------+
|    1 |   20 |
|    3 | NULL |
+------+------+

However, because ISNULL(Z.z) is in the where clause, this doesn't occur.  It is for that
same reason that I think the second row in table X should not be disturbed.  Each update
to each table should occur if and only if all where conditions succeed.  Here is a
detailed scenario of what I thought would happen.
1. Z.z is updated to 10, X.y is updated to 1 because Z.z was null and X.y was not 1 and
X.x was equal to Z.x.
2. Run again to find out that we don't need to update anymore because Z.z is NOT null when
X.x is equal to Z.x.  ----End of Update----

I do not know exactly, are these results correct or not (I can not find anything about it
in the documentation), so I just mark this bug report as verified (the results are the
same on the latest 5.0.17-BK on Linux).

By the way, the other RDBMS capable of multiple tables update (Oracle) does not allow to
perform this kind of updates at all (without PRIMARY KEY involved etc.):

SQL>  update (select X.y c1, Z.z c2, X.z c3 from X,Z where X.x = Z.z and X.y <> 1 and Z.z
is null) set c1=1, c2 = c3;
 update (select X.y c1, Z.z c2, X.z c3 from X,Z where X.x = Z.z and X.y <> 1 and Z.z is
null) set c1
                                                                                          
       *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
...

So, there may be no universal reasonable way to process such an updates at all. Let the
developers decide...

Valeriy,

-snip-
update (select X.y c1, Z.z c2, X.z c3 from X,Z where X.x = Z.z
and X.y <> 1 and Z.z is null) set c1=1, c2 = c3;
 update (select X.y c1, Z.z c2, X.z c3 from X,Z where X.x = Z.z and X.y
<> 1 and Z.z is null) set c1
-end snip-

This Update statement you ran in Oracle is not correct.  'Where X.x = Z.z' should have
read 'Where X.x = Z.x.'  Furthermore, changing the x values within both the X and Z tables
to be primary keys does nothing for this, at least from what we tested.

Nevertheless, thank you very much for taking the time to try to solve the issue.  What
shall I expect from here?  From your email, it seems that you are forwarding this on to
the developers.  Will I hear anything from them as far as a workaround or a possible
solution to this issue?

Thanks again,
Dan

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32931

multi_update::send_data() was counting updates, not updated rows. Thus if one 
record have several updates it will be counted several times in 'rows matched'
but updated only once.

Fixed in 4.1.17, cset 1.2469.3.1

Fixed in 5.0.17

Fixed in 5.1.4

Noted in 4.1.17, 5.1.17, 5.1.4 changelogs.

Is there a case that (isnull(Z.z) = true ) would work better ?

I also remember reading that all comparisons involving null result in a null return. ( I
read that somewhere, really, I did, honest), but I avoid using nulls, because they are
null.

The problem occurs when you go to read back null values;  Not all drivers handle them the
same way.  Some crash, some don't, if the target variables are not in fact true variant
placeholders that can hold 'NULL'.

Avoid trouble. Initialize your data with defaults.

And, by the way, I'm VERY impressed with the way MySQL is being handled.

My hopes are to migrate to pure Java on Linux as soon as possible, for thin clients.  I'm
spending far too much time fighting dotnet issues already, and my environment of choice is
being subrogated into the dotnet framework.  NO WAY.
I don't want to move to java, I mostly have to.  Hope it doesn't sux.

Keep up the good work.