| Bug #6978 | Errors on multi table update and delete | ||
|---|---|---|---|
| Submitted: | 3 Dec 2004 9:07 | Modified: | 3 Dec 2004 11:36 |
| Reporter: | Jan Lindström | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.8 & 5.0.3 | OS: | Linux (Linux 2.4.21-99 smp) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[3 Dec 2004 10:26]
Heikki Tuuri
Jan, please read the manual carefully. For example, in the multi-table delete, you have an OR-condition, and every row in both tables will be in the join result. Then it might be the documented behavior to delete all rows in both tables. Regards, Heikki
[3 Dec 2004 11:08]
Jan Lindström
Manual does not clearly say what happens if you are using or. But and works as expected: mysql> select * from a; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | +---+------+ 6 rows in set (0.00 sec) mysql> select * from b; +---+------+ | c | d | +---+------+ | 1 | 2 | | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | +---+------+ 6 rows in set (0.01 sec) mysql> update a,b set a = 9, b = 4, c = 9, d = 6 where a = 1 and c = 2; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from a; +---+------+ | a | b | +---+------+ | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | | 9 | 4 | +---+------+ 6 rows in set (0.00 sec) mysql> select * from b; +---+------+ | c | d | +---+------+ | 1 | 2 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | | 9 | 6 | +---+------+ 6 rows in set (0.00 sec) mysql> delete a,b from a,b where a = 3 and c = 4; Query OK, 2 rows affected (0.00 sec) mysql> select * from a; +---+------+ | a | b | +---+------+ | 2 | 3 | | 4 | 3 | | 5 | 2 | | 6 | 3 | | 9 | 4 | +---+------+ 5 rows in set (0.00 sec) mysql> select * from b; +---+------+ | c | d | +---+------+ | 1 | 2 | | 3 | 2 | | 5 | 2 | | 6 | 3 | | 9 | 6 | +---+------+ 5 rows in set (0.00 sec)
[3 Dec 2004 11:36]
Jan Lindström
I should have done my homework on cartesian product in relational algebra. Provided example query works as it should, because: (1) There are several matching rows in cartesian product on update and therefore it will result duplicate key (same row from both tables a and b exists in cartesian product several times). (2) Similarly delete will remove all rows because all rows in both tables are part of the cartesian product of all rows. As an example consider following: mysql> insert into b values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into a values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select count(*) from a,b; +----------+ | count(*) | +----------+ | 36 | +----------+ 1 row in set (0.04 sec) mysql> select count(*) from a,b where a=3 or c=4; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) mysql> select * from a,b; +---+------+---+------+ | a | b | c | d | +---+------+---+------+ | 1 | 2 | 1 | 2 | | 2 | 3 | 1 | 2 | | 3 | 2 | 1 | 2 | | 4 | 3 | 1 | 2 | | 5 | 2 | 1 | 2 | | 6 | 3 | 1 | 2 | | 1 | 2 | 2 | 3 | | 2 | 3 | 2 | 3 | | 3 | 2 | 2 | 3 | | 4 | 3 | 2 | 3 | | 5 | 2 | 2 | 3 | | 6 | 3 | 2 | 3 | | 1 | 2 | 3 | 2 | | 2 | 3 | 3 | 2 | | 3 | 2 | 3 | 2 | | 4 | 3 | 3 | 2 | | 5 | 2 | 3 | 2 | | 6 | 3 | 3 | 2 | | 1 | 2 | 4 | 3 | | 2 | 3 | 4 | 3 | | 3 | 2 | 4 | 3 | | 4 | 3 | 4 | 3 | | 5 | 2 | 4 | 3 | | 6 | 3 | 4 | 3 | | 1 | 2 | 5 | 2 | | 2 | 3 | 5 | 2 | | 3 | 2 | 5 | 2 | | 4 | 3 | 5 | 2 | | 5 | 2 | 5 | 2 | | 6 | 3 | 5 | 2 | | 1 | 2 | 6 | 3 | | 2 | 3 | 6 | 3 | | 3 | 2 | 6 | 3 | | 4 | 3 | 6 | 3 | | 5 | 2 | 6 | 3 | | 6 | 3 | 6 | 3 | +---+------+---+------+ 36 rows in set (0.00 sec) mysql> select * from a,b where a=3 or c=4; +---+------+---+------+ | a | b | c | d | +---+------+---+------+ | 3 | 2 | 1 | 2 | | 3 | 2 | 2 | 3 | | 3 | 2 | 3 | 2 | | 1 | 2 | 4 | 3 | | 2 | 3 | 4 | 3 | | 3 | 2 | 4 | 3 | | 4 | 3 | 4 | 3 | | 5 | 2 | 4 | 3 | | 6 | 3 | 4 | 3 | | 3 | 2 | 5 | 2 | | 3 | 2 | 6 | 3 | +---+------+---+------+ 11 rows in set (0.00 sec)
Description: Multi table update returns a duplicate key error message when there are no duplicate keys in a relation. Multi table delete removes all rows even when using where clause. For example: mysql> insert into a values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into b values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> drop table a; Query OK, 0 rows affected (0.00 sec) mysql> drop table b; Query OK, 0 rows affected (0.00 sec) mysql> create table a(a int not null, b int, primary key(a)); Query OK, 0 rows affected (0.01 sec) mysql> create table b(c int not null, d int, primary key(c)); Query OK, 0 rows affected (0.00 sec) mysql> insert into a values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into b values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from a; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | +---+------+ 6 rows in set (0.00 sec) mysql> select * from b; +---+------+ | c | d | +---+------+ | 1 | 2 | | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 2 | | 6 | 3 | +---+------+ 6 rows in set (0.00 sec) mysql> update a,b set a = 9, b = 4, c = 9, d = 6 where -> a = 1 or c = 1; ERROR 1062 (23000): Duplicate entry '9' for key 1 mysql> delete a,b from a,b where a = 3 or c = 4; Query OK, 12 rows affected (0.00 sec) mysql> select * from a; Empty set (0.00 sec) mysql> select * from b; Empty set (0.00 sec) These test using innodb, but similar results when using engine = myisam; How to repeat: create table a(a int not null, b int, primary key(a)); create table b(c int not null, d int, primary key(c)); insert into a values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); insert into b values (1,2),(2,3),(3,2),(4,3),(5,2),(6,3); update a,b set a = 9, b = 4, c = 9, d = 6 where a = 1 or c = 1; delete a,b from a,b where a = 3 or c = 4; commit;