[Milhouse]

@pnkiller78: I don't have a 5.7.x installation, so can you try the following query:

Code:

DELETE FROM path
WHERE (strContent IS NULL OR strContent = '') AND
      (strSettings IS NULL OR strSettings = '') AND
      (strHash IS NULL OR strHash = '') AND
      (exclude IS NULL OR exclude != 1) AND
      (idParentPath IS NULL OR NOT EXISTS (SELECT 1 FROM (SELECT idPath FROM path) as parentPath
                                                    WHERE parentPath.idPath = path.idParentPath)) AND
      NOT EXISTS (SELECT 1 FROM files
                  WHERE files.idPath = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM tvshowlinkpath
                  WHERE tvshowlinkpath.idPath = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM movie
                  WHERE movie.c23 = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM episode
                  WHERE episode.c19 = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM musicvideo
                  WHERE musicvideo.c14 = path.idPath)

You should get the 1093 error.

Now try:

Code:

SET optimizer_switch = 'derived_merge=off';

and repeat the first query - does it work or continue to fail?

Modifying the optimizer_switch to restore pre-5.7.6 compatibility on a per-session basis might be the easiest solution...

Edit: Or could you try modifying the query to use "SELECT DISTINCT" as it mentions in the linked query optimizer article (just make sure you restore the default optimize_switch!)

(Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization.)

However we don't know what other queries will also need changing, in which case restoring pre-5.7.6 compatibility might still be the best option.

The thing is that MySQL isn't a long term strategic solution, so spending time rewriting and testing existing code that works perfectly well with our strategic solution (SQLite) is becoming less of an option. Quick and dirty might have to suffice in order to keep MySQL limping along, or alternatively users will just have to stick with pre-5.7.x servers.