Bug #81014 DELETE from joined tables with WHERE using derived table fails with error 1093
Submitted: 8 Apr 2016 13:28 Modified: 29 Apr 2016 17:17
Reporter: Jim Parks Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[8 Apr 2016 13:28] Jim Parks
Description:
Given tables t1 and t2, DELETE using a derived table in the WHERE clause fails if one of the affected tables is used in the derived table.  This is regardless of setting for derived_merge.  Works fine on 5.6.29 and 5.1.71.

Breaks many existing queries.  Workaround is to use a temporary table instead of a derived table. 

How to repeat:
Create tables and run DELETE as follows:

mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.7.11-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE t1, t2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.id IN (SELECT * FROM (SELECT id FROM t1 ) t1sub );
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> SHOW VARIABLES LIKE 'optimizer_switch';+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SET optimizer_switch = 'derived_merge=off';                                                                                                                                            Query OK, 0 rows affected (0.00 sec)

mysql> DELETE t1, t2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.id IN (SELECT * FROM (SELECT id FROM t1 ) t1sub );
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Suggested fix:
[29 Apr 2016 17:17] Paul Dubois
Posted by developer:
 
Noted in 5.7.13 changelog.

A DELETE from joined tables using a derived table in the WHERE clause
failed if one of the joined tables was used in the derived table.