Problem With Case In View

[peterlaursen]

1) Just try to execute 'SHOW CREATE VIEW viewname' after creating.  Will it return the same lettercases as the original CREATE statement (or the same as what SQLyog does).  If not this is a server problem.  It should then be reported to bugs.mysql.com if it is same with latest server (5.1.50). I can do that or you can once we have all detail and have verified on 5.1.50

2) If we shall verify it on our environments I need CREATE TABLE statements for the tables as well.

3) Tell on which platform your server is running.

4) Tell what “SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names'” return. Refer: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/identifier-case-sensitivity.html.  (but at the moment I am actually not sure (I simply forgot!) if this variable is supposed to have effect on VIEWs and if VIEWS are ever case-sensitive.  But I think so. I do remember some bugs in this respect – and even think I posted 1 or 2 bugs reprots about this 2-3 years back). 

It is the `g_TreeAlias` that *lowercases* in the JOIN clause.  Anything else I miss here? 

[peterlaursen]

I just posted this bug report: http://bugs.mysql.com/bug.php?id=56751

To me it looks like they (again) messed up with VIEWs. But It looks to me like your server is a Linux server with setting 'lower_case_table_names' = 0 and then another report should be filed for this.

[peterlaursen]

oops .. I overlooked that you told “5.1.41 on Win7-64 (lower_case_table_names=2)”

[peterlaursen]

I also reported this: http://bugs.mysql.com/bug.php?id=56757

It would be very nice if you could create an account at mysql.com (if you don't have already) and post additional information – like CREATE TABLE for the tables involved.  Also check the CREATE VIEW statement again,please – as I cannot create this VIEW (I get 'syntax error').

[edmundek]

'peterlaursen' wrote on '13:

… Also check the CREATE VIEW statement again,please – as I cannot create this VIEW (I get 'syntax error')…

hello, it was just part of view only for example..

here is full sql for “demonstration”:

tables:

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`)
);

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
);

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

I will write the same on mysql forum …

Edit: and thank you for your realy quick answer 🙂

[Author]

Posts