Bug #90852 field-name case in informaiton_schema.tables isn't honest in select_list

• Submitted: 14 May 2018 2:41
• Modified: 15 May 2018 7:50
• Reporter: Tsubasa Tanaka (OCA)
• Status: Duplicate
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0.11
• OS: CentOS (7.4)
• CPU Architecture: x86

[14 May 2018 2:41] Tsubasa Tanaka

Description:
MySQL 5.7 and earlier, querying information_schema.tables with lower-case select_list returns lower-case field-name.

mysql57 10> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.01 sec)

mysql57 10> SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

mysql57 10> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

But MySQL 8.0.11, is ignore case in select_list and always returns filed-name by upper-case.

mysql80 9> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql80 9> SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

mysql80 9> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

How to repeat:
See description.

Suggested fix:
Normal tables(Not in information_schema) is honest in select_list.
information_schema.tables should be honest too.

mysql80 9> CREATE TABLE d1.t1 (a int);
Query OK, 0 rows affected (0.07 sec)

mysql80 9> INSERT INTO d1.t1 (a) VALUES (1);
Query OK, 1 row affected (0.07 sec)

mysql80 9> SELECT a FROM d1.t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql80 9> SELECT A FROM d1.t1;
+------+
| A    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

[14 May 2018 2:57] MySQL Verification Team

Hello Tanaka-San,

Thank you for the report!

Thanks,
Umesh

[14 May 2018 2:58] MySQL Verification Team

# 8.0.11

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql>  SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.01 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

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

[14 May 2018 3:01] MySQL Verification Team

-- 5.7.22, 5.6.40 - table case retained as is

mysql> SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | user       |
+--------------+------------+
1 row in set (0.00 sec)

mysql>

[15 May 2018 7:46] Ståle Deraas

Posted by developer:
 
Closing this as a duplicate of a won't fix bug.

The new behaviour in 8.0 is according to the SQL standard.

workaround:  use column alias, for example:

select table_schema as table_schema, table_name as table_name
from information_schema.tables where table_name = 'users';