Bug #75532 Join between I_S schema tables is case insensitive/returns wrong values
Submitted: 16 Jan 2015 17:24 Modified: 13 Oct 2016 16:25
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.5.41, 5.6.21, 5.5.42, 5.6.23, 5.7.6 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[16 Jan 2015 17:24] Justin Swanhart
Description:
Generic = (equality) comparison of the TABLES.table_name or COLUMNS.table_name columns works in a case /sensitive/ manner.

When join TABLES to COLUMNS, however, somehow the comparison becomes (in part) case insensitive, and an extra row is returned.  

lower_case_file_system=OFF
lower_case_table_names=0

How to repeat:
mysql> create database miguel;
Query OK, 1 row affected (0.00 sec)

mysql> use miguel;
Database changed
mysql> create table foo_a (a int);                                              Query OK, 0 rows affected (0.02 sec)

mysql> create table foo_A (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select TABLE_NAME from information_schema.tables where table_name='foo_A'and table_schema='miguel';
+------------+
| TABLE_NAME |
+------------+
| foo_A      |
+------------+
1 row in set (0.00 sec)

mysql> select TABLE_NAME from information_schema.columns where table_name='foo_A'and table_schema='miguel';
+------------+
| TABLE_NAME |
+------------+
| foo_A      |
+------------+
1 row in set (0.00 sec)

mysql> select t.table_name, c.table_name from information_schema.tables t join information_schema.columns c on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME and t.TABLE_SCHEMA='miguel' and c.TABLE_SCHEMA='miguel' and t.table_name='foo_A' and c.table_name='foo_A';
+------------+------------+
| table_name | table_name |
+------------+------------+
| foo_A      | foo_A      |
| foo_A      | foo_a      |
+------------+------------+
2 rows in set (0.04 sec)

The second row SHOULD NOT BE THERE.

Suggested fix:
unknown
[16 Jan 2015 18:57] Sveta Smirnova
Thank you for the report.

Verified as described.
[13 Oct 2016 16:25] Paul Dubois
Posted by developer:
 
Noted in 8.0.0 changelog.

For comparisons of database or table names in INFORMATION_SCHEMA
queries, using COLLATE to force a given collation worked only if
applied to the INFORMATION_SCHEMA table column, but not if applied to
the comparison value.

For additional information about performing such comparisons, see
http://dev.mysql.com/doc/refman/8.0/en/charset-collation-information-schema.html