Bug #41426 I_S optimization algorithm can not extract lookup values in some cases.
Submitted: 12 Dec 2008 11:25
Reporter: Sergei Glukhov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any

[12 Dec 2008 11:25] Sergei Glukhov
Description:
I_S optimization algorithm can not extract lookup values in some cases. 

How to repeat:
execute following query(mysqld with debug option enabled):
SELECT constraint_name, table_name, column_name, referenced_table_name,
referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND referenced_table_schema = table_schema;

and check master.trace file:
you will find the following line:
INDEX VALUES: db_name='<nil>', table_name='<nil>'
It means that lookup values are not used.
but it should be
INDEX VALUES: db_name='test', table_name='<nil>'

if you interchange referenced_table_schema and table_schema the the query works
as it should be i.e.

SELECT constraint_name, table_name, column_name, referenced_table_name,
referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND table_schema = referenced_table_schema;

works fine.