Bug #39270 I_S optimization algorithm does not work properly in some cases

Description:
There are two problems:
1. I_S optimization algorithm can not take lookup values in some cases.
2. Explain returns incorrect information for some I_S tables
   KEY_COLUMN_USAGE, PARTITIONS, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS,
   TRIGGERS

How to repeat:
1.
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.

2.
check result difference between following queries:
explain select * from information_schema.key_column_usage where table_schema = SCHEMA();
explain select * from information_schema.tables where table_schema = SCHEMA();

'from key_column_usage' Extra field has scanty information.

Suggested fix:
Problem#1:
don't know yet
Problem#2:
set OPTIMIZE_I_S_TABLE for these tables,
see ST_SCHEMA_TABLE schema_tables[] array.

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/60732

2725 Sergey Glukhov	2008-12-05
      Bug#39270 I_S optimization algorithm does not work properly in some cases
      I_S  KEY_COLUMN_USAGE, PARTITIONS, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, TRIGGERS tables:
      set OPTIMIZE_I_S_TABLE flag to make them EXPLAIN visible.

Approved as patch to fix problem #2. We've previously agreed with Sergey Gluhov that problem #1 will be branched off to another bug.

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/61880

2817 Sergey Glukhov	2008-12-17
      Bug#39270 I_S optimization algorithm does not work properly in some cases
      I_S  KEY_COLUMN_USAGE, PARTITIONS,
      REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, TRIGGERS tables:
      set OPTIMIZE_I_S_TABLE flag to make them EXPLAIN visible.

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 6.0.10 changelog.

INFORMATION_SCHEMA access optimizations did not work properly in some
cases.

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/87902

2916 Sergey Glukhov	2009-10-23
      Bug#39270 I_S optimization algorithm does not work properly in some cases
      backport to Betony
     @ mysql-test/r/information_schema.result
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony
     @ mysql-test/t/information_schema.test
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony
     @ sql/sql_show.cc
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:lars-erik.bjork@sun.com-20091023144402-05x9kn8fz878z011) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in earlier 6.0.x.

Setting report to NDI pending push to 5.5.x.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.