Bug #84456 column names in metadata appearing as uppercase when selecting from I_S
Submitted: 10 Jan 2017 11:49 Modified: 7 Mar 19:06
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[10 Jan 2017 11:49] Shane Bester
Description:
Directly affects 8.0 since the information_schema queries now use views.
But underlying problem seems to affect 5.1, 5.5, 5.6, 5.7, 8.0.

Compare the 5.7 and 8.0 outputs:

mysql> select table_schema, table_name from information_schema.tables where table_name = 'users';
+--------------------+------------+
| TABLE_SCHEMA       | TABLE_NAME |
+--------------------+------------+
| performance_schema | users      |
+--------------------+------------+
1 row in set (0.01 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 8.0.1-dmr-debug |
+-----------------+
1 row in set (0.00 sec)

---

mysql> select table_schema, table_name from information_schema.tables where table_name = 'users';
+--------------------+------------+
| table_schema       | table_name |
+--------------------+------------+
| performance_schema | users      |
+--------------------+------------+
1 row in set (0.04 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.17-debug |
+--------------+
1 row in set (0.15 sec)

It seems to be an issue with temptable views, as second testcase shows.

mysql> create algorithm=merge     view v as select A from t;
Query OK, 0 rows affected (0.00 sec)

mysql> create algorithm=temptable view e as select A from t;
Query OK, 0 rows affected (0.00 sec)

mysql> select version(),a from `v`;
+-----------+------+
| version() | A    |
+-----------+------+
| 5.7.17    |    1 |
+-----------+------+
1 row in set (0.00 sec)

mysql> select version(),a from `e`;
+-----------+------+
| version() | a    |
+-----------+------+
| 5.7.17    |    1 |
+-----------+------+
1 row in set (0.00 sec) 

How to repeat:
select table_schema, table_name from 
information_schema.tables where table_name = 'users';

And - 

drop table if exists t,v,e;
drop view if exists t,v,e;
create table t(a int)engine=innodb;
insert into t values(1);
create algorithm=merge     view v as select A from t;
create algorithm=temptable view e as select A from t;
select version(),a from `v`;
select version(),a from `e`;

Suggested fix:
workaround:  use column alias, for example:

select table_schema as table_schema, table_name as table_name 
from information_schema.tables where table_name = 'users';
[15 Apr 2017 13:43] Shane Bester
https://bugs.mysql.com/bug.php?id=85947
[11 Jul 2017 11:33] Erlend Dahl
Bug#85947 I_S column name is upper case and breaks app compatibility

was marked as a duplicate
[15 Nov 2017 18:07] Erlend Dahl
Posted by developer:

[11 Sep 2017 7:33] Gopal Shankar 

The decision is to use the workaround as stated in 'Suggested Fix:' section
in the bug report.
[7 Nov 2018 13:04] Simon Mudd
Duplicate: https://bugs.mysql.com/bug.php?id=93102.
[7 Mar 19:06] Paul Dubois
Posted by developer:
 
Revised https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-usage-differences.html to add:

Queries on INFORMATION_SCHEMA tables may return column names in a
different lettercase than in earlier MySQL series. Applications
should test result set column names in case-insensitive fashion. If
that is not feasible, a workaround is to use column aliases in the
select list that return column names in the required lettercase. For
example:

SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';