2017-07-25, 08:21
(2017-07-25, 07:17)jingai Wrote: The query doesn't fail without it on MySQL 5.7.18, though. It might be worth not setting the option if >= 5.7.18 (or whatever version 'fixed' this).
Your SELECT query won't fail due to having derived_merge=on, but queries that DELETE from a table which is used in a subquery will fail with MySQL 5.7.6+ when derived_merge is ON (for example when cleaning the database, see the DELETE query in this post).
If the delete-from-a-table-in-a-subquery restriction is no longer an issue in MySQL 5.7.18 then maybe derived_merge=off could be applied conditionally, but I think it unlikely that anything will have changed for the better in MySQL.
Rewriting all the affected queries to avoid deleting from tables used in a subquery is the only other option, and that just isn't going to happen IMHO.