MySQL Bugs: #57830: querying I_S.tables for TABLE_NAME='TABLE' returns name in lowercase

Bug #57830	querying I_S.tables for TABLE_NAME='TABLE' returns name in lowercase
Submitted:	29 Oct 2010 2:30	Modified:	29 Oct 2010 3:16
Reporter:	Alfredo Kojima	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.50, 5.5.7	OS:	MacOS (10.6.4)
Assigned to:		CPU Architecture:	Any

Description:
When I do a SELECT on I_S.tables, the TABLE_NAME column from the resultset will change the case of the table name, depending on how I query it. 

How to repeat:
I have a schema called x, with a few tables:

mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| TEST1       |
| TEST2       |
| y           |
+-------------+
3 rows in set (0.00 sec)

If I query I_S.tables for tables in that schema:
mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x';
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | NULL    | x      | TEST1 |
| table       | NULL    | x      | TEST2 |
| table       | NULL    | x      | y     |
+-------------+---------+--------+-------+
3 rows in set (0.00 sec)

So far, so good. Now, If I want to query info on a specific table:
mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x'  AND 'TEST2'=TABLE_NAME;
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | NULL    | x      | test2 |
+-------------+---------+--------+-------+

The name is now returned in lower case!

OTOH, tweaking the where clause just a little will not produce that effect:

mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x'  AND 'TEST2'<>TABLE_NAME;
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | NULL    | x      | TEST1 |
| table       | NULL    | x      | y     |
+-------------+---------+--------+-------+
2 rows in set (0.02 sec)

Verified on 5.5.7 also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database x;
Query OK, 1 row affected (0.00 sec)

mysql> use x;
Database changed
mysql> create table `TEST1` (c1 int);
Query OK, 0 rows affected (0.07 sec)

mysql> create table `TEST2` (c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql> create table y (c1 int);
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as
    -> 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW'
    -> AND table_schema = 'x';
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | def     | x      | TEST1 |
| table       | def     | x      | TEST2 |
| table       | def     | x      | y     |
+-------------+---------+--------+-------+
3 rows in set (0.00 sec)

mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as
    -> 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW'
    -> AND table_schema = 'x'  AND 'TEST2'=TABLE_NAME;
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | def     | x      | test2 |
+-------------+---------+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x'  AND 'TEST2'<>TABLE_NAME;
+-------------+---------+--------+-------+
| OBJECT_TYPE | CATALOG | SCHEMA | NAME  |
+-------------+---------+--------+-------+
| table       | def     | x      | TEST1 |
| table       | def     | x      | y     |
+-------------+---------+--------+-------+
2 rows in set (0.00 sec)

mysql> explain SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x'  AND 'TEST2'=TABLE_NAME;
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table  | type | possible_keys | key                     | key_len | ref  | rows | Extra                                           |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_frm_only; Scanned 0 databases |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = 'x'  AND 'TEST2'<>TABLE_NAME;
+----+-------------+--------+------+---------------+--------------+---------+------+------+------------------------------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref  | rows | Extra                                          |
+----+-------------+--------+------+---------------+--------------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA | NULL    | NULL | NULL | Using where; Open_frm_only; Scanned 1 database |
+----+-------------+--------+------+---------------+--------------+---------+------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

Same with TABLE_SCHEMA:

mysql> create schema SC;
Query OK, 1 row affected (0.03 sec)

mysql> create table SC.TB (A int);
Query OK, 0 rows affected (0.11 sec)

mysql> select table_schema, table_name from information_schema.tables where table_schema = 'SC';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| sc           | TB         |
+--------------+------------+
1 row in set (0.01 sec)

mysql> show schemas like 'SC';
+---------------+
| Database (SC) |
+---------------+
| SC            |
+---------------+
1 row in set (0.04 sec)