Bug #93102 I_S queries don't return column names in the same case as requested.
Submitted: 7 Nov 9:38 Modified: 7 Nov 13:17
Reporter: Simon Mudd (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.12,8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[7 Nov 9:38] Simon Mudd
Description:
I run the following queries on 2 different servers with similar configurations and see the following:

> select @@version, table_name from information_schema.TABLES limit 1;
+-----------+--------------------+
| @@version | TABLE_NAME         |
+-----------+--------------------+
| 8.0.13    | innodb_table_stats |
+-----------+--------------------+
1 row in set (0.00 sec)

vs

> select @@version, table_name from information_schema.TABLES limit 1;
+------------+----------------+
| @@version  | table_name     |
+------------+----------------+
| 5.7.23-log | CHARACTER_SETS |
+------------+----------------+
1 row in set (0.01 sec)

Note: that the column name case does not match the queried case in 8.0.13 (true also in 8.0.12) so code that's looking for the matching column doesn't find it.

Unless there's a good reason to return an upper case column name when a lower case column was requested I think this is a bug.

How to repeat:
See above.

I see references to https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_tab... 

but this does not talk about column aliases which is what I'm seeing.

I also have:

8.0.13:
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

5.7.23: 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

but these settings don't seem to reflect a misconfiguration or an expected difference in behaviour.

Suggested fix:
Please return the column name in the same case as the query requested it.
[7 Nov 11:26] Peter Laursen
SELECT @@version, table_name FROM information_schema.TABLES LIMIT 1;

.. Return the same results in UPPERCASE

1)
@@version   table_name      
----------  ----------------
5.7.24-LOG  CHARACTER_SETS  

2)
@@version  TABLE_NAME      
---------  ----------------
8.0.13     CHARACTER_SETS  

-- Peter
-- not MySQL/Oracle person
[7 Nov 11:42] Simon Mudd
Peter,

You are misunderstanding the problem. It's not the _results_ that are important here but the _column names_.

So note:

> select @@version, TABLE_NAME, table_name from information_Schema.tables limit 1;
+-----------+--------------------+--------------------+
| @@version | TABLE_NAME         | TABLE_NAME         |
+-----------+--------------------+--------------------+
| 8.0.13    | innodb_table_stats | innodb_table_stats |
+-----------+--------------------+--------------------+
1 row in set (0.00 sec)

And check the column _headings_.

5.7 does not do this but does: 

root@transdb-1058 [(none)]> select @@version, table_name, TABLE_NAME from information_Schema.tables limit 1;
+------------+----------------+----------------+
| @@version  | table_name     | TABLE_NAME     |
+------------+----------------+----------------+
| 5.7.22-log | CHARACTER_SETS | CHARACTER_SETS |
+------------+----------------+----------------+
1 row in set (0.00 sec)

So the column names here do not match in 8.0 what was provided as the select fields in the query. In 8.0 for I_S.TABLES at least the resultant column name is always upper case.
[7 Nov 11:47] Peter Laursen
There is an inconsistece between (at least some) new InnoDB_tables added in 8.0 as copared to tables that existed before, it seems.  However SHO statement is not affected:

SHOW VARIABLES LIKE 'lower_case_table_names'; -- 1

SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE 'innodb_table_stats'; -- returns lowercase
SHOW TABLES LIKE 'innodb_tablestats'; -- returns UPPERCASE

SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE 'character_sets'; -- returns UPPERCASE
SHOW TABLES LIKE 'character_sets'; -- returns UPPERCASE
[7 Nov 11:53] Simon Mudd
Peter, that may be the case.  I've not bumped into issues with this yet but the synopsis of this specific bug report is not about data dictionary related changes where table names change case compared to 5.7. If there are inconsistencies I'd suggest you file a separate bug report explaining what you see after you've checked if this is described as an incompatible change between 5.7 and 8.0.
[7 Nov 11:54] Peter Laursen
OK @Simon. :-)

But then I cannot reproduce on Windows with neither current 5.7 nor 8.0. The column I get is 'CHARACTER_SETS' on your query on both versions It may require lower_case_tablenames = 0 then to reproduce? This would make some sense since lowercase characters are sorted before UPPERCASE characters

But I think I discovered an inconsistence that also is a bug (and probably is causing your bug as well). IMO SHOW and SELECT FROM I_S should return the same lettercase in the results.
[7 Nov 11:56] Peter Laursen
I can file a bug, but let us see the reply from the support team here first.
[7 Nov 12:52] Shane Bester
see https://bugs.mysql.com/bug.php?id=84456
[7 Nov 13:03] Simon Mudd
Shane, thanks for the confirmation of the earlier bug reports. (sorry for not checking.)
Clearly as people start to use I_S meta data this will affect them more.
[7 Nov 13:05] Simon Mudd
So probably also best to mark this as a duplicate of the original bug report.
[7 Nov 13:12] Sinisa Milivojevic
Hi,

Thank you for your bug report.

This bug is a duplicate of the bug:

https://bugs.mysql.com/bug.php?id=84456

This means that the priority of the original bug should be increased.
[7 Nov 13:46] Peter Laursen
@Shane .. do you think it would make sense or not for me to file a separate bug?
[7 Nov 13:48] Peter Laursen
I could ask @Sinisa the same ..
[7 Nov 13:55] Sinisa Milivojevic
Peter,

There is truly no need for filing another bug.

This is now a part of the internal process. When anything is changed about this problem, it will be published in the original bug and in the duplicate.