Bug #99294 INFORMATION_SCHEMA.TABLES always returns capitalized column name in result set

• Submitted: 18 Apr 2020 2:11
• Modified: 23 Apr 2020 17:29
• Reporter: Herman Lee
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0.17
• OS: Any
• CPU Architecture: Any

[18 Apr 2020 2:11] Herman Lee

Description:
When querying from information_schema.table, the column names of the resultset always seems to be capitalized, regardless of the case of the fieldname specified. Other views/tables in information_schema do not seem to behave the same way.

For example:

SELECT taBle_nAmE from information_schema.tables limit 1;

returns 'TABLE_NAME' as the column name, but 

SELECT taBle_nAmE from information_schema.table_constraints limit 1;

returns 'taBle_nAmE', which is the expected result.

The case causes problems for certain connectors like python which may try to access the field of the resultset array using the key 'taBle_nAmE'. It leads to a key not found error because the resultset key is actually is 'TABLE_NAME', which is unexpected given the user's query.

How to repeat:
SELECT taBle_nAmE from information_schema.tables limit 1;
TABLE_NAME
innodb_table_stats

SELECT taBle_nAmE from information_schema.table_constraints limit 1;
taBle_nAmE
innodb_table_stats

[18 Apr 2020 8:54] MySQL Verification Team

It's actually inconsistent,  even though using alias can be a workaround,  so I'm verifying it.

mysql>
mysql> SELECT taBle_nAmE from information_schema.tables limit 1;
+-------------------+
| TABLE_NAME        |
+-------------------+
| inventory_country |
+-------------------+
1 row in set (0.01 sec)

mysql>
mysql> SELECT taBle_nAmE from information_schema.table_constraints limit 1;
+--------------------+
| taBle_nAmE         |
+--------------------+
| innodb_table_stats |
+--------------------+
1 row in set (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 8.0.19-commercial |
+-------------------+
1 row in set (0.00 sec)

[23 Apr 2020 17:29] Herman Lee

This also seems to affect the information_schema.columns table too.

[28 Apr 2020 7:13] Gopal Shankar

Posted by developer:
 
Please note that, with the alias 'AS Table_Name', we do see consistent behavior in 8.0.19.

SELECT Table_Name AS Table_Name FROM information_schema.TABLES LIMIT 1;
+--------------------+
| Table_Name         |
+--------------------+
| innodb_table_stats |
+--------------------+

SELECT Table_Name AS Table_Name FROM information_schema.TABLE_CONSTRAINTS LIMIT 1;
+--------------------+
| Table_Name         |
+--------------------+
| innodb_table_stats |
+--------------------+

SELECT Table_Name AS Table_Name FROM information_schema.COLUMNS LIMIT 1;
+--------------------+
| Table_Name         |
+--------------------+
| innodb_table_stats |
+--------------------+

This behavior is same for most of I_S tables in 8.0.
We request to please report example case if the use of
'AS <alias>' too does not help.