Bug #86022 TABLE_SCHEMA column transformed to lower case for upper case database names
Submitted: 20 Apr 2017 16:31 Modified: 21 Apr 2017 7:35
Reporter: Lukas Eder Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:5.7.11 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any
Tags: Database name, information_schema, lower case, upper case

[20 Apr 2017 16:31] Lukas Eder
Description:
When a database name is written in upper case, it is reported in lower case from the INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA column in some situations

How to repeat:
CREATE DATABASE `UPPER`;
CREATE TABLE `UPPER`.`T` (`I` int);

-- These two queries incorrectly report the database name as `upper`
SELECT DISTINCT table_schema FROM information_schema.columns 
WHERE information_schema.columns.table_schema = 'UPPER';

SELECT DISTINCT table_schema FROM information_schema.columns 
WHERE information_schema.columns.table_schema IN ('UPPER');

-- These two queries correctly report the database name as `UPPER`:
SELECT DISTINCT table_schema FROM information_schema.columns 
WHERE concat(information_schema.columns.table_schema) IN ('UPPER');

SELECT DISTINCT table_schema FROM information_schema.columns 
WHERE information_schema.columns.table_schema IN ('UPPER', 'dummy');
[20 Apr 2017 23:13] Miguel Solorzano
Thank you for the bug report. I couldn't repeat with most recent source server on Manjaro Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 Source distribution 2017-apr-19

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.7 > CREATE DATABASE `UPPER`;
Query OK, 1 row affected (0,00 sec)

mysql 5.7 > CREATE TABLE `UPPER`.`T` (`I` int);
Query OK, 0 rows affected (0,33 sec)

mysql 5.7 > 
mysql 5.7 > -- These two queries incorrectly report the database name as `upper`
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns 
    -> WHERE information_schema.columns.table_schema = 'UPPER';
+--------------+
| table_schema |
+--------------+
| UPPER        |
+--------------+
1 row in set (0,00 sec)

mysql 5.7 > 
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns 
    -> WHERE information_schema.columns.table_schema IN ('UPPER');
+--------------+
| table_schema |
+--------------+
| UPPER        |
+--------------+
1 row in set (0,00 sec)

mysql 5.7 > 
mysql 5.7 > -- These two queries correctly report the database name as `UPPER`:
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns 
    -> WHERE concat(information_schema.columns.table_schema) IN ('UPPER');
+--------------+
| table_schema |
+--------------+
| UPPER        |
+--------------+
1 row in set (0,00 sec)

mysql 5.7 > 
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns 
    -> WHERE information_schema.columns.table_schema IN ('UPPER', 'dummy');
+--------------+
| table_schema |
+--------------+
| UPPER        |
+--------------+
1 row in set (0,00 sec)

mysql 5.7 > 

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.19                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| tls_version             | TLSv1,TLSv1.1                   |
| version                 | 5.7.19                          |
| version_comment         | Source distribution 2017-apr-19 |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
+-------------------------+---------------------------------+
8 rows in set (0,00 sec)

mysql 5.7 >
[21 Apr 2017 7:35] Lukas Eder
Thanks for looking into this. I omitted the OS because this can be reproduced on Windows and MacOSX as reported here in a jOOQ issue:
https://github.com/jOOQ/jOOQ/issues/5213

Some additional info:

mysql> SHOW VARIABLES LIKE "%VERSION%";

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.11-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

Probably also relevant here:

mysql> SHOW VARIABLES LIKE '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+

Let me know if you need any additional info.