Bug #79590 ERROR 1093 (HY000): You can't specify target table '.' for update in FROM clause
Submitted: 10 Dec 2015 9:33 Modified: 6 Jan 2016 16:56
Reporter: zhang jinzhu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.9 OS:MacOS
Assigned to: CPU Architecture:Any

[10 Dec 2015 9:33] zhang jinzhu
Description:
UPDATE users SET position = (SELECT COUNT(pos) + 1 FROM (SELECT DISTINCT(position) AS pos FROM users) AS t2 WHERE t2.pos < users.position) WHERE id = 3;

Will report `ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause`

But from here, http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#derived-table-optimizati... It says:

----
The derived_merge flag controls whether the optimizer attempts to merge derived tables and views into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging and avoids the error just described. (

*********Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization**************

.) If an ER_UPDATE_TABLE_USED error occurs for a view reference that uses an expression equivalent to the subquery, adding ALGORITHM=TEMPTABLE to the view definition prevents merging and takes precedence over the current derived_merge value.

----

I wonder to know why it seems doesn't work? any thing wrong?

How to repeat:
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (`id` int unsigned AUTO_INCREMENT, `name` varchar(255), `position` int DEFAULT NULL, PRIMARY KEY (`id`));

INSERT INTO `users` (`name`,`position`) VALUES ('user1','1');
INSERT INTO `users` (`name`,`position`) VALUES ('user2','2');
INSERT INTO `users` (`name`,`position`) VALUES ('user3','3');
INSERT INTO `users` (`name`,`position`) VALUES ('user4','4');
INSERT INTO `users` (`name`,`position`) VALUES ('user5','5');

UPDATE users SET position = (SELECT COUNT(pos) + 1 FROM (SELECT DISTINCT(position) AS pos FROM users) AS t2 WHERE t2.pos < users.position) WHERE id = 3;
[10 Dec 2015 9:47] MySQL Verification Team
Thank you for the bug report. repeatable only with 5.7.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.11 Source distribution PULL: 2015-DEC-05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > DROP TABLE IF EXISTS `users`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE `users` (`id` int unsigned AUTO_INCREMENT, `name` varchar(255), `position` int DEFAULT NULL, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.72 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO `users` (`name`,`position`) VALUES ('user1','1');
Query OK, 1 row affected (0.14 sec)

mysql 5.7 > INSERT INTO `users` (`name`,`position`) VALUES ('user2','2');
Query OK, 1 row affected (0.16 sec)

mysql 5.7 > INSERT INTO `users` (`name`,`position`) VALUES ('user3','3');
Query OK, 1 row affected (0.11 sec)

mysql 5.7 > INSERT INTO `users` (`name`,`position`) VALUES ('user4','4');
Query OK, 1 row affected (0.08 sec)

mysql 5.7 > INSERT INTO `users` (`name`,`position`) VALUES ('user5','5');
Query OK, 1 row affected (0.06 sec)

mysql 5.7 >
mysql 5.7 > UPDATE users SET position = (SELECT COUNT(pos) + 1 FROM (SELECT DISTINCT(position) AS pos FROM users) AS t2 WHERE t2.pos < users.position) WHERE id = 3;
ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause
mysql 5.7 > exit
Bye

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.48 Source distribution PULL: 2015-DEC-05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > DROP TABLE IF EXISTS `users`;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql 5.5 > CREATE TABLE `users` (`id` int unsigned AUTO_INCREMENT, `name` varchar(255), `position` int DEFAULT NULL, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.17 sec)

mysql 5.5 >
mysql 5.5 > INSERT INTO `users` (`name`,`position`) VALUES ('user1','1');
Query OK, 1 row affected (0.05 sec)

mysql 5.5 > INSERT INTO `users` (`name`,`position`) VALUES ('user2','2');
Query OK, 1 row affected (0.05 sec)

mysql 5.5 > INSERT INTO `users` (`name`,`position`) VALUES ('user3','3');
Query OK, 1 row affected (0.06 sec)

mysql 5.5 > INSERT INTO `users` (`name`,`position`) VALUES ('user4','4');
Query OK, 1 row affected (0.06 sec)

mysql 5.5 > INSERT INTO `users` (`name`,`position`) VALUES ('user5','5');
Query OK, 1 row affected (0.06 sec)

mysql 5.5 >
mysql 5.5 > UPDATE users SET position = (SELECT COUNT(pos) + 1 FROM (SELECT DISTINCT(position) AS pos FROM users) AS t2 WHERE t2.pos < users.position) WHERE id = 3;
Query OK, 0 rows affected (0.28 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql 5.5 > exit
Bye

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29 Source distribution PULL: 2015-DEC-05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > DROP TABLE IF EXISTS `users`;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql 5.6 > CREATE TABLE `users` (`id` int unsigned AUTO_INCREMENT, `name` varchar(255), `position` int DEFAULT NULL, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.61 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `users` (`name`,`position`) VALUES ('user1','1');
Query OK, 1 row affected (0.05 sec)

mysql 5.6 > INSERT INTO `users` (`name`,`position`) VALUES ('user2','2');
Query OK, 1 row affected (0.08 sec)

mysql 5.6 > INSERT INTO `users` (`name`,`position`) VALUES ('user3','3');
Query OK, 1 row affected (0.06 sec)

mysql 5.6 > INSERT INTO `users` (`name`,`position`) VALUES ('user4','4');
Query OK, 1 row affected (0.14 sec)

mysql 5.6 > INSERT INTO `users` (`name`,`position`) VALUES ('user5','5');
Query OK, 1 row affected (0.05 sec)

mysql 5.6 >
mysql 5.6 > UPDATE users SET position = (SELECT COUNT(pos) + 1 FROM (SELECT DISTINCT(position) AS pos FROM users) AS t2 WHERE t2.pos < users.position) WHERE id = 3;
Query OK, 0 rows affected (0.11 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql 5.6 >
[10 Dec 2015 9:55] zhang jinzhu
Yes, I also could confirm this only happened on v5.7,  just get this issue after upgrading
[6 Jan 2016 16:56] Paul DuBois
Noted in 5.7.11, 5.8.0 changelogs.

A derived table contained in the SET clause of an UPDATE statement 
should be materialized to avoid an error about updating a table that
is also read in the same statement. Materialization did not occur for
some statements, leading to that error.