I found a bug today in MySQL 5.6, I hope this saves someone some time:

I’ve been trying to select columns names from a table, for a PHP class to check they exist and use elsewhere. To do this I built a sub query that selects results from the INFORMATION_SCHEMA database to return a subset of the total results. Initially, I was not getting any results back from the query.

I ran both the inner and outer queries separately, and both returned the expected results, it was only when they were ran as a single query that no results came back.

Eventually, I stumbled across this MySQL bug report:

https://bugs.mysql.com/bug.php?id=77191

The bug is in MySQL’s optimiser, and it prevents the use of the INFORMATION_SCHEMA database in a subquery.

The fix is to turn off semi-join transformations in the optimiser:

set optimizer_switch='semijoin=off';

Joining the a table in the INFORMATION_SCHEMA database still works as expected, but unfortunately the database isn’t indexed, so any queries that join it will take much longer to run.