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:
