[MDEV-12801] "Create table like" statement ignores capitalization Created: 2017-05-15  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Platform Windows, Server
Affects Version/s: 5.5, 10.0, 10.1, 10.1.23, 10.2
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Martin Niemeier Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 2
Labels: upstream-fixed
Environment:

Windows 7, 64Bit,
lower_case_table_names=2,
Engine=MyISAM,
MariaDB 10.1.23


Issue Links:
Relates
relates to MDEV-12800 Table name gets converted to lower ca... Confirmed

 Description   

I am running a MariaDB server on Windows with lower_case_table_names=2 (i.e. names are stored as declared, but compared in lowercase).

If I create a new table using the "CREATE TABLE LIKE" statement, the capitalization
of the target table name is ignored and the lower case spelling is used instead.
The following two SQL statements reproduce the issue:

CREATE TABLE `TestTable` (`TestColumn` INT NULL, INDEX `TestIndex` (`TestColumn`)) ENGINE=MyISAM;
CREATE TABLE `AnotherTable` LIKE `TestTable`;

After execution, the second table is called "anothertable" in the database, not "AnotherTable" as expected.

Might be related to MDEV-12800

 Comments   
Comment by Martin Niemeier [ 2017-05-15 ]

Capitalization is also ignored when creating a view, e.g.:

CREATE TABLE `TestTable` (`TestColumn` INT NULL, INDEX `TestIndex` (`TestColumn`)) ENGINE=MyISAM;
CREATE VIEW `A-View` AS (SELECT * FROM `TestTable`);

Results in a view called "a-view", not "A-View" as expected. I refrain from creating a separate bug report as both bugs probably have the same cause.

Comment by Elena Stepanova [ 2017-05-18 ]

Thanks for the report.
Does it cause any real problems, and if so, could you please describe what they are?

Reproducible on all of 5.5 - 10.2, as well as on MySQL 5.5. Not reproducible on 5.6, 5.7.

Comment by Martin Niemeier [ 2017-06-12 ]

Well, it does not cause "real problems" as there is a workaround, its more of an inconvenience.
I use the MariaDB server for Big-Data applications where I have lots of tables that are visible 1-to-1 to the end-user. For the table names to be human readable, it is preferable if the capitalization is preserved.

The workaround is to first create a the table with a temporary name, and then rename it to the intended name, e.g.

CREATE TABLE temp_table_name LIKE TableA;
RENAME TABLE temp_table_name TO TableB;

This way, the capitalization is correct.

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