MySQL Bugs: #37070: I_S.key_column

Bug #37070	I_S.key_column_usage corruption
Submitted:	29 May 2008 13:48	Modified:	30 May 2008 9:37
Reporter:	Claude Martin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.51a-community/5.1/6.0	OS:	Linux
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:

Creating a unique index on a table already having primary key, corrupts the
table information_schema.key_column_usage.

The example in 'how to repeat' seems minimal (modulo the datatypes).

Server is on Red Hat 5 (x86-64), client is on Solaris 8.

How to repeat:
mysql> create table ATABLE(F1 decimal(5,2) primary key, F2 char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> select t.constraint_name, t.constraint_type,       k.column_name, ordinal_position from information_schema.table_constraints t,      information_schema.key_column_usage k  where k.table_name = 'ATABLE'       and k.table_schema ='test'       and t.table_name = k.table_name       and t.table_schema = k.table_schema order by ordinal_position;
+-----------------+-----------------+-------------+------------------+
| constraint_name | constraint_type | column_name | ordinal_position |
+-----------------+-----------------+-------------+------------------+
| PRIMARY         | PRIMARY KEY     | F1          |                1 |
+-----------------+-----------------+-------------+------------------+
1 row in set (0.00 sec)

mysql> create unique index f1ind on ATABLE (F2);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select t.constraint_name, t.constraint_type,       k.column_name, ordinal_position from information_schema.table_constraints t,      information_schema.key_column_usage k  where k.table_name = 'ATABLE'       and k.table_schema ='test'       and t.table_name = k.table_name       and t.table_schema = k.table_schema order by ordinal_position;
+-----------------+-----------------+-------------+------------------+
| constraint_name | constraint_type | column_name | ordinal_position |
+-----------------+-----------------+-------------+------------------+
| PRIMARY         | PRIMARY KEY     | F2          |                1 |
| f1ind           | UNIQUE          | F2          |                1 |
| PRIMARY         | PRIMARY KEY     | F1          |                1 |
| f1ind           | UNIQUE          | F1          |                1 |
+-----------------+-----------------+-------------+------------------+
4 rows in set (0.00 sec)

Thank you for the bug report. Verified as described.

It's not a bug, see example below

create table ATABLE(F1 decimal(5,2) primary key, F2 char(20));
create unique index f1ind on ATABLE (F2);

select table_schema, table_name, constraint_name, constraint_type
from information_schema.table_constraints where table_name= 'ATABLE';
table_schema   table_name      constraint_name constraint_type
test   ATABLE  PRIMARY PRIMARY KEY
test   ATABLE  f1ind   UNIQUE
--------
This result is correct.

select table_schema, table_name, constraint_name, column_name, ordinal_position
from information_schema.key_column_usage where table_name= 'ATABLE';
table_schema   table_name      constraint_name column_name     ordinal_position
test   ATABLE  PRIMARY F1      1
test   ATABLE  f1ind   F2      1
--------
This result is correct too. 
See also http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html
The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

So result of select from this bug report is correct too.