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

• Submitted: 5 Sep 2008 9:59
• Modified: 12 Nov 2009 19:21
• Reporter: Sergei Glukhov
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[5 Sep 2008 9:59] Sergei Glukhov

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.

[5 Dec 2008 12:41] Bugs System

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.

[11 Dec 2008 19:46] Sergey Petrunya

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

[17 Dec 2008 16:16] Bugs System

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.

[20 Jan 2009 18:54] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090119171328-2hemf2ndc1dxl0et) (version source revid:[e-mail]-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

[29 Jan 2009 21:04] Paul DuBois

Noted in 6.0.10 changelog.

INFORMATION_SCHEMA access optimizations did not work properly in some
cases.

[23 Oct 2009 8:01] Bugs System

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

[31 Oct 2009 8:20] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091031081410-qkxmjsdzjmj840aq) (version source revid:[e-mail]-20091023144402-05x9kn8fz878z011) (merge vers: 6.0.14-alpha) (pib:13)

[31 Oct 2009 17:49] Paul DuBois

Already fixed in earlier 6.0.x.

Setting report to NDI pending push to 5.5.x.

[12 Nov 2009 8:17] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091110093229-0bh5hix780cyeicl) (version source revid:[e-mail]-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)

[12 Nov 2009 19:21] Paul DuBois

Noted in 5.5.0 changelog.