MySQL Bugs: #74999: Inconsistent data in KEY_COLUMN_USAGE.COLUMN

Bug #74999	Inconsistent data in KEY_COLUMN_USAGE.COLUMN_NAME
Submitted:	25 Nov 2014 18:57	Modified:	25 Nov 2014 21:49
Reporter:	Vasso Vasso	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	case, column_name, KEY_COLUMN_USAGE

Description:
Column name stored in INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME might be in another case than the actual column name.

How to repeat:
CREATE TABLE test (id INT, fk INT NULL, PRIMARY KEY (`id`));
ALTER TABLE test ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES test (id);
ALTER TABLE test CHANGE COLUMN fk FK INT;

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'test' AND CONSTRAINT_NAME = 'fk';

Actual result: 'fk'

Suggested fix:
Expected result: 'FK'

Repeatable on 5.5.40 binary distribution but not on 5.6 source:

[miguel@andes mysql-5.5.40-linux2.6-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test (id INT, fk INT NULL, PRIMARY KEY (`id`));
 (fk) REFERENCES test (id);
ALTER TABLE test CHANGE COLUMN fk FK INT;

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'test' AND CONSTRAINT_NAME = 'fk';Query OK, 0 rows affected (0.65 sec)

mysql> ALTER TABLE test ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES test (id);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test CHANGE COLUMN fk FK INT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'test' AND CONSTRAINT_NAME = 'fk';
+-------------+
| COLUMN_NAME |
+-------------+
| fk          |
+-------------+
1 row in set (0.00 sec)

[miguel@andes dbs]$ 5.6/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test (id INT, fk INT NULL, PRIMARY KEY (`id`));
ABLE test ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES test (id);
ALTER TABLE test CHANGE COLUMN fk FK INT;

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'test' AND CONSTRAINT_NAME = 'fk';Query OK, 0 rows affected (0.84 sec)

mysql> ALTER TABLE test ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES test (id);
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test CHANGE COLUMN fk FK INT;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'test' AND CONSTRAINT_NAME = 'fk';
+-------------+
| COLUMN_NAME |
+-------------+
| FK          |
+-------------+
1 row in set (0.01 sec)

mysql>