Fixing 10 years old MySQL bug 20356 lower table name on view

[9 Jun 2006 11:55] Peter Laursen

Description:

On Windows when creating a VIEW having UPPERCASE(s) it its name and based on tables also having UPPERCASE(s) in their names, *SHOW CREATE TABLE* returns only LOWERCASES for the VIEW itself and the TABLES used for its construction.

This makes it difficult to use a Windows machine with MySQL as a developer machine when using VIEWs (that can be very complex with several (hundreds) UNIONs and JOINs etc.) and then port it to a production machine on *nix where UPERCASES are used for TABLE and VIEW names.

IMHO it also is both an (un-necessary ?) over-simplification and a misunderstanding of how the 'case-insensivity' of Windows should be understood.

How to repeat:

CREATE TABLE `test`.`TableName1` (`id` bigint NOT NULL ,`t` varchar (20) NULL);

CREATE VIEW `test`.`ViewName1` AS ( SELECT * FROM TableName1);

SHOW CREATE TABLE TableName1;

/*

CREATE TABLE `TableName1` ( `id` bigint(20) NOT NULL,`t` varchar(20) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

*/

SHOW CREATE TABLE ViewName1;

/*

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewname1` AS (select `tablename1`.`id` AS `id`,`tablename1`.`t` AS `t` from `tablename1`)

==> note that `ViewName1` becomes`viewname1` and `TableName1` becomes `tablename1`

*/

Suggested fix:

Let 'SHOW CREATE TABLE' return the LETTERCASE used when creating the VIEW.

If this in 'not a bug' according to the docs, then change the docs, so it will become a bug!  :-)  It is really annoying!