MySQL Bugs: #79590: ERROR 1093 (HY000): You can't specify target table '.' for update in FROM clause

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

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;

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 >

Yes, I also could confirm this only happened on v5.7,  just get this issue after upgrading

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.