Bug #49708 'information_schema' is treated different from other database names in queries
Submitted: 15 Dec 2009 9:48 Modified: 5 Jan 2010 14:55
Reporter: Bernt Marius Johnsen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0, 5.1, next-mt OS:Linux
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[15 Dec 2009 9:48] Bernt Marius Johnsen
Description:
'information_schema' is treated different from other database/schema names in queries. Makes it very hard to write portable orthogonal code against MySQL (and, yes, I know INFORMATION_SCHEMA is a "virtual" database/schema, but why should I care, the behaviour should by orthogonal anyway).

(Running next-mr with default collation:
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
)

mysql> select distinct table_schema from information_schema.tables where table_schema = 'INFORMATION_SCHEMA';
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> select distinct table_schema from information_schema.tables where table_schema = 'information_schema';
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> select distinct table_schema from information_schema.tables where table_schema = 'test';
+--------------+
| table_schema |
+--------------+
| test         |
+--------------+
1 row in set (0.01 sec)

mysql> select distinct table_schema from information_schema.tables where table_schema = 'TEST';
Empty set (0.00 sec)

mysql> show databases like 'information_schema';
+-------------------------------+
| Database (information_schema) |
+-------------------------------+
| information_schema            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> show databases like 'INFORMATION_SCHEMA';
+-------------------------------+
| Database (INFORMATION_SCHEMA) |
+-------------------------------+
| information_schema            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> show databases like 'TEST';
Empty set (0.00 sec)

mysql> 

How to repeat:
select distinct table_schema from information_schema.tables where table_schema = 'INFORMATION_SCHEMA';
select distinct table_schema from information_schema.tables where table_schema = 'information_schema';
select distinct table_schema from information_schema.tables where table_schema = 'test';
select distinct table_schema from information_schema.tables where table_schema = 'TEST';
show databases like 'information_schema';
show databases like 'INFORMATION_SCHEMA';
show databases like 'test';
show databases like 'TEST';
[15 Dec 2009 10:19] Sveta Smirnova
Thank you for the report.

Verified as described.

This behavior should be documented at least.
[5 Jan 2010 0:29] Omer Barnir
Information schema is the one that is behaving correctly.
[5 Jan 2010 7:51] Bernt Marius Johnsen
I did not report that 'information_schema' was treated "erroneously", I reported that that it was treated "different" (i.e. not orthogonal). Now it has been made clear that 'information_schema' is the only one treated correctly, I still consider this as a bug since all other schema names apparently are NOT treated correctly.
[5 Jan 2010 8:23] Bernt Marius Johnsen
Clarification: It's not the schema name INFORMATION_SCHEMA that causes the problem. I agree with Peter that here INFORMATION_SCHEMA is treated correctly and that other schema names are treated mysqlistic, and that it is not a bug, just weird.

The problem is the comparision in the where clause, which behaves differently depending on the value in the TABLE_SCHEMA column of INFORMATION_SCHEMA.TABLES. If the value is 'information_schema', the =-operator in the where clause has other case sensitivity than for other values in the same column.
[5 Jan 2010 14:55] Peter Gulutzan
TABLE_SCHEMA's collation is utf8_general_ci, case insensitive.
MySQL sometimes ignores that and acts as if it's case sensitive.
If this bug report is that MySQL sometimes compares case insensitive,
then it's not a bug, because only information_schema is correct.
If this bug report is that MySQL sometimes compares case sensitive,
then it's a duplicate of
Bug#34921 comparisons with Information schema tables don't honor collation.
[21 Jan 2010 19:01] Paul Dubois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.