Bug #15028 | Update multiple tables updates more rows than expected | ||
---|---|---|---|
Submitted: | 17 Nov 2005 17:21 | Modified: | 8 Dec 2005 23:53 |
Reporter: | Dan Julson | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL 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 | CPU Architecture: | Any |
[17 Nov 2005 17:21]
Dan Julson
[17 Nov 2005 18:30]
Valeriy Kravchuk
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?
[17 Nov 2005 21:26]
Dan Julson
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----
[20 Nov 2005 14:20]
Valeriy Kravchuk
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...
[21 Nov 2005 15:08]
Dan Julson
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
[1 Dec 2005 20:22]
Bugs System
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
[2 Dec 2005 16:20]
Evgeny Potemkin
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
[2 Dec 2005 18:11]
Evgeny Potemkin
Fixed in 5.0.17
[2 Dec 2005 21:55]
Evgeny Potemkin
Fixed in 5.1.4
[8 Dec 2005 23:53]
Paul DuBois
Noted in 4.1.17, 5.1.17, 5.1.4 changelogs.
[2 Dec 2007 6:28]
Mark Bestland
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.
[2 Dec 2007 6:35]
Mark Bestland
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.