MySQL Bugs: #56757: Lettercase for table *lowercases* in VIEW-definition built from SELECT .. JOIN

Bug #56757	Lettercase for table lowercases in VIEW-definition built from SELECT .. JOIN
Submitted:	13 Sep 2010 18:49	Modified:	15 Sep 2010 7:15
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.50	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
We have a user/customer reporting that a VIEW created as 

DELIMITER $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_treeobject` AS (
SELECT
  `t`.`treeID`            AS `treeID`,
  `t`.`internalName`      AS `internalName`,
FROM ((`g_Tree` `t`
    JOIN `g_TreeAlias` `ta` ON ((`ta`.`treeID` = `t`.`treeID`)) 
     )) $$

DELIMITER ;

gets saved as (as per SHOW CREATE VIEW)

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_treeobject` AS (
SELECT
  `t`.`treeID`            AS `treeID`,
  `t`.`internalName`      AS `internalName`,
FROM ((`g_tree` `t`
    JOIN `g_treealias` `ta` ON ((`ta`.`treeID` = `t`.`treeID`)) 
     ))

User tells that he is using 5.1.41 on Win7-64 (with lower_case_table_names=2).

Note that the lettercase for `TreeAlias` gets *lowercased* in the JOIN-clause.

The report to our Forums (at Webyog) is here: 
http://www.webyog.com/forums/index.php?showtopic=5326&st=0&gopid=23195&#entry23195

Actually on 5.1.50 64-bit for Windows I am not able to create this VIEW at all.  It get 'syntax error' - not 'table does not exist' (the latter would make sense as I did not create 'underlying tables' - but parsing for 'syntax error' is before 'table does not exist' so creating tables would/should make no difference.

Seem that 'lower_case_table_names = 2' is now completely out of control on Win and Mac (again). Refer also http://bugs.mysql.com/bug.php?id=56751

How to repeat:
See above. 

(I have currently no option to downgrade to 5.1.41 - I hope that the above is enough to trigger an investigation)

Suggested fix:
Wake up every MySQL developer at 4 am every morning for the next 6 month and make them repeat 50 times "Not all files systems are case-sensitive and there is a 'lower_case_table_names' in MySQL!"

:-)

well .. maybe more precisely described as "lettercase for an underlying table of a VIEW lowercases in the JOIN-clause in VIEW-definition"

Indeed, table name is lowercased:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show global variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> create table `t_T`(c1 int);
Query OK, 0 rows affected (0.13 sec)

mysql> create view v1 as select t.c1 from (`t_T` t join `t_T` ta on t.c1 = ta.c1
);
Query OK, 0 rows affected (0.03 sec)

mysql> show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `v1` AS select `t`.`c1` AS `c1` from (`t_t` `t` join `t_t`
 `ta` on((`t`.`c1` = `ta`.`c1`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.02 sec)

mysql> select * from v1;
Empty set (0.05 sec)

So, what? Why is this a problem?

As you can see, I can both create (similar) view and use it, as soon as underlying table exists. What do I miss? 

Please, send complete test case, with CREATE TABLE statements and all other details.

I will try to get more details from the original case.

The problem is for most users cosmetical of course.  It will work as comparison is case insensitive on Windows.  But still an issue for users that want to use some 'rules' for lettercasing their database objects (for readability or whatever). 

More important though is that this VIEW cannot be migrated to a case sensitive environment (Linux). You'll get 'table does not exist' if you try. Lots of people develop on Windows and move applications to a production server on Linux. 'lowercase_case_table_names = 2' on Windows should ensure that this is possible. But here (with mixed-case tablenames in a VIEW constructed with a SELECT .. JOIN) it will fail.  It may not be the only situation. 

Note that with the example *only* immediately after "JOIN" it happens that TableName lowercases. Are you sure it does not too in Linux/Unix? Because then it cannot create here at all.

Updated synopsis.

It does not seem to be possible to contact this user of ours.  But Valeriuy's example/test case is equally good:

A VIEW created like

SELECT .. JOIN `t_T` ta on t.c1 = ta.c1

becomes 

SELECT .. JOIN `t_t` ta on t.c1 = ta.c1

.. what means that a DUMP from Windows and MAC (with lower_case_table_names = 2) will fail to import on Linux (with lower_case_table_names = 0). Add to this the cosmetical/readability issue of lowercasing long descriptive identifiers.

I have this problem vith view (no problem with create/show table or stored procedures)

[code]
CREATE TABLE `g_Tree` (
  `treeID` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`treeID`)
);
CREATE TABLE `g_TreeAlias` (
  `treeID` INT(11) NOT NULL,
  `alias` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`treeID`,`alias`)
);
[/code]
view:
[code]CREATE
    VIEW `test`.`g_view_test` 
    AS
(
SELECT ta.* FROM g_Tree t
INNER JOIN g_TreeAlias ta ON t.treeID=ta.treeID
);[/code]

result for "SHOW CREATE VIEW g_view_test":
[code]g_view_test	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_test` AS (
    select `ta`.`treeID` AS `treeID`,`ta`.`alias` AS `alias` 
    from (`g_tree` `t` 
       join `g_treealias` `ta` on((`t`.`treeID` = `ta`.`treeID`))
    )
)	
utf8	
utf8_general_ci[/code]

result for "SHOW CREATE TABLE g_Tree"
[code]
Table	
Create Table
g_Tree	CREATE TABLE `g_Tree` (
  `treeID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`treeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[/code]

[code]
SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';
lower_case_table_names	2
[/code]
Win7-64b        5.1.41 (or 5.1.48 - same problem) 

problem is lowercase table name in view even if global settings is correctly set to 2  (SHOW CREATE VIEW g_view_test) as you can se in "table" it works (SHOW CREATE TABLE g_Tree)

Looks like we agreed what the problem is and how it is different from bug #56751.