MySQL Bugs: #75532: Join between I_S schema tables is case insensitive/returns wrong values

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

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

Thank you for the report.

Verified as described.

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