MySQL Bugs: #93102: I_S queries don't return column names in the same case as requested.

Bug #93102	I_S queries don't return column names in the same case as requested.
Submitted:	7 Nov 2018 9:38	Modified:	7 Nov 2018 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

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.

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

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.

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

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.

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.

I can file a bug, but let us see the reply from the support team here first.

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

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.

So probably also best to mark this as a duplicate of the original bug report.

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.

@Shane .. do you think it would make sense or not for me to file a separate bug?

I could ask @Sinisa the same ..

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.