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>'".
