MySQL Bugs: #48445: inconsistency wth SHOW and SELECT FROM I

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

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.

.. 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.

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';

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