Bug #89343 SHOW TABLES LIKE doesn't show INFORMATION_SCHEMA tables when searched lowercase
Submitted: 22 Jan 2018 11:59 Modified: 22 Jan 2018 12:16
Reporter: Arthur Tokarchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.20, 5.6.39, 5.7.21 OS:Any
Assigned to: CPU Architecture:Any

[22 Jan 2018 11:59] Arthur Tokarchuk
Description:
Hi!

https://dev.mysql.com/doc/refman/5.7/en/charset-collation-information-schema.html

It's said in documentation that 
"Searches in INFORMATION_SCHEMA string columns for values that refer to INFORMATION_SCHEMA itself do use the utf8_general_ci collation because INFORMATION_SCHEMA is a “virtual” database not represented in the file system."

That is true for the schema itself, but doesn't work for tables contained in it.

mysql> show tables in information_schema like '%ROUT%';
+---------------------------------------+
| Tables_in_information_schema (%ROUT%) |
+---------------------------------------+
| ROUTINES                              |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> show tables in information_schema like '%rout%';
Empty set (0.00 sec)

Although if an ordinary select on information_schema.tables works fine (and this doesn't work for ordinary tables, that follow lower_case_table_names setting rules)

mysql> select table_name from information_schema.tables where table_schema = 'information_schema' and table_name like '%rout%';
+------------+
| table_name |
+------------+
| ROUTINES   |
+------------+
1 row in set (0.00 sec)

mysql> select table_name from information_schema.tables where table_schema = 'information_schema' and table_name like '%ROUT%';
+------------+
| table_name |
+------------+
| ROUTINES   |
+------------+
1 row in set (0.00 sec)

mysql> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

How to repeat:
show tables in information_schema like '%ROUT%';
show tables in information_schema like '%rout%';
[22 Jan 2018 12:16] Umesh Shastry
Hello Arthur Tokarchuk,

Thank you for the report.

Thanks,
Umesh