Bug #41426 I_S optimization algorithm can not extract lookup values in some cases.

• Submitted: 12 Dec 2008 11:25
• Reporter: Sergei Glukhov
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.1, 6.0
• OS: Any
• 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.