Bug #84456 column names in metadata appearing as uppercase when selecting from I_S

• Submitted: 10 Jan 2017 11:49
• Modified: 7 Mar 2019 19:06
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0.1
• OS: Any
• 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] MySQL Verification Team

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 2019 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';