MySQL Bugs: #93765: Information_schema Stores values in KEY_COLUMN

Bug #93765	Information_schema Stores values in KEY_COLUMN_USAGE in the wrong case.
Submitted:	29 Dec 2018 9:36	Modified:	30 Dec 2018 17:47
Reporter:	dbForge Team	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
We rely on the fact that information_schema.KEY_COLUMN_USAGE saves values case sensitive

How to repeat:
CREATE DATABASE db1;

CREATE TABLE db1.Persons (
  ID int NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE db1.Orders (
    PersonID int,
    FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

SELECT REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = "Orders" 
  AND CONSTRAINT_SCHEMA = "db1"
  AND COLUMN_NAME = "PersonID"

Expected:
KEY_COLUMN_USAGE must contain "ID"

Actual:
KEY_COLUMN_USAGE contains "id"

Suggested fix:
information_schema.KEY_COLUMN_USAGE should save table fields without changing their register

This case should be just Windows since is a case insensitive OS below on Linux:
miguel@luz:~/dbs $ ./80c
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

Copyright (c) 2000, 2019, 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 8.0 > CREATE DATABASE db1;
Query OK, 1 row affected (0,59 sec)

mysql 8.0 > CREATE TABLE db1.Persons (
    ->   ID int NOT NULL,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (1,52 sec)

mysql 8.0 > CREATE TABLE db1.Orders (
    ->     PersonID int,
    ->     FOREIGN KEY (PersonID) REFERENCES Persons(ID)
    -> );
Query OK, 0 rows affected (1,81 sec)

mysql 8.0 > SELECT REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE
    -> WHERE TABLE_NAME = "Orders"
    ->   AND CONSTRAINT_SCHEMA = "db1"
    ->   AND COLUMN_NAME = "PersonID";
+------------------------+
| REFERENCED_COLUMN_NAME |
+------------------------+
| ID                     |
+------------------------+
1 row in set (0,06 sec)

mysql 8.0 >

Thank you for the bug report.

C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

Copyright (c) 2000, 2019, 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 8.0 > SHOW VARIABLES LIKE "%lower_case_table_name%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql 8.0 > CREATE DATABASE db1;
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > CREATE TABLE db1.Persons (
    ->   ID int NOT NULL,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql 8.0 > CREATE TABLE db1.Orders (
    ->     PersonID int,
    ->     FOREIGN KEY (PersonID) REFERENCES Persons(ID)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > SELECT REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE
    -> WHERE TABLE_NAME = "Orders"
    ->   AND CONSTRAINT_SCHEMA = "db1"
    ->   AND COLUMN_NAME = "PersonID";
+------------------------+
| REFERENCED_COLUMN_NAME |
+------------------------+
| ID                     |
+------------------------+
1 row in set (0.01 sec)

mysql 8.0 > SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| immediate_server_version | 999999                                 |
| innodb_version           | 8.0.15                                 |
| original_server_version  | 999999                                 |
| protocol_version         | 10                                     |
| slave_type_conversions   |                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2                  |
| version                  | 8.0.15                                 |
| version_comment          | Source distribution BUILD: 2018-DEC-11 |
| version_compile_machine  | x86_64                                 |
| version_compile_os       | Win64                                  |
| version_compile_zlib     | 1.2.11                                 |
+--------------------------+----------------------------------------+
11 rows in set (0.00 sec)

mysql 8.0 >

Duplicate of bug https://bugs.mysql.com/bug.php?id=88718.