Bug #47755 I_S queries ignore database name upper/lower case.
Submitted: 1 Oct 2009 6:54 Modified: 1 Oct 2009 7:09
Reporter: Rafal Somla Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 Oct 2009 6:54] Rafal Somla
Description:
Queries against INFORMATION_SCHEMA database which list objects other than tables in a given database, do not distinguish databases whose names differ in case only. A "WHERE xxxx_schema = '<db_name>'" clause of the query is *not* case sensitive.

This leads to a situation where a query with WHERE clause can return non-empty result set for a database which is empty. This is confusing. However, the xxx_schema column in the result contains the correct database name, so it is possible to recognize the situation.

This problem leads to BUG#47386 as si_objects code assumes that WHERE clauses for I_S queries are case sensitive.

How to repeat:
The following test script illustrates the issue:

CREATE DATABASE db1;
CREATE DATABASE DB1;

USE db1;

CREATE TABLE t1 (a int);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE PROCEDURE p1() SET @foo=1;
CREATE FUNCTION f1() RETURNS int RETURN 1;
CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW SET @foo=1;
CREATE EVENT e1 ON SCHEDULE EVERY 1 YEAR DO SET @foo=1;

# Note that database DB1 is empty

USE information_schema;

--echo
--echo -- The following queries should return no results.
SELECT table_schema, table_name, table_type  
  FROM tables WHERE table_schema = 'DB1';
--echo --
SELECT routine_schema, routine_name, routine_type  
  FROM routines WHERE routine_schema = 'DB1';
--echo --
SELECT trigger_schema, trigger_name
  FROM triggers WHERE trigger_schema = 'DB1';
--echo --
SELECT event_schema, event_name
  FROM events WHERE event_schema = 'DB1';
--echo --
--echo

DROP DATABASE DB1;
DROP DATABASE db1;

--exit

Suggested fix:
Make WHERE clauses case sensitive as elsewhere. If a user wants to ignore case, he can use clause "WHERE LCASE(xxxx_schema) = '<db_name>'".
[1 Oct 2009 7:09] Sveta Smirnova
Thank you for the report.

This is duplicate of bug #34921
[21 Jan 2010 18:58] Paul Dubois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.