[MDEV-31472] Wrong results in queries to I_S for table/schema having capitals in the name Created: 2023-06-13  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.11.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Lawrin Novitsky Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes ODBC-391 With lower_case_table_names=2 server... Closed
Relates
relates to MDEV-14432 mysqldump does not preserve case of t... Confirmed

 Description   

If the server has lower_case_table_names=2(I tested on Windows, and did not verify on MacOS) and table or schema name contains capital letter like

CREATE TABLE TestAccount(id INT NOT NULL);

then various queries comparing those names will return those in all lower case letters like:

select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database() AND TABLE_NAME = 'TestAccount';
+--------------+
| TABLE_NAME   |
+--------------+
| testaccount |
+--------------+
1 row in set (0.000 sec)
select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database();
+--------------+
| TABLE_NAME   |
+--------------+
| TestAccount  |
+--------------+
1 row in set (0.001 sec)

It will happen on any table. Or for schema name.
More over, it's not possible to compare such name in case sensitive manner

select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database() AND TABLE_NAME = BINARY 'TestAccount';
0 rows in set (0.000 sec)

e.g. application can query schema table names from TABLES, get it with real cases of all letters, and then compare that name BINARY in COLUMNS or other table, and get nothing. If developer wants the application to be portable, I can imagine that he/she will want to compare names case sensitively.
I've tested this with 10.11, but I am pretty sure it's not specific to this version - initial ODBC report mentioned 10.3


Generated at Thu Feb 08 10:24:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.