MySQL Bugs: #25630: INFORMATION_SCHEMA collation wrong for filesystems with case sensitive filenames

Bug #25630	INFORMATION_SCHEMA collation wrong for filesystems with case sensitive filenames
Submitted:	15 Jan 2007 15:59	Modified:	23 Jul 2009 22:12
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Verified
Category:	Server: I_S	Severity:	S3 (Non-critical)
Version:	5.0.33-standard-log/5.1BK	OS:	Linux (Centos 4.x)
Assigned to:		Target Version:
Tags:	lower_case_table_names, information_schema, bfsm_2007_01_18
Triage:	Triaged: D5 (Feature request)

Description:
The Query

mysql> select table_schema, table_name, count(*) as anz from tables group by
table_schema, table_name having anz > 1;

should never return any rows on a noncorrupt server.

How to repeat:
mysql> select table_schema, table_name, count(*) as anz from tables group by
table_schema, table_name having anz > 1;
+--------------+------------+-----+
| table_schema | table_name | anz |
+--------------+------------+-----+
| bp           | CreditCard |   2 |
+--------------+------------+-----+
1 row in set (0.00 sec)

[root@mc01bizdb-01 bp]# ls -1b Cred*frm
Creditcard.frm
CreditCard.frm

Note the spelling variants for Creditcard/CreditCard.

Suggested fix:
1. Fix collation for information_schema. ( table_schema, table_name ) is supposed to be a
primary key for I_S.TABLES:
2. Fix the handling of tablename case sensitivity so that we are finally compliant with
the SQL standard.

Does the MySQL Reference Manual say that MySQL
uses a special collation for information_schema.tables
that corresponds to the platform?

If not, I don't understand how this is a "bug".

In I_S, I tried to join STATISTICS to TABLES (and later COLUMNS) to get a list of types
used in indices in large tables.

The join between these two tables does not work if you have two tables bp.CreditCard and
bp.Creditcard in your system and the system does not use lower_case_table_names and is on
a Unix filesystem. It will join the TABLES row bp.CreditCard to statistics row
bp.Creditcard. 

To fix this, you have to either

a) cast this manually to BINARY on system with the above filesystem condition (i.e.
workaround exists)

b) fix INFORMATION_SCHEMA to provide the proper collation for the table name and schema
name, which is dependent on the above filesystem condition

or

c) fix MySQL to treat table and schema names according to the SQL standard

I prefer solution c.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18259

ChangeSet@1.2382, 2007-01-17 17:59:45+04:00, gluh@mysql.com +6 -0
  Bug#25630 INFORMATION_SCHEMA collation wrong for filesystems with case sensitive
filenames
  Binary CS is used for SCHEMA_NAME & TABLE_NAME fields for I_S

See bug #46771

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