Bug #31562 HAVING and lower case
Submitted: 12 Oct 2007 11:21 Modified: 29 Nov 2007 0:51
Reporter: Adam Charzewski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.45 OS:Any (Linux, Windows)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: having, VIEW

[12 Oct 2007 11:21] Adam Charzewski
Description:
I have two serwers Linux (ver. 5.0.19) and Windows (ver. 5.0.45). Linux works as a master and replication is set to Windows. On Windows machine I have config lower_case_table_names=1.
Replication works OK.
The problem is that on Linux machine I created view and it works and on the Windows machine is an error "View xxxv references invalid table(s) or column(s) or function(s".

 

How to repeat:
On master create following table:
CREATE TABLE  `BazaRozwiazan`.`XXX` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  `test` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and following View:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`,
`X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

Now on windows machine view looks like this:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `xxxv` AS select `x`.`ID` AS `ID`,`x`.`Name` AS `Name`,`x`.`test` AS `test` from `xxx` `X` group by `x`.`Name` having (`x`.`test` = 1);

Suggested fix:
Use in view:
from `XXX` AS `x` insted of from `XXX` AS `X`

or do not use having clause.
[13 Oct 2007 9:04] Peter Laursen
I think this is basically this issue:
http://bugs.mysql.com/bug.php?id=20356

'lower_case_table_names' never worked on MySQL since 4.1 (on 4.0 it worked perfectly).  Now a this bug was reported almost 2 years ago, wan't it about time to do something?
[15 Oct 2007 8:17] Adam Charzewski
This topic is not present in http://bugs.mysql.com/bug.php?id=20356
If lower_case_table_names is set to 1 table name in view should be case insensitive but it is not!
Try following view:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name`;

It works also on windows machine. But following view does not:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

The issue is that having clause is not case insensitive.

You can repeat this problem only using windows machine.
Try this:
CREATE TABLE  `BazaRozwiazan`.`XXX` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  `test` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And this select works:

select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

But this view is not:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

view without having clause works ok:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name`;
[15 Oct 2007 11:12] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 Oct 2007 11:12] Sveta Smirnova
test case

Attachment: bug31562.test (application/octet-stream, text), 376 bytes.

[15 Oct 2007 11:13] Sveta Smirnova
options

Attachment: bug31562-master.opt (application/octet-stream, text), 27 bytes.

[9 Nov 2007 9:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37415

ChangeSet@1.2564, 2007-11-09 11:39:16+02:00, gkodinov@magare.gmz +3 -0
  Bug #31562: HAVING and lower case
  
  The columns in HAVING can reference the GROUP BY and 
  SELECT columns. There can be "table" prefixes when
  referencing these columns. And these "table" prefixes
  in HAVING use the table alias if available.
  This means that table aliases are subject to the same
  storage rules as table names and are dependent on 
  lower_case_table_names in the same way as the table 
  names are.
  Fixed by treating table aliases as table names
  and make them lowercase when printing out the SQL
  statement for view persistence.
[13 Nov 2007 9:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37646

ChangeSet@1.2564, 2007-11-13 11:39:52+02:00, gkodinov@magare.gmz +5 -0
  Bug #31562: HAVING and lower case
  
  The columns in HAVING can reference the GROUP BY and 
  SELECT columns. There can be "table" prefixes when
  referencing these columns. And these "table" prefixes
  in HAVING use the table alias if available.
  This means that table aliases are subject to the same
  storage rules as table names and are dependent on 
  lower_case_table_names in the same way as the table 
  names are.
  Fixed by :
  1. Treating table aliases as table names
  and make them lowercase when printing out the SQL
  statement for view persistence.
  2. Using case insensitive comparison for table 
  aliases when requested by lower_case_table_names
[16 Nov 2007 9:31] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[29 Nov 2007 0:51] Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

HAVING could treat lettercase of table aliases incorrectly if
lower_case_table_names was enabled.