MySQL INFORMATION_SCHEMA Subquery Bug

I found a bug today in MySQL 5.6. I hope this saves someone some time…
I’d been trying to select columns names from a table, so that I could check that the columns existed before using them 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 wasn’t 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 I ran them as a single query that no results came back.
Eventually, I stumbled across this MySQL bug report.
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.
Follow us on Twitter at @MaydenDev and let us know if this post helped you!