Bug #48445 inconsistency wth SHOW and SELECT FROM I_S
Submitted: 30 Oct 2009 20:28 Modified: 11 Nov 2016 16:28
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0+, 5.1, 5.4.4 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Triaged: D4 (Minor)

[30 Oct 2009 20:28] Peter Laursen
Description:
This is a 'spin-off' from http://bugs.mysql.com/48415

Here Chuck's observation about case insensitivity of I_S.triggers table applies to all I_S tables (.tables and .schemata) containing information about database objects that are case sensitive on some platforms.  

How to repeat:
-- 5.1.36 on RHEL5 linux

CREATE DATABASE somedb;

USE somedb;

CREATE TABLE `t1` (
  `id` INT(11) DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

CREATE TABLE `T1` (
  `id` INT(11) DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=latin1

SHOW TABLES FROM somedb LIKE 't%';
/*
Tables_in_somedb (t%)
--------------------
t1                  
*/

SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE 't%' AND TABLE_SCHEMA = 'somedb';
/*
table_name
----------
T1        
t1        
*/

Suggested fix:
For consistency between SHOW and SELECT I think the %name columns of affected I_S tables will need to use a _cs or _bin collation (but I also do not think a utf8_general_cs collation exists currently?).

However I also think that such changes will need to be considered carefully of course!

But I have now seen several replies by MySQL people here replying to users requesting extensions to various SHOW statements that this will not be implemented as queries to I_S is an equally good option.  

I believe this simple example shows that it is not always. Difference between case sensitivity of some platforms and the default utf8 collation creates ambiguity.
[30 Oct 2009 23:24] Peter Laursen
.. of course you may add a " .. COLLATE utf8_bin .." to the WHERE, but since this is always required for such SELECT to be safe, I think the COLLATION should be defined for the column(s) affected.
[19 Nov 2009 7:52] Sveta Smirnova
Thank you for the report.

Verified as described although I think behavior of SHOW is more weird.

Workaround:
SELECT DISTINCT table_name FROM information_schema.TABLES WHERE table_name LIKE 't%' AND TABLE_SCHEMA = 'somedb';
[11 Nov 2016 16:28] Paul Dubois
Posted by developer:
 
Fixed in 8.0.0 by addition of data dictionary.

Related:
http://dev.mysql.com/doc/refman/8.0/en/charset-collation-information-schema.html