Bug #34921 comparisons with Information schema tables don't honor collation
Submitted: 28 Feb 2008 11:16 Modified: 7 Sep 2016 19:24
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.1.23/5.1BK OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R4 (High) / E4 (High)

[28 Feb 2008 11:16] Roland Bouman
Description:
Comparisons with columns of information_schema tables do not seem to honor the current settings for collations.

In a query like:

select *
from   information_schema.columns
where  table_schema = schema()
and    table_name = 'City' 

the comparison 

table_name = 'City' 

is stubbornly performed in a case sensitive manner even though:
- the collation for the session is utf8_general_ci
- the collation for the column is  utf8_general_ci

Even if an explicit collation is specified at the expression level like so:

table_name = 'City' COLLATE utf_general_ci

the comparison is still performed in a case sensitive manner.

This is a serious problem and will break many queries. AFAIK comparisons used to work case-insensitive by default, at least until 5.1.21, probably 5.1.22.

How to repeat:
mysql> use world;

-- demonstrate that all table names are in lower case:

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

-- demonstrate that the connection uses a case insensitive collation:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

-- demonstrate that there are no explicit collation specifications for the information schema table

mysql> show create table information_schema.columns;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COLUMNS | CREATE TEMPORARY TABLE "COLUMNS" (
  "TABLE_CATALOG" varchar(512) DEFAULT NULL,
  "TABLE_SCHEMA" varchar(64) NOT NULL DEFAULT '',
  "TABLE_NAME" varchar(64) NOT NULL DEFAULT '',
  "COLUMN_NAME" varchar(64) NOT NULL DEFAULT '',
  "ORDINAL_POSITION" bigint(21) unsigned NOT NULL DEFAULT '0',
  "COLUMN_DEFAULT" longtext,
  "IS_NULLABLE" varchar(3) NOT NULL DEFAULT '',
  "DATA_TYPE" varchar(64) NOT NULL DEFAULT '',
  "CHARACTER_MAXIMUM_LENGTH" bigint(21) unsigned DEFAULT NULL,
  "CHARACTER_OCTET_LENGTH" bigint(21) unsigned DEFAULT NULL,
  "NUMERIC_PRECISION" bigint(21) unsigned DEFAULT NULL,
  "NUMERIC_SCALE" bigint(21) unsigned DEFAULT NULL,
  "CHARACTER_SET_NAME" varchar(64) DEFAULT NULL,
  "COLLATION_NAME" varchar(64) DEFAULT NULL,
  "COLUMN_TYPE" longtext NOT NULL,
  "COLUMN_KEY" varchar(3) NOT NULL DEFAULT '',
  "EXTRA" varchar(27) NOT NULL DEFAULT '',
  "PRIVILEGES" varchar(80) NOT NULL DEFAULT '',
  "COLUMN_COMMENT" varchar(255) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- demonstrate that the current collation for the information_schema table's columns should be case insensitive

mysql> select table_collation from information_schema.tables where table_schema 
= 'information_schema' and table_name = 'columns';
+-----------------+
| table_collation |
+-----------------+
| utf8_general_ci |
+-----------------+
1 row in set (0.00 sec)

-- demonstrate that the comparisons are performed in a case sensitive manner, contrary to the implied collation:

mysql> select *
    -> from information_schema.columns
    -> where table_schema = schema()
    -> and table_name = 'City';
Empty set (0.00 sec)
 
-- demonstrat that even an explicit collation does not cure this ailment:

mysql> select *
    -> from information_schema.columns
    -> where table_schema = schema()
    -> and table_name = 'City' collate utf8_general_ci;
Empty set (0.01 sec)

-- demonstrate that only the exact table name returns rows:

select *
from information_schema.columns
where table_schema = schema()
and table_name = 'city'

(returns expected result)

Suggested fix:
Please take the character set/collation into account when doing comparisons against information schema tables.

interestingly, comparisons with the identifiers of the information_Schema tables themselves *are* performed in a case-instensitive manner:

(
select *
from information_schema.columns
where table_schema = 'INFORMATION_SCHEMA' -- lower case in db
and table_name = 'tables'                 -- upper case in db
)
[28 Feb 2008 12:25] Godofredo Miguel Solorzano
Thank you for the bug report.
[16 Apr 2008 19:44] Peter Gulutzan
See also Trudy's comments on Bug #25630
"INFORMATION_SCHEMA collation wrong for filesystems with case sensitive
filenames"

It's true that this won't find 'CITY'
SELECT ... WHERE TABLE_NAME = 'City' COLLATE utf8_general_ci;
but this will:
SELECT ... WHERE TABLE_NAME COLLATE utf8_general_ci = 'City';
[23 Apr 2008 15:27] Martin Hansson
In fact, comparisons with fields in information_schema.columns should always be be performed non-case-sensitive, unless an explicit COLLATE <case-sensitive collation> is used. 

The collation coercion rules give the highest priority to explicit COLLATE specifications. After that, a column's collation takes precedence over a string constant. And since the columns in the information_schema.columns table use a non-case-sensitive collation, the comparison uses that collation, unless COLLATE is issued.

Consider:

select column_name, collation_name from information_schema.columns where table_name='columns';
+--------------------------+-----------------+
| column_name              | collation_name  |
+--------------------------+-----------------+
.
.
.
| TABLE_NAME               | utf8_general_ci |
.
.
.

Yes, reflexivity is a beatiful thing. :-)
[24 Apr 2008 10:19] Bugs System
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/45938

ChangeSet@1.2562, 2008-04-24 12:11:57+02:00, mhansson@riffraff.(none) +3 -0
  Bug#34921: comparisons with Information schema tables 
  don't honor collation
  
  When searching for tables using information_schema tables,
  the optimization to answer the query by only opening FRM
  files failed because a lookup key was created and the file
  system will generally disregard collations when using this
  key.
  Fixed by not creating the lookup key if the bespoke 
  optimization is used.
[24 Apr 2008 10:20] Bugs System
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/45939

ChangeSet@1.2562, 2008-04-24 12:06:13+02:00, mhansson@riffraff.(none) +3 -0
  Bug#34921: comparisons with Information schema tables 
  don't honor collation
  
  When searching for tables using information_schema tables,
  the optimization to answer the query by only opening FRM
  files failed because a lookup key was created and the file
  system will generally disregard collations when using this
  key.
  Fixed by not creating the lookup key if the bespoke 
  optimization is used.
[29 Apr 2008 16:09] Martin Hansson
The below fix does not require any collations to be changed. Instead, the fix reimplements the optimization in WL#3732 so that we do not use the key to open files. There is still room for improvement, however, since pre-WL#3732 we would scan all databases, not just those matching the search key.

The 2 benchmarks below illustrate where the performance gain is. Execution times were measured on an intel quad-core with 8G Ram.

Benchmark 1
-----------

Creates 5000 tables in the same database.

With this patch    0.022s
Without this patch 0.001s
Pre-WL#3732        0.028s

Benchmark 2
-----------

Creates 2000 databases, 15 tables in each one.

With this patch    0.014
Without this patch 0.003
Pre-WL#3732        0.797
[29 Apr 2008 16:12] Martin Hansson
Benchmark 1

Attachment: benchmark.test (application/octet-stream, text), 269 bytes.

[29 Apr 2008 16:13] Martin Hansson
Benchmark 2

Attachment: benchmark2.test (application/octet-stream, text), 829 bytes.

[29 Apr 2008 16:15] Bugs System
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/46198

ChangeSet@1.2569, 2008-04-29 18:07:26+02:00, mhansson@riffraff.(none) +3 -0
  Bug#34921: comparisons with Information schema tables don't 
  honor collation
  
  The bug introduced by WL 3732 is that the file system's
  open function is used as an index when searching for tables
  with a given table name and database name. The problem is
  that we support a diversity of file systems treating e.g.
  letter case differently. In general the file system cannot
  be trusted to implement the correct collation.
  Fixed by using the search key to prune database names from
  the result set before scanning the databases, as opposed to
  opening files by name.
[12 May 2008 8:37] Martin Hansson
Summary of an IRC discussion regarding Bug#34921 on May 7
------------------------------------------------------------------

Participants: Alexander Barkov (bar), Sergey Gluhov (gluh), Sergei Golubchik
(serg), Peter Gulutzan (peter), Martin Hansson (mhansson), Georgi Kodinov
(joro)

This is a summary of a proposed fix for this bug - "the proposal" , that will
also rectify other small problems related to table and database searches in
information_schema.

Worklog#3732: Information schema optimization introduced an optimization for
table and database lookups in information_schema that broke behaviour on some
platforms. It actually broke behaviour on all platforms, but the main problem
is on platforms with case-sensitive filenames. This broken behaviour is
reported as Bug#34921.

Wl#3732 notwithstanding, the current proposal is to change the collation,
utf8_general_ci, for all file-based SQL objects in
information_schema. According to bar, it was wrong to use this collation in
the first place since it does not respect accent differences. This should
(says bar) have been fixed along with Bug#25630: INFORMATION_SCHEMA collation
wrong for filesystems with case sensitive filenames.

joro, bar, serg, and gluh all agree that that the I_S should reflect the
general handling of identifiers. I.e. when two identifiers are considered
syntactically equal on a given platform, the collation for the corresponding
column in an information_schema table should treat strings consisting of the
identifiers accordingly. Hence we need to have different collations on
different platforms. By implementing this, Bug#34921 will disappear.

peter's main objection is that this is a major change in behaviour and has to
be approved by the server product team. Another objection is that this
contradicts the current manual, which says that there should be only one
collation inside information_schema.

Another point of concern is whether search with an explicitly mentioned collation
will still work. The answer is yes, and the reason is that the optimization
from WL#3732 will be disabled in this case. It will be slow but correct.

We also discussed whether the aforementioned change of collation is actually
desired by users and since this is - as peter correctly pointer out - a matter
of opinion, we took a vote on it. The veridict was to change the collations,
and the verdict was accepted by everyone.

  "<serg> it's only logical to say that SELECT FROM I_S.TABLES WHERE
  TABLE_NAME='tablename' should use the same rules as SELECT * FROM tablename"

  "<peter> serg: asserting 'it is a bug fix' does not address the evidence,
  which is (a) the older behaviour was accepted in a clear and written
  decision, (b) the bug complaint is not about the older behaviour but about
  the newer behaviour, (c) the documentation says somewhere that we have one
  collation, (d) there was nothing in the 'specification' for the optimization
  (as far as i can see) that hinted about this change."

  "<serg> (a) if the bug is caused by a decided behavior, it's still a
  bug. (b) "newer" = "caused by the optimization" ? yes, I agree. (c) well,
  it's wrong then. (d) correct, it's an unexpected bug caused by the
  optimization. which does not mean that it should not be fixed."

The following columns would be affected (according to gluh). Columns within
parenteses should probably not be changed since we only populate them with
NULL. Catalogs and sql_path's are not used in MySQL.

  SCHEMATA: (CATALOG_NAME) SCHEMA_NAME (SQL_PATH)

  TABLES, COLUMNS, STATISTICS, VIEWS, hidden table TABLE_NAMES, PARTITIONS:
    (TABLE_CATALOG) TABLE_SCHEMA TABLE_NAME

  EVENT: (EVENT_CATALOG) EVENT_SCHEMA

  PROC: (ROUTINE_CATALOG) ROUTINE_SCHEMA (SQL_PATH)

  USER_PRIVILEGES: (TABLE_CATALOG)

  SCHEMA_PRIVILEGES: (TABLE_CATALOG) TABLE_SCHEMA

  TABLE_PRIVILEGES, COLUMN_PRIVILEGES: (TABLE_CATALOG) TABLE_SCHEMA TABLE_NAME

  hidden table OPEN_TABLES: Database Table

  FILES: FILE_NAME TABLESPACE_NAME (TABLE_CATALOG) TABLE_SCHEMA TABLE_NAME

  PROCESSLIST: DB

  TABLE_CONSTRAINTS: (CONSTRAINT_CATALOG) CONSTRAINT_SCHEMA TABLE_SCHEMA TABLE_NAME

  KEY_COLUMN_USAGE: (CONSTRAINT_CATALOG) CONSTRAINT_SCHEMA (TABLE_CATALOG)
  TABLE_SCHEMA TABLE_NAME REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME

  REFERENTIAL_CONSTRAINTS: (CONSTRAINT_CATALOG) CONSTRAINT_SCHEMA
  (UNIQUE_CONSTRAINT_CATALOG) UNIQUE_CONSTRAINT_SCHEMA REFERENCED_TABLE_NAME

  TRIGGERS: (TRIGGER_CATALOG) TRIGGER_SCHEMA TRIGGER_NAME
  (EVENT_OBJECT_CATALOG) EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE

Another argument that was raised by serg is regarding constraints on the
information_schema tables. The definition of base table "TABLES" includes:

...
TABLE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
TABLE_SCHEMA  INFORMATION_SCHEMA.SQL_IDENTIFIER,
TABLE_NAME    INFORMATION_SCHEMA.SQL_IDENTIFIER,
CONSTRAINT TABLES_PRIMARY_KEY
  PRIMARY KEY ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ),
...

Currently we cannot support this constraint since we can have two different
tables on Linux named 't' and 'T'. The current collation for TABLES.TABLE_NAME
treats them as equal, and hence we fail to fulfil this constraint. For
Windows, this same applies if we have two tables name 'ta' and 'tä'.

This may be of minor imortance, however:

  "<peter> roland_away: it baffles me that anybody cares about this 'implied
  pk'. it seems to me that it is a reintroduction of the confusion between
  "what we will look at when deciding if it's a duplicate" and "what we will
  use when searching information_schema". and our whole, sole, entire purpose
  is what we will use when searching information_schema, for now."

Other databases: oracle will do searches with a case sensitive collation
because that's the collation oracle normally uses. microsoft SQL server will
do searches with a case insensitive collation because that is the collation
that microsoft normally uses. We support both ways depanding on platform,
which is the rationale behind the proposal.

Subjects that were not addressed in the meeting are: logfile groups and
falcon tablespaces. Discussions are unfolding this week per e-mail.
[10 Oct 2008 21:11] Arjen Lentz
Is this stalled?
[12 Oct 2008 9:32] Sveta Smirnova
Bug #39894 was marked as duplicate of this one.
[14 Sep 2009 6:29] Roel Van de Paar
See bug #46771
[1 Oct 2009 7:10] Sveta Smirnova
Bug #47755 was marked as duplicate of this one.
[21 Jan 2010 18:59] Paul Dubois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.
[23 Jun 2010 15:15] Georgi Kodinov
See bug #53095.
[2 Sep 2016 8:57] Gopal Shankar
With WL#6599, we do proper comparison. See below.

set names utf8;
create table city (t1 int);
select *
from   information_schema.columns
where  table_schema = schema()
and    table_name = 'City';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	GENERATION_EXPRESSION
select *
from   information_schema.columns
where  table_schema = schema()
and    table_name = 'City' collate utf8_general_ci;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	GENERATION_EXPRESSION
def	test	city	t1	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references
[7 Sep 2016 7:01] Ståle Deraas
Posted by developer:
 
Fixed by WL#6599
[7 Sep 2016 19:24] Paul Dubois
Posted by developer:
 
Noted in 8.0.0 changelog.

For queries on INFORMATION_SCHEMA tables, comparisons of schema and
table names could be case sensitive or insensitive, depending on the
characteristics of the underlying file system and the
lower_case_table_names system variable value. Schema and table name
comparisons now are case insensitive. For some queries, this may
produce results that differ from previous results; to handle this,
provide a COLLATE clause specifying a collation that has the desired
comparison properties.
[8 Sep 2016 16:35] Paul Dubois
Revised changelog entry:

For queries on INFORMATION_SCHEMA tables, comparisons of schema and
table names could be case sensitive or insensitive, depending on the
characteristics of the underlying file system and the
lower_case_table_names system variable value. Furthermore, it was
ineffective to provide a COLLATE clause to change the comparison
properties because that clause was ignored. This has been changed so
that COLLATE is no longer ignored and can be used to obtain the
desired comparison properties.