MySQL Bugs: #79333: Unexpected error 1093 on nested subquery for update

Bug #79333	Unexpected error 1093 on nested subquery for update
Submitted:	19 Nov 2015 5:16	Modified:	7 Dec 2015 18:30
Reporter:	Danielle McLean	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.9	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
It is illegal to write a query in the form

  DELETE FROM t WHERE id IN (SELECT id FROM t);

or any similar query, as noted in https://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html Such a query is rightly rejected by the MySQL server, producing an error message like

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

However, the Restrictions on Subqueries page linked above also mentions an exception to the rule - it is explicitly valid to write such an updating statement by nesting two subqueries, like this:

  DELETE FROM t WHERE id IN (SELECT * FROM (SELECT id FROM t) AS hack);

The above statement should be legal and should achieve the effect the first statement meant to achieve. It might not be especially efficient, but it should definitely work. However, in MySQL 5.7.9, this second query is also rejected, producing exactly the same error message as the first query.

As the documentation claims this is a legal query, I can only assume a bug has been introduced in the MySQL server. Note that MariaDB (I tested MariaDB 10.0.15) behaves as expected - the first query is rejected, and the second query is accepted.

How to repeat:
CREATE DATABASE _sample;
USE _sample;
CREATE TABLE sample (id INT PRIMARY KEY);
DELETE FROM sample WHERE id IN (SELECT * FROM (SELECT id FROM sample) AS hack);
-- error is produced unexpectedly:
-- ERROR 1093 (HY000): You can't specify target table 'sample' for update in FROM clause

Suggested fix:
I don't know which part of MySQL has changed to cause this issue, but it does appear to be a regression. Perhaps subquery optimisation is now too aggressive and accidentally reduces the valid double-subquery form to the invalid single-subquery form? That's one possibility. Whatever's changed needs fixing, basically.

5.0/5.1/5.5/56 Results (version not affected)

Attachment: results_delete_query.txt (text/plain), 3.44 KiB.

Thank you for the bug report. Only version 5.7 affected.

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.10 Source distribution PULL: 2015-NOV-07

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 > CREATE DATABASE _sample;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE _sample;
Database changed
mysql 5.7 > CREATE TABLE sample (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > DELETE FROM sample WHERE id IN (SELECT * FROM (SELECT id FROM sample) AS hack);
ERROR 1093 (HY000): You can't specify target table 'sample' for update in FROM clause

This requirement was missed when implementing support for merging of derived tables the way that view references are used. We plan to fix this problem in the first possible point release for 5.7.

In the meantime, a few workarounds are possible:

- set optimizer_switch='derived_merge=off'

- Replace the derived table with a view created with 'ALGORITHM=TEMPTABLE'

- Make sure the derived table must be materialized, e.g by adding a LIMIT clause in its definition.
  (The LIMIT must be larger than the number of rows in the query).
 Example: DELETE FROM t WHERE id IN (SELECT * FROM (SELECT id FROM t LIMIT 999999) AS hack);

Noted in 5.7.11, 5.8.0 changelogs.

A regression caused failure of the workaround at 
http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html
for avoiding ER_UPDATE_TABLE_USED errors when referencing the same
table in a subquery as was used as target for an UPDATE or DELETE
statement.