Bug #23299 Some queries against INFORMATION_SCHEMA with subqueries fail

Description:
mysql> use information_schema; select TABLE_NAME, TABLE_TYPE from TABLES where TABLE_SCHEMA = 'mysqltest' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (select TABLE_NAME from COLUMNS where TABLE_SCHEMA = 'mysqltest' and COLUMN_NAME = 'pid');
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
ERROR 1194 (HY000): Table 'COLUMNS' is marked as crashed and should be repaired

The COLUMNS table is not actually crashed at this point as I can still select from it normally, in addition:

mysql> repair table COLUMNS;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

Which is probably intended, although perhaps a more telling error message would be appropriate.

This problem has been observed on both x86_64 and x86 machines.

The attached dump has only two tables, the original database had 54 tables which were dropped one by one in reverse alphabetacal order until the query worked. After that, I removed as many fields from the tables as possible. Removing the second field from the second table seems to make the problem go away.

How to repeat:
If you have a database called `mysqltest`, drop it.

Import the dump:
mysql -u root -p <mysqlbug.dump

Log into the MySQL monitor:
mysql -u root -p

Execute these queries:
use information_schema;
select TABLE_NAME, TABLE_TYPE from TABLES where TABLE_SCHEMA = 'mysqltest' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (select TABLE_NAME from COLUMNS where TABLE_SCHEMA = 'mysqltest' and COLUMN_NAME = 'pid');

Observe the failed query.

Note: The name of the database doesn't seem to matter, and I am only referring to it as `mysqltest` for convenience.

Suggested fix:
The query presented above should not fail and should return the expected results.

Database schema dump

Attachment: mysqlbug.dump (text/plain), 1.68 KiB.

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16818

ChangeSet@1.2327, 2006-12-12 13:49:27+04:00, gluh@mysql.com +3 -0
  Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
  For subqueries we should clear MI_INFO state
  which can be changed during execution of 
  init_read_record function.
  HA_EXTRA_RESET_STATE is replaced with HA_EXTRA_RESET.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19011

ChangeSet@1.2390, 2007-01-30 18:46:06+04:00, gluh@mysql.com +3 -0
  Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
  additional call of file->extra() method with HA_EXTRA_NO_CACHE parameter

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19190

ChangeSet@1.2399, 2007-02-01 19:12:45+04:00, gluh@mysql.com +3 -0
  Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
  additional call of file->extra() method with HA_EXTRA_NO_CACHE parameter

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

Some queries against INFORMATION_SCHEMA that used subqueries failed.