MySQL Bugs: #76259: derived_merge=on causes ERROR 1093 (HY000): You can't specify target table

Bug #76259	derived_merge=on causes ERROR 1093 (HY000): You can't specify target table
Submitted:	11 Mar 2015 12:49	Modified:	24 Mar 2015 21:18
Reporter:	Yasuo Honda	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.6-m16 MySQL Community Server (GPL)	OS:	Linux (CentOS Linux release 7.0.1406 (Core))
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Testing Rails ActiveRecord unit test using MySQL 5.7.6-m16, which causes You can't specify target table 'developers' for update in FROM clause: error.

These tests have been successfully running using previous version of MySQL 5.5.

How to repeat:

drop DATABASE activerecord_unittest;
create DATABASE activerecord_unittest DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
use activerecord_unittest;
DROP TABLE developers;
CREATE TABLE developers (id int(11) AUTO_INCREMENT PRIMARY KEY, name varchar(255), salary int(11) DEFAULT 70000, created_at datetime, updated_at datetime, created_on datetime, updat
ed_on datetime) ENGINE=InnoDB;
DROP TABLE developers_projects;
CREATE TABLE developers_projects (developer_id int(11) NOT NULL, project_id int(11) NOT NULL, joined_on date, access_level int(11) DEFAULT 1) ENGINE=InnoDB;
DROP TABLE projects;
CREATE TABLE projects (id int(11) AUTO_INCREMENT PRIMARY KEY, name varchar(255), type varchar(255)) ENGINE=InnoDB;
INSERT INTO computers_developers (developer_id, computer_id) VALUES (1, 536958526);
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (1, 'David', 80000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:
19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (2, 'Jamis', 150000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12
:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (3, 'fixture_3', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (4, 'fixture_4', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (5, 'fixture_5', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (6, 'fixture_6', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (7, 'fixture_7', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (8, 'fixture_8', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (9, 'fixture_9', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 1
2:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (10, 'fixture_10', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11
 12:12:19','2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (11, 'Jamis', 9000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:
19', '2015-03-11 12:12:19');
INSERT INTO projects (id, name) VALUES (2, 'Active Controller');
INSERT INTO projects (id, name) VALUES (1, 'Active Record');
INSERT INTO developers_projects (developer_id, project_id, joined_on) VALUES (1, 2, '2004-10-10');
INSERT INTO developers_projects (developer_id, project_id, joined_on) VALUES (1, 1, '2004-10-10');
INSERT INTO developers_projects (developer_id, project_id) VALUES (2, 1);
INSERT INTO developers_projects (developer_id, project_id) VALUES (11, 1);
SELECT  developers.* FROM developers WHERE developers.id = 1 LIMIT 1;
SELECT developers.* FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.id INNER JOIN projects ON projects.id = developers_projects.proje
ct_id WHERE projects.name = 'Active Controller';
SELECT @@optimizer_switch\G
DELETE FROM developers WHERE developers.id IN (SELECT id FROM (SELECT developers.id FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.i
d INNER JOIN projects ON projects.id =  developers_projects.project_id WHERE projects.name = 'Active Controller') __active_record_temp);

Suggested fix:
Workaround is setting this parameter at /etc/my.cnf

optimizer_switch=derived_merge=off

I can reproduce this as well.  I've made a few changes to your testcase:

DROP DATABASE IF EXISTS bug76259;
CREATE DATABASE bug76259;
USE bug76259;

CREATE TABLE developers (id int(11) AUTO_INCREMENT PRIMARY KEY, name varchar(255), salary int(11) DEFAULT 70000, created_at datetime, updated_at datetime, created_on datetime, updated_on datetime) ENGINE=InnoDB;
CREATE TABLE developers_projects (developer_id int(11) NOT NULL, project_id int(11) NOT NULL, joined_on date, access_level int(11) DEFAULT 1) ENGINE=InnoDB;
CREATE TABLE projects (id int(11) AUTO_INCREMENT PRIMARY KEY, name varchar(255), type varchar(255)) ENGINE=InnoDB;

INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (1, 'David', 80000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (2, 'Jamis', 150000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (3, 'fixture_3', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (4, 'fixture_4', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (5, 'fixture_5', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (6, 'fixture_6', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (7, 'fixture_7', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (8, 'fixture_8', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (9, 'fixture_9', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (10, 'fixture_10', 100000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19','2015-03-11 12:12:19');
INSERT INTO developers (id, name, salary, created_at, created_on, updated_at, updated_on) VALUES (11, 'Jamis', 9000, '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19', '2015-03-11 12:12:19');
INSERT INTO projects (id, name) VALUES (2, 'Active Controller');
INSERT INTO projects (id, name) VALUES (1, 'Active Record');
INSERT INTO developers_projects (developer_id, project_id, joined_on) VALUES (1, 2, '2004-10-10');
INSERT INTO developers_projects (developer_id, project_id, joined_on) VALUES (1, 1, '2004-10-10');
INSERT INTO developers_projects (developer_id, project_id) VALUES (2, 1);
INSERT INTO developers_projects (developer_id, project_id) VALUES (11, 1);

-- This statement fails with derived_merge=on (default):
SET optimizer_switch='derived_merge=on';
DELETE FROM developers WHERE developers.id IN (SELECT id FROM (SELECT developers.id FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.id INNER JOIN projects ON projects.id =  developers_projects.project_id WHERE projects.name = 'Active Controller') __active_record_temp);

-- Disabling derived_merge, the statement passes:
SET optimizer_switch='derived_merge=off';
DELETE FROM developers WHERE developers.id IN (SELECT id FROM (SELECT developers.id FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.id INNER JOIN projects ON projects.id =  developers_projects.project_id WHERE projects.name = 'Active Controller') __active_record_temp);

Outputs:

ysql> DELETE FROM developers WHERE developers.id IN (SELECT id FROM (SELECT developers.id FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.id INNER JOIN projects ON projects.id =  developers_projects.project_id WHERE projects.name = 'Active Controller') __active_record_temp);
ERROR 1093 (HY000): You can't specify target table 'developers' for update in FROM clause
mysql> SET optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM developers WHERE developers.id IN (SELECT id FROM (SELECT developers.id FROM developers INNER JOIN developers_projects ON developers_projects.developer_id = developers.id INNER JOIN projects ON projects.id =  developers_projects.project_id WHERE projects.name = 'Active Controller') __active_record_temp);
Query OK, 1 row affected (0.01 sec)

This is not a bug: In 5.7 we have enhanced processing of derived tables so that they are handled more similar to how views are handled.

In 5.6, you may get a similar error condition if you replace the derived table with a view:

CREATE VIEW v AS
SELECT developers.id
FROM developers INNER JOIN developers_projects
          ON developers_projects.developer_id = developers.id
          INNER JOIN projects ON projects.id =  developers_projects.project_id
WHERE projects.name = 'Active Controller';

DELETE FROM developers WHERE developers.id IN (SELECT id FROM v);

An error is given because the DELETE statement accesses a table that is also deleted from, and is merged into the DELETE statement.

However, if you replace the view v with a materialized view (ALGORITHM=TEMPTABLE), this query works.

Possible workarounds:

- Use optimizer_switch='derived_merge=off';

- Force the derived table to be materialized, e.g by adding DISTINCT after SELECT, or adding a LIMIT.

Thanks Roy!  I am going to change it to a documentation bug.  It's not on http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html , and it should be mentioned for the case of upgrades.

Thanks for providing workarounds. I understand this is an expected behavior in MySQL 5.7.6-m13 and later version of MySQL.

Then it would be appreciated if this behavior is updated in the release notes and/or update manuals.

Thanks,

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Added information at:
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
under "SQL Changes.'

For reference, the documentation is to be found by looking for "optimizer_switch in" http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html