Bug #70356 can not select value from information_schema table without workaround

• Submitted: 16 Sep 2013 16:49
• Modified: 3 Jan 2014 19:51
• Reporter: Ruslan Zakirov
• Status: No Feedback
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.5.33
• OS: MacOS
• CPU Architecture: Any
• Tags: case, information schema

[16 Sep 2013 16:49] Ruslan Zakirov

Description:
Wanted to select "canonic" name of a table from information schema to make my code work on systems with case sensitive table names. Was really surprised by the following result:

mysql> select table_name from information_schema.tables where table_schema = 'rt4test' and table_name = 'groupmembers';
+--------------+
| table_name   |
+--------------+
| groupmembers |
+--------------+
1 row in set (0.02 sec)

Use the following workaround:

mysql> select table_name from information_schema.tables where table_schema = 'rt4test' and lower(table_name) = 'groupmembers';
+--------------+
| table_name   |
+--------------+
| GroupMembers |
+--------------+
1 row in set (0.04 sec)

How to repeat:
mysql> select table_name from information_schema.tables where table_name = 'gRoUpMeMbErS';
+--------------+
| table_name   |
+--------------+
| gRoUpMeMbErS |
| gRoUpMeMbErS |
| gRoUpMeMbErS |
| gRoUpMeMbErS |
+--------------+
4 rows in set (0.59 sec)

Suggested fix:
I expect select to return data from the table, not from request.

[3 Dec 2013 19:51] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior. Please provide output of SHOW VARIABLES LIKE '%char%' and SHOW VARIABLES LIKE '%col%'

[4 Jan 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".