I found a MySQL 5.6 bug

I setup a MySQL 5.6 instance and immediately granted table level permissions to a user. I then ran my audit query to confirm that the permissions were added. The query returned zero results. I thought maybe I had done something wrong. I confirmed that the permissions were actually there.

Here is an example of the table level portion of the audit query:

SELECT -- the list of privileges for tables
mt.host `host`,
mt.user `user`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)

The query has a SUB SELECT that checks to make sure tables with the name of grant actually exist. For some reason doing a sub-select on the information schema does not work on MySQL 5.6. There is a bug already reported for this:

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

The work around is to run this:

set optimizer_switch='semijoin=off';