MySQL Bugs: #49708: 'information_schema' is treated different from other database names in queries

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

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

Thank you for the report.

Verified as described.

This behavior should be documented at least.

Information schema is the one that is behaving correctly.

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.

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.

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.

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